How to dynamically generate multiple worksheets in apache poi

Question

I would like to dynamically generate multiple worksheets for a workbook/excel in Apache poi. I want to know how can I do it an efficient and thread safe/concurrent way.

1. So multiple worksheet dynamically with the option to name them.
2. Each worksheet will have their own set of columns etc ( or style).
3. Write return those back in a servlet etc.

Please help.

Thank you.

Answer 1

Please find the sample code.

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ExcelUtility {

    public static boolean writeDataSheetWise(final String excelFileName, final List<String> headers,
            Map<String, List<Object[]>> sheetRowDataList) throws IOException, InvalidFormatException {

        boolean isWritten = false;
        HSSFWorkbook workbook = new HSSFWorkbook();
        for(String sheetName: sheetRowDataList.keySet()) {
            createSheet(workbook, sheetName, headers, sheetRowDataList.get(sheetName));
        }

        try {
            System.out.println("\nWritting data to excel file <" + excelFileName + ">");

            FileOutputStream outputStream = new FileOutputStream(new File(excelFileName));
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            isWritten = true;

            System.out.println("\nData is successfully written to excel file <"+excelFileName+">.");

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return isWritten;
    }

    public static boolean writeData(final String excelFileName, final String sheetName, final List<String> headers,
            List<Object[]> rowDataList) throws IOException, InvalidFormatException {

        boolean isWritten = false;
        HSSFWorkbook workbook = new HSSFWorkbook();
        createSheet(workbook, sheetName, headers, rowDataList);

        try {
            System.out.println("\nWritting data to excel file <" + excelFileName + ">");

            FileOutputStream outputStream = new FileOutputStream(new File(excelFileName));
            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
            isWritten = true;

            System.out.println("\nData is successfully written to excel file <"+excelFileName+">.");

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return isWritten;

    }

    @SuppressWarnings("deprecation")
    private static void createSheet(final HSSFWorkbook workbook, final String sheetName, final List<String> headers,
            final List<Object[]> rowDataList) {

        HSSFSheet sheet = workbook.createSheet(sheetName);

        int rowCount = 0;

        HSSFCellStyle style = workbook.createCellStyle();
        HSSFFont headersFont = workbook.createFont();
        headersFont.setFontName(HSSFFont.FONT_ARIAL);
        headersFont.setFontHeightInPoints((short) 16);
        headersFont.setColor(HSSFColor.GREEN.index);
        headersFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(headersFont);

        // Creating header row
        Row headerRow = sheet.createRow(rowCount++);
        for (int i = 0; i < headers.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue(headers.get(i));
            sheet.autoSizeColumn(i);
        }

        for (Object rowDataObject[] : rowDataList) {
            Row row = sheet.createRow(rowCount++);
            int cellnum = 0;
            for (Object rowData : rowDataObject) {
                Cell cell = row.createCell(cellnum++);
                if (rowData instanceof Date)
                    cell.setCellValue((Date) rowData);
                else if (rowData instanceof Boolean)
                    cell.setCellValue((Boolean) rowData);
                else if (rowData instanceof String)
                    cell.setCellValue((String) rowData);
                else if (rowData instanceof Integer)
                    cell.setCellValue((Integer) rowData);
                else if (rowData instanceof Long)
                    cell.setCellValue((Long) rowData);
                else if (rowData instanceof Double)
                    cell.setCellValue((Double) rowData);

            }
        }
    }
}

Answer 2

like this?

public static void createExcel(String excelFilePath, String sheetName)
        throws IOException {
    FileOutputStream fos = null;
    try {
        HSSFWorkbook workbook = null;
        if (new File(excelFilePath).createNewFile()) {
            workbook = new HSSFWorkbook();
        } else {
            POIFSFileSystem pfs = new POIFSFileSystem(new FileInputStream(
                    new File(excelFilePath)));
            workbook = new HSSFWorkbook(pfs);
        }
        if (workbook.getSheet(sheetName) == null) {
            fos = new FileOutputStream(excelFilePath);
            workbook.createSheet(sheetName);
            workbook.write(fos);
        }

    } catch (IOException e) {
        throw e;
    } finally {
        if (fos != null) {
            fos.close();
        }
    }
}