java excel 插入文件,如何在Java中的Excel文件在同一张纸上用插入值的新行

I want to insert new rows while writing in Excel sheet.

Here is my code:

public static void addValuesInWorkbook(String pathAndFileName, String sheetName,

int rowNum, String valuesString,String delimeter) {

if(pathAndFileName.length() > 0 && sheetName.length() > 0 && rowNum >= 0 && valuesString.length() > 0 && delimeter.length() > 0)

{

String[] colValues= null;

if("|".equals(delimeter))

colValues = valuesString.split("\|");

else

colValues = valuesString.split(delimeter);

int cellnum = 0;

FileInputStream fsIP;

try {

fsIP = new FileInputStream(new File(pathAndFileName));

//Read the spreadsheet that needs to be updated

if(rowNum > 0)

{

rowNum--; //As row indexing starts from 0

}

HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook

HSSFSheet sheet = wb.getSheet(sheetName);

HSSFRow row = sheet.getRow(((rowNum>0)?rowNum:0));

HSSFCell cell=null;

for(String colValue:colValues)

{ if(row!=null){

cell = row.getCell(cellnum);

if(cell==null)

cell = row.createCell(cellnum);

}

else if (row == null)

{

row =sheet.createRow(rowNum); //Create a row if it does not exist

cell = row.createCell(cellnum);

}

cell.setCellValue(colValue);

cellnum++;

}

fsIP.close(); //Close the InputStream

FileOutputStream output_file =new FileOutputStream(new File(pathAndFileName)); //Open FileOutputStream to write updates

wb.write(output_file); //write changes

output_file.close(); //close the stream

} catch (FileNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

} catch (IOException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

}

Values are getting inserted with headers(column names

) but whenever new rows are inserted it replaces the older row values.

Please help.

解决方案

This is because you are creating new rows on a row index which already has values.

You need to shift the existing rows 1 row down, so the index you want to insert a row is "free".

In other words, "createRow()" will always create a new row and index x, no matter if there is already one.

The first argument should be the index of the row where you want to insert something, the second one the number of your last row and the third one should be 1. So all rows beginning from x will be shifted one row down and you can insert your row at x.

Example: every line is a row with content.

1 ----------------------

2 ----------------------

3 ---------------------- < you want to insert a new row at index 3

4 ----------------------

5 ----------------------

6 ----------------------

7 ----------------------

shiftRows(3,7,1) will do this:

1 ----------------------

2 ----------------------

3 empty row

4 ---------------------- < your "old" row #3

5 ----------------------

6 ----------------------

7 ----------------------

8 ----------------------

createRow(3), set cell values:

1 ----------------------

2 ----------------------

3 // < your new row #3 witht he new content

4 ---------------------- < your "old" row #3

5 ----------------------

6 ----------------------

7 ----------------------

8 ----------------------