/**
* 创建表格样式
*
* @param wb 工作薄对象
* @return 样式列表
*/
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Font titleFont = wb.createFont();
titleFont.setFontName("Arial");
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(titleFont);
styles.put("title", style);
style = wb.createCellStyle();
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
Font dataFont = wb.createFont();
dataFont.setFontName("Arial");
dataFont.setFontHeightInPoints((short) 10);
style.setFont(dataFont);
styles.put("data", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_LEFT);
styles.put("data1", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_CENTER);
styles.put("data2", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
style.setAlignment(CellStyle.ALIGN_RIGHT);
styles.put("data3", style);
style = wb.createCellStyle();
style.cloneStyleFrom(styles.get("data"));
// style.setWrapText(true);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
Font headerFont = wb.createFont();
headerFont.setFontName("Arial");
headerFont.setFontHeightInPoints((short) 10);
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setColor(IndexedColors.WHITE.getIndex());
style.setFont(headerFont);
styles.put("header", style);
return styles;
}
/** @Description: 初始化内容行字体 */
private static void initContentFont() {
contentFont.setFontName("宋体");
contentFont.setFontHeightInPoints((short) 10);
contentFont.setBoldweight(Font.BOLDWEIGHT_NORMAL);
contentFont.setCharSet(Font.DEFAULT_CHARSET);
contentFont.setColor(IndexedColors.BLUE_GREY.index);
}
/** @Description: 初始化标题行字体 */
private static void initTitleFont() {
titleFont.setFontName("宋体");
titleFont.setFontHeightInPoints((short) 20); // 设置字体大小
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); // 粗体
titleFont.setCharSet(Font.DEFAULT_CHARSET);
titleFont.setColor(IndexedColors.BLUE_GREY.index);
}
public CellStyle buildDimensionCellStyle(Sheet sheet) {
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
String headerBGColor = (String) this.getProperty(PROPERTY_DIMENSION_NAME_BACKGROUND_COLOR);
logger.debug("Header background color : " + headerBGColor);
short backgroundColorIndex =
headerBGColor != null
? IndexedColors.valueOf(headerBGColor).getIndex()
: IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_BACKGROUND_COLOR).getIndex();
cellStyle.setFillForegroundColor(backgroundColorIndex);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
String bordeBorderColor = (String) this.getProperty(PROPERTY_HEADER_BORDER_COLOR);
logger.debug("Header border color : " + bordeBorderColor);
short borderColorIndex =
bordeBorderColor != null
? IndexedColors.valueOf(bordeBorderColor).getIndex()
: IndexedColors.valueOf(DEFAULT_HEADER_BORDER_COLOR).getIndex();
cellStyle.setLeftBorderColor(borderColorIndex);
cellStyle.setRightBorderColor(borderColorIndex);
cellStyle.setBottomBorderColor(borderColorIndex);
cellStyle.setTopBorderColor(borderColorIndex);
Font font = sheet.getWorkbook().createFont();
Short headerFontSize = (Short) this.getProperty(PROPERTY_HEADER_FONT_SIZE);
logger.debug("Header font size : " + headerFontSize);
short headerFontSizeShort =
headerFontSize != null ? headerFontSize.shortValue() : DEFAULT_HEADER_FONT_SIZE;
font.setFontHeightInPoints(headerFontSizeShort);
String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
logger.debug("Font name : " + fontName);
fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
font.setFontName(fontName);
String color = (String) this.getProperty(PROPERTY_DIMENSION_NAME_COLOR);
logger.debug("Dimension color : " + color);
short colorIndex =
bordeBorderColor != null
? IndexedColors.valueOf(color).getIndex()
: IndexedColors.valueOf(DEFAULT_DIMENSION_NAME_COLOR).getIndex();
font.setColor(colorIndex);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setItalic(true);
cellStyle.setFont(font);
return cellStyle;
}
public CellStyle buildDataCellStyle(Sheet sheet) {
CellStyle cellStyle = sheet.getWorkbook().createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_RIGHT);
cellStyle.setVerticalAlignment(CellStyle.ALIGN_CENTER);
String cellBGColor = (String) this.getProperty(PROPERTY_CELL_BACKGROUND_COLOR);
logger.debug("Cell background color : " + cellBGColor);
short backgroundColorIndex =
cellBGColor != null
? IndexedColors.valueOf(cellBGColor).getIndex()
: IndexedColors.valueOf(DEFAULT_CELL_BACKGROUND_COLOR).getIndex();
cellStyle.setFillForegroundColor(backgroundColorIndex);
cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
String bordeBorderColor = (String) this.getProperty(PROPERTY_CELL_BORDER_COLOR);
logger.debug("Cell border color : " + bordeBorderColor);
short borderColorIndex =
bordeBorderColor != null
? IndexedColors.valueOf(bordeBorderColor).getIndex()
: IndexedColors.valueOf(DEFAULT_CELL_BORDER_COLOR).getIndex();
cellStyle.setLeftBorderColor(borderColorIndex);
cellStyle.setRightBorderColor(borderColorIndex);
cellStyle.setBottomBorderColor(borderColorIndex);
cellStyle.setTopBorderColor(borderColorIndex);
Font font = sheet.getWorkbook().createFont();
Short cellFontSize = (Short) this.getProperty(PROPERTY_CELL_FONT_SIZE);
logger.debug("Cell font size : " + cellFontSize);
short cellFontSizeShort =
cellFontSize != null ? cellFontSize.shortValue() : DEFAULT_CELL_FONT_SIZE;
font.setFontHeightInPoints(cellFontSizeShort);
String fontName = (String) this.getProperty(PROPERTY_FONT_NAME);
logger.debug("Font name : " + fontName);
fontName = fontName != null ? fontName : DEFAULT_FONT_NAME;
font.setFontName(fontName);
String cellColor = (String) this.getProperty(PROPERTY_CELL_COLOR);
logger.debug("Cell color : " + cellColor);
short cellColorIndex =
cellColor != null
? IndexedColors.valueOf(cellColor).getIndex()
: IndexedColors.valueOf(DEFAULT_CELL_COLOR).getIndex();
font.setColor(cellColorIndex);
cellStyle.setFont(font);
return cellStyle;
}
private HSSFCellStyle getHeaderStyle2(
final HSSFWorkbook workbook,
final boolean shouldRed,
final boolean shouldBackground,
final boolean shouldSmall,
final boolean end,
final boolean changeover,
final Font font) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 9);
font.setColor(HSSFFont.COLOR_NORMAL);
style.setFont(font);
if (shouldRed) {
font.setColor(HSSFFont.COLOR_RED);
style.setFont(font);
}
if (shouldBackground) {
if (changeover) {
style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
} else {
style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
}
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
}
if (shouldSmall) {
font.setFontHeightInPoints((short) 7);
style.setFont(font);
}
if (end) {
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
}
return style;
}
private CellStyle getStyle(Workbook workbook) {
CellStyle style = workbook.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
// 设置单元格字体
Font headerFont = workbook.createFont(); // 字体
headerFont.setFontHeightInPoints((short) 14);
headerFont.setColor(HSSFColor.BLACK.index);
headerFont.setFontName("宋体");
style.setFont(headerFont);
style.setWrapText(true);
// 设置单元格边框及颜色
style.setBorderBottom((short) 1);
style.setBorderLeft((short) 1);
style.setBorderRight((short) 1);
style.setBorderTop((short) 1);
style.setWrapText(true);
return style;
}
private HSSFCellStyle getHeaderStyleChangeoverEnd(final HSSFWorkbook workbook, final Font font) {
HSSFCellStyle style = workbook.createCellStyle();
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_MEDIUM);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
style.setWrapText(true);
font.setFontName(HSSFFont.FONT_ARIAL);
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
font.setFontHeightInPoints((short) 9);
style.setFont(font);
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
return style;
}
@Test
public void generateExcelPoiReport() throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("Accounts");
int rowNum = 1;
Row row = sheet.createRow(rowNum++);
Font font = wb.createFont();
font.setFontHeightInPoints((short) 24);
font.setFontName(FONT_TYPE);
font.setColor(FONT_COLOR_TITLE);
/** * Header **** */
CellStyle style = wb.createCellStyle();
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setFillForegroundColor(BACKGROUND_COLOR);
Cell cell = row.createCell((short) 1);
cell.setCellValue("Account Report per Beneficiary");
cell.setCellStyle(style);
sheet.addMergedRegion(
new CellRangeAddress(
1, // first row (0-based)
2, // last row (0-based)
1, // first column (0-based)
16 // last column (0-based)
));
/** * Body **** */
font = wb.createFont();
font.setFontHeightInPoints((short) 12);
font.setFontName(FONT_TYPE);
font.setColor(FONT_COLOR);
style = wb.createCellStyle();
style.setFont(font);
rowNum = rowNum + 3;
List<Account> accounts = accountManager.getAllAccounts();
for (Account account : accounts) {
row = sheet.createRow(rowNum++);
cell = row.createCell((short) 1);
cell.setCellStyle(style);
cell.setCellValue(account.getName());
cell = row.createCell((short) 2);
cell.setCellValue(account.getNumber());
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue(account.getEntityId());
cell.setCellStyle(style);
}
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(2);
sheet.autoSizeColumn(3);
// Write the output to a file
FileOutputStream fileOut = new FileOutputStream("C:/Temp/workbook.xls");
wb.write(fileOut);
fileOut.close();
}
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
short borderColor = IndexedColors.GREY_50_PERCENT.getIndex();
CellStyle style;
DataFormat format = wb.createDataFormat();
Font titleFont = wb.createFont();
titleFont.setFontName("Tahoma");
titleFont.setFontHeightInPoints((short) 12);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font headFont = wb.createFont();
headFont.setFontName("Tahoma");
headFont.setFontHeightInPoints((short) 11);
headFont.setColor(IndexedColors.WHITE.getIndex());
headFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(headFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setWrapText(true);
styles.put("header", style);
Font groupFont = wb.createFont();
groupFont.setFontName("Tahoma");
groupFont.setFontHeightInPoints((short) 11);
groupFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setFont(groupFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupleft", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setFont(groupFont);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupright", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
/* style.setFont(groupFont); */
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupcenter", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0"));
/* style.setFont(groupFont); */
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("groupnumber", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
/* style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
*/ styles.put("cellleft", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellcenter", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellright", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0"));
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellnumber", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_RIGHT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setDataFormat(format.getFormat("#,##0.00"));
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
styles.put("cellnumber2", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_LEFT);
style.setVerticalAlignment(CellStyle.VERTICAL_TOP);
style.setWrapText(true);
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
styles.put("celltop", style);
return styles;
}
@Override
protected void buildExcelDocument(
Map<String, Object> model,
HSSFWorkbook workbook,
HttpServletRequest request,
HttpServletResponse response)
throws Exception {
// get data model which is passed by the Spring container
List<Object> errorList = (List<Object>) model.get("errorList");
List<DonVi> donViError = (List<DonVi>) errorList.get(0);
List<String> statusError = (List<String>) errorList.get(1);
// create a new Excel sheet
HSSFSheet sheet = workbook.createSheet("Bộ phận sử dụng bị lỗi import");
// create style for header cells
CellStyle style = workbook.createCellStyle();
Font font = workbook.createFont();
font.setFontName("Times New Roman");
font.setFontHeight((short) 260);
style.setFont(font);
sheet.setDefaultRowHeight((short) 400);
sheet.setColumnWidth(0, 3000);
sheet.setColumnWidth(1, 12000);
sheet.setColumnWidth(2, 12000);
sheet.setColumnWidth(3, 4000);
sheet.setColumnWidth(4, 6000);
sheet.setColumnWidth(5, 5000);
sheet.setDefaultColumnStyle(0, style);
sheet.setDefaultColumnStyle(1, style);
sheet.setDefaultColumnStyle(2, style);
sheet.setDefaultColumnStyle(3, style);
sheet.setDefaultColumnStyle(4, style);
sheet.setDefaultColumnStyle(5, style);
// create header row
CellStyle style2 = workbook.createCellStyle();
Font font2 = workbook.createFont();
font2.setFontName("Times New Roman");
font2.setBoldweight(Font.BOLDWEIGHT_BOLD);
font2.setFontHeight((short) 260);
style2.setFont(font2);
style2.setAlignment(CellStyle.ALIGN_CENTER);
HSSFRow row2 = sheet.createRow(0);
row2.createCell(0).setCellValue("Công ty điện lực thành phố Cần Thơ");
row2.getCell(0).setCellStyle(style);
row2.createCell(1).setCellValue("Kho Công ty Điện Lực Cần Thơ");
row2.getCell(1).setCellStyle(style);
// create header row
HSSFRow header = sheet.createRow(1);
response.setHeader("Content-Disposition", "inline; filename=" + "BophansudungError.xls");
header.createCell(0).setCellValue("Mã BPSD");
header.getCell(0).setCellStyle(style2);
header.createCell(1).setCellValue("Tên BPSD");
header.getCell(1).setCellStyle(style2);
header.createCell(2).setCellValue("Địa chỉ");
header.getCell(2).setCellStyle(style2);
header.createCell(3).setCellValue("Email");
header.getCell(3).setCellStyle(style2);
header.createCell(4).setCellValue("Số điện thoại");
header.getCell(4).setCellStyle(style2);
header.createCell(5).setCellValue("Lỗi");
header.getCell(5).setCellStyle(style2);
// create data rows
int rowCount = 2;
int i = 0;
for (DonVi dv : donViError) {
HSSFRow aRow = sheet.createRow(rowCount++);
aRow.createCell(0).setCellValue(dv.getDvMa());
aRow.createCell(1).setCellValue(dv.getDvTen());
aRow.createCell(2).setCellValue(dv.getDiaChi());
aRow.createCell(3).setCellValue(dv.getEmail());
aRow.createCell(4).setCellValue(dv.getSdt());
aRow.createCell(5).setCellValue(statusError.get(i++));
}
}
private static Map createStyles(Workbook wb) {
Map styles = new HashMap();
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 14);
titleFont.setFontName("Trebuchet MS");
CellStyle style = wb.createCellStyle();
style.setFont(titleFont);
style.setBorderBottom((short) 7);
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
styles.put("title", style);
Font itemFont = wb.createFont();
itemFont.setFontHeightInPoints((short) 9);
itemFont.setFontName("Trebuchet MS");
style = wb.createCellStyle();
style.setAlignment((short) 1);
style.setFont(itemFont);
styles.put("item_left", style);
style = wb.createCellStyle();
style.setAlignment((short) 3);
style.setFont(itemFont);
styles.put("item_right", style);
style = wb.createCellStyle();
style.setAlignment((short) 3);
style.setFont(itemFont);
style.setBorderRight((short) 7);
style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderBottom((short) 7);
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderLeft((short) 7);
style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderTop((short) 7);
style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setDataFormat(
wb.createDataFormat().getFormat("_($* #,##0.00_);_($* (#,##0.00);_($* \"-\"??_);_(@_)"));
styles.put("input_$", style);
style = wb.createCellStyle();
style.setAlignment((short) 3);
style.setFont(itemFont);
style.setBorderRight((short) 7);
style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderBottom((short) 7);
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderLeft((short) 7);
style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderTop((short) 7);
style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setDataFormat(wb.createDataFormat().getFormat("0.000%"));
styles.put("input_%", style);
style = wb.createCellStyle();
style.setAlignment((short) 3);
style.setFont(itemFont);
style.setBorderRight((short) 7);
style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderBottom((short) 7);
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderLeft((short) 7);
style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderTop((short) 7);
style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setDataFormat(wb.createDataFormat().getFormat("0"));
styles.put("input_i", style);
style = wb.createCellStyle();
style.setAlignment((short) 2);
style.setFont(itemFont);
style.setDataFormat(wb.createDataFormat().getFormat("m/d/yy"));
styles.put("input_d", style);
style = wb.createCellStyle();
style.setAlignment((short) 3);
style.setFont(itemFont);
style.setBorderRight((short) 7);
style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderBottom((short) 7);
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderLeft((short) 7);
style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderTop((short) 7);
style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setDataFormat(wb.createDataFormat().getFormat("$##,##0.00"));
style.setBorderBottom((short) 7);
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern((short) 1);
styles.put("formula_$", style);
style = wb.createCellStyle();
style.setAlignment((short) 3);
style.setFont(itemFont);
style.setBorderRight((short) 7);
style.setRightBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderBottom((short) 7);
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderLeft((short) 7);
style.setLeftBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setBorderTop((short) 7);
style.setTopBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setDataFormat(wb.createDataFormat().getFormat("0"));
style.setBorderBottom((short) 7);
style.setBottomBorderColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern((short) 1);
styles.put("formula_i", style);
return styles;
}
public File generateXLSResponse(QueryResult queryResult, Locale locale, String baseURL) {
File excelFile = new File("export_parts.xls");
// Blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Parts Data");
String header = StringUtils.join(queryResult.getQuery().getSelects(), ";");
String[] columns = header.split(";");
Map<Integer, String[]> data = new HashMap<>();
String[] headerFormatted = createXLSHeaderRow(header, columns, locale);
data.put(1, headerFormatted);
Map<Integer, String[]> commentsData = new HashMap<>();
String[] headerComments = createXLSHeaderRowComments(header, columns);
commentsData.put(1, headerComments);
List<String> selects = queryResult.getQuery().getSelects();
int i = 1;
for (QueryResultRow row : queryResult.getRows()) {
i++;
data.put(i, createXLSRow(selects, row, baseURL));
commentsData.put(i, createXLSRowComments(selects, row));
}
// Iterate over data and write to sheet
Set<Integer> keyset = data.keySet();
int rownum = 0;
for (Integer key : keyset) {
Row row = sheet.createRow(rownum++);
String[] objArr = data.get(key);
int cellnum = 0;
for (String obj : objArr) {
Cell cell = row.createCell(cellnum++);
cell.setCellValue(obj);
}
CreationHelper factory = workbook.getCreationHelper();
Drawing drawing = sheet.createDrawingPatriarch();
String[] commentsObjArr = commentsData.get(key);
cellnum = 0;
for (String commentsObj : commentsObjArr) {
if (commentsObj.length() > 0) {
Cell cell = row.getCell(cellnum) != null ? row.getCell(cellnum) : row.createCell(cellnum);
// When the comment box is visible, have it show in a 1x3 space
ClientAnchor anchor = factory.createClientAnchor();
anchor.setCol1(cell.getColumnIndex());
anchor.setCol2(cell.getColumnIndex() + 1);
anchor.setRow1(row.getRowNum());
anchor.setRow2(row.getRowNum() + 1);
Comment comment = drawing.createCellComment(anchor);
RichTextString str = factory.createRichTextString(commentsObj);
comment.setString(str);
// Assign the comment to the cell
cell.setCellComment(comment);
}
cellnum++;
}
}
// Define header style
Font headerFont = workbook.createFont();
headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
headerFont.setFontHeightInPoints((short) 10);
headerFont.setFontName("Courier New");
headerFont.setItalic(true);
headerFont.setColor(IndexedColors.WHITE.getIndex());
CellStyle headerStyle = workbook.createCellStyle();
headerStyle.setFont(headerFont);
headerStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
headerStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
// Set header style
for (int j = 0; j < columns.length; j++) {
Cell cell = sheet.getRow(0).getCell(j);
cell.setCellStyle(headerStyle);
if (cell.getCellComment() != null) {
String comment = cell.getCellComment().getString().toString();
if (comment.equals(QueryField.CTX_PRODUCT_ID)
|| comment.equals(QueryField.CTX_SERIAL_NUMBER)
|| comment.equals(QueryField.PART_MASTER_NUMBER)) {
for (int k = 0; k < queryResult.getRows().size(); k++) {
Cell grayCell =
sheet.getRow(k + 1).getCell(j) != null
? sheet.getRow(k + 1).getCell(j)
: sheet.getRow(k + 1).createCell(j);
grayCell.setCellStyle(headerStyle);
}
}
}
}
try {
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(excelFile);
workbook.write(out);
out.close();
} catch (Exception e) {
LOGGER.log(Level.FINEST, null, e);
}
return excelFile;
}
