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

骑猪看日落 2023-01-20 14:52 2阅读 0赞

*  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 ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1eGllMTM_size_16_color_FFFFFF_t_70]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1eGllMTM_size_16_color_FFFFFF_t_70 1]

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");
                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;
        }
    }

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1eGllMTM_size_16_color_FFFFFF_t_70]: /images/20221021/af5ea1281bcb48e199aa78feeb463caa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3h1eGllMTM_size_16_color_FFFFFF_t_70 1]: /images/20221021/775d90644dc14806bad4e4a9d16a5d5d.png