List of usage examples for org.apache.poi.xssf.usermodel XSSFCell setCellFormula
@Override public final void setCellFormula(String formula) throws FormulaParseException, IllegalStateException
From source file:tan.jam.jsf.Shifting.java
public static void InsertRow(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); XSSFRow newRow = worksheet.getRow(destinationRowNum); XSSFRow sourceRow = worksheet.getRow(sourceRowNum); if (newRow != null) { worksheet.shiftRows(destinationRowNum, worksheet.getLastRowNum(), 1); } else {/*w ww.ja va 2 s . com*/ newRow = worksheet.createRow(destinationRowNum); } for (int i = 0; i < sourceRow.getLastCellNum(); i++) { // Grab a copy of the old/new cell XSSFCell oldCell = sourceRow.getCell(i); XSSFCell newCell = newRow.createCell(i); if (oldCell == null) { newCell = null; continue; } XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); switch (oldCell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: newCell.setCellFormula("+" + "F" + destinationRowNum + "*G" + destinationRowNum); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: //newCell.setCellValue(oldCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: newCell.setCellValue(""); break; } } for (int i = 0; i < worksheet.getNumMergedRegions(); i++) { CellRangeAddress cellRangeAddress = worksheet.getMergedRegion(i); if (cellRangeAddress.getFirstRow() == sourceRow.getRowNum()) { CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.getRowNum(), (newRow.getRowNum() + (cellRangeAddress.getLastRow() - cellRangeAddress.getFirstRow())), cellRangeAddress.getFirstColumn(), cellRangeAddress.getLastColumn()); worksheet.addMergedRegion(newCellRangeAddress); } } int inc = destinationRowNum + 1; worksheet.getRow(destinationRowNum).getCell(7).setCellFormula("+F" + inc + "*G" + inc); }
From source file:tan.jam.jsf.Shifting.java
private static void CopyData(XSSFWorkbook workbook, XSSFSheet worksheet, int sourceRowNum, int destinationRowNum, int Mov) { XSSFRow newRow = worksheet.getRow(destinationRowNum); XSSFRow sourceRow = worksheet.getRow(sourceRowNum); for (int i = sourceRow.getLastCellNum(); i > 8 + Mov; i--) { int d = i - 1; XSSFCell oldCell = sourceRow.getCell(d); XSSFCell newCell = newRow.createCell(i); if (oldCell == null) { newCell = null;/*from w ww .j a va 2 s .c o m*/ continue; } XSSFCellStyle newCellStyle = workbook.createCellStyle(); newCellStyle.cloneStyleFrom(oldCell.getCellStyle()); ; newCell.setCellStyle(newCellStyle); if (oldCell.getCellComment() != null) { newCell.setCellComment(oldCell.getCellComment()); } if (oldCell.getHyperlink() != null) { newCell.setHyperlink(oldCell.getHyperlink()); } newCell.setCellType(oldCell.getCellType()); switch (oldCell.getCellType()) { case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BLANK: newCell.setCellValue(oldCell.getStringCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; } } }
From source file:uk.ac.bbsrc.tgac.miso.spring.util.FormUtils.java
License:Open Source License
public static void createSampleExportForm(File outpath, JSONArray jsonArray) throws Exception { InputStream in = null;/*from w w w. ja v a 2 s . c om*/ in = FormUtils.class.getResourceAsStream("/forms/ods/export_samples.xlsx"); if (in != null) { XSSFWorkbook oDoc = new XSSFWorkbook(in); XSSFSheet sheet = oDoc.getSheet("samples_export"); FileOutputStream fileOut = new FileOutputStream(outpath); int i = 5; for (JSONObject jsonObject : (Iterable<JSONObject>) jsonArray) { if ("sampleinwell".equals(jsonObject.getString("name"))) { String sampleinwell = jsonObject.getString("value"); // "sampleid:wellid:samplealias:projectname:projectalias:dnaOrRNA" String sampleId = sampleinwell.split(":")[0]; String wellId = sampleinwell.split(":")[1]; String sampleAlias = sampleinwell.split(":")[2]; String projectName = sampleinwell.split(":")[3]; String projectAlias = sampleinwell.split(":")[4]; String dnaOrRNA = sampleinwell.split(":")[5]; XSSFRow row = sheet.createRow(i); XSSFCell cellA = row.createCell(0); cellA.setCellValue(projectName); XSSFCell cellB = row.createCell(1); cellB.setCellValue(projectAlias); XSSFCell cellC = row.createCell(2); cellC.setCellValue(sampleId); XSSFCell cellD = row.createCell(3); cellD.setCellValue(sampleAlias); XSSFCell cellE = row.createCell(4); cellE.setCellValue(wellId); XSSFCell cellG = row.createCell(6); XSSFCell cellH = row.createCell(7); XSSFCell cellI = row.createCell(8); XSSFCell cellL = row.createCell(11); if ("R".equals(dnaOrRNA)) { cellG.setCellValue("NA"); cellL.setCellFormula("1000/H" + (i + 1)); } else if ("D".equals(dnaOrRNA)) { cellH.setCellValue("NA"); cellI.setCellValue("NA"); cellL.setCellFormula("1000/G" + (i + 1)); } XSSFCell cellM = row.createCell(12); cellM.setCellFormula("50-L" + (i + 1)); i++; } } oDoc.write(fileOut); fileOut.close(); } else { throw new IOException("Could not read from resource."); } }
From source file:vd10_workbook.AssignedVoteManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("phieu_phan_cong"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Phiu phn cng"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 4 //last column (0-based) );//from ww w.j a v a 2 s. c o m sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //Ngy bt u sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("Ngy bt u"); AbilityManagement.setThickBorder(cell, workbook); //S ngy sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("S ngy"); AbilityManagement.setThickBorder(cell, workbook); //ID nhn vin sheet.setColumnWidth(3, 5000); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue("ID nhn vin"); AbilityManagement.setThickBorder(cell, workbook); //ID loi cng vic sheet.setColumnWidth(4, 5000); cell = (XSSFCell) row.createCell((short) 4); cell.setCellValue("ID loi cng vic"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getStartDate().toString()); cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getNumberOfdate()); cell = (XSSFCell) row.createCell((short) 3); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getEmployeeID()); cell = (XSSFCell) row.createCell((short) 4); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getWorkTypeID()); } //== calculate sum of days ==// row = sheet.createRow((short) startRow + 2 + this.list.size()); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Tng"); //merge cells at column 1 & 2 cellRangeAddress = new CellRangeAddress(startRow + 2 + this.list.size(), //first row (0-based) startRow + 2 + this.list.size(), //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) ); sheet.addMergedRegion(cellRangeAddress); //set border RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderRight(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderTop(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); cell = row.createCell((short) 2); cell.setCellFormula("SUM(C" + (startRow + 1 + 2) + ":C" + (startRow + 2 + this.list.size()) + ")"); AbilityManagement.setThickBorder(cell, workbook); }
From source file:vd10_workbook.EmployeeManagement.java
public void createWorkSheet(XSSFWorkbook workbook) { XSSFSheet sheet = workbook.createSheet("ds_nhan_vien"); int startRow = 0; XSSFRow row = sheet.createRow((short) startRow); //== THE TITLE ==// //SET HEIGHT OF ROW 2 (in excel) row.setHeight((short) 500); XSSFCell cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("Danh sch nhn vin"); //MEARGING CELLS //this statement for merging cells CellRangeAddress cellRangeAddress = new CellRangeAddress(startRow, //first row (0-based) startRow, //last row (0-based) 0, //first column (0-based) 7 //last column (0-based) );// w ww. j av a 2 s . com sheet.addMergedRegion(cellRangeAddress); // Center Align Cell Contents XSSFCellStyle align = workbook.createCellStyle(); align.setAlignment(XSSFCellStyle.ALIGN_CENTER); align.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER); cell.setCellStyle(align); //set border AbilityManagement.setRegionBoder(cellRangeAddress, workbook, sheet); //==THE LABELS ==// //STT row = sheet.createRow((short) startRow + 1); row.setHeight((short) 400); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("STT"); AbilityManagement.setThickBorder(cell, workbook); //H? tn sheet.setColumnWidth(1, 5000); cell = (XSSFCell) row.createCell((short) 1); cell.setCellValue("H? tn"); AbilityManagement.setThickBorder(cell, workbook); //Gii tnh sheet.setColumnWidth(2, 5000); cell = (XSSFCell) row.createCell((short) 2); cell.setCellValue("Gii tnh"); AbilityManagement.setThickBorder(cell, workbook); //Ngy sinh sheet.setColumnWidth(3, 5000); cell = (XSSFCell) row.createCell((short) 3); cell.setCellValue("Ngy sinh"); AbilityManagement.setThickBorder(cell, workbook); //CMND sheet.setColumnWidth(4, 5000); cell = (XSSFCell) row.createCell((short) 4); cell.setCellValue("CMND"); AbilityManagement.setThickBorder(cell, workbook); //Mc lng sheet.setColumnWidth(5, 5000); cell = (XSSFCell) row.createCell((short) 5); cell.setCellValue("Mc lng"); AbilityManagement.setThickBorder(cell, workbook); //?a ch sheet.setColumnWidth(6, 10000); cell = (XSSFCell) row.createCell((short) 6); cell.setCellValue("?a ch"); AbilityManagement.setThickBorder(cell, workbook); //?n v sheet.setColumnWidth(7, 2000); cell = (XSSFCell) row.createCell((short) 7); cell.setCellValue("?n v"); AbilityManagement.setThickBorder(cell, workbook); //fill out the rows for (int i = 0; i < this.list.size(); i++) { row = sheet.createRow((short) startRow + 2 + i); cell = (XSSFCell) row.createCell((short) 0); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(i + 1); cell = (XSSFCell) row.createCell((short) 1); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getName().toString()); cell = (XSSFCell) row.createCell((short) 2); AbilityManagement.setThinBorder(cell, workbook); if (this.list.get(i).getSex() == 1) { cell.setCellValue("Nam"); } else { cell.setCellValue("N"); } cell = (XSSFCell) row.createCell((short) 3); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getDateOfbirth().toString()); cell = (XSSFCell) row.createCell((short) 4); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getIdentityNumber().toString()); cell = (XSSFCell) row.createCell((short) 5); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getSalary()); cell = (XSSFCell) row.createCell((short) 6); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getAddress()); cell = (XSSFCell) row.createCell((short) 7); AbilityManagement.setThinBorder(cell, workbook); cell.setCellValue(this.list.get(i).getDepartmentID()); } //== calculate sum of days ==// row = sheet.createRow((short) startRow + 2 + this.list.size()); cell = (XSSFCell) row.createCell((short) 0); cell.setCellValue("S nhn vin c lng >= 6tr "); //merge cells at column 1 & 2 cellRangeAddress = new CellRangeAddress(startRow + 2 + this.list.size(), //first row (0-based) startRow + 2 + this.list.size(), //last row (0-based) 0, //first column (0-based) 1 //last column (0-based) ); sheet.addMergedRegion(cellRangeAddress); //set border RegionUtil.setBorderBottom(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderRight(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderTop(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); RegionUtil.setBorderLeft(XSSFCellStyle.BORDER_MEDIUM, cellRangeAddress, sheet, workbook); cell = row.createCell((short) 2); cell.setCellFormula( "COUNTIF(F" + (startRow + 1 + 2) + ":F" + (startRow + 2 + this.list.size()) + ",\">= 6000000\")"); AbilityManagement.setThickBorder(cell, workbook); }