POI是apache提供的一个读写Excel文档的开源组件,在操作excel时常要合并单元格,合并单元格的方法是:
sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 2));
public CellRangeAddress(int firstRow,int lastRow, int firstCol, int lastCol)
自适应列宽度:
sheet.autoSizeColumn(1);
sheet.autoSizeColumn(1, true);
这两种方式都是自适应列宽度,但是注意这个方法在后边的版本才提供,poi的版本不要太老。 注意:第一个方法在合并单元格的的单元格并不好使,必须用第二个方法。
sheet.setColumnWidth(m, “列名”.getBytes().length*2*256);
这个方法是计算字符串的长度,以便设置列宽,该方法在解决中文的问题上比较好,前面两种方法对中文不好好用。。。。
还有在自适应宽度的时候,有时候遇到单元格是公式单元格,自适应不起作用,那是因为单元格存的是公式,并不是真正的数据,解决方法:
HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet.getWorkbook());
CellValue cell71Val = evaluator.evaluate(cell71);
cell71.setCellValue(cell71Val.getNumberValue());
将格式化后的数据再次set进去,就是真正的值了。
POI IndexedColors 所有颜色索引
olor
org.apache.poi.hssf.util.HSSFColor$GREY_80_PERCENT@1aadbf7
color
org.apache.poi.hssf.util.HSSFColor$INDIGO@4f4458
color
org.apache.poi.hssf.util.HSSFColor$PLUM@100c56
color
org.apache.poi.hssf.util.HSSFColor$BROWN@19a1fae
color
org.apache.poi.hssf.util.HSSFColor$OLIVE_GREEN@1960c9e
color
org.apache.poi.hssf.util.HSSFColor$DARK_GREEN@168f39
color
org.apache.poi.hssf.util.HSSFColor$SEA_GREEN@11525cd
color
org.apache.poi.hssf.util.HSSFColor$DARK_TEAL@164e67f
color
org.apache.poi.hssf.util.HSSFColor$GREY_40_PERCENT@158cfda
color
org.apache.poi.hssf.util.HSSFColor$BLUE_GREY@1b613c0
color
org.apache.poi.hssf.util.HSSFColor$ORANGE@cae1b3
color
org.apache.poi.hssf.util.HSSFColor$LIGHT_ORANGE@1a7e798
color
org.apache.poi.hssf.util.HSSFColor$GOLD@55d1ef
color
org.apache.poi.hssf.util.HSSFColor$LIME@49b9ad
color
org.apache.poi.hssf.util.HSSFColor$AQUA@3d0f0e
color
org.apache.poi.hssf.util.HSSFColor$LIGHT_BLUE@a7624e
color
org.apache.poi.hssf.util.HSSFColor$TAN@1271218
color
org.apache.poi.hssf.util.HSSFColor$LAVENDER@150b2d
color
org.apache.poi.hssf.util.HSSFColor$ROSE@190c9dc
color
org.apache.poi.hssf.util.HSSFColor$PALE_BLUE@b4bc8e
color
org.apache.poi.hssf.util.HSSFColor$LIGHT_YELLOW@1c74456
color
org.apache.poi.hssf.util.HSSFColor$LIGHT_GREEN@15783a2
color
org.apache.poi.hssf.util.HSSFColor$LIGHT_TURQUOISE@830993
color
org.apache.poi.hssf.util.HSSFColor$SKY_BLUE@e99642
color
org.apache.poi.hssf.util.HSSFColor$BLUE@187f194
color
org.apache.poi.hssf.util.HSSFColor$TEAL@55f9f
color
org.apache.poi.hssf.util.HSSFColor$DARK_RED@c8e08f
color
org.apache.poi.hssf.util.HSSFColor$VIOLET@edd19
color
org.apache.poi.hssf.util.HSSFColor$TURQUOISE@1d5c7a1
color
org.apache.poi.hssf.util.HSSFColor$YELLOW@252119
color
org.apache.poi.hssf.util.HSSFColor$PINK@19ff062
color
org.apache.poi.hssf.util.HSSFColor$DARK_BLUE@15eb4d0
color
org.apache.poi.hssf.util.HSSFColor$LIGHT_CORNFLOWER_BLUE@b0e84b
color
org.apache.poi.hssf.util.HSSFColor$ROYAL_BLUE@62a19d
color
org.apache.poi.hssf.util.HSSFColor$CORAL@16075b3
color
org.apache.poi.hssf.util.HSSFColor$ORCHID@1cf46c2
color
org.apache.poi.hssf.util.HSSFColor$LIGHT_TURQUOISE@12e8a41
color
org.apache.poi.hssf.util.HSSFColor$LEMON_CHIFFON@76ec7a
color
org.apache.poi.hssf.util.HSSFColor$PLUM@19f7952
color
org.apache.poi.hssf.util.HSSFColor$CORNFLOWER_BLUE@d60cdd
color
org.apache.poi.hssf.util.HSSFColor$GREY_50_PERCENT@e6bc11
color
org.apache.poi.hssf.util.HSSFColor$GREY_25_PERCENT@452267
color
org.apache.poi.hssf.util.HSSFColor$TEAL@d5b614
color
org.apache.poi.hssf.util.HSSFColor$VIOLET@a4e3ae
color
org.apache.poi.hssf.util.HSSFColor$DARK_YELLOW@15fd4a3
color
org.apache.poi.hssf.util.HSSFColor$DARK_BLUE@8137c9
color
org.apache.poi.hssf.util.HSSFColor$GREEN@1757596
color
org.apache.poi.hssf.util.HSSFColor$DARK_RED@7ad4d5
color
org.apache.poi.hssf.util.HSSFColor$TURQUOISE@2aee3f
color
org.apache.poi.hssf.util.HSSFColor$PINK@7f788b
color
org.apache.poi.hssf.util.HSSFColor$YELLOW@c311d5
color
org.apache.poi.hssf.util.HSSFColor$BLUE@c7e580
color
org.apache.poi.hssf.util.HSSFColor$BRIGHT_GREEN@1ac55ac
color
org.apache.poi.hssf.util.HSSFColor$RED@12cc460
color
org.apache.poi.hssf.util.HSSFColor$WHITE@10ad7e
color
org.apache.poi.hssf.util.HSSFColor$BLACK@ee336f
完整示例:
Sheet sheet = book.createSheet("mimvp_report.xls");
HSSFCellStyle titleStyle = (HSSFCellStyle) book.createCellStyle();
Font ztFont = book.createFont();
ztFont.setColor(Font.COLOR_RED); // 将字体设置为“红色”
ztFont.setFontHeightInPoints((short) 12);// 将字体大小设置为12px
ztFont.setFontName("楷体"); // 将“华文行楷”字体应用到当前单元格上
ztFont.setBold(true);
titleStyle.setFont(ztFont);
titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 创建单元格样式对象
HSSFCellStyle alignStyle = (HSSFCellStyle) book.createCellStyle();
alignStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
alignStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
// 标题行
Row th = sheet.createRow((short) 0);
for (int i = 0; i < titles.length; i++) {
Cell cell = th.createCell(i);
cell.setCellStyle(titleStyle);
cell.setCellValue(titles[i]);
sheet.setColumnWidth(i, 512 * titles[i].length()); // 设置动态列宽
}
// 合并单元格
book.getSheet(sheetname).addMergedRegion(new CellRangeAddress(0, 1, 0, 0));
book.getSheet(sheetname).addMergedRegion(new CellRangeAddress(0, 1, 1, 1));
book.getSheet(sheetname).addMergedRegion(new CellRangeAddress(0, 1, 2, 2));
book.getSheet(sheetname).addMergedRegion(new CellRangeAddress(0, 1, 3, 3));
book.getSheet(sheetname).addMergedRegion(new CellRangeAddress(0, 1, 4, 4));
book.getSheet(sheetname).addMergedRegion(new CellRangeAddress(0, 0, 5, 9));
book.getSheet(sheetname).addMergedRegion(new CellRangeAddress(0, 0, 10, 15));
book.getSheet(sheetname).addMergedRegion(new CellRangeAddress(0, 0, 16, 25));
POI 设置宽度和行高
FileOutputStream fos = new FileOutputStream("D:\\15.xls");
HSSFWorkbook wb = new HSSFWorkbook();
// 设置cell宽度
HSSFSheet sheet = wb.createSheet("sheet1");
// api 段信息 Set the width (in units of 1/256th of a character width)
sheet.setColumnWidth(0, 20 * 256);
// 设置行高度
HSSFRow row = sheet.createRow(0);
//heightInPoints 设置的值永远是height属性值的20倍
row.setHeightInPoints(20);
HSSFRow row1 = sheet.createRow(5);
// Set the row's height or set to ff (-1) for undefined/default-height.
// Set the height in "twips" or
// 1/20th of a point.
row1.setHeight((short) (25 * 20));
HSSFCell cell = row.createCell(0);
cell.setCellValue("a1b2c3d4e5f6g7h8i9");
// 设置默认宽度、高度值
HSSFSheet sheet2 = wb.createSheet("sheet2");
sheet2.setDefaultColumnWidth(20);
sheet2.setDefaultRowHeightInPoints(20);
// 格式化单元格日期信息
HSSFDataFormat dataFormat = wb.createDataFormat();
short dataformat = dataFormat.getFormat("yyyy-mm-dd HH:MM");
HSSFCellStyle style = wb.createCellStyle();
style.setDataFormat(dataformat);
HSSFCell cell2 = sheet2.createRow(0).createCell(0);
cell2.setCellValue(new Date());
cell2.setCellStyle(style);
wb.write(fos);
fos.close();
参考推荐: