/**
* 创建表格样式
*
* @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;
}
public void insertDataToExcel(int numRow, Object[] object) {
try {
if (null != wb.getSheetAt(0)) {
Sheet aSheet = wb.getSheetAt(0);
Row row = aSheet.getRow((short) numRow);
if (row == null) row = aSheet.createRow((short) numRow);
for (int i = 0; i < object.length; i++) {
Cell csCell = row.createCell((short) i);
CellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
if (object[i] != null) csCell.setCellValue(object[i].toString());
else csCell.setCellValue("0");
}
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
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 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;
}
private void getAllAroundBorder(CellStyle style) {
style.setBorderBottom(CellStyle.BORDER_THIN);
style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderLeft(CellStyle.BORDER_THIN);
style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderRight(CellStyle.BORDER_THIN);
style.setRightBorderColor(IndexedColors.BLACK.getIndex());
style.setBorderTop(CellStyle.BORDER_THIN);
style.setTopBorderColor(IndexedColors.BLACK.getIndex());
}
/** 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 static void setStyle(Workbook wb) {
titleFont = wb.createFont();
titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
titleStyle = wb.createCellStyle();
titleStyle.setBorderBottom(CellStyle.BORDER_THIN);
titleStyle.setBorderLeft(CellStyle.BORDER_THIN);
titleStyle.setBorderRight(CellStyle.BORDER_THIN);
titleStyle.setBorderTop(CellStyle.BORDER_THIN);
titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
titleStyle.setFont(titleFont);
contentStyle = wb.createCellStyle();
contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
contentStyle.setBorderRight(CellStyle.BORDER_THIN);
contentStyle.setBorderTop(CellStyle.BORDER_THIN);
contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
}
private CellStyle buildCellStyle() {
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
cellStyle.setBorderTop(CellStyle.BORDER_THIN);
cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderLeft(CellStyle.BORDER_THIN);
cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderRight(CellStyle.BORDER_THIN);
cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
cellStyle.setBorderBottom(CellStyle.BORDER_THIN);
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
return cellStyle;
}
/** @Description: 初始化表头行样式 */
private static void initHeadCellStyle() {
headStyle.setAlignment(CellStyle.ALIGN_CENTER);
headStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
headStyle.setFont(headFont);
headStyle.setFillBackgroundColor(IndexedColors.YELLOW.index);
headStyle.setBorderTop(CellStyle.BORDER_MEDIUM);
headStyle.setBorderBottom(CellStyle.BORDER_THIN);
headStyle.setBorderLeft(CellStyle.BORDER_THIN);
headStyle.setBorderRight(CellStyle.BORDER_THIN);
headStyle.setTopBorderColor(IndexedColors.BLUE.index);
headStyle.setBottomBorderColor(IndexedColors.BLUE.index);
headStyle.setLeftBorderColor(IndexedColors.BLUE.index);
headStyle.setRightBorderColor(IndexedColors.BLUE.index);
}
/** @Description: 初始化内容行样式 */
private static void initContentCellStyle() {
contentStyle.setAlignment(CellStyle.ALIGN_CENTER);
contentStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
contentStyle.setFont(contentFont);
contentStyle.setBorderTop(CellStyle.BORDER_THIN);
contentStyle.setBorderBottom(CellStyle.BORDER_THIN);
contentStyle.setBorderLeft(CellStyle.BORDER_THIN);
contentStyle.setBorderRight(CellStyle.BORDER_THIN);
contentStyle.setTopBorderColor(IndexedColors.BLUE.index);
contentStyle.setBottomBorderColor(IndexedColors.BLUE.index);
contentStyle.setLeftBorderColor(IndexedColors.BLUE.index);
contentStyle.setRightBorderColor(IndexedColors.BLUE.index);
contentStyle.setWrapText(true); // 字段换行
}
/**
* Discription:[设置文件内容的显示样式]
*
* @param wb
* @return
* @author:[代超]
* @update:[日期YYYY-MM-DD] [更改人姓名][变更描述]
*/
public CellStyle setContentSheetSysle(Workbook wb) {
CellStyle style = wb.createCellStyle();
// 生成并设置另一个样式
style.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.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);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
// 生成一个字体
Font font = wb.createFont();
font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
// 把字体应用到当前的样式
style.setFont(font);
return style;
}
/**
* 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;
}
public void addBorder(
int startRowIndex,
int endRowIndex,
int startColumnIndex,
int endColumnIndex,
short borderWeight) {
CellStyle cellStyle = null;
for (int i = startRowIndex; i <= endRowIndex; i++) {
for (int j = startColumnIndex; j <= endColumnIndex; j++) {
HSSFCell cell = getCell(i, j);
cellStyle = createCellStyle();
cellStyle.cloneStyleFrom(cell.getCellStyle());
cellStyle.setBorderBottom(borderWeight);
cellStyle.setBorderLeft(borderWeight);
cellStyle.setBorderRight(borderWeight);
cellStyle.setBorderTop(borderWeight);
cell.setCellStyle(cellStyle);
}
}
}
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;
}
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;
}
/**
* Creates a new instance.
*
* @param workbook target workbook
* @throws IllegalArgumentException if some parameters were {@code null}
*/
WorkbookInfo(Workbook workbook) {
if (workbook == null) {
throw new IllegalArgumentException("workbook must not be null"); // $NON-NLS-1$
}
this.workbook = workbook;
Font font = workbook.createFont();
commonStyle = workbook.createCellStyle();
commonStyle.setFont(font);
commonStyle.setBorderTop(BorderStyle.THIN);
commonStyle.setBorderBottom(BorderStyle.THIN);
commonStyle.setBorderLeft(BorderStyle.THIN);
commonStyle.setBorderRight(BorderStyle.THIN);
titleStyle = workbook.createCellStyle();
titleStyle.cloneStyleFrom(commonStyle);
titleStyle.setLocked(true);
titleStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
titleStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex());
titleStyle.setAlignment(HorizontalAlignment.CENTER);
CreationHelper helper = workbook.getCreationHelper();
DataFormat df = helper.createDataFormat();
dataStyle = workbook.createCellStyle();
dataStyle.cloneStyleFrom(commonStyle);
dateDataStyle = workbook.createCellStyle();
dateDataStyle.cloneStyleFrom(commonStyle);
dateDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd")); // $NON-NLS-1$
timeDataStyle = workbook.createCellStyle();
timeDataStyle.cloneStyleFrom(commonStyle);
timeDataStyle.setDataFormat(df.getFormat("hh:mm:ss")); // $NON-NLS-1$
datetimeDataStyle = workbook.createCellStyle();
datetimeDataStyle.cloneStyleFrom(commonStyle);
datetimeDataStyle.setDataFormat(df.getFormat("yyyy-mm-dd hh:mm:ss")); // $NON-NLS-1$
}
public void setCellMoney(int numRow, int numCol) {
try {
if (wb.getSheetAt(0) != null) {
Sheet aSheet = wb.getSheetAt(0);
DataFormat format = wb.createDataFormat();
Row row = aSheet.getRow((short) numRow);
Cell csCell = row.getCell((short) numCol);
CellStyle style = wb.createCellStyle();
style.setDataFormat(format.getFormat("0.00"));
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBottomBorderColor(HSSFColor.BLACK.index);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setLeftBorderColor(HSSFColor.BLACK.index);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setRightBorderColor(HSSFColor.BLACK.index);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
style.setTopBorderColor(HSSFColor.BLACK.index);
csCell.setCellStyle(style);
}
} catch (Exception e) {
System.out.println("insertDataToExcel" + e);
}
}
@Test
public void test_poi() {
final int rowNum = 27;
final int colNum = 15;
HSSFWorkbook wb = null;
Sheet sheet = null;
String today = "2013/8/31";
String sign = "Month to date";
String[] titles = {
"",
"",
"",
"Chinapay eMail\r\n 商城总计",
"Japan Page\r\n 日本馆首页",
"Taiwan Page\r\n 台湾馆首页",
"USA Page\r\n 美国馆首页",
"Anhui Page\r\n 安徽馆首页",
"China Page\r\n 中国馆首页"
};
String[] colNames = {
"",
"Page View (PV)\r\n 浏览量",
"Unique Visitor (UV)\r\n 独立访客",
"Completed Orders\r\n 确认订单",
"Transaction Amount\r\n 交易金额",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量",
"1st Top Seller\r\n 最佳销量",
"Unit Price 单价",
"Qty Sold 销量",
"2nd Top Seller\r\n 第二销量",
"Unit Price 单价",
"Qty Sold 销量",
"3rd Top Seller\r\n 第三销量",
"Unit Price 单价",
"Qty Sold 销量"
};
int n = 0;
int len = 1;
String fileName = "D:/日报.xls";
File f = new File(fileName);
ByteArrayOutputStream byteArrayOut = null;
BufferedImage bufferImg = null;
String[] jpgUrls = {
"http://img.chinapay.com/data/files/store_37452/goods_93/small_201303271804531386.jpg",
"http://img.chinapay.com/data/files/store_44066/goods_37/201308280953576580.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_95/small_201309031434558044.jpg",
"http://img.chinapay.com/data/files/store_289253/goods_180/small_201309031403003861.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_98/small_201309121508186810.jpg",
"http://img.chinapay.com/data/files/store_37452/goods_24/small_201301241133447193.jpg"
};
String[] https = {
"http://emall.chinapay.com/goods/37452/1010000109792.html",
"http://emall.chinapay.com/goods/44066/1010000119323.html",
"http://emall.chinapay.com/goods/289253/1010000119621.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/289253/1010000119627.html?jpsv=laoxcashback6",
"http://emall.chinapay.com/goods/37452/1010000120588.html",
"http://emall.chinapay.com/goods/37452/1010000107096.html"
};
URL url = null;
HSSFHyperlink link = null;
HSSFPatriarch patri = null;
HSSFClientAnchor anchor = null;
try {
if (!f.exists()) {
wb = new HSSFWorkbook();
} else {
FileInputStream in = new FileInputStream(fileName);
wb = new HSSFWorkbook(in);
}
CellStyle style = wb.createCellStyle();
style.setFillForegroundColor(HSSFCellStyle.THIN_BACKWARD_DIAG);
style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); // 垂直居中
style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// style.setLeftBorderColor(HSSFColor.RED.index);
style.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
style.setBorderLeft(HSSFCellStyle.BORDER_THIN); // 左边框
style.setBorderTop(HSSFCellStyle.BORDER_THIN); // 上边框
style.setBorderRight(HSSFCellStyle.BORDER_THIN); // 右边框
style.setWrapText(true);
sheet = wb.createSheet("sheet " + ((int) (100000 * Math.random())));
// 设置列的宽度
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeight((short) 400);
Row row = null;
Cell cell = null;
for (int r = 0; r < rowNum; r++) {
row = sheet.createRow(r);
// 设置第1行当高度
if (r == 0) {
row.setHeightInPoints(30);
}
// 设置第2列以后的宽度(即列号>=2的列，列号从0开始)
if (r >= 2) {
sheet.setColumnWidth(r, 3020);
}
for (int c = 0; c < colNum; c++) {
cell = row.createCell(c);
cell.setCellStyle(style);
// 处理第一行
if (r == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r, 3, 4));
sheet.addMergedRegion(new CellRangeAddress(r, r, 5, 6));
sheet.addMergedRegion(new CellRangeAddress(r, r, 7, 8));
sheet.addMergedRegion(new CellRangeAddress(r, r, 9, 10));
sheet.addMergedRegion(new CellRangeAddress(r, r, 11, 12));
sheet.addMergedRegion(new CellRangeAddress(r, r, 13, 14));
if (c < 3) {
cell.setCellValue(titles[n++]);
} else {
if ((c & 1) == 1) {
System.out.println("c===" + c);
cell.setCellValue(titles[n++]);
}
}
}
// 处理第2~8行
if (r > 0 && r <= 8) {
if (c == 0) {
if (r < 8 && (r & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 1, 0, 0));
System.err.println("row----->" + r + " len----->" + (len));
cell.setCellValue(colNames[len++]);
} else if (r > 8) {
System.out.println("len+++++++++>" + (len));
cell.setCellValue(colNames[len++]);
}
} else if (c == 1) {
cell.setCellValue((r & 1) == 1 ? today : sign);
System.err.println("r---->" + r);
} else if (c == 2) {
cell.setCellValue((r & 1) == 1 ? "当天" : "当月");
} else {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
cell.setCellValue("26.55");
}
}
}
// 处理第8行以后的数据(不包括第8行)
if (r > 8) {
// 设置列高(图片的高度)
if (r % 3 == 0) {
sheet.getRow(r).setHeightInPoints(110);
}
if (c == 0) {
System.err.println("r---->" + r);
cell.setCellValue(colNames[r - 4]);
} else if (c == 1) {
cell.setCellValue((r % 3) == 0 ? today : (r % 3 == 1 ? "PV 浏览量" : "Total Sales 总额"));
} else if (c == 2) {
if (r % 9 == 0) {
sheet.addMergedRegion(new CellRangeAddress(r, r + 8, c, c));
if (r / 9 == 1) cell.setCellValue("当天");
else cell.setCellValue("当月");
cell.setCellStyle(style);
}
} else {
if (r % 3 == 0) {
if ((c & 1) == 1) {
sheet.addMergedRegion(new CellRangeAddress(r, r, c, c + 1));
// 添加远程图片信息
url = new URL(jpgUrls[(c - 3) / 2]);
bufferImg = ImageIO.read(url.openStream());
byteArrayOut = new ByteArrayOutputStream();
ImageIO.write(bufferImg, "jpg", byteArrayOut);
patri = (HSSFPatriarch) sheet.createDrawingPatriarch();
anchor = new HSSFClientAnchor(10, 2, 0, 0, (short) c, r, (short) (c + 2), r + 1);
patri.createPicture(
anchor,
wb.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
bufferImg.flush();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// System.out.println(https[(c-3)/2]);
// link.setAddress("fetion/"+https[(c-3)/2]);
// cell.setHyperlink(link);
// link = (HSSFHyperlink) cell.getHyperlink();
// link = new HSSFHyperlink(HSSFHyperlink.LINK_URL);
// link.setAddress(https[(c-3)/2]);
// cell.setHyperlink(link);
}
} else {
if ((c & 1) == 0) {
link = wb.getCreationHelper().createHyperlink(Hyperlink.LINK_URL);
link.setAddress(https[(c - 3) / 2]);
cell.setHyperlink(link); // 设定单元格的链接
cell.setCellValue("图片超链接");
} else {
cell.setCellValue("Number");
}
}
}
}
}
}
// 备注
row = sheet.createRow(27);
cell = row.createCell(0);
sheet.addMergedRegion(new CellRangeAddress(27, 27, 0, colNum - 1));
cell.setCellValue("* 销量排名不以销售金额计算，如相同销量者，则以PV量少者为优胜");
FileOutputStream out = new FileOutputStream(fileName);
wb.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("++++++++++++ EXCEl文件 success +++++++++++++");
}
/**
* 创建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;
}
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;
}
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;
}