List of usage examples for org.apache.poi.ss.usermodel Cell setCellFormula
void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;
From source file:org.unhcr.eg.odk.utilities.xlsform.excel.ExcelFileUtility.java
protected static void copyContent(Sheet sheetSource, Sheet sheetDestination) { //Iterate through each rows from first sheet Iterator<Row> rowIterator = sheetSource.iterator(); int i = 0;// w w w . j a v a 2 s . c o m while (rowIterator.hasNext()) { Row row = rowIterator.next(); Row rowDestination = sheetDestination.createRow(i); i++; //For each row, iterate through each columns Iterator<Cell> cellIterator = row.cellIterator(); int j = 0; while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); Cell cellDestination = rowDestination.createCell(j); j++; cellDestination.setCellComment(cell.getCellComment()); // cellDestination.setCellStyle(cell.getCellStyle()); cellDestination.setCellType(cell.getCellType()); switch (cell.getCellType()) { case Cell.CELL_TYPE_BOOLEAN: cellDestination.setCellValue(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_NUMERIC: if (DateUtil.isCellDateFormatted(cell)) { cellDestination.setCellValue(cell.getDateCellValue()); } else { cellDestination.setCellValue(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: cellDestination.setCellValue(cell.getRichStringCellValue()); break; case Cell.CELL_TYPE_BLANK: cellDestination.setCellValue(cell.getStringCellValue()); break; case Cell.CELL_TYPE_ERROR: cellDestination.setCellValue(cell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: cellDestination.setCellFormula(cell.getCellFormula()); break; } } } }
From source file:output.ExcelM3Upgrad.java
private void writeMigration() { Sheet sheet = workbook.getSheetAt(0); workbook.setSheetName(0, "Migration"); sheet.setDisplayGridlines(false);//from w w w . j a v a2 s.c o m sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); styles = createStyles(workbook); int rownum = beginROW; int cellnum = beginCOL; Row row = sheet.createRow(rownum++); for (int k = 0; k < model.getListColumn().length; k++) { Cell cell = row.createCell(cellnum++); cell.setCellValue(i18n.Language.getLabel(model.getListColumn()[k].getIdLng())); cell.setCellStyle(styles.get("header")); sheet.setColumnHidden(cell.getColumnIndex(), model.getListColumn()[k].isHidden()); sheet.autoSizeColumn(k); dialStatus(); } ArrayList<Integer> listHeader = new ArrayList<>(); for (int i = 0; i < M3UpdObjModel.header.length; i++) { listHeader.add(M3UpdObjModel.header[i]); } String[] listLevel = i18n.Language.traduce(Ressource.listLevel) .toArray(new String[Ressource.listLevel.length]); data = model.getData(); for (int i = 0; i < data.length; i++) { busyDial.setText("Alimentation de la ligne " + (i + 1) + " sur " + data.length); row = sheet.createRow(rownum++); Object[] objArr = data[i]; cellnum = beginCOL; boolean first = true; int j = 0; for (Object obj : objArr) { Cell cell = row.createCell(cellnum++); if (obj instanceof Date) { cell.setCellValue((Date) obj); } else if (obj instanceof Boolean) { if (first) { first = false; if ((Boolean) obj) { cell.setCellValue("Oui"); } else { cell.setCellValue("Non"); } } else { if ((Boolean) obj) { cell.setCellValue("OK"); } else { cell.setCellValue("KO"); } } } else if (obj instanceof String) { cell.setCellValue((String) obj); } else if (obj instanceof Double) { cell.setCellValue((Double) obj); } if (listHeader.indexOf(218) == j) { try { int n = Integer.parseInt(obj.toString().trim()); if (n == -1) { cell.setCellValue("ERROR"); } else { cell.setCellValue(listLevel[n]); } } catch (NumberFormatException ex) { cell.setCellValue(""); } } if (j < objArr.length - 3) { cell.setCellStyle(styles.get("cell_b_centered_locked")); } else { cell.setCellStyle(styles.get("cell_b_centered")); } j++; dialStatus(); } dialStatus(); } dialStatus(); busyDial.setText("Formatage du document"); CellRangeAddressList userList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 1, beginCOL + data[0].length - 1); DataValidationConstraint userConstraint; DataValidation userValidation; if (type == 0) { userConstraint = DVConstraint.createExplicitListConstraint((String[]) model.getM3UserModel() .getListUserSelect().toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = new HSSFDataValidation(userList, userConstraint); } else { XSSFDataValidationHelper userHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); userConstraint = (XSSFDataValidationConstraint) userHelper .createExplicitListConstraint((String[]) model.getM3UserModel().getListUserSelect() .toArray(new String[model.getM3UserModel().getListUserSelect().size()])); userValidation = (XSSFDataValidation) userHelper.createValidation(userConstraint, userList); } sheet.addValidationData(userValidation); CellRangeAddressList migList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 2, beginCOL + data[0].length - 2); DataValidationConstraint migConstraint; DataValidation migValidation; if (type == 0) { migConstraint = DVConstraint.createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = new HSSFDataValidation(migList, migConstraint); } else { XSSFDataValidationHelper migHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); migConstraint = (XSSFDataValidationConstraint) migHelper .createExplicitListConstraint(new String[] { "OK", "KO" }); migValidation = (XSSFDataValidation) migHelper.createValidation(migConstraint, migList); } sheet.addValidationData(migValidation); CellRangeAddressList levelList = new CellRangeAddressList(beginROW + 1, beginROW + data.length, beginCOL + data[0].length - 3, beginCOL + data[0].length - 3); DataValidationConstraint levelConstraint; DataValidation levelValidation; ArrayList<String> listNameLevel = new ArrayList<>(); listNameLevel.add("ERROR"); listNameLevel.addAll(i18n.Language.traduce(Ressource.listLevel));//.toArray(new String[Ressource.listLevel.length]) if (type == 0) { levelConstraint = DVConstraint .createExplicitListConstraint(listNameLevel.toArray(new String[listNameLevel.size()])); levelValidation = new HSSFDataValidation(levelList, levelConstraint); } else { XSSFDataValidationHelper levelHelper = new XSSFDataValidationHelper((XSSFSheet) sheet); levelConstraint = (XSSFDataValidationConstraint) levelHelper.createExplicitListConstraint( i18n.Language.traduce(Ressource.listLevel).toArray(new String[Ressource.listLevel.length])); levelValidation = (XSSFDataValidation) levelHelper.createValidation(levelConstraint, levelList); } sheet.addValidationData(levelValidation); int irow = beginROW; int icol = beginCOL + model.getListColumn().length + 2; row = sheet.getRow(irow); Cell cell = row.createCell(icol); sheet.addMergedRegion(new CellRangeAddress(irow, irow, icol, icol + 1)); cell.setCellValue("Estimation de la charge"); cell.setCellStyle(styles.get("header")); irow++; row = sheet.getRow(irow); int cpt = 0; ArrayList<String> listStringLevel = i18n.Language.traduce(Ressource.listLevel); for (String s : listStringLevel) { cell = row.createCell(icol); cell.setCellValue(s); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(218) + beginCOL); cell.setCellFormula( "COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + s + "\")*" + Ressource.listWeightLevel[cpt]); cell.setCellStyle(styles.get("cell_b_centered_locked")); irow++; row = sheet.getRow(irow); cpt++; } row = sheet.getRow(irow); cell = row.createCell(icol); cell.setCellValue("Total des charges"); cell.setCellStyle(styles.get("cell_b_centered_locked")); cell = row.createCell(icol + 1); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(listHeader.indexOf(icol + 1)); cell.setCellFormula("SUM(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + Ressource.listLevel.length + 1) + ")"); cell.setCellStyle(styles.get("cell_b_centered_locked")); for (int k = 0; k < model.getListColumn().length + 3; k++) { sheet.autoSizeColumn(k); } sheet.protectSheet("3kles2014"); }
From source file:output.ExcelM3Upgrad.java
private void writeGraph() { busyDial.setText("Gnration des graphiques statistiques"); Sheet s = workbook.getSheetAt(1);//from ww w . ja v a 2 s . c o m workbook.setSheetName(1, "Statistiques"); ArrayList<Integer> listHeader = new ArrayList<>(); for (int i = 0; i < M3UpdObjModel.header.length; i++) { listHeader.add(M3UpdObjModel.header[i]); } int irow = 4; Row row = s.createRow(irow); Cell cell = row.createCell(2); s.addMergedRegion(new CellRangeAddress(irow, irow, 2, 7)); cell.setCellValue("Rpartition des spcifiques"); cell.setCellStyle(styles.get("cell_centered_locked")); irow = 8; row = s.createRow(irow); for (int i = 0; i < com.app.main.Ressource.listTypeM3Entity.length; i++) { cell = row.createCell(3); cell.setCellValue(com.app.main.Ressource.listTypeM3Entity[i]); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(beginCOL); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + com.app.main.Ressource.listTypeM3Entity[i] + "\")"); irow++; row = s.createRow(irow); dialStatus(); } irow = 4; row = s.getRow(irow); cell = row.createCell(10); s.addMergedRegion(new CellRangeAddress(irow, irow, 10, 15)); cell.setCellValue("Existance des sources"); cell.setCellStyle(styles.get("cell_centered_locked")); int posVal = listHeader.indexOf(199); posVal += beginCOL; irow = 8; row = s.getRow(irow); cell = row.createCell(12); cell.setCellValue("OK"); cell = row.createCell(13); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnLetter = CellReference.convertNumToColString(posVal); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "Oui" + "\")"); irow++; row = s.getRow(irow); cell = row.createCell(12); cell.setCellValue("NOK"); cell = row.createCell(13); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); // columnLetter = CellReference.convertNumToColString(posVal); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "Non" + "\")"); irow = 24; row = s.createRow(irow); cell = row.createCell(2); s.addMergedRegion(new CellRangeAddress(irow, irow, 2, 7)); cell.setCellValue("Synthse de migration"); cell.setCellStyle(styles.get("cell_centered_locked")); int posMig = listHeader.indexOf(201); posMig += beginCOL; int posUser = listHeader.indexOf(202); posUser += beginCOL; irow = 28; row = s.createRow(irow); cell = row.createCell(3); cell.setCellValue("OK+USER"); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String columnMig = CellReference.convertNumToColString(posMig); String columnUser = CellReference.convertNumToColString(posUser); cell.setCellFormula("SUMPRODUCT((" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":" + columnMig + (beginROW + data.length + 1) + "=\"" + "OK" + "\")*(" + workbook.getSheetName(0) + "!" + columnUser + (beginROW + 2) + ":" + columnUser + (beginROW + data.length + 1) + "<>\"" + "" + "\"))"); irow++; row = s.createRow(irow); cell = row.createCell(3); cell.setCellValue("OK"); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("SUMPRODUCT((" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":" + columnMig + (beginROW + data.length + 1) + "=\"" + "OK" + "\")*(" + workbook.getSheetName(0) + "!" + columnUser + (beginROW + 2) + ":" + columnUser + (beginROW + data.length + 1) + "=\"" + "" + "\"))"); irow++; row = s.createRow(irow); cell = row.createCell(3); cell.setCellValue("NOK"); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnMig + (beginROW + 2) + ":" + columnMig + (beginROW + data.length + 1) + ",\"KO\")"); irow++; row = s.createRow(irow); cell = row.createCell(3); cell.setCellValue("Somme"); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); String posSum = CellReference.convertNumToColString(4); cell.setCellFormula("SUM(" + posSum + (irow - 2) + ":" + posSum + (irow) + ")"); posVal = listHeader.indexOf(217); posVal += beginCOL; irow = 24; row = s.getRow(irow); cell = row.createCell(10); s.addMergedRegion(new CellRangeAddress(irow, irow, 10, 15)); cell.setCellValue("Analyse des objets instanciables"); cell.setCellStyle(styles.get("cell_centered_locked")); irow = 28; row = s.getRow(irow); cell = row.createCell(12); cell.setCellValue("Class OK"); cell = row.createCell(13); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); columnLetter = CellReference.convertNumToColString(posVal); cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "" + "\")"); irow++; row = s.getRow(irow); cell = row.createCell(12); cell.setCellValue("Class NOK"); cell = row.createCell(13); cell.setCellType(HSSFCell.CELL_TYPE_FORMULA); columnLetter = CellReference.convertNumToColString(posVal); //cell.setCellFormula("COUNTIF(" + workbook.getSheetName(0) + "!" + columnLetter + (beginROW + 2) + ":" + columnLetter + (beginROW + data.length + 1) + ",\"" + "<>" + "\"&\"" + "*" + "\")"); cell.setCellFormula("E32-N29"); s.protectSheet("3kles2014"); }
From source file:packtest.CreateCell.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new XSSFWorkbook(); //or new HSSFWorkbook(); CreationHelper creationHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet("new sheet"); // Create a row and put some cells in it. Rows are 0 based. Row row = sheet.createRow((short) 0); // Create a cell and put a value in it. Cell cell = row.createCell((short) 0); cell.setCellValue(1);/*w w w.j a v a 2 s.c o m*/ //numeric value row.createCell(1).setCellValue(1.2); //plain string value row.createCell(2).setCellValue("This is a string cell"); //rich text string RichTextString str = creationHelper.createRichTextString("Apache"); Font font = wb.createFont(); font.setItalic(true); font.setUnderline(Font.U_SINGLE); str.applyFont(font); row.createCell(3).setCellValue(str); //boolean value row.createCell(4).setCellValue(true); //formula row.createCell(5).setCellFormula("SUM(A1:B1)"); //date CellStyle style = wb.createCellStyle(); style.setDataFormat(creationHelper.createDataFormat().getFormat("m/d/yy h:mm")); cell = row.createCell(6); cell.setCellValue(new Date()); cell.setCellStyle(style); //hyperlink row.createCell(7).setCellFormula("SUM(A1:B1)"); cell.setCellFormula("HYPERLINK(\"http://google.com\",\"Google\")"); // Write the output to a file FileOutputStream fileOut = new FileOutputStream("ooxml-cell.xlsx"); wb.write(fileOut); fileOut.close(); }
From source file:project1.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//ww w.jav a 2 s .c o m // if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); // else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:ro.dabuno.office.integration.BusinessPlan.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from ww w . ja va 2s.c om if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Business Plan"); //turn off gridlines sheet.setDisplayGridlines(false); sheet.setPrintGridlines(false); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); //the following three statements are required only for HSSF sheet.setAutobreaks(true); printSetup.setFitHeight((short) 1); printSetup.setFitWidth((short) 1); //the header row: centered text in 48pt font Row headerRow = sheet.createRow(0); headerRow.setHeightInPoints(12.75f); for (int i = 0; i < titles.length; i++) { Cell cell = headerRow.createCell(i); cell.setCellValue(titles[i]); cell.setCellStyle(styles.get("header")); } //columns for 11 weeks starting from 9-Jul Calendar calendar = Calendar.getInstance(); int year = calendar.get(Calendar.YEAR); calendar.setTime(fmt.parse("9-Jul")); calendar.set(Calendar.YEAR, year); for (int i = 0; i < 11; i++) { Cell cell = headerRow.createCell(titles.length + i); cell.setCellValue(calendar); cell.setCellStyle(styles.get("header_date")); calendar.roll(Calendar.WEEK_OF_YEAR, true); } //freeze the first row sheet.createFreezePane(0, 1); Row row; Cell cell; int rownum = 1; for (int i = 0; i < data.length; i++, rownum++) { row = sheet.createRow(rownum); if (data[i] == null) continue; for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); String styleName; boolean isHeader = i == 0 || data[i - 1] == null; switch (j) { case 0: if (isHeader) { styleName = "cell_b"; cell.setCellValue(Double.parseDouble(data[i][j])); } else { styleName = "cell_normal"; cell.setCellValue(data[i][j]); } break; case 1: if (isHeader) { styleName = i == 0 ? "cell_h" : "cell_bb"; } else { styleName = "cell_indented"; } cell.setCellValue(data[i][j]); break; case 2: styleName = isHeader ? "cell_b" : "cell_normal"; cell.setCellValue(data[i][j]); break; case 3: styleName = isHeader ? "cell_b_centered" : "cell_normal_centered"; cell.setCellValue(Integer.parseInt(data[i][j])); break; case 4: { calendar.setTime(fmt.parse(data[i][j])); calendar.set(Calendar.YEAR, year); cell.setCellValue(calendar); styleName = isHeader ? "cell_b_date" : "cell_normal_date"; break; } case 5: { int r = rownum + 1; String fmla = "IF(AND(D" + r + ",E" + r + "),E" + r + "+D" + r + ",\"\")"; cell.setCellFormula(fmla); styleName = isHeader ? "cell_bg" : "cell_g"; break; } default: styleName = data[i][j] != null ? "cell_blue" : "cell_normal"; } cell.setCellStyle(styles.get(styleName)); } } //group rows for each phase, row numbers are 0-based sheet.groupRow(4, 6); sheet.groupRow(9, 13); sheet.groupRow(16, 18); //set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 256 * 6); sheet.setColumnWidth(1, 256 * 33); sheet.setColumnWidth(2, 256 * 20); sheet.setZoom(75); //75% scale // Write the output to a file String file = "businessplan.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); wb.close(); }
From source file:savio_estadisticas.clases.Control.Estadisticas.Table_DataBase.java
public void GenerateStatistis(Workbook libro, int total, int Ninguno) { Sheet estadisticas = libro.createSheet("Estadisticas"); for (int i = 0; i < 22; i++) { Row fila_esta = estadisticas.createRow(i); for (int j = 0; j < 4; j++) { Cell celda_esta = fila_esta.createCell(j); switch (i) { case 0: switch (j) { case 0: celda_esta.setCellValue("Recurso"); break; case 1: celda_esta.setCellValue("Cursos"); break; case 2: celda_esta.setCellValue("Promedio (%)"); break; case 3: celda_esta.setCellValue("Total Cursos"); break; }/*from w ww .j a v a 2s . c o m*/ break; case 1: switch (j) { case 0: celda_esta.setCellValue("Tareas"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!D:D,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 2: switch (j) { case 0: celda_esta.setCellValue("Consultas"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!E:E,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 3: switch (j) { case 0: celda_esta.setCellValue("Etiquetas"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!F:F,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 4: switch (j) { case 0: celda_esta.setCellValue("Foros"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!G:G,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 5: switch (j) { case 0: celda_esta.setCellValue("Chats"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!H:H,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 6: switch (j) { case 0: celda_esta.setCellValue("Lecciones"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!I:I,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 7: switch (j) { case 0: celda_esta.setCellValue("Wikis"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!J:J,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 8: switch (j) { case 0: celda_esta.setCellValue("Bases de Datos"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!K:K,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 9: switch (j) { case 0: celda_esta.setCellValue("Paquetes SCORM"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!L:L,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 10: switch (j) { case 0: celda_esta.setCellValue("Archivos"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!M:M,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 11: switch (j) { case 0: celda_esta.setCellValue("URLs"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!N:N,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 12: switch (j) { case 0: celda_esta.setCellValue("Paginas"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!O:O,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 13: switch (j) { case 0: celda_esta.setCellValue("Cuestionarios"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!P:P,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 14: switch (j) { case 0: celda_esta.setCellValue("Talleres"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!Q:Q,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 15: switch (j) { case 0: celda_esta.setCellValue("VPL"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!R:R,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 16: switch (j) { case 0: celda_esta.setCellValue("Libros"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!S:S,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 17: switch (j) { case 0: celda_esta.setCellValue("Glosario"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!T:T,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 18: switch (j) { case 0: celda_esta.setCellValue("Portafolio"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!U:U,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 19: switch (j) { case 0: celda_esta.setCellValue("Innovadores"); break; case 1: celda_esta.setCellFormula("COUNTIF(Tabla!V:V,\">0\")"); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; case 20: switch (j) { case 0: celda_esta.setCellValue("Ninguno"); break; case 1: celda_esta.setCellValue(Ninguno); break; case 2: celda_esta.setCellFormula("B" + (i + 1) + "/D2"); CellStyle style = libro.createCellStyle(); style.setDataFormat(libro.createDataFormat().getFormat("0.0%")); celda_esta.setCellStyle(style); break; case 3: celda_esta.setCellValue(total); break; } break; } } } //celda_esta.setCellFormula("COUNTIF(Tabla!S:S,\">0\")"); }
From source file:servlet.exportScoreSheet.java
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> * methods.//from www . j a va2 s . c o m * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { HttpSession ss = request.getSession(); Account ac = (Account) ss.getAttribute("ac"); int cId = Integer.parseInt((Long) ss.getAttribute("cId") + ""); Course c = Course.getCourseByID(cId); Workbook wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("scoresheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Score sheet of " + c.getName() + " course"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); List<Account> listStudentScore = (List<Account>) ss.getAttribute("listStudentScore"); int rownum = 2; int cellcount = 1; Row sumRow = sheet.createRow(rownum); sumRow.setHeightInPoints(55); Cell cell; cell = sumRow.createCell(0); cell.setCellValue("Student name"); cell.setCellStyle(styles.get("header")); int countback = listStudentScore.get(0).getListStudentScore().size(); int maxScore = 0; for (int i = countback - 1; i >= 0; i--) { cell = sumRow.createCell(cellcount); UserScore u = listStudentScore.get(0).getListStudentScore().get(i); cell.setCellValue("(" + cellcount + ") " + u.getAm_name() + " (" + u.getFull_mark() + ")"); cell.setCellStyle(styles.get("header")); cellcount++; maxScore += u.getFull_mark(); } cell = sumRow.createCell(cellcount); cell.setCellValue("Total (" + maxScore + ")"); cell.setCellStyle(styles.get("header")); rownum++; for (Account account : listStudentScore) { sumRow = sheet.createRow(rownum); sumRow.setHeightInPoints(35); cell = sumRow.createCell(0); cell.setCellValue(account.getFirstname() + " " + account.getLastname()); int j = 1; for (int i = account.getListStudentScore().size() - 1; i >= 0; i--) { UserScore usc = (UserScore) account.getListStudentScore().get(i); cell = sumRow.createCell(j); Assignment a = null; if (usc.getAss_type().equalsIgnoreCase("web")) { a = Assignment.getAmTimeByAmID(usc.getStof().getAm_id()); String status = Assignment.lastedSentStatus(usc.getStof().getLasted_send_date(), a); if (status.equalsIgnoreCase("ontime") || status.equalsIgnoreCase("hurryup") || status.equalsIgnoreCase("late")) { cell.setCellValue(usc.getStof().getScore()); } else { status = Assignment.calculateTime(a); if (status.equalsIgnoreCase("miss")) { cell.setCellValue(usc.getStof().getScore()); } else { cell.setCellValue("-"); } } } else if (usc.getAss_type().equalsIgnoreCase("file")) { a = Assignment.getAmTimeByAmID(usc.getStf().getAm_id()); String status = Assignment.lastedSentStatus(usc.getStf().getLasted_send_date(), a); if (status.equalsIgnoreCase("ontime") || status.equalsIgnoreCase("hurryup") || status.equalsIgnoreCase("late")) { cell.setCellValue(usc.getStf().getScore()); } else { status = Assignment.calculateTime(a); if (status.equalsIgnoreCase("miss")) { cell.setCellValue(usc.getStf().getScore()); } else { cell.setCellValue("-"); } } } j++; } cell = sumRow.createCell(j); int lastcol = account.getListStudentScore().size(); //calculate column int dv = lastcol / 26; String coltmp = ""; for (int i = 0; i < dv; i++) { coltmp += "A"; } coltmp += (char) ('A' + (lastcol - (dv * 26))); System.out.println(coltmp); // String ref = (char) ('A' + 1) + "" + (rownum + 1) + ":" + coltmp + (rownum + 1); System.out.println(ref); cell.setCellFormula("SUM(" + ref + ")"); rownum++; } // Write the output to a file String filename = "scoresheet_" + c.getName() + ".xlsx"; String file = getServletContext().getRealPath("/") + "/file/scoresheet/" + filename; // String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); response.sendRedirect("file/scoresheet/" + filename); // // Workbook wb = new XSSFWorkbook(); // Sheet sheet = wb.createSheet("scoresheet"); // PrintSetup printSetup = sheet.getPrintSetup(); // printSetup.setLandscape(true); // sheet.setFitToPage(true); // sheet.setHorizontallyCenter(true); // // //title row // Row titleRow = sheet.createRow(0); // titleRow.setHeightInPoints(45); // Cell titleCell = titleRow.createCell(0); // titleCell.setCellValue("Score sheet of " + "...." + " course"); // sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1")); // // //row with totals below // int rownum = 2; // Row sumRow = sheet.createRow(rownum); // sumRow.setHeightInPoints(35); // Cell cell; // cell = sumRow.createCell(0); // cell.setCellValue("Name:"); // // for (int j = 1; j < 12; j++) { // cell = sumRow.createCell(j); // String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; // cell.setCellFormula("SUM(" + ref + ")"); // } // // // Write the output to a file // String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx"; // FileOutputStream out = new FileOutputStream(file); // wb.write(out); // out.close(); }
From source file:step.datapool.excel.ExcelFunctions.java
License:Open Source License
/** * Konvertiert unterschiedliche Formate in Strings. * * @param cell Excel Zelle/*from w ww .ja v a 2 s . com*/ * @param evaluator FormulaEvaluator * @return Wert der Zelle als String */ public static String getCellValueAsString(Cell cell, FormulaEvaluator evaluator) { boolean isFormulaPatched = false; String initialFormula = null; int chkTyp = cell.getCellType(); if (chkTyp == Cell.CELL_TYPE_FORMULA) { initialFormula = cell.getCellFormula(); // Some formula have to be changed before they can be evaluated in POI String formula = FormulaPatch.patch(initialFormula); if (!formula.equals(initialFormula)) { isFormulaPatched = true; cell.setCellFormula(formula); evaluator.notifySetFormula(cell); } } try { int typ = evaluateFormulaCell(cell, evaluator); if (typ == -1) typ = cell.getCellType(); switch (typ) { case Cell.CELL_TYPE_NUMERIC: /* Datum und Zeit (sind auch Zahlen) */ if (DateUtil.isCellDateFormatted(cell)) { Date dat = cell.getDateCellValue(); GregorianCalendar cal = new GregorianCalendar(); cal.setTime(dat); /* * In Excel beginnt die Zeitrechnung am 01.01.1900. Ein Datum ist immer als * double gespeichert. Dabei ist der Teil vor dem Dezimalpunkt das Datum * und der Teil nach dem Dezimalpunkt die Zeit (z.B. 1.5 entspricht 01.01.1900 12:00:00). * Falls der Tag 0 angegeben ist wird der Datumsanteil mit 31.12.1899 zurueck- * gegeben. Erhalten wir also ein Jahr kleiner als 1900, dann haben wir eine * Zeit. */ if (cal.get(Calendar.YEAR) < 1900) { // Zeitformat SimpleDateFormat STD_TIM = new SimpleDateFormat("kk:mm:ss"); return STD_TIM.format(dat); } SimpleDateFormat STD_DAT = new SimpleDateFormat("dd.MM.yyyy"); return STD_DAT.format(dat); // Datumsformat } else { /* int, long, double Formate */ double dbl = cell.getNumericCellValue(); int tryInt = (int) dbl; long tryLong = (long) dbl; if (tryInt == dbl) { return new Integer(tryInt).toString(); // int-Format } else if (tryLong == dbl) { return new Long(tryLong).toString(); // long-Format } // return new Double(dbl).toString(); // double-Format String numberValueString = new Double(dbl).toString(); // double-Format // always use decimal format try { // scale 14 to solve problem like value 0.22 --> 0.219999999999997 BigDecimal roundedBigDecimal = new BigDecimal(numberValueString).setScale(14, RoundingMode.HALF_UP); // use constructor BigDecimal(String)! String customValueString = getCustomDecimalFormat().format(roundedBigDecimal); if (!customValueString.equals(numberValueString)) { logger.debug("getCellValusAsString: Changing string value of double '{}' to '{}'", numberValueString, customValueString); numberValueString = customValueString; // bigdecimal-format } } catch (Exception e) { logger.error("An error occurred trying to convert the cell value number to decimal format " + numberValueString, e); } return numberValueString; } case Cell.CELL_TYPE_BOOLEAN: return Boolean.toString(cell.getBooleanCellValue()); case Cell.CELL_TYPE_FORMULA: /* Dieser Fall wird jetzt nie eintreffen, da im Falle einer Formel neu die * Berechnung zurueckgegeben wurde, die dann einen eigenen Typ hat. */ return cell.getCellFormula(); case Cell.CELL_TYPE_STRING: return cell.getRichStringCellValue().getString(); case Cell.CELL_TYPE_BLANK: return ""; case Cell.CELL_TYPE_ERROR: switch (cell.getErrorCellValue()) { case 1: return "#NULL!"; case 2: return "#DIV/0!"; case 3: return "#VALUE!"; case 4: return "#REF!"; case 5: return "#NAME?"; case 6: return "#NUM!"; case 7: return "#N/A"; default: return "#ERR!"; } default: return "ERROR: unknown Format"; } } finally { if (isFormulaPatched) { cell.setCellFormula(initialFormula); evaluator.notifySetFormula(cell); } } }
From source file:templategenerator.TemplateWindow.java
private void writeVals() { //Get the workbook instance for XLS file workbook = new HSSFWorkbook(); sheet = workbook.createSheet("Top Level"); int current = 1; for (int i = 0; i < quantity; i++) { Row row = sheet.createRow(i);/*w w w .ja v a 2 s . c om*/ Cell cell = row.createCell(0); cell.setCellValue(serial++); cell = row.createCell(1); cell.setCellValue(i + 1); //comment cell = row.createCell(3); cell.setCellFormula(anode1Code + (i + 1) + codeSuffix); cell = row.createCell(13); cell.setCellFormula(anode2Code + (i + 1) + codeSuffix); cell = row.createCell(23); cell.setCellFormula(anode3Code + (i + 1) + codeSuffix); cell = row.createCell(33); cell.setCellFormula(anode4Code + (i + 1) + codeSuffix); cell = row.createCell(43); cell.setCellFormula(anode5Code + (i + 1) + codeSuffix); cell = row.createCell(53); cell.setCellFormula(cathodeCode + (i + 1) + codeSuffix); cell = row.createCell(64); cell.setCellFormula(glassingCode + (i + 1) + codeSuffix); } }