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();
}
