CODEWITHSUNDEEP
javaexcelnullpointerexceptionapache-poioverwrite
Shreyas SG
Shreyas SG

Reputation: 388

Java | Apache POI | How to avoid null pointer exception without creating a row

I am stuck in a deadlock situation here.

I have values stored in an ArrayList. Each value is a string, which is concat of few other strings separated by a delimiter (##).

Eg: ArrayList lst has following 4 values.

  1. aaa##bbb##ccc##
  2. d##ee##ffdfg##
  3. w##xx##zzzzz##
  4. mmmm##n##ooo##

I am iterating in a for-loop, for lst.size(), and fetching each value at once. Where, I am splitting the string and storing it into a String Array.

erow = aaa##bbb##ccc##

String[] arry = erow.split("##");

Within this for-loop, I am using another for-loop, for arry.length() to fetch each of the string (like aaa) and write it into a cell on excel, using Apache POI. Finally, I want aaa in cell A1 of excel, bbb in cell B1, ccc in cell C1, d in cell A2, so on.

The problem I am facing is, after every iteration, values in previous column (A1 to A4, B1 to B4 etc.) is being erased. At the end, only C1 to C4 values remain.

I have used CreateRow and CreateCell methods. If i use getRow and getCell methods, I am getting NullPointerException!

If I use an excel that has some data in A1 through C4 cells, and use getRow, getCell and setCellValue methods, things work splendidly. The aaa, bbb values are being written onto corresponding cells.

But every time i can't use excel with existing data. I want data to be written onto new excel file. If the excel doesn't have any data, getCell, getRow methods throw NPE.

Please help.. how can i resolve this?

Main()

xlo.createExcel(xlPath, xl_name, tabName,".xls");
for(int s=0;s<vals.size();s++)
{
   erow = vals.get(s);
   String[] cval = erow.split("###");

   for(int c=0; c<cval.length;c++)
   {
      xlo.writeExcel(xlPath+"/"+xl_name, tabName, s, c, cval[c]);
      System.out.println(cval[c]);
   }
   System.out.println();                        
}

System.out.println("File created ---> "+xl_name+"\n");

WriteExcel()

public void writeExcel(String xlPath, String xlSheet, int rowNum, int cellNum, String inputString) throws Exception 
{
    FileInputStream fis = new FileInputStream(xlPath);
    Workbook wb =  WorkbookFactory.create(fis);
    Sheet sh = wb.getSheet(xlSheet);
    Row row = sh.createRow(rowNum);
    Cell cell=row.createCell(cellNum);

    CellStyle cs = wb.createCellStyle();
    //cs.setWrapText(true);
    cell.setCellStyle(cs);

    cell.setCellValue(inputString);
    FileOutputStream fos=new FileOutputStream(xlPath);
    wb.write(fos);
    wb.close();
    fos.close();

}

CreateExcel()

public void createExcel(String path, String fileName, String tabName, String fileExtn) throws Exception 
{
    Workbook workbook = new XSSFWorkbook();
    workbook.createSheet(tabName);
    FileOutputStream fileOut;
    try 
    {
        fileOut = new FileOutputStream(path+"/"+fileName+fileExtn);
        workbook.write(fileOut);

        fileOut.close();
        workbook.close();
    } 
    catch (Exception e) 
    {
        System.out.println("Excel not created!");
    }
}

Upvotes: 1

Views: 1700

Answers (1)

Susannah Potts
Susannah Potts

Reputation: 827

I've had to do exactly this before (but with a , delimiter). Here's the reduced version of that code for ##:

private XSSFWorkbook writeToExcel(LinkedHashMap<Integer, String> formattedReport) {
    try {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet();
        int rowIndex = sheet.getLastRowNum();
        Set<Integer> keySet = formattedReport.keySet();
        for (Integer key : keySet) {
            XSSFRow row = sheet.createRow(rowIndex++);
            String line = formattedReport.get(key);
            String[] strArr = line.split("##");

            int cellCount = 0;
            for (String str : strArr) {
                XSSFCell cell = row.createCell(cellCount++);
                cell.setCellValue(str);
            }
        }

        return workbook;

    } catch (Exception e) {
        e.printStackTrace();
    }

    return null; //or return new XSSFWorkbook();
}

This should work as you want it to. It uses a Map so you'll have to change it to use ArrayList but it should function no differently.

Upvotes: 2

Related Questions