我想.xlsx在 JAVA 中使用 POI 編寫一個(gè)大文件（50K 行）。我希望每一行都包含多個(gè)下拉單元格。當(dāng)行數(shù)小于 30K 時(shí)代碼工作正常，但如果行數(shù)超過 35K 則寫入損壞的文件。我試過了SXSSFWorkbook，XSSFWorkbook但沒有一個(gè)對我有用。這是代碼： SXSSFWorkbook workbook = new SXSSFWorkbook(100); SXSSFSheet sheet = workbook.createSheet(); String[] optionsArray = new String[] {"1000.00","2000.00"}; int no_of_rows = 35000; for(int i=0;i<=no_of_rows;i++) { SXSSFRow row1 = sheet.createRow(i); SXSSFCell r1c1 = row1.createCell(0); DataValidationConstraint constraint1 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray); CellRangeAddressList addressList1 = new CellRangeAddressList(i, i, 0, 0); DataValidation dataValidation1 = sheet.getDataValidationHelper().createValidation(constraint1, addressList1); sheet.addValidationData(dataValidation1); r1c1.setCellValue("1000.00"); SXSSFCell r1c2 = row1.createCell(1); DataValidationConstraint constraint2 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray); CellRangeAddressList addressList2 = new CellRangeAddressList(i, i, 1, 1); DataValidation dataValidation2 = sheet.getDataValidationHelper().createValidation(constraint2, addressList2); sheet.addValidationData(dataValidation2); r1c2.setCellValue("2000.00"); } FileOutputStream fos =new FileOutputStream(new File("c:\\data\\testout.xlsx")); workbook.write(fos); workbook.close(); fos.close();我希望 POI 至少能夠很好地處理 50K 行此類數(shù)據(jù)。
1 回答
動(dòng)漫人物
TA貢獻(xiàn)1815條經(jīng)驗(yàn) 獲得超10個(gè)贊
不要DataValidation為每個(gè)單元格創(chuàng)建一個(gè)。相反，只創(chuàng)建兩個(gè)需要的數(shù)據(jù)驗(yàn)證，一個(gè)A1:A50001用于B1:B50001. 為此，CellRangeAddressLists 可以包含整個(gè)列范圍：
CellRangeAddressList addressList1 = new CellRangeAddressList(0, no_of_rows, 0, 0);
和
CellRangeAddressList addressList1 = new CellRangeAddressList(0, no_of_rows, 1, 1);
你的例子：
import java.io.File;
import java.io.FileOutputStream;
import org.apache.poi.ss.util.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.*;
class CreateSXSSFWorkbookDataValidations {
public static void main(String[] args) throws Exception {
SXSSFWorkbook workbook = new SXSSFWorkbook(100);
SXSSFSheet sheet = workbook.createSheet();
String[] optionsArray = new String[] {"1000.00","2000.00"};
int no_of_rows = 50000;
for(int i=0;i<=no_of_rows;i++) {
SXSSFRow row1 = sheet.createRow(i);
SXSSFCell r1c1 = row1.createCell(0);
r1c1.setCellValue("1000.00");
SXSSFCell r1c2 = row1.createCell(1);
r1c2.setCellValue("2000.00");
}
DataValidationConstraint constraint1 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);
CellRangeAddressList addressList1 = new CellRangeAddressList(0, no_of_rows, 0, 0);
DataValidation dataValidation1 = sheet.getDataValidationHelper().createValidation(constraint1, addressList1);
sheet.addValidationData(dataValidation1);
DataValidationConstraint constraint2 = sheet.getDataValidationHelper().createExplicitListConstraint(optionsArray);
CellRangeAddressList addressList2 = new CellRangeAddressList(0, no_of_rows, 1, 1);
DataValidation dataValidation2 = sheet.getDataValidationHelper().createValidation(constraint2, addressList2);
sheet.addValidationData(dataValidation2);
FileOutputStream fos = new FileOutputStream(new File("testout.xlsx"));
workbook.write(fos);
workbook.close();
fos.close();
}
}
當(dāng)不同的唯一數(shù)據(jù)驗(yàn)證的計(jì)數(shù)超過限制時(shí)，不可能對每個(gè)單個(gè)單元格進(jìn)行不同的唯一數(shù)據(jù)驗(yàn)證。這個(gè)限制是一個(gè)Excel限制，而不是apache poi一個(gè)。
Excel 規(guī)范和限制中沒有記錄這種數(shù)據(jù)驗(yàn)證的特殊限制。但這可能與唯一單元格格式/單元格樣式 (64,000) 的限制或工作表中超鏈接的限制 (66,530) 有關(guān)。
添加回答
舉報(bào)
0/150
提交
取消