private static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // 报表标题字体 Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight((short) Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // 标题单元格格式 HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("学生列表"); cellTitle.setCellStyle(cellStyleTitle); worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2)); // 标题合并列 Date date = new Date(); SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd"); HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1); HSSFCell cellDate = dateTitle.createCell(startColIndex); cellDate.setCellValue("这个报表创建于: " + dateFormat.format(date)); }
/** * Builds the report title and the date header * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset */ public static void buildTitle(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Create font style for the report title Font fontTitle = worksheet.getWorkbook().createFont(); fontTitle.setBoldweight(Font.BOLDWEIGHT_BOLD); fontTitle.setFontHeight((short) 280); // Create cell style for the report title HSSFCellStyle cellStyleTitle = worksheet.getWorkbook().createCellStyle(); cellStyleTitle.setAlignment(CellStyle.ALIGN_CENTER); cellStyleTitle.setWrapText(true); cellStyleTitle.setFont(fontTitle); // Create report title HSSFRow rowTitle = worksheet.createRow((short) startRowIndex); rowTitle.setHeight((short) 500); HSSFCell cellTitle = rowTitle.createCell(startColIndex); cellTitle.setCellValue("Compensation Report"); cellTitle.setCellStyle(cellStyleTitle); // Create merged region for the report title worksheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 14)); // Create date header HSSFRow dateTitle = worksheet.createRow((short) startRowIndex + 1); HSSFCell cellDate = dateTitle.createCell(startColIndex); cellDate.setCellValue("This report was generated at " + new Date()); }
/** * Builds the column headers * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset */ public static void buildHeaders( HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Production> datasource) { // Create font style for the headers Font font = worksheet.getWorkbook().createFont(); font.setBoldweight(Font.BOLDWEIGHT_BOLD); // Create cell style for the headers HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle(); headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_25_PERCENT.index); headerCellStyle.setFillPattern(CellStyle.FINE_DOTS); headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headerCellStyle.setWrapText(true); headerCellStyle.setFont(font); headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN); // Create the column headers HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2); rowHeader.setHeight((short) 500); HSSFCell cell0 = rowHeader.createCell(startColIndex + 0); cell0.setCellValue("Shift"); cell0.setCellStyle(headerCellStyle); int dyn = 1; List<Compensation> compensations = datasource.get(0).getCompensations(); for (Compensation compensation : compensations) { HSSFCell cellc1 = rowHeader.createCell(startColIndex + 0 + dyn); cellc1.setCellValue(compensation.getElectricmeter().getName()); cellc1.setCellStyle(headerCellStyle); dyn++; HSSFCell cellc2 = rowHeader.createCell(startColIndex + 0 + dyn); cellc2.setCellValue(compensation.getElectricmeter().getName() + "\nkW·h"); cellc2.setCellStyle(headerCellStyle); dyn++; } HSSFCell cell5 = rowHeader.createCell(startColIndex + 0 + dyn + 0); cell5.setCellValue("580 İnd/Reak"); cell5.setCellStyle(headerCellStyle); HSSFCell cell6 = rowHeader.createCell(startColIndex + 0 + dyn + 1); cell6.setCellValue("880 Kap/Reak"); cell6.setCellStyle(headerCellStyle); }
public static HSSFCellStyle rowStyle2(final HSSFSheet sheet) { HSSFCellStyle style = sheet.getWorkbook().createCellStyle(); style.setBorderTop(HSSFCellStyle.BORDER_MEDIUM); style.setBorderLeft(HSSFCellStyle.BORDER_MEDIUM); style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM); style.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM); return style; }
/** * Fills the report with content * * @param worksheet * @param startRowIndex starting row offset * @param startColIndex starting column offset * @param datasource the data source */ public static void fillReport( HSSFSheet worksheet, int startRowIndex, int startColIndex, List<Expense> datasource) { // Row offset startRowIndex += 2; // Create cell style for the body HSSFCellStyle bodyCellStyle = worksheet.getWorkbook().createCellStyle(); bodyCellStyle.setAlignment(CellStyle.ALIGN_CENTER); bodyCellStyle.setWrapText(true); HSSFCellStyle numericStyle = worksheet.getWorkbook().createCellStyle(); numericStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00")); // Create body for (int i = startRowIndex; i + startRowIndex - 2 < datasource.size() + 2; i++) { // Create a new row HSSFRow row = worksheet.createRow((short) i + 1); HSSFCell cell0 = row.createCell(startColIndex + 0); cell0.setCellValue(datasource.get(i - 2).getMember().getUsername()); cell0.setCellStyle(bodyCellStyle); HSSFCell cell1 = row.createCell(startColIndex + 1); cell1.setCellValue(datasource.get(i - 2).getExpensetype().getCode()); cell1.setCellStyle(bodyCellStyle); HSSFCell cell2 = row.createCell(startColIndex + 2); cell2.setCellValue(datasource.get(i - 2).getDocumentDate()); HSSFCell cell3 = row.createCell(startColIndex + 3); cell3.setCellValue(datasource.get(i - 2).getCompany()); cell3.setCellStyle(bodyCellStyle); HSSFCell cell4 = row.createCell(startColIndex + 4); cell4.setCellValue(datasource.get(i - 2).getDescription()); cell4.setCellStyle(bodyCellStyle); HSSFCell cell5 = row.createCell(startColIndex + 5); cell5.setCellType(HSSFCell.CELL_TYPE_NUMERIC); cell5.setCellValue(datasource.get(i - 2).getAmount().doubleValue()); cell5.setCellStyle(numericStyle); } }
private static void buildHeaders(HSSFSheet worksheet, int startRowIndex, int startColIndex) { // Header字体 Font font = worksheet.getWorkbook().createFont(); font.setBoldweight((short) Font.BOLDWEIGHT_BOLD); // font.setColor(HSSFColor.BLUE.index);//设置字体颜色 // 单元格样式 HSSFCellStyle headerCellStyle = worksheet.getWorkbook().createCellStyle(); // headerCellStyle.setFillForegroundColor(HSSFColor.GREY_80_PERCENT.index);//前景色 // headerCellStyle.setFillBackgroundColor(HSSFColor.GREY_80_PERCENT.index);//背景色 // headerCellStyle.setFillPattern(CellStyle.FINE_DOTS); //设置填充方式 headerCellStyle.setAlignment(CellStyle.ALIGN_CENTER); headerCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); headerCellStyle.setWrapText(true); headerCellStyle.setFont(font); headerCellStyle.setBorderBottom(CellStyle.BORDER_THIN); headerCellStyle.setBorderLeft(CellStyle.BORDER_THIN); headerCellStyle.setBorderRight(CellStyle.BORDER_THIN); headerCellStyle.setBorderTop(CellStyle.BORDER_THIN); // 创建字段标题 HSSFRow rowHeader = worksheet.createRow((short) startRowIndex + 2); rowHeader.setHeight((short) 500); HSSFCell cell1 = rowHeader.createCell(startColIndex + 0); cell1.setCellValue("学号"); cell1.setCellStyle(headerCellStyle); HSSFCell cell2 = rowHeader.createCell(startColIndex + 1); cell2.setCellValue("姓名"); cell2.setCellStyle(headerCellStyle); HSSFCell cell3 = rowHeader.createCell(startColIndex + 2); cell3.setCellValue("得分"); cell3.setCellStyle(headerCellStyle); }
/** Writes the report to the output stream */ public static void write(HttpServletResponse response, HSSFSheet worksheet) { logger.debug("Writing report to the stream"); try { // Retrieve the output stream ServletOutputStream outputStream = response.getOutputStream(); // Write to the output stream worksheet.getWorkbook().write(outputStream); // Flush the stream outputStream.flush(); } catch (Exception e) { logger.error("Unable to write report to the output stream"); } }
/** * 输出图片到指定的单元格,参考POI例子中的ReportImageUtil类。 * * @param cell -- 单元格 * @param bytes -- 图片内容 */ public static void addImageToSheet(HSSFCell cell, byte[] bytes) { if (cell == null) { _log.showError("-----insertImageToSheet: cell is null!"); return; } if (bytes == null || bytes.length == 0) { _log.showError("-----insertImageToSheet: bytes is null!"); return; } // 取所在表单对象 HSSFSheet sheet = cell.getSheet(); // 取图片输出行与列 int firstRow = cell.getRowIndex(); int lastRow = cell.getRowIndex(); int firstCol = cell.getColumnIndex(); int lastCol = cell.getColumnIndex(); // 取单元格所在的区域 CellRangeAddress range = getMergedRegion(cell); if (range != null) { firstRow = range.getFirstRow(); lastRow = range.getLastRow(); firstCol = range.getFirstColumn(); lastCol = range.getLastColumn(); } _log.showDebug( "---------image cells=[" + firstRow + "," + firstCol + "," + lastRow + "," + lastCol + "]"); // 图片输出要比单元格的高与宽偏5个值,保留单元的边框,宽度1023表示填充满,高度255表示填充满 HSSFClientAnchor anchor = new HSSFClientAnchor(5, 5, 1023, 255, (short) firstCol, firstRow, (short) lastCol, lastRow); anchor.setAnchorType(HSSFClientAnchor.MOVE_AND_RESIZE); // 取图片管理器,如果没有则创建 HSSFPatriarch draw = sheet.getDrawingPatriarch(); if (draw == null) { draw = sheet.createDrawingPatriarch(); } // 插入新图片,返回的新图片序号无效 sheet.getWorkbook().addPicture(bytes, HSSFWorkbook.PICTURE_TYPE_JPEG); // 上面代码中新建图片的序号没有考虑原有图片数量,所以取原图片数量+1作为新图片的序号 List<HSSFPicture> lsPicture = getAllPicture(sheet); int index = lsPicture.size() + 1; _log.showDebug("---------new image index=" + index); draw.createPicture(anchor, index); }
/** * 添加临时表格内容 * * @param mainSheet -- 原表单内容 * @param subSheet -- 临时表单内容 * @return */ public static HSSFSheet appendSheet(HSSFSheet mainSheet, HSSFSheet subSheet) { if (mainSheet == null || subSheet == null) return null; // 判断报表是否允许输出 if (!isAllowOut(mainSheet)) return mainSheet; // 原报表的最后一行 int endRowNum = mainSheet.getPhysicalNumberOfRows(); HSSFRow sourow = null, descrow = null; HSSFCell sourcell = null, descell = null, orgcell = null; int i = 0, offsetcnt = 0; // 复制表格中的图片 copySheetImage(mainSheet.getWorkbook(), subSheet.getWorkbook()); // 设置以合并的单元格 CellRangeAddress range = null; int mergedNum = subSheet.getNumMergedRegions(); for (i = 0; i < mergedNum; i++) { range = subSheet.getMergedRegion(i); range.setFirstRow(range.getFirstRow() + endRowNum); range.setLastRow(range.getLastRow() + endRowNum); mainSheet.addMergedRegion(range); } range = null; // int k = 0; // 设置相关参数 mainSheet.setAlternativeExpression(subSheet.getAlternateExpression()); mainSheet.setAlternativeFormula(subSheet.getAlternateFormula()); mainSheet.setAutobreaks(subSheet.getAutobreaks()); mainSheet.setDialog(subSheet.getDialog()); mainSheet.setDisplayGuts(subSheet.getDisplayGuts()); mainSheet.setFitToPage(subSheet.getFitToPage()); for (java.util.Iterator<Row> iterow = subSheet.rowIterator(); iterow.hasNext(); ) { sourow = (HSSFRow) iterow.next(); offsetcnt = sourow.getRowNum() + endRowNum; descrow = mainSheet.createRow(offsetcnt); descrow.setHeight(sourow.getHeight()); descrow.setHeightInPoints(sourow.getHeightInPoints()); java.util.Iterator<Cell> iter = sourow.cellIterator(); while (iter.hasNext()) { sourcell = (HSSFCell) iter.next(); int column = sourcell.getColumnIndex(); descell = descrow.createCell(column); // 取模板中的单元格,与来源表单位置相同 int row = sourcell.getRowIndex(); orgcell = mainSheet.getRow(row).getCell(column); if (orgcell != null) { // 取模板中的类型赋值 descell.setCellType(orgcell.getCellType()); // 取模板中的样式赋值 descell.setCellStyle(orgcell.getCellStyle()); } else { _log.showWarn("module xls [{0}, {1}] cell is null!", row, column); } if (sourcell.getCellType() == HSSFCell.CELL_TYPE_STRING) descell.setCellValue(sourcell.getStringCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) descell.setCellValue(sourcell.getNumericCellValue()); else if (sourcell.getCellType() == HSSFCell.CELL_TYPE_BLANK) ; } sourow = null; sourcell = null; descrow = null; orgcell = null; } return mainSheet; }
ExcelSheet(HSSFSheet sheet) { this.sheet = sheet; this.book = sheet.getWorkbook(); }