POI-XSSF简介

一、简介

Apache POI XSSF是用来处理Microsoft Excel格式文件的Java API，它提供了创建、修改、读取和写入XLS电子表格的方法。

XSSF与HSSF的区别：HSSF是处理`.xls`格式的Microsoft Excel文件，与97–2003版本兼容；XSSF是处理`.xls`或`.xlsx`格式的Microsoft Excel和OpenOffice xml文件，与2007或更高版本兼容。

二、安装

pom.xml：

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>5.0.0</version>
</dependency>

三、核心类

工作簿(Workbook)

XSSFWorkbook：

XSSFWorkbook workbook = new XSSFWorkbook();

表格(Sheet)

XSSFSheet：

XSSFSheet sheet = workbook.createSheet("Hello World");

行(Row)

XSSFRow：

XSSFRow row = sheet.createRow(0);

单元格(Cell)

XSSFCell：

XSSFCell cell = row.createCell(0);

单元格样式

XSSFCellStyle

XSSFCellStyle cellStyle = workbook.createCellStyle();

字体

XSSFFont：

XSSFFont font = workbook.createFont();

Helper

XSSFCreationHelper：它用作公式解析和设置超链接等的支持类。

XSSFCreationHelper createHelper = workbook.getCreationHelper();

超链接

XSSFHyperlink：

XSSFHyperlink link = createHelper.createHyperlink(HyperlinkType.URL);

四、样例

1、Hello World

创建工作簿

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Hello World");
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue("Hello");
try(FileOutputStream out = new FileOutputStream(new File("G:/Temp/POI/hello-word.xlsx"))){
	workbook.write(out);
}

运行后会生成hello-word.xlsx文件，内容如下：

A	B	C
Hello World

打开工作簿

try(FileInputStream in = new FileInputStream(new File("G:/Temp/POI/hello-word.xlsx"))){
	XSSFWorkbook workbook = new XSSFWorkbook(in);
	XSSFSheet sheet = workbook.getSheet("Hello World");
	XSSFRow row = sheet.getRow(0);
	XSSFCell cell = row.getCell(0);
	System.out.println(cell.getStringCellValue());
}

运行后输出：

Hello

2、单元格类型

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("new sheet");
XSSFCreationHelper createHelper = workbook.getCreationHelper();
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue(new Date());
//日期格式
XSSFCellStyle cellStyle = workbook.createCellStyle();
cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("YYYY-MM-dd HH:mm:ss"));
cell.setCellStyle(cellStyle);
cell = row.createCell(1);
cell.setCellValue(Calendar.getInstance());
cell.setCellStyle(cellStyle);
//不同类型
XSSFRow anotherRow = sheet.createRow(1);
anotherRow.createCell(0).setCellValue(true);
anotherRow.createCell(1).setCellValue(5.1);
anotherRow.createCell(2).setCellValue(new Date());

try(FileOutputStream out = new FileOutputStream(new File("G:/Temp/POI/workbook.xlsx"))){
	workbook.write(out);
}

运行后生成文件的内容如下：

A	B	C
2021-12-11 21:10:49	2021-12-11 21:10:49
TRUE	5.1	44541.88252

3、单元格样式

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("new sheet");
XSSFCreationHelper createHelper = workbook.getCreationHelper();
XSSFRow row = sheet.createRow(0);
//宽高
row.setHeight((short) 800);
sheet.setColumnWidth(0, 3000);
XSSFCell cell = row.createCell(0);
cell.setCellValue("Center");
XSSFCellStyle cellStyle = workbook.createCellStyle();
//对齐
cellStyle.setAlignment(HorizontalAlignment.CENTER);
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
cell.setCellStyle(cellStyle);
//边框
cell = row.createCell(1);
cell.setCellValue("Border");
XSSFCellStyle borderStyle = workbook.createCellStyle();
borderStyle.setBorderLeft(BorderStyle.THIN);
borderStyle.setBorderRight(BorderStyle.THICK);
borderStyle.setBorderTop(BorderStyle.DASH_DOT);
borderStyle.setBorderBottom(BorderStyle.DOUBLE);
cell.setCellStyle(borderStyle);
//背景色
cell = row.createCell(2);
cell.setCellValue("Fill");
XSSFCellStyle fillStyle = workbook.createCellStyle();
fillStyle.setFillBackgroundColor(IndexedColors.LIGHT_GREEN.index);
fillStyle.setFillPattern(FillPatternType.LESS_DOTS);
cell.setCellStyle(fillStyle);
//前景色
cell = row.createCell(3);
cell.setCellValue("Color");
XSSFCellStyle colorStyle = workbook.createCellStyle();
colorStyle.setFillForegroundColor(IndexedColors.BLUE.index);
colorStyle.setFillPattern(FillPatternType.BIG_SPOTS);
cell.setCellStyle(colorStyle);
try(FileOutputStream out = new FileOutputStream(new File("G:/Temp/POI/workbook.xlsx"))){
	workbook.write(out);
}

运行后生成文件的内容如下：

4、字体与超链接

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("new sheet");
XSSFCreationHelper createHelper = workbook.getCreationHelper();
XSSFRow row = sheet.createRow(0);
XSSFCell cell = row.createCell(0);
cell.setCellValue("Font Style");
sheet.setColumnWidth(0, 3500);
XSSFCellStyle cellStyle = workbook.createCellStyle();
//字体
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 14);
font.setFontName("Stencil-Normal");
font.setUnderline(Font.U_SINGLE);
font.setItalic(true);
font.setBold(true);
font.setColor(IndexedColors.BLUE.getIndex());
cellStyle.setFont(font);
cell.setCellStyle(cellStyle);
//超链接
XSSFHyperlink link = createHelper.createHyperlink(HyperlinkType.URL);
link.setAddress("https://www.baidu.com");
cell.setHyperlink(link);
try(FileOutputStream out = new FileOutputStream(new File("G:/Temp/POI/workbook.xlsx"))){
	workbook.write(out);
}

运行后生成文件的内容如下：

5、公式

XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("new sheet");
XSSFRow row = sheet.createRow(0);
XSSFRow valRow = sheet.createRow(1);
//X=2
XSSFCell cell = row.createCell(0);
cell.setCellValue("X");
XSSFCell valCell = valRow.createCell(0);
valCell.setCellValue(2);
//Y=3
cell = row.createCell(1);
cell.setCellValue("Y");
valCell = valRow.createCell(1);
valCell.setCellValue(3);
//SUM
cell = row.createCell(2);
cell.setCellValue("Total");
valCell = valRow.createCell(2);
valCell.setCellFormula("SUM(A2, B2)");
//POWER
cell = row.createCell(3);
cell.setCellValue("POWER");
valCell = valRow.createCell(3);
valCell.setCellFormula("POWER(A2, B2)");
//MAX
cell = row.createCell(4);
cell.setCellValue("MAX");
valCell = valRow.createCell(4);
valCell.setCellFormula("MAX(A2, B2)");
//解析值
XSSFCreationHelper createHelper = workbook.getCreationHelper();
createHelper.createFormulaEvaluator().evaluateAll();
try(FileOutputStream out = new FileOutputStream(new File("G:/Temp/POI/workbook.xlsx"))){
	workbook.write(out);
}