/**
* 创建表格样式
*
* @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;
}
private Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;
// Title Style
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 16);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(titleFont);
style.setWrapText(true);
styles.put("title", style);
// Sub Title Style
Font subTitleFont = wb.createFont();
subTitleFont.setFontHeightInPoints((short) 14);
subTitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.BRIGHT_GREEN.index);
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setFont(subTitleFont);
style.setWrapText(true);
styles.put("subTitle", style);
Font headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerFont.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(headerFont);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
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("cell", style);
return styles;
}
/** Create a library of cell styles */
private static Map<String, CellStyle> createStyles(Workbook wb) {
Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
CellStyle style;
Font titleFont = wb.createFont();
titleFont.setFontHeightInPoints((short) 18);
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFont(titleFont);
styles.put("title", style);
Font monthFont = wb.createFont();
monthFont.setFontHeightInPoints((short) 11);
monthFont.setColor(IndexedColors.WHITE.getIndex());
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(monthFont);
style.setWrapText(true);
styles.put("header", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
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("cell", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula", style);
style = wb.createCellStyle();
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
style.setFillPattern(CellStyle.SOLID_FOREGROUND);
style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
styles.put("formula_2", style);
return styles;
}
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;
}
/** @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;
}
public CellStyle getHeaderStyle(XSSFWorkbook wb, Short fontSize) {
CellStyle style = wb.createCellStyle();
Font font = wb.createFont();
if (fontSize != null) {
font.setFontHeightInPoints(fontSize);
} else {
font.setFontHeightInPoints((short) 10);
}
// font.setColor(color.getIndex());
font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style.setFont(font);
style.setAlignment(CellStyle.ALIGN_CENTER);
style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
style.setWrapText(true); // 自动换行
style.setBorderTop(CellStyle.BORDER_THIN);
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setBorderRight(CellStyle.BORDER_THIN);
return style;
}
/**
* Método para gerar arquivo excel (xls);
*
* @author Alexandre Martins da Silva
* @param documento - objeto para gerar o arquivo excel (xls)
*/
public void posProcessarXls(Object documento) {
HSSFWorkbook planilha = (HSSFWorkbook) documento;
HSSFSheet folha = planilha.getSheetAt(0);
HSSFRow cabecalho = folha.getRow(0);
HSSFCellStyle estiloDaCelula = planilha.createCellStyle();
Font fonteDoCabecalho = planilha.createFont();
fonteDoCabecalho.setColor(IndexedColors.WHITE.getIndex());
fonteDoCabecalho.setBold(true);
fonteDoCabecalho.setFontHeightInPoints((short) 13);
estiloDaCelula.setFont(fonteDoCabecalho);
estiloDaCelula.setFillForegroundColor(IndexedColors.BLACK.getIndex());
estiloDaCelula.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
for (int i = 0; i < cabecalho.getPhysicalNumberOfCells(); i++) {
cabecalho.getCell(i).setCellStyle(estiloDaCelula);
}
}
/**
* Discription:[设置文件头显示样式]
*
* @param wb
* @return
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public CellStyle setRootSheetSysle(Workbook wb) {
CellStyle style = wb.createCellStyle();
// 设置这些样式
style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 对齐方式：居中对齐
// 生成一个字体
Font font = wb.createFont();
font.setColor(HSSFColor.VIOLET.index);
font.setFontHeightInPoints((short) 12);
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
// 把字体应用到当前的样式
style.setFont(font);
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;
}
/**
* 资产明细表
*
* @param response
* @param customer_2
* @param customer_0or1
* @throws IOException
*/
@RequestMapping("/report/frontequip/exportFrontEquipListReport_assetclean.do")
public void exportFrontEquipListReport_assetclean(
HttpServletResponse response, String customer_2, String customer_0or1) throws IOException {
String customer_2_name = customerService.get(customer_2).getName();
String customer_0or1_name = customerService.get(customer_0or1).getName();
List<FrontEquipListReport> list =
frontEquipReportRepository.queryFrontEquipListReport(customer_2, customer_0or1);
List<FrontEquipListReport_subtype> list_subtype_prod =
frontEquipReportRepository.queryFrontEquipListReport_header(customer_2, customer_0or1);
XSSFWorkbook wb = new XSSFWorkbook();
Sheet sheet = wb.createSheet();
int rownum = 0;
// 标题
Row title = sheet.createRow(rownum++);
Cell title_cell = title.createCell(0);
title_cell.setCellValue(customer_2_name + customer_0or1_name + "前端设备明细表");
CellStyle title_style = wb.createCellStyle();
Font title_font = wb.createFont();
title_font.setFontHeightInPoints((short) 16);
// f.setColor(IndexedColors.RED.getIndex());
title_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
title_style.setFont(title_font);
title_style.setAlignment(CellStyle.ALIGN_CENTER);
title_style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
title_cell.setCellStyle(title_style);
// 和并单元格
// sheet.addMergedRegion(new CellRangeAddress(0, (short) 0, 0, (short) 15));
// ===========================================================================================
rownum = exportFrontEquipListReport_header_assetclean(list_subtype_prod, rownum, wb, sheet);
// =============================================================================================
// 开始构建整个excel的文件
// 处理对应的品名在excel的哪一列
Map<String, Integer> prod_col_index_map = new HashMap<String, Integer>();
int cellIndex = 3;
for (FrontEquipListReport_subtype subtype : list_subtype_prod) {
for (FrontEquipListReport_prod prod : subtype.getProds()) {
prod_col_index_map.put(prod.getProd_id(), cellIndex);
cellIndex = cellIndex + 2;
}
}
if (list != null && list.size() > 0) {
int i = 1;
for (FrontEquipListReport customer : list) {
Row row = sheet.createRow(rownum++);
Cell cell_prod_0 = row.createCell(0);
cell_prod_0.setCellValue(i);
// cell_prod_0.setCellStyle(style);
Cell cell_prod_1 = row.createCell(1);
cell_prod_1.setCellValue(customer.getPole_code());
Cell cell_prod_2 = row.createCell(2);
cell_prod_2.setCellValue(customer.getPole_name());
for (FrontEquipListReport_prod prod : customer.getProdes()) {
Cell cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()));
cell_prod.setCellValue(prod.getNum());
// cell_prod.setCellStyle(style);
cell_prod = row.createCell(prod_col_index_map.get(prod.getProd_id()) + 1);
cell_prod.setCellValue(prod.getValue_net().doubleValue());
}
i++;
}
// 对标题行 进行单元格合并
sheet.addMergedRegion(
new CellRangeAddress(0, (short) 0, 0, (short) prod_col_index_map.size()));
}
sheet.createFreezePane(3, 3);
// 添加总计一行
CellStyle style_sum = wb.createCellStyle();
Font style_sum_font = wb.createFont();
style_sum_font.setFontHeightInPoints((short) 12);
// f.setColor(IndexedColors.RED.getIndex());
style_sum_font.setBoldweight(Font.BOLDWEIGHT_BOLD);
style_sum.setFont(style_sum_font);
style_sum.setAlignment(CellStyle.ALIGN_RIGHT);
style_sum.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
Row row_sum = sheet.createRow(rownum++);
Cell cell_sum_2 = row_sum.createCell(2);
cell_sum_2.setCellValue("小计:");
cell_sum_2.setCellStyle(style_sum);
for (int i = 3; i < cellIndex; i++) {
String col = CellReference.convertNumToColString(i);
Cell cell_sum = row_sum.createCell(i);
// =SUM(C4:C29) 从第4行开始到最后一样
// cell_sum.setCellValue("SUM("+col+"4:"+col+(rownum-1)+")");
cell_sum.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
cell_sum.setCellStyle(style_sum);
Cell cell_net = row_sum.createCell(i);
cell_net.setCellFormula("SUM(" + col + "5:" + col + (rownum - 1) + ")");
cell_net.setCellStyle(style_sum);
}
String filename = customer_2_name + customer_0or1_name + "前端设备明细表-净资产.xlsx";
// FileOutputStream out = new FileOutputStream(filename);
response.setHeader(
"content-disposition",
"attachment; filename=" + new String(filename.getBytes("UTF-8"), "ISO8859-1"));
// response.setContentType("application/vnd.ms-excel;charset=uft-8");
response.setContentType(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=uft-8");
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
}
@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 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;
}
/**
* 创建excel文档，
*
* @param list 数据
* @param keys list中map的key数组集合
* @param columnNames excel的列名
*/
public static Workbook createWorkBook(List<Map<String, Object>> listMap, List<?> list) {
// 创建excel工作簿
Workbook wb = new HSSFWorkbook();
// 创建第一个sheet（页），并命名
Sheet sheet = wb.createSheet("数据");
// 手动设置列宽。第一个参数表示要为第几列设；，第二个参数表示列的宽度，n为列高的像素数。
/* for(int i=0;i<keys.length;i++){
sheet.setColumnWidth((short) i, (short) (35.7 * 150));
}*/
// 创建第一行
Row row = sheet.createRow((short) 0);
// 创建两种单元格格式
CellStyle cs = wb.createCellStyle();
CellStyle cs2 = wb.createCellStyle();
// 创建两种字体
Font f = wb.createFont();
Font f2 = wb.createFont();
// 创建第一种字体样式（用于列名）
f.setFontHeightInPoints((short) 10);
f.setColor(IndexedColors.BLACK.getIndex());
f.setBoldweight(Font.BOLDWEIGHT_BOLD);
// 创建第二种字体样式（用于值）
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.BLACK.getIndex());
// Font f3=wb.createFont();
// f3.setFontHeightInPoints((short) 10);
// f3.setColor(IndexedColors.RED.getIndex());
// 设置第一种单元格的样式（用于列名）
cs.setFont(f);
cs.setBorderLeft(CellStyle.BORDER_THIN);
cs.setBorderRight(CellStyle.BORDER_THIN);
cs.setBorderTop(CellStyle.BORDER_THIN);
cs.setBorderBottom(CellStyle.BORDER_THIN);
cs.setAlignment(CellStyle.ALIGN_CENTER);
// 设置第二种单元格的样式（用于值）
cs2.setFont(f2);
cs2.setBorderLeft(CellStyle.BORDER_THIN);
cs2.setBorderRight(CellStyle.BORDER_THIN);
cs2.setBorderTop(CellStyle.BORDER_THIN);
cs2.setBorderBottom(CellStyle.BORDER_THIN);
cs2.setAlignment(CellStyle.ALIGN_CENTER);
for (int i = 0; i < listMap.size(); i++) {
if (Boolean.parseBoolean(listMap.get(i).get("hide") + "")) {
listMap.remove(listMap.get(i));
}
}
// 设置列名
for (int i = 0; i < listMap.size(); i++) {
Cell cell = row.createCell(i);
cell.setCellValue(listMap.get(i).get("name") + "");
cell.setCellStyle(cs);
}
// 设置每行每列的值
for (short i = 0; i < list.size(); i++) {
// Row 行,Cell 方格 , Row 和 Cell 都是从0开始计数的
// 创建一行，在页sheet上
Row row1 = sheet.createRow((short) i + 1);
// 在row行上创建一个方格
for (int j = 0; j < listMap.size(); j++) {
Cell cell = row1.createCell(j);
Map<String, Object> map = (Map<String, Object>) list.get(i);
cell.setCellValue(
map.get(listMap.get(j).get("colkey")) == null
? " "
: map.get(listMap.get(j).get("colkey")).toString());
cell.setCellStyle(cs2);
}
}
return wb;
}
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;
}
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;
}
