How to write data into multiple sheets in same excel

Question

I am trying to write the data into same excel file in different sheets, below is the code I tried here only one sheet is creating and data is updating in that sheet, new sheet name is overriding on old sheet. Here I am calling call method two times with 2 different sheet name, when we call from 1st time data need to update in sheet1 and 2nd time call data need to update in sheet2 but in this code only sheet2 is creating and data updating in that.

 public static void call(Map<String, String[]> dataListLMS_IPS, String sheet)
    {
        try {
            String filePath = "C:\\Users\\PATIV25\\Downloads\\APEX UPEX.xlsx";
            File theDir = new File(filePath);
            String filename = theDir.toString();
            FileOutputStream fileOut = new FileOutputStream(filename);
            fileOut.close();
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet spreadsheet = workbook.createSheet(sheet);
            XSSFRow row;
            Set<String> keyid = dataListLMS_IPS.keySet();
            int rowid = 0;
            // writing the data into the sheets...
            CellStyle style = workbook.createCellStyle();
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            for (String key : keyid) {

                row = spreadsheet.createRow(rowid++);
                String[] i = dataListLMS_IPS.get(key);
                int cellid = 0;
                int a = 0;
                for (String obj : i) {
                    Cell cell = row.createCell(cellid++);
                    cell.setCellValue(obj);
                    if (rowid != 1) {
                        if (i[2].equals(i[6]) && i[3].equals(i[7])) {
                            style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
                            cell.setCellValue(obj);
                            if (a == 2 || a == 3 || a == 6 || a == 7)
                                cell.setCellStyle(style);
                            a++;
                        }
                    }
                }
            }
            // .xlsx is the format for Excel Sheets...
            // writing the workbook into the file...
            FileOutputStream out = new FileOutputStream(theDir);
            workbook.write(out);
            out.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }
    public static void main(String[] arg) throws Exception {

        Map<String, String[]> data = new LinkedHashMap<>();
        data.put("A", new String[]{"ACC NO: ", "REPORT TYPE", "PAYMENTID", "AMOUNT", "REPORT TYPE", "PAYMENTID", "AMOUNT", "RESULT"});
        data.put("v", new String[]{"ACC NO: ", "REPORT TYPE", "PAYMENTID", "AMOUNT", "REPORT TYPE", "PAYMENTID", "AMOUNT", "RESULT"});

        call(data, "sheet1");
        call(data, "sheet2");
    }

Does this answer your question? [Creating multiple sheets using Apache poi and servlets](https://stackoverflow.com/questions/20064481/creating-multiple-sheets-using-apache-poi-and-servlets) — Arsh Coder, Oct 27 '21 at 06:24

AppleT · Answer 1 · 2021-10-27T09:21:06.963

The existing logic is incorrect. You need to separate the creation of file and sheets into different sections if you want to call the call method twice. Try this:

public static void call(Map<String, String[]> dataListLMS_IPS, FileOutputStream fileOut) throws IOException
    {
        
            XSSFWorkbook workbook = new XSSFWorkbook();
            
            Set<String> keyid = dataListLMS_IPS.keySet();
            int rowid = 0;
            // writing the data into the sheets...
            CellStyle style = workbook.createCellStyle();
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            
            for (String key : keyid) {
                XSSFSheet spreadsheet = workbook.createSheet(key);
                XSSFRow row;
                row = spreadsheet.createRow(0);
                String[] i = dataListLMS_IPS.get(key);
                int cellid = 0;
                int a = 0;
                for (String obj : i) {
                    XSSFCell cell = row.createCell(cellid++);
                    cell.setCellValue(obj);
                    if (rowid != 1) {
                        if (i[2].equals(i[6]) && i[3].equals(i[7])) {
                            style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.getIndex());
                            cell.setCellValue(obj);
                            if (a == 2 || a == 3 || a == 6 || a == 7)
                                cell.setCellStyle(style);
                            a++;
                        }
                    }
                }
            }
            workbook.write(fileOut);
        
    }
    public static void main(String[] arg) throws Exception {

        Map<String, String[]> data = new LinkedHashMap<>();
        data.put("A", new String[]{"ACC NO: ", "REPORT TYPE", "PAYMENTID", "AMOUNT", "REPORT TYPE", "PAYMENTID", "AMOUNT", "RESULT"});
        data.put("v", new String[]{"ACC NO: ", "REPORT TYPE", "PAYMENTID", "AMOUNT", "REPORT TYPE", "PAYMENTID", "AMOUNT", "RESULT"});
        FileOutputStream fileOut = null;
        try {
            String filePath = "d:\\APEX UPEX.xlsx";
            File theDir = new File(filePath);
            String filename = theDir.toString();
            fileOut = new FileOutputStream(filename);
        
            call(data,  fileOut);
            call(data, fileOut);
        
        }
        catch (Exception e)
        {
            e.printStackTrace();
        } finally {
            if (fileOut != null)
                fileOut.close();
        }
    }

It will create 2 sheets in the same Excel file.

Thanks for giving the solution, it is creating 2 sheets but in second sheet value updating from 1st row, not from 0th row, please help me to solve this. — arun, Oct 27 '21 at 08:37
Its worked but cant we pass sheet name through method instead as key of data, both the data should update in each sheet not one one, can you me to solve this. — arun, Oct 27 '21 at 11:30

How to write data into multiple sheets in same excel

1 Answers1