POI操作办公文件
简介
将用户信息导出为 excel 表格(导出数据)
将Excel 表中的信息录入到网站数据库(习题上传..)
开发中经常会设计到 excel 的处理,如导出 Excel ,导入 Excel 到数据库中
操作 Excel 目前比较流行的就是 Apoache POI 和 阿里巴巴的 easyExcel
Apache POI 官网 :https://poi.apache.org/
包名称说明
- HSSF提供读写Microsoft Excel XLS格式档案的功能。
- XSSF提供读写Microsoft Excel OOXML XLSX格式档案的功能。
- HWPF提供读写Microsoft Word DOC格式档案的功能。
- HSLF提供读写Microsoft PowerPoint格式档案的功能。
- HDGF提供读Microsoft Visio格式档案的功能。
- HPBF提供读Microsoft Publisher格式档案的功能。
- HSMF提供读Microsoft Outlook格式档案的功能。
easyExcel
官网地址: https://github.com/alibaba/easyexcel
官方文档: https://www.yuque.com/easyexcel/doc/easyexcel
POI-Excel 写
- 建立一个项目,创建模块
- 引入 pom 依赖
<!-- xls 03 的依赖-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.9</version>
</dependency>
<!--xlsx(07)-->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.9</version>
</dependency>
<!--日期格式化工具-->
<dependency>
<groupId>joda-time</groupId>
<artifactId>joda-time</artifactId>
<version>2.10.1</version>
</dependency>
03 | 07 版本的写,就是对象不同,方法是一样的
需要注意: 2003版本和2007版本存在兼容性的问题! 03 最多只有 65535 行
/**
* excel03 写
*/
@Test
public void TestPOIWrite() throws IOException {
String path = "D:\\CloudMusic\\";
// 创建一个新的 Excel 工作簿
Workbook workbook = new HSSFWorkbook();
// 创建一个工作表
Sheet sheet =workbook.createSheet("我是测试1");
// 创建第一行
Row row1 = sheet.createRow(0);
// 创建单元格(1,1)
Cell row1Cell1 = row1.createCell(0);
row1Cell1.setCellValue("机构名称");
// 创建单元格(1,2)
Cell row1Cell2 = row1.createCell(1);
row1Cell2.setCellValue("花花幼儿园");
// 创建第二行
Row row2 = sheet.createRow(1);
// 创建单元格(2-1)
Cell row2Cell1 = row2.createCell(0);
row2Cell1.setCellValue("手机号");
// 创建单元格(2,2)
Cell row2Cell2 = row2.createCell(1);
row2Cell2.setCellValue("13403504277");
// 创建第三行
Row row3 = sheet.createRow(2);
// 创建单元格(3,1)
Cell row3Cell1 = row3.createCell(0);
row3Cell1.setCellValue("创建时间");
// 创建单元格(3,2)
Cell row3Cell2 = row3.createCell(1);
String createDate = new DateTime().toString("yyyy-MM-dd hh:mm:ss");
row3Cell2.setCellValue(createDate);
// 文件输出流
FileOutputStream fs = new FileOutputStream(path+"机构测试表03.xls");
// 将工作表写入到 文件输出流
workbook.write(fs);
fs.flush();
fs.close();
System.out.println("文件生成成功");
}
07 写入 xlsx
/**
* excel 07 版本写
*/
@Test
public void TestPOIWrit07() throws IOException {
// 创建新的 Excel 工作薄,对象和 03 不一样
Workbook workbook = new XSSFWorkbook();
// 新建一个工作表 sheet
Sheet sheet = workbook.createSheet();
// 创建第一行
Row row1 = sheet.createRow(0);
// 创建单元格(1,1)
Cell row1Cell1 = row1.createCell(0);
row1Cell1.setCellValue("机构名称");
// 创建单元格(1,2)
Cell row1Cell2 = row1.createCell(1);
row1Cell2.setCellValue("花花幼儿园");
// 创建第二行
Row row2 = sheet.createRow(1);
// 创建单元格(2,1)
Cell row2Cell1 = row2.createCell(0);
row2Cell1.setCellValue("手机号");
// 创建单元格(2,2)
Cell row2Cell2 = row2.createCell(1);
row2Cell2.setCellValue("13403504277");
// 创建第三行
Row row3 = sheet.createRow(2);
// 创建单元格(3,1)
Cell row3Cell1 = row3.createCell(0);
row3Cell1.setCellValue("创建时间");
// 创建单元格(3,2)
Cell row3Cell2 = row3.createCell(1);
String createDate = new DateTime().toString("yyyy-MM-dd hh:mm:ss");
row3Cell2.setCellValue(createDate);
// 文件输出流
FileOutputStream out = new FileOutputStream(path+"测试07.xlsx");
// 将文件写入输出流
workbook.write(out);
out.flush();
out.close();
System.out.println("文件生成完毕");
}
大文件写 HSSF 03 版
缺点;:最多只能处理65536行,否则会抛出异常
public void TestPOIBigWrite03() throws IOException {
Workbook workbook = new HSSFWorkbook();
Sheet sheet = workbook.createSheet();
for (int i = 0; i < 100000; i++) {
Row row = sheet.createRow(i);
Cell cell = row.createCell(i);
cell.setCellValue("测试"+i);
}
FileOutputStream out = new FileOutputStream(path+ "测试.xls");
workbook.write(out);
out.flush();
out.close();
}
java.lang.IllegalArgumentException: Invalid row number (65536) outside
allowable range (0..65535)
java.lang.IllegalArgumentException: Invalid column index (256). Allowable column range for BIFF8 is (0..255) or ('A'..'IV')
优点:过程中写入缓存,不操作磁盘,最后一次性写入磁盘,速度快
大文件写 XSSF 07版
缺点: 缺点:写数据时速度非常慢,非常耗内存,也会发生内存溢出,如100万条
优点: 可以写较大的数据量,如 20万条
大文件写 SXSSF
优点: 可以写非常大的数据量,如100万条甚至更多条,写数据速度快,占用更少的内存
注意:
过程中会产生临时文件,需要清理临时文件
默认由100条记录被保存在内存中,如果超过这数量,则最前面的数据被写入临时文件
如果想自定义内存中数据的数量,可以使用 new SXSSFWorkbook (数量)
SXSSFWorkbook 来自官方的解释: 实现了 “BigGridDemo” 策略的流式 XSSFWorkbook版本, 这允许写入非常大的文件而不会耗尽内存,因为任何时候只有可配置的行部门被保存在内存中。
请注意,仍然可能会消耗大量内存,这些内存基于你正在使用的功能,例如合并区域,注释,仍在只存储在内存中,因此如果广泛使用,可能需要大量内存
POI-Excel 读
03 版本
public List<String> file(MultipartFile file) throws IOException {
List<String> list = new ArrayList<>();
// 读取excel 工作簿
Workbook workbook = new HSSFWorkbook(file.getInputStream());
// 获取第一个工作表
Sheet sheet = workbook.getSheetAt(0);
// 工作表有多少行
int lastRowNum = sheet.getLastRowNum();
for (int i = 0; i < lastRowNum; i++) {
// 获得行数
Row row = sheet.getRow(i);
if(row != null){
// 获取最后一行的单元格
int cellcount = row.getPhysicalNumberOfCells();
for (int j = 0; j <cellcount ; j++) {
Cell cell = row.getCell(j);
if(cell != null){
// 获取单元格的类型
int cellType = cell.getCellType();
String stringCellValue = null;
// 判断单元格的数据类型
switch (cellType){
case HSSFCell
.CELL_TYPE_STRING:
System.out.println("String 类型");
stringCellValue = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
System.out.println("布尔类型");
stringCellValue = String.valueOf(cell.getBooleanCellValue());
break;
case HSSFCell.CELL_TYPE_BLANK:
System.out.println("空");
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if(HSSFDateUtil.isCellDateFormatted(cell)){
System.out.println("日期类型");
Date value = cell.getDateCellValue();
stringCellValue = new SimpleDateFormat("yyyy-MM-dd").format(value);
}else {
// 是数字
System.out.println("将数字转换为 字符串");
// 将 cell 类型转换为 字符串
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
stringCellValue = cell.getStringCellValue();
}
break;
case HSSFCell.CELL_TYPE_ERROR:
System.out.println("数据类型错误");
break;
default:
System.out.println("未知的数据类型");
}
System.out.print(stringCellValue+"||");
list.add(stringCellValue);
}
}
System.out.println();
}
}
return list;
}
POI 计算公式 03 xls 版本
@PostMapping("/file1")
public String file1(MultipartFile file) throws IOException {
String result = null;
Workbook workbook = new HSSFWorkbook(file.getInputStream());
Sheet sheet = workbook.getSheetAt(0);
Row row = sheet.getRow(0);
Cell cell = row.getCell(2);
// 公式计算器
FormulaEvaluator evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
// 得到类型
int cellType = cell.getCellType();
// 判断是否是公式类型
if(HSSFCell.CELL_TYPE_FORMULA == cellType){
// 得到的是公式
String cellFormula = cell.getCellFormula();
// 计算后的单元格
CellValue evaluate = evaluator.evaluate(cell);
// 计算后的结果转化为 string
result = evaluate.formatAsString();
System.out.println(result);
}
return result;
}
导出word 文件
public void export() throws IOException {
// 创建07版的word文档
XWPFDocument word = new XWPFDocument();
// 首页标题
XWPFParagraph title = word.createParagraph();
// 设置段落居中
title.setAlignment(ParagraphAlignment.valueOf(STJc.INT_CENTER));
XWPFRun titleRun = title.createRun();
titleRun.setFontSize(20);
titleRun.setFontFamily("楷体");
titleRun.setBold(Boolean.TRUE);
titleRun.setText("出师表/前出师表");
// 换行
titleRun.addBreak();
// 第二行
XWPFParagraph row1 = word.createParagraph();
// 设置左对齐
row1.setAlignment(ParagraphAlignment.CENTER);
XWPFRun row1Run = row1.createRun();
row1Run.setFontSize(12);
row1Run.setFontFamily("黑体");
row1Run.setText("作者: 诸葛亮");
row1Run.addBreak();
List<String> data = data();
for (int i = 0; i <data.size() ; i++) {
XWPFParagraph row = word.createParagraph();
row.setAlignment(ParagraphAlignment.LEFT);
XWPFRun row2Run = row.createRun();
row2Run.setFontSize(12);
row2Run.setFontFamily("黑体");
row2Run.setText(data.get(i));
row2Run.addBreak();
}
FileOutputStream fos = new FileOutputStream("C:\\Users\\ojd\\Desktop\\出师表.pdf");
word.write(fos);
fos.flush();
fos.close();
}
文档 XWPFDocument
XWPFDocument是对 .docx 文档操作的高级封装 API
创建新文档
XWPFDocument doc = new XWPFDocument();
读取已有文档:段落,表格,图片
XWDFDocument doc = new XWDFDocument(new FileInputStream(“。、deepoove.docx”));
我们就可以读取文档内容,包括段落,表格,图片等
// 段落
List<XWPFParagraph> paragraphs = doc.getParagraphs();
// 表格
List<XWPFTable> tables = doc.getTables();
// 图片
List<XWPFPictureData> allPictures = doc.getAllPictures();
// 页眉
List<XWPFHeader> headerList = doc.getHeaderList();
// 页脚
List<XWPFFooter> footerList = doc.getFooterList();
生成 文档
try (FileOutputStream out = new FileOutputStream("simple.docx")) {
doc.write(out);
}
段落 XWPFParagraph 段落
段落是组合 Word文档的一个基本单元
创建新段落
XWPFParagraph p1 = doc.createParagraph();
设置格式格式
// 对齐方式
p1.setAlignment(ParagraphAlignment.CENTER);
// 边框
p1.setBorderBottom(Borders.DOUBLE);
p1.setBorderTop(Borders.DOUBLE);
p1.setBorderRight(Borders.DOUBLE);
p1.setBorderLeft(Borders.DOUBLE);
p1.setBorderBetween(Borders.SINGLE);
基本元素 XWPFRun
XWPFRun是段落的基本单元,它可以是一个文本,可以是一张图片。
阅读文本
// 获取文字
String text = paragraph.getText();
// 获取段落内所有XWPFRun
List<XWPFRun> runs = paragraph.getRuns();
创建文本
// 段落末尾创建XWPFRun
XWPFRun run = paragraph.createRun();
run.setText("为这个段落追加文本");
插入文本
// 段落起始插入XWPFRun
XWPFRun insertNewRun = paragraph.insertNewRun(0);
insertNewRun.setText("在段落起始位置插入这段文本");
修改 XWPF 运行文本
List<XWPFRun> runs = paragraph.getRuns();
// setText默认为追加文本,参数0表示设置第0个位置的文本,覆盖上一次设置
runs.get(0).setText("追加文本", 0);
runs.get(0).setText("修改文本", 0);
样式:颜色、字体
// 颜色
run.setColor("00ff00");
// 斜体
run.setItalic(true);
// 粗体
run.setBold(true);
// 字体
run.setFontFamily("Courier");
// 下划线
run.setUnderline(UnderlinePatterns.DOT_DOT_DASH);
文本换行
run.addCarriageReturn();
段落图片 XWPFPicture
抽取图片
List<XWPFPictureData> allPictures = doc.getAllPictures();
XWPFPicture pciture = allPictures.get(0);
byte[] data = pciture.getPictureData().getData();
创建图片
InputStream stream = new FileInputStream("./sayi.png");
XWPFRun run = paragraph.createRun();
run.addPicture(stream, XWPFDocument.PICTURE_TYPE_PNG, "Generated", Units.toEMU(256), Units.toEMU(256));
表格XWPFTTable
表格是构成 Word 文档的另一个重要基本元素。
创建新表格
创建一个三行三列的表格:
XWPFTable table = doc.createTable(3, 3);
设置单元格文本
表格是由表格行XWPF行组成,单元每行是由格XWPFCell组成,每个格内部又是许多XWPFaragraph段落组成。
table.getRow(1).getCell(1).setText("EXAMPLE OF TABLE");
等价于
XWPFParagraph p1 = table.getRow(0).getCell(0).addParagraph();
XWPFRun r1 = p1.createRun();
r1.setText("EXAMPLE OF TABLE");
单位格图片
图片操作其实就是一段一段,然后一段操作中的图片。
XWPFParagraph p1 = table.getRow(0).getCell(0).addParagraph();
XWPFRun r1 = p1.createRun()
设置格样式:背景色、单元方式
// 背景色
cell.setColor(cellStyle.getBackgroundColor());
// 获取单元格段落后设置对齐方式
XWPFParagraph addParagraph = cell.addParagraph();
addParagraph.setAlignment(ParagraphAlignment.CENTER);