List of usage examples for org.apache.poi.ss.usermodel CreationHelper createDataFormat
DataFormat createDataFormat();
From source file:pruebaimportarexcel.excel.ExcelUtils.java
/** * Devuelve el CellStyle adecuado para mostrar una fecha en un formato * legible//from ww w.ja v a2s . c o m * * @return */ public CellStyle getDateStyle() { if (dateStyle == null) { dateStyle = wb.createCellStyle(); CreationHelper ch = wb.getCreationHelper(); dateStyle.setDataFormat(ch.createDataFormat().getFormat("dd/mm/yyyy")); } return dateStyle; }
From source file:ro.ldir.report.formatter.TeamExcelFormatter.java
License:Open Source License
public final Workbook convert(Workbook wb) { Sheet sheet = wb.createSheet("Liste echipe"); Row row = sheet.createRow(0);/*from w ww . j a v a2 s . co m*/ row.createCell(0).setCellValue("ID"); row.createCell(1).setCellValue("Email manager"); row.createCell(2).setCellValue("Nume manager"); row.createCell(3).setCellValue("Comun\u04d1 manager"); row.createCell(4).setCellValue("Jude\u0163 manager"); row.createCell(5).setCellValue("Cod acces"); row.createCell(6).setCellValue("Nume"); row.createCell(7).setCellValue("Num\u04d1r membri"); row.createCell(8).setCellValue("Multi-manager"); row.createCell(9).setCellValue("Nume organiza\u0163ie"); row.createCell(10).setCellValue("Comun\u04d1 organiza\u0163ie"); row.createCell(11).setCellValue("Jude\u0163 organiza\u0163ie"); row.createCell(12).setCellValue("Tip organiza\u0163ie"); row.createCell(13).setCellValue("Nume persoan\u04d1 de contact"); row.createCell(14).setCellValue("Email persoan\u04d1 de contact"); row.createCell(15).setCellValue("Num\u04d1r membri organiza\u0163ie"); row.createCell(16).setCellValue("Num\u04d1r GPS"); row.createCell(17).setCellValue("Transport"); row.createCell(18).setCellValue("Num\u04d1r saci"); row.createCell(19).setCellValue("Num\u04d1r m\u04d1nu\015Fi"); row.createCell(20).setCellValue("Num\u04d1r lope\u0163i"); row.createCell(21).setCellValue("Utilaje"); row.createCell(22).setCellValue("Num\u04d1r mormane alocate"); row.createCell(23).setCellValue("List\u04d1 mormane alocate"); row.createCell(24).setCellValue("Sum\u04d1 volum mormane alocate"); row.createCell(25).setCellValue("Activitati"); row.createCell(26).setCellValue("Data inregistrarii"); int i = 0; CreationHelper createHelper = wb.getCreationHelper(); for (Team team : teams) { if (team.getOrganizationMembers() == null || team.getOrganizationMembers().size() == 0) { i++; row = sheet.createRow(i); teamHeader(row, team); teamFooter(row, team); continue; } for (Organization org : team.getOrganizationMembers()) { i++; row = sheet.createRow(i); teamHeader(row, team); organization(row, org); teamFooter(row, team); } if (team.getTeamManager() != null) { StringBuffer ab = new StringBuffer(); List<User.Activity> activities = team.getTeamManager().getActivities(); if (activities != null && activities.size() > 0) { for (User.Activity activity : activities) if (activity != null) if (activity.getReportName() != null) ab.append(activity.getReportName() + ", "); else ab.append(" " + ", "); if (ab.length() > 1) row.createCell(25).setCellValue(ab.substring(0, ab.length() - 2)); else row.createCell(25).setCellValue(" "); } if (team.getTeamManager().getRecordDate() != null) { CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); Cell dateCell = row.createCell(26); dateCell.setCellValue(team.getTeamManager().getRecordDate()); dateCell.setCellStyle(cellStyle); } } } return wb; }
From source file:ro.ldir.report.formatter.UserExcelFormatter.java
License:Open Source License
public final Workbook convert(Workbook wb) { Sheet sheet = wb.createSheet("Utilizatori"); CreationHelper createHelper = wb.getCreationHelper(); teams = new ArrayList<Team>(); Row row = sheet.createRow(0);/*from w w w . jav a 2s .co m*/ row.createCell(0).setCellValue("Prenume"); row.createCell(1).setCellValue("Nume"); row.createCell(2).setCellValue("Email"); row.createCell(3).setCellValue("Telefon"); row.createCell(4).setCellValue("Rol"); row.createCell(5).setCellValue("Jude\u0163"); row.createCell(6).setCellValue("Oras"); row.createCell(7).setCellValue("Data \u00eenregistr\u04d1rii"); row.createCell(8).setCellValue("ID"); row.createCell(9).setCellValue("Nr. mormane"); row.createCell(10).setCellValue("Nr. zone"); row.createCell(11).setCellValue("Activitate"); for (int i = 0; i < users.size(); i++) { row = sheet.createRow(i + 1); User user = users.get(i); if (user == null) continue; row.createCell(0).setCellValue(user.getFirstName()); row.createCell(1).setCellValue(user.getLastName()); row.createCell(2).setCellValue(user.getEmail()); row.createCell(3).setCellValue(user.getPhone()); row.createCell(4).setCellValue(user.getRole()); row.createCell(5).setCellValue(user.getCounty()); row.createCell(6).setCellValue(user.getTown()); if (user.getRecordDate() != null) { CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); Cell dateCell = row.createCell(7); dateCell.setCellValue(user.getRecordDate()); dateCell.setCellStyle(cellStyle); } row.createCell(8, Cell.CELL_TYPE_NUMERIC).setCellValue(user.getUserId()); if (user.getGarbages() == null) row.createCell(9, Cell.CELL_TYPE_NUMERIC).setCellValue(0); else row.createCell(9, Cell.CELL_TYPE_NUMERIC).setCellValue(user.getGarbages().size()); if (user.getMemberOf() == null || user.getMemberOf().getChartedAreas() == null) row.createCell(10, Cell.CELL_TYPE_NUMERIC).setCellValue(0); else row.createCell(10, Cell.CELL_TYPE_NUMERIC) .setCellValue(user.getMemberOf().getChartedAreas().size()); StringBuffer ab = new StringBuffer(); List<User.Activity> activities = user.getActivities(); if (activities != null && activities.size() > 0) { for (User.Activity activity : activities) if (activity != null) if (activity.getReportName() != null) ab.append(activity.getReportName() + ", "); else ab.append(" " + ", "); if (ab.length() > 1) row.createCell(11).setCellValue(ab.substring(0, ab.length() - 2)); else row.createCell(11).setCellValue(" "); } List<Team> managedTeams = user.getManagedTeams(); if (managedTeams != null && managedTeams.size() > 0) { for (Team team : managedTeams) teams.add(team); } } TeamExcelFormatter teamWb = new TeamExcelFormatter(teams); wb = teamWb.convert(wb); return wb; }
From source file:sql.fredy.sqltools.XLSExport.java
License:Open Source License
/** * Create the XLS-File named fileName// w w w . java2s . c om * * @param fileName is the Name (incl. Path) of the XLS-file to create * * */ public int createXLS(String fileName) { // I need to have a query to process if ((getQuery() == null) && (getPstmt() == null)) { logger.log(Level.WARNING, "Need to have a query to process"); return 0; } // I also need to have a file to write into if (fileName == null) { logger.log(Level.WARNING, "Need to know where to write into"); return 0; } fileName = fixFileName(fileName); checkXlsx(fileName); // I need to have a connection to the RDBMS if (getCon() == null) { logger.log(Level.WARNING, "Need to have a connection to process"); return 0; } //Statement stmt = null; ResultSet resultSet = null; ResultSetMetaData rsmd = null; try { // first we have to create the Statement if (getPstmt() == null) { pstmt = getCon().prepareStatement(getQuery()); } //stmt = getCon().createStatement(); } catch (SQLException sqle1) { setException(sqle1); logger.log(Level.WARNING, "Can not create Statement. Message: " + sqle1.getMessage().toString()); return 0; } logger.log(Level.FINE, "FileName: " + fileName); logger.log(Level.FINE, "Query : " + getQuery()); logger.log(Level.FINE, "Starting export..."); // create an empty sheet Workbook wb; Sheet sheet; Sheet sqlsheet; CreationHelper createHelper = null; //XSSFSheet xsheet; //HSSFSheet sheet; if (isXlsx()) { wb = new SXSSFWorkbook(); createHelper = wb.getCreationHelper(); } else { wb = new HSSFWorkbook(); createHelper = wb.getCreationHelper(); } sheet = wb.createSheet("Data Export"); // create a second sheet just containing the SQL Statement sqlsheet = wb.createSheet("SQL Statement"); Row sqlrow = sqlsheet.createRow(0); Cell sqltext = sqlrow.createCell(0); try { if (getQuery() != null) { sqltext.setCellValue(getQuery()); } else { sqltext.setCellValue(pstmt.toString()); } } catch (Exception lex) { } CellStyle style = wb.createCellStyle(); style.setWrapText(true); sqltext.setCellStyle(style); Row r = null; int row = 0; // row number int col = 0; // column number int columnCount = 0; try { //resultSet = stmt.executeQuery(getQuery()); resultSet = pstmt.executeQuery(); logger.log(Level.FINE, "query executed"); } catch (SQLException sqle2) { setException(sqle2); logger.log(Level.WARNING, "Can not execute query. Message: " + sqle2.getMessage().toString()); return 0; } // create Header in XLS-file ArrayList<String> head = new ArrayList(); try { rsmd = resultSet.getMetaData(); logger.log(Level.FINE, "Got MetaData of the resultset"); columnCount = rsmd.getColumnCount(); logger.log(Level.FINE, Integer.toString(columnCount) + " Columns in this resultset"); r = sheet.createRow(row); // titlerow if ((!isXlsx()) && (columnCount > 255)) { columnCount = 255; } for (int i = 0; i < columnCount; i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue(rsmd.getColumnName(i + 1)); head.add(rsmd.getColumnName(i + 1)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } } catch (SQLException sqle3) { setException(sqle3); logger.log(Level.WARNING, "Can not create XLS-Header. Message: " + sqle3.getMessage().toString()); return 0; } // looping the resultSet int wbCounter = 0; try { while (resultSet.next()) { // this is the next row col = 0; // put column counter back to 0 to start at the next row row++; // next row // create a new sheet if more then 60'000 Rows and xls file if ((!isXlsx()) && (row % 65530 == 0)) { wbCounter++; row = 0; sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter)); logger.log(Level.INFO, "created a further page because of a huge amount of data"); // create the head r = sheet.createRow(row); // titlerow for (int i = 0; i < head.size(); i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue((String) head.get(i)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } row++; } try { r = sheet.createRow(row); } catch (Exception e) { logger.log(Level.WARNING, "Error while creating row number " + row + " " + e.getMessage()); wbCounter++; row = 0; sheet = wb.createSheet("Data Export " + Integer.toString(wbCounter)); logger.log(Level.WARNING, "created a further page in the hope it helps..."); // create the head r = sheet.createRow(row); // titlerow for (int i = 0; i < head.size(); i++) { // we create the cell Cell cell = r.createCell(col); // set the value of the cell cell.setCellValue((String) head.get(i)); // then we align center CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); // now we make it bold //HSSFFont f = wb.createFont(); Font headerFont = wb.createFont(); headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD); cellStyle.setFont(headerFont); //cellStyle.setFont(f); // adapt this font to the cell cell.setCellStyle(cellStyle); col++; } row++; } col = 0; // put column counter back to 0 to start at the next row String previousMessage = ""; for (int i = 0; i < columnCount; i++) { try { // depending on the type, create the cell switch (rsmd.getColumnType(i + 1)) { case java.sql.Types.INTEGER: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.FLOAT: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.DOUBLE: r.createCell(col).setCellValue(resultSet.getDouble(i + 1)); break; case java.sql.Types.DECIMAL: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.NUMERIC: r.createCell(col).setCellValue(resultSet.getFloat(i + 1)); break; case java.sql.Types.BIGINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.TINYINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.SMALLINT: r.createCell(col).setCellValue(resultSet.getInt(i + 1)); break; case java.sql.Types.DATE: // first we get the date java.sql.Date dat = resultSet.getDate(i + 1); java.util.Date date = new java.util.Date(dat.getTime()); r.createCell(col).setCellValue(date); break; case java.sql.Types.TIMESTAMP: // first we get the date java.sql.Timestamp ts = resultSet.getTimestamp(i + 1); Cell c = r.createCell(col); try { c.setCellValue(ts); // r.createCell(col).setCellValue(ts); // Date Format CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat( createHelper.createDataFormat().getFormat("yyyy/mm/dd hh:mm:ss")); c.setCellStyle(cellStyle); } catch (Exception e) { c.setCellValue(" "); } break; case java.sql.Types.TIME: // first we get the date java.sql.Time time = resultSet.getTime(i + 1); r.createCell(col).setCellValue(time); break; case java.sql.Types.BIT: boolean b1 = resultSet.getBoolean(i + 1); r.createCell(col).setCellValue(b1); break; case java.sql.Types.BOOLEAN: boolean b2 = resultSet.getBoolean(i + 1); r.createCell(col).setCellValue(b2); break; case java.sql.Types.CHAR: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; case java.sql.Types.NVARCHAR: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; case java.sql.Types.VARCHAR: try { r.createCell(col).setCellValue(resultSet.getString(i + 1)); } catch (Exception e) { r.createCell(col).setCellValue(" "); logger.log(Level.WARNING, "Exception while writing column {0} row {3} type: {1} Message: {2}", new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row }); } break; default: r.createCell(col).setCellValue(resultSet.getString(i + 1)); break; } } catch (Exception e) { //e.printStackTrace(); if (resultSet.wasNull()) { r.createCell(col).setCellValue(" "); } else { logger.log(Level.WARNING, "Unhandled type at column {0}, row {3} type: {1}. Filling up with blank {2}", new Object[] { col, rsmd.getColumnType(i + 1), e.getMessage(), row }); r.createCell(col).setCellValue(" "); } } col++; } } //pstmt.close(); } catch (SQLException sqle3) { setException(sqle3); logger.log(Level.WARNING, "Exception while writing data into sheet. Message: " + sqle3.getMessage().toString()); } try { // Write the output to a file FileOutputStream fileOut = new FileOutputStream(fileName); wb.write(fileOut); fileOut.close(); logger.log(Level.INFO, "File created"); logger.log(Level.INFO, "Wrote: {0} lines into XLS-File", Integer.toString(row)); } catch (Exception e) { logger.log(Level.WARNING, "Exception while writing xls-File: " + e.getMessage().toString()); } return row; }
From source file:uk.co.certait.htmlexporter.writer.excel.ExcelTableCellWriter.java
License:Apache License
@Override public void renderCell(Element element, int rowIndex, int columnIndex) { Cell cell = sheet.getRow(rowIndex).createCell(columnIndex); Double numericValue;/*w w w .j a va2 s. com*/ if (isDateCell(element)) { DateFormat df = new SimpleDateFormat(getDateCellFormat(element)); try { cell.setCellValue(df.parse(getElementText(element))); } catch (ParseException pex) { System.out.println("Invalid Usage"); } } else if ((numericValue = getNumericValue(element)) != null) { cell.setCellValue(numericValue); } else { cell = sheet.getRow(rowIndex).createCell(columnIndex, Cell.CELL_TYPE_STRING); cell.setCellValue(getElementText(element)); } Style style = styleMapper.getStyleForElement(element); cell.setCellStyle(styleGenerator.getStyle(cell, style)); if (isDateCell(element)) { CreationHelper createHelper = sheet.getWorkbook().getCreationHelper(); cell.getCellStyle() .setDataFormat(createHelper.createDataFormat().getFormat(getDateCellFormat(element))); } String commentText; if ((commentText = getCellCommentText(element)) != null) { ExcelCellCommentGenerator.addCellComment(cell, commentText, getCellCommentDimension(element)); } if (definesFreezePane(element)) { sheet.createFreezePane(columnIndex, rowIndex); } }
From source file:workbench.db.exporter.ExcelDataFormat.java
License:Apache License
protected void setupWithWorkbook(Workbook wb) { CreationHelper helper = wb.getCreationHelper(); DataFormat dataFormat = helper.createDataFormat(); setUpHeader(wb);//w w w. j a v a 2 s. c o m setUpText(wb); setUpDate(wb, dataFormat); setUpDecimal(wb, dataFormat); setUpInteger(wb, dataFormat); setUpTs(wb, dataFormat); setUpMultiline(wb); }
From source file:y.graphs.XLSHelper.java
License:Open Source License
public static boolean saveElfData(String filename, ElfDb db, double sensibilita, boolean save_grafico) { final DateTime[] times = db.getPeriods(); final ElfValue[][] dayvalues = db.getSampledData(); final int[] mediane = db.getOpValues(); final int[] maxs = db.getOpMaxDay(); final int[] counts = db.getOpValueCount(); final int maxi = db.getMaxidx(); Workbook wb = null;// w w w . jav a2s . c om try { if (Utils.abortOnExistingAndDontOverwrite(filename)) return false; wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(Config.getResource("TitleStats")); int rown = 0; Row row = sheet.createRow(rown++); Cell cell = row.createCell(0); cell.setCellValue(Config.getResource("TitleDate")); cell = row.createCell(1); cell.setCellValue(Config.getResource("TitleMediana")); cell = row.createCell(2); cell.setCellValue(Config.getResource("TitleMaxM")); cell = row.createCell(3); cell.setCellValue(Config.getResource("TitleNumberOfData")); CellStyle dateStyle1 = wb.createCellStyle(); dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy")); CellStyle doubleFormat1 = wb.createCellStyle(); DataFormat format1 = wb.createDataFormat(); doubleFormat1.setDataFormat(format1.getFormat("0.00")); for (int i = 0; i < mediane.length; i++) { row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellStyle(dateStyle1); cell.setCellValue(Utils.toDateString(dayvalues[i][0].getTime())); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(mediane[i])); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(maxs[i])); cell = row.createCell(3); cell.setCellValue(counts[i]); } // line with DataFunction max row = sheet.createRow(rown++); row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("MsgMax") + "(" + db.getOperationPerformed().getName() + ") - " + Utils.toDateString(times[maxi])); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi])); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(maxs[maxi])); cell = row.createCell(3); cell.setCellValue(counts[maxi]); // line with max final ElfValue maxvalue = db.getSelectedElfValue(new Comparator<ElfValue>() { @Override public int compare(ElfValue o1, ElfValue o2) { return o1.getValue() - o2.getValue(); } }); row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("MsgMax") + "(" + Utils.toDateString(maxvalue.getTime()) + ")"); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getValue())); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getMax())); cell = row.createCell(3); cell.setCellValue(counts[maxi]); // sheet containing all raw data Sheet sheetdata = wb.createSheet(Config.getResource("TitleSheetDatas")); CellStyle dateTimeStyle2 = wb.createCellStyle(); dateTimeStyle2.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); CellStyle doubleFormat2 = wb.createCellStyle(); DataFormat format2 = wb.createDataFormat(); doubleFormat2.setDataFormat(format2.getFormat("0.00")); rown = 0; row = sheetdata.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("TitleDate")); cell = row.createCell(1); cell.setCellValue(Config.getResource("TitleValue")); cell = row.createCell(2); cell.setCellValue(Config.getResource("TitlePeak")); cell = row.createCell(3); cell.setCellValue(Config.getResource("TitleMediana")); cell = row.createCell(4); cell.setCellValue(Config.getResource("TitleDayMax")); cell = row.createCell(5); cell.setCellValue(Config.getResource("TitleMedianaMax")); cell = row.createCell(6); cell.setCellValue(Config.getResource("TitleSens")); cell = row.createCell(7); cell.setCellValue(Config.getResource("TitleQualityTarget")); cell = row.createCell(8); cell.setCellValue(Config.getResource("TitleAttentionValue")); for (int i = 0; i < dayvalues.length; i++) { final ElfValue[] day = dayvalues[i]; for (int k = 0; k < day.length; k++) { final ElfValue value = day[k]; final DateTime time = value.getTime(); row = sheetdata.createRow(rown++); cell = row.createCell(0); cell.setCellStyle(dateTimeStyle2); cell.setCellValue(Utils.toDateString(time)); cell = row.createCell(1); cell.setCellStyle(doubleFormat2); if (value.isValid()) cell.setCellValue(ElfValue.valueIntToDouble(value.getValue())); else cell.setCellValue(""); cell = row.createCell(2); cell.setCellStyle(doubleFormat2); if (value.isValid()) cell.setCellValue(ElfValue.valueIntToDouble(value.getMax())); else cell.setCellValue(""); cell = row.createCell(3); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(mediane[i])); cell = row.createCell(4); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(maxs[i])); cell = row.createCell(5); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi])); cell = row.createCell(6); cell.setCellStyle(doubleFormat2); cell.setCellValue(sensibilita); cell = row.createCell(7); cell.setCellStyle(doubleFormat2); cell.setCellValue(3); cell = row.createCell(8); cell.setCellStyle(doubleFormat2); cell.setCellValue(10); } } if (save_grafico) { final int maxline = rown - 1; sheet = wb.createSheet(Config.getResource("TitleChart")); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 18, 25); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); ScatterChartData data = chart.getChartDataFactory().createScatterChartData(); // LineChartData data = chart.getChartDataFactory().createLineChartData(); ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setMinimum(0.0); leftAxis.setMaximum(10.0); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<String> xs = DataSources.fromStringCellRange(sheetdata, new CellRangeAddress(1, maxline, 0, 0)); ChartDataSource<Number> ys_val = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 1, 1)); ChartDataSource<Number> ys_sens = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 6, 6)); ChartDataSource<Number> ys_qual = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 7, 7)); ChartDataSource<Number> ys_att = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 8, 8)); ScatterChartSeries data_val = data.addSerie(xs, ys_val); data_val.setTitle(Config.getResource("TitleMeasuredValues")); ScatterChartSeries data_sens = data.addSerie(xs, ys_sens); data_sens.setTitle(Config.getResource("TitleInstrumentSens")); ScatterChartSeries data_qual = data.addSerie(xs, ys_qual); data_qual.setTitle(Config.getResource("TitleQualityTarget")); ScatterChartSeries data_att = data.addSerie(xs, ys_att); data_att.setTitle(Config.getResource("TitleAttentionValue")); chart.plot(data, bottomAxis, leftAxis); } FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close(); return true; } catch (Exception e) { Utils.MessageBox(Config.getResource("MsgErrorXlsx") + "\n" + e.toString(), Config.getResource("TitleError")); return false; } finally { if (wb != null) try { wb.close(); } catch (IOException e) { } } }
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 w w w . jav a2s. 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(); }
From source file:y.graphs.XLSHelper.java
License:Open Source License
public static boolean saveCurrentsData(String filename, CurrentDb db, boolean save_grafico) { final DateTime[] times = db.getPeriods(); final CurrentValue[][] dayvalues = db.getSampledData(); final int[] mediane = db.getOpValues(); final int[] maxs = db.getOpMaxDay(); final int[] counts = db.getOpValueCount(); final int maxi = db.getMaxidx(); Workbook wb = null;// w ww . ja v a2s. com try { if (Utils.abortOnExistingAndDontOverwrite(filename)) return false; wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); Sheet sheet = wb.createSheet(Config.getResource("TitleStats")); int rown = 0; Row row = sheet.createRow(rown++); Cell cell = row.createCell(0); cell.setCellValue(Config.getResource("TitleDate")); cell = row.createCell(1); cell.setCellValue(Config.getResource("TitleMediana")); cell = row.createCell(2); cell.setCellValue(Config.getResource("TitleMaxM")); cell = row.createCell(3); cell.setCellValue(Config.getResource("TitleNumberOfData")); CellStyle dateStyle1 = wb.createCellStyle(); dateStyle1.setDataFormat(createHelper.createDataFormat().getFormat("d/m/yy")); CellStyle doubleFormat1 = wb.createCellStyle(); DataFormat format1 = wb.createDataFormat(); doubleFormat1.setDataFormat(format1.getFormat("0.00")); for (int i = 0; i < mediane.length; i++) { row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellStyle(dateStyle1); cell.setCellValue(Utils.toDateString(dayvalues[i][0].getTime())); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(mediane[i])); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(maxs[i])); cell = row.createCell(3); cell.setCellValue(counts[i]); } // line with DataFunction max row = sheet.createRow(rown++); row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("MsgMax") + "(" + db.getOperationPerformed().getName() + ") - " + Utils.toDateString(times[maxi])); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi])); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(ElfValue.valueIntToDouble(maxs[maxi])); cell = row.createCell(3); cell.setCellValue(counts[maxi]); // line with max final CurrentValue maxvalue = db.getSelectedCurrentValue(new Comparator<CurrentValue>() { @Override public int compare(CurrentValue o1, CurrentValue o2) { return o1.getValue() - o2.getValue(); } }); row = sheet.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("MsgMax") + "(" + Utils.toDateString(maxvalue.getTime()) + ")"); cell = row.createCell(1); cell.setCellStyle(doubleFormat1); cell.setCellValue(MeasurementValue.valueIntToDouble(maxvalue.getValue())); cell = row.createCell(2); cell.setCellStyle(doubleFormat1); cell.setCellValue(""); cell = row.createCell(3); cell.setCellValue(counts[maxi]); // sheet containing all raw data Sheet sheetdata = wb.createSheet(Config.getResource("TitleSheetDatas")); CellStyle dateTimeStyle2 = wb.createCellStyle(); dateTimeStyle2.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); CellStyle doubleFormat2 = wb.createCellStyle(); DataFormat format2 = wb.createDataFormat(); doubleFormat2.setDataFormat(format2.getFormat("0.00")); rown = 0; row = sheetdata.createRow(rown++); cell = row.createCell(0); cell.setCellValue(Config.getResource("TitleDate")); cell = row.createCell(1); cell.setCellValue(Config.getResource("TitleValue")); cell = row.createCell(2); cell.setCellValue(Config.getResource("TitlePeak")); cell = row.createCell(3); cell.setCellValue(Config.getResource("TitleMediana")); cell = row.createCell(4); cell.setCellValue(Config.getResource("TitleDayMax")); cell = row.createCell(5); cell.setCellValue(Config.getResource("TitleMedianaMax")); for (int i = 0; i < dayvalues.length; i++) { final CurrentValue[] day = dayvalues[i]; for (int k = 0; k < day.length; k++) { final CurrentValue value = day[k]; final DateTime time = value.getTime(); row = sheetdata.createRow(rown++); cell = row.createCell(0); cell.setCellStyle(dateTimeStyle2); cell.setCellValue(Utils.toDateString(time)); cell = row.createCell(1); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(value.getValue())); cell = row.createCell(2); // cell.setCellStyle(doubleFormat2); cell.setCellValue(""); cell = row.createCell(3); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(mediane[i])); cell = row.createCell(4); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(maxs[i])); cell = row.createCell(5); cell.setCellStyle(doubleFormat2); cell.setCellValue(ElfValue.valueIntToDouble(mediane[maxi])); } } if (save_grafico) { final int maxline = rown - 1; sheet = wb.createSheet(Config.getResource("TitleChart")); Drawing drawing = sheet.createDrawingPatriarch(); ClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 1, 1, 18, 25); Chart chart = drawing.createChart(anchor); ChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); ScatterChartData data = chart.getChartDataFactory().createScatterChartData(); // LineChartData data = chart.getChartDataFactory().createLineChartData(); ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM); ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); leftAxis.setMinimum(0.0); leftAxis.setMaximum(10.0); leftAxis.setCrosses(AxisCrosses.AUTO_ZERO); ChartDataSource<String> xs = DataSources.fromStringCellRange(sheetdata, new CellRangeAddress(1, maxline, 0, 0)); ChartDataSource<Number> ys_val = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 1, 1)); ChartDataSource<Number> ys_sens = DataSources.fromNumericCellRange(sheetdata, new CellRangeAddress(1, maxline, 6, 6)); ScatterChartSeries data_val = data.addSerie(xs, ys_val); data_val.setTitle(Config.getResource("TitleMeasuredValues")); ScatterChartSeries data_sens = data.addSerie(xs, ys_sens); data_sens.setTitle(Config.getResource("TitleInstrumentSens")); chart.plot(data, bottomAxis, leftAxis); } FileOutputStream fileOut = new FileOutputStream(filename); wb.write(fileOut); fileOut.close(); return true; } catch (Exception e) { Utils.MessageBox(Config.getResource("MsgErrorXlsx") + "\n" + e.toString(), Config.getResource("TitleError")); return false; } finally { if (wb != null) try { wb.close(); } catch (IOException e) { } } }