write data to excel file in java using apache POI

Question

I am having a problem here while writing some data to an Excel sheet by using Apache POI. I want to call a function several times which will write the data into Excel sheet that I have created.

Is there any way that I can move the pointer to the next row every time at the end of the function???

My code is given below...

public static void excelLog(String filename , String message)
  {  

    String dest="D:\\testexcel.xls";
    HSSFWorkbook myWorkBook = new HSSFWorkbook();
    HSSFSheet mySheet = myWorkBook.createSheet();
    HSSFRow myRow = null;
    HSSFCell myCell = null;
    String excelData [][] = new String [1][2];
    excelData[0][0]=filename;
    excelData[0][1]=message;

    myRow = mySheet.createRow(rowNum);

    for (int cellNum = 0; cellNum < 2 ; cellNum++){
    myCell = myRow.createCell((short) cellNum);
    myCell.setCellValue(excelData[rowNum][cellNum]);      

    }
    rowNum++;

    try{
        FileOutputStream out = new FileOutputStream(dest);
        myWorkBook.write(out);
        out.close();
    }catch(Exception e){
        e.printStackTrace();
    }           
 }

Please help me in this. Thank you :)

one more thing i was wondering that here i just want to insert two columns in the excel sheet so i used 2-d array. if i have to fill 5 columns in the sheet then using array will be too complicated right??? so what is the substitute here?? — ASH
– ASH, Commented Jun 27, 2012 at 9:54

Iswanto San · Accepted Answer · 2012-06-27 10:30:37Z

This is modified code so it's support dynamic column size. The idea is to create a row once and reuse it if the row is already exists.

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PoiSample {

private static String dest = "D:\\testexcel.xls";
private static HSSFWorkbook myWorkBook = new HSSFWorkbook();
private static HSSFSheet mySheet = myWorkBook.createSheet();

private static void excelLog(int row, int col, String value) {
    HSSFRow myRow = mySheet.getRow(row);

    if (myRow == null)
        myRow = mySheet.createRow(row);

    HSSFCell myCell = myRow.createCell(col);
    myCell.setCellValue(value);
}

public static void main(String[] args) {
    int numCol = 10; // assume 10 cols

    for (int i = 0; i < 10; i++) {
        for (int j = 0; j < numCol; j++) {
            excelLog(i, j, "Row : " + i + ", Cell : " + j);
        }
    }

    try {
        FileOutputStream out = new FileOutputStream(dest);
        myWorkBook.write(out);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}

Iswanto San · Accepted Answer · 2013-04-05 13:13:44Z

3

The problem with your code is in every call for excelLog method, it's will generate new excel file. I changed your code as below.

import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class PoiSample {

private static String dest = "D:\\testexcel.xls";
private static HSSFWorkbook myWorkBook = new HSSFWorkbook();
private static HSSFSheet mySheet = myWorkBook.createSheet();

public static void excelLog(String filename, String message, int rowNum) {

    HSSFRow myRow = null;
    HSSFCell myCell = null;
    String excelData[][] = new String[1][2];
    excelData[0][0] = filename;
    excelData[0][1] = message;

    myRow = mySheet.createRow(rowNum);

    for (int cellNum = 0; cellNum < 2; cellNum++) {
        myCell = myRow.createCell(cellNum);
        myCell.setCellValue(excelData[0][cellNum]);

    }

    try {
        FileOutputStream out = new FileOutputStream(dest);
        myWorkBook.write(out);
        out.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

public static void main(String[] args) {
    for (int i = 0; i < 10; i++)
        excelLog("filename " + i, "message " + i, i);
}

}

edited Apr 5, 2013 at 13:13

answered Jun 27, 2012 at 9:03

Iswanto San

18.6k13 gold badges60 silver badges79 bronze badges

1 Comment

ASH Over a year ago

can you tell me one more thing i was wondering that here i jst want to insert two coloumns in the excel sheet so i used 2-d array. if i have to fill 5 coloumns in the sheet then using array will be too complicated right??? so what is the substitute here??

Collectives™ on Stack Overflow

write data to excel file in java using apache POI

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related