【Java SE】POI操作文件
POI Jar依赖
Apache POI Common
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
Apache POI API Based On OPC and OOXML Schemas
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
Apache POI
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml-schemas -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-scratchpad -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-scratchpad</artifactId>
<version>4.1.2</version>
</dependency>
各个Jar的作用
| Component | Application type | Maven artifactId | Notes |
|---|---|---|---|
| POIFS | OLE2 Filesystem | poi | Required to work with OLE2 / POIFS based files |
| HPSF | OLE2 Property Sets | poi | |
| HSSF | Excel XLS | poi | For HSSF only, if common SS is needed see below |
| HSLF | PowerPoint PPT | poi-scratchpad | |
| HWPF | Word DOC | poi-scratchpad | |
| HDGF | Visio VSD | poi-scratchpad | |
| HPBF | Publisher PUB | poi-scratchpad | |
| HSMF | Outlook MSG | poi-scratchpad | |
| OpenXML4J | OOXML | poi-ooxml plus one of poi-ooxml-schemas, ooxml-schemas |
Only one schemas jar is needed, see below for differences |
| XSSF | Excel XLSX | poi-ooxml | |
| XSLF | PowerPoint PPTX | poi-ooxml | |
| XWPF | Word DOCX | poi-ooxml | |
| Common SS | Excel XLS and XLSX | poi-ooxml | WorkbookFactory and friends all require poi-ooxml, not just core poi |
说明：
HSSFWorkbook类来处理 xls，XSSFWorkbook类来处理 xlsx，它们都继承接口 Workbook
Sheet名字最多31个字符，* / \不能用
常用代码
// 字体
HSSFFont font = wb.createFont();
font.setFontName("宋体"); // 设置字体名称
font.setFontHeightInPoints((short) 14); // 设置字体大小
font.setColor(IndexedColors.BLUE.getIndex()); // 设置字体颜色
font.setBold(true); // 设置加粗
// 样式
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.LEFT);
style.setVerticalAlignment(VerticalAlignment.CENTER);
// 设置边框
style.setBorderTop(BorderStyle.THIN);
style.setBorderBottom(BorderStyle.THIN);
style.setBorderLeft(BorderStyle.THIN);
style.setBorderRight(BorderStyle.THIN);
// 样式设置字体
style.setFont(font);
// 单元格设置样式
cell.setCellStyle(style);
示例代码：
/**
* 导出数据
*
* @return
*/
@GetMapping("/exportData")
@ApiOperation(value = "导出数据", notes = "导出数据")
public void exportData(HttpServletRequest request, HttpServletResponse response) throws Exception {
String filename = new String("导出数据".getBytes("gb2312"), "ISO8859-1") + DatePattern.PURE_DATETIME_MS_FORMAT.format(new Date()) + ".xls";
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
response.setContentType(request.getServletContext().getMimeType(filename));
OutputStream output = response.getOutputStream();
errorSensitiveScanResultService.exportData(result, output);
}
/**
* 导出数据
*
* @param result
* @param output
*/
@Override
public void exportData(OutputStream output) {
try {
// 测试数据
List<String> typeList = new ArrayList<>();
typeList.add("语文");
typeList.add("数学");
List<String> yuwen = new ArrayList<>();
yuwen.add("姓名,成绩");
yuwen.add("张三,82");
yuwen.add("李四,95");
List<String> shuxue = new ArrayList<>();
shuxue.add("姓名,成绩");
shuxue.add("张三,99");
shuxue.add("李四,81");
Map<String, List<String>> score = new HashMap<>();
score.put("语文", yuwen);
score.put("数学", shuxue);
// 创建xls格式工作簿
HSSFWorkbook wb = new HSSFWorkbook(); // XSSFWorkbook 创建xlsx格式工作簿
try {
for (String name : typeList) {
// 创建工作表
HSSFSheet sheet = wb.createSheet(name);
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.LEFT);// 水平样式
style.setVerticalAlignment(VerticalAlignment.CENTER);// 垂直样式
List<String> rowList = score.get(name);
for (int rowIndex = 0; rowIndex < rowList.size(); rowIndex++) {
// 创建行
HSSFRow row = sheet.createRow(rowIndex);
List<String> colList = Arrays.asList(rowList.get(rowIndex).split(","));
for (int colIndex = 0; colIndex < colList.size(); colIndex++) {
// 创建单元格
HSSFCell cell = row.createCell(colIndex);
// 设置单元格值
cell.setCellValue(colList.get(colIndex));
cell.setCellStyle(style);
}
}
}
} finally {
wb.write(output);
}
} catch (Exception e) {
e.printStackTrace();
}
}
hutool 处理 Excel
/**
* 数据导出
*
* @param outputStream
*/
public void exportData(OutputStream outputStream) {
List<Department> list = list();
if (CollectionUtil.isEmpty(list)) {
return;
}
List<String> nameList = new ArrayList<>();
nameList.add("部门名称");
nameList.add("部门简称");
nameList.add("信息填报名称");
nameList.add("部门编码");
nameList.add("区划编码");
ExcelWriter excelWriter = new ExcelWriter();
excelWriter.writeRow(nameList);
setCellStyle(excelWriter, 0, 0);
setCellStyle(excelWriter, 1, 0);
setCellStyle(excelWriter, 2, 0);
setCellStyle(excelWriter, 3, 0);
setCellStyle(excelWriter, 4, 0);
for (Department department : list) {
List<Object> valueList = new ArrayList<>();
valueList.add(department.getName());
valueList.add(department.getShortName());
valueList.add(department.getFillInName());
valueList.add(department.getCode());
valueList.add(department.getRegionCode());
excelWriter.writeRow(valueList);
}
for (int i = 0; i < 5; i++) {
excelWriter.setColumnWidth(i, 20 * 2);
}
excelWriter.flush(outputStream);
}
private void setCellStyle(ExcelWriter writer, int x, int y) {
CellStyle cellStyle = writer.createCellStyle(x, y);
// 顶边栏
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setTopBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 右边栏
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setRightBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 底边栏
cellStyle.setBorderBottom(BorderStyle.THIN);
cellStyle.setBottomBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 左边栏
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setLeftBorderColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
// 填充前景色
cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.GREY_25_PERCENT.getIndex());
cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
}
