i'm new creating questions, so here is i go =D
我是新创建的问题，这里是i go =D。
I have all my data stored in a SXSSFWorkbook and i'm trying to add some formula information before write and save the file, everything works ok but when i open the file there is some blank cells where the formula should be, i found that 176 rows return NULL, thats why i cant create the formula cells on the first 176 rows, thats the odd part because there is data on those rows, after the 176 row the formula seems to be ok, does the work and everything else works, update, create, add more rows and save.
我有我所有的数据存储在一个SXSSFWorkbook和我想添加一些公式信息编写和保存文件之前,一切都好,但当我打开文件有一些空白单元格的公式应该是,我发现176行返回NULL,这是为什么我不能创建公式细胞在第一个176行,这奇怪的部分,因为那些行数据,在176年行公式似乎是好的,都是工作和其他工作,更新、创建、添加更多的行并保存。
So here is some code =D.
这里是一些代码=D。
private void fillWorkBook(Vector tableData, String path) throws FileNotFoundException, IOException, InvalidFormatException{
//this.xlsxWorkbook.setSheetName(0, "Proshop");
SXSSFWorkbook wb = new SXSSFWorkbook(500);
wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
this.createHeader(wb);
if(this.checkFile(path)){
File f = new File(path);
OPCPackage pkg = pkg = OPCPackage.open(f);
this.xlsxWorkbook = new XSSFWorkbook(pkg);
Vector oldData = this.createDataVectorXLSX(0);
for(int i = 5; i >= 0; i--)
oldData.removeElementAt(i);
for(int i = 0; i < 7; i++)
oldData.removeElementAt(oldData.size()-1);
this.fillWithTableData(wb, oldData);
pkg.close();
//System.out.println(path);
} else {
//System.out.println("nuevo archivo - fillWorkData");//tableData.toString());
}
this.fillWithTableData(wb, tableData);
this.createFooter(wb);
this.setColumnWidth(wb.getSheetAt(0));
this.setCombinedCells(wb.getSheetAt(0));
//sheet.shiftRows(5, 6, 10);
//col, fil
wb.getSheetAt(0).createFreezePane(0, 6);
this.addImage(wb);
this.createFormulaData(wb);
wb.setForceFormulaRecalculation(true);
this.createDataSalida(wb, path);
}
private void createFormulaData(SXSSFWorkbook wb) {
XSSFSheet sheetXLSX = null;
sheetXLSX = (SXSSFSheet) wb.getSheetAt(0);
Row row;
float marcaImpuesto[] = ViewController.factores;
ViewController.factores = null;
int cells = 0;
for ( int i = 6; i <= sheetXLSX.getLastRowNum(); i ++ ){
row = sheetXLSX.getRow( i );
if(row != null){
if(cells < row.getLastCellNum())
cells = row.getLastCellNum();
for ( int j = 0; j < cells; j++ ){
Cell cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
switch(j){
case 9:
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(""+CellReference.convertNumToColString(j-4)+(i+1)+"*"+marcaImpuesto[3]);
break;
case 8:
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(""+CellReference.convertNumToColString(j-3)+(i+1)+"*"+marcaImpuesto[2]);
break;
case 7:
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(""+CellReference.convertNumToColString(j-2)+(i+1)+"*"+marcaImpuesto[1]);
break;
case 6:
cell.setCellType(HSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula(""+CellReference.convertNumToColString(j-1)+(i+1)+"*"+marcaImpuesto[0]);
break;
case 5:
cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
break;
default:
//cell.setCellType(Cell.);
break;
}
}
} }
}
UPDATE.-
UPDATE. -
Well the solution it's a bit complex, needs more code and CPU time, first you need to know what the cells will be(number, string, null, formula,etc), then create all your cell using XSSFCell or HSSFCell or the new generic SS usermodel, then create your cells inside a temp Sheet, make all the changes you gonna need to finally iterate over that Sheet and populate the SXSSFSheet.
解决方案有点复杂,需要更多的代码和CPU时间,首先你需要了解细胞将被(数字、字符串,null,公式等),然后创建你所有的细胞使用XSSFCell HSSFCell或新通用的党*usermodel,然后创建你的细胞在一个临时表,让所有你需要的改变并填充SXSSFSheet最后遍历表。
One more thing
一件事
Spreadsheet API Feature Summary as you can see i can't evaluate formulas but in my code im doing all the process, and i KNOW already it won't save the formula evaluated, thats why i used wb.setForceFormulaRecalculation(true); so the another solution it's write only numbers, that means evaluate and do all the math with java and then save plain numbers.
你可以看到，我不能评估公式，但是在我的代码中，我做了所有的过程，我知道它不会保存公式的计算，这就是为什么我使用了wb. setforceformula (true);另一种解决方案是只写数字，这意味着用java计算和计算所有的数学运算然后保存简单的数字。
Cheers.
欢呼。
1 个解决方案
#1
-1
Take a look at SXSSFSheet.setRandomAccessWindowSize(int), this property set the number of rows will be in memory.
看一下SXSSFSheet.setRandomAccessWindowSize(int)，该属性设置的行数将在内存中。
#1
-1
Take a look at SXSSFSheet.setRandomAccessWindowSize(int), this property set the number of rows will be in memory.
看一下SXSSFSheet.setRandomAccessWindowSize(int)，该属性设置的行数将在内存中。