List of usage examples for org.apache.poi.ss.usermodel Cell setCellFormula
void setCellFormula(String formula) throws FormulaParseException, IllegalStateException;
From source file:test.poi.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false);// w w w . ja va2s . c o m sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "E:/loan-calculator.xls"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:test.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb = new HSSFWorkbook(); // if(args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); // else wb = new Workbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);/* w ww . ja v a2 s .c om*/ 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 = "E:\\timesheet.xls"; // if(wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:uk.co.danielrendall.fractdim.app.workers.ExcelExportWorker.java
License:Open Source License
public void visit(ResolutionGridCollection collection) { intermediateResolutionRow = 2;/*from ww w. j a v a 2s .c om*/ for (Double resolution : collection.getAvailableResolutions()) { currentResolution = resolution; int initialRawDataRow = currentDataRow + 1; // rows are 0 based, but formulas are 1-based DisplacementGridCollection dgc = collection.collectionForResolution(resolution); dgc.accept(this); int finalRawDataRow = currentDataRow; // actually currentdataRow - 1 + 1 final Row intermediateAverageRow = intermediateSheet.getRow(intermediateResolutionRow); Cell displacementSum = intermediateAverageRow.createCell(intermediateAngleColumn); Cell reciprocal = intermediateAverageRow.createCell(intermediateAngleColumn + 1); displacementSum .setCellFormula(String.format("MIN('Data'!E%d:E%d)", initialRawDataRow, finalRawDataRow)); reciprocal.setCellFormula(String.format("LOG(%s)", new CellReference(intermediateResolutionRow, intermediateAngleColumn, false, false) .formatAsString())); intermediateResolutionRow++; } }
From source file:Utilities.ExportToXLSX.java
public void addNewLeave(String destFilePath, String[] data, int rowNum) throws ParseException, FileNotFoundException, IOException { // WB = new XSSFWorkbook(); FileOutputStream output;/*from ww w . ja v a 2s .c o m*/ Calendar calendar = Calendar.getInstance(); // for dates; Row row; Cell cell; //String[] data = input.nextLine().split(GlobalVar.PARSE); //System.out.println(Arrays.toString(data)); row = sheet.createRow(rowNum); // row in excel // String[] header = GlobalVar.NEW_LEAVE_TITLES; String[] header = GlobalVar.LEAVE_TITLES_V1; for (int i = 0; i < header.length; i++) { cell = row.createCell(i); cell.setCellStyle(standardStyle); // matches the items in the header switch (i) { case 0: cell.setCellValue(data[GlobalVar.CN_ID]); // cell.setCellType(GlobalVar.CTRL_NUM_TYPE); break; case 1: cell.setCellValue(Integer.parseInt(data[GlobalVar.LAST4SSN_ID])); cell.setCellStyle(ssnStyle); //cell.setCellType(GlobalVar.SSN_TYPE); break; case 2: cell.setCellValue(data[GlobalVar.LN_ID]); // cell.setCellType(HSSFCell.CELL_TYPE_); break; case 3: calendar.setTime(fmt.parse(data[GlobalVar.PSO_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; case 4: calendar.setTime(fmt.parse(data[GlobalVar.PSI_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; case 5: switch (data[GlobalVar.LV_A_ID].charAt(0)) { case '1': cell.setCellValue("CONUS"); break; case '2': cell.setCellValue("OCONUS"); break; default: cell.setCellValue(""); } //cell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case 6: switch (data[GlobalVar.LV_T_ID].charAt(0)) { case 'A': cell.setCellValue("Ordinary"); break; case 'D': cell.setCellValue("Emergency"); break; case 'T': cell.setCellValue("Permissive TDY"); break; case 'F': cell.setCellValue("Convalescent"); break; default: cell.setCellValue(""); } //cell.setCellType(HSSFCell.CELL_TYPE_ERROR); break; case 7: int r = rowNum + 1; //switch from zero based to one based (label on worksheet) // System.out.println(r); String fmla = "IF(AND(E" + r + ",D" + r + "),E" + r + "-D" + r + "+1" + ",\"\")"; cell.setCellFormula(fmla); cell.setCellStyle(formulaStyle); //cell.setCellValue(data[GlobalVar.P_ND_ID]); break; case 8: cell.setCellValue(data[GlobalVar.L5_ID]); // print out the first five chars of names // cell.setCellType(GlobalVar.LAST5_TYPE); break; } } output = new FileOutputStream(destFilePath); wb.write(output); output.close(); }
From source file:Utilities.ExportToXLSX.java
private void createSignedLeaveBody(String dbPath, String destFilePath) throws ParseException { Scanner input = null;//from w w w . ja v a 2s . co m // CellStyle style = createStandardStyle(); Calendar calendar = Calendar.getInstance(); // for dates; try { input = new Scanner(new File(dbPath)); } catch (FileNotFoundException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); JOptionPane.showMessageDialog(null, "No signed leaves found."); } //Sheet sheet = wb.getSheetAt(0); //get the first sheet int rowNum = 1; // skip the header line while (input.hasNextLine()) { Row row; Cell cell; String[] data = input.nextLine().split("`"); // System.out.println(Arrays.toString(data)); row = sheet.createRow(rowNum); // String[] header = GlobalVar.SIGNED_LEAVE_TITLES; for (int i = 0; i < header.length; i++) { cell = row.createCell(i); cell.setCellStyle(standardStyle); // matches the items in the signed leave header switch (i) { case 0: cell.setCellValue(data[GlobalVar.CN_ID]); //cell.setCellType(GlobalVar.CTRL_NUM_TYPE); break; case 1: cell.setCellValue(Integer.parseInt(data[GlobalVar.LAST4SSN_ID])); cell.setCellStyle(ssnStyle); //cell.setCellType(Cell.CELL_TYPE_STRING); break; case 2: cell.setCellValue(data[GlobalVar.LN_ID]); // cell.setCellType(GlobalVar.LASTNAME_TYPE); break; case 3: calendar.setTime(fmt.parse(data[GlobalVar.SO_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; case 4: calendar.setTime(fmt.parse(data[GlobalVar.SI_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); //cell.setCellStyle(styles.get(styleName)); break; case 5: switch (data[GlobalVar.LV_A_ID].charAt(0)) { case '1': cell.setCellValue("CONUS"); break; case '2': cell.setCellValue("OCONUS"); break; default: cell.setCellValue(""); } //cell.setCellType(HSSFCell.CELL_TYPE_BLANK); break; case 6: switch (data[GlobalVar.LV_T_ID].charAt(0)) { case 'A': cell.setCellValue("Ordinary"); break; case 'D': cell.setCellValue("Emergency"); break; case 'T': cell.setCellValue("Permissive TDY"); break; case 'F': cell.setCellValue("Convalescent"); break; default: cell.setCellValue(""); } break; case 7: int r = rowNum + 1; //switch from zero based to one based (label on worksheet) String fmla = "IF(AND(E" + r + ",D" + r + "),E" + r + "- D" + r + "+1" + ",\"\")"; //inclusive days cell.setCellFormula(fmla); cell.setCellStyle(formulaStyle); break; case 8: cell.setCellValue(data[GlobalVar.L5_ID]); // print out the first five chars of names //cell.setCellType(GlobalVar.LAST5_TYPE); break; case 9: calendar.setTime(fmt.parse(data[GlobalVar.PSO_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; case 10: calendar.setTime(fmt.parse(data[GlobalVar.PSI_D_ID])); cell.setCellValue(calendar); cell.setCellStyle(dateStyle); break; } //switch loop } // for loop rowNum++; } // hide the last column (first five chars of the names) sheet.setColumnHidden(8, true); // this will hide the column index 8 FileOutputStream output; try { output = new FileOutputStream(destFilePath); wb.write(output); output.close(); } catch (FileNotFoundException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); } catch (IOException ex) { Logger.getLogger(ExportToXLSX.class.getName()).log(Level.SEVERE, null, ex); } }
From source file:utilities.XLSXAdminReportsManager.java
License:Open Source License
public Response getNewReport(Connection sd, HttpServletRequest request, HttpServletResponse response, ArrayList<String> header, ArrayList<AR> report, String filename, boolean byProject, boolean bySurvey, boolean byDevice, int year, int month) { Response responseVal = null;// www.j a va2 s .c o m String escapedFileName = null; try { escapedFileName = URLDecoder.decode(filename, "UTF-8"); escapedFileName = URLEncoder.encode(escapedFileName, "UTF-8"); } catch (UnsupportedEncodingException e1) { e1.printStackTrace(); } escapedFileName = escapedFileName.replace("+", " "); // Spaces ok for file name within quotes escapedFileName = escapedFileName.replace("%2C", ","); // Commas ok for file name within quotes if (header != null) { Workbook wb = null; int rowNumber = 0; Sheet dataSheet = null; CellStyle errorStyle = null; try { /* * Create XLSX File */ GeneralUtilityMethods.setFilenameInResponse(filename + "." + "xlsx", response); // Set file name wb = new SXSSFWorkbook(10); // Serialised output dataSheet = wb.createSheet("data"); rowNumber = 0; Map<String, CellStyle> styles = XLSUtilities.createStyles(wb); CellStyle headerStyle = styles.get("header"); errorStyle = styles.get("error"); /* * Write the headers */ Row yearRow = dataSheet.createRow(rowNumber++); Cell cell = yearRow.createCell(0); // Year cell.setCellValue(localisation.getString("bill_year")); cell = yearRow.createCell(1); cell.setCellValue(year); Row monthRow = dataSheet.createRow(rowNumber++); cell = monthRow.createCell(0); // Month cell.setCellValue(localisation.getString("bill_month")); cell = monthRow.createCell(1); cell.setCellValue(month); rowNumber++; // blank row Row headerRow = dataSheet.createRow(rowNumber++); int colNumber = 0; while (colNumber < header.size()) { cell = headerRow.createCell(colNumber); cell.setCellStyle(headerStyle); cell.setCellValue(header.get(colNumber)); colNumber++; } int monthlyCol = 0; int allTimeCol = 0; int firstDataRow = rowNumber + 1; for (AR ar : report) { if (ar.usageInPeriod > 0 || ar.allTimeUsage > 0) { colNumber = 0; Row row = dataSheet.createRow(rowNumber++); cell = row.createCell(colNumber++); // ident cell.setCellValue(ar.userIdent); cell = row.createCell(colNumber++); // Name cell.setCellValue(ar.userName); cell = row.createCell(colNumber++); // User created if (ar.created != null) { cell.setCellStyle(styles.get("date")); cell.setCellValue(ar.created); } if (byProject || bySurvey) { cell = row.createCell(colNumber++); // Project cell.setCellValue(ar.p_id); cell = row.createCell(colNumber++); cell.setCellValue(ar.project); } if (bySurvey) { cell = row.createCell(colNumber++); // Survey cell.setCellValue(ar.s_id); cell = row.createCell(colNumber++); cell.setCellValue(ar.survey); } if (byDevice) { cell = row.createCell(colNumber++); // Device cell.setCellValue(ar.device); } monthlyCol = colNumber; cell = row.createCell(colNumber++); // Monthly Usage cell.setCellValue(ar.usageInPeriod); allTimeCol = colNumber; cell = row.createCell(colNumber++); // All time Usage cell.setCellValue(ar.allTimeUsage); } } // Add totals Row row = dataSheet.createRow(rowNumber++); // Monthly cell = row.createCell(monthlyCol); String colAlpha = getColAlpha(monthlyCol); String formula = "SUM(" + colAlpha + firstDataRow + ":" + colAlpha + (rowNumber - 1) + ")"; cell.setCellType(CellType.FORMULA); cell.setCellStyle(styles.get("bold")); cell.setCellFormula(formula); // All time cell = row.createCell(allTimeCol); colAlpha = getColAlpha(allTimeCol); formula = "SUM(" + colAlpha + firstDataRow + ":" + colAlpha + (rowNumber - 1) + ")"; cell.setCellType(CellType.FORMULA); cell.setCellStyle(styles.get("bold")); cell.setCellFormula(formula); } catch (Exception e) { log.log(Level.SEVERE, "Error", e); response.setHeader("Content-type", "text/html; charset=UTF-8"); String msg = e.getMessage(); if (msg.contains("does not exist")) { msg = localisation.getString("msg_no_data"); } Row dataRow = dataSheet.createRow(rowNumber + 1); Cell cell = dataRow.createCell(0); cell.setCellStyle(errorStyle); cell.setCellValue(msg); responseVal = Response.status(Status.OK).entity("Error: " + e.getMessage()).build(); } finally { try { OutputStream outputStream = response.getOutputStream(); wb.write(outputStream); wb.close(); outputStream.close(); ((SXSSFWorkbook) wb).dispose(); // Dispose of temporary files } catch (Exception ex) { log.log(Level.SEVERE, "Error", ex); } } } return responseVal; }
From source file:vistas.reportes.procesos.pruebaExcel.java
public static void main(String[] args) throws Exception { Workbook wb;// w w w. j ava 2 s . 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("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(); try { File path = new File(file); Desktop.getDesktop().open(path); } catch (IOException ex) { ex.printStackTrace(); } }
From source file:y.graphs.XLSHelper.java
License:Open Source License
public static void saveCorrelationsCurrents(String filename, CurrentElfDb db, final double imax, final double ui, final double ub) throws IOException { if (Utils.abortOnExistingAndDontOverwrite(filename)) return;/*from ww w . jav a2 s .c o m*/ Workbook wb = new XSSFWorkbook(); final String nDati = Config.getResource("TitleSheetDatas"); final String nDelta = Config.getResource("TitleSheetDelta"); final String nCalcs = Config.getResource("TitleSheetCalcs"); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(nDati); CellStyle dateStyle1 = wb.createCellStyle(); dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy")); CellStyle timeStyle1 = wb.createCellStyle(); timeStyle1.setDataFormat(createHelper.createDataFormat().getFormat("HH:mm")); CellStyle doubleFormat1 = wb.createCellStyle(); DataFormat format1 = wb.createDataFormat(); doubleFormat1.setDataFormat(format1.getFormat("0.00")); CellStyle percFormat1 = wb.createCellStyle(); percFormat1.setDataFormat(format1.getFormat("0.00%")); int rown = 0; Row row = sheet.createRow(rown++); final String[] CorrentiColonne = Config.getInstance().getXLSColumnNames(); for (int i = 0; i < CorrentiColonne.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(CorrentiColonne[i]); } final List<ElfValue> elfs = db.getElfDb(); final List<CurrentValue> currs = db.getCurrentDb(); for (int i = 0, maxi = Math.max(elfs.size(), currs.size()); i < maxi; i++) { final ElfValue e = i <= elfs.size() ? elfs.get(i) : null; final CurrentValue c = i <= currs.size() ? currs.get(i) : null; row = sheet.createRow(rown++); int columnnn = 0; if (c == null) { Cell cell = row.createCell(columnnn++); cell.setCellValue(""); cell = row.createCell(columnnn++); cell.setCellValue(""); cell = row.createCell(columnnn++); cell.setCellValue(""); } else { Cell cell = row.createCell(columnnn++); cell.setCellValue(Utils.toDateString(c.getTime())); // data corrente cell.setCellStyle(dateStyle1); cell = row.createCell(columnnn++); cell.setCellStyle(timeStyle1); cell.setCellValue(Utils.toTimeString(c.getTime())); // ora corrente cell = row.createCell(columnnn++); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(c.getValue())); } if (e == null) { Cell cell = row.createCell(columnnn++); cell.setCellValue(""); cell = row.createCell(columnnn++); cell.setCellValue(""); cell = row.createCell(columnnn++); cell.setCellValue(""); } else { Cell cell = row.createCell(columnnn++); cell.setCellStyle(dateStyle1); cell.setCellValue(Utils.toDateString(e.getTime())); // data corrente cell = row.createCell(columnnn++); cell.setCellStyle(timeStyle1); cell.setCellValue(Utils.toTimeString(e.getTime())); // ora corrente cell = row.createCell(columnnn++); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(e.getValue())); // ora corrente } } final int total_rown = rown; // intermedi { sheet = wb.createSheet(nDelta); rown = 0; int columnnn; columnnn = 0; row = sheet.createRow(rown++); Cell cell = row.createCell(columnnn++); cell.setCellValue("dI"); cell = row.createCell(columnnn++); cell.setCellValue("dB"); cell = row.createCell(columnnn++); cell.setCellValue("dIdB"); cell = row.createCell(columnnn++); cell.setCellValue("dI^2"); cell = row.createCell(columnnn++); cell.setCellValue("dB^2"); cell = row.createCell(columnnn++); cell.setCellValue("Ri = Bi/Ii"); cell = row.createCell(columnnn++); cell.setCellValue("Ri^2"); for (int i = 2; i <= total_rown; i++) { columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellFormula(nDati + "!C" + i + "-" + nCalcs + "!$B$2"); cell = row.createCell(columnnn++); cell.setCellFormula(nDati + "!F" + i + "-" + nCalcs + "!$B$3"); cell = row.createCell(columnnn++); cell.setCellFormula("A" + i + "*B" + i); cell = row.createCell(columnnn++); cell.setCellFormula("A" + i + "*A" + i); cell = row.createCell(columnnn++); cell.setCellFormula("B" + i + "*B" + i); cell = row.createCell(columnnn++); cell.setCellFormula(nDati + "!F" + i + "/" + nDati + "!C" + i); cell = row.createCell(columnnn++); cell.setCellFormula("F" + i + "*F" + i); } } // correlazioni e calcoli finali { sheet = wb.createSheet(nCalcs); rown = 0; int columnnn; columnnn = 0; row = sheet.createRow(rown++); Cell cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleNumberOfData")); cell = row.createCell(columnnn++); cell.setCellFormula("COUNT(" + nDati + "!C:C)"); // B1 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleCurAvg")); cell = row.createCell(columnnn++); cell.setCellFormula("AVERAGE(" + nDati + "!C:C)"); // B2 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleFieldAvg")); cell = row.createCell(columnnn++); cell.setCellFormula("AVERAGE(" + nDati + "!F:F)"); // B3 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleRm")); cell = row.createCell(columnnn++); cell.setCellFormula("AVERAGE(" + nDelta + "!F:F)"); // B4 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleImax")); cell = row.createCell(columnnn++); cell.setCellStyle(doubleFormat1); cell.setCellValue(imax); // B5 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleUI")); cell = row.createCell(columnnn++); cell.setCellStyle(percFormat1); cell.setCellValue(ui); // B6 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleUB")); cell = row.createCell(columnnn++); cell.setCellStyle(percFormat1); cell.setCellValue(ub); // B7 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleURm")); cell = row.createCell(columnnn++); cell.setCellStyle(percFormat1); cell.setCellFormula("$B$6*$B$6+$B$7*$B$7-$B$6*$B$7*$B$9"); // B8 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleCorrelation")); cell = row.createCell(columnnn++); cell.setCellFormula("SUM(" + nDelta + "!C:C)/SQRT(SUM(" + nDelta + "!D:D)*SUM(" + nDelta + "!E:E))"); // B9 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleBmax")); cell = row.createCell(columnnn++); cell.setCellFormula("$B$4*$B$5"); // B10 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleEperc")); cell = row.createCell(columnnn++); cell.setCellFormula("$B$8*SUM(delta!G:G)/$B$1/$B$1"); // B11 = u(Rm)^2 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleUBmax")); cell = row.createCell(columnnn++); cell.setCellFormula("$B$5 * SQRT($B$11 + $B$4*$B$4*$B$6*$B$6)"); // B12 columnnn = 0; row = sheet.createRow(rown++); cell = row.createCell(columnnn++); cell.setCellValue(Config.getResource("TitleEBmax")); cell = row.createCell(columnnn++); cell.setCellStyle(percFormat1); cell.setCellFormula("2*$B$12/$B$10"); // B13 } FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); wb.close(); fileOut.close(); }