POI基本api

1、引入依赖

 <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>

2、demo

package com.sirius.poi.test;


import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import java.io.FileOutputStream;
import java.io.OutputStream;

public class PoiDemo {

    public static void main(String[] args) throws Exception {
        //1、创建工作簿
        Workbook workbook = new HSSFWorkbook();
        //2、创建Sheet
        Sheet sheet = workbook.createSheet("sheet名称");
        //3、创建行Row
        Row row = sheet.createRow(0);
        //4、创建单元格Cell
        //可以指定单元格的类型，支持的类型有下面7种： _NONE(-1),NUMERIC(0),STRING(1), 公式 FORMULA(2),BLANK(3), 布尔BOOLEAN(4),ERROR(5);
        Cell cell = row.createCell(0, CellType.STRING);
        //5、填充数据
        cell.setCellValue("苹果");
        //指定Cell单元格使用该样式
        cell.setCellStyle(getCellStyle(workbook));
        //创建文件输出流
        OutputStream os = new FileOutputStream("F:\\1.xlsx");
        //将文件放入流
        workbook.write(os);
        //刷新流
        os.flush();
        os.close();
        workbook.close();
    }

    /**
     * 2.3 样式和字体
     * 如果我们需要导出的Excel美观一些，如设置字体的样式加粗、颜色、大小等等，就需要创建样式和字体。
     */
    public static CellStyle getCellStyle(Workbook workbook){
        //创建样式：
        CellStyle cellStyle = workbook.createCellStyle();
        //1）左右垂直居中
        //左右居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 设置垂直居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        //2）字体加粗、颜色
        Font font = workbook.createFont();
        //字体颜色为红色
        font.setColor(IndexedColors.RED.getIndex());
        //字体加粗
        font.setBold(true);
        //倾斜、下划线
        //设置倾斜
//        font.setItalic(boolean italic);
        //设置下划线
//        font.setUnderline(byte underline);

        cellStyle.setFont(font);

        /**
         * 设置单元格的数据类型
         */
        // 设置单元格样式 - 数字格式
//        CellStyle numberCellStyle = workbook.createCellStyle();
//        numberCellStyle.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));
//        //指定单元格
//        Row row = sheet.createRow(0);
//        Cell cell = row.createCell(0);
//        cell.setCellStyle(numberCellStyle);

        /**
         * 日期格式
         */
        // 设置单元格样式 - 日期格式
//        CellStyle dateCellStyle = workbook.createCellStyle();
//        dateCellStyle.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd"));
//        //指定单元格
//        Row row = sheet.createRow(0);
//        Cell cell = row.createCell(0);
//        cell.setCellStyle(dateCellStyle);
        return cellStyle;
    }
}

4、工具类

package com.sirius.poi.utils;

import com.alibaba.fastjson.JSONObject;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.lang.reflect.Field;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 604138
 * @date 2023/8/10
 */
public class ExportUtils {

    /**
     * 数据导出
     * @param fileName 导出excel名称
     * @param data 导出的数据
     * @param c 导出数据的实体class
     * @param response 响应
     * @throws Exception
     */
    public static void exportExcel(String fileName, String data, Class<?> c, HttpServletResponse response) throws Exception {
        try {
            // 创建表头
            // 创建工作薄
            Workbook workbook = new XSSFWorkbook();
            Sheet sheet = workbook.createSheet();
            // 创建表头行
            Row rowHeader = sheet.createRow(0);
            if (c == null) {
                throw new RuntimeException("Class对象不能为空!");
            }
            Field[] declaredFields = c.getDeclaredFields();
            List<String> headerList = new ArrayList<>();
            if (declaredFields.length == 0) {
                return;
            }
            for (int i = 0; i < declaredFields.length; i++) {
                Cell cell = rowHeader.createCell(i, CellType.STRING);
                String headerName = String.valueOf(declaredFields[i].getName());
                cell.setCellValue(headerName);
                headerList.add(i, headerName);
            }
            // 填充数据
            List<?> objects = JSONObject.parseArray(data, c);
            Object obj = c.newInstance();
            if (!CollectionUtils.isEmpty(objects)) {
                for (int o = 0; o < objects.size(); o++) {
                    Row rowData = sheet.createRow(o + 1);
                    for (int i = 0; i < headerList.size(); i++) {
                        Cell cell = rowData.createCell(i);
                        Field nameField = c.getDeclaredField(headerList.get(i));
                        nameField.setAccessible(true);
                        String value = String.valueOf(nameField.get(objects.get(o)));
                        cell.setCellValue(value);
                    }
                }
            }
            response.setContentType("application/vnd.ms-excel");
            String resultFileName = URLEncoder.encode(fileName, "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + resultFileName + ";" + "filename*=utf-8''" + resultFileName);
            workbook.write(response.getOutputStream());
            workbook.close();
            response.flushBuffer();
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 导入
     * 导入工具类仅限Java的八大基础数据类型和String类型。如果还有其他类型需要自己扩展。
     * @param workbook 工作簿
     * @param c 实体类
     * @return 实体类集合
     */
    public static <T> List<T> importExcel(Workbook workbook,Class<?> c){
        List<T> dataList = new ArrayList<>();
        try {
            Sheet sheet = workbook.getSheetAt(0);
            int i = 0;
            T o = null;
            for (Row row : sheet) {
                Row row1 = sheet.getRow(i + 1);
                if(row1 != null){
                    o = (T) c.newInstance();
                    Field[] declaredFields = c.getDeclaredFields();
                    for (int i1 = 0; i1 < declaredFields.length; i1++) {
                        String name = declaredFields[i1].getName();
                        Field declaredField1 = o.getClass().getDeclaredField(name);
                        declaredField1.setAccessible(true);
                        Cell cell = row1.getCell(i1);
                        String type = declaredFields[i1].getType().getName();
                        String value = String.valueOf(cell);
                        if(StringUtils.equals(type,"int") || StringUtils.equals(type,"Integer")){
                            declaredField1.set(o,Integer.parseInt(value));
                        } else if(StringUtils.equals(type,"java.lang.String") || StringUtils.equals(type,"char") || StringUtils.equals(type,"Character") ||
                                StringUtils.equals(type,"byte") || StringUtils.equals(type,"Byte")){
                            declaredField1.set(o,value);
                        } else if(StringUtils.equals(type,"boolean") || StringUtils.equals(type,"Boolean")){
                            declaredField1.set(o,Boolean.valueOf(value));
                        } else if(StringUtils.equals(type,"double") || StringUtils.equals(type,"Double")){
                            declaredField1.set(o,Double.valueOf(value));
                        } else if (StringUtils.equals(type,"long") || StringUtils.equals(type,"Long")) {
                            declaredField1.set(o,Long.valueOf(value));
                        } else if(StringUtils.equals(type,"short") || StringUtils.equals(type,"Short")){
                            declaredField1.set(o,Short.valueOf(value));
                        } else if(StringUtils.equals(type,"float") || StringUtils.equals(type,"Float")){
                            declaredField1.set(o,Float.valueOf(value));
                        }
                    }
                }
                dataList.add(o);
            }
            workbook.close();
            return dataList;
        }catch (Exception e){
            e.printStackTrace();
        }
        return dataList;
    }
}

4、web导入导出

   @PostMapping("/export")
    public void export() throws Exception {
        String data = "[{}]";
        //模拟json数据
        ExportUtils.exportExcel("学生信息", data, Student.class, response);
    }

    @PostMapping("/import")
    public void importExcel(@RequestParam("excel") MultipartFile excel) {
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(excel.getInputStream());
            Sheet sheet = workbook.getSheetAt(0);
            List<Student> students = new ArrayList<>();
            int i = 0;
            for (Row row : sheet) {
                Row row1 = sheet.getRow(i + 1);
                if (row1 != null) {
                    Student data = new Student();
                    data.setStudentId(Integer.parseInt(row1.getCell(0).getStringCellValue()));
                    data.setName(row1.getCell(1).getStringCellValue());
                    data.setAge(Integer.parseInt(row1.getCell(2).getStringCellValue()));
                    data.setCredit(Integer.parseInt(row1.getCell(3).getStringCellValue()));
                    students.add(data);
                }
            }
            System.out.println(students);
            workbook.close();
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }