List of usage examples for org.apache.poi.xssf.usermodel XSSFCellStyle setDataFormat
public void setDataFormat(int fmt)
From source file:es.tena.foundation.util.POIUtil.java
/** * Crea un map con los estilos para cada tipo de datos * * @param wb/*from www . ja v a 2 s . c o m*/ * @return un hashMap con las claves 'porcentaje', 'coeficiente', 'moneda', * 'fecha' y 'cabecera' */ public static Map<String, XSSFCellStyle> createStyles(XSSFWorkbook wb) { Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat fmt = wb.createDataFormat(); XSSFCellStyle style1 = wb.createCellStyle(); style1.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style1.setDataFormat(fmt.getFormat("0.0%")); styles.put("porcentaje", style1); XSSFCellStyle style2 = wb.createCellStyle(); style2.setAlignment(XSSFCellStyle.ALIGN_CENTER); style2.setDataFormat(fmt.getFormat("0.0X")); styles.put("coeficiente", style2); XSSFCellStyle style3 = wb.createCellStyle(); style3.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style3.setDataFormat(fmt.getFormat("#,##0.00")); styles.put("moneda", style3); XSSFCellStyle style4 = wb.createCellStyle(); style4.setAlignment(XSSFCellStyle.ALIGN_RIGHT); style4.setDataFormat(fmt.getFormat("mmm dd")); styles.put("fecha", style4); XSSFCellStyle style5 = wb.createCellStyle(); XSSFFont headerFont = wb.createFont(); headerFont.setBold(true); style5.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); style5.setFillPattern(XSSFCellStyle.SOLID_FOREGROUND); style5.setFont(headerFont); styles.put("cabecera", style5); return styles; }
From source file:jp.ac.utokyo.rcast.karkinos.summary.SummaryDB.java
License:Apache License
private static void setRow(List sl, Row row, XSSFWorkbook wb) { XSSFCellStyle styleDouble = wb.createCellStyle(); XSSFDataFormat fd = wb.createDataFormat(); styleDouble.setDataFormat(fd.getFormat("0.00E+00")); int n = 0;/* w w w. j av a 2 s. c o m*/ for (Object o : sl) { Cell c = row.createCell(n); if (o instanceof String) { c.setCellValue((String) o); } else if (o instanceof Double) { double d = (Double) o; if (d == Double.NEGATIVE_INFINITY) { c.setCellValue("n.v."); } else { c.setCellValue(d); if (d < 0.01) { c.setCellStyle(styleDouble); } } } else if (o instanceof Integer) { c.setCellValue((Integer) o); } else { c.setCellValue(String.valueOf(o)); } n++; } }
From source file:net.openchrom.msd.converter.supplier.excel.io.ChromatogramWriter.java
License:Open Source License
private Map<String, XSSFCellStyle> createStyles(XSSFWorkbook excelWorkbook) { Map<String, XSSFCellStyle> styles = new HashMap<String, XSSFCellStyle>(); XSSFDataFormat dataFormat = excelWorkbook.createDataFormat(); /*/*from w ww . j a va 2 s.c om*/ * Abundance values */ XSSFCellStyle style1 = excelWorkbook.createCellStyle(); style1.setAlignment(XSSFCellStyle.ALIGN_CENTER); style1.setDataFormat(dataFormat.getFormat("0.000")); styles.put(ABUNDANCE_STYLE, style1); /* * Styles to use. */ return styles; }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createEventSheets(final XSSFWorkbook workbook) { // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need CreationHelper helper = workbook.getCreationHelper(); final XSSFCellStyle dataStyle = workbook.createCellStyle(); dataStyle.setAlignment(HorizontalAlignment.RIGHT); final XSSFCellStyle headerStyle = workbook.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold);/*from w w w. j a va2 s. c om*/ final XSSFCellStyle dateStyle = workbook.createCellStyle(); dateStyle.setDataFormat(helper.createDataFormat().getFormat("HH:mm:ss")); // Calculate a good window size ResultService resultService = getResultService(); EventRecord firstResult = resultService.getFirstResult(); EventRecord lastResult = resultService.getLastResult(); if (firstResult == null || lastResult == null) { return; } long start = firstResult.getStartTime(); long end = lastResult.getStartTime(); long windowSize = AbstractEventReporter.getWindowSize(start, end, 100); // Well-known window sizes // Keep track of sheets by event name. Note that XLSX truncates sheets to 31 chars, so use 28 chars and ~01, ~02 final Map<String, String> sheetNames = new HashMap<String, String>(31); final Map<String, XSSFSheet> sheets = new HashMap<String, XSSFSheet>(31); final Map<String, AtomicInteger> rowNums = new HashMap<String, AtomicInteger>(31); ResultHandler handler = new ResultHandler() { @Override public boolean processResult(long fromTime, long toTime, Map<String, DescriptiveStatistics> statsByEventName, Map<String, Integer> failuresByEventName) throws Throwable { // Get or create a sheet for each event for (String eventName : statsByEventName.keySet()) { // What sheet name to we use? String sheetName = sheetNames.get(eventName); if (sheetName == null) { sheetName = eventName; if (eventName.length() > 28) { int counter = 1; // Find a sheet name not in use while (true) { sheetName = eventName.substring(0, 28); sheetName = String.format("%s~%02d", sheetName, counter); // Have we used this, yet? if (sheets.containsKey(sheetName)) { // Yes, we have used it. counter++; continue; } // This is unique break; } } sheetNames.put(eventName, sheetName); } // Get and create the sheet, if necessary XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { // Create try { sheet = workbook.createSheet(sheetName); sheets.put(sheetName, sheet); sheet.getHeader().setCenter(title + " - " + eventName); sheet.getPrintSetup().setFitWidth((short) 1); sheet.getPrintSetup().setLandscape(true); } catch (Exception e) { logger.error("Unable to create workbook sheet for event: " + eventName, e); continue; } // Intro XSSFCell cell = sheet.createRow(0).createCell(0); cell.setCellValue(title + " - " + eventName + ":"); cell.setCellStyle(headerStyle); // Headings XSSFRow row = sheet.createRow(1); cell = row.createCell(0); cell.setCellStyle(headerStyle); cell.setCellValue("time"); cell = row.createCell(1); cell.setCellStyle(headerStyle); cell.setCellValue("mean"); cell = row.createCell(2); cell.setCellStyle(headerStyle); cell.setCellValue("min"); cell = row.createCell(3); cell.setCellStyle(headerStyle); cell.setCellValue("max"); cell = row.createCell(4); cell.setCellStyle(headerStyle); cell.setCellValue("stdDev"); cell = row.createCell(5); cell.setCellStyle(headerStyle); cell.setCellValue("num"); cell = row.createCell(6); cell.setCellStyle(headerStyle); cell.setCellValue("numPerSec"); cell = row.createCell(7); cell.setCellStyle(headerStyle); cell.setCellValue("fail"); cell = row.createCell(8); cell.setCellStyle(headerStyle); cell.setCellValue("failPerSec"); // Size the columns sheet.autoSizeColumn(0); sheet.autoSizeColumn(1); sheet.autoSizeColumn(2); sheet.autoSizeColumn(3); sheet.autoSizeColumn(4); sheet.autoSizeColumn(5); sheet.autoSizeColumn(6); sheet.autoSizeColumn(7); sheet.autoSizeColumn(8); } AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { rowNum = new AtomicInteger(2); rowNums.put(eventName, rowNum); } DescriptiveStatistics stats = statsByEventName.get(eventName); Integer failures = failuresByEventName.get(eventName); double numPerSec = (double) stats.getN() / ((double) (toTime - fromTime) / 1000.0); double failuresPerSec = (double) failures / ((double) (toTime - fromTime) / 1000.0); XSSFRow row = sheet.createRow(rowNum.getAndIncrement()); XSSFCell cell; cell = row.createCell(0, Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(dateStyle); cell.setCellValue(new Date(toTime)); cell = row.createCell(5, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getN()); cell = row.createCell(6, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numPerSec); cell = row.createCell(7, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failures); cell = row.createCell(8, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(failuresPerSec); // Leave out values if there is no mean if (Double.isNaN(stats.getMean())) { continue; } cell = row.createCell(1, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMean()); cell = row.createCell(2, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMin()); cell = row.createCell(3, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getMax()); cell = row.createCell(4, Cell.CELL_TYPE_NUMERIC); cell.setCellValue(stats.getStandardDeviation()); } return true; } }; resultService.getResults(handler, start, windowSize, windowSize, false); // Create charts in the sheets for (String eventName : sheetNames.keySet()) { // Get the sheet name String sheetName = sheetNames.get(eventName); if (sheetName == null) { logger.error("Did not find sheet for event: " + eventName); continue; } // Get the sheet XSSFSheet sheet = sheets.get(sheetName); if (sheet == null) { logger.error("Did not find sheet for name: " + sheetName); continue; } // What row did we get up to AtomicInteger rowNum = rowNums.get(eventName); if (rowNum == null) { logger.error("Did not find row number for event: " + sheetName); continue; } // This axis is common to both charts ChartDataSource<Number> xTime = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 0, 0)); // Graph of event times XSSFDrawing drawingTimes = sheet.createDrawingPatriarch(); ClientAnchor anchorTimes = drawingTimes.createAnchor(0, 0, 0, 0, 0, 5, 15, 25); Chart chartTimes = drawingTimes.createChart(anchorTimes); ChartLegend legendTimes = chartTimes.getOrCreateLegend(); legendTimes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataTimes = chartTimes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisTimes = chartTimes.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); bottomAxisTimes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisTimes = chartTimes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Mean ChartDataSource<Number> yMean = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 1, 1)); LineChartSeries yMeanSerie = chartDataTimes.addSeries(xTime, yMean); yMeanSerie.setTitle(title + " - " + eventName + ": Mean (ms)"); // Std Dev ChartDataSource<Number> yStdDev = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 4, 4)); LineChartSeries yStdDevSerie = chartDataTimes.addSeries(xTime, yStdDev); yStdDevSerie.setTitle(title + " - " + eventName + ": Standard Deviation (ms)"); // Plot event times chartTimes.plot(chartDataTimes, bottomAxisTimes, leftAxisTimes); // Graph of event volumes // Graph of event times XSSFDrawing drawingVolumes = sheet.createDrawingPatriarch(); ClientAnchor anchorVolumes = drawingVolumes.createAnchor(0, 0, 0, 0, 0, 25, 15, 35); Chart chartVolumes = drawingVolumes.createChart(anchorVolumes); ChartLegend legendVolumes = chartVolumes.getOrCreateLegend(); legendVolumes.setPosition(LegendPosition.BOTTOM); LineChartData chartDataVolumes = chartVolumes.getChartDataFactory().createLineChartData(); ChartAxis bottomAxisVolumes = chartVolumes.getChartAxisFactory() .createCategoryAxis(AxisPosition.BOTTOM); bottomAxisVolumes.setNumberFormat("#,##0;-#,##0"); ValueAxis leftAxisVolumes = chartVolumes.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); // Number per second ChartDataSource<Number> yNumPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 6, 6)); LineChartSeries yNumPerSecSerie = chartDataVolumes.addSeries(xTime, yNumPerSec); yNumPerSecSerie.setTitle(title + " - " + eventName + ": Events per Second"); // Failures per second ChartDataSource<Number> yFailPerSec = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, rowNum.intValue() - 1, 8, 8)); LineChartSeries yFailPerSecSerie = chartDataVolumes.addSeries(xTime, yFailPerSec); yFailPerSecSerie.setTitle(title + " - " + eventName + ": Failures per Second"); // Plot volumes chartVolumes.plot(chartDataVolumes, bottomAxisVolumes, leftAxisVolumes); } }
From source file:org.apache.fineract.accounting.closure.storeglaccountbalance.service.GLClosureJournalEntryBalanceReadPlatformServiceImpl.java
License:Apache License
/** * Create the excel file with the balance report data * /*www . j a v a 2 s . co m*/ * @param reportDataList * @return {@link File} object */ @SuppressWarnings("unused") private File createGLClosureAccountBalanceReportExcelFile( final Collection<GLClosureAccountBalanceReportData> reportDataList) { File file = null; try { if (reportDataList != null) { final String[] columnTitles = new String[13]; columnTitles[0] = "AccountCostCentre"; columnTitles[1] = "AccountDepartment"; columnTitles[2] = "AccountNumber"; columnTitles[3] = "TransactionType"; columnTitles[4] = "TransactionDate"; columnTitles[5] = "GoodsAmount"; columnTitles[6] = "Reference"; columnTitles[7] = "Narrative"; columnTitles[8] = "UniqueReferenceNumber"; columnTitles[9] = "UserNumber"; columnTitles[10] = "Source"; columnTitles[11] = "PostedDate"; columnTitles[12] = "TransactionAnalysisCode"; XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet spreadsheet = workbook.createSheet(" nominaltransactions "); XSSFRow row; XSSFFont font; XSSFCellStyle style; XSSFDataFormat dataFormat; int rowId = 0; int cellId = 0; row = spreadsheet.createRow(rowId++); for (String columnTitle : columnTitles) { font = workbook.createFont(); style = workbook.createCellStyle(); font.setBold(true); font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); style.setFont(font); Cell cell = row.createCell(cellId++); cell.setCellValue(columnTitle); cell.setCellStyle(style); } for (GLClosureAccountBalanceReportData reportData : reportDataList) { row = spreadsheet.createRow(rowId++); font = workbook.createFont(); dataFormat = workbook.createDataFormat(); font.setFontName("Arial"); font.setFontHeightInPoints((short) 10); font.setBold(false); // ==================================================== Cell cell = row.createCell(2); style = workbook.createCellStyle(); cell.setCellType(Cell.CELL_TYPE_STRING); style.setDataFormat(dataFormat.getFormat("@")); style.setFont(font); cell.setCellValue(reportData.getAccountNumber()); cell.setCellStyle(style); // ==================================================== // ==================================================== if (reportData.getTransactionType() != null) { cell = row.createCell(3); style = workbook.createCellStyle(); style.setFont(font); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(reportData.getTransactionType().getValue()); cell.setCellStyle(style); } // ==================================================== // ==================================================== if (reportData.getTransactionDate() != null) { cell = row.createCell(4); style = workbook.createCellStyle(); Date transactionDate = reportData.getTransactionDate().toDate(); style.setDataFormat(dataFormat.getFormat("MM/DD/YY")); style.setFont(font); cell.setCellValue(transactionDate); cell.setCellStyle(style); } // ==================================================== // ==================================================== if (reportData.getAmount() != null) { cell = row.createCell(5); style = workbook.createCellStyle(); Double amount = reportData.getAmount().doubleValue(); style.setDataFormat(dataFormat.getFormat("0.00")); style.setFont(font); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(amount); cell.setCellStyle(style); } // ==================================================== // ==================================================== if (reportData.getReference() != null) { cell = row.createCell(6); style = workbook.createCellStyle(); cell.setCellType(Cell.CELL_TYPE_STRING); style.setDataFormat(dataFormat.getFormat("@")); style.setFont(font); cell.setCellValue(reportData.getReference()); cell.setCellStyle(style); } // ==================================================== // ==================================================== if (reportData.getPostedDate() != null) { cell = row.createCell(11); style = workbook.createCellStyle(); Date postedDate = reportData.getPostedDate().toDate(); style.setDataFormat(dataFormat.getFormat("MM/DD/YY")); style.setFont(font); cell.setCellValue(postedDate); cell.setCellStyle(style); } // ==================================================== } final String fileDirectory = FileSystemContentRepository.MIFOSX_BASE_DIR + File.separator + ""; if (!new File(fileDirectory).isDirectory()) { new File(fileDirectory).mkdirs(); } file = new File(fileDirectory + "gl_closure_account_balance_report.xls"); FileOutputStream fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); fileOutputStream.close(); } } catch (Exception exception) { logger.error(exception.getMessage(), exception); } return file; }
From source file:org.apache.fineract.infrastructure.dataexport.helper.XlsFileHelper.java
License:Apache License
public static void createFile(final SqlRowSet sqlRowSet, final File file, final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap, final DataExportCoreTable coreTable) { try {// w ww .j av a 2 s . c o m final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData(); final int columnCount = sqlRowSetMetaData.getColumnCount(); // Create a new spreadsheet workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet for the workbook XSSFSheet sheet = workbook.createSheet(); // create a new cell style object XSSFCellStyle cellStyle = workbook.createCellStyle(); // create a new data format object XSSFDataFormat dataFormat = workbook.createDataFormat(); int rowIndex = 0; int columnIndex = 0; Row row = sheet.createRow(rowIndex++); for (int i = 1; i <= columnCount; i++) { // create a new cell for each columns for the header row Cell cell = row.createCell(columnIndex++); // get the column label of the dataset String columnLabel = DataExportUtils .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable); // set the value of the cell cell.setCellValue(WordUtils.capitalize(columnLabel)); } while (sqlRowSet.next()) { columnIndex = 0; row = sheet.createRow(rowIndex++); for (int i = 1; i <= columnCount; i++) { Cell cell = row.createCell(columnIndex++); String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i); MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName); String columnValue = sqlRowSet.getString(i); String columnName = sqlRowSetMetaData.getColumnName(i); // replace code value id with the code value name AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType); // update the column value columnValue = columnValueDataType.getKey(); // update the data type mysqlDataType = columnValueDataType.getValue(); // replace app user id with respective username columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName, columnValue, mysqlDataType); // update the column value columnValue = columnValueDataType.getKey(); // update the data type mysqlDataType = columnValueDataType.getValue(); if (columnValue != null) { switch (mysqlDataType.getCategory()) { case NUMERIC: // TINYINT(1) is also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212 if (mysqlDataType.equals(MysqlDataType.TINYINT) && sqlRowSetMetaData.getPrecision(i) == 1 && (columnValue.equals("true") || columnValue.equals("false"))) { // Handle the cell as string, it is already a casted boolean: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columnValue); } else { double numberAsDouble = Double.parseDouble(columnValue); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numberAsDouble); } break; case DATE_TIME: DateFormat dateFormat; Date date; switch (mysqlDataType) { case DATE: case DATETIME: String mysqlDateFormat = "yyyy-MM-dd"; String excelDateFormat = "MM/DD/YYYY"; if (mysqlDataType.equals(MysqlDataType.DATETIME)) { mysqlDateFormat = "yyyy-MM-dd HH:mm:ss"; excelDateFormat = "MM/DD/YYYY HH:MM:SS"; } dateFormat = new SimpleDateFormat(mysqlDateFormat); date = dateFormat.parse(columnValue); cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat)); cell.setCellValue(date); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyle); break; default: cell.setCellValue(columnValue); break; } break; default: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columnValue); break; } } else { cell.setCellValue(columnValue); } } } //Write the workbook in file system FileOutputStream fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); fileOutputStream.close(); } catch (Exception exception) { exception.printStackTrace(); } }
From source file:org.displaytag.render.XssfTableWriter.java
License:Artistic License
/** * @see org.displaytag.render.TableWriterTemplate#writeColumnValue(Object,org.displaytag.model.Column) *///from w w w .ja va2 s .c o m protected void writeColumnValue(Object value, Column column) throws Exception { if (value instanceof Number) { Number num = (Number) value; // Percentage if (value.toString().indexOf("%") > -1) { this.currentCell.setCellValue(num.doubleValue() / 100); XSSFCellStyle cellStyle = this.wb.createCellStyle(); if (this.pctFormat == -1) { this.pctFormat = this.wb.createDataFormat().getFormat("0.00%"); } cellStyle.setDataFormat(this.pctFormat); this.currentCell.setCellStyle(cellStyle); } else { this.currentCell.setCellValue(num.doubleValue()); } } else if (value instanceof Date) { this.currentCell.setCellValue((Date) value); } else if (value instanceof Calendar) { this.currentCell.setCellValue((Calendar) value); } else { this.currentCell.setCellValue(new XSSFRichTextString(this.escapeColumnValue(value))); } }
From source file:org.mifosplatform.infrastructure.dataexport.helper.XlsFileHelper.java
License:Mozilla Public License
public static void createFile(final SqlRowSet sqlRowSet, final File file, final HashMap<Long, CodeValueData> codeValueMap, final HashMap<Long, AppUserData> appUserMap, final DataExportCoreTable coreTable) { try {/* w ww .ja va2s.com*/ final SqlRowSetMetaData sqlRowSetMetaData = sqlRowSet.getMetaData(); final int columnCount = sqlRowSetMetaData.getColumnCount(); // Create a new spreadsheet workbook XSSFWorkbook workbook = new XSSFWorkbook(); // Create a blank sheet for the workbook XSSFSheet sheet = workbook.createSheet(); // create a new cell style object XSSFCellStyle cellStyle = workbook.createCellStyle(); // create a new data format object XSSFDataFormat dataFormat = workbook.createDataFormat(); int rowIndex = 0; int columnIndex = 0; Row row = sheet.createRow(rowIndex++); for (int i = 1; i <= columnCount; i++) { // create a new cell for each columns for the header row Cell cell = row.createCell(columnIndex++); // get the column label of the dataset String columnLabel = DataExportUtils .createHumanReadableTableColumnLabel(sqlRowSetMetaData.getColumnLabel(i), coreTable); // set the value of the cell cell.setCellValue(WordUtils.capitalize(columnLabel)); } while (sqlRowSet.next()) { columnIndex = 0; row = sheet.createRow(rowIndex++); for (int i = 1; i <= columnCount; i++) { Cell cell = row.createCell(columnIndex++); String columnTypeName = sqlRowSetMetaData.getColumnTypeName(i); MysqlDataType mysqlDataType = MysqlDataType.newInstance(columnTypeName); String columnValue = sqlRowSet.getString(i); String columnName = sqlRowSetMetaData.getColumnName(i); // replace code value id with the code value name AbstractMap.SimpleEntry<String, MysqlDataType> columnValueDataType = DataExportUtils .replaceCodeValueIdWithValue(codeValueMap, columnName, columnValue, mysqlDataType); // update the column value columnValue = columnValueDataType.getKey(); // update the data type mysqlDataType = columnValueDataType.getValue(); // replace app user id with respective username columnValueDataType = DataExportUtils.replaceAppUserIdWithUserName(appUserMap, columnName, columnValue, mysqlDataType); // update the column value columnValue = columnValueDataType.getKey(); // update the data type mysqlDataType = columnValueDataType.getValue(); if (columnValue != null) { switch (mysqlDataType.getCategory()) { case NUMERIC: // TINYINT(1), BIT(1), etc are also treated as an alias for a BOOL in MySQL in certain versions of the JDBC connector, option tinyInt1isBit // See: http://stackoverflow.com/questions/16798744/why-does-tinyint1-function-as-a-boolean-but-int1-does-not/35488212#35488212 if (sqlRowSetMetaData.getPrecision(i) == 1 && (columnValue.equals("true") || columnValue.equals("false"))) { // Handle the cell as string, it is already a casted boolean: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columnValue); } else { double numberAsDouble = Double.parseDouble(columnValue); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellValue(numberAsDouble); } break; case DATE_TIME: DateFormat dateFormat; Date date; switch (mysqlDataType) { case DATE: case DATETIME: String mysqlDateFormat = "yyyy-MM-dd"; String excelDateFormat = "MM/DD/YYYY"; if (mysqlDataType.equals(MysqlDataType.DATETIME)) { mysqlDateFormat = "yyyy-MM-dd HH:mm:ss"; excelDateFormat = "MM/DD/YYYY HH:MM:SS"; } dateFormat = new SimpleDateFormat(mysqlDateFormat); date = dateFormat.parse(columnValue); cellStyle.setDataFormat(dataFormat.getFormat(excelDateFormat)); cell.setCellValue(date); cell.setCellType(Cell.CELL_TYPE_NUMERIC); cell.setCellStyle(cellStyle); break; default: cell.setCellValue(columnValue); break; } break; default: cell.setCellType(Cell.CELL_TYPE_STRING); cell.setCellValue(columnValue); break; } } else { cell.setCellValue(columnValue); } } } //Write the workbook in file system FileOutputStream fileOutputStream = new FileOutputStream(file); workbook.write(fileOutputStream); fileOutputStream.close(); } catch (Exception exception) { exception.printStackTrace(); } }
From source file:org.nekorp.workflow.desktop.servicio.reporte.global.GeneradorReporteGlobal.java
License:Apache License
@Override public void generaReporte(ParametrosReporteGlobal param) { FileOutputStream fileOut = null; try {/*from w w w.j a va2 s . c o m*/ //List<Servicio> datos = servicioDAO.getByDate(new DateTime("2013-05-1T00:00:00.000-00:00"), new DateTime("2013-05-1T23:59:59.999-00:00")); List<Servicio> datos = servicioDAO.getByDate(param.getFechaInicial(), param.getFechaFinal()); GeneradorReporteGlobal.LOGGER.debug("file:" + param.getDestination()); GeneradorReporteGlobal.LOGGER.debug("fecha Inicial:" + param.getFechaInicial()); GeneradorReporteGlobal.LOGGER.debug("fecha Final:" + param.getFechaFinal()); XSSFWorkbook wb = new XSSFWorkbook(); //estilo para las fechas XSSFCellStyle dateCellStyle = wb.createCellStyle(); CreationHelper createHelper = wb.getCreationHelper(); dateCellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); //estilos para las cantidades XSSFCellStyle moneyCellStyle = wb.createCellStyle(); XSSFDataFormat mf = wb.createDataFormat(); moneyCellStyle.setDataFormat(mf.getFormat("$#,##0.00")); Sheet sheet = wb.createSheet("Hoja1"); int rowCount = 1; Cell actual; Row r; for (Servicio x : datos) { RenglonRG datRen = renglonFactory.build(x); r = sheet.createRow(rowCount); rowCount = rowCount + 1; for (int i = 0; i < valueMap.length; i++) { actual = r.createCell(i); if (valueMap[i].getType() == CellValueType.TEXT) { Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue()); actual.setCellValue(obj.toString()); } if (valueMap[i].getType() == CellValueType.DATE) { Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue()); if (obj != null) { Date val = (Date) obj; actual.setCellValue(val); actual.setCellStyle(dateCellStyle); } } if (valueMap[i].getType() == CellValueType.AMOUNT) { Object obj = PropertyUtils.getProperty(datRen, valueMap[i].getValue()); double val = (double) obj; actual.setCellValue(val); actual.setCellStyle(moneyCellStyle); } if (valueMap[i].getType() == CellValueType.FORMULA) { String formulaRaw = valueMap[i].getValue(); ST formula = new ST(formulaRaw); formula.add("row", rowCount + ""); actual.setCellFormula(formula.render()); actual.setCellStyle(moneyCellStyle); } } } llenarEncabezado(sheet, 0, 0); fileOut = new FileOutputStream(param.getDestination()); wb.write(fileOut); } catch (IllegalAccessException | InvocationTargetException | NoSuchMethodException | FormulaParseException | IOException ex) { GeneradorReporteGlobal.LOGGER.error("error al generar reporte", ex); } finally { try { fileOut.close(); } catch (IOException | NullPointerException ex) { GeneradorReporteGlobal.LOGGER.error("error al cerrar archivo de reporte", ex); } } }
From source file:pe.gob.mef.gescon.util.ExcelUtil.java
/** * Mtodo que crea una celda con un determinado valor tipo decimal. * @param filaExcel Fila donde crear la celda, tipo Row. * @param posicion Posicin de la columna donde crear la celda, tipo int. * @param valor Valor decimal que tendr la celda, tipo Double. * @param estiloCelda Estilo de la celda, tipo XSSFCellStyle. *//*from ww w . jav a 2 s .c om*/ public void creaCelda(Row filaExcel, int posicion, Double valor, XSSFCellStyle estiloCelda) { this.celdaExcel = (Cell) filaExcel.createCell(posicion); estiloCelda.setDataFormat(this.libroExcel.getCreationHelper().createDataFormat().getFormat("#0.00")); this.celdaExcel.setCellValue(valor); this.celdaExcel.setCellStyle(estiloCelda); }