List of usage examples for org.apache.poi.ss.util CellRangeAddress valueOf
public static CellRangeAddress valueOf(String ref)
From source file:itpreneurs.itp.report.archive.LoanCalculator.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w ww. j a va 2 s. co m if (args.length > 0 && args[0].equals("-xls")) wb = new HSSFWorkbook(); else wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Loan Calculator"); sheet.setPrintGridlines(false); sheet.setDisplayGridlines(false); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); sheet.setColumnWidth(0, 3 * 256); sheet.setColumnWidth(1, 3 * 256); sheet.setColumnWidth(2, 11 * 256); sheet.setColumnWidth(3, 14 * 256); sheet.setColumnWidth(4, 14 * 256); sheet.setColumnWidth(5, 14 * 256); sheet.setColumnWidth(6, 14 * 256); createNames(wb); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(35); for (int i = 1; i <= 7; i++) { titleRow.createCell(i).setCellStyle(styles.get("title")); } Cell titleCell = titleRow.getCell(2); titleCell.setCellValue("Simple Loan Calculator"); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$1:$H$1")); Row row = sheet.createRow(2); Cell cell = row.createCell(4); cell.setCellValue("Enter values"); cell.setCellStyle(styles.get("item_right")); row = sheet.createRow(3); cell = row.createCell(2); cell.setCellValue("Loan amount"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_$")); cell.setAsActiveCell(); row = sheet.createRow(4); cell = row.createCell(2); cell.setCellValue("Annual interest rate"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_%")); row = sheet.createRow(5); cell = row.createCell(2); cell.setCellValue("Loan period in years"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_i")); row = sheet.createRow(6); cell = row.createCell(2); cell.setCellValue("Start date of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellStyle(styles.get("input_d")); row = sheet.createRow(8); cell = row.createCell(2); cell.setCellValue("Monthly payment"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(9); cell = row.createCell(2); cell.setCellValue("Number of payments"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Loan_Years*12,\"\")"); cell.setCellStyle(styles.get("formula_i")); row = sheet.createRow(10); cell = row.createCell(2); cell.setCellValue("Total interest"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Total_Cost-Loan_Amount,\"\")"); cell.setCellStyle(styles.get("formula_$")); row = sheet.createRow(11); cell = row.createCell(2); cell.setCellValue("Total cost of loan"); cell.setCellStyle(styles.get("item_left")); cell = row.createCell(4); cell.setCellFormula("IF(Values_Entered,Monthly_Payment*Number_of_Payments,\"\")"); cell.setCellStyle(styles.get("formula_$")); // Write the output to a file String file = "/Users/vincentgong/Documents/workspaces/Resource/itpreneurs/report/loan-calculator.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:ke.co.mspace.nonsmppmanager.service.SMSOutServiceImpl.java
@Override public void generateXSL(String user, String startDate, String endDate) { try {// ww w .j av a 2s . com HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); HSSFSheet sheet = wb.createSheet("Users_Sheet1"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("SMS OUT REPORT"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); String[] titles = { "Mobile", "Source Address", "Message", "Time Spent", "Last Update", "User", "Status", "No. of SMS" }; HSSFRow row = sheet.createRow(1); row.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = row.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } List<SMSOut> exportSMSOutReport = (List) userSMSOutReport(user, startDate, endDate).get("result"); int rowNum = 2; for (SMSOut anSMS : exportSMSOutReport) { row = sheet.createRow(rowNum); row.createCell(0).setCellValue(anSMS.getDestinationAddr()); row.createCell(1).setCellValue(anSMS.getSourceAddr()); row.createCell(2).setCellValue(anSMS.getMessagePayload()); row.createCell(3).setCellValue(anSMS.getTimeSubmitted()); row.createCell(4).setCellValue(anSMS.getTimeProcessed()); row.createCell(5).setCellValue(anSMS.getUser()); row.createCell(6).setCellValue(anSMS.getRealStatus()); row.createCell(7).setCellValue(anSMS.getSmsCount()); rowNum++; } sheet.setColumnWidth(0, 20 * 256); //30 characters wide sheet.setColumnWidth(1, 15 * 256); for (int i = 2; i < 5; i++) { sheet.setColumnWidth(i, 20 * 256); //6 characters wide } sheet.setColumnWidth(5, 10 * 256); sheet.setColumnWidth(6, 20 * 256); sheet.setColumnWidth(7, 10 * 256); //10 characters wide FacesContext context = FacesContext.getCurrentInstance(); HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse(); res.setContentType("application/vnd.ms-excel"); res.setHeader("Content-disposition", "attachment;filename=mydata.xlsx"); ServletOutputStream out = res.getOutputStream(); wb.write(out); out.flush(); out.close(); FacesContext.getCurrentInstance().responseComplete(); } catch (Exception e) { e.printStackTrace(); } }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
/** * Creates a Microsoft Excel Workbook containing Topup activity provided in * a CSV text file. The format of the created file will be Office Open XML * (OOXML)./* ww w.jav a 2s .c o m*/ * <p> * It expects the CSV to have the following columns from left to right:<br * /> * topup.uuid, topup.msisdn, topup.amount, network.name, topupStatus.status, * topup.topupTime * <p> * This method has been created to allow for large Excel files to be created * without overwhelming memory. * * * @param topupCSVFile a valid CSV text file. It should contain the full * path and name of the file e.g. "/tmp/export/topups.csv" * @param delimiter the delimiter used in the CSV file * @param excelFile the Microsoft Excel file to be created. It should * contain the full path and name of the file e.g. "/tmp/export/topups.xlsx" * @return whether the creation of the Excel file was successful or not */ public static boolean createExcelExport(final String topupCSVFile, final String delimiter, final String excelFile) { boolean success = true; int rowCount = 0; // To keep track of the row that we are on Row row; Map<String, CellStyle> styles; SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk // Each line of the file is approximated to be 200 bytes in size, // therefore 5000 lines are approximately 1 MB in memory // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk Sheet sheet = wb.createSheet("Airtime Topup"); styles = createStyles(wb); PrintSetup printSetupTopup = sheet.getPrintSetup(); printSetupTopup.setLandscape(true); sheet.setFitToPage(true); // Set up the heading to be seen in the Excel sheet row = sheet.createRow(rowCount); Cell titleCell; row.setHeightInPoints(45); titleCell = row.createCell(0); titleCell.setCellValue("Airtime Topups"); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); titleCell.setCellStyle(styles.get("title")); rowCount++; row = sheet.createRow(rowCount); row.setHeightInPoints(12.75f); for (int i = 0; i < TOPUP_TITLES.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(TOPUP_TITLES[i]); cell.setCellStyle(styles.get("header")); } rowCount++; FileUtils.deleteQuietly(new File(excelFile)); FileOutputStream out; try { FileUtils.touch(new File(excelFile)); // Read the CSV file and populate the Excel sheet with it LineIterator lineIter = FileUtils.lineIterator(new File(topupCSVFile)); String line; String[] lineTokens; int size; while (lineIter.hasNext()) { row = sheet.createRow(rowCount); line = lineIter.next(); lineTokens = StringUtils.split(line, delimiter); size = lineTokens.length; for (int cellnum = 0; cellnum < size; cellnum++) { Cell cell = row.createCell(cellnum); cell.setCellValue(lineTokens[cellnum]); } rowCount++; } out = new FileOutputStream(excelFile); wb.write(out); out.close(); } catch (FileNotFoundException e) { logger.error("FileNotFoundException while trying to create Excel file '" + excelFile + "' from CSV file '" + topupCSVFile + "'."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } catch (IOException e) { logger.error("IOException while trying to create Excel file '" + excelFile + "' from CSV file '" + topupCSVFile + "'."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } wb.dispose(); // dispose of temporary files backup of this workbook on disk return success; }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
/** * Used to create a MS Excel file from a list of * * @param topups// w ww . j av a 2s . c o m * @param networkHash a map with an UUID as the key and the name of the * network as the value * @param statusHash a map with an UUID as the key and the name of the * transaction status as the value * @param delimiter * @param excelFile the Microsoft Excel file to be created. It should * contain the full path and name of the file e.g. "/tmp/export/topups.xlsx" * @return whether the creation of the Excel file was successful or not */ public static boolean createExcelExport(final List<IncomingLog> topups, final HashMap<String, String> networkHash, final HashMap<String, String> statusHash, final String delimiter, final String excelFile) { boolean success = true; int rowCount = 0; // To keep track of the row that we are on Row row; Map<String, CellStyle> styles; SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk // Each line of the file is approximated to be 200 bytes in size, // therefore 5000 lines are approximately 1 MB in memory // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk Sheet sheet = wb.createSheet("Airtime Topup"); styles = createStyles(wb); PrintSetup printSetupTopup = sheet.getPrintSetup(); printSetupTopup.setLandscape(true); sheet.setFitToPage(true); // Set up the heading to be seen in the Excel sheet row = sheet.createRow(rowCount); Cell titleCell; row.setHeightInPoints(45); titleCell = row.createCell(0); titleCell.setCellValue("Airtime Topups"); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); titleCell.setCellStyle(styles.get("title")); rowCount++; row = sheet.createRow(rowCount); row.setHeightInPoints(12.75f); for (int i = 0; i < TOPUP_TITLES.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(TOPUP_TITLES[i]); cell.setCellStyle(styles.get("header")); } rowCount++; FileUtils.deleteQuietly(new File(excelFile)); FileOutputStream out; try { FileUtils.touch(new File(excelFile)); Cell cell; for (IncomingLog topup : topups) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue(topup.getUuid()); //cell = row.createCell(1); //cell.setCellValue(topup.getMessageid()); cell = row.createCell(2); cell.setCellValue(topup.getDestination()); cell = row.createCell(3); cell.setCellValue(networkHash.get(topup.getOrigin())); cell = row.createCell(4); cell.setCellValue(statusHash.get(topup.getMessage())); cell = row.createCell(5); cell.setCellValue(topup.getLogTime().toString()); rowCount++; } out = new FileOutputStream(excelFile); wb.write(out); out.close(); } catch (IOException e) { logger.error("IOException while trying to create Excel file '" + excelFile + "' from list of topups."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } wb.dispose(); // dispose of temporary files backup of this workbook on disk return success; }
From source file:ke.co.tawi.babblesms.server.utils.export.topups.AllTopupsExportUtil.java
License:Open Source License
public static boolean createExcelExport2(final List<OutgoingLog> topups, final HashMap<String, String> networkHash, final HashMap<String, String> statusHash, final String delimiter, final String excelFile) { boolean success = true; int rowCount = 0; // To keep track of the row that we are on Row row;//from ww w . j a v a 2 s . com Map<String, CellStyle> styles; SXSSFWorkbook wb = new SXSSFWorkbook(5000); // keep 5000 rows in memory, exceeding rows will be flushed to disk // Each line of the file is approximated to be 200 bytes in size, // therefore 5000 lines are approximately 1 MB in memory // wb.setCompressTempFiles(true); // temporary files will be gzipped on disk Sheet sheet = wb.createSheet("Airtime Topup"); styles = createStyles(wb); PrintSetup printSetupTopup = sheet.getPrintSetup(); printSetupTopup.setLandscape(true); sheet.setFitToPage(true); // Set up the heading to be seen in the Excel sheet row = sheet.createRow(rowCount); Cell titleCell; row.setHeightInPoints(45); titleCell = row.createCell(0); titleCell.setCellValue("Airtime Topups"); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); titleCell.setCellStyle(styles.get("title")); rowCount++; row = sheet.createRow(rowCount); row.setHeightInPoints(12.75f); for (int i = 0; i < TOPUP_TITLES.length; i++) { Cell cell = row.createCell(i); cell.setCellValue(TOPUP_TITLES[i]); cell.setCellStyle(styles.get("header")); } rowCount++; FileUtils.deleteQuietly(new File(excelFile)); FileOutputStream out; try { FileUtils.touch(new File(excelFile)); Cell cell; for (OutgoingLog topup : topups) { row = sheet.createRow(rowCount); cell = row.createCell(0); cell.setCellValue(topup.getUuid()); //cell = row.createCell(1); //cell.setCellValue(topup.getMessageid()); cell = row.createCell(2); cell.setCellValue(topup.getDestination()); cell = row.createCell(3); cell.setCellValue(networkHash.get(topup.getOrigin())); cell = row.createCell(4); cell.setCellValue(statusHash.get(topup.getMessage())); cell = row.createCell(5); cell.setCellValue(topup.getLogTime().toString()); rowCount++; } out = new FileOutputStream(excelFile); wb.write(out); out.close(); } catch (IOException e) { logger.error("IOException while trying to create Excel file '" + excelFile + "' from list of topups."); logger.error(ExceptionUtils.getStackTrace(e)); success = false; } wb.dispose(); // dispose of temporary files backup of this workbook on disk return success; }
From source file:mn.tsagaangeruud.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;/* w w w .j a va 2 s.c om*/ if (args.length > 0 && args[0].equals("-xls")) { wb = new HSSFWorkbook(); } else { wb = new XSSFWorkbook(); } Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("Timesheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Weekly Timesheet"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1")); //header row Row headerRow = sheet.createRow(1); headerRow.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = headerRow.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rownum = 2; for (int i = 0; i < 10; i++) { Row row = sheet.createRow(rownum++); for (int j = 0; j < titles.length; j++) { Cell cell = row.createCell(j); if (j == 9) { //the 10th cell contains sum over week days, e.g. SUM(C3:I3) String ref = "C" + rownum + ":I" + rownum; cell.setCellFormula("SUM(" + ref + ")"); cell.setCellStyle(styles.get("formula")); } else if (j == 11) { cell.setCellFormula("J" + rownum + "-K" + rownum); cell.setCellStyle(styles.get("formula")); } else { cell.setCellStyle(styles.get("cell")); } } } //row with totals below Row sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(35); Cell cell; cell = sumRow.createCell(0); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellValue("Total Hrs:"); cell.setCellStyle(styles.get("formula")); for (int j = 2; j < 12; j++) { cell = sumRow.createCell(j); String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; cell.setCellFormula("SUM(" + ref + ")"); if (j >= 9) cell.setCellStyle(styles.get("formula_2")); else cell.setCellStyle(styles.get("formula")); } rownum++; sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Regular Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("L13"); cell.setCellStyle(styles.get("formula_2")); sumRow = sheet.createRow(rownum++); sumRow.setHeightInPoints(25); cell = sumRow.createCell(0); cell.setCellValue("Total Overtime Hours"); cell.setCellStyle(styles.get("formula")); cell = sumRow.createCell(1); cell.setCellFormula("K13"); cell.setCellStyle(styles.get("formula_2")); //set sample data for (int i = 0; i < sample_data.length; i++) { Row row = sheet.getRow(2 + i); for (int j = 0; j < sample_data[i].length; j++) { if (sample_data[i][j] == null) continue; if (sample_data[i][j] instanceof String) { row.getCell(j).setCellValue((String) sample_data[i][j]); } else { row.getCell(j).setCellValue((Double) sample_data[i][j]); } } } //finally set column widths, the width is measured in units of 1/256th of a character width sheet.setColumnWidth(0, 30 * 256); //30 characters wide for (int i = 2; i < 9; i++) { sheet.setColumnWidth(i, 6 * 256); //6 characters wide } sheet.setColumnWidth(10, 10 * 256); //10 characters wide // Write the output to a file String file = "timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:model.Reports.java
public void generateXSL(List<SmsOutUserBean> smsOutUserBeans, int count) { try {// w w w . j av a 2 s . co m HSSFWorkbook wb = new HSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); HSSFSheet sheet = wb.createSheet("Users_Sheet1"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); //title row Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("SMS OUT REPORT"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$H$1")); String[] titles = { "Mobile", "Source Address", "Message", "Time Sent", "Last Update", "User", "Status", "Number of SMS", }; HSSFRow row = sheet.createRow(1); row.setHeightInPoints(40); Cell headerCell; for (int i = 0; i < titles.length; i++) { headerCell = row.createCell(i); headerCell.setCellValue(titles[i]); headerCell.setCellStyle(styles.get("header")); } int rowNum = 2; for (SmsOutUserBean bean : smsOutUserBeans) { row = sheet.createRow(rowNum); row.createCell(0).setCellValue(bean.getSmsOutModel().getDestinationAddress()); row.createCell(1).setCellValue(bean.getSmsOutModel().getSourceAddress()); row.createCell(2).setCellValue(bean.getSmsOutModel().getMessagePayload()); row.createCell(3) .setCellValue(HelperUtil.conDateToString(bean.getSmsOutModel().getTimeSubmitted())); row.createCell(4) .setCellValue(HelperUtil.conDateToString(bean.getSmsOutModel().getTimeProcessed())); row.createCell(5).setCellValue(bean.getUserBean().getUsername()); row.createCell(6).setCellValue(bean.getSmsOutModel().getRealStatus()); row.createCell(7).setCellValue(bean.getSmsOutModel().getSmsCount()); rowNum++; } sheet.setColumnWidth(0, 20 * 256); //30 characters wide sheet.setColumnWidth(1, 15 * 256); for (int i = 2; i < 5; i++) { sheet.setColumnWidth(i, 20 * 256); //6 characters wide } sheet.setColumnWidth(5, 10 * 256); sheet.setColumnWidth(6, 20 * 256); sheet.setColumnWidth(7, 10 * 256); //10 characters wide FacesContext context = FacesContext.getCurrentInstance(); HttpServletResponse res = (HttpServletResponse) context.getExternalContext().getResponse(); res.setContentType("application/vnd.ms-excel"); res.setHeader("Content-disposition", "attachment;filename=mydata.xls"); ServletOutputStream out = res.getOutputStream(); wb.write(out); out.flush(); out.close(); FacesContext.getCurrentInstance().responseComplete(); } catch (Exception e) { e.printStackTrace(); } }
From source file:net.ceos.project.poi.annotated.core.ConditionalFormattingHandler.java
License:Apache License
/** * Apply the conditional formatting according the values defined at the * respective annotation. Is only available at the declared object, in * another words, at the linked {@link Sheet} with the object. * //from w w w .ja v a 2 s .co m * @param configCriteria * the {@link XConfigCriteria} * @param conditionalFomat * the {@link XlsConditionalFormat} * @throws ConfigurationException */ protected static void applyCondition(XConfigCriteria configCriteria, XlsConditionalFormat conditionalFomat) throws ConfigurationException { // Define a Conditional Formatting rule, which triggers formatting // according the developer definition and applies patternFormatting SheetConditionalFormatting sheet = configCriteria.getSheet().getSheetConditionalFormatting(); /* apply all rules defined */ int i = 0; ConditionalFormattingRule[] rules = new ConditionalFormattingRule[conditionalFomat.rules().length]; XlsConditionalFormatRules[] rulesAnnotated = conditionalFomat.rules(); for (XlsConditionalFormatRules rule : rulesAnnotated) { ConditionalFormattingRule setRule = sheet.createConditionalFormattingRule(rule.operator(), rule.formula1(), StringUtils.isNotBlank(rule.formula2()) ? rule.formula2() : null); CellStyle decorator = null; try { decorator = configCriteria.getCellStyle(conditionalFomat.decorator()); } catch (ElementException e) { throw new ConfigurationException(ExceptionMessage.CONFIGURATION_DECORATOR_MISSING.getMessage(), e); } /* add FontFormatting */ FontFormatting fontFormat = setRule.createFontFormatting(); Font f = configCriteria.getWorkbook().getFontAt(decorator.getFontIndex()); fontFormat.setFontStyle(f.getItalic(), f.getBold()); fontFormat.setFontColorIndex(f.getColor()); fontFormat.setUnderlineType(f.getUnderline()); /* add BorderFormatting */ BorderFormatting borderFormat = setRule.createBorderFormatting(); borderFormat.setBorderBottom(decorator.getBorderBottom()); borderFormat.setBorderTop(decorator.getBorderTop()); borderFormat.setBorderLeft(decorator.getBorderLeft()); borderFormat.setBorderRight(decorator.getBorderRight()); borderFormat.setBottomBorderColor(decorator.getBottomBorderColor()); borderFormat.setTopBorderColor(decorator.getTopBorderColor()); borderFormat.setLeftBorderColor(decorator.getLeftBorderColor()); borderFormat.setRightBorderColor(decorator.getRightBorderColor()); /* add PatternFormatting */ PatternFormatting patternFormat = setRule.createPatternFormatting(); patternFormat.setFillBackgroundColor(decorator.getFillForegroundColor()); /* join rule */ rules[i++] = setRule; } /* Define a region */ CellRangeAddress[] regions = { CellRangeAddress.valueOf(CellFormulaConverter .calculateRangeAddressFromTemplate(configCriteria, conditionalFomat.rangeAddress())) }; /* Apply Conditional Formatting rule defined above to the regions */ sheet.addConditionalFormatting(regions, rules); }
From source file:net.sf.jasperreports.engine.export.JRXlsExporter.java
License:Open Source License
@Override protected void setAutoFilter(String autoFilterRange) { sheet.setAutoFilter(CellRangeAddress.valueOf(autoFilterRange)); }
From source file:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java
License:LGPL
private void createDashBoard(Sheet sheet, String[] measureNames, List<FEMModelMeasure> measures, Map<String, CellStyle> styles) { // Create header - metaData is expected to be csv // Each user variable has 4 additional columns int maxMetaDataColumns = getMaxMetaDataColumns(measures); int rowCounter = 0; // Create header row Row headerRow1 = sheet.createRow(rowCounter++); Row headerRow2 = sheet.createRow(rowCounter++); Row headerRow3 = sheet.createRow(rowCounter++); // Create column headers Cell headerCell;//from w w w. java 2 s . co m int colCtr = 0; { int colID = colCtr++; headerRow1.createCell(colID); headerRow2.createCell(colID); headerCell = headerRow3.createCell(colID); /* * headerCell.setCellValue("Sno"); * headerCell.setCellStyle(styles.get("header")); */ sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$A$3")); headerRow1.getCell(0).setCellValue("Sno"); headerRow1.getCell(0).setCellStyle(styles.get("header")); } for (int i = 0; i < maxMetaDataColumns; i++) { int colID = colCtr++; String xcolID = getColumnPrefix(colID); headerRow3.createCell(colID); headerRow2.createCell(colID); headerCell = headerRow1.createCell(colID); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + xcolID + "$1:$" + xcolID + "$3")); headerCell.setCellValue("Model Identifier " + i); headerCell.setCellStyle(styles.get("header")); } String[] compHeader = { "MAX", "MIN", "AVERAGE", "STDEV" }; for (int i = 0; i < userSeries.size(); i++) { int startColID = colCtr; // For each measure for (int j = 0; j < measureNames.length; j++) { int mstartColID = colCtr; // The four possible composites for (int k = 0; k < compHeader.length; k++) { int colID = colCtr++; headerRow1.createCell(colID); headerRow2.createCell(colID); headerCell = headerRow3.createCell(colID); headerCell.setCellValue(compHeader[k]); headerCell.setCellStyle(styles.get("header")); } headerRow2.getCell(mstartColID).setCellValue(measureNames[j]); headerRow2.getCell(mstartColID).setCellStyle(styles.get("formula")); String startColPrefix = getColumnPrefix(mstartColID); String endColPrefix = getColumnPrefix(colCtr - 1); sheet.addMergedRegion( CellRangeAddress.valueOf("$" + startColPrefix + "$2:$" + endColPrefix + "$2")); } String[] exp = userSeries.get(i).split("="); headerRow1.getCell(startColID).setCellValue(exp[0]); headerRow1.getCell(startColID).setCellStyle(styles.get("title")); String startColPrefix = getColumnPrefix(startColID); String endColPrefix = getColumnPrefix(colCtr - 1); sheet.addMergedRegion(CellRangeAddress.valueOf("$" + startColPrefix + "$1:$" + endColPrefix + "$1")); } // For each model output the data int ctr = 1; for (FEMModelMeasure model : measures) { Row row = sheet.createRow(rowCounter++); colCtr = 0; Cell cell = row.createCell(colCtr++); cell.setCellValue(ctr++); String[] metaData = model.getMetaData().split("\t"); int mLength = metaData.length; for (int i = 0; i < mLength; i++) { cell = row.createCell(colCtr++); cell.setCellValue(metaData[i]); } while (mLength < maxMetaDataColumns) { cell = row.createCell(colCtr++); mLength++; } // String modelName = model.getModelName().replaceAll(" ", // "_").trim();; for (int i = 0; i < userSeries.size(); i++) { String[] exp = userSeries.get(i).split("="); for (int j = 0; j < measureNames.length; j++) { double[][] strains = model.getMeasure(measureNames[j]); if (strains == null) continue; // Measure//userSeries Hashtable<String, String> map = model.getFormulaMap(measureNames[j]); try { String[] formulas = map.get(exp[0]).split("#"); for (int k = 0; k < compHeader.length; k++) { // String sname = // modelName+"_"+measureNames[j]+"_"+compHeader[k]+"_"+exp[0]; cell = row.createCell(colCtr++); cell.setCellFormula(formulas[k]); } } catch (Exception exx) { //exx.printStackTrace(); System.out.println(exx + " occured for expresion " + exp[0]); } } } } }