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;
}
}
} }
}
Aucun commentaire:
Enregistrer un commentaire