springboot 整合POI 导入导出execl表格

it大佬博哥

已于 2022-11-29 16:02:11 修改

阅读量551

点赞数

于 2022-11-29 15:05:18 首次发布

本文链接：https://blog.csdn.net/m0_63630791/article/details/128098466

版权

先看效果

效果就是导出：是把数据库中的数据转成表格数据
导入：导入一个表格然后在表格里面的数据新增到数据库中

先看工具类

package com.haogezye.util;

import com.haogezye.pojo.User;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.tomcat.util.http.fileupload.ByteArrayOutputStream;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

public class POIUtils {
   
//导出  把list数据转成表格数据
    public static ResponseEntity<byte[]> employee2Excel(List<User> list) {
   
        //1.创建一个Excel文档 以.xls结尾
        HSSFWorkbook workbook = new HSSFWorkbook();
        //2.创建文档摘要
        workbook.createInformationProperties();
        //3.获取并配置文档信息DocumentSummaryInformation
        DocumentSummaryInformation docInfo = workbook.getDocumentSummaryInformation();
        docInfo.setCategory("员工信息");
        docInfo.setManager("fyf");
        docInfo.setCompany("ff公司");

        //4.获取文档摘要信息SummaryInformation
        SummaryInformation summInfo = workbook.getSummaryInformation();
        summInfo.setTitle("员工信息表");
        summInfo.setAuthor("fyf");
        //设置备注
        summInfo.setComments("文档由java提供");

        //创建标题行的样式HSSFCellStyle
        HSSFCellStyle headerStyle = workbook.createCellStyle();
        //setFillForegroundColor setFillPattern要同时设置才有效 单元格颜色的设置IndexedColors
        headerStyle.setFillForegroundColor(IndexedColors.YELLOW.index);
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //设置日期行的样式HSSFDataFormat
        HSSFCellStyle dateCellStyle = workbook.createCellStyle();
        dateCellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy"));

        //创建sheetHSSFSheet
        HSSFSheet sheet = workbook.createSheet("员工信息表");

        //创建列的宽度 单位是bit
        sheet.setColumnWidth(0, 5 * 256);
        sheet.setColumnWidth(1, 12 * 256);
        sheet.setColumnWidth(2, 10 * 256);
        sheet.setColumnWidth(3, 5 * 256);
        sheet.setColumnWidth(4, 12 * 256);

        //6.创建标题行HSSFRow
        HSSFRow r0 = sheet.createRow(0);
        HSSFCell c0 = r0.createCell(0);
        c0.setCellValue("编号");
        c0.setCellStyle(headerStyle);
        HSSFCell c1 = r0.createCell(1);
        c1.setCellStyle(headerStyle);
        c1.setCellValue("姓名");
        HSSFCell c2 = r0.createCell(2);
        c2.setCellStyle(headerStyle);
        c2.setCellValue("工号");
        HSSFCell c3 = r0.createCell(3);
        c3.setCellStyle(headerStyle);
        c3.setCellValue("性别");
        HSSFCell c4 = r0.createCell(4);
        c4.setCellStyle(headerStyle);
        c4.setCellValue("出生日期");

        //填充行数据
        for (int i = 0; i < list.size(); i++) {
   
            User emp = list.get(i);
            //第一行是字段
            HSSFRow row