java:excel文件读写二、poi写xls和xlsx
1.前言
apache poi官网:https://poi.apache.org/
在讲解组件api时,提到了EXCEL(HSSF,XSSF)中,如何同时兼容写入xls和xlsx文件的使用,地址如下:
https://poi.apache.org/components/spreadsheet/converting.html
官方指明更新到poi的3.5版本,可以用一种兼容的方式操作xls和xlsx,依赖配置如下:
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.5.4</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<!-- xls -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.2.1</version>
</dependency>
<!-- xlsx -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.2.1</version>
</dependency>
</dependencies>
2.使用
package com.xiaoxu.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.lang.NonNull;
import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.util.*;
import java.util.stream.Collectors;
/**
* @author xiaoxu
* @date 2022-03-13 13:26
* spring_boot:com.xiaoxu.utils.ExcelUtil
*/
public class ExcelUtil {
private static final String xlsSeparator = ".xls";
private static final String xlsxSeparator = ".xlsx";
private static final String separator = "\\";
public static void createExcel(@NonNull String fileName){
/*
*fileName 文件名
* */
File deskUrl = FileSystemView.getFileSystemView().getHomeDirectory();
List<String> splitArr = ExcelUtil.splitFileName(fileName);
if(splitArr.size()==1){
fileName = deskUrl+ separator + splitArr.get(0) +xlsSeparator;
}else if(splitArr.size()==2){
if(!(xlsxSeparator.equals(splitArr.get(1))||xlsSeparator.equals(splitArr.get(1)))){
throw new RuntimeException("文件名后缀必须为.xls或者.xlsx");
}
StringBuilder sb = new StringBuilder();
sb.append(deskUrl).append(separator);
for (String s : splitArr) {
sb.append(s);
}
fileName = sb.toString();
}else{
throw new RuntimeException("文件名不正确");
}
//例以前为:HSSFWorkbook和XSSFWorkbook
Workbook wb;
//创建xls(HSSF)和xlsx(XSSF)文件
if(fileName.endsWith(".xls")){
wb=new HSSFWorkbook();
}else if(fileName.endsWith(".xlsx")){
wb=new XSSFWorkbook();
}else{
throw new RuntimeException("文件后缀必须是xls或xlsx");
}
CreationHelper creationHelper = wb.getCreationHelper();
//例以前为:HSSFSheet和XSSFSheet
Sheet s = wb.createSheet("我是sheet1");
//例以前为:HSSFRow和XSSFRow
Row r;
//例以前为:HSSFCell和XSSFCell
Cell cell1;
Cell cell2;
//样式创建,以前为:HSSFCellStyle和XSSFCellStyle,以此类推
CellStyle c1 = wb.createCellStyle();
CellStyle c2 = wb.createCellStyle();
DataFormat df = wb.createDataFormat();
Font f1 =wb.createFont();
Font f2 =wb.createFont();
f1.setFontHeightInPoints((short) 14);
f1.setColor(IndexedColors.SEA_GREEN.getIndex());
f1.setBold(true);
f2.setFontHeightInPoints((short) 10);
f2.setColor(IndexedColors.DARK_RED.getIndex());
f2.setBold(false);
c1.setFont(f1);
c1.setDataFormat(df.getFormat("#,##0.0"));
c2.setFont(f2);
c2.setDataFormat(df.getFormat("text"));
c2.setBorderBottom(BorderStyle.DASH_DOT);
for (int i=0;i<3;i++){
r = s.createRow(i);
for(int j=0;j<10;j+=2){
cell1 = r.createCell(j);
cell2 = r.createCell(j+1);
cell1.setCellStyle(c1);
cell2.setCellStyle(c2);
cell1.setCellValue(i+((double)j/10));
cell2.setCellValue(
creationHelper.createRichTextString("你好,"+j)
);
}
}
if(wb instanceof HSSFWorkbook){
System.out.println("我是xls文件~");
}else{
System.out.println("我是xlsx文件~");
}
FileOutputStream fileOutputStream;
try {
fileOutputStream = new FileOutputStream(fileName);
wb.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
wb.close();
} catch (FileNotFoundException e) {
throw new RuntimeException("文件路径未找到或进程无法访问:"+e.getMessage());
}catch (IOException i){
throw new RuntimeException("文件写入出错:"+i.getMessage());
}
}
public static List<String> splitFileName(String fileName){
String suffix = null;
String file;
List<String> files = new ArrayList<>();
int i;
if(fileName!=null){
i = fileName.lastIndexOf(".");
if(i!=-1){
file = fileName.substring(0,i);
suffix = fileName.substring(i);
}else{
file = fileName;
}
}else{
throw new NullPointerException("文件名不能为null");
}
files.add(file);
files.add(suffix);
return files.stream().filter(Objects::nonNull).collect(Collectors.toList());
}
public static void main(String[] args) {
ExcelUtil.createExcel("3.xlsx");
}
}
执行结果如下:
我是xlsx文件~
13:59:13.640 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save core properties part
13:59:13.643 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save content types part
13:59:13.748 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save package relationships
13:59:13.754 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/app.xml'
13:59:13.758 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/core.xml'
13:59:13.762 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/sharedStrings.xml'
13:59:13.762 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/styles.xml'
13:59:13.762 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/workbook.xml'
13:59:13.764 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/worksheets/sheet1.xml'
13:59:13.774 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save content types part
13:59:13.785 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save package relationships
13:59:13.788 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/app.xml'
13:59:13.790 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/core.xml'
13:59:13.793 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/sharedStrings.xml'
13:59:13.793 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/styles.xml'
13:59:13.794 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/workbook.xml'
13:59:13.795 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/worksheets/sheet1.xml'
桌面找到文件,打开xlsx文件展示如下:
同理创建.xls文件:
public static void main(String[] args) {
// ExcelUtil.createExcel("3.xlsx");
ExcelUtil.createExcel("2.xls");
}
执行结果如下:
12:01:08.345 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - creating new workbook from scratch
12:01:08.400 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getNumSheets=1
12:01:08.409 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - exit create new workbook from scratch
12:01:08.524 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - Sheet createsheet from scratch called
12:01:08.559 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - Sheet createsheet from scratch exit
12:01:08.560 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getXF=21
12:01:08.562 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getXF=22
12:01:08.565 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getXF=22
12:01:08.565 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - getXF=23
12:01:08.579 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - addRow
12:01:08.579 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - exit addRow
12:01:08.584 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.584 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.585 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,0'
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.587 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,2'
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,4'
12:01:08.588 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,6'
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row0
12:01:08.589 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,8'
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - addRow
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - exit addRow
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,0'
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,2'
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.590 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,4'
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,6'
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row1
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,8'
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - addRow
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - exit addRow
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,0'
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.591 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,2'
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,4'
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.592 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,6'
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - add value record row2
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalSheet - replaceValueRecord
12:01:08.593 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - insert to sst string='你好,8'
我是xls文件~
12:01:08.624 [main] DEBUG org.apache.poi.hssf.usermodel.HSSFWorkbook - HSSFWorkbook.getBytes()
12:01:08.624 [main] WARN org.apache.poi.POIDocument - DocumentSummaryInformation property set came back as null
12:01:08.624 [main] WARN org.apache.poi.POIDocument - SummaryInformation property set came back as null
12:01:08.630 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - setting bof for sheetnum =0 at pos=1545
12:01:08.633 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - Serializing Workbook with offsets
12:01:08.634 [main] DEBUG org.apache.poi.hssf.model.InternalWorkbook - Exiting serialize workbook
打开文件,效果一致:
3 另外的方式
private static final String xlsSeparator = ".xls";
private static final String xlsxSeparator = ".xlsx";
private static final String separator = "\\";
/**
* @param fileName 写入excel的文件名称
* @param headers excel的表头
* @param data excel的表格内容 :map的key就是表头的值,然后value就是填入的值
*/
public static void writeToExcel(@NonNull String fileName,List<String> headers,List<Map<String,String>> data){
fileName = getExcelFileName(fileName,null);
if(!(fileName.endsWith(xlsSeparator)||fileName.endsWith(xlsxSeparator))){
throw new RuntimeException("文件名后缀必须为.xls或者.xlsx");
}
if(CollectionUtils.isEmpty(headers)){
throw new IllegalArgumentException("excel的headers不能为空");
}
if(data==null){
throw new NullPointerException("excel的data不能为null");
}
Workbook wb=null;
if(fileName.endsWith(xlsSeparator)){
wb= new HSSFWorkbook();
}else if(fileName.endsWith(xlsxSeparator)){
wb = new XSSFWorkbook();
}
String sheetName = WorkbookUtil.createSafeSheetName("我是第一个sheet");
Sheet sheet = wb.createSheet(sheetName);
/*
* 字体样式1
* */
Font font1 = wb.createFont();
font1.setBold(true);
font1.setColor(IndexedColors.BLUE.getIndex());
//CellStyle1
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setFont(font1);
Row row = sheet.createRow(0);
headers.forEach(h->{
row.createCell(headers.indexOf(h)).setCellValue(h);
});
int s = data.size();
int startRowNum=0,endRowNum=0;
if(s>=1){
startRowNum = 1;
endRowNum = s;
}
Set<Integer> indexs = new HashSet<>();
if(startRowNum!=0){
for (int i = 1; i <=endRowNum; i++) {
Row row1 = sheet.createRow(i);
Map<String, String> stringStringMap = data.get(i-1);
stringStringMap.forEach((key,value)->{
headers.forEach(h->{
if(key.equals(h)){
row1.createCell(headers.indexOf(key)).setCellValue(value);
indexs.add(headers.indexOf(key));
}
});
});
}
}
// 全部统一设置列宽
for (int i = 0; i < headers.size(); i++) {
sheet.setColumnWidth(i,256*headers.get(i).length()*2);
}
//针对每一列数据,有数据的则根据数据自适应宽度
//在setCellValue后,adjust column width to fit the content
for (Integer index : indexs) {
sheet.autoSizeColumn(index);
}
try(OutputStream outputStream = new FileOutputStream(fileName)){
wb.write(outputStream);
wb.close();
}catch (IOException i){
System.out.println(i.getMessage());
}
}