如何在Java中的Excel文件在同一张纸上用插入值的新行 [英] how to insert new rows with values in the same sheet of an excel file in java

问题描述

我想，而在Excel工作表中写入插入新行。

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

下面是我的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.

请帮忙。

推荐答案

这是因为你在其中已经有值的行索引创建新行。
您需要将现有的行转移1排下来的，你要插入行是免费的索引。

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".

在换句话说，createRow（）总是会创建一个新的行和索引X，无论是否已经有一个。

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

使用sheet.shiftRows（..）
看到：
的https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#shiftRows(int, INT，INT）

Use sheet.shiftRows(..) See: https://poi.apache.org/apidocs/org/apache/poi/hssf/usermodel/HSSFSheet.html#shiftRows(int, int, int)

第一个参数应该是你要插入一些行的索引，第二个你的最后一排的号，第三个应该是1。因此，从x开头的行将一行向下移位，你可以在x插入一行。

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），将做到这一点：

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

1 ----------------------
2 ----------------------
3 empty row 
4 ---------------------- < your "old" row #3
5 ----------------------
6 ----------------------
7 ----------------------
8 ----------------------

createRow（3），设置单元格的值：

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 ----------------------

这篇关于如何在Java中的Excel文件在同一张纸上用插入值的新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！