当我使用Apache poi编辑一个包含公式、格式和冻结窗格的.xlsx文件时，保存时间太长了。我正在使用以下代码：
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();
}
编辑 1：
Excel 文件：http://ge.tt/5orGWSJ2/v/0?c
从 Excel 将数据加载到 JTable 中：
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：
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文件的方法？或者我可以仅针对单个列部分地写入数据，而不是整个工作簿？