Saturday, September 14, 2013

Creating an excel (*.xls) file with apache.poi library

Apache poi provides a very clean api to create and manipulate microsoft documents.
Here an example of creating an xls file from a String having of format like below:
"column1";"column2";"column3" \n"line1value1";"line1value2";"line1value3"\n"line2value1";"line2value2";"line2value3"
 
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class StringToExcelFileConverter {

    public static File convert(String csvString) {

        try {

            File xlsTempFile = File.createTempFile("temp" + System.nanoTime(), ".xls");
            FileOutputStream fileOut = new FileOutputStream(xlsTempFile);
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet worksheet = workbook.createSheet("detail");
            String separator = getSeparator();//like ";"  in above string
            String[] rowStrings = csvString.split("\n");
            for (int i = 0; i < rowStrings.length; i++) {
                HSSFRow row1 = worksheet.createRow((short) i);
                String[] columns = rowStrings[i].split(separator);
                for (int j = 0; j < columns.length; j++) {
                    HSSFCell cell = row1.createCell((short) j);
                    cell.setCellValue(columns[j].replace("\"", ""));
                    HSSFCellStyle cellStyle = workbook.createCellStyle();
                    cellStyle.setFillForegroundColor(HSSFColor.WHITE.index);
                    cell.setCellStyle(cellStyle);
                }
            }

            workbook.write(fileOut);
            fileOut.flush();
            fileOut.close();
            return xlsTempFile;
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
 return null;
    }
}
Now you can open that file with following line if you have any application associated with the xls extension

Desktop.getDesktop().open(xlsFile);

No comments:

Post a Comment