需要太长时间保存

需要太长时间保存

本文介绍了FileOutputStream(Apachhe POI)需要太长时间保存的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

当我使用Apache poi编辑.xlsx文件时,保存时间太长..xlsx文件包含公式格式设置和冻结窗格.我正在使用以下代码,

When I am edit a .xlsx file using Apache poi, its taking too long to save. The .xlsx file contains, formulas formatting and freeze pane. I am using the following code,

try {
            FileInputStream file = new FileInputStream(new File(path));
            XSSFWorkbook fWorkbook = new XSSFWorkbook(file);
            XSSFSheet fSheet = fWorkbook.getSheetAt(0);

            for(int i = 0; i < jTable1.getRowCount(); i++){
                if(jTable1.getModel().getValueAt(i, index1).equals("1")){
                    XSSFCell cell = fSheet.getRow(i+1).getCell(index1);
                    cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                    cell.setCellValue(new Double(1));
                    XSSFCellStyle cs = fWorkbook.createCellStyle();
                    cs.setDataFormat(fWorkbook.getCreationHelper().createDataFormat().getFormat("dd/MMMM/yyyy"));
                    cell =fSheet.getRow(i+1).getCell(index2);
                    cell.setCellValue(new Date());
                    cell.setCellStyle(cs);
                }
            }
            file.close();
            FileOutputStream fileOutputStream = new FileOutputStream(path);
            fWorkbook.write(fileOutputStream);
            fileOutputStream.close();
            JOptionPane.showMessageDialog(this, "Data saved successfully.");
            parent.removeContent();
        }catch(Exception e){
            e.printStackTrace();
        }

excel文件: http://ge.tt/5orGWSJ2/v/0?c

从Excel将数据加载到JTable中:

Loading Data into JTable from Excel:

try {
                FileInputStream file1 = new FileInputStream(new File("c:\\sample.xlsx"));
                XSSFWorkbook workbook = new XSSFWorkbook(file1);
                XSSFSheet sheet = workbook.getSheetAt(0);
                int rowc=sheet.getLastRowNum()+1;
                int colc=sheet.getRow(0).getLastCellNum();
                Object heading[] = new Object[colc+1];
                XSSFRow row1 = sheet.getRow(0);
                for(int i=0, column =0; i < colc; i++){
                    if(i == 1){
                        heading[column++] = "";
                    }
                    heading[column++] = cellToString(row1.getCell(i));
                }
                Object [][]j=new Object[rowc-1][colc+1];

                for (int i = 1; i < rowc; i++) {
                    row1 = sheet.getRow(i);
                    for (int jj = 0, column = 0; jj < colc; jj++) {
                        if(column == 1){
                            j[i-1][column++] = new Boolean(false);
                            j[i-1][column] = cellToString(row1.getCell(jj));
                        }
                        else{
                            j[i-1][column]=cellToString(row1.getCell(jj));
                        }
                        column++;
                    }
                }

                jTable1.setModel(new DefaultTableModel(j, heading){
                    public Class getColumnClass(int columnIndex) {
                        if(columnIndex == 0){
                            return java.lang.Integer.class;
                        }
                        else if(columnIndex == 1){
                            return java.lang.Boolean.class;
                        }
                        else{
                            return java.lang.Object.class;
                        }
                    }
                });

                jTable1.getColumnModel().getColumn(1).setMaxWidth(60);
                jTable1.setAutoResizeMode( JTable.AUTO_RESIZE_OFF );
                jTable1.getTableHeader().setReorderingAllowed(false);
            } catch (IOException ex) {
                JOptionPane.showMessageDialog(BarcodePrint.this, ex);
            }

在编辑BarcodePrint列之后将数据写入excel:

Writing data into excel after editing column BarcodePrint:

try {
            FileInputStream file = new FileInputStream(new File("c:\\sample.xlsx"));
            XSSFWorkbook fWorkbook = new XSSFWorkbook(file);
            XSSFCellStyle cs = fWorkbook.createCellStyle();
            cs.setDataFormat(fWorkbook.getCreationHelper().createDataFormat().getFormat("dd/MMMM/yyyy"));
            Date currentdate = new Date();
            XSSFSheet fsheet = fWorkbook.getSheetAt(0);
            Double barcodeprintstatus = new Double(1);
            int newindex = 24;
            int printdate = 26;
            int uniqueid = 27;
            for(int i = 0; i < jTable1.getModel().getRowCount(); i++){
                if(jTable1.getModel().getValueAt(i, newindex).equals("1")){
                    for(int k=1; k < fsheet.getPhysicalNumberOfRows(); k++){
                        XSSFCell cell = fsheet.getRow(k).getCell(uniqueid-1);
                        String uid = cell.getRawValue();
                        if(jTable1.getModel().getValueAt(i, uniqueid).toString().equals(uid)){
                            cell = (fsheet.getRow(i+1)).getCell(newindex-1);
                            cell.setCellType(XSSFCell.CELL_TYPE_NUMERIC);
                            cell.setCellValue(barcodeprintstatus);
                            cell = fsheet.getRow(i+1).getCell(printdate-1);
                            cell.setCellValue(currentdate);
                            cell.setCellStyle(cs);
                        }
                    }
                }
            }

            file.close();
            FileOutputStream fileOutputStream = new FileOutputStream("c:\\sample.xlsx");
            fWorkbook.write(fileOutputStream); // this is taking so much of time. Approximately 1 min.
            fileOutputStream.close();
        }catch(Exception e){
            e.printStackTrace();
        }

我无法解决此问题.如上所述, fWorkbook.write(fileOutputStream); 花费了很多时间.请帮忙.还有其他方法可以保存excel文件吗?还是我可以为单个列而不是孔工作簿部分地写数据?

I am unable to solve this problem. fWorkbook.write(fileOutputStream); is taking so much of time as I mentioned above. Please help. Is there any other way to save the excel file? Or can I write data partially for a single column rather than the hole workbook?

推荐答案

尝试改进代码.

您两次调用 fSheet.getRow(i + 1).尝试引入一个变量并重用该行而不是获取它.

You call fSheet.getRow(i+1) twice. Try to introduce a variable and reuse the row rather than obtain it.

cell.setCellValue(new Double(1));

在for循环之前创建1个double并重复使用.

Create the 1 double once before the for loop and reuse it.

XSSFCellStyle cs = fWorkbook.createCellStyle();
cs.setDataFormat(fWorkbook.getCreationHelper().createDataFormat().getFormat("dd/MMMM/yyyy"));

将单元格样式创建和初始化移出for循环.在循环之前创建它并重复使用.

Move the cell style creation and initializing out of the for loop. Create it before the loop and reuse.

cell.setCellValue(new Date());

引入一个Date变量并仅创建一次Date.再次在for循环之前.

Introduce a Date variable and create the Date just once. Again before the for loop.

这篇关于FileOutputStream(Apachhe POI)需要太长时间保存的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

07-30 19:53