List of usage examples for org.apache.poi.hssf.usermodel HSSFRow setHeight
@Override public void setHeight(short height)
From source file:HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.//from w w w . j a v a 2 s . com */ private static void testCreateSampleSheet(String outputFilename) throws IOException { int rownum; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(HSSFCellStyle.BORDER_THIN); cs2.setFillPattern((short) 1); // fill w fg cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue(rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(HSSFCellStyle.BORDER_THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it s = wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet FileOutputStream out = new FileOutputStream(outputFilename); wb.write(out); out.close(); }
From source file:cn.mario256.blog.ExcelView.java
License:Open Source License
/** * ?Excel// w w w. j ava 2s . c o m * * @param model * ? * @param workbook * HSSFWorkbook * @param request * HttpServletRequest * @param response * HttpServletResponse */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "S" + "H" + "O" + "P" + "+" + "+")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.app.ExcelView.java
License:Open Source License
/** * ?Excel/*from w w w . j a v a 2s. c om*/ * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "A" + "P" + "P" + "T" + "E" + "A" + "M")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.claim.controller.Center16AndJula2015Controller.java
public ProgrameStatus center16_Jula_2015(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); List<ObjRptChula> listData = new ArrayList<ObjRptChula>(); int col_last = 29; int row_start = 8; // index row int row_formula_start = row_start + 1; int col_txtid_width = col_last + 1; try {/*from w w w .j a v a 2s . c o m*/ connection = new DBManage().open(); Center16AndChula2015DAO chula2015DAO = new Center16AndChula2015DAO(); chula2015DAO.setConnection(connection); if (report.getServiceCode().equals(HCODE_CENTER16)) { // center 16 listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CENTER16); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2 "; EXCELL_HOSPITAL = "?? ? 16 13661 "; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "HC16_13661_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName( " ? 16 "); } else if (report.getServiceCode().equals(HCODE_CHULA)) { // Jula listData = chula2015DAO.getListChulaDetail(report.getStmp(), HCODE_CHULA); EXCELL_HEADER1 = "???? OP : " + chula2015DAO.getMonthPayment(report.getStmp()); EXCELL_HEADER2 = "? 16 13661 Model 2"; EXCELL_HOSPITAL = "?? ? ? 13756"; //out out = new FileOutputStream(report.getPathFile() + "" + File.separator + "Chula_13756_" + report.getYearMonth() + "-" + report.getNo() + ".xls"); report.setServiceName("?"); } //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "CH16_CHula_detail_2015.xls")); // style Excell HSSFWorkbook wbCenter16Jula = new HSSFWorkbook(file); this.loadStyle(wbCenter16Jula); // Start sheet 1 ******************************************************************************* HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.createFreezePane(4, 8); // col,row sheet.setColumnWidth(col_txtid_width, WIDTH_TXID); HSSFCell cell = null; HSSFRow row = null; // row 0 HEADER0 row = sheet.createRow(0); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER1); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, col_last)); cell.setCellStyle(csHead); // row 1 HEADER2 row = sheet.createRow(1); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HEADER2); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, col_last)); cell.setCellStyle(csHead); // row 1 HOSPITAL row = sheet.createRow(2); row.setHeight((short) 390); cell = row.createCell(0); cell.setCellValue(EXCELL_HOSPITAL); sheet.addMergedRegion(new CellRangeAddress(2, 2, 0, col_last)); cell.setCellStyle(csHead); int curRow = row_start; int i = 1; for (int j = 0; j < listData.size(); j++) { ObjRptChula data = listData.get(j); int col1 = i; row = sheet.createRow(curRow); row.setHeight((short) 340); /*PoiHssfUtil stylePoi = new PoiHssfUtil(wb, row, cell); stylePoi.setStyleText(0, String.valueOf(col1), PoiHssfUtil.CENTER);*/ cell = row.createCell(0); cell.setCellValue(col1); cell.setCellStyle(csNum4); cell = row.createCell(1); cell.setCellValue(data.getPid()); cell.setCellStyle(csStringPid); cell = row.createCell(2); cell.setCellValue(data.getHn()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(data.getPname()); cell.setCellStyle(csStringLeft); cell = row.createCell(4); cell.setCellValue(data.getHmain()); cell.setCellStyle(csStringLeft); cell = row.createCell(5); cell.setCellValue(data.getDateopd_th()); cell.setCellStyle(csStringLeft); cell = row.createCell(6); cell.setCellValue(data.getPdxcode()); cell.setCellStyle(csNum4); cell = row.createCell(7); cell.setCellValue(data.getChrg_car()); cell.setCellStyle(csDouble2); cell = row.createCell(8); cell.setCellValue(data.getChrg_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(9); cell.setCellValue(data.getChrg_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(10); cell.setCellValue(data.getChrg_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(11); cell.setCellValue(data.getChrg_202()); cell.setCellStyle(csDouble2); cell = row.createCell(12); cell.setCellValue(data.getChrg_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(13); cell.setCellValue(data.getChrg_other()); cell.setCellStyle(csDouble2); cell = row.createCell(14); cell.setCellValue(data.getChrg_total()); cell.setCellStyle(csDouble2); cell = row.createCell(15); cell.setCellValue(data.getSum_chrg()); cell.setCellStyle(csDouble2); cell = row.createCell(16); cell.setCellValue(data.getPaid_car()); cell.setCellStyle(csDouble2); cell = row.createCell(17); cell.setCellValue(data.getPaid_rehab_inst()); cell.setCellStyle(csDouble2); cell = row.createCell(18); cell.setCellValue(data.getPaid_ophc()); cell.setCellStyle(csDouble2); cell = row.createCell(19); cell.setCellValue(data.getPaid_car_rehabinst_ophc_total()); cell.setCellStyle(csDouble2); cell = row.createCell(20); cell.setCellValue(data.getPaid_202()); cell.setCellStyle(csDouble2); cell = row.createCell(21); cell.setCellValue(data.getPaid_stditem()); cell.setCellStyle(csDouble2); cell = row.createCell(22); cell.setCellValue(data.getPaid_other()); cell.setCellStyle(csDouble2); cell = row.createCell(23); cell.setCellValue(data.getPaid_202_stditem_other_total()); cell.setCellStyle(csDouble2); cell = row.createCell(24); cell.setCellValue(data.getPaid_cal_point()); cell.setCellStyle(csDouble2); cell = row.createCell(25); cell.setCellValue(data.getPaid_cal_point_total()); cell.setCellStyle(csDouble2); cell = row.createCell(26); cell.setCellValue(data.getPaid_total()); cell.setCellStyle(csDouble2); cell = row.createCell(27); cell.setCellValue(data.getCompensation_fee_total()); cell.setCellStyle(csDouble2); cell = row.createCell(28); cell.setCellValue(data.getRemark()); cell.setCellStyle(csStringLeft); cell = row.createCell(29); cell.setCellValue(data.getInvoice_no()); cell.setCellStyle(csStringLeft); cell = row.createCell(30); cell.setCellValue(data.getTxid()); cell.setCellStyle(csStringtxid); curRow++; i++; } // row = sheet.createRow(curRow); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 6)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csNum4B); row.createCell(2).setCellStyle(csNum4B); row.createCell(3).setCellStyle(csNum4B); row.createCell(4).setCellStyle(csNum4B); row.createCell(5).setCellStyle(csNum4B); row.createCell(6).setCellStyle(csNum4B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSum(7, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSum(8, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSum(9, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSum(10, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSum(11, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSum(12, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSum(13, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSum(14, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSum(15, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSum(16, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSum(17, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(18); cell.setCellFormula(builderFormulaSum(18, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(19); cell.setCellFormula(builderFormulaSum(19, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(20); cell.setCellFormula(builderFormulaSum(20, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(21); cell.setCellFormula(builderFormulaSum(21, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(22); cell.setCellFormula(builderFormulaSum(22, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(23); cell.setCellFormula(builderFormulaSum(23, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(24); cell.setCellFormula(builderFormulaSum(24, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(25); cell.setCellFormula(builderFormulaSum(25, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(26); cell.setCellFormula(builderFormulaSum(26, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(27); cell.setCellFormula(builderFormulaSum(27, row_formula_start, curRow)); cell.setCellStyle(csDouble2B); cell = row.createCell(28); cell.setCellStyle(csNum4B); workbookBase.setSheetName(0, report.getServiceCode() + " " + report.getServiceName()); workbookBase.write(out); out.close(); file.close(); Console.LOG(Message.exportSuccess(report.getServiceName()), 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); Console.LOG(e.getMessage(), 0); programeStatus.setMessage(ConstantMessage.MSG_PROCESS_FAILS + e.toString()); programeStatus.setTitle(ConstantMessage.MSG_CONTACT_ADMIN); programeStatus.setProcessStatus(false); } finally { if (connection != null) { try { connection.close(); } catch (SQLException ex) { Logger.getLogger(Center16AndJula2015Controller.class.getName()).log(Level.SEVERE, null, ex); } } } return programeStatus; }
From source file:com.cms.utils.ExcelReader.java
public static void copyRow(HSSFSheet srcSheet, HSSFSheet destSheet, HSSFRow srcRow, HSSFRow destRow, Map<Integer, HSSFCellStyle> styleMap) { Set<CellRangeAddress> mergedRegions = new TreeSet<CellRangeAddress>(); destRow.setHeight(srcRow.getHeight()); for (int j = srcRow.getFirstCellNum(); j <= srcRow.getLastCellNum(); j++) { HSSFCell oldCell = srcRow.getCell(j); HSSFCell newCell = destRow.getCell(j); if (oldCell != null) { if (newCell == null) { newCell = destRow.createCell(j); }/*from w w w . j a va 2s. c o m*/ copyCell(oldCell, newCell, styleMap); CellRangeAddress mergedRegion = getMergedRegion(srcSheet, srcRow.getRowNum(), (short) oldCell.getColumnIndex()); if (mergedRegion != null) { CellRangeAddress newMergedRegion = new CellRangeAddress(mergedRegion.getFirstRow(), mergedRegion.getFirstColumn(), mergedRegion.getLastRow(), mergedRegion.getLastColumn()); if (isNewMergedRegion(newMergedRegion, mergedRegions)) { mergedRegions.add(newMergedRegion); destSheet.addMergedRegion(newMergedRegion); } } } } }
From source file:com.dp2345.ExcelView.java
License:Open Source License
/** * ?Excel//from w w w .j a va 2 s. c o m * * @param model * ? * @param workbook * workbook * @param request * request * @param response * response */ public void buildExcelDocument(Map<String, Object> model, HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response) throws Exception { Assert.notEmpty(properties); HSSFSheet sheet; if (StringUtils.isNotEmpty(sheetName)) { sheet = workbook.createSheet(sheetName); } else { sheet = workbook.createSheet(); } int rowNumber = 0; if (titles != null && titles.length > 0) { HSSFRow header = sheet.createRow(rowNumber); header.setHeight((short) 400); for (int i = 0; i < properties.length; i++) { HSSFCell cell = header.createCell(i); HSSFCellStyle cellStyle = workbook.createCellStyle(); cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFFont font = workbook.createFont(); font.setFontHeightInPoints((short) 11); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyle.setFont(font); cell.setCellStyle(cellStyle); if (i == 0) { HSSFPatriarch patriarch = sheet.createDrawingPatriarch(); HSSFComment comment = patriarch .createComment(new HSSFClientAnchor(0, 0, 0, 0, (short) 1, 1, (short) 4, 4)); comment.setString(new HSSFRichTextString("P" + "o" + "w" + "e" + "r" + "e" + "d" + " " + "B" + "y" + " " + "D" + "P" + "2" + "3" + "4" + "5")); cell.setCellComment(comment); } if (titles.length > i && titles[i] != null) { cell.setCellValue(titles[i]); } else { cell.setCellValue(properties[i]); } if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } rowNumber++; } if (data != null) { for (Object item : data) { HSSFRow row = sheet.createRow(rowNumber); for (int i = 0; i < properties.length; i++) { HSSFCell cell = row.createCell(i); if (converters != null && converters.length > i && converters[i] != null) { Class<?> clazz = PropertyUtils.getPropertyType(item, properties[i]); ConvertUtils.register(converters[i], clazz); cell.setCellValue(BeanUtils.getProperty(item, properties[i])); ConvertUtils.deregister(clazz); if (clazz.equals(Date.class)) { DateConverter dateConverter = new DateConverter(); dateConverter.setPattern(DEFAULT_DATE_PATTERN); ConvertUtils.register(dateConverter, Date.class); } } else { cell.setCellValue(BeanUtils.getProperty(item, properties[i])); } if (rowNumber == 0 || rowNumber == 1) { if (widths != null && widths.length > i && widths[i] != null) { sheet.setColumnWidth(i, widths[i]); } else { sheet.autoSizeColumn(i); } } } rowNumber++; } } if (contents != null && contents.length > 0) { rowNumber++; for (String content : contents) { HSSFRow row = sheet.createRow(rowNumber); HSSFCell cell = row.createCell(0); HSSFCellStyle cellStyle = workbook.createCellStyle(); HSSFFont font = workbook.createFont(); font.setColor(HSSFColor.GREY_50_PERCENT.index); cellStyle.setFont(font); cell.setCellStyle(cellStyle); cell.setCellValue(content); rowNumber++; } } response.setContentType("application/force-download"); if (StringUtils.isNotEmpty(filename)) { response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(filename, "UTF-8")); } else { response.setHeader("Content-disposition", "attachment"); } }
From source file:com.haulmont.yarg.formatters.impl.XLSFormatter.java
License:Apache License
/** * Method writes horizontal band/* www. j a v a 2 s . c om*/ * Note: Only one band for row is supported. Now we think that many bands for row aren't usable. * * @param band - band to write * @param templateSheet - template sheet * @param resultSheet - result sheet */ protected void writeHorizontalBand(BandData band, HSSFSheet templateSheet, HSSFSheet resultSheet) { String rangeName = band.getName(); AreaReference templateRange = getAreaForRange(templateWorkbook, rangeName); if (templateRange == null) { throw wrapWithReportingException(String.format("No such named range in xls file: %s", rangeName)); } CellReference[] crefs = templateRange.getAllReferencedCells(); CellReference topLeft, bottomRight; AreaReference resultRange; int rowsAddedByHorizontalBandBackup = rowsAddedByHorizontalBand; int rownumBackup = rownum; if (crefs != null) { addRangeBounds(band, crefs); ArrayList<HSSFRow> resultRows = new ArrayList<HSSFRow>(); int currentRowNum = -1; int currentRowCount = -1; int currentColumnCount = 0; int offset = 0; topLeft = new CellReference(rownum + rowsAddedByHorizontalBand, 0); // no child bands - merge regions now if (band.getChildrenList().isEmpty()) { copyMergeRegions(resultSheet, rangeName, rownum + rowsAddedByHorizontalBand, getCellFromReference(crefs[0], templateSheet).getColumnIndex()); } for (CellReference cellRef : crefs) { HSSFCell templateCell = getCellFromReference(cellRef, templateSheet); HSSFRow resultRow; if (templateCell.getRowIndex() != currentRowNum) { //create new row resultRow = resultSheet.createRow(rownum + rowsAddedByHorizontalBand); copyPageBreaks(templateSheet, resultSheet, templateCell.getRowIndex(), resultRow.getRowNum()); rowsAddedByHorizontalBand += 1; //todo move to options if (templateCell.getCellStyle().getParentStyle() != null && templateCell.getCellStyle().getParentStyle().getUserStyleName() != null && templateCell.getCellStyle().getParentStyle().getUserStyleName() .equals(DYNAMIC_HEIGHT_STYLE)) { //resultRow.setHeight(templateCell.getRow().getHeight()); } else { resultRow.setHeight(templateCell.getRow().getHeight()); } resultRows.add(resultRow); currentRowNum = templateCell.getRowIndex(); currentRowCount++; currentColumnCount = 0; offset = templateCell.getColumnIndex(); } else { // or write cell to current row resultRow = resultRows.get(currentRowCount); currentColumnCount++; } copyCellFromTemplate(templateCell, resultRow, offset + currentColumnCount, band); } bottomRight = new CellReference(rownum + rowsAddedByHorizontalBand - 1, offset + currentColumnCount); resultRange = new AreaReference(topLeft, bottomRight); areaDependencyManager.addDependency(new Area(band.getName(), Area.AreaAlign.HORIZONTAL, templateRange), new Area(band.getName(), Area.AreaAlign.HORIZONTAL, resultRange)); bandsToResultRanges.put(band, new Range(resultSheet.getSheetName(), resultRange.getFirstCell().getCol() + 1, resultRange.getFirstCell().getRow() + 1, resultRange.getLastCell().getCol() + 1, resultRange.getLastCell().getRow() + 1)); } for (BandData child : band.getChildrenList()) { writeBand(child); } // scheduled merge regions if (!band.getChildrenList().isEmpty() && crefs != null) { copyMergeRegions(resultSheet, rangeName, rownumBackup + rowsAddedByHorizontalBandBackup, getCellFromReference(crefs[0], templateSheet).getColumnIndex()); } rownum += rowsAddedByHorizontalBand; rowsAddedByHorizontalBand = 0; rownum += rowsAddedByVerticalBand; rowsAddedByVerticalBand = 0; }
From source file:com.huateng.struts.query.action.T50202Action.java
License:Open Source License
public String download() { String brhBelow = InformationUtil.getBrhGroupString(brhId); System.out.println(brhBelow); String thisMonStart = date + "01"; String thisMonEnd = date + "31"; String lastYear = String.valueOf(Integer.parseInt(date.substring(0, 4)) - 1) + "1231"; String lastMon = thisMonStart;//start? String thisYearStart = date.substring(0, 4) + "0101"; String thisYearEnd = date.substring(0, 4) + "1231"; try {// w ww . j a v a 2 s. c o m //? String[][] data = new String[40][9]; //?data for (int i = 0; i < data.length; i++) { for (int j = 0; j < data[i].length; j++) { if (j < 4) { data[i][j] = ""; } else { data[i][j] = "0"; } } } Iterator it = null; //??? //?data data[0][0] = "1"; data[0][1] = "?"; data[0][3] = ""; data[1][1] = ""; data[1][2] = "?"; data[1][3] = ""; data[2][2] = "?"; data[2][3] = ""; String sql = "select CONN_TYPE, " + "sum(case when apply_date <= '" + lastYear + "' then 1 else 0 end) as c1," + "sum(case when apply_date < '" + lastMon + "' then 1 else 0 end) as c2," + "sum(case when (apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd + "') then 1 else 0 end) as c3," + "sum(case when (apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd + "') then 1 else 0 end) as c4 " + "from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE"; List list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); int index = 0; if ("J".equals(obj[0])) { index = 1; } else { index = 2; } data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); } data[0][4] = String.valueOf(Integer.valueOf(data[1][4]) + Integer.valueOf(data[2][4])); data[0][5] = String.valueOf(Integer.valueOf(data[1][5]) + Integer.valueOf(data[2][5])); data[0][6] = String.valueOf(Integer.valueOf(data[1][6]) + Integer.valueOf(data[2][6])); data[0][7] = String.valueOf(Integer.valueOf(data[1][7]) + Integer.valueOf(data[2][7])); data[0][8] = String.valueOf(Integer.valueOf(data[1][8]) + Integer.valueOf(data[2][8])); } //??? //?data data[3][0] = "2"; data[3][1] = "?POS"; data[3][3] = "?"; data[4][1] = ""; data[4][2] = "?"; data[4][3] = "?"; data[5][2] = "?"; data[5][3] = "?"; sql = "select CONN_TYPE ," + "sum(case when t.REC_CRT_TS <= '" + lastYear + "' then 1 else 0 end) as c1," + "sum(case when t.REC_CRT_TS < '" + lastMon + "' then 1 else 0 end) as c2," + "sum(case when (t.REC_CRT_TS >= '" + thisMonStart + "' and t.REC_CRT_TS <= '" + thisMonEnd + "') then 1 else 0 end) as c3," + "sum(case when (t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd + "') then 1 else 0 end) as c4 " + "from TBL_TERM_INF t,TBL_MCHT_BASE_INF m where t.MCHT_CD = m.MCHT_NO " + "and m.ACQ_INST_ID in " + brhBelow + " group by CONN_TYPE"; list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); int index = 0; if ("J".equals(obj[0])) { index = 4; } else { index = 5; } data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); } data[3][4] = String.valueOf(Integer.valueOf(data[4][4]) + Integer.valueOf(data[5][4])); data[3][5] = String.valueOf(Integer.valueOf(data[4][5]) + Integer.valueOf(data[5][5])); data[3][6] = String.valueOf(Integer.valueOf(data[4][6]) + Integer.valueOf(data[5][6])); data[3][7] = String.valueOf(Integer.valueOf(data[4][7]) + Integer.valueOf(data[5][7])); data[3][8] = String.valueOf(Integer.valueOf(data[4][8]) + Integer.valueOf(data[5][8])); } //??? //?data data[6][0] = "3"; data[6][1] = "?"; sql = "SELECT DESCR,nvl(mchnt1.c,0),nvl(mchnt2.c,0),nvl(mchnt3.c,0),nvl(mchnt4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp " + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date <= '" + lastYear + "' group by MCHT_GRP) mchnt1 on (grp.MCHNT_TP_GRP = mchnt1.MCHT_GRP) " + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date < '" + lastMon + "' group by MCHT_GRP) mchnt2 on (grp.MCHNT_TP_GRP = mchnt2.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date >= '" + thisMonStart + "' and apply_date <= '" + thisMonEnd + "' group by MCHT_GRP) mchnt3 on (grp.MCHNT_TP_GRP = mchnt3.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c from TBL_MCHT_BASE_INF where ACQ_INST_ID in " + brhBelow + " and apply_date >= '" + thisYearStart + "' and apply_date <= '" + thisYearEnd + "' group by MCHT_GRP) mchnt4 on (grp.MCHNT_TP_GRP = mchnt4.MCHT_GRP) " + "order by MCHNT_TP_GRP "; list = commQueryDAO.findBySQLQuery(sql); int index = 6; int len = 0; if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); if (!StringUtil.isNull(obj[0]) && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) { int a = obj[0].toString().indexOf("("); int b = obj[0].toString().indexOf(""); if (a > 0 && b > 0) { data[index][2] = obj[0].toString().substring(0, a < b ? a : b); } else { data[index][2] = obj[0].toString().substring(0, a + b + 1); } } else { data[index][2] = obj[0].toString(); } data[index][3] = ""; data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); index++; len++; } } data[index][0] = "4"; data[index][1] = "?POS"; sql = "SELECT DESCR,nvl(term1.c,0),nvl(term2.c,0),nvl(term3.c,0),nvl(term4.c,0) FROM TBL_INF_MCHNT_TP_GRP grp " + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS <= '" + lastYear + "' group by MCHT_GRP) term1 on (grp.MCHNT_TP_GRP = term1.MCHT_GRP) " + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS < '" + lastMon + "' group by MCHT_GRP) term2 on (grp.MCHNT_TP_GRP = term2.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisMonStart + "' and t.REC_CRT_TS <= '" + thisMonEnd + "' group by MCHT_GRP) term3 on (grp.MCHNT_TP_GRP = term3.MCHT_GRP)" + "left outer join (select MCHT_GRP,COUNT(*) as c FROM TBL_TERM_INF t,TBL_MCHT_BASE_INF m where m.ACQ_INST_ID in " + brhBelow + " and t.MCHT_CD = m.MCHT_NO AND t.REC_CRT_TS >= '" + thisYearStart + "' and t.REC_CRT_TS <= '" + thisYearEnd + "' group by MCHT_GRP) term4 on (grp.MCHNT_TP_GRP = term4.MCHT_GRP) " + "order by MCHNT_TP_GRP "; list = commQueryDAO.findBySQLQuery(sql); if (null != list && !list.isEmpty()) { it = list.iterator(); while (it.hasNext()) { Object[] obj = (Object[]) it.next(); if (!StringUtil.isNull(obj[0]) && (obj[0].toString().indexOf("(") != -1 || obj[0].toString().indexOf("") != -1)) { int a = obj[0].toString().indexOf("("); int b = obj[0].toString().indexOf(""); if (a > 0 && b > 0) { data[index][2] = obj[0].toString().substring(0, a < b ? a : b); } else { data[index][2] = obj[0].toString().substring(0, a + b + 1); } } else { data[index][2] = obj[0].toString(); } data[index][3] = "?"; data[index][4] = obj[1].toString(); data[index][5] = obj[2].toString(); data[index][6] = obj[3].toString(); data[index][7] = obj[4].toString(); data[index][8] = String .valueOf(Integer.valueOf(obj[1].toString()) + Integer.valueOf(obj[4].toString())); index++; } } HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(); HSSFRow row = null; HSSFCell cell = null; sheet.setDefaultColumnWidth(10); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 6000); //? HSSFCellStyle styleTitle = ExcelUtil.createStyleTitle(workbook); HSSFCellStyle styleBold = ExcelUtil.createStyleBold(workbook); HSSFCellStyle styleCenter = ExcelUtil.createStyleCenter(workbook); HSSFCellStyle styleRight = ExcelUtil.createStyleRight(workbook); HSSFCellStyle styleThin = ExcelUtil.createStyleThinCenter(workbook); short rowIndex = 0;// // row = sheet.createRow(rowIndex++); row.setHeight((short) 800); cell = row.createCell(0); cell.setCellStyle(styleTitle); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue("?"); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8));// ?? rowIndex++; row = sheet.createRow(rowIndex++); cell = row.createCell(0); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(""); cell = row.createCell(1); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(date.substring(0, 4) + "" + date.substring(4) + ""); cell = row.createCell(3); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(""); cell = row.createCell(4); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleThin); cell.setCellValue(InformationUtil.getBrhName(brhId)); String[] titles = { "??", " ", "", "??", "", "", "", "", "" }; row = sheet.createRow(rowIndex++); for (int i = 0; i < titles.length; i++) { cell = row.createCell(i); cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellValue(titles[i]); cell.setCellStyle(styleBold); } for (int i = 0; i < index; i++) { row = sheet.createRow(rowIndex++); for (int j = 0; j < data[i].length; j++) { cell = row.createCell(j); if (j < 4) { cell.setCellType(HSSFCell.CELL_TYPE_STRING); cell.setCellStyle(styleCenter); cell.setCellValue(data[i][j]); } else { cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell.setCellStyle(styleRight); cell.setCellValue(Double.valueOf(data[i][j])); } } } int start = 1; sheet.addMergedRegion(new CellRangeAddress(start + 1, start + 1, 4, 8)); sheet.addMergedRegion(new CellRangeAddress(start + 2, start + 2, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 3, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 3, start + 5, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 4, start + 5, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 6, 1, 2)); sheet.addMergedRegion(new CellRangeAddress(start + 6, start + 8, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 7, start + 8, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 9, start + 9 + len - 1, 1, 1)); sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 0, 0)); sheet.addMergedRegion(new CellRangeAddress(start + 9 + len, start + 9 + len + len - 1, 1, 1)); String path = ExcelUtil.writeFiles(workbook, "RN50202RN_" + brhId + "_" + CommonFunction.getCurrentDateTime() + ".xls"); return returnService(Constants.SUCCESS_CODE_CUSTOMIZE + path); } catch (Exception e) { e.printStackTrace(); return returnService("?,?", e); } }
From source file:com.insoul.ti.controller.ContestProjectEntryController.java
@RequestMapping("/download") public ModelAndView download(HttpServletResponse response) throws Exception { HSSFWorkbook workbook = new HSSFWorkbook();// Excel HSSFSheet sheet = workbook.createSheet();// ExcelSheet sheet.createFreezePane(1, 2);// // /* w w w. j ava 2 s. c o m*/ sheet.setColumnWidth(0, 1000); sheet.setColumnWidth(1, 3500); sheet.setColumnWidth(2, 3500); sheet.setColumnWidth(3, 6500); sheet.setColumnWidth(4, 6500); sheet.setColumnWidth(5, 6500); sheet.setColumnWidth(6, 6500); sheet.setColumnWidth(7, 2500); // Sheet? // HSSFCellStyle sheetStyle = workbook.createCellStyle(); // // sheetStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index); // ? // sheetStyle.setFillPattern(HSSFCellStyle.FINE_DOTS); // ? // for (int i = 0; i <= 14; i++) { // sheet.setDefaultColumnStyle((short) i, sheetStyle); // } // HSSFFont headfont = workbook.createFont(); headfont.setFontName(""); headfont.setFontHeightInPoints((short) 22);// ? headfont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);// // ?? HSSFCellStyle headstyle = workbook.createCellStyle(); headstyle.setFont(headfont); headstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? headstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// headstyle.setLocked(true); headstyle.setWrapText(true);// ? // ?? HSSFFont columnHeadFont = workbook.createFont(); columnHeadFont.setFontName(""); columnHeadFont.setFontHeightInPoints((short) 10); columnHeadFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // ? HSSFCellStyle columnHeadStyle = workbook.createCellStyle(); columnHeadStyle.setFont(columnHeadFont); columnHeadStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? columnHeadStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// columnHeadStyle.setLocked(true); columnHeadStyle.setWrapText(true); columnHeadStyle.setLeftBorderColor(HSSFColor.BLACK.index);// columnHeadStyle.setBorderLeft((short) 1);// ? columnHeadStyle.setRightBorderColor(HSSFColor.BLACK.index);// ? columnHeadStyle.setBorderRight((short) 1);// ? columnHeadStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? columnHeadStyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? // ???? columnHeadStyle.setFillForegroundColor(HSSFColor.WHITE.index); HSSFFont font = workbook.createFont(); font.setFontName(""); font.setFontHeightInPoints((short) 10); // ?? HSSFCellStyle style = workbook.createCellStyle(); style.setFont(font); style.setAlignment(HSSFCellStyle.ALIGN_LEFT);// ? style.setVerticalAlignment(HSSFCellStyle.VERTICAL_TOP);// style.setWrapText(true); style.setLeftBorderColor(HSSFColor.BLACK.index); style.setBorderLeft((short) 1); style.setRightBorderColor(HSSFColor.BLACK.index); style.setBorderRight((short) 1); style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? style.setBottomBorderColor(HSSFColor.BLACK.index); // ? style.setFillForegroundColor(HSSFColor.WHITE.index);// ? // ?? HSSFCellStyle centerstyle = workbook.createCellStyle(); centerstyle.setFont(font); centerstyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);// ? centerstyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);// centerstyle.setWrapText(true); centerstyle.setLeftBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderLeft((short) 1); centerstyle.setRightBorderColor(HSSFColor.BLACK.index); centerstyle.setBorderRight((short) 1); centerstyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // ? centerstyle.setBottomBorderColor(HSSFColor.BLACK.index); // ? centerstyle.setFillForegroundColor(HSSFColor.WHITE.index);// ? try { // HSSFRow row0 = sheet.createRow(0); // row0.setHeight((short) 900); // HSSFCell cell0 = row0.createCell(0); cell0.setCellValue(new HSSFRichTextString("???")); cell0.setCellStyle(headstyle); /** * ?? ??0 ??0 ??0 ??0 */ CellRangeAddress range = new CellRangeAddress(0, 0, 0, 14); sheet.addMergedRegion(range); // HSSFRow row1 = sheet.createRow(1); row1.setHeight((short) 750); HSSFCell cell = row1.createCell(0); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(1); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(2); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(3); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(4); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(5); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(6); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(7); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(8); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(9); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(10); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(11); cell.setCellValue(new HSSFRichTextString("")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(12); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(13); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(columnHeadStyle); cell = row1.createCell(14); cell.setCellValue(new HSSFRichTextString("??")); cell.setCellStyle(columnHeadStyle); List<ContestEntry> projectList = contestEntryDAO.findAll(); int m = 2; int len = projectList.size(); for (int i = 0; i < len; i++) { ContestEntry c = projectList.get(i); HSSFRow row = sheet.createRow(m + i); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString(c.getId() + "")); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(new HSSFRichTextString(c.getLocation())); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(new HSSFRichTextString(c.getInstance())); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(new HSSFRichTextString(c.getIndustry())); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(new HSSFRichTextString(c.getLegalFormation())); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(new HSSFRichTextString(c.getRegtime())); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(new HSSFRichTextString(c.getEmployqty() + "")); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue(new HSSFRichTextString(c.getLegalPerson())); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue(new HSSFRichTextString(c.getUserCategory())); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue(new HSSFRichTextString(c.getContact())); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue(new HSSFRichTextString(c.getIdNumber())); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue(new HSSFRichTextString(c.getBankName())); cell.setCellStyle(style); cell = row.createCell(12); cell.setCellValue(new HSSFRichTextString(c.getBankUserName())); cell.setCellStyle(style); cell = row.createCell(13); cell.setCellValue(new HSSFRichTextString(c.getBankAccount())); cell.setCellStyle(style); cell = row.createCell(14); cell.setCellValue(new HSSFRichTextString(c.getSupportMoney())); cell.setCellStyle(style); } String filename = System.nanoTime() + ".xls";// Excel?? response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-disposition", "attachment;filename=" + filename); OutputStream ouputStream = response.getOutputStream(); workbook.write(ouputStream); ouputStream.flush(); ouputStream.close(); } catch (Exception e) { log.error("download excel Error.", e); } return null; }
From source file:com.learn.core.utils.HSSFReadWrite.java
License:Apache License
/** * given a filename this outputs a sample sheet with just a set of * rows/cells.//from w ww . j av a2s .c o m */ private static void testCreateSampleSheet(String outputFilename) throws IOException { try (HSSFWorkbook wb = new HSSFWorkbook()) { HSSFSheet s = wb.createSheet(); HSSFCellStyle cs = wb.createCellStyle(); HSSFCellStyle cs2 = wb.createCellStyle(); HSSFCellStyle cs3 = wb.createCellStyle(); HSSFFont f = wb.createFont(); HSSFFont f2 = wb.createFont(); f.setFontHeightInPoints((short) 12); f.setColor((short) 0xA); f.setBold(true); f2.setFontHeightInPoints((short) 10); f2.setColor((short) 0xf); f2.setBold(true); cs.setFont(f); cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("($#,##0_);[Red]($#,##0)")); cs2.setBorderBottom(BorderStyle.THIN); cs2.setFillPattern(FillPatternType.SOLID_FOREGROUND); cs2.setFillForegroundColor((short) 0xA); cs2.setFont(f2); wb.setSheetName(0, "HSSF Test"); int rownum; for (rownum = 0; rownum < 300; rownum++) { HSSFRow r = s.createRow(rownum); if ((rownum % 2) == 0) { r.setHeight((short) 0x249); } for (int cellnum = 0; cellnum < 50; cellnum += 2) { HSSFCell c = r.createCell(cellnum); c.setCellValue( rownum * 10000 + cellnum + (((double) rownum / 1000) + ((double) cellnum / 10000))); if ((rownum % 2) == 0) { c.setCellStyle(cs); } c = r.createCell(cellnum + 1); c.setCellValue(new HSSFRichTextString("TEST")); // 50 characters divided by 1/20th of a point s.setColumnWidth(cellnum + 1, (int) (50 * 8 / 0.05)); if ((rownum % 2) == 0) { c.setCellStyle(cs2); } } } // draw a thick black border on the row at the bottom using BLANKS rownum++; rownum++; HSSFRow r = s.createRow(rownum); cs3.setBorderBottom(BorderStyle.THICK); for (int cellnum = 0; cellnum < 50; cellnum++) { HSSFCell c = r.createCell(cellnum); c.setCellStyle(cs3); } s.addMergedRegion(new CellRangeAddress(0, 3, 0, 3)); s.addMergedRegion(new CellRangeAddress(100, 110, 100, 110)); // end draw thick black border // create a sheet, set its title then delete it wb.createSheet(); wb.setSheetName(1, "DeletedSheet"); wb.removeSheetAt(1); // end deleted sheet try (FileOutputStream out = new FileOutputStream(outputFilename)) { wb.write(out); } } }