Hutool Excel模板下载，Excel数据导入

• excel导入的时候没有指定表头的head，如果有人修改了excel的head，不影响功能使用

        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.3.8</version>
        </dependency>
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.10</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.1</version>
        </dependency>
						<-- 添加插件-->
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-resources-plugin</artifactId>
                <configuration>
                    <encoding>UTF-8</encoding>
                    <nonFilteredFileExtensions>
                                    <nonFilteredFileExtension>xlsx</nonFilteredFileExtension>
                                    <nonFilteredFileExtension>xls</nonFilteredFileExtension>
                   </nonFilteredFileExtensions>
                </configuration>
            </plugin>

excel

package com.example.demo.utils;

import cn.hutool.core.io.IoUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSONObject;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.springframework.stereotype.Controller;
import org.springframework.util.ResourceUtils;
import org.springframework.util.StringUtils;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.util.*;

@Controller
@RequestMapping("/excel")
public class ExcelController {


  
    @PostMapping("/download")
    public void download(HttpServletResponse response) {
        try {
            // 1 读取对象
            File file = ResourceUtils.getFile("classpath:excel/用户信息.xls");
            InputStream inputStream = new FileInputStream(file);
            final ExcelReader reader = ExcelUtil.getReader(inputStream);
            List<List<Object>> lists = reader.read();
            ExcelWriter writer = ExcelUtil.getWriter(false);
            writer.write(lists);
            //设置列宽
            writer.setColumnWidth(3, 15);
            response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode("settlementFeedback.xls", "UTF-8"));
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");


        //设置响应头
       //     SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
 //        response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
//        response.setContentType("application/vnd.ms-excel");
//        response.setHeader("Content-Disposition", "attachment;filename="+URLEncoder.encode("fileName"+"_"+sdf.format(new Date()), "UTF-8")+".xlsx");

  
          ServletOutputStream outputStream = response.getOutputStream();
            writer.flush(outputStream, true);
            writer.close();
            IoUtil.close(outputStream);
        } catch (IOException e) {
            System.out.println(e.getMessage());
        }
    }


    @PostMapping("/upload")
    public String upload(@RequestParam("uploadFile") MultipartFile multipartFile) {
        String suffex = multipartFile.getOriginalFilename().split("\\.")[1];
        if (!"xls".equals(suffex)) {
            return "导入失败,只支持xls文件上传";
        }
        if (Objects.isNull(multipartFile)) {
            return "导入失败,上传文件为空";
        }
        //得到文件流
        InputStream is = null;
        StringBuffer errMsg = new StringBuffer("");
        try {
            is = multipartFile.getResource().getInputStream();
            //导入的excel 只有五列数据，故此处传5
            List<Map<Integer, String>> importData = getImportData(is, 5);
            for (int i = 0; i < importData.size(); i++) {
                Map<Integer, String> map = importData.get(i);
                try {
                    User user = new User();
                    if (StringUtils.isEmpty(map.get(0))) {
                        continue;
                    }
                    //数字编号和excel列一一对应，如果新增列需要同步改动
                    user.setName(map.get(0));
                    user.setAge(Integer.valueOf(map.get(1)));
                    user.setAddress(map.get(2));
                    user.setEmail(map.get(3));
                    user.setPhone(map.get(4));
                    System.out.println(JSONObject.toJSONString(user));
                } catch (Exception e) {
                  //  log.error("清单反馈上传{}行数据异常->{}",i,e.getMessage());
                    errMsg.append(i+1 + ",");
                }
            }
        } catch (Exception e) {
         //   log.error("医保反馈上传失败->{}",e.getMessage());
            IoUtil.close(is);
        }
        if (errMsg.length() > 0) {
            return errMsg.toString() + "行数据导入失败,其余导入成功.";
        }
        return "导入成功";
    }

    /**
     *
     * @param is
     * @param size excel 列的个数
     * @return
     */
    public static List<Map<Integer, String>> getImportData(InputStream is,int size) {
        if (is == null) {
            return Collections.emptyList();
        }
        Workbook workbook = null;
        try {
            workbook = Workbook.getWorkbook(is);
        } catch (BiffException | IOException e) {
            //log.error(e.getMessage(), e);
            return Collections.EMPTY_LIST;
        }
        if (workbook == null) {
            return Collections.emptyList();
        }
        //第一个sheet
        Sheet sheet = workbook.getSheet(0);
        //行数
        int rowCounts = sheet.getRows() - 1;
        if (rowCounts == 0) {
            return Collections.emptyList();
        }
        List<Map<Integer, String>> list = new ArrayList<>(Math.max(rowCounts, 0));
        //双重for循环取出数据
        for (int i = 1; i <= rowCounts; i++) {
            Map<Integer, String> params = new HashMap<>();
            //获取第i行数据
            Cell[] cells = sheet.getRow(i);
            //判断是否是空行
            boolean empty = Arrays.stream(cells).allMatch(cell -> CellType.EMPTY.equals(cell.getType()));
            if (!empty) {
                for (int j = 0; j < size; j++) {
                    //获取低i行第J列数据
                    Cell cell = sheet.getCell(j, i);
                    params.put(j, cell.getContents());
                }
                list.add(params);
            } else {
                break;
            }
        }
        return list;
    }
}

代码有点 遗憾就是导出 名字不能是中文 如果解决了 会继续补充