Java(119):ExcelUtil工具类(org.apache.poi读取和写入Excel)

ExcelUtil工具类(XSSFWorkbook读取和写入Excel)，入参和出参都是：List<Map<String,Object>>

一、读取Excel

testdata.xlsx

1、new XSSFWorkbook对象

File file = new File(filePath);
FileInputStream fis = new FileInputStream(file);
XSSFWorkbook wb = new XSSFWorkbook(fis);

2、new XSSFSheet对象

//获取sheet第一个
XSSFSheet xSheet = wb.getSheetAt(0);
if(sheetName!=null) {
//如果sheetName入参可用这个,通过名称获取
xSheet = wb.getSheet(sheetName);
}

3、遍历行读取数据写入List<Map<String,Object>>

for (int i = 0; i <= xSheet.getLastRowNum(); i++) { //遍历所有行
if(i==0){ //标题行
for (int j = 0; j < xSheet.getRow(i).getPhysicalNumberOfCells(); j++) {
headMap.put(j, xSheet.getRow(i).getCell(j).toString());
}
continue;
}
Map<String, Object> paramsMap = new HashMap<>();
for (int j = 0; j < xSheet.getRow(i).getPhysicalNumberOfCells(); j++) { //遍历当前行所有列
String key=headMap.get(j);
Cell cell=xSheet.getRow(i).getCell(j);
switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
paramsMap.put(key,cell.getStringCellValue());
break;
case Cell.CELL_TYPE_NUMERIC:
paramsMap.put(key,new Double(cell.getNumericCellValue()).intValue());
break;
}
//paramsMap.put(key,xSheet.getRow(i).getCell(j));
}
resultList.add(paramsMap);
}

二、写入Excel

1、XSSFWorkbook 创建一个工作蒲

XSSFWorkbook xwb = new XSSFWorkbook();
XSSFSheet sheet = xwb.createSheet(sheetName);

2、XSSFSheet写入标题行

XSSFRow row = sheet.createRow(0);
for (int i=0;i<listHead.size();i++){
//根据需要给第一行每一列设置标题
XSSFCell cell = row.createCell(i);
cell.setCellValue(listHead.get(i));
}

3、数据赋值并设置列

//创建行
XSSFRow rows;
//创建列，即单元格Cell
XSSFCell cells;

//把List里面的数据写到excel中(除标题行外)
for (int i=0;i<listA.size();i++) {
//从第一行开始写入
rows = sheet.createRow(i + 1);
//创建每个单元格Cell，即列的数据
Map<String, Object> testMap =listA.get(i);
for (int j=0;j<listHead.size();j++){
cells = rows.createCell(j);
String head=listHead.get(j);
String type=testMap.get(head).getClass().getTypeName();
switch (type) {
case "java.lang.Integer":
cells.setCellValue((int) testMap.get(head));
break;
case "java.lang.String":
cells.setCellValue((String) testMap.get(head));
break;
}
}
}

4、写入excel

File file = new File(filePath);
FileOutputStream outputStream = new FileOutputStream(file);
xwb.write(outputStream);
outputStream.flush();
outputStream.close();

result.xlsx

三、整体工具类(参考)

java">package com.example.utils;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * Description :
 *
 * @author : HMF
 * Date : Created in 10:40 2023/11/23
 * @version :
 */
public class ExcelUtil {

    /**
     * 读取excel,放入List<Map<String, String>>
     * @param filePath 读取excel的文件路径+文件名称
     * @return datalist
     */
    public static List<Map<String,Object>> readExcel(String filePath){
        return readExcel(filePath,null);
    }

    /**
     * 读取excel,放入List<Map<String, String>>
     * @param filePath 读取excel的文件路径+文件名称
     * @Param sheetName sheet名称
     * @return datalist
     */
    public static List<Map<String,Object>> readExcel(String filePath,String sheetName){
        List<Map<String,Object>> resultList = new ArrayList<>();
        Map<Integer, String> headMap = new HashMap<>();
        try {
            File file = new File(filePath);
            FileInputStream fis = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            //获取sheet第一个
            XSSFSheet xSheet = wb.getSheetAt(0);
            if(sheetName!=null) {
                //如果sheetName入参可用这个,通过名称获取
                xSheet = wb.getSheet(sheetName);
            }
            for (int i = 0; i <= xSheet.getLastRowNum(); i++) {  //遍历所有行
                if(i==0){  //标题行
                    for (int j = 0; j < xSheet.getRow(i).getPhysicalNumberOfCells(); j++) {
                        headMap.put(j, xSheet.getRow(i).getCell(j).toString());
                    }
                    continue;
                }
                Map<String, Object> paramsMap = new HashMap<>();
                for (int j = 0; j < xSheet.getRow(i).getPhysicalNumberOfCells(); j++) {  //遍历当前行所有列
                    String key=headMap.get(j);
                    Cell cell=xSheet.getRow(i).getCell(j);
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_STRING:
                            paramsMap.put(key,cell.getStringCellValue());
                            break;
                        case Cell.CELL_TYPE_NUMERIC:
                            paramsMap.put(key,new Double(cell.getNumericCellValue()).intValue());
                            break;
                    }
                    //paramsMap.put(key,xSheet.getRow(i).getCell(j));
                }
                resultList.add(paramsMap);
            }

        }catch (IOException e){
            System.out.println(e);
        }
        return resultList;

    }

    /**
     * 写入excel
     * @param filePath 读取excel的文件路径+文件名称
     * @Param sheetName sheet名称
     * @param listA  入参列表
     * @param listHead 标题头列表
     */
    public static void writeExcel(String filePath, String sheetName, List<Map<String, Object>> listA,List<String> listHead){

        XSSFWorkbook xwb = new XSSFWorkbook();
        XSSFSheet  sheet = xwb.createSheet(sheetName);

        //标题行
        XSSFRow row = sheet.createRow(0);
        for (int i=0;i<listHead.size();i++){
            //根据需要给第一行每一列设置标题
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(listHead.get(i));
        }

        //创建行
        XSSFRow rows;
        //创建列，即单元格Cell
        XSSFCell cells;

        //把List里面的数据写到excel中(除标题行外)
        for (int i=0;i<listA.size();i++) {
            //从第一行开始写入
            rows = sheet.createRow(i + 1);
            //创建每个单元格Cell，即列的数据
            Map<String, Object> testMap =listA.get(i);
            for (int j=0;j<listHead.size();j++){
                cells = rows.createCell(j);
                String head=listHead.get(j);
                String type=testMap.get(head).getClass().getTypeName();
                switch (type) {
                    case "java.lang.Integer":
                        cells.setCellValue((int) testMap.get(head));
                        break;
                    case "java.lang.String":
                        cells.setCellValue((String) testMap.get(head));
                        break;
                }
            }
        }

        //用输出流写到excel
        try {
            File file = new File(filePath);
            FileOutputStream outputStream = new FileOutputStream(file);
            xwb.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }catch (IOException e) {
            e.printStackTrace();
        }

    }



    public static void main(String args[]){
        String filePath="src/main/resources/testdata.xlsx";
        List<Map<String, Object>> resultList= readExcel(filePath,"Sheet1");
        for(Map<String, Object> testMap:resultList){
            System.out.println(testMap);
//            for(String excKey :testMap.keySet()){
//                System.out.println(excKey+": "+testMap.get(excKey));
//            }
        }


        List<String> listHead =new ArrayList<>();
        listHead.add("id");
        listHead.add("name");
        listHead.add("age");
        listHead.add("grade");

        writeExcel("src/main/resources/result.xlsx","result",resultList,listHead);


    }


}