List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:com.claim.controller.VajiraRFController.java
public ProgrameStatus hcRFSummary(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); int col_last = 17; int row_start = 8; // index row int row_formula_start = row_start + 1; List<ObjRptVajiraRfSummary> listData = new ArrayList<ObjRptVajiraRfSummary>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try {// w ww .j a v a 2 s .c om connection = new DBManage().open(); VajiraREDao vajiraREDao = new VajiraREDao(); vajiraREDao.setConnection(connection); //readTemplate file = new FileInputStream(new File("." + File.separator + "xls" + File.separator + "HC_RF_SUM.xls")); EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}", new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH)); //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report); EXCELL_HEADER2 = report.getTitle2(); EXCELL_HOSPITAL = "?: " + StringOpUtil.removeNull(report.getServiceName()) + " (" + StringOpUtil.removeNull(report.getServiceCode()) + ")"; // style Excell this.setFontFamily("TH SarabunPSK"); //this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.loadStyle(new HSSFWorkbook(file)); // Start sheet 1 HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.setColumnWidth((col_last + 1), WIDTH_TXID); // 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 2 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 autoNumber = 1; listData = vajiraREDao.getListVajiraSumGroupHcode(report); for (int i = 0; i < listData.size(); i++) { ObjRptVajiraRfSummary objData = listData.get(i); row = sheet.createRow(curRow); row.setHeight((short) 400); cell = row.createCell(0); cell.setCellValue(autoNumber); cell.setCellStyle(csNum3); cell = row.createCell(1); cell.setCellValue(objData.getHcode()); cell.setCellStyle(csStringLeft); cell = row.createCell(2); cell.setCellValue(objData.getHcode_name()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(objData.getCount_pid()); cell.setCellStyle(csNum3); cell = row.createCell(4); cell.setCellValue(objData.getCount_txid()); cell.setCellStyle(csNum3); cell = row.createCell(5); cell.setCellValue(objData.getSum_chrg_hc()); cell.setCellStyle(csDouble2R); cell = row.createCell(6); cell.setCellValue(objData.getSum_chrg_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(7); cell.setCellValue(objData.getSum_chrg_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(8); cell.setCellValue(objData.getSum_chrg_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(9); cell.setCellValue(objData.getSum_chrg_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(10); cell.setCellValue(objData.getSum_paid_hc()); cell.setCellStyle(csDouble2R); cell = row.createCell(11); cell.setCellValue(objData.getSum_paid_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(12); cell.setCellValue(objData.getSum_paid_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(13); cell.setCellValue(objData.getSum_paid_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(14); cell.setCellValue(objData.getSum_paid_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(15); cell.setCellValue(objData.getSum_point()); cell.setCellStyle(csDouble2R); cell = row.createCell(16); cell.setCellValue(objData.getSum_reimburse()); cell.setCellStyle(csDouble2R); cell = row.createCell(17); cell.setCellValue(objData.getSum_totalreimburse()); cell.setCellStyle(csDouble2R); curRow++; autoNumber++; } //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); // row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csString2Center); row.createCell(2).setCellStyle(csString2Center); cell = row.createCell(3); cell.setCellFormula(builderFormulaSumRound(3, row_formula_start, curRow, 0)); cell.setCellStyle(csNum4BCenter); cell = row.createCell(4); cell.setCellFormula(builderFormulaSumRound(4, row_formula_start, curRow, 0)); cell.setCellStyle(csNum4BCenter); cell = row.createCell(5); cell.setCellFormula(builderFormulaSumRound(5, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(6); cell.setCellFormula(builderFormulaSumRound(6, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSumRound(7, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSumRound(8, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSumRound(9, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSumRound(10, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSumRound(11, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSumRound(12, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSumRound(13, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSumRound(14, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSumRound(15, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSumRound(16, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSumRound(17, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last)); cell.setCellStyle(csStringB); /* ############ ? ############### */ workbookBase.setSheetName(0, report.getServiceCode());// + " " + report.getServiceName()); new FileUtil().mkdirMutiDirectory(pathDirectory); out = new FileOutputStream(pathDirectory + "" + File.separator + FILE_NAME_SUM + report.getYearMonth() + "-" + report.getNo() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG( "??? Op? ? ???", 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 { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; }
From source file:com.claim.controller.VajiraRFController.java
public ProgrameStatus hcRfSummarySpliteWithHcode(OppReport report) { ProgrameStatus programeStatus = new ProgrameStatus(); int col_last = 17; int row_start = 8; // index row int row_formula_start = row_start + 1; List<ObjRptVajiraRfSummary> listData = new ArrayList<ObjRptVajiraRfSummary>(); String stmp = StringOpUtil.removeNull(report.getYearMonth()) + "-" + report.getNo(); String pathDirectory = report.getPathFile() + "" + File.separator + "" + stmp + "" + File.separator + ""; try {/*from w w w . j a v a2 s.co m*/ connection = new DBManage().open(); VajiraREDao vajiraREDao = new VajiraREDao(); vajiraREDao.setConnection(connection); //readTemplate file = new FileInputStream( new File("." + File.separator + "xls" + File.separator + "HC_RF_SUM_SPLITE.xls")); EXCELL_HEADER1 = HEADER_DETAIL.replace("{YEAR}", new DateUtil().getBudgeMonthYear_543(stmp, ConstantVariable.BUDGET_MONTH)); //EXCELL_HEADER2 = new DateUtil().convertStmpToString(report.getStmp()) + getTitleDateOpd(report); EXCELL_HEADER2 = report.getTitle2(); EXCELL_HOSPITAL = "?: " + StringOpUtil.removeNull(report.getServiceName()) + " (" + StringOpUtil.removeNull(report.getServiceCode()) + ")"; // style Excell this.setFontFamily("TH SarabunPSK"); //this.setColorCell(HSSFColor.LIGHT_GREEN.index); this.loadStyle(new HSSFWorkbook(file)); // Start sheet 1 HSSFSheet sheet = workbookBase.getSheetAt(0); sheet.setColumnWidth((col_last + 1), WIDTH_TXID); // 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 2 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 autoNumber = 1; listData = vajiraREDao.getListVajiraSumGroupHmain(report); for (int i = 0; i < listData.size(); i++) { ObjRptVajiraRfSummary objData = listData.get(i); row = sheet.createRow(curRow); row.setHeight((short) 400); cell = row.createCell(0); cell.setCellValue(autoNumber); cell.setCellStyle(csNum3); cell = row.createCell(1); cell.setCellValue(objData.getHmain()); cell.setCellStyle(csStringLeft); cell = row.createCell(2); cell.setCellValue(objData.getHmain_name()); cell.setCellStyle(csStringLeft); cell = row.createCell(3); cell.setCellValue(objData.getCount_pid()); cell.setCellStyle(csNum3); cell = row.createCell(4); cell.setCellValue(objData.getCount_txid()); cell.setCellStyle(csNum3); cell = row.createCell(5); cell.setCellValue(objData.getSum_chrg_hc()); cell.setCellStyle(csDouble2R); cell = row.createCell(6); cell.setCellValue(objData.getSum_chrg_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(7); cell.setCellValue(objData.getSum_chrg_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(8); cell.setCellValue(objData.getSum_chrg_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(9); cell.setCellValue(objData.getSum_chrg_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(10); cell.setCellValue(objData.getSum_paid_hc()); cell.setCellStyle(csDouble2R); cell = row.createCell(11); cell.setCellValue(objData.getSum_paid_202()); cell.setCellStyle(csDouble2R); cell = row.createCell(12); cell.setCellValue(objData.getSum_paid_stditem()); cell.setCellStyle(csDouble2R); cell = row.createCell(13); cell.setCellValue(objData.getSum_paid_other()); cell.setCellStyle(csDouble2R); cell = row.createCell(14); cell.setCellValue(objData.getSum_paid_total()); cell.setCellStyle(csDouble2R); cell = row.createCell(15); cell.setCellValue(objData.getSum_point()); cell.setCellStyle(csDouble2R); cell = row.createCell(16); cell.setCellValue(objData.getSum_reimburse()); cell.setCellStyle(csDouble2R); cell = row.createCell(17); cell.setCellValue(objData.getSum_totalreimburse()); cell.setCellStyle(csDouble2R); curRow++; autoNumber++; } //mergeRowLimit(sheet, curRow, count_limit, indexsCol, col1); // row = sheet.createRow(curRow); row.setHeight((short) 450); cell = row.createCell(0); cell.setCellValue(""); sheet.addMergedRegion(new CellRangeAddress(curRow, curRow, 0, 2)); cell.setCellStyle(csNum4B); row.createCell(1).setCellStyle(csString2Center); row.createCell(2).setCellStyle(csString2Center); cell = row.createCell(3); cell.setCellFormula(builderFormulaSumRound(3, row_formula_start, curRow, 0)); cell.setCellStyle(csNum4BCenter); cell = row.createCell(4); cell.setCellFormula(builderFormulaSumRound(4, row_formula_start, curRow, 0)); cell.setCellStyle(csNum4BCenter); cell = row.createCell(5); cell.setCellFormula(builderFormulaSumRound(5, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(6); cell.setCellFormula(builderFormulaSumRound(6, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(7); cell.setCellFormula(builderFormulaSumRound(7, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(8); cell.setCellFormula(builderFormulaSumRound(8, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(9); cell.setCellFormula(builderFormulaSumRound(9, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(10); cell.setCellFormula(builderFormulaSumRound(10, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(11); cell.setCellFormula(builderFormulaSumRound(11, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(12); cell.setCellFormula(builderFormulaSumRound(12, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(13); cell.setCellFormula(builderFormulaSumRound(13, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(14); cell.setCellFormula(builderFormulaSumRound(14, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(15); cell.setCellFormula(builderFormulaSumRound(15, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(16); cell.setCellFormula(builderFormulaSumRound(16, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); cell = row.createCell(17); cell.setCellFormula(builderFormulaSumRound(17, row_formula_start, curRow, 0)); cell.setCellStyle(csDouble2B); /* ############ ? ############### */ int rowDateTimeCurrent = curRow + 1; // BathText row = sheet.createRow((rowDateTimeCurrent)); cell = row.createCell(0); cell.setCellValue("? _" + new DateUtil().getDateTimeCurrent()); sheet.addMergedRegion(new CellRangeAddress(rowDateTimeCurrent, rowDateTimeCurrent, 0, col_last)); cell.setCellStyle(csStringB); /* ############ ? ############### */ workbookBase.setSheetName(0, report.getServiceCode());// + " " + report.getServiceName()); new FileUtil().mkdirMutiDirectory(pathDirectory); out = new FileOutputStream(pathDirectory + "" + File.separator + FILE_NAME_SUM_REPORT + StringOpUtil.removeNull(report.getServiceCode()) + "_" + report.getStmp() + ".xls"); workbookBase.write(out); out.close(); file.close(); Console.LOG("? " + StringOpUtil.removeNull(report.getServiceName()) + " : " + report.getYearMonth() + "-" + report.getNo() + " ?", 1); programeStatus.setMessage(ConstantMessage.MSG_REPORT_SUCCESS); programeStatus.setTitle(ConstantMessage.MSG_REPORT_COMPLETE); programeStatus.setProcessStatus(true); } catch (Exception e) { e.printStackTrace(); } finally { try { if (connection != null) { connection.close(); } } catch (SQLException e) { e.printStackTrace(); } } return programeStatus; }
From source file:com.claudesoft.service.CarTbDetailed.java
@Override void makeTableHead(HSSFSheet sheet) { HSSFRow row = null;/*ww w . j a v a 2s . c om*/ HSSFCell cell = null; row = sheet.createRow(2); for (int j = 0; j < this.columnCount; j++) { // if (j == 0) { sheet.setColumnWidth(j, 10 * 256); } else { sheet.setColumnWidth(j, 15 * 256); } cell = row.createCell(j); cell.setCellStyle(makeTableHeadStyle()); } cell = sheet.getRow(2).getCell(0); cell.setCellValue(""); cell = sheet.getRow(2).getCell(1); cell.setCellValue(""); cell = sheet.getRow(2).getCell(2); cell.setCellValue(""); cell = sheet.getRow(2).getCell(3); cell.setCellValue(""); cell = sheet.getRow(2).getCell(4); cell.setCellValue(""); cell = sheet.getRow(2).getCell(5); cell.setCellValue(""); cell = sheet.getRow(2).getCell(6); cell.setCellValue(""); cell = sheet.getRow(2).getCell(7); cell.setCellValue(""); cell = sheet.getRow(2).getCell(8); cell.setCellValue(""); cell = sheet.getRow(2).getCell(9); cell.setCellValue(""); cell = sheet.getRow(2).getCell(10); cell.setCellValue(""); cell = sheet.getRow(2).getCell(11); cell.setCellValue(""); }
From source file:com.cms.utils.ExcelReader.java
public static void copySheets(HSSFSheet newSheet, HSSFSheet sheet, boolean copyStyle) { int maxColumnNum = 0; Map<Integer, HSSFCellStyle> styleMap = (copyStyle) ? new HashMap<Integer, HSSFCellStyle>() : null; for (int i = sheet.getFirstRowNum(); i <= sheet.getLastRowNum(); i++) { HSSFRow srcRow = sheet.getRow(i); HSSFRow destRow = newSheet.createRow(i); if (srcRow != null) { copyRow(sheet, newSheet, srcRow, destRow, styleMap); if (srcRow.getLastCellNum() > maxColumnNum) { maxColumnNum = srcRow.getLastCellNum(); }// w w w . jav a 2s. co m } } for (int i = 0; i <= maxColumnNum; i++) { newSheet.setColumnWidth(i, sheet.getColumnWidth(i)); } }
From source file:com.comcast.cats.config.ui.monitoring.reboot.UpTimeAndRebootStatusBean.java
License:Open Source License
public void postProcessXls(Object document) { logger.trace("postProcessXls start document " + document); if (document != null) { HSSFWorkbook workBook = (HSSFWorkbook) document; HSSFSheet sheet = workBook.getSheetAt(0); HSSFRow headerRow = sheet.getRow(0); for (int i = 0; i < headerRow.getPhysicalNumberOfCells(); i++) { sheet.setColumnWidth(i, 30 * 265); // width for 40 characters }/*from ww w . ja v a 2s.co m*/ sheet.shiftRows(0, sheet.getLastRowNum(), 5); // shift rows 0 to n // by 1 to get space // for header sheet.addMergedRegion(CellRangeAddress.valueOf("A1:F3")); HSSFFont headerFont = workBook.createFont(); headerFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); HSSFCellStyle headerCellStyle = workBook.createCellStyle(); headerCellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index); headerCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); headerCellStyle.setFont(headerFont); headerCellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); headerCellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); HSSFCell headerCell = headerRow.createCell(0); headerCell.setCellStyle(headerCellStyle); headerCell.setCellValue("CATS Uptime and Reboot Status : " + (new Date())); HSSFCellStyle metaDataCellStyle = workBook.createCellStyle(); metaDataCellStyle.setFont(headerFont); HSSFRow metaDataRow = sheet.getRow(3); if (metaDataRow == null) { metaDataRow = sheet.createRow(3); } HSSFCell metaDataKey = metaDataRow.createCell(0); metaDataKey.setCellStyle(metaDataCellStyle); metaDataKey.setCellValue("CATS Instance"); HSSFCell metaDataValue = metaDataRow.createCell(1); metaDataValue.setCellStyle(metaDataCellStyle); metaDataValue.setCellValue(AuthController.getHostAddress()); HSSFCellStyle datatTableHeaderCellStyle = workBook.createCellStyle(); datatTableHeaderCellStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); datatTableHeaderCellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND); datatTableHeaderCellStyle.setFont(headerFont); HSSFRow actualDataTableHeaderRow = sheet.getRow(5); for (int i = 0; i < actualDataTableHeaderRow.getPhysicalNumberOfCells(); i++) { HSSFCell cell = actualDataTableHeaderRow.getCell(i); if (cell != null) { String cellValue = cell.getStringCellValue(); cellValue = cellValue.replace("<br/> ", ""); // replace // any line // breaks cell.setCellValue(cellValue); cell.setCellStyle(datatTableHeaderCellStyle); } } } logger.trace("postProcessXls end"); }
From source file:com.dp2345.ExcelView.java
License:Open Source License
/** * ?Excel//ww w . j a v a 2 s.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" + " " + "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.eryansky.core.excelTools.ExcelUtils.java
License:Apache License
public static void copySheetStyle(HSSFWorkbook destwb, HSSFSheet dest, HSSFWorkbook srcwb, HSSFSheet src) { if (src == null || dest == null) return;/*from w w w . j a va2 s . c om*/ dest.setAlternativeExpression(src.getAlternateExpression()); dest.setAlternativeFormula(src.getAlternateFormula()); dest.setAutobreaks(src.getAutobreaks()); dest.setDialog(src.getDialog()); if (src.getColumnBreaks() != null) { for (int col : src.getColumnBreaks()) { dest.setColumnBreak(col); } } dest.setDefaultColumnWidth(src.getDefaultColumnWidth()); dest.setDefaultRowHeight(src.getDefaultRowHeight()); dest.setDefaultRowHeightInPoints(src.getDefaultRowHeightInPoints()); dest.setDisplayGuts(src.getDisplayGuts()); dest.setFitToPage(src.getFitToPage()); dest.setHorizontallyCenter(src.getHorizontallyCenter()); dest.setDisplayFormulas(src.isDisplayFormulas()); dest.setDisplayGridlines(src.isDisplayGridlines()); dest.setDisplayRowColHeadings(src.isDisplayRowColHeadings()); dest.setGridsPrinted(src.isGridsPrinted()); dest.setPrintGridlines(src.isPrintGridlines()); for (int i = 0; i < src.getNumMergedRegions(); i++) { CellRangeAddress r = src.getMergedRegion(i); dest.addMergedRegion(r); } if (src.getRowBreaks() != null) { for (int row : src.getRowBreaks()) { dest.setRowBreak(row); } } dest.setRowSumsBelow(src.getRowSumsBelow()); dest.setRowSumsRight(src.getRowSumsRight()); int maxcol = 0; for (int i = 0; i <= src.getLastRowNum(); i++) { HSSFRow row = src.getRow(i); if (row != null) { if (maxcol < row.getLastCellNum()) maxcol = row.getLastCellNum(); } } for (int col = 0; col <= maxcol; col++) { if (src.getColumnWidth(col) != src.getDefaultColumnWidth()) dest.setColumnWidth(col, src.getColumnWidth(col)); dest.setColumnHidden(col, src.isColumnHidden(col)); } }
From source file:com.esd.cs.common.PoiCreateExcel.java
License:Open Source License
/** * ?//from ww w.j a v a2 s . com * * @param FilePath * @param workerList * @return */ public static boolean createExcel(String FilePath, List<WorkerTemp> workerList) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow = sheet.createRow(0); HSSFCell headell = headRow.createCell(0); // ??? headell = headRow.createCell(0); headell.setCellValue("??"); headell = headRow.createCell(1); headell.setCellValue("??"); sheet.setColumnWidth(1, 8000); headell = headRow.createCell(2); headell.setCellValue(""); sheet.setColumnWidth(2, 13000); for (int i = 1; i <= workerList.size(); i++) { WorkerTemp worker = workerList.get(i - 1); // Excel? HSSFRow row = sheet.createRow(i); HSSFCell cell = row.createCell(0); // ??? cell = row.createCell(0); cell.setCellValue(worker.getWorkerName()); cell = row.createCell(1); cell.setCellValue(worker.getWorkerHandicapCode()); cell = row.createCell(2); cell.setCellValue(worker.getRemark()); } try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; }
From source file:com.esd.cs.common.PoiCreateExcel.java
License:Open Source License
/** * ???//from w w w .java2s. c om * * @param FilePath * @param companyList * @return */ public static boolean createComapnyExcel(String FilePath, List<Company> companyList) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow = sheet.createRow(0); HSSFCell headell = headRow.createCell(0); // ??? headell = headRow.createCell(0); headell.setCellValue("?"); headell = headRow.createCell(1); headell.setCellValue("?"); headell = headRow.createCell(2); headell.setCellValue("????"); sheet.setColumnWidth(2, 12000); // headell = headRow.createCell(3); headell.setCellValue(""); headell = headRow.createCell(4); headell.setCellValue("?"); headell = headRow.createCell(5); headell.setCellValue("???"); headell = headRow.createCell(6); headell.setCellValue("??"); headell = headRow.createCell(7); headell.setCellValue(""); headell = headRow.createCell(8); headell.setCellValue("???"); sheet.setColumnWidth(8, 12000); for (int i = 1; i <= companyList.size(); i++) { Company company = companyList.get(i - 1); // Excel? HSSFRow row = sheet.createRow(i); HSSFCell cell = row.createCell(0); // ??? // ? cell = row.createCell(0); cell.setCellValue(company.getCompanyCode()); // ? cell = row.createCell(1); cell.setCellValue(company.getCompanyTaxCode()); // ??? cell = row.createCell(2); cell.setCellValue(company.getCompanyName()); // cell = row.createCell(3); cell.setCellValue(company.getCompanyLegal()); // ? cell = row.createCell(4); cell.setCellValue(company.getCompanyContactPerson()); // ??? cell = row.createCell(5); cell.setCellValue(company.getCompanyPhone()); // ?? cell = row.createCell(6); cell.setCellValue(company.getCompanyMobile()); // cell = row.createCell(7); cell.setCellValue(company.getCompanyZipCode()); // ??? cell = row.createCell(8); cell.setCellValue(company.getCompanyAddress()); } try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.flush(); os.close(); companyList.clear(); companyList = null; os = null; wb = null; System.gc(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; }
From source file:com.esd.cs.common.PoiCreateExcel.java
License:Open Source License
/** * /*ww w .j a va 2 s . c o m*/ * * @param FilePath * @param companyList * @return */ public static boolean createRepeaExcel(String FilePath, List<ReportViewModel> companyList, ReportModel model) { // Excel Workbook,excel HSSFWorkbook wb = new HSSFWorkbook(); // Excelsheet,exceltab HSSFSheet sheet = wb.createSheet("sheet1"); // excel? sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 3500); // Excel? HSSFRow headRow0 = sheet.createRow(0); HSSFCell headCell = headRow0.createCell(0); // ?? sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 13));// ??? headCell = headRow0.createCell(0); // headCell.setCellValue(model.getTitle()); // ? HSSFCellStyle style = wb.createCellStyle(); style.setFillBackgroundColor(HSSFColor.GREEN.index); style.setAlignment(CellStyle.ALIGN_CENTER);// style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// // HSSFFont font = wb.createFont(); font.setFontHeightInPoints((short) 12); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // style.setFont(font); headCell.setCellStyle(style); // ? ?? HSSFRow RowTow = sheet.createRow(1); HSSFCell CellTow = headRow0.createCell(1); // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));// ??? CellTow = RowTow.createCell(0); // CellTow.setCellValue(model.getCreateCompany()); // ? // ?? sheet.addMergedRegion(new CellRangeAddress(1, 1, 6, 13));// ??? CellTow = RowTow.createCell(6); HSSFCellStyle style1 = wb.createCellStyle(); style1.setFillBackgroundColor(HSSFColor.GREEN.index); style1.setAlignment(CellStyle.ALIGN_RIGHT);// ? CellTow.setCellStyle(style1); // CellTow.setCellValue(model.getCreateData()); // ? HSSFRow headRow = sheet.createRow(2); HSSFCell headell = headRow.createCell(2); // ??? headell = headRow.createCell(0); headell.setCellValue(model.getType()); headell = headRow.createCell(1); headell.setCellValue("??"); headell = headRow.createCell(2); headell.setCellValue("??"); sheet.setColumnWidth(2, 3000); // headell = headRow.createCell(3); headell.setCellValue("???"); sheet.setColumnWidth(3, 3000); // headell = headRow.createCell(4); headell.setCellValue("???"); sheet.setColumnWidth(4, 4000); // headell = headRow.createCell(5); headell.setCellValue("???"); sheet.setColumnWidth(5, 4000); // headell = headRow.createCell(6); headell.setCellValue("???"); sheet.setColumnWidth(6, 4500); // headell = headRow.createCell(7); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(8); headell.setCellValue(""); sheet.setColumnWidth(8, 4000); headell = headRow.createCell(9); headell.setCellValue(""); sheet.setColumnWidth(9, 4000); headell = headRow.createCell(10); headell.setCellValue("?"); sheet.setColumnWidth(10, 4000); headell = headRow.createCell(11); headell.setCellValue("???"); headell = headRow.createCell(12); headell.setCellValue("?"); headell = headRow.createCell(13); headell.setCellValue("?"); for (int i = 0; i < companyList.size(); i++) { ReportViewModel company = companyList.get(i); // Excel? HSSFRow row = sheet.createRow(i + 3); HSSFCell cell = row.createCell(i + 3); // ??? // ???? cell = row.createCell(0); cell.setCellValue(company.getReportName()); // ?? cell = row.createCell(1); cell.setCellValue(company.getUnitNum()); // ?? cell = row.createCell(2); cell.setCellValue(company.getEmpTotal()); // ??? cell = row.createCell(3); cell.setCellValue(company.getUnAudit()); // ?, ??? cell = row.createCell(4); cell.setCellValue(company.getUnReAudit()); // ?, ?? cell = row.createCell(5); cell.setCellValue(company.getAuditOk()); // ?, ?? cell = row.createCell(6); cell.setCellValue(company.getUnauditOk()); // cell = row.createCell(7); cell.setCellValue(company.getShouldTotal().toString()); // ? cell = row.createCell(8); cell.setCellValue(company.getAlreadyTotal().toString()); // cell = row.createCell(9); cell.setCellValue(company.getLessTotal().toString()); // ? cell = row.createCell(10); cell.setCellValue(company.getAmountPayable().toString()); // ??? cell = row.createCell(11); cell.setCellValue(company.getReductionAmount().toString()); // ? cell = row.createCell(12); cell.setCellValue(company.getActualAmount().toString()); // ? cell = row.createCell(13); cell.setCellValue(company.getAlreadyAmount().toString()); } // ? HSSFRow row = sheet.createRow(companyList.size() + 3); HSSFCell cell = row.createCell(companyList.size() + 3); // ??? // ???? sheet.addMergedRegion(new CellRangeAddress(companyList.size() + 3, companyList.size() + 3, 0, 13));// ??? cell = row.createCell(0); // ? HSSFCellStyle styleFoot = wb.createCellStyle(); styleFoot.setAlignment(CellStyle.ALIGN_RIGHT);// ? cell.setCellStyle(styleFoot); // cell.setCellValue(model.getCreatePeople()); try { FileOutputStream os = new FileOutputStream(FilePath); wb.write(os); os.flush(); os.close(); companyList.clear(); companyList = null; os = null; wb = null; System.gc(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } return true; }