public static void copyCell(Cell oldCell, Cell newCell, boolean copyStyle) { if (copyStyle) { newCell.setCellStyle(oldCell.getCellStyle()); } switch (oldCell.getCellType()) { case Cell.CELL_TYPE_STRING: newCell.setCellValue(oldCell.getRichStringCellValue()); break; case Cell.CELL_TYPE_NUMERIC: newCell.setCellValue(oldCell.getNumericCellValue()); break; case Cell.CELL_TYPE_BLANK: newCell.setCellType(Cell.CELL_TYPE_BLANK); break; case Cell.CELL_TYPE_BOOLEAN: newCell.setCellValue(oldCell.getBooleanCellValue()); break; case Cell.CELL_TYPE_ERROR: newCell.setCellErrorValue(oldCell.getErrorCellValue()); break; case Cell.CELL_TYPE_FORMULA: newCell.setCellFormula(oldCell.getCellFormula()); break; default: break; } }
public void insertFormula(int numRow, int numCol, String formula) { try { if (wb.getSheetAt(0) != null) { Sheet aSheet = wb.getSheetAt(0); Row row = aSheet.createRow((short) numRow); Cell csCell = row.createCell((short) numCol); csCell.setCellFormula(formula); } } catch (Exception e) { System.out.println("insertDataToExcel" + e); } }
@SuppressWarnings("unchecked") private void createBodyRow(Row currentRow, Object[] bodyRow, int[] types) { Cell currentCell; // write the section attributes to the row for (int index = 0; index < bodyRow.length; index++) { // create the cell currentCell = currentRow.createCell(index); // set the format of the cells currentCell.setCellStyle(bodyStyle); if (types[index] != Integer.MAX_VALUE) { currentCell.setCellType(types[index]); } // put the value into the cell switch (types[index]) { case Cell.CELL_TYPE_NUMERIC: currentCell.setCellValue((double) bodyRow[index]); break; case Cell.CELL_TYPE_FORMULA: currentCell.setCellFormula(String.valueOf(bodyRow[index])); break; case Integer.MAX_VALUE: HashMap<String, String> cellInfo = (HashMap<String, String>) bodyRow[index]; // sets the hyperlink to be blue and underline HSSFCellStyle hyperlinkStyle = (HSSFCellStyle) GetBodyCellStyle(workbook); Font hyperlinkFont = workbook.createFont(); hyperlinkFont.setUnderline(Font.U_SINGLE); hyperlinkFont.setColor(HSSFColor.BLUE.index); hyperlinkStyle.setFont(hyperlinkFont); currentCell.setCellStyle(hyperlinkStyle); currentCell.setCellValue(cellInfo.get("Label")); currentCell.setHyperlink(LinkDataToMap(cellInfo)); break; // MAX_VALUE integer will be used to denote hyperlinks case Cell.CELL_TYPE_STRING: currentCell.setCellValue(String.valueOf(bodyRow[index])); break; default: throw new UnsupportedOperationException( "The type " + types[index] + " is not supported." + " Only types 0, 1, 2, and Integer.MAX_VALUE (for hyperlinks)" + " are supported. See the javadoc for org.apache.poi.ss.usermodel.Cell " + "for more information on the types of cells."); } } }
private void addSumBottom() { for (int i = 0; i < book.getNumberOfSheets(); i++) { Sheet sheet = book.getSheetAt(i); Row row = sheet.createRow(sheet.getLastRowNum() + 1); row.setHeight((short) (ROW_HEIGHT + 100)); for (int j = 0; j < 1000000; j++) { if (StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(0).getCell(j))) && StringUtils.isBlank(CellUtils.getStringValue(sheet.getRow(2).getCell(j)))) { break; } Cell cell = row.createCell(j); cell.setCellStyle(Style.get(book).SUM); if (j == 0) { cell.setCellValue("合计"); } else { cell.setCellValue(0); } if (j >= 7) { cell.setCellType(Cell.CELL_TYPE_FORMULA); cell.setCellFormula( String.format( "SUM(%s%s:%s%s)", CellUtils.convertToABC(j + 1), 5, CellUtils.convertToABC(j + 1), sheet.getLastRowNum())); } } sheet.addMergedRegion( new CellRangeAddress(sheet.getLastRowNum(), sheet.getLastRowNum(), 0, 6)); } for (int i = 0; i < book.getNumberOfSheets(); i++) { Sheet sheet = book.getSheetAt(i); for (int j = 4; j <= sheet.getLastRowNum(); j++) { Row row = sheet.getRow(j); for (int k = 0; k <= row.getLastCellNum(); k++) { Cell cell = row.getCell(k); if (cell == null) { continue; } if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(k)))) { cell.setCellStyle(Style.get(book).SUM); } } } } }
/** Verify that FormulaParser handles defined names beginning with underscores, see Bug #49640 */ public void testFormulasWithUnderscore() { HSSFWorkbook wb = new HSSFWorkbook(); Name nm1 = wb.createName(); nm1.setNameName("_score1"); nm1.setRefersToFormula("A1"); Name nm2 = wb.createName(); nm2.setNameName("_score2"); nm2.setRefersToFormula("A2"); Sheet sheet = wb.createSheet(); Cell cell = sheet.createRow(0).createCell(2); cell.setCellFormula("_score1*SUM(_score1+_score2)"); assertEquals("_score1*SUM(_score1+_score2)", cell.getCellFormula()); }
/** * Discription:[写一行] * * @param row 行 * @param keyValues 一个beanMap对象,包含着这一行中的所有数据 * @param properties 哪些值需要写入行 * @param dateFormat 日期格式,默认:yyyy-MM-dd * @author:[代超] * @throws Exception * @update:[日期YYYY-MM-DD] [更改人姓名][变更描述] */ public void writeRow(Row row, Map keyValues, Map properties, String dateFormat, T t) throws Exception { // 表格内容样式 CellStyle contentStyle = setContentSheetSysle(row.getSheet().getWorkbook()); if (keyValues == null || keyValues.size() < 1 || row == null) { return; } if (dateFormat == null || "".equals(dateFormat.trim())) { dateFormat = "yyyy-MM-dd"; } Iterator it = keyValues.entrySet().iterator(); for (int i = 0; it.hasNext(); i++) { Map.Entry next = (Map.Entry) it.next(); String dataValue = ObjectUtils.toString(properties.get(next.getKey()), ""); if (dataValue == null || "".equals(dataValue.trim())) { // 无需导出当前字段 i--; continue; } Object value = keyValues.get(next.getKey()); // 值为空时仍然创建单元格并且赋予样式。 Cell cell = row.createCell(i); cell.setCellStyle(contentStyle); if (value == null) { // 当值为空的时候,不必做其他操作了 continue; } if (dataValue.toLowerCase().indexOf("formula") > -1) { // 公式 String formula = ExcelFormula.parseFormula(value.toString()) .replaceAll("-1", String.valueOf(cell.getRowIndex() + 1)); cell.setCellFormula(formula); } else { Class c = PropertyUtils.getPropertyType(t, next.getKey().toString()); writeCell(cell, value, c.getSimpleName(), dateFormat); } } }
public void writeFormulas(FormulaResolver formulaResolver) { Set sheetNames = sheetFormulasMap.keySet(); for (Iterator iterator = sheetNames.iterator(); iterator.hasNext(); ) { String sheetName = (String) iterator.next(); List formulas = (List) sheetFormulasMap.get(sheetName); for (int i = 0; i < formulas.size(); i++) { Formula formula = (Formula) formulas.get(i); String formulaString = formulaResolver.resolve(formula, null); if (formulaString != null) { Cell hssfCell = Util.getOrCreateCell( formula.getSheet().getPoiSheet(), formula.getRowNum(), formula.getCellNum()); try { hssfCell.setCellFormula(formulaString); } catch (RuntimeException e) { log.error("Can't set formula: " + formulaString, e); // hssfCell.setCellType( Cell.CELL_TYPE_BLANK ); throw new RuntimeException("Can't set formula: " + formulaString, e); } } } } }
public static void main(String[] args) throws Exception { Workbook wb; 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(); }
/** * 资产明细表 * * @param response * @param customer_2 * @param customer_0or1 * @throws IOException */ @RequestMapping("/report/frontequip/exportFrontEquipListReport_assetclean.do") public void exportFrontEquipListReport_assetclean( HttpServletResponse response, String customer_2, String customer_0or1) throws IOException { String customer_2_name = customerService.get(customer_2).getName(); String customer_0or1_name = customerService.get(customer_0or1).getName(); List<FrontEquipListReport> list = frontEquipReportRepository.queryFrontEquipListReport(customer_2, customer_0or1); List<FrontEquipListReport_subtype> list_subtype_prod = frontEquipReportRepository.queryFrontEquipListReport_header(customer_2, customer_0or1); XSSFWorkbook wb = new XSSFWorkbook(); Sheet sheet = wb.createSheet(); int rownum = 0; // 标题 Row title = sheet.createRow(rownum++); Cell title_cell = title.createCell(0); title_cell.setCellValue(customer_2_name + customer_0or1_name + "前端设备明细表"); CellStyle title_style = wb.createCellStyle(); Font title_font = wb.createFont(); title_font.setFontHeightInPoints((short) 16); // f.setColor(IndexedColors.RED.getIndex()); title_font.setBoldweight(Font.BOLDWEIGHT_BOLD); title_style.setFont(title_font); title_style.setAlignment(CellStyle.ALIGN_CENTER); title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); title_cell.setCellStyle(title_style); // 和并单元格 // sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) 15)); // =========================================================================================== rownum = exportFrontEquipListReport_header_assetclean(list_subtype_prod, rownum, wb, sheet); // ============================================================================================= // 开始构建整个excel的文件 // 处理对应的品名在excel的哪一列 Map<String, Integer> prod_col_index_map = new HashMap<String, Integer>(); int cellIndex = 3; for (FrontEquipListReport_subtype subtype : list_subtype_prod) { for (FrontEquipListReport_prod prod : subtype.getProds()) { prod_col_index_map.put(prod.getProd_id(), cellIndex); cellIndex = cellIndex + 2; } } if (list != null && list.size() > 0) { int i = 1; for (FrontEquipListReport customer : list) { Row row = sheet.createRow(rownum++); Cell cell_prod_0 = row.createCell(0); cell_prod_0.setCellValue(i); // cell_prod_0.setCellStyle(style); Cell cell_prod_1 = row.createCell(1); cell_prod_1.setCellValue(customer.getPole_code()); Cell cell_prod_2 = row.createCell(2); cell_prod_2.setCellValue(customer.getPole_name()); for (FrontEquipListReport_prod prod : customer.getProdes()) { Cell cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id())); cell_prod.setCellValue(prod.getNum()); // cell_prod.setCellStyle(style); cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()) + 1); cell_prod.setCellValue(prod.getValue_net().doubleValue()); } i++; } // 对标题行 进行单元格合并 sheet.addMergedRegion( new CellRangeAddress(0, (short) 0, 0, (short) prod_col_index_map.size())); } sheet.createFreezePane(3, 3); // 添加总计一行 CellStyle style_sum = wb.createCellStyle(); Font style_sum_font = wb.createFont(); style_sum_font.setFontHeightInPoints((short) 12); // f.setColor(IndexedColors.RED.getIndex()); style_sum_font.setBoldweight(Font.BOLDWEIGHT_BOLD); style_sum.setFont(style_sum_font); style_sum.setAlignment(CellStyle.ALIGN_RIGHT); style_sum.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Row row_sum = sheet.createRow(rownum++); Cell cell_sum_2 = row_sum.createCell(2); cell_sum_2.setCellValue("小计:"); cell_sum_2.setCellStyle(style_sum); for (int i = 3; i < cellIndex; i++) { String col = CellReference.convertNumToColString(i); Cell cell_sum = row_sum.createCell(i); // =SUM(C4:C29) 从第4行开始到最后一样 // cell_sum.setCellValue("SUM("+col+"4:"+col+(rownum-1)+")"); cell_sum.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")"); cell_sum.setCellStyle(style_sum); Cell cell_net = row_sum.createCell(i); cell_net.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")"); cell_net.setCellStyle(style_sum); } String filename = customer_2_name + customer_0or1_name + "前端设备明细表-净资产.xlsx"; // FileOutputStream out = new FileOutputStream(filename); response.setHeader( "content-disposition", "attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1")); // response.setContentType("application/vnd.ms-excel;charset=uft-8"); response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=uft-8"); OutputStream out = response.getOutputStream(); wb.write(out); out.flush(); out.close(); }
private void rebuildFormula(Sheet sheet) { ProductType type = ProductType.get(sheet.getSheetName()); SizeInfo sizeInfo = SizeInfo.getByType(type); // size -> list<columnIndex> Map<String, List<Integer>> columns = new HashMap<String, List<Integer>>(); for (int colIndex = 6 + sizeInfo.getSizeNumber() + 1; colIndex < sheet.getRow(2).getLastCellNum(); colIndex++) { Row row = sheet.getRow(2); String value = row.getCell(colIndex).getStringCellValue().trim(); if ("数量".equals(value) || StringUtils.isBlank(value)) { continue; } if (!columns.containsKey(value)) { columns.put(value, new ArrayList<Integer>()); } columns.get(value).add(colIndex); } // for(int rowIndex = 3; rowIndex <= sheet.getLastRowNum(); rowIndex++) { // Row row = sheet.getRow(rowIndex); // // for(int colIndex = 7; colIndex <= 7 + sizeRange - 1; colIndex++) { // String size = sheet.getRow(2).getCell(colIndex).getStringCellValue().trim(); // // List<Integer> cols = columns.get(size); // row.getCell(colIndex).setCellType(Cell.CELL_TYPE_FORMULA); // row.getCell(colIndex).setCellFormula(this.buildSumFormula(rowIndex, cols)); // } // } for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) { Row row = sheet.getRow(rowIndex); if (row == null) { continue; } for (int colIndex = 7; colIndex <= row.getLastCellNum(); colIndex++) { if ("数量".equals(CellUtils.getStringValue(sheet.getRow(2).getCell(colIndex)))) { Cell cell = row.getCell(colIndex); cell.setCellType(Cell.CELL_TYPE_FORMULA); String formula = String.format( "SUM(%s%s:%s%s)", CellUtils.convertToABC(colIndex - sizeInfo.getSizeNumber() + 1), rowIndex + 1, CellUtils.convertToABC(colIndex - 1 + 1), rowIndex + 1); cell.setCellFormula(formula); } } } for (int rowIndex = 4; rowIndex <= sheet.getLastRowNum(); rowIndex++) { Row row = sheet.getRow(rowIndex); if ("合计".equals(CellUtils.getStringValue(row.getCell(0)))) { for (int colIndex = 7; colIndex <= sheet.getLastRowNum(); colIndex++) { Cell cell = row.getCell(colIndex); if (cell == null) { continue; } cell.setCellType(Cell.CELL_TYPE_FORMULA); String formula = String.format( "SUM(%s%s:%s%s)", CellUtils.convertToABC(colIndex + 1), 5, CellUtils.convertToABC(colIndex + 1), rowIndex); cell.setCellFormula(formula); } } } }
void excelCopy() { FileInputStream file1; int lastRowUpdated = 0, currentRow = 0; int i = 0; try { file1 = new FileInputStream( "C:\\Users\\SharvaP\\Desktop\\Excel Format\\LookUp for Transaction Name.xls"); int columnIndex = 0; Workbook lookupWorkbook = new HSSFWorkbook(file1); Sheet lookupSheet = lookupWorkbook.getSheetAt(0); FileInputStream file2 = new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round1_temp.xls"); Workbook round1Workbook = new HSSFWorkbook(file2); Sheet round1Sheet = round1Workbook.getSheetAt(0); FileInputStream file3 = new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\template.xls"); Workbook templateWorkbook = new HSSFWorkbook(file3); Sheet templateSheet = templateWorkbook.getSheetAt(0); FileInputStream file4 = new FileInputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\round2_temp.xls"); Workbook round2Workbook = new HSSFWorkbook(file4); Sheet round2Sheet = round2Workbook.getSheetAt(0); int averageRow = round1Sheet.getPhysicalNumberOfRows() - 2; int percentileRow = round1Sheet.getPhysicalNumberOfRows() - 1; System.out.println("Average row1:" + averageRow); int averageRow2 = round2Sheet.getPhysicalNumberOfRows() - 2; int percentileRow2 = round2Sheet.getPhysicalNumberOfRows() - 1; /*Row round1Row = round1Sheet.getRow(averageRow); Row round2Row = round2Sheet.getRow(averageRow2); Cell tempRound1Cell=round1Row.getCell(columnIndex); Cell tempRound2Cell=round2Row.getCell(columnIndex); CellStyle cellStyle = templateWorkbook.createCellStyle(); CreationHelper createHelper=templateWorkbook.getCreationHelper(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("#")); */ Iterator<Row> rowIterator1 = lookupSheet.iterator(); while (rowIterator1.hasNext()) { Row lookupRow = rowIterator1.next(); if (lookupRow.getRowNum() > 0) { Cell lookupCell = lookupRow.getCell(1); String transationNameTemplate = lookupCell.getStringCellValue(); // System.out.println("NAme:"+transationNameTemplate); // System.out.println(templateSheet.getPhysicalNumberOfRows()); for (int count = lastRowUpdated; count < templateSheet.getPhysicalNumberOfRows() - 1; count++) { Row templateRow = templateSheet.getRow(count); Cell templateCell = templateRow.getCell(0); Cell templateCellFormulae1 = templateRow.getCell(5); Cell templateCellFormulae2 = templateRow.getCell(6); String templateCellName = templateCell.getStringCellValue(); // System.out.println("template cell name:"+templateCellName); currentRow = templateRow.getRowNum(); if (templateCellName.equals(transationNameTemplate)) { // System.out.println("Transac Name:"+templateCellName+", row num:"+currentRow+", last // updated row:"+lastRowUpdated); Row round1RowAverage = round1Sheet.getRow(averageRow); Cell round1CellAverage = round1RowAverage.getCell(columnIndex); Row round2RowAverage = round2Sheet.getRow(averageRow2); Cell round2CellAverage = round2RowAverage.getCell(columnIndex); Cell templateCellRound1Average = templateRow.getCell(1); Cell templateCellRound2Average = templateRow.getCell(3); templateCellFormulae1.setCellFormula( "IF(B" + (currentRow + 1) + ">D" + (currentRow + 1) + ",((B" + (currentRow + 1) + "-D" + (currentRow + 1) + ")/B" + (currentRow + 1) + ")*100,((B" + (currentRow + 1) + "-D" + (currentRow + 1) + ")/D" + (currentRow + 1) + ")*100)"); System.out.println(round1CellAverage.getNumericCellValue()); templateCellRound1Average.setCellValue(round1CellAverage.getNumericCellValue()); templateCellRound2Average.setCellValue(round2CellAverage.getNumericCellValue()); Row round1RowPercentile = round1Sheet.getRow(percentileRow); Cell round1CellPercentile = round1RowPercentile.getCell(columnIndex); Row round2RowPercentile = round2Sheet.getRow(percentileRow2); Cell round2CellPercentile = round2RowPercentile.getCell(columnIndex); Cell templateCellRound1Percentile = templateRow.getCell(2); Cell templateCellRound2Percentile = templateRow.getCell(4); templateCellFormulae2.setCellFormula( "IF(C" + (currentRow + 1) + ">E" + (currentRow + 1) + ",((C" + (currentRow + 1) + "-E" + (currentRow + 1) + ")/C" + (currentRow + 1) + ")*100,((C" + (currentRow + 1) + "-E" + (currentRow + 1) + ")/E" + (currentRow + 1) + ")*100)"); templateCellRound1Percentile.setCellValue(round1CellPercentile.getNumericCellValue()); templateCellRound2Percentile.setCellValue(round2CellPercentile.getNumericCellValue()); // System.out.println("round1 avg value:"+round1CellAverage.getNumericCellValue()); // System.out.println("round2 avg value:"+round2CellAverage.getNumericCellValue()); // System.out.println("Match Found"+templateRow.getCell(1).getNumericCellValue()); columnIndex++; lastRowUpdated = currentRow; break; } } } } FileOutputStream out = new FileOutputStream("C:\\Users\\SharvaP\\Desktop\\Excel Format\\Template.xls"); templateWorkbook.write(out); out.close(); System.out.println("Finished...."); file1.close(); file2.close(); file3.close(); file4.close(); } catch (FileNotFoundException e) { System.out.println(e.getMessage()); e.printStackTrace(); } catch (IOException e) { System.out.println(e.getMessage()); e.printStackTrace(); } }
/** * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods. * * @param request servlet request * @param response servlet response * @throws ServletException if a servlet-specific error occurs * @throws IOException if an I/O error occurs */ protected void processRequest(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException { HttpSession ss = request.getSession(); Account ac = (Account) ss.getAttribute("ac"); int cId = Integer.parseInt((Long) ss.getAttribute("cId") + ""); Course c = Course.getCourseByID(cId); Workbook wb = new XSSFWorkbook(); Map<String, CellStyle> styles = createStyles(wb); Sheet sheet = wb.createSheet("scoresheet"); PrintSetup printSetup = sheet.getPrintSetup(); printSetup.setLandscape(true); sheet.setFitToPage(true); sheet.setHorizontallyCenter(true); Row titleRow = sheet.createRow(0); titleRow.setHeightInPoints(45); Cell titleCell = titleRow.createCell(0); titleCell.setCellValue("Score sheet of " + c.getName() + " course"); titleCell.setCellStyle(styles.get("title")); sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$N$1")); List<Account> listStudentScore = (List<Account>) ss.getAttribute("listStudentScore"); int rownum = 2; int cellcount = 1; Row sumRow = sheet.createRow(rownum); sumRow.setHeightInPoints(55); Cell cell; cell = sumRow.createCell(0); cell.setCellValue("Student name"); cell.setCellStyle(styles.get("header")); int countback = listStudentScore.get(0).getListStudentScore().size(); int maxScore = 0; for (int i = countback - 1; i >= 0; i--) { cell = sumRow.createCell(cellcount); UserScore u = listStudentScore.get(0).getListStudentScore().get(i); cell.setCellValue("(" + cellcount + ") " + u.getAm_name() + " (" + u.getFull_mark() + ")"); cell.setCellStyle(styles.get("header")); cellcount++; maxScore += u.getFull_mark(); } cell = sumRow.createCell(cellcount); cell.setCellValue("Total (" + maxScore + ")"); cell.setCellStyle(styles.get("header")); rownum++; for (Account account : listStudentScore) { sumRow = sheet.createRow(rownum); sumRow.setHeightInPoints(35); cell = sumRow.createCell(0); cell.setCellValue(account.getFirstname() + " " + account.getLastname()); int j = 1; for (int i = account.getListStudentScore().size() - 1; i >= 0; i--) { UserScore usc = (UserScore) account.getListStudentScore().get(i); cell = sumRow.createCell(j); Assignment a = null; if (usc.getAss_type().equalsIgnoreCase("web")) { a = Assignment.getAmTimeByAmID(usc.getStof().getAm_id()); String status = Assignment.lastedSentStatus(usc.getStof().getLasted_send_date(), a); if (status.equalsIgnoreCase("ontime") || status.equalsIgnoreCase("hurryup") || status.equalsIgnoreCase("late")) { cell.setCellValue(usc.getStof().getScore()); } else { status = Assignment.calculateTime(a); if (status.equalsIgnoreCase("miss")) { cell.setCellValue(usc.getStof().getScore()); } else { cell.setCellValue("-"); } } } else if (usc.getAss_type().equalsIgnoreCase("file")) { a = Assignment.getAmTimeByAmID(usc.getStf().getAm_id()); String status = Assignment.lastedSentStatus(usc.getStf().getLasted_send_date(), a); if (status.equalsIgnoreCase("ontime") || status.equalsIgnoreCase("hurryup") || status.equalsIgnoreCase("late")) { cell.setCellValue(usc.getStf().getScore()); } else { status = Assignment.calculateTime(a); if (status.equalsIgnoreCase("miss")) { cell.setCellValue(usc.getStf().getScore()); } else { cell.setCellValue("-"); } } } j++; } cell = sumRow.createCell(j); int lastcol = account.getListStudentScore().size(); // calculate column int dv = lastcol / 26; String coltmp = ""; for (int i = 0; i < dv; i++) { coltmp += "A"; } coltmp += (char) ('A' + (lastcol - (dv * 26))); System.out.println(coltmp); // String ref = (char) ('A' + 1) + "" + (rownum + 1) + ":" + coltmp + (rownum + 1); System.out.println(ref); cell.setCellFormula("SUM(" + ref + ")"); rownum++; } // Write the output to a file String filename = "scoresheet_" + c.getName() + ".xlsx"; String file = getServletContext().getRealPath("/") + "/file/scoresheet/" + filename; // String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx"; FileOutputStream out = new FileOutputStream(file); wb.write(out); out.close(); response.sendRedirect("file/scoresheet/" + filename); // // Workbook wb = new XSSFWorkbook(); // Sheet sheet = wb.createSheet("scoresheet"); // 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("Score sheet of " + "...." + " course"); // sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$D$1")); // // //row with totals below // int rownum = 2; // Row sumRow = sheet.createRow(rownum); // sumRow.setHeightInPoints(35); // Cell cell; // cell = sumRow.createCell(0); // cell.setCellValue("Name:"); // // for (int j = 1; j < 12; j++) { // cell = sumRow.createCell(j); // String ref = (char) ('A' + j) + "3:" + (char) ('A' + j) + "12"; // cell.setCellFormula("SUM(" + ref + ")"); // } // // // Write the output to a file // String file = "C:\\Users\\Orarmor\\Desktop\\scoresheet.xlsx"; // FileOutputStream out = new FileOutputStream(file); // wb.write(out); // out.close(); }
/** Adds in a Row to the given Sheet */ public Row addRow( Workbook wb, SheetToAdd sheetToAdd, RowToAdd rowToAdd, int rowIndex, ReportData reportData, ReportDesign design, Map<String, String> repeatSections) { // Create a new row and copy over style attributes from the row to add Row newRow = sheetToAdd.getSheet().createRow(rowIndex); Row rowToClone = rowToAdd.getRowToClone(); try { CellStyle rowStyle = rowToClone.getRowStyle(); if (rowStyle != null) { newRow.setRowStyle(rowStyle); } } catch (Exception e) { // No idea why this is necessary, but this has thrown IndexOutOfBounds errors getting the // rowStyle. Mysteries of POI } newRow.setHeight(rowToClone.getHeight()); // Iterate across all of the cells in the row, and configure all those that need to be // added/cloned List<CellToAdd> cellsToAdd = new ArrayList<CellToAdd>(); int totalCells = rowToClone.getPhysicalNumberOfCells(); int cellsFound = 0; for (int cellNum = 0; cellsFound < totalCells; cellNum++) { Cell currentCell = rowToClone.getCell(cellNum); log.debug("Handling cell: " + currentCell); if (currentCell != null) { cellsFound++; } // If we find that the cell that we are on is a repeating cell, then add the appropriate // number of cells to clone String repeatingColumnProperty = getRepeatingColumnProperty(sheetToAdd.getOriginalSheetNum(), cellNum, repeatSections); if (repeatingColumnProperty != null) { String[] dataSetSpanSplit = repeatingColumnProperty.split(","); String dataSetName = dataSetSpanSplit[0]; DataSet dataSet = getDataSet(reportData, dataSetName, rowToAdd.getReplacementData()); int numCellsToRepeat = 1; if (dataSetSpanSplit.length == 2) { numCellsToRepeat = Integer.parseInt(dataSetSpanSplit[1]); } log.debug( "Repeating this cell with dataset: " + dataSet + " and repeat of " + numCellsToRepeat); int repeatNum = 0; for (DataSetRow dataSetRow : dataSet) { repeatNum++; for (int i = 0; i < numCellsToRepeat; i++) { Cell cell = (i == 0 ? currentCell : rowToClone.getCell(cellNum + i)); if (repeatNum == 1 && cell != null && cell != currentCell) { cellsFound++; } Map<String, Object> newReplacements = getReplacementData( rowToAdd.getReplacementData(), reportData, design, dataSetName, dataSetRow, repeatNum); cellsToAdd.add(new CellToAdd(cell, newReplacements)); log.debug("Adding " + cell + " with dataSetRow: " + dataSetRow); } } cellNum += numCellsToRepeat; } else { cellsToAdd.add(new CellToAdd(currentCell, rowToAdd.getReplacementData())); log.debug("Adding " + currentCell); } } // Now, go through all of the collected cells, and add them back in ExcelStyleHelper styleHelper = new ExcelStyleHelper(wb); String prefix = getExpressionPrefix(design); String suffix = getExpressionSuffix(design); for (int i = 0; i < cellsToAdd.size(); i++) { CellToAdd cellToAdd = cellsToAdd.get(i); Cell newCell = newRow.createCell(i); Cell cellToClone = cellToAdd.getCellToClone(); if (cellToClone != null) { String contents = ExcelUtil.getCellContentsAsString(cellToClone); newCell.setCellStyle(cellToClone.getCellStyle()); try { newCell.setCellFormula(cellToClone.getCellFormula()); } catch (Exception e) { // Do nothing here. I don't know why POI throw exceptions here when the cell is not a // formula, but this suppresses them... } int numFormattings = sheetToAdd.getSheet().getSheetConditionalFormatting().getNumConditionalFormattings(); for (int n = 0; n < numFormattings; n++) { ConditionalFormatting f = sheetToAdd.getSheet().getSheetConditionalFormatting().getConditionalFormattingAt(n); for (CellRangeAddress add : f.getFormattingRanges()) { if (add.getFirstRow() == rowToAdd.getRowToClone().getRowNum() && add.getLastRow() == rowToClone.getRowNum()) { if (add.getFirstColumn() == cellToClone.getColumnIndex() && add.getLastColumn() == cellToClone.getColumnIndex()) { ConditionalFormattingRule[] rules = new ConditionalFormattingRule[f.getNumberOfRules()]; for (int j = 0; j < f.getNumberOfRules(); j++) { rules[j] = f.getRule(j); } CellRangeAddress[] cellRange = new CellRangeAddress[1]; cellRange[0] = new CellRangeAddress(rowIndex, rowIndex, i, i); sheetToAdd .getSheet() .getSheetConditionalFormatting() .addConditionalFormatting(cellRange, rules); } } } } if (ObjectUtil.notNull(contents)) { Object newContents = EvaluationUtil.evaluateExpression( contents, cellToAdd.getReplacementData(), prefix, suffix); ExcelUtil.setCellContents(styleHelper, newCell, newContents); } } } return newRow; }