Usages of

org.apache.poi.xssf.streaming.SXSSFCell.setCellFormula(?)
/** * ���������������cell���������styleMap��������������������������������������������� * @param targetCell ������������ * @param sourceCell ������������ * @param targetWork ������������ * @param sourceWork ������������ * @param styleMap ������������ */ public static void copyCell(SXSSFCell targetCell, XSSFCell sourceCell, SXSSFWorkbook targetWork, XSSFWorkbook sourceWork) { if(targetCell == null || sourceCell == null || targetWork == null || sourceWork == null ){ throw new IllegalArgumentException("������PoiUtil.copyCell()������������targetCell���sourceCell���targetWork���sourceWork���������������������������������������"); } //��������������������� XSSFCellStyle newCellStyle = (XSSFCellStyle) targetWork.createCellStyle(); newCellStyle.cloneStyleFrom(sourceCell.getCellStyle()); targetCell.setCellStyle(newCellStyle); //��������������������� switch (sourceCell.getCellType()) { case XSSFCell.CELL_TYPE_STRING: targetCell.setCellValue(sourceCell.getRichStringCellValue()); break; case XSSFCell.CELL_TYPE_NUMERIC: targetCell.setCellValue(sourceCell.getNumericCellValue()); break; case XSSFCell.CELL_TYPE_BLANK: targetCell.setCellType(XSSFCell.CELL_TYPE_BLANK); break; case XSSFCell.CELL_TYPE_BOOLEAN: targetCell.setCellValue(sourceCell.getBooleanCellValue()); break; case XSSFCell.CELL_TYPE_ERROR: targetCell.setCellErrorValue(sourceCell.getErrorCellValue()); break; case XSSFCell.CELL_TYPE_FORMULA: targetCell.setCellFormula(sourceCell.getCellFormula()); break; default: break; } }
private void setCellData(){ SXSSFCell sxssfCell = _xssfCellExportContext.getExcelCell(); DataType dataType = _xssfCellExportContext.getDataType(); String data = _xssfCellExportContext.getData(); Map<String,Object> contentMap = _xssfCellExportContext.contentMap(); String tableColumn =(String) _xssfCellExportContext.getVariable("tableColumn"); if(tableColumn != null){ dataType = DataType.string; data = tableColumn; } try{ switch(dataType){ case formula: sxssfCell.setCellType(Cell.CELL_TYPE_FORMULA); Object fv = _xssfCellExportContext.getFormulaValue(); if(fv != null){ if(fv instanceof Number){ sxssfCell.setCellValue(((Number)fv).doubleValue()); }else{ String fvString = fv.toString(); Double fvDouble = Doubles.tryParse(fvString); if(fvDouble != null){ // if value is numeric, we cached sxssfCell.setCellValue(fvDouble); }else{ // else we miss it.It helps we import. sxssfCell.setCellValue(""); } } } sxssfCell.setCellFormula(SpreadsheetExportHelper.filterFormulaIllegalCharacter(data.substring(1))); Integer afrow = (Integer)contentMap.get("afrow"); Integer aerow = (Integer)contentMap.get("aerow"); Integer afcol = (Integer)contentMap.get("afcol"); Integer aecol = (Integer)contentMap.get("aecol"); if(afrow != null && afcol != null ){ Map<String,String> attributes = new HashMap<>(); attributes.put("ca", "1"); if(aerow != null && aecol != null){ int rowIndex= _xssfCellExportContext.sheetCell().getX(); int columnIndex = _xssfCellExportContext.sheetCell().getY(); attributes.put("aca", "1"); attributes.put("ref", CellReference.convertNumToColString(columnIndex-1)+rowIndex+":"+CellReference.convertNumToColString(columnIndex+aecol-1)+(rowIndex+aerow)); attributes.put("t", "array"); }else{ sxssfCell.setCellFormula(""); } sxssfCell.getFormulaValue().setAttributes(attributes); } break; case bool: sxssfCell.setCellType(Cell.CELL_TYPE_BOOLEAN); sxssfCell.setCellValue(Boolean.valueOf(data.toLowerCase())); break; case string: sxssfCell.setCellType(Cell.CELL_TYPE_STRING); sxssfCell.setCellValue(data); break; case numeric: sxssfCell.setCellType(Cell.CELL_TYPE_NUMERIC); sxssfCell.setCellValue(Double.parseDouble(data)); break; case date: Date javaDate = DateTimeUtil.parseAsYYYYMMdd(data); sxssfCell.setCellValue(javaDate); break; case time: double timeValue = convertTimeAsDouble(data); sxssfCell.setCellValue(timeValue); break; case datetime: Date javaDatetime = DateTimeUtil.parseAsYYYYMMddHHmmss(data); sxssfCell.setCellValue(javaDatetime); break; case error: sxssfCell.setCellType(Cell.CELL_TYPE_ERROR); sxssfCell.setCellValue(data); break; case blank: sxssfCell.setCellType(Cell.CELL_TYPE_BLANK); break; } }catch(Exception ex){ sxssfCell.setCellType(Cell.CELL_TYPE_STRING); sxssfCell.setCellValue(_xssfCellExportContext.sheetCell().getRawData()); } }
public static void main(String[] args) { SXSSFWorkbook workbook = new SXSSFWorkbook(); SXSSFSheet spreadsheet = workbook.createSheet("Scores Info"); SXSSFRow row; SXSSFCell cellA; SXSSFCell cellB; SXSSFCell cellC; CellStyle headerRowStyle = createHeaderRowStyle(workbook); // create the header row SXSSFRow headerRow = spreadsheet.createRow(0); cellA = headerRow.createCell(0, CellType.STRING); cellA.setCellStyle(headerRowStyle); cellA.setCellValue("Name"); cellB = headerRow.createCell(1, CellType.STRING); cellB.setCellStyle(headerRowStyle); cellB.setCellValue("Age"); cellC = headerRow.createCell(2, CellType.STRING); cellC.setCellStyle(headerRowStyle); cellC.setCellValue("Scores"); SXSSFCell cellD = headerRow.createCell(3); cellD.setCellStyle(headerRowStyle); SXSSFCell cellE = headerRow.createCell(4, CellType.STRING); cellE.setCellStyle(headerRowStyle); cellE.setCellValue("Average score"); spreadsheet.trackColumnForAutoSizing(4); spreadsheet.autoSizeColumn(4); Font oddFont = workbook.createFont(); oddFont.setColor(IndexedColors.GREEN.index); CellStyle oddRowStyle = workbook.createCellStyle(); oddRowStyle.setFont(oddFont); // create the rest of the rows int i = 1; while (i <= 100000) { Person person = new Person(); row = spreadsheet.createRow(i); cellA = row.createCell(0, CellType.STRING); cellB = row.createCell(1, CellType.NUMERIC); cellC = row.createCell(2, CellType.NUMERIC); cellA.setCellValue(person.getName()); cellB.setCellValue(person.getAge()); cellC.setCellValue(person.getScore()); if (i == 1) { cellE = row.createCell(4, CellType.FORMULA); cellE.setCellFormula("AVERAGE(C2:C101)"); } if (i % 2 == 0) { cellA.setCellStyle(oddRowStyle); cellB.setCellStyle(oddRowStyle); cellC.setCellStyle(oddRowStyle); } i++; } FileOutputStream out; try { out = new FileOutputStream(new File("scores.xlsx")); workbook.write(out); out.close(); } catch (IOException e) { e.printStackTrace(); } }
public static void setBeneficiairies(SetBeneficiaries setBenef) throws SQLException, IOException, NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, InvalidFormatException { // setBenef.getWb().setCurrentbeneficiaire(setBenef.getWb().getCurrentbeneficiaire()+1); setBenef.getMonitor() .subTask("Processing beneficiaries " + setBenef.getRs().getFirstname() + " " + setBenef.getRs().getName() + " : " + setBenef.getWb().getCurrentbeneficiaire() + " of " + setBenef.getWb().getCountbeneficiaire() + "..."); setBenef.setRow(setBenef.getSpreadsheet().createRow(setBenef.getIntrow())); SXSSFCell cell = null; int j = 0; // position Colonne A cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getPositioncrew() != null) { cell.setCellValue(setBenef.getRs().getPositioncrew()); } // Name Colonne B j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getName() != null) { cell.setCellValue(setBenef.getRs().getName()); } // first name Colonne C j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getFirstname() != null) { cell.setCellValue(setBenef.getRs().getFirstname()); } // structure name vessel Colonne D j++; cell = setBenef.getRow().createCell(j); String fstrutcurename = ""; if (setBenef.getRs().getStructurename() != null) { fstrutcurename = setBenef.getRs().getStructurename(); cell.setCellValue(setBenef.getRs().getStructurename()); } // crew manning agency Colonne j++; // periode de couverture Colonne E j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getPeriodeinsurance() != null) { cell.setCellValue(setBenef.getRs().getPeriodeinsurance()); } // Single Ou Family Colonne F j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getFamilycovered() != null) { cell.setCellValue(setBenef.getRs().getFamilycovered()); } // Nationalit�� Colonne G j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getNationality() != null) { cell.setCellValue(setBenef.getRs().getNationality()); } // Pays Colonne H j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getCountry() != null) { cell.setCellValue(setBenef.getRs().getCountry()); } // Nbre d'enfant Colonne I j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getChildren() != null) { cell.setCellValue(setBenef.getRs().getChildren()); } // Debut de mouvement Colonne J j++; cell = setBenef.getRow().createCell(j); CellStyle cellStyle; if (setBenef.getRs().getStartmovement() != null) { cell.setCellValue(setBenef.getRs().getStartmovement()); } cellStyle = setBenef.getNewworkbook().createCellStyle(); cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy")); cell.setCellStyle(cellStyle); // Fin de mouvement Colonne K j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getEndmovement() != null) { cell.setCellValue(setBenef.getRs().getEndmovement()); } cell.setCellStyle(cellStyle); // Salaire_Currency Colonne L j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getSalarycurrency() != null) { cell.setCellValue(setBenef.getRs().getSalarycurrency()); } // Nbre de mois Colonne M j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getMois() != null) { cell.setCellValue(setBenef.getRs().getMois()); } // Salaire Mensuel Colonne N j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getMonthlysalary() != null) { cell.setCellValue(setBenef.getRs().getMonthlysalary()); } // nbre de jour Colonne O j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getJour() != null) { cell.setCellValue(setBenef.getRs().getJour()); } // TO_INVOICE Colonne P j++; cell = setBenef.getRow().createCell(j); if (setBenef.getRs().getToinvoice() != null) { cell.setCellValue(setBenef.getRs().getToinvoice()); } setBenef.getWb().setXldformuleaggaregate(""); setBenef.setCompteur(1); // On traite les modules. on repete les cellules de formule pour le // nombre de module possible. for (int i = 1; i <= setBenef.getNbmodule(); i++) { Boolean proceed = false; // on recup��re certaine information du module en fonction des infos // du nom de la company, du nom du module, du nom de la formule et // de la couverure familliale Method fieldGetter = null; try { fieldGetter = setBenef.getRs().getClass().getMethod("getCompany" + i); } catch (NoSuchMethodException | SecurityException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } String fCompany = ""; try { if (fieldGetter.invoke(setBenef.getRs()) != null) { fCompany = fieldGetter.invoke(setBenef.getRs()).toString(); } } catch (IllegalAccessException | IllegalArgumentException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { fieldGetter = setBenef.getRs().getClass().getMethod("getFormule" + i); } catch (NoSuchMethodException | SecurityException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } String fFormule = ""; try { if (fieldGetter.invoke(setBenef.getRs()) != null) { fFormule = fieldGetter.invoke(setBenef.getRs()).toString(); } } catch (IllegalAccessException | IllegalArgumentException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { fieldGetter = setBenef.getRs().getClass().getMethod("getFormulename" + i); } catch (NoSuchMethodException | SecurityException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } String fFormulename = ""; try { if (fieldGetter.invoke(setBenef.getRs()) != null) { fFormulename = fieldGetter.invoke(setBenef.getRs()).toString(); } } catch (IllegalAccessException | IllegalArgumentException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } try { fieldGetter = setBenef.getRs().getClass().getMethod("getPolicenumber" + i); } catch (NoSuchMethodException | SecurityException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } String fpolicenumber = ""; try { if (fieldGetter.invoke(setBenef.getRs()) != null) { fpolicenumber = fieldGetter.invoke(setBenef.getRs()).toString(); } } catch (IllegalAccessException | IllegalArgumentException e1) { // TODO Auto-generated catch block e1.printStackTrace(); } String fFamilycovered = setBenef.getRs().getFamilycovered(); setBenef.setModul(setBenef.getWb().getBenefits(setBenef.getLasession(), fCompany, fFormule, fFormulename, fFamilycovered)); setBenef.setModulboat(setBenef.getWb().getboatBenefits(setBenef.getLasession(), fCompany, fFormule, fFormulename, fstrutcurename)); Float Amount; try { if (setBenef.getModul() != null) { // en fonction du mode de calcul on recup��re la valeur de Amount = setBenef.getModul().getModulprice();// Float.parseFloat(modul.getModulprice()); } else { Amount = 0f; } } catch (NumberFormatException e) { // TODO Auto-generated catch block // e.printStackTrace(); Amount = 0f; } // il faut recup��rer les aggregate. // si la valeur est diff��rente de z��ro on prend la valeur saisie // sion on prend la valeur calcul��e. String aggregate = setBenef.getWb().readaggregate(setBenef.getLasession(), fCompany, fFormule, fFormulename, fpolicenumber); if (setBenef.getModul() != null) { if (setBenef.getSpreadsheet().getSheetName().equals("Total WYCC")) { proceed = true; } else if (setBenef.getSpreadsheet().getSheetName().equals(fCompany)) { proceed = true; } else { proceed = false; } if (proceed) { Session mysession = setBenef.getLasession(); ModulBoat mymodulboat = setBenef.getModulboat(); if (mymodulboat != null) { String mycaclulmode = mymodulboat.getCalculmode(); setBenef.setResult(setBenef.getWb().readformula(mysession, mycaclulmode, 1)); } setBenef.getWb().setFormula(setBenef.getIntrow(), setBenef.getResult(), setBenef.getNewworkbook(), setBenef.getRow(), setBenef.getCompteur(), setBenef.getModulboat(), aggregate); setBenef.setCompteur(setBenef.getCompteur() + 1); } } } setBenef.getMonitor().worked(1); j = (setBenef.getWb().getStartColumnformule() + (setBenef.getWb().getOffsetColumn() * setBenef.getNbmodule())); cell = setBenef.getRow().createCell(j); int stringlength = setBenef.getWb().getXldformuleaggaregate().length(); if (stringlength > 0) { String form = setBenef.getWb().getXldformuleaggaregate().substring(0, stringlength - 1); setBenef.getWb().setXldformuleaggaregate(setBenef.getWb().getXldformuleaggaregate().substring(0, setBenef.getWb().getXldformuleaggaregate().length() - 1)); } // xldformuleaggaregate=xldformuleaggaregate.substring(0, // xldformuleaggaregate.length()-1); logger.info(setBenef.getWb().getXldformuleaggaregate()); cell.setCellFormula(setBenef.getWb().getXldformuleaggaregate()); setBenef.setLastcellule(cell); if (setBenef.getIntrow() == 4) { setBenef.setFirstcellul(cell); setBenef.setAddressfirstcell("" + setBenef.getFirstcellul().getAddress()); } // Check if the user pressed "cancel" if (setBenef.getMonitor().isCanceled()) { setBenef.getMonitor().done(); return; } // setBenef.setIntrow(setBenef.getIntrow() +1); }
@Override public void write(Object newDAO) throws OfficeWriterException { SpreadSheetCellDAO sscd = MSExcelWriter.checkSpreadSheetCellDAO(newDAO); String safeSheetName=WorkbookUtil.createSafeSheetName(sscd.getSheetName()); SXSSFSheet currentSheet=this.currentWorkbook.getSheet(safeSheetName); if (currentSheet==null) {// create sheet if it does not exist yet currentSheet=this.currentWorkbook.createSheet(safeSheetName); if (!(safeSheetName.equals(sscd.getSheetName()))) { LOG.warn("Sheetname modified from \""+sscd.getSheetName()+"\" to \""+safeSheetName+"\" to correspond to Excel conventions."); } // create drawing anchor (needed for comments...) this.mappedDrawings.put(safeSheetName,currentSheet.createDrawingPatriarch()); } // check if cell exist CellAddress currentCA = new CellAddress(sscd.getAddress()); SXSSFRow currentRow = currentSheet.getRow(currentCA.getRow()); if (currentRow==null) { // row does not exist? => create it currentRow=currentSheet.createRow(currentCA.getRow()); } SXSSFCell currentCell = currentRow.getCell(currentCA.getColumn()); if ((currentCell!=null)) { // cell already exists and no template loaded ? => throw exception throw new OfficeWriterException("Invalid cell specification: cell already exists at "+currentCA); } // create cell if no template is loaded or cell not available in template currentCell=currentRow.createCell(currentCA.getColumn()); // set the values accordingly if (!("".equals(sscd.getFormula()))) { // if formula exists then use formula currentCell.setCellFormula(sscd.getFormula()); } else { // else use formattedValue currentCell.setCellValue(sscd.getFormattedValue()); } // set comment if ((sscd.getComment()!=null) && (!("".equals(sscd.getComment())))) { /** the following operations are necessary to create comments **/ /** Define size of the comment window **/ ClientAnchor anchor = this.currentWorkbook.getCreationHelper().createClientAnchor(); anchor.setCol1(currentCell.getColumnIndex()); anchor.setCol2(currentCell.getColumnIndex()+this.howc.getCommentWidth()); anchor.setRow1(currentRow.getRowNum()); anchor.setRow2(currentRow.getRowNum()+this.howc.getCommentHeight()); /** create comment **/ Comment currentComment = mappedDrawings.get(safeSheetName).createCellComment(anchor); currentComment.setString(this.currentWorkbook.getCreationHelper().createRichTextString(sscd.getComment())); currentComment.setAuthor(this.howc.getCommentAuthor()); currentCell.setCellComment(currentComment); } }
Usage snippet has been bookmarked! Review your bookmarks
Thank you! Review your likes