HSSF createrow creates only one row each time

Question

I'm trying to create one workbook with multiple sheets, but always getting only one row in each excel sheet, which is last line in the source file. My code is working as follows:

public void  parse()
{
    BufferedReader reader = null;
    BufferedWriter writer = null;
    String tmpFile = "tmpFile.tmp";
    ExcelFile excelFile = new ExcelFile(_fileName + ".xlsx");
    int lineCount = 0;
    try 
    {
        reader = new BufferedReader(new FileReader(_fileName));
        writer = new BufferedWriter(new FileWriter(tmpFile));
        String line = null;         
        while ( (line = reader.readLine()) != null)
        {
            String newLine = processLine(line.trim());
            if (newLine == null)
                continue;
            else
            {                   
                if (newLine.isEmpty() )
                {
                    lineCount++;
                    if(lineCount > 1)
                    {
                        continue;
                    }
                }
                else if (!newLine.isEmpty())
                {
                    lineCount = 0;
                }
                if (getRepositoryName(newLine) != null && !getRepositoryName(newLine).isEmpty() )
                    excelFile.writeLine(getRepositoryName(newLine), newLine);
            }

        }
        reader.close();
        excelFile.createFile(_fileName.split("\\.")[0]);            
    }
        catch (IOException e) {
            e.printStackTrace();
        }
}

the creation of each row done by: excelFile.writeLine(getRepositoryName(newLine), newLine); from previous method.

writeLine in ExcelFile class implemented as follows:

   public void writeLine(String sheetName, String line)
{
    HSSFSheet sheet = getSheet2(sheetName);     
    HSSFSheet sheetMain = getSheet2(_mainSheetName);
    HSSFRow row = sheet.createRow(sheet.getLastRowNum() == 0 ? 0 :sheet.getLastRowNum()+1);
    HSSFRow rowMain = sheetMain.createRow(sheetMain.getLastRowNum() == 0 ? 0 :sheetMain.getLastRowNum()+1);
    String[] columnsArr = line.split(",");
    for (int i = 0; i < columnsArr.length ; i++)
    {
        HSSFCell cell = row.createCell(i);
        HSSFCell cellMain = rowMain.createCell(i);
        cell.setCellValue(columnsArr[i]);
        cellMain.setCellValue(columnsArr[i]);
    }

}

Need to know what I'm doing wrong, and if it's possible to create multiple row when not running in a loop.

Thanks in advance!

Answer 1

Problem is in lines:

HSSFRow row = sheet.createRow(sheet.getLastRowNum() == 0 ? 0 :sheet.getLastRowNum()+1);
HSSFRow rowMain = sheetMain.createRow(sheetMain.getLastRowNum() == 0 ? 0 :sheetMain.getLastRowNum()+1);

First time method sheet.getLastRowNum() returns 0, so sheet.createRow is given 0 as an argument. But next time result is the same and you again create row 0 (and that is the same in each iteration of the loop).

From the javadoc of getLastRowNum():

Owing to idiosyncrasies in the excel file format, if the result of calling this method is zero, you can't tell if that means there are zero rows on the sheet, or one at position zero. For that case, additionally call getPhysicalNumberOfRows() to tell if there is a row at position zero or not.

So something like this should work:

HSSFRow row = sheet.createRow(sheet.getPhysicalNumberOfRows() == 0 ? 0 :sheet.getLastRowNum()+1);
HSSFRow rowMain = sheetMain.createRow(sheetMain.getPhysicalNumberOfRows() == 0 ? 0 :sheetMain.getLastRowNum()+1);