List of usage examples for org.apache.poi.ss.usermodel Sheet getPrintSetup
PrintSetup getPrintSetup();
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).// w ww .ja v a2 s . com * <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//from w w w. ja v a 2 s . 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;/* w w w. j a v a 2 s .c o m*/ 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;/*from w ww . j a va2s . 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("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:net.algem.planning.export.PlanningExportService.java
License:Open Source License
/** * Export to Excel destination file./*from ww w. ja va 2 s .c o m*/ * * @param dayPlan list of day schedules * @param destFile destination file * @throws IOException */ public void exportPlanning(List<DayPlan> dayPlan, File destFile) throws IOException { GemLogger.info("Exporting planning to " + destFile); Hour defStartTime = new Hour(ConfigUtil.getConf(ConfigKey.START_TIME.getKey())); int offset = defStartTime.getHour(); int totalh = 24 - offset; // total time length in hours HSSFWorkbook workbook = new HSSFWorkbook(); Sheet sheet = workbook.createSheet("Planning"); if (dayPlan.size() > 0) { DateFormat df = new SimpleDateFormat("EEEE dd MMM yyyy"); Header header = sheet.getHeader(); String hd = df.format(dayPlan.get(0).getSchedule().get(0).getDate().getDate()); header.setCenter(HSSFHeader.fontSize((short) 12) + HSSFHeader.startBold() + hd + HSSFHeader.endBold()); } PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); printSetup.setPaperSize(paperSize); sheet.setFitToPage(true); sheet.setHorizontallyCenter(false);// was true before 2.15.8 sheet.setMargin(Sheet.TopMargin, 0.75); // 1.905 sheet.setMargin(Sheet.BottomMargin, 0.4); // 0.4 inch = 1.016 cm sheet.setMargin(Sheet.LeftMargin, 0.4); sheet.setMargin(Sheet.RightMargin, 0.4); Map<String, CellStyle> styles = createStyles(workbook); Row headerRow = sheet.createRow(0); for (int i = 0; i < dayPlan.size(); i++) { Cell roomCell = headerRow.createCell(i + 1); // Set the width (in units of 1/256th of a character width) //sheet.setColumnWidth(i + 1, totalh * 256);// max number of characters must not depend of time length sheet.setColumnWidth(i + 1, 24 * 256); // cours.titre character varying(32) roomCell.setCellValue(dayPlan.get(i).getLabel()); roomCell.setCellStyle(styles.get("header")); } int offsetMn = offset * 60;// offset in minutes List<Row> rows = new ArrayList<>(); System.out.println(" offset = " + offset + " totalh = " + totalh); for (int t = 0, rowNumber = 1; t < totalh * 60; t += 5, rowNumber++) { // 1 row = 5mn Hour hour = new Hour(offsetMn + t); Row row = sheet.createRow(rowNumber); //row.setHeightInPoints(25); row.setHeightInPoints(PrintSetup.A3_PAPERSIZE == paperSize ? 12 : 6); // TIME SUBDIVISIONS if (t % 15 == 0) { Cell cell = row.createCell(0); if (t % 30 == 0) { cell.setCellValue(hour.toString());//show time if (t % 60 == 0) { cell.setCellStyle(styles.get("hour")); } else { cell.setCellStyle(styles.get("hour-half")); } } else { cell.setCellStyle(styles.get("hour-quarter")); } } else { // BETWEEN SUBDIVISION Cell cell = row.createCell(0); if ("23:55".equals(hour.toString())) { // last slice cell.setCellStyle(styles.get("hour-last")); } else { cell.setCellStyle(styles.get("hour")); } if (rowNumber % 3 == 0) { // merge every 3 rows sheet.addMergedRegion(new CellRangeAddress(rowNumber - 2, rowNumber, 0, 0)); } } rows.add(row); } Map<java.awt.Color, CellStyle> coursStyleCache = new HashMap<>(); for (int i = 0; i < dayPlan.size(); i++) { DayPlan plan = dayPlan.get(i); int col = i + 1; for (ScheduleObject event : plan.getSchedule()) { // if event starts before default starting time if (event.getStart().toMinutes() < offsetMn) { event.setStart(new Hour(offset * 60)); } int startRowPos = (event.getStart().toMinutes() - offsetMn) / 5 + 1; int endRowPos = (event.getEnd().toMinutes() - offsetMn) / 5; Cell courseCell = rows.get(startRowPos - 1).createCell(col); courseCell.setCellValue(getLabel(event, workbook));// title text CellStyle style = getCourseStyle(workbook, event, coursStyleCache); courseCell.setCellStyle(style); if (startRowPos != endRowPos) { sheet.addMergedRegion(new CellRangeAddress(startRowPos, endRowPos, col, col)); for (int row = startRowPos; row < endRowPos; row++) { rows.get(row).createCell(col).setCellStyle(style); } } } } try (FileOutputStream out = new FileOutputStream(destFile)) { workbook.write(out); } }
From source file:nz.ac.auckland.abi.formatting.poi.ModelJSONToExcel.java
License:LGPL
private void addWorkSheet(Workbook wb, String measure, List<FEMModelMeasure> measures, int maxTimePoints, Map<String, CellStyle> styles) { Sheet sheet = wb.createSheet(measure); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true);//www. j av a2 s .com sheet.setHorizontallyCenter(true); final int leadingHeaders = 4; int rowCounter = 0; // Create header row Row headerRow = sheet.createRow(rowCounter++); headerRow.setHeightInPoints(40); Cell headerCell; int colCtr = 0; { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("ModelName"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("StartTime"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("EndTime"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MetaData"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("ID"); headerCell.setCellStyle(styles.get("header")); } // Insert Time points for (int i = 0; i < maxTimePoints; i++) { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("" + i); headerCell.setCellStyle(styles.get("header")); } // Insert composite variables { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MAX"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MIN"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("MEAN"); headerCell.setCellStyle(styles.get("header")); } { headerCell = headerRow.createCell(colCtr++); headerCell.setCellValue("SD"); headerCell.setCellStyle(styles.get("header")); } // Output the values for each measure for (FEMModelMeasure mes : measures) { double[][] strains = mes.getMeasure(measure); if (strains == null) continue; int numRows = strains.length + 1; // 1 for Avg int rowStarts = rowCounter; int colCounter = 0; for (int rctr = 0; rctr < numRows - 1; rctr++) { colCounter = 0; int myRowID = rowCounter + 1; Row row = sheet.createRow(rowCounter++); for (int colc = 0; colc < leadingHeaders; colc++) { // Common // Elements row.createCell(colCounter++); } // Create ROW ID { Cell cell = row.createCell(colCounter++); cell.setCellValue("S" + (rctr + 1)); } String strainStartXLColName = getColumnPrefix(colCounter); int strainLength = strains[rctr].length; for (int stc = 0; stc < strainLength; stc++) { Cell cell = row.createCell(colCounter++); cell.setCellValue(strains[rctr][stc]); } String strainEndXLColName = getColumnPrefix(colCounter - 1); while (strainLength < maxTimePoints) { // Create dummy cells to // fill up space row.createCell(colCounter++); strainLength++; } // Add formulas and create names { //String modelName = mes.getModelName(); //String sname = ""; // MAX Cell cell = row.createCell(colCounter++); String ref = strainStartXLColName + "" + myRowID + ":" + strainEndXLColName + "" + myRowID; cell.setCellFormula("MAX(" + ref + ")"); cell.setCellStyle(styles.get("MAX")); /* * sname = * (modelName+"_"+measure+"_"+"MAX_S"+(rctr+1)..replaceAll * (" ", "_").trim(); Name namedCel = wb.createName(); * namedCel.setNameName(sname); String reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // MIN cell = row.createCell(colCounter++); cell.setCellFormula("MIN(" + ref + ")"); cell.setCellStyle(styles.get("MIN")); /* * sname = modelName+"_"+measure+"_"+"MIN_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // MEAN cell = row.createCell(colCounter++); cell.setCellFormula("AVERAGE(" + ref + ")"); cell.setCellStyle(styles.get("AVERAGE")); /* * sname = modelName+"_"+measure+"_"+"AVERAGE_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ // STANDARD DEVIATION cell = row.createCell(colCounter++); cell.setCellFormula("STDEV(" + ref + ")"); cell.setCellStyle(styles.get("STDEV")); /* * sname = modelName+"_"+measure+"_"+"STDEV_S"+(rctr+1); * namedCel = wb.createName(); namedCel.setNameName(sname); * reference = * measure+"!"+getColumnPrefix(colCounter-1)+""+myRowID; // * cell reference namedCel.setRefersToFormula(reference); */ } } // Add user defined series Hashtable<String, String> formulaMap = new Hashtable<String, String>(); for (String exp : userSeries) { // Replace all S[0-9]*, and D[0-9]* with appropriate column // values String toks[] = exp.split("="); int myRowID = rowCounter; colCounter = 0; Row row = sheet.createRow(rowCounter++); for (int colc = 0; colc < leadingHeaders; colc++) { // Common // Elements row.createCell(colCounter++); } // Create ROW ID { Cell cell = row.createCell(colCounter++); cell.setCellValue(toks[0]); } String strainStartXLColName = getColumnPrefix(colCounter); int strainLength = strains[numRows - 2].length; for (int stc = 0; stc < strainLength; stc++) { Cell cell = row.createCell(colCounter++); // Get the expression String expression = toks[1].toLowerCase();// Regex is case // senstive, // since th // COLUMN // PREFIXs are // CAPS, // replaceAll // will work as // expected else // S17 will // mathc for S1 // (but not s1) for (int sCtr = mes.numSegments; sCtr > 0; sCtr--) { String XLColName = (char) ('A' + stc + leadingHeaders + 1) + "" + (rowStarts + sCtr); // Note // excel // formulas // need // base // 1 expression = expression.replaceAll("s" + sCtr + "{1}", XLColName); } cell.setCellFormula(expression); cell.setCellStyle(styles.get("AVGSERIES")); } String strainEndXLColName = getColumnPrefix(colCounter - 1); while (strainLength < maxTimePoints) { // Create dummy cells to // fill up space row.createCell(colCounter++); strainLength++; } // Add formulas and create names { StringBuffer formulas = new StringBuffer(); String modelName = mes.getModelName(); char c = modelName.charAt(0); if (c >= '0' && c <= '9') { modelName = "_" + modelName; } String measureName = measure.replaceAll("\\(", "_").replaceAll("\\)", "_").replaceAll(" ", ""); String sname = ""; // MAX Cell cell = row.createCell(colCounter++); String ref = strainStartXLColName + myRowID + ":" + strainEndXLColName + myRowID; cell.setCellFormula("MAX(" + ref + ")"); cell.setCellStyle(styles.get("MAX")); sname = (modelName + "_" + measureName + "_" + "MAX_" + toks[0]).replaceAll(" ", "_").trim(); Name namedCel = wb.createName(); namedCel.setNameName(sname); String reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base try { namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // MIN cell = row.createCell(colCounter++); cell.setCellFormula("MIN(" + ref + ")"); cell.setCellStyle(styles.get("MIN")); sname = (modelName + "_" + measureName + "_" + "MIN_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // MEAN cell = row.createCell(colCounter++); cell.setCellFormula("AVERAGE(" + ref + ")"); cell.setCellStyle(styles.get("AVERAGE")); sname = (modelName + "_" + measureName + "_" + "AVERAGE_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference + "#"); // STANDARD DEVIATION cell = row.createCell(colCounter++); cell.setCellFormula("STDEV(" + ref + ")"); cell.setCellStyle(styles.get("STDEV")); sname = (modelName + "_" + measureName + "_" + "STDEV_" + toks[0]).replaceAll(" ", "_") .trim(); namedCel = wb.createName(); namedCel.setNameName(sname); reference = measureName + "!" + getColumnPrefix(colCounter - 1) + (myRowID + 1); // cell // reference // in // xl // base namedCel.setRefersToFormula(reference); formulas.append(reference); formulaMap.put(toks[0], formulas.toString()); } catch (Exception exx) { //exx.printStackTrace(); System.out.println(exx + " occured for formula " + reference); } } } mes.addToFormulaMap(measure, formulaMap); // Set the commom columns Row row; Cell cell; row = sheet.getRow(rowStarts); cell = row.getCell(0); cell.setCellValue(mes.getModelName()); cell = row.getCell(1); cell.setCellValue(mes.getStartTime()); cell = row.getCell(2); cell.setCellValue(mes.getEndTime()); cell = row.getCell(3); cell.setCellValue(mes.getMetaData()); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$" + (rowStarts + 1) + ":$A$" + (rowCounter))); // Since // excel // number // starts // from // 1 // but // api // is // 0 sheet.addMergedRegion(CellRangeAddress.valueOf("$B$" + (rowStarts + 1) + ":$B$" + (rowCounter))); sheet.addMergedRegion(CellRangeAddress.valueOf("$C$" + (rowStarts + 1) + ":$C$" + (rowCounter))); sheet.addMergedRegion(CellRangeAddress.valueOf("$D$" + (rowStarts + 1) + ":$D$" + (rowCounter))); sheet.createRow(rowCounter++);// Create Empty row for model break } }
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * //w w w . j a va2 s.co m * method name : getExcelSurveyReport * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : Get Streaming excel object for valid/invalid survey report * * Date : Mar 16, 2016 1:23:57 PM */ public OutputStream getExcelSurveyReport(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramStaffRole = params.get(Constants.CONST_ROLE_STAFF); String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String paramSemesterCode = params.get(Constants.CONST_PARAM_SEMESTER_CODE); String titleRegion = null; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; List<ReportSummary> reportSummaries = (List<ReportSummary>) object; if (templateName.equals(Constants.CONST_VALID_SURVEY_REPORT)) { sheet = workbook.createSheet( UtilProperty.getMessage("prop.course.teaching.survey.report.survey.valid", null, locale)); } if (templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { sheet = workbook.createSheet( UtilProperty.getMessage("prop.course.teaching.survey.report.survey.invalid", null, locale)); } sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey + " - " + paramSemesterCode); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey + " - " + paramSemesterCode); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD) && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.university", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.college", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.rank.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); } cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.instructor.id", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.instructor.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.college", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) { cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); } cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.course.code", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.student.registered", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.mean", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.response.percentage", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (ReportSummary reportSummary : reportSummaries) { int colNum = 0; Row row = sheet.createRow((short) rowNum); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD) && !templateName.equals(Constants.CONST_INVALID_SURVEY_REPORT)) { row.createCell(colNum++).setCellValue(reportSummary.getUniversityRank()); row.createCell(colNum++).setCellValue(reportSummary.getCollegeRank()); row.createCell(colNum++).setCellValue(reportSummary.getDepartmentRank()); } row.createCell(colNum++).setCellValue(Double.parseDouble(reportSummary.getEmpNumber())); row.createCell(colNum++).setCellValue(creationHelper.createRichTextString(reportSummary.getEmpName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(reportSummary.getCollegeCode())); if (!paramStaffRole.equals(Constants.CONST_ROLE_STAFF_HOD)) { row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(reportSummary.getDepartmentName())); } row.createCell(colNum++).setCellValue(reportSummary.getCourseCode()); row.createCell(colNum++).setCellValue(Integer.parseInt(reportSummary.getSectionNo())); row.createCell(colNum++).setCellValue(reportSummary.getRegisteredStudent()); Cell cellStudentNoResponse = row.createCell(colNum++); cellStudentNoResponse.setCellValue(reportSummary.getStudentNoResponse()); cellStudentNoResponse.setCellStyle(styles.get(FORMULA_1)); Cell cellTeachingMean = row.createCell(colNum++); cellTeachingMean.setCellValue(reportSummary.getTeachingMean()); cellTeachingMean.setCellStyle(styles.get(FORMULA_1)); Cell cellTeachingPercentageFavor = row.createCell(colNum++); cellTeachingPercentageFavor.setCellValue(reportSummary.getTeachingPercentageFavor()); cellTeachingPercentageFavor.setCellStyle(styles.get(FORMULA_1)); Cell cellQuestionMean = row.createCell(colNum++); cellQuestionMean.setCellValue(reportSummary.getQuestionMean()); cellQuestionMean.setCellStyle(styles.get(FORMULA_1)); Cell cellQuestionPercentageFavor = row.createCell(colNum++); cellQuestionPercentageFavor.setCellValue(reportSummary.getQuestionPercentageFavor()); cellQuestionPercentageFavor.setCellStyle(styles.get(FORMULA_1)); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }
From source file:om.edu.squ.squportal.portlet.tsurvey.dao.excel.TeachingSurveyExcelImpl.java
License:Open Source License
/** * /* w w w . j a va2 s .c om*/ * method name : getExcelCollegeCoursesAsstDean * @param templateName * @param object * @param response * @param params * @param locale * @return * @throws DocumentException * @throws IOException * TeachingSurveyExcelImpl * return type : OutputStream * * purpose : * * Date : Jun 7, 2016 11:49:24 AM */ public OutputStream getExcelCollegeCoursesAsstDean(String templateName, Object object, ResourceResponse response, Map<String, String> params, Locale locale) throws DocumentException, IOException { int colHead = 0; int rowNum = 0; String paramTypeSurvey = params.get(Constants.CONST_PARAM_TYPE_SURVEY); String titleRegion = null; List<StudentResponse> studentResponses = (List<StudentResponse>) object; Workbook workbook = new HSSFWorkbook(); CreationHelper creationHelper = workbook.getCreationHelper(); Map<String, CellStyle> styles = createStyles(workbook); Sheet sheet = null; Cell cellSH = null; sheet = workbook .createSheet(UtilProperty.getMessage("prop.course.teaching.survey.courses.list", null, locale)); sheet.getPrintSetup().setLandscape(true); sheet.getPrintSetup().setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); /** Header Footer **/ Footer footer = sheet.getFooter(); Header header = sheet.getHeader(); footer.setRight("Page &P of &N"); footer.setLeft("&D"); header.setLeft(UtilProperty.getMessage("prop.course.teaching.survey.heading", null, locale)); header.setCenter(UtilProperty.getMessage("prop.course.teaching.survey.title", null, locale)); header.setRight(paramTypeSurvey); sheet.setRepeatingRows(CellRangeAddress.valueOf("2:2")); sheet.setDisplayGridlines(true); sheet.setPrintGridlines(true); /** Title **/ Row titleRow = sheet.createRow(rowNum); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue(paramTypeSurvey); titleCell.setCellStyle(styles.get(TITLE)); ++rowNum; titleRegion = "$A$" + rowNum + ":$O$" + rowNum; sheet.addMergedRegion(CellRangeAddress.valueOf(titleRegion)); /** Header Row **/ Row rowSubHeader = sheet.createRow(rowNum++); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.analysis.department", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper .createRichTextString(UtilProperty.getMessage("prop.course.teaching.survey.course", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.section", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.number", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.committee.member.name", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.seats.taken", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.response.students", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); cellSH = rowSubHeader.createCell(colHead++); cellSH.setCellValue(creationHelper.createRichTextString( UtilProperty.getMessage("prop.course.teaching.survey.include.exclude", null, locale))); cellSH.setCellStyle(styles.get(SUB_HEADER)); /** Report details **/ for (StudentResponse studentResponse : studentResponses) { int colNum = 0; Row row = sheet.createRow((short) rowNum); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getDepartmentName())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getCourseCode())); row.createCell(colNum++).setCellValue(Integer.parseInt(studentResponse.getSectionNo())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpNumber())); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getEmpName())); row.createCell(colNum++).setCellValue(studentResponse.getSeatsTaken()); row.createCell(colNum++).setCellValue(studentResponse.getStudentResponse()); row.createCell(colNum++) .setCellValue(creationHelper.createRichTextString(studentResponse.getIncludeExclude())); rowNum++; } response.setContentType("application/vnd.ms-excel"); OutputStream outputStream = response.getPortletOutputStream(); workbook.write(outputStream); outputStream.flush(); outputStream.close(); return null; }
From source file:org.aio.handy.poi.TimesheetDemo.java
License:Apache License
public static void main(String[] args) throws Exception { Workbook wb;//from w w w . j ava2 s . c o 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("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 = "e:/timesheet.xls"; if (wb instanceof XSSFWorkbook) file += "x"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); }
From source file:org.bbreak.excella.reports.ReportsTestUtil.java
License:Open Source License
/** * /*from w w w . j a v a2s. co m*/ * * @param expected * @param actual * @param isActCopyOfExp ??????true * @throws ReportsCheckException */ public static void checkSheet(Sheet expected, Sheet actual, boolean isActCopyOfExp) throws ReportsCheckException { List<CheckMessage> errors = new ArrayList<CheckMessage>(); Workbook expectedWorkbook = expected.getWorkbook(); Workbook actualWorkbook = actual.getWorkbook(); if (log.isDebugEnabled()) { log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)) + "] check start!"); } // ---------------------- // ???? // ---------------------- // ?? String eSheetName = expectedWorkbook.getSheetName(expectedWorkbook.getSheetIndex(expected)); String aSheetName = actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)); if (!isActCopyOfExp) { if (!eSheetName.equals(aSheetName)) { errors.add(new CheckMessage("??", eSheetName, aSheetName)); } } // ? String ePrintSetupString = getPrintSetupString(expected.getPrintSetup()); String aPrintSetupString = getPrintSetupString(actual.getPrintSetup()); if (!ePrintSetupString.equals(aPrintSetupString)) { errors.add(new CheckMessage("?", ePrintSetupString, aPrintSetupString)); } // ? String eHeaderString = getHeaderString(expected.getHeader()); String aHeaderString = getHeaderString(actual.getHeader()); if (!eHeaderString.equals(aHeaderString)) { errors.add(new CheckMessage("", eHeaderString, aHeaderString)); } String eFooterString = getFooterString(expected.getFooter()); String aFooterString = getFooterString(actual.getFooter()); if (!eFooterString.equals(aFooterString)) { errors.add(new CheckMessage("", eFooterString, aFooterString)); } // String eBreaksString = getBreaksString(expected); String aBreaksString = getBreaksString(actual); log.debug(eBreaksString + "/" + aBreaksString); if (!eBreaksString.equals(aBreaksString)) { errors.add(new CheckMessage("", eBreaksString, aBreaksString)); } // ? String expectedPrintArea = expectedWorkbook.getPrintArea(expectedWorkbook.getSheetIndex(expected)); String actualPrintArea = actualWorkbook.getPrintArea(actualWorkbook.getSheetIndex(actual)); if (expectedPrintArea != null || actualPrintArea != null) { // ????????Null????????????? // if ( expectedPrintArea == null || actualPrintArea == null || !equalPrintArea( expectedPrintArea, actualPrintArea, isActCopyOfExp)) { // errors.add( new CheckMessage( "?", expectedPrintArea, actualPrintArea)); // } if (!isActCopyOfExp) { if (expectedPrintArea == null || actualPrintArea == null || !expectedPrintArea.equals(actualPrintArea)) { errors.add(new CheckMessage("?", expectedPrintArea, actualPrintArea)); } } } // (?) String ePaneInformationString = getPaneInformationString(expected.getPaneInformation()); String aPaneInformationString = getPaneInformationString(actual.getPaneInformation()); if (!ePaneInformationString.equals(aPaneInformationString)) { errors.add(new CheckMessage("(?)", expectedPrintArea, actualPrintArea)); } // ?????? // ????? // ????? // // if (expected.isDisplayGridlines() ^ actual.isDisplayGridlines()) { errors.add(new CheckMessage("", String.valueOf(expected.isDisplayGridlines()), String.valueOf(actual.isDisplayGridlines()))); } // ? if (expected.isDisplayRowColHeadings() ^ actual.isDisplayRowColHeadings()) { errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayRowColHeadings()), String.valueOf(actual.isDisplayRowColHeadings()))); } // ? if (expected.isDisplayFormulas() ^ actual.isDisplayFormulas()) { errors.add(new CheckMessage("?", String.valueOf(expected.isDisplayFormulas()), String.valueOf(actual.isDisplayFormulas()))); } // ?? if (expected.getNumMergedRegions() != actual.getNumMergedRegions()) { errors.add(new CheckMessage("??", String.valueOf(expected.getNumMergedRegions()), String.valueOf(actual.getNumMergedRegions()))); } for (int i = 0; i < actual.getNumMergedRegions(); i++) { CellRangeAddress actualAddress = null; if (expected instanceof HSSFSheet) { actualAddress = ((HSSFSheet) actual).getMergedRegion(i); } else if (expected instanceof XSSFSheet) { actualAddress = ((XSSFSheet) actual).getMergedRegion(i); } StringBuffer expectedAdressBuffer = new StringBuffer(); boolean equalAddress = false; for (int j = 0; j < expected.getNumMergedRegions(); j++) { CellRangeAddress expectedAddress = null; if (expected instanceof HSSFSheet) { expectedAddress = ((HSSFSheet) expected).getMergedRegion(j); } else if (expected instanceof XSSFSheet) { expectedAddress = ((XSSFSheet) expected).getMergedRegion(j); } if (expectedAddress.toString().equals(actualAddress.toString())) { equalAddress = true; break; } CellReference crA = new CellReference(expectedAddress.getFirstRow(), expectedAddress.getFirstColumn()); CellReference crB = new CellReference(expectedAddress.getLastRow(), expectedAddress.getLastColumn()); expectedAdressBuffer.append(" [" + crA.formatAsString() + ":" + crB.formatAsString() + "]"); } if (!equalAddress) { errors.add(new CheckMessage("??", expectedAdressBuffer.toString(), actualAddress.toString())); } } int maxColumnNum = -1; if (expected instanceof HSSFSheet) { maxColumnNum = HSSF_MAX_COLUMN_NUMBER; } else if (expected instanceof XSSFSheet) { maxColumnNum = XSSF_MAX_COLUMN_NUMBER; } for (int i = 0; i < maxColumnNum; i++) { try { // checkCellStyle(expected.getWorkbook(), expected.getColumnStyle(i), actual.getWorkbook(), actual.getColumnStyle(i)); } catch (ReportsCheckException e) { CheckMessage checkMessage = e.getCheckMessages().iterator().next(); checkMessage.setMessage("[" + i + "]" + checkMessage.getMessage()); errors.add(checkMessage); } // if (expected.getColumnWidth(i) != actual.getColumnWidth(i)) { errors.add(new CheckMessage("[" + i + "]", String.valueOf(expected.getColumnWidth(i)), String.valueOf(actual.getColumnWidth(i)))); } } // ??? if (expected.getLastRowNum() != actual.getLastRowNum()) { // ?????? if (expected.getLastRowNum() < actual.getLastRowNum()) { int lastRowIndex = -1; if (expected instanceof HSSFSheet) { lastRowIndex = 0; } Iterator<Row> rowIterator = actual.rowIterator(); while (rowIterator.hasNext()) { Row row = rowIterator.next(); // ????? Iterator<Cell> cellIterator = row.cellIterator(); while (cellIterator.hasNext()) { Cell cell = cellIterator.next(); if (cell.getCellTypeEnum() != CellType.BLANK) { lastRowIndex = row.getRowNum(); break; } } } if (expected.getLastRowNum() != lastRowIndex) { errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()), String.valueOf(lastRowIndex))); } } else { errors.add(new CheckMessage("", String.valueOf(expected.getLastRowNum()), String.valueOf(actual.getLastRowNum()))); } } if (errors.isEmpty()) { for (int i = 0; i <= expected.getLastRowNum(); i++) { try { checkRow(expected.getRow(i), actual.getRow(i)); } catch (ReportsCheckException e) { errors.addAll(e.getCheckMessages()); } } } if (!errors.isEmpty()) { if (log.isErrorEnabled()) { for (CheckMessage message : errors) { log.error("?[" + message.getMessage() + "]"); log.error(":" + message.getExpected()); log.error(":" + message.getActual()); } } throw new ReportsCheckException(errors); } if (log.isDebugEnabled()) { log.debug("[" + actualWorkbook.getSheetName(actualWorkbook.getSheetIndex(actual)) + "] check end."); } }