POI 操作 Excel
一、POI 简介
在我们的项目开发中,经常会面临这样一种场景,如前端上传 Excel 文件给后台,后台对 Excel 文件进行解析后,进行相应的处理;以及后台从数据库中查出相关的数据后,生成 Excel 给前端。详细了解,请移步官网。POI 官网
POI 可以操作的文件类类型有:Workbook 接口下的实现类为其可操作的文件。
- HSSF - 提供读写Microsoft Excel格式档案的功能。
- XSSF - 提供读写Microsoft Excel OOXML格式档案的功能。
- HWPF - 提供读写Microsoft Word格式档案的功能。
- HSLF - 提供读写Microsoft PowerPoint格式档案的功能。
- HDGF - 提供读写Microsoft Visio格式档案的功能。
二、 EXCEL常用操作方法
1、创建新工作簿
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo1 {
public static void main(String[] args) throws IOException {
// 定义一个新的工作簿
Workbook wb = new HSSFWorkbook();
OutputStream os = new FileOutputStream("E:\\poi.xls");
wb.write(os);
os.close();
}
}
2、创建新的 sheet 页
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo2 {
public static void main(String[] args) throws IOException {
// 定义一个新的工作簿
Workbook wb = new HSSFWorkbook();
// 创建一个 Sheet 页
wb.createSheet("第一个Sheet页");
wb.createSheet("第二个Sheet页");
OutputStream os = new FileOutputStream("E:\\sheet.xls");
wb.write(os);
os.close();
}
}
3、创建单元格
注意:单元格的所在行是从 0 开始计数,而列则是从 1 开始计数。
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo3 {
public static void main(String[] args) throws IOException {
// 定义一个新的工作簿
Workbook wb = new HSSFWorkbook();
// 创建一个 Sheet 页
Sheet sheet = wb.createSheet("第一个Sheet页");
// 创建一行
Row row = sheet.createRow(0);
// 创建一个单元格,第一列
Cell cell = row.createCell(0);
// 给单元格设置值
cell.setCellValue(1);
row = sheet.createRow(1);
// 创建一个单元格,第二列
cell = row.createCell(0);
// 给单元格设置值
cell.setCellValue("Hello Poi");
OutputStream os = new FileOutputStream("E:\\cell.xls");
wb.write(os);
os.close();
}
}
4、创建一个时间格式的单元格
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo4 {
public static void main(String[] args) throws IOException {
// 定义一个新的工作簿
Workbook wb = new HSSFWorkbook();
// 创建一个 Sheet 页
Sheet sheet = wb.createSheet("第一个Sheet页");
// 创建一行
Row row = sheet.createRow(0);
// 创建一个单元格,第一列
Cell cell = row.createCell(0);
// 给单元格设置值
cell.setCellValue(new Date());
OutputStream os = new FileOutputStream("E:\\time.xls");
wb.write(os);
os.close();
}
}
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo4 {
public static void main(String[] args) throws IOException {
// 定义一个新的工作簿
Workbook wb = new HSSFWorkbook();
// 创建一个 Sheet 页
Sheet sheet = wb.createSheet("第一个Sheet页");
// 创建一行
Row row = sheet.createRow(0);
// 创建一个单元格,第一列
Cell cell = row.createCell(0);
// 给单元格设置值
cell.setCellValue(new Date());
CreationHelper creationHelper = wb.getCreationHelper();
// 单元格样式类
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:MM:ss"));
// 第二列
cell = row.createCell(1);
cell.setCellValue(new Date());
cell.setCellStyle(cellStyle);
OutputStream os = new FileOutputStream("E:\\time.xls");
wb.write(os);
os.close();
}
}
5、处理不同内容格式的单元格
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Date;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo5 {
public static void main(String[] args) throws IOException {
// 定义一个新的工作簿
Workbook wb = new HSSFWorkbook();
// 创建一个 Sheet 页
Sheet sheet = wb.createSheet("第一个Sheet页");
// 创建一行
Row row = sheet.createRow(0);
row.createCell(0).setCellValue(new Date());
row.createCell(1).setCellValue(1);
row.createCell(2).setCellValue("一个字符串");
row.createCell(3).setCellValue(true);
OutputStream os = new FileOutputStream("E:\\kind.xls");
wb.write(os);
os.close();
}
}
6、遍历工作簿的行和列并获取单元格内容
倘若存在如下一个 xls 文件,获取其中的内容。
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import java.io.*;
import java.util.Date;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo6 {
public static void main(String[] args) throws IOException {
InputStream is = new FileInputStream("E:\\data.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
// 获取第一个 Sheet 页
HSSFSheet sheet = wb.getSheetAt(0);
if (sheet == null){
return;
}
// 遍历行 Row
for (int rowNum = 0;rowNum <= sheet.getLastRowNum();rowNum++){
HSSFRow row = sheet.getRow(rowNum);
if(row == null)
continue;
// 遍历列 Cell
for (int cellNum = 0; cellNum <= row.getLastCellNum();cellNum++){
HSSFCell cell = row.getCell(cellNum);
if (cell == null){
continue;
}
System.out.print(getValue(cell) + " ");
}
System.out.println();
}
}
// 判断 cell 中数据的类型
private static String getValue(HSSFCell hssfCell){
if (hssfCell.getCellType() == CellType.BOOLEAN) {
return String.valueOf(hssfCell.getBooleanCellValue());
}
else if (hssfCell.getCellType() == CellType.STRING) {
return String.valueOf(hssfCell.getStringCellValue());
}
else {
return String.valueOf(hssfCell.getNumericCellValue());
}
}
}
控制台输出如下:
注意:我们需要先判断单元格中的数据类型,只能以对应的类型调用相应方法获取值才 OK ,否则报错。
7、文本提取
package com.wangzhao.poi;
import org.apache.poi.hssf.extractor.EventBasedExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.extractor.ExcelExtractor;
import org.apache.poi.ss.usermodel.CellType;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo7 {
public static void main(String[] args) throws IOException {
InputStream is = new FileInputStream("E:\\data.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
ExcelExtractor excelExtractor = new EventBasedExcelExtractor(fs);
System.out.println(excelExtractor.getText());
}
}
控制台输出如下:
package com.wangzhao.poi;
import org.apache.poi.hssf.extractor.EventBasedExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.extractor.ExcelExtractor;
import org.apache.poi.ss.usermodel.CellType;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo7 {
public static void main(String[] args) throws IOException {
InputStream is = new FileInputStream("E:\\data.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
ExcelExtractor excelExtractor = new EventBasedExcelExtractor(fs);
// 我们不需要 sheet 页的 name
excelExtractor.setIncludeSheetNames(false);
System.out.println(excelExtractor.getText());
}
}
控制台输出如下:
8、单元格高级操作
1、单元格对齐方式
package com.wangzhao.poi;
import javafx.concurrent.Worker;
import org.apache.poi.hssf.extractor.EventBasedExcelExtractor;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.extractor.ExcelExtractor;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.util.Date;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo8 {
public static void main(String[] args) throws IOException {
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
OutputStream os = new FileOutputStream("E:\\poi.xls");
Sheet sheet = wb.createSheet("Sheet 1"); // 创建第一个 Sheet 页
Row row = sheet.createRow(2); // 创建一个行
row.setHeightInPoints(30); // 设置行高
createCell(wb,row,(short) 0,HorizontalAlignment.CENTER,VerticalAlignment.BOTTOM);
createCell(wb,row,(short) 1,HorizontalAlignment.RIGHT,VerticalAlignment.CENTER);
createCell(wb,row,(short) 2,HorizontalAlignment.GENERAL,VerticalAlignment.DISTRIBUTED);
createCell(wb,row,(short) 3,HorizontalAlignment.JUSTIFY,VerticalAlignment.JUSTIFY);
createCell(wb,row,(short) 4,HorizontalAlignment.LEFT,VerticalAlignment.TOP);
wb.write(os);
os.close();
}
/**
* 创建一个单元格并为其设置指定的对其方式
* @param wb 工作簿
* @param row
* @param column
* @param halign
* @param valign
*/
private static void createCell(Workbook wb, Row row, short column, HorizontalAlignment halign, VerticalAlignment valign){
Cell cell = row.createCell(column); // 创建单元格
cell.setCellValue(new HSSFRichTextString("Align It")); // 设置值
CellStyle cellStyle = wb.createCellStyle(); // 创建单元格样式
cellStyle.setAlignment(halign); // 设置单元格水平对其方式
cellStyle.setVerticalAlignment(valign); // 设置单元格垂直对其方式
cell.setCellStyle(cellStyle); // 设置单元格样式
}
}
2、单元格边框处理
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo9 {
public static void main(String[] args) throws IOException {
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("Sheet 1"); // 创建第一个 Sheet 页
Row row = sheet.createRow(2); // 创建一个行
Cell cell = row.createCell(1);
cell.setCellValue("Hello World");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setBorderBottom(BorderStyle.MEDIUM); // 底部边框
cellStyle.setBottomBorderColor(IndexedColors.YELLOW.getIndex()); // 底部颜色
cellStyle.setBorderLeft(BorderStyle.THIN); // 左边边框
cellStyle.setBottomBorderColor(IndexedColors.GREEN.getIndex()); // 左边边框颜色
cellStyle.setBorderRight(BorderStyle.MEDIUM); // 右边边框
cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 右边边框颜色
cellStyle.setBorderTop(BorderStyle.MEDIUM); // 顶部边框
cellStyle.setBottomBorderColor(IndexedColors.RED.getIndex()); // 右边边框颜色
cell.setCellStyle(cellStyle);
OutputStream os = new FileOutputStream("E:\\poi.xls");
wb.write(os);
os.close();
}
}
3、单元格填充色和颜色操作
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo10 {
public static void main(String[] args) throws IOException {
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("Sheet 1"); // 创建第一个 Sheet 页
Row row = sheet.createRow(2); // 创建一个行
Cell cell = row.createCell(1); // 创建一个单元格
cell.setCellValue("Hello World");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
cell.setCellStyle(cellStyle);
OutputStream os = new FileOutputStream("E:\\poi.xls");
wb.write(os);
os.close();
}
}
4、单元格合并
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo10 {
public static void main(String[] args) throws IOException {
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("Sheet 1"); // 创建第一个 Sheet 页
Row row = sheet.createRow(2); // 创建一个行
Cell cell = row.createCell(1);
cell.setCellValue("合并单元格测试");
sheet.addMergedRegion(new CellRangeAddress(
1, // 起始行
1, // 结束行
1, // 起始列
2 // 结束列
));
OutputStream os = new FileOutputStream("E:\\poi.xls");
wb.write(os);
os.close();
}
}
9、字体处理
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo11 {
public static void main(String[] args) throws IOException {
Workbook wb = new HSSFWorkbook(); // 定义一个新的工作簿
Sheet sheet = wb.createSheet("Sheet 1"); // 创建第一个 Sheet 页
Row row = sheet.createRow(2); // 创建一个行
// 创建一个字体处理类
Font font = wb.createFont();
font.setFontHeightInPoints((short)24);
font.setFontName("Courier New");
font.setItalic(true);
font.setStrikeout(true);
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font);
Cell cell = row.createCell(1);
cell.setCellValue("Hello World !");
cell.setCellStyle(cellStyle);
OutputStream os = new FileOutputStream("E:\\poi.xls");
wb.write(os);
os.close();
}
}
10、读取和重写单元格
package com.wangzhao.poi;
import java.io.*;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo12 {
public static void main(String[] args) throws IOException {
InputStream is = new FileInputStream("E:\\poi.xls");
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
HSSFRow row = sheet.createRow(0);
HSSFCell cell = row.createCell(1);
cell.setCellValue("I am a cell");
OutputStream os = new FileOutputStream("E:\\poi.xls");
wb.write(os);
os.close();
}
}
11、换行
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo13 {
public static void main(String[] args) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("0");
Row row = sheet.createRow(1);
Cell cell = row.createCell(1);
cell.setCellValue("我要换行\n换行成功!");
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setWrapText(true);
cell.setCellStyle(cellStyle);
OutputStream os = new FileOutputStream("E:\\poi.xls");
wb.write(os);
os.close();
}
}
12、数据格式
package com.wangzhao.poi;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
/**
* @author wangzhao
* @date 2019/11/28 15:57
*/
public class Demo14 {
public static void main(String[] args) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet = wb.createSheet("0");
DataFormat format = wb.createDataFormat();
Row row;
Cell cell;
int rowNum = 0;
int celllNum = 0;
row = sheet.createRow(rowNum++);
cell = row.createCell(celllNum++);
cell.setCellValue(111111.25);
CellStyle style = wb.createCellStyle();
style.setDataFormat(format.getFormat("0.0")); // 设置数据格式
cell.setCellStyle(style);
row = sheet.createRow(rowNum++);
cell = row.createCell(celllNum++);
cell.setCellValue(111111.25);
style = wb.createCellStyle();
style.setDataFormat(format.getFormat("#,##0.000")); // 设置数据格式
cell.setCellStyle(style);
OutputStream os = new FileOutputStream("E:\\poi.xls");
wb.write(os);
os.close();
}
}