1、搭建maven项目,引入:
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
2、HSSFWorkbook用于低版本2003Excel,*.xls
XSSFWorkbook用于高版本2007+以上excel ,后缀 *.xlsx
3、使用通用接口,通过抛出异常的方式,获取Workbook ,解析excel,如:
Workbook wb = null;
try {
wb = new HSSFWorkbook(new FileInputStream(new File("cellstyle.xlsx")));
} catch (Exception e) {
wb = new XSSFWorkbook(new FileInputStream(new File("cellstyle.xlsx")));
}
CreationHelper helper=wb.getCreationHelper();//设置单元格格式,日期格式,连接格式等
4、创建页
Sheet sheet=wb.createSheet();//excel页
CellStyle style1 = workbook.createCellStyle();//创建样式 如字体,背景等
5、创建行
Row row=sheet.createRow(0);//行
row.setHeight((short) 800);//设置高度
row.setRowStyle(style);//设置行样式
6、创建单元格
Cell cell=row.createCell(0);//单元格
cell.setCellStyle(style);//设置单元格样式
7、CellStyle,Font样式使用样例:
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 30);
font.setFontName("IMPACT");
font.setItalic(true);
font.setColor(HSSFColor.BRIGHT_GREEN.index);
// Set font into style
XSSFCellStyle style = workbook.createCellStyle();
style.setFont(font);
8、运用公式举例
cell.setCellType(XSSFCell.CELL_TYPE_FORMULA);
cell.setCellFormula("SUM(C2:C3)");
9、使用链接样例
CreationHelper ch = workbook.getCreationHelper();
XSSFFont hlinkfont = workbook.createFont();
hlinkfont.setUnderline(XSSFFont.U_SINGLE);
hlinkfont.setColor(HSSFColor.BLUE.index);
XSSFCellStyle hlinkstyle = workbook.createCellStyle();
hlinkstyle.setFont(hlinkfont);
cell = spreadsheet.createRow(1).createCell( 1);
cell.setCellValue("URL Link");
XSSFHyperlink link = (XSSFHyperlink) ch.createHyperlink(Hyperlink.LINK_URL);
link.setAddress("http://write.blog.csdn.net/postedit/77742671");
cell.setHyperlink((XSSFHyperlink) link);
cell.setCellStyle(hlinkstyle);
10、设置文字方向
myStyle = workbook.createCellStyle();
myStyle.setRotation((short) 30);