java：excel文件读写三、poi读、写xls和xlsx
1 依赖配置
<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 使用
读取excel数据，去除空的列数据，并写入到其他excel文件中：
package com.xiaoxu.utils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.lang.NonNull;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;
import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;
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 void checkExcelFileSuffix(@NonNull String fileName){
if(!(fileName.endsWith(xlsSeparator)||fileName.endsWith(xlsxSeparator))){
throw new IllegalArgumentException("文件后缀必须是xls或xlsx");
}
}
/**
* @param fileName
* @param sheetIndex
* @param newFileName
*/
// 快捷方法参数：点击方法名,alt+enter：add javadoc
@SuppressWarnings("rawtypes")
public static void readAndWriteToExcel(@NonNull String fileName,int sheetIndex,@NonNull String newFileName){
checkExcelFileSuffix(newFileName);
Map<String,List> dataMap = readExcel(fileName,sheetIndex);
System.out.println("parse map's data:"+dataMap);
String excelFileName = getExcelFileName(newFileName);
Workbook wb;
//创建xls（HSSF）和xlsx（XSSF）文件
if(excelFileName.endsWith(".xls")){
wb=new HSSFWorkbook();
}else if(excelFileName.endsWith(".xlsx")){
wb=new XSSFWorkbook();
}else{
throw new RuntimeException("文件后缀必须是xls或xlsx");
}
CreationHelper creationHelper = wb.getCreationHelper();
Sheet s = wb.createSheet("第一个sheet");
List<Integer> len = new ArrayList<>();
if(!CollectionUtils.isEmpty(dataMap)){
dataMap.forEach((key,value) ->{
len.add(value.size());
});
int maxRow = Collections.max(len);
AtomicInteger ato = new AtomicInteger(0);
for (int i = 0 ;i < maxRow; i++) {
Row row = s.createRow(i);
dataMap.forEach((key,value)->{
for(int j=0;j<value.size();j++){
if(ato.get()==j){
//System.out.println("开始设置列英文："+key+"；列索引 :"+CellReference.convertColStringToIndex(key));
Cell cell = row.createCell(CellReference.convertColStringToIndex(key));
setCellValueByClass(cell,value.get(j),creationHelper);
}
}
});
ato.addAndGet(1);
}
}
FileOutputStream fileOutputStream;
try {
fileOutputStream = new FileOutputStream(excelFileName);
wb.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
wb.close();
} catch (IOException e) {
throw new RuntimeException("文件写入出错："+e.getMessage());
}
}
public static void setCellValueByClass(Cell cell,Object obj,CreationHelper creationHelper){
if(obj instanceof String){
cell.setCellValue(creationHelper.createRichTextString((String) obj));
}else if(obj instanceof Double){
cell.setCellValue((double) obj);
}else if(obj instanceof Boolean){
cell.setCellValue((boolean) obj);
}else if (obj instanceof Date){
cell.setCellValue((Date) obj);
}else {
throw new RuntimeException("暂时只支持String、double、boolean、Date类型excel数据转换");
}
}
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());
}
/**
* @param fileName 读取文件的名称
* @param sheetIndex sheet索引，0开始
* @return 返回的map的key是A、B、D...
*/
@SuppressWarnings(value = {"rawtypes"})
public static Map<String,List> readExcel(@NonNull String fileName,int sheetIndex){
String excelFileName = ExcelUtil.getExcelFileName(fileName);
Map<String,List> dataMap = new HashMap<>();
// Use an InputStream, needs more memory
// new File(excelFileName)
try(InputStream inputStream = new FileInputStream(excelFileName)){
Workbook sheets = WorkbookFactory.create(inputStream);
Sheet sheetAt;
try {
sheetAt = sheets.getSheetAt(sheetIndex);
} catch (IllegalArgumentException e) {
e.printStackTrace();
return new HashMap<>();
}
//DataFormatter dataFormatter = new DataFormatter();
if(sheetAt!=null){
for (Row cells : sheetAt) {
for (Cell cell : cells) {
CellReference cellReference = new CellReference(cells.getRowNum(), cell.getColumnIndex());
String colString = CellReference.convertNumToColString(cellReference.getCol());
//第一列代表A；第二列代表B；以次类推...
//System.out.println("行："+cellReference.getRow()+"; 列："+colString);
Object cellObj = getCellValue(cell);
//System.out.println("值："+cellObj);
save(dataMap,colString,cellObj);
}
}
}
} catch (IOException f){
f.printStackTrace();
}
return dataMap;
}
/*
* 针对单个key和value存入
* 且不使用map.get():因为可能存在value为null的情况
* */
@SuppressWarnings(value = {"rawtypes","unchecked"})
public static void save(Map<String,List> saveMap,String key,Object value){
/*把读取的map中的value为null或者""的字符串去掉*/
if(value instanceof String && !StringUtils.hasLength((String)value)){
return;
}
if(!saveMap.containsKey(key)){
List valList = new ArrayList();
valList.add(value);
saveMap.put(key,valList);
}else{
saveMap.get(key).add(value);
}
}
/*
* 存入map,不做去重操作
* */
@SuppressWarnings(value = {"rawtypes","unchecked"})
public static void storeInMap(Map<String,List> emptyMap,Map<String,List> dataMap){
for(Map.Entry<String,List> entry:dataMap.entrySet()){
String key = entry.getKey();
List value = entry.getValue();
if(!emptyMap.containsKey(key)){
emptyMap.put(key,value);
}else{
emptyMap.get(key).addAll(value);
}
}
}
/**
* @param c cell对象
* @return cell的content
*/
//获取cell的值
public static Object getCellValue(Cell c){
switch(c.getCellType()){
case NUMERIC:
if(DateUtil.isCellDateFormatted(c)){
return c.getDateCellValue();
}else{
return c.getNumericCellValue();
}
case STRING:
return c.getRichStringCellValue().getString();
/*公式*/
case FORMULA:
return c.getCellFormula();
case BOOLEAN:
return c.getBooleanCellValue();
/*如果为blank类型，数据会是null，处理为空字符串*/
case BLANK:
return "";
case _NONE:
case ERROR:
default:
break;
}
return null;
}
public static String getExcelFileName(@NonNull String 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("文件名不正确");
}
return fileName;
}
public static void main(String[] args) {
// ExcelUtil.createExcel("test.xlsx");
// ExcelUtil.readExcel("test123.xlsx", 0);
ExcelUtil.readAndWriteToExcel("test123.xlsx",0,"new.xlsx");
// ExcelUtil.readAndWriteToExcel("test456.xlsx",0,"new2.xlsx");
}
}
2.1 情形1
test123.xlsx:
执行结果如下：
13:59:13.773 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/_rels/workbook.xml.rels
13:59:13.800 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/worksheets/_rels/sheet1.xml.rels
13:59:13.833 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /_rels/.rels
13:59:14.427 [main] DEBUG org.apache.poi.ooxml.POIXMLFactory - using default POIXMLDocumentPart for http://schemas.openxmlformats.org/officeDocument/2006/relationships/printerSettings
parse map's data:{A=[3.1232321E7], B=[2.13213421421421E24], C=[你好, 345.0], D=[hai, 中国来啦], E=[true], F=[Tue Apr 05 00:00:00 CST 2022], G=[哈哈], H=[1.0]}
13:59:14.870 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save core properties part
13:59:14.871 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save content types part
13:59:14.896 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save package relationships
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/app.xml'
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/core.xml'
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/sharedStrings.xml'
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/styles.xml'
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/workbook.xml'
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/worksheets/sheet1.xml'
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save content types part
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save package relationships
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/app.xml'
13:59:14.913 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/core.xml'
13:59:14.930 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/sharedStrings.xml'
13:59:14.931 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/styles.xml'
13:59:14.931 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/workbook.xml'
13:59:14.932 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/worksheets/sheet1.xml'
new.xlsx：
2.2 情形2
test456.xlsx：
执行结果如下：
14:03:27.883 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /xl/_rels/workbook.xml.rels
14:03:27.930 [main] DEBUG org.apache.poi.openxml4j.opc.PackageRelationshipCollection - Parsing relationship: /_rels/.rels
parse map's data:{A=[3.1232321E7], C=[你好, 345.0], D=[hai, 中国来啦], E=[true], F=[Tue Apr 05 00:00:00 CST 2022], G=[哈哈], H=[1.0], J=[2.0]}
14:03:28.688 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save core properties part
14:03:28.688 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save content types part
14:03:28.750 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save package relationships
14:03:28.750 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/app.xml'
14:03:28.750 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/core.xml'
14:03:28.766 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/sharedStrings.xml'
14:03:28.766 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/styles.xml'
14:03:28.766 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/workbook.xml'
14:03:28.766 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/worksheets/sheet1.xml'
14:03:28.782 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save content types part
14:03:28.782 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save package relationships
14:03:28.797 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/app.xml'
14:03:28.797 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'docProps/core.xml'
14:03:28.797 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/sharedStrings.xml'
14:03:28.797 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/styles.xml'
14:03:28.797 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/workbook.xml'
14:03:28.797 [main] DEBUG org.apache.poi.openxml4j.opc.ZipPackage - Save part 'xl/worksheets/sheet1.xml'
new2.xlsx：
2.3 情形3
如果需要对数据进行格式化写入，如日期，修改如下：
public static void setCellValueByClass(Cell cell,Object obj,CreationHelper creationHelper,Workbook wb){
if(obj instanceof String){
cell.setCellValue(creationHelper.createRichTextString((String) obj));
}else if(obj instanceof Double){
cell.setCellValue((double) obj);
}else if(obj instanceof Boolean){
cell.setCellValue((boolean) obj);
}else if (obj instanceof Date){
CellStyle cellStyle = wb.createCellStyle();
cellStyle.setDataFormat(
creationHelper.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss")
);
cell.setCellValue((Date) obj);
cell.setCellStyle(cellStyle);
}else {
throw new RuntimeException("暂时只支持String、double、boolean、Date类型excel数据转换");
}
}
/**
* @param fileName
* @param sheetIndex
* @param newFileName
*/
// 快捷方法参数：点击方法名,alt+enter：add javadoc
@SuppressWarnings("rawtypes")
public static void readAndWriteToExcel(@NonNull String fileName,int sheetIndex,@NonNull String newFileName){
checkExcelFileSuffix(newFileName);
Map<String,List> dataMap = readExcel(fileName,sheetIndex);
System.out.println("parse map's data:"+dataMap);
String excelFileName = getExcelFileName(newFileName);
Workbook wb;
//创建xls（HSSF）和xlsx（XSSF）文件
if(excelFileName.endsWith(".xls")){
wb=new HSSFWorkbook();
}else if(excelFileName.endsWith(".xlsx")){
wb=new XSSFWorkbook();
}else{
throw new RuntimeException("文件后缀必须是xls或xlsx");
}
CreationHelper creationHelper = wb.getCreationHelper();
Sheet s = wb.createSheet("第一个sheet");
List<Integer> len = new ArrayList<>();
if(!CollectionUtils.isEmpty(dataMap)){
dataMap.forEach((key,value) ->{
len.add(value.size());
});
int maxRow = Collections.max(len);
AtomicInteger ato = new AtomicInteger(0);
for (int i = 0 ;i < maxRow; i++) {
Row row = s.createRow(i);
dataMap.forEach((key,value)->{
for(int j=0;j<value.size();j++){
if(ato.get()==j){
//System.out.println("开始设置列英文："+key+"；列索引 :"+CellReference.convertColStringToIndex(key));
Cell cell = row.createCell(CellReference.convertColStringToIndex(key));
setCellValueByClass(cell,value.get(j),creationHelper,wb);
}
}
});
ato.addAndGet(1);
}
}
FileOutputStream fileOutputStream;
try {
fileOutputStream = new FileOutputStream(excelFileName);
wb.write(fileOutputStream);
fileOutputStream.flush();
fileOutputStream.close();
wb.close();
} catch (IOException e) {
throw new RuntimeException("文件写入出错："+e.getMessage());
}
}
重新执行效果如下：
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
