List of usage examples for org.apache.poi.xssf.usermodel XSSFSheet createRow
@Override public XSSFRow createRow(int rownum)
From source file:optrecursive_testbench.Save.java
public void save3D(Matrix matrice, String filename, int kj) throws FileNotFoundException, IOException { ///It is working String dirPath = OptRecursive_Testbench.excelFilePath + File.separator + filepath; setupDirectory(dirPath);//from ww w .j av a 2 s . c om String fileName = dirPath + File.separator + filename + kj + ".xlsx"; try { FileOutputStream fileOut = new FileOutputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet worksheet = workbook.createSheet("POI Worksheet"); int lastvaluex = matrice.getRowDimension(); int lastvaluey = matrice.getColumnDimension(); int ih = 0; int jh = 0; while (ih < lastvaluex) { XSSFRow row = worksheet.createRow(ih); while (jh < lastvaluey) { XSSFCell cell = row.createCell(jh); cell.setCellValue(matrice.get(ih, jh)); jh++; } ih++; jh = 0; } workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:optrecursive_testbench.Save.java
public void saveString(String[] matrice, String filename) throws FileNotFoundException, IOException { ///It is working String dirPath = OptRecursive_Testbench.excelFilePath + File.separator + filepath; setupDirectory(dirPath);/*from w w w .j a v a2 s .co m*/ String fileName = dirPath + File.separator + filename + ".xlsx"; try { FileOutputStream fileOut = new FileOutputStream(fileName); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet worksheet = workbook.createSheet("POI Worksheet"); int lastvaluex = matrice.length; int lastvaluey = 1; int ih = 0; int jh = 0; while (ih < lastvaluex) { XSSFRow row = worksheet.createRow(ih); while (jh < lastvaluey) { XSSFCell cell = row.createCell(jh); cell.setCellValue(matrice[ih]); jh++; } ih++; jh = 0; } workbook.write(fileOut); fileOut.flush(); fileOut.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } }
From source file:org.adimadim.kosu.MainForm.java
private void exportToExcel() throws Exception { List<RaceScore> raceScoreList = getRaceService().retrieveRaceScoresByRaceId(selectedRace.getRaceId()); XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("Sonuclar"); XSSFRow row = sheet.createRow(0); row.createCell(0).setCellValue("Id"); row.createCell(1).setCellValue("Gs No"); row.createCell(2).setCellValue("Ad"); row.createCell(3).setCellValue("Soyad"); row.createCell(4).setCellValue("Cinsiyet"); row.createCell(5).setCellValue("Sra"); int i = 1;// w w w . ja va2s. c o m for (RaceScore raceScore : raceScoreList) { XSSFRow tempRow = sheet.createRow(i); tempRow.createCell(0).setCellValue(raceScore.getAccount().getAccountId()); tempRow.createCell(1).setCellValue(raceScore.getAccount().getChestNumber()); tempRow.createCell(2).setCellValue(raceScore.getAccount().getName()); tempRow.createCell(3).setCellValue(raceScore.getAccount().getSurname()); tempRow.createCell(4).setCellValue(raceScore.getAccount().getGender()); tempRow.createCell(5).setCellValue(raceScore.getOrderNo()); i++; } String fileName = "sonuclar.xlsx"; try (FileOutputStream fos = new FileOutputStream(fileName)) { workbook.write(fos); } System.out.println(fileName + " written successfully"); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
/** * Create a 'Summary' sheet containing the table of averages *//*www . j av a 2 s.co m*/ private void createSummarySheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj = getTestService().getTestRunMetadata(test, run); // Create the sheet XSSFSheet sheet = workbook.createSheet("Summary"); // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need XSSFCellStyle summaryDataStyle = sheet.getWorkbook().createCellStyle(); summaryDataStyle.setAlignment(HorizontalAlignment.RIGHT); XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold); XSSFRow row = null; int rowCount = 0; row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Name:"); row.getCell(0).setCellStyle(headerStyle); row.getCell(1).setCellValue(title); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { String description = (String) testRunObj.get(FIELD_DESCRIPTION); description = description == null ? "" : description; row.getCell(0).setCellValue("Description:"); row.getCell(0).setCellStyle(headerStyle); row.getCell(1).setCellValue(description); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Progress (%):"); row.getCell(0).setCellStyle(headerStyle); Double progress = (Double) testRunObj.get(FIELD_PROGRESS); progress = progress == null ? 0.0 : progress; row.getCell(1).setCellValue(progress * 100); row.getCell(1).setCellType(XSSFCell.CELL_TYPE_NUMERIC); row.getCell(1).setCellStyle(summaryDataStyle); } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("State:"); row.getCell(0).setCellStyle(headerStyle); String state = (String) testRunObj.get(FIELD_STATE); if (state != null) { row.getCell(1).setCellValue(state); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Started:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_STARTED); if (time > 0) { row.getCell(1).setCellValue(FastDateFormat .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Finished:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_COMPLETED); if (time > 0) { row.getCell(1).setCellValue(FastDateFormat .getDateTimeInstance(FastDateFormat.MEDIUM, FastDateFormat.MEDIUM).format(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } row = sheet.createRow(rowCount++); { row.getCell(0).setCellValue("Duration:"); row.getCell(0).setCellStyle(headerStyle); Long time = (Long) testRunObj.get(FIELD_DURATION); if (time > 0) { row.getCell(1).setCellValue(DurationFormatUtils.formatDurationHMS(time)); row.getCell(1).setCellStyle(summaryDataStyle); } } rowCount++; rowCount++; // Create a header row row = sheet.createRow(rowCount++); // Header row String[] headers = new String[] { "Event Name", "Total Count", "Success Count", "Failure Count", "Success Rate (%)", "Min (ms)", "Max (ms)", "Arithmetic Mean (ms)", "Standard Deviation (ms)" }; int columnCount = 0; for (String header : headers) { XSSFCell cell = row.getCell(columnCount++); cell.setCellStyle(headerStyle); cell.setCellValue(header); } // Grab results and output them columnCount = 0; TreeMap<String, ResultSummary> summaries = collateResults(true); for (Map.Entry<String, ResultSummary> entry : summaries.entrySet()) { // Reset column count columnCount = 0; row = sheet.createRow(rowCount++); String eventName = entry.getKey(); ResultSummary summary = entry.getValue(); SummaryStatistics statsSuccess = summary.getStats(true); SummaryStatistics statsFail = summary.getStats(false); // Event Name row.getCell(columnCount++).setCellValue(eventName); // Total Count row.getCell(columnCount++).setCellValue(summary.getTotalResults()); // Success Count row.getCell(columnCount++).setCellValue(statsSuccess.getN()); // Failure Count row.getCell(columnCount++).setCellValue(statsFail.getN()); // Success Rate (%) row.getCell(columnCount++).setCellValue(summary.getSuccessPercentage()); // Min (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMin()); // Max (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMax()); // Arithmetic Mean (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getMean()); // Standard Deviation (ms) row.getCell(columnCount++).setCellValue((long) statsSuccess.getStandardDeviation()); } // Auto-size the columns for (int i = 0; i < 10; i++) { sheet.autoSizeColumn(i); } sheet.setColumnWidth(1, 5120); // Printing PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitWidth((short) 1); ps.setLandscape(true); // Header and footer sheet.getHeader().setCenter(title); }
From source file:org.alfresco.bm.report.XLSXReporter.java
License:Open Source License
private void createPropertiesSheet(XSSFWorkbook workbook) throws IOException, NotFoundException { DBObject testRunObj;/* www . ja va2 s. c o m*/ try { testRunObj = services.getTestDAO().getTestRun(test, run, true); } catch (ObjectNotFoundException e) { logger.error("Test run not found!", e); return; } // Ensure we don't leak passwords testRunObj = AbstractRestResource.maskValues(testRunObj); BasicDBList propertiesList = (BasicDBList) testRunObj.get(FIELD_PROPERTIES); if (propertiesList == null) { logger.error("Properties not found!"); return; } // Order the properties, nicely TreeMap<String, DBObject> properties = new TreeMap<String, DBObject>(); for (Object propertyObj : propertiesList) { DBObject property = (DBObject) propertyObj; String key = (String) property.get(FIELD_NAME); properties.put(key, property); } XSSFSheet sheet = workbook.createSheet("Properties"); // Create the fonts we need Font fontBold = workbook.createFont(); fontBold.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create the styles we need XSSFCellStyle propertyStyle = sheet.getWorkbook().createCellStyle(); propertyStyle.setAlignment(HorizontalAlignment.RIGHT); propertyStyle.setWrapText(true); XSSFCellStyle headerStyle = sheet.getWorkbook().createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.RIGHT); headerStyle.setFont(fontBold); XSSFRow row = null; int rowCount = 0; XSSFCell cell = null; int cellCount = 0; row = sheet.createRow(rowCount++); cell = row.createCell(cellCount++); { cell.setCellValue("Property"); cell.setCellStyle(headerStyle); } cell = row.createCell(cellCount++); { cell.setCellValue("Value"); cell.setCellStyle(headerStyle); } cell = row.createCell(cellCount++); { cell.setCellValue("Origin"); cell.setCellStyle(headerStyle); } cellCount = 0; // Iterate all the properties for the test run for (Map.Entry<String, DBObject> entry : properties.entrySet()) { DBObject property = entry.getValue(); String key = (String) property.get(FIELD_NAME); String value = (String) property.get(FIELD_VALUE); String origin = (String) property.get(FIELD_ORIGIN); row = sheet.createRow(rowCount++); cell = row.createCell(cellCount++); { cell.setCellValue(key); cell.setCellStyle(propertyStyle); } cell = row.createCell(cellCount++); { cell.setCellValue(value); cell.setCellStyle(propertyStyle); } cell = row.createCell(cellCount++); { cell.setCellValue(origin); cell.setCellStyle(propertyStyle); } // Back to first column cellCount = 0; } // Size the columns sheet.autoSizeColumn(0); sheet.setColumnWidth(1, 15360); sheet.autoSizeColumn(2); // Printing PrintSetup ps = sheet.getPrintSetup(); sheet.setAutobreaks(true); ps.setFitWidth((short) 1); ps.setLandscape(true); // Header and footer sheet.getHeader().setCenter(title); }
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.ja v a 2s . co m 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 * /*from www . jav 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 {/*from w ww.ja v a2 s .co 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.apache.ofbiz.pricat.AbstractPricatParser.java
License:Apache License
public void writeCommentsToFile(XSSFWorkbook workbook, XSSFSheet sheet) { report.println();// ww w. j a va2 s . c om report.print(UtilProperties.getMessage(resource, "WriteCommentsBackToExcel", locale), InterfaceReport.FORMAT_NOTE); FileOutputStream fos = null; XSSFCreationHelper factory = workbook.getCreationHelper(); XSSFFont boldFont = workbook.createFont(); boldFont.setFontName("Arial"); boldFont.setBold(true); boldFont.setCharSet(134); boldFont.setFontHeightInPoints((short) 9); XSSFFont plainFont = workbook.createFont(); plainFont.setFontName("Arial"); plainFont.setCharSet(134); plainFont.setFontHeightInPoints((short) 9); XSSFSheet errorSheet = null; if (errorMessages.keySet().size() > 0) { String errorSheetName = UtilDateTime.nowDateString("yyyy-MM-dd HHmm") + " Errors"; errorSheetName = WorkbookUtil.createSafeSheetName(errorSheetName); errorSheet = workbook.createSheet(errorSheetName); workbook.setSheetOrder(errorSheetName, 0); workbook.setActiveSheet(workbook.getSheetIndex(errorSheetName)); XSSFDrawing drawingPatriarch = errorSheet.getDrawingPatriarch(); if (drawingPatriarch == null) { drawingPatriarch = errorSheet.createDrawingPatriarch(); } for (int i = 0; i <= getHeaderRowNo(); i++) { XSSFRow newRow = errorSheet.createRow(i); XSSFRow row = sheet.getRow(i); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, drawingPatriarch); } // copy merged regions for (int i = 0; i < sheet.getNumMergedRegions(); i++) { CellRangeAddress mergedRegion = sheet.getMergedRegion(i); if (mergedRegion.getFirstRow() < getHeaderRowNo()) { errorSheet.addMergedRegion(mergedRegion); } } // copy images List<XSSFPictureData> pics = workbook.getAllPictures(); List<XSSFShape> shapes = sheet.getDrawingPatriarch().getShapes(); for (int i = 0; i < shapes.size(); i++) { XSSFShape shape = shapes.get(i); XSSFAnchor anchor = shape.getAnchor(); if (shape instanceof XSSFPicture && anchor instanceof XSSFClientAnchor) { XSSFPicture pic = (XSSFPicture) shape; XSSFClientAnchor clientAnchor = (XSSFClientAnchor) anchor; if (clientAnchor.getRow1() < getHeaderRowNo()) { for (int j = 0; j < pics.size(); j++) { XSSFPictureData picture = pics.get(j); if (picture.getPackagePart().getPartName() .equals(pic.getPictureData().getPackagePart().getPartName())) { drawingPatriarch.createPicture(clientAnchor, j); } } } } } } try { // set comments in the original sheet XSSFDrawing patriarch = sheet.getDrawingPatriarch(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFComment comment = sheet.getCellComment(new CellAddress(cell.getRow(), cell.getCol())); boolean isNewComment = false; if (comment == null) { XSSFClientAnchor anchor = factory.createClientAnchor(); anchor.setDx1(100); anchor.setDx2(100); anchor.setDy1(100); anchor.setDy2(100); anchor.setCol1(cell.getCol()); anchor.setCol2(cell.getCol() + 4); anchor.setRow1(cell.getRow()); anchor.setRow2(cell.getRow() + 4); anchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE); comment = patriarch.createCellComment(anchor); isNewComment = true; } XSSFRichTextString rts = factory.createRichTextString("OFBiz PriCat:\n"); rts.applyFont(boldFont); rts.append(errorMessages.get(cell), plainFont); comment.setString(rts); comment.setAuthor("Apache OFBiz PriCat"); if (isNewComment) { sheet.getRow(cell.getRow()).getCell(cell.getCol()).setCellComment(comment); OFBizPricatUtil.formatCommentShape(sheet, cell); } } } // set comments in the new error sheet XSSFDrawing errorPatriarch = errorSheet.getDrawingPatriarch(); int newRowNum = getHeaderRowNo() + 1; Map<Integer, Integer> rowMapping = new HashMap<Integer, Integer>(); for (CellReference cell : errorMessages.keySet()) { if (cell != null && errorMessages.get(cell) != null) { XSSFRow row = sheet.getRow(cell.getRow()); Integer rowNum = Integer.valueOf(row.getRowNum()); int errorRow = newRowNum; if (rowMapping.containsKey(rowNum)) { errorRow = rowMapping.get(rowNum).intValue(); } else { XSSFRow newRow = errorSheet.getRow(errorRow); if (newRow == null) { newRow = errorSheet.createRow(errorRow); } rowMapping.put(rowNum, Integer.valueOf(errorRow)); newRow.setHeight(row.getHeight()); copyRow(row, newRow, factory, errorPatriarch); newRowNum++; } } } // write to file if (sequenceNum > 0L) { File commentedExcel = FileUtil.getFile(tempFilesFolder + userLoginId + "/" + sequenceNum + ".xlsx"); fos = new FileOutputStream(commentedExcel); workbook.write(fos); } else { fos = new FileOutputStream(pricatFile); workbook.write(fos); } fos.flush(); fos.close(); workbook.close(); } catch (FileNotFoundException e) { report.println(e); Debug.logError(e, module); } catch (IOException e) { report.println(e); Debug.logError(e, module); } finally { if (fos != null) { try { fos.close(); } catch (IOException e) { Debug.logError(e, module); } } if (workbook != null) { try { workbook.close(); } catch (IOException e) { Debug.logError(e, module); } } } report.println(UtilProperties.getMessage(resource, "ok", locale), InterfaceReport.FORMAT_OK); report.println(); }
From source file:org.appdynamics.licensecount.file.WriteExcelDoc.java
public void init() { XSSFWorkbook workbook = new XSSFWorkbook(); //Create a blank sheet XSSFSheet licenseSummary = workbook.createSheet(LicenseS.LICENSE_SUMMARY); XSSFSheet licenseTiers = workbook.createSheet(LicenseS.TIER_SUMMARY); XSSFSheet licenseHourlyTiers = workbook.createSheet(LicenseS.HOURLY_TIER_SUMMARY); XSSFSheet licenseNodeInfo = workbook.createSheet(LicenseS.NODE_INFO_SUMMARY); XSSFSheet licenseNoNodeTiers = workbook.createSheet(LicenseS.TIERS_WITH_NO_NODES); style = workbook.createCellStyle();//from w w w . j av a 2 s . com style.setAlignment(HorizontalAlignment.RIGHT); addNodeInfo(licenseNodeInfo); addTierWNoNodeInfo(licenseNoNodeTiers); // Lets create the first row which will be the header. int headerRowIndex = 0; Row headerRow = licenseSummary.createRow(headerRowIndex); Row tierRow = licenseTiers.createRow(headerRowIndex); Row hourlyTierRow = licenseHourlyTiers.createRow(headerRowIndex); int i = 0; Cell cell_1 = headerRow.createCell(i); cell_1.setCellValue(LicenseS.CUSTOMER_NAME); Cell cell_2 = tierRow.createCell(i); cell_2.setCellValue(LicenseS.APPLICATION_NAME); cell_2 = tierRow.createCell(i + 1); cell_2.setCellValue(LicenseS.TIER_NAME); Cell cell_3 = hourlyTierRow.createCell(i); cell_3.setCellValue(LicenseS.APPLICATION_NAME); cell_3 = hourlyTierRow.createCell(i + 1); cell_3.setCellValue(LicenseS.TIER_NAME); i += 2; int columnCount = 2; int columnCount1 = 3; // Create the date headers for (CustomerLicenseRange cRange : customer.getCustomerRangeValues()) { cell_1 = headerRow.createCell(columnCount); cell_1.setCellValue(cRange.getColumnName()); cell_2 = tierRow.createCell(columnCount1); cell_2.setCellValue(cRange.getColumnName()); columnCount++; columnCount1++; } i = addCustomer(licenseSummary, i); //logger.log(Level.INFO,"Next row " + ++i); headerRow = licenseSummary.createRow(++i); cell_1 = headerRow.createCell(0); cell_1.setCellValue(LicenseS.APPLICATION_NAME); i++; int tierRowCount = 2; int createdHourlyTierHeader = 0; columnCount1 = 3; //logger.log(Level.INFO,new StringBuilder().append("\n\n\tNumber of applications ").append(customer.getApplications().size()).toString()); for (ApplicationLicenseCount app : customer.getApplications().values()) { i = addApplication(licenseSummary, i, app); int inCount = 0; for (TierLicenseCount tier : app.getTierLicenses().values()) { if (createdHourlyTierHeader == 0) { for (TierHourLicenseRange tr : tier.getTierHourLicenseRange()) { cell_3 = hourlyTierRow.createCell(columnCount1); cell_3.setCellValue(tr.getHourColumnName()); columnCount1++; } createdHourlyTierHeader = 1; } tierRowCount = addTier(licenseTiers, tierRowCount, tier, app.getApplicationName(), inCount); inCount++; } if (inCount != 0) tierRowCount++; i++; } tierRowCount = 2; for (ApplicationLicenseCount app : customer.getApplications().values()) { //i=addApplication(licenseSummary,i,app); int inCount = 0; tierRowCount = addHourlyApp(licenseHourlyTiers, tierRowCount, app, inCount); for (TierLicenseCount tier : app.getTierLicenses().values()) { tierRowCount = addHourlyTier(licenseHourlyTiers, tierRowCount, tier, app.getApplicationName(), inCount); inCount++; } tierRowCount++; i++; } try { //Write the workbook in file system //String fileName=new StringBuilder().append("/Users/gilbert.solorzano/Documents/").append(customer.getName()).append("LicenseFile.xlsx").toString(); FileOutputStream out = new FileOutputStream(new File(LicenseS.FILENAME_V)); workbook.write(out); out.close(); StringBuilder bud = new StringBuilder(); bud.append("Completed writing the file: ").append(LicenseS.FILENAME_V).append("."); logger.log(Level.INFO, bud.toString()); } catch (Exception e) { e.printStackTrace(); } }