i'm new creating questions, so here is 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.
So here is some code =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.-
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.
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.
Cheers.
