org.apache.poi 读取excel通用、写入excel通用

掌控日月

已于 2023-01-12 17:16:24 修改

阅读量3.4k

点赞数 2

文章标签： java

于 2022-09-02 15:29:20 首次发布

本文链接：https://blog.csdn.net/z125499702/article/details/126663811

版权

引用：

 implementation ('org.apache.poi:poi-ooxml:5.2.2') {
        exclude group:'org.apache.poi', module:'poi-ooxml-lite'
 }
 implementation 'org.apache.poi:poi-ooxml-full:5.2.2'

实现类：

读取excel: 可以将一个excel文件的所有sheet页中的所有行列读取出来；



import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

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

/**
 * excel读取类
 *
 * @Author: 
 * @Date: 2022/8/29 14:57
 * @Copyright (C): 
 */
@Slf4j
public class ReadExcel {

    /**
     * xls后缀的表格文件
     */
    private static final String EXCEL_XLS = ".xls";
    /**
     * xlsx后缀的表格文件
     */
    private static final String EXCEL_XLSX = ".xlsx";


    public Map<Integer, List<Map<Integer, String>>> readExcel(File srcFile) throws IOException {
        log.info("readExcel srcFile={}", srcFile);
        String fileName = srcFile.getName();
        String extString = fileName.substring(fileName.lastIndexOf("."));
        if (EXCEL_XLS.equalsIgnoreCase(extString)) {
            try (
                    Workbook workbook = new HSSFWorkbook(new FileInputStream(srcFile));
            ) {
                return readExcel(workbook, true);
            }
        } else if (EXCEL_XLSX.equalsIgnoreCase(extString)) {
            try (
                    Workbook workbook = new XSSFWorkbook(new FileInputStream(srcFile));
            ) {
                return readExcel(workbook, false);
            }
        }
        return Collections.emptyMap();
    }


    private Map<Integer, List<Map<Integer, String>>> readExcel(Workbook workbook, boolean isXls) {
        Map<Integer, List<Map<Integer, String>>> result = new HashMap();
        DataFormatter formatter = new DataFormatter();
        int sheetSize = workbook.getNumberOfSheets();
        for (int sheetIndex = 0; sheetIndex < sheetSize; sheetIndex++) {
            List<Map<Integer, String>> rowList = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            int rowSize = sheet.getLastRowNum();
            for (int rowIndex = 0; rowIndex <= rowSize; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                Map<Integer, String> rowMap = new HashMap<>();
                int cellNumSize = row.getLastCellNum();
                for (int cellNum = 0; cellNum < cellNumSize; cellNum++) {
                    Cell cell = row.getCell(cellNum);
                    switch (cell.getCellType()){
                        case STRING:
                            rowMap.put(cellNum, cell.getStringCellValue());
                            break;
                        case NUMERIC:
                            rowMap.put(cellNum, formatter.formatCellValue(cell));
                            break;
                        default:
                            rowMap.put(cellNum, formatter.formatCellValue(cell));
                            break;
                    }
                }
                rowList.add(rowMap);
            }
            result.put(sheetIndex, rowList);
        }
        return result;
    }


}

使用方法：

在使用中根据实际场景，读取市可以忽略表头行；



import org.junit.Test;

import java.io.File;
import java.util.List;
import java.util.Map;

public class TestReadExcel {

    public static final String TEST_01 = "D:\\temp1.xlsx";

    private ReadExcel readExcel = new ReadExcel();


    @Test
    public void testReadExcel() throws Exception {
        Map<Integer, List<Map<Integer, String>>> map = readExcel.readExcel(new File(TEST_01));
            map.forEach((key,list) ->{
                list.forEach(s ->{
                    s.forEach((k,v) ->{
                        System.out.println(k+ " ---" + v);
                    });
                });
            });
        }
}

写入excel：

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.List;

/**
 * excel读取类
 *
 * @Author: jutou
 * @Date: 2022/8/29 14:57
 * @Copyright (C): JuTouTech
 */
@Slf4j
public class WriteExcel {

    public <T> void  buildExcelXlsx(List<ExportExcelBean> template, List<T> dataList, File destFile) throws NoSuchFieldException, IllegalAccessException, IOException {
        if (dataList.isEmpty() || template.isEmpty()) {
            return;
        }
        try (
                // 创建excel文件对象
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
                OutputStream outNewExcel = new FileOutputStream(destFile);
        ) {

            // 获取标题样式（表头、第一行）
            CellStyle titleCellStyle = createTitleCellStyle(xssfWorkbook);

            // 获取内容样式
            CellStyle contentCellStyle = createContentCellStyle(xssfWorkbook);

            // 获取模板长度：模板是选中的导出的属性长度
            int templateSize = template.size();

            // 获取数据的数量
            int dataSize = dataList.size();

            // 定义标题行高
            short titleLineHigh = 20;

            // 定义内容行高
            short contentLineHigh = 20;


            T t = dataList.get(0);

            // 获取泛型 T 的类型
            Class<?> aClass = t.getClass();

            // 创建sheet表
            XSSFSheet sheet = xssfWorkbook.createSheet();

            // 创建标题行
            XSSFRow row1 = sheet.createRow(0);

            // 设置标题行高
            row1.setHeightInPoints(titleLineHigh);


            // 设置标题
            for (int i = 0; i < templateSize; i++) {

                // 设置列宽度
                sheet.setColumnWidth(i, template.get(i).getColumnWide());

                // 创建标题列
                XSSFCell cell = row1.createCell(i);

                // 设置标题列样式
                cell.setCellStyle(titleCellStyle);

                // 标题列赋值
                cell.setCellValue(template.get(i).getPropertyName());

            }

            // 设置内容
            for (int i = 0; i < dataSize; i++) {

                // 创建行
                XSSFRow row = sheet.createRow(i + 1);

                // 设置行高
                row.setHeightInPoints(contentLineHigh);

                for (int j = 0; j < templateSize; j++) {

                    // 创建列
                    XSSFCell cell = row.createCell(j);

                    // 设置列样式
                    cell.setCellStyle(contentCellStyle);

                    // 获取属性名
                    String property = template.get(j).getProperty();

                    // 通过属性名获取目标属性
                    Field targetField = aClass.getDeclaredField(property);

                    // 获取目标对象（目标属性是目标对象中的一个属性）
                    T targetObject = dataList.get(i);

                    // 给列赋值
                    setExcelValue(cell, targetField, targetObject);

                }
            }
            xssfWorkbook.write(outNewExcel);
        }
    }


    // 创建标题样式
    private CellStyle createTitleCellStyle(XSSFWorkbook xssfWorkbook) {

        // 获取标题字体对象
        XSSFFont titleFont = xssfWorkbook.createFont();

        titleFont.setFontName("宋体");

        // 设置标题字体加粗
        titleFont.setBold(true);

        // 设置标题字体颜色 (黑色)
        titleFont.setColor((short) 0);

        // 设置标题字体大小
        titleFont.setFontHeightInPoints((short) 12);

        // 创建标题样式
        CellStyle titleCellStyle = xssfWorkbook.createCellStyle();

        // 单元格样式水平居中
        titleCellStyle.setAlignment(HorizontalAlignment.LEFT);

        // 单元格样式垂直居中
        titleCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置上右左下边框(厚边框)
        titleCellStyle.setBorderTop(BorderStyle.THIN);
        titleCellStyle.setBorderRight(BorderStyle.THIN);
        titleCellStyle.setBorderBottom(BorderStyle.THIN);
        titleCellStyle.setBorderLeft(BorderStyle.THIN);

        // 将字体赋值给样式
        titleCellStyle.setFont(titleFont);

        return titleCellStyle;
    }


    // 创建内容样式
    private CellStyle createContentCellStyle(XSSFWorkbook xssfWorkbook) {

        // 获取标题字体对象
        XSSFFont contentFont = xssfWorkbook.createFont();

        contentFont.setFontName("宋体");

        // 设置标题字体加粗
        contentFont.setBold(false);

        // 设置标题字体颜色 (黑色)
        contentFont.setColor((short) 0);

        // 设置标题字体大小
        contentFont.setFontHeightInPoints((short) 12);

        // 创建标题样式
        CellStyle contentCellStyle = xssfWorkbook.createCellStyle();

        // 单元格样式水平居中
        contentCellStyle.setAlignment(HorizontalAlignment.LEFT);

        // 单元格样式垂直居中
        contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        // 设置上右左下边框(厚边框)
        contentCellStyle.setBorderTop(BorderStyle.THIN);
        contentCellStyle.setBorderRight(BorderStyle.THIN);
        contentCellStyle.setBorderBottom(BorderStyle.THIN);
        contentCellStyle.setBorderLeft(BorderStyle.THIN);

        // 将字体赋值给样式
        contentCellStyle.setFont(contentFont);

        return contentCellStyle;
    }


    // 单元格赋值
    private <T> void setExcelValue(XSSFCell cell, Field targetField, T targetObject) throws IllegalAccessException {

        // 获取属性的类型
        Class<?> declaringClass = targetField.getType();

        // 跳过私有属性访问检查
        targetField.setAccessible(true);

        // 获取属性值
        Object value = targetField.get(targetObject);

        // 通过属性类型给excel的单元格赋值的类型；如果value为空，赋值会报错，所以为空要设置一个默认值
        if (declaringClass == Short.class) {
            if (value == null) {
                cell.setCellValue(0);
            } else {
                cell.setCellValue((Short) value);
            }
        } else if (declaringClass == Integer.class) {
            if (value == null) {
                cell.setCellValue(0);
            } else {
                cell.setCellValue((Integer) value);
            }
        } else if (declaringClass == Long.class) {
            if (value == null) {
                cell.setCellValue(0);
            } else {
                cell.setCellValue((Long) value);
            }
        } else if (declaringClass == Float.class) {
            if (value == null) {
                cell.setCellValue(0.0);
            } else {
                cell.setCellValue((Float) value);
            }
        } else if (declaringClass == Double.class) {
            if (value == null) {
                cell.setCellValue(0.0);
            } else {
                cell.setCellValue((Double) value);
            }
        } else if (declaringClass == BigDecimal.class) {
            if (value == null) {
                cell.setCellValue(0.0);
            } else {
                cell.setCellValue(((BigDecimal) value).doubleValue());
            }
        } else if (declaringClass == Date.class) {
            if (value == null) {
                cell.setCellValue("");
            } else {
                cell.setCellValue(format(((Date) value), DATE_FORMAT_FULL));
            }
        } else if (declaringClass == String.class) {
            if (value == null) {
                cell.setCellValue("");
            } else {
                cell.setCellValue((String) value);
            }
        } else {
            if (value == null) {
                cell.setCellValue("");
            } else {
                cell.setCellValue(value.toString());
            }
        }
    }


    public static final DateTimeFormatter DATE_FORMAT_FULL = DateTimeFormatter.ofPattern("yyyy/MM/dd HH:mm:ss");


    private String format(Date date ,DateTimeFormatter DATE_FORMAT_FULL) {
        return date == null ? null :  DATE_FORMAT_FULL.format(date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime());
    }
}

@Data
public class ExportExcelBean implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 属性
     * */
    private String property;
 
    /**
     * propertyName
     * */
    private String propertyName;
 
    /**
     * 是否选中
     * */
    private boolean select;
 
    /**
     * 导出列宽
     * */
    private Integer columnWide;


    public ExportExcelBean(String property, String propertyName, Integer columnWide) {
        this.property = property;
        this.propertyName = propertyName;
        this.columnWide = columnWide;
    }
}

写入使用方法：

private WriteExcel writeExcel = new WriteExcel();
 
public static final String TEST_02 = "D:\\export1.xlsx";

@Test
    public void testWriteExcel() throws Exception {
        List<ExportExcelBean> template = new ArrayList<>();
        template.add(new ExportExcelBean("city","城市", 3000));
        template.add(new ExportExcelBean("country","国家", 3000));
        List<TestExcelBean> aaa = new ArrayList<>();
        aaa.add(new TestExcelBean("北京","中国"));
        aaa.add(new TestExcelBean("上海","中国"));
        aaa.add(new TestExcelBean("纽约","美国"));
        writeExcel.buildExcelXlsx(template, aaa, new File(TEST_02));
    }