hutool + spring boot 导入导出

最新推荐文章于 2024-03-19 20:23:29 发布

qq_31683775

最新推荐文章于 2024-03-19 20:23:29 发布

阅读量136

点赞数

文章标签： spring boot windows linux

本文链接：https://blog.csdn.net/qq_31683775/article/details/134644799

版权

文章介绍了如何使用Java技术，如ApachePOI库，实现Excel文件的导入和导出功能，包括读取单元格数据、处理表头和数据，以及生成和下载Excel文件的操作。

摘要由CSDN通过智能技术生成

1.导出

@PostMapping("/exportExcel")
    public void exportExcel(@RequestBody CadreExportExcelREQ req, HttpServletResponse response) {
        SysUser userInfo = AuthUtil.getUserInfo();
        if(!userInfo.getUsername().equals("admin")){
            //req.setCadreTelephone(userInfo.getMobile());
            Department userDepartmentByUserId = systemService.findUserDepartmentByUserId(userInfo.getId());
            if(userDepartmentByUserId == null){
                req.setUnit("-1");
            }else{
                req.setUnit(userDepartmentByUserId.getName());
            }
        }
        Map<String,Object> pmap=new HashMap<>();
        pmap.put("cadreName",req.getCadreName());
        pmap.put("cadreTelephone",req.getCadreTelephone());
        pmap.put("unit",req.getUnit());

        List<Map<String,Object>> cadreAndCompanyJoin = cityService.getCadreAndCompanyJoin(pmap);

        List<List<Object>> list =new ArrayList<>();
        for(Map<String,Object> city:cadreAndCompanyJoin){
            List<Object> list1=new ArrayList<>();
            //县区名称
            list1.add("镇平县");
            //县区编码
            list1.add("411324");
            //乡镇(街道)
            list1.add(city.get("unit"));
            //具体地址
            list1.add(city.get("adress"));

            //企业名称
            list1.add(city.get("company_name"));
            //统一社会
            //信用代码
            list1.add(city.get("social_credit_code"));
            //企业联系人
            list1.add(city.get("enterprise_contact"));
            //企业联系人电话
            list1.add(city.get("enterprise_contact_Num"));
            //包联企业层级
            list1.add(city.get("level"));

            //干部姓名
            list1.add(city.get("cadre_name"));
            //联系方式
            list1.add(city.get("cadre_telephone"));
            //性别
            list1.add(city.get("sex"));
            //职务
            list1.add(city.get("position"));
            //单位
            list1.add(city.get("cadre_unit"));

            //包联干部层级
            String cadre_level = (String)city.get("cadre_level");
            String cadreLevel01="";
            if(StringUtils.equals(cadre_level,"0")){
                cadreLevel01= "县处级";
            }else if(StringUtils.equals(cadre_level,"1")){
                cadreLevel01= "乡科级";
            }else if(StringUtils.equals(cadre_level,"2")){
                cadreLevel01= "股级及一般干部";
            }/*else if(StringUtils.equals(cadre_level,"3")){
                cadreLevel01= "一般干部";
            }*/
            list1.add(cadreLevel01);
            //企业类型
            String company_type = (String)city.get("company_type");
            if(company_type == null){
                company_type = "";
            }
            String[] split = StringUtils.split(company_type, ",");
            List<String> strings = Arrays.asList(split);
            List<String> out=new ArrayList<>();
            if(strings.contains("0")){
                out.add("万人助万企包联企业");
            }
            if(strings.contains("1")){
                out.add("营商环境样本企业库");
            }

            list1.add(StringUtils.join(out,","));

            //备注
            list1.add(city.get("remarks"));

            list.add(list1);
        }

        try {
            downloadExcel(list,response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出excel
     */
    public static void downloadExcel(List<List<Object>> list, HttpServletResponse response) throws IOException {
        /*String tempPath =System.getProperty("java.io.tmpdir") + IdUtil.fastSimpleUUID() + ".xlsx";
        File file = new File(tempPath);*/

        //获取模板文件第一个sheet
        ClassPathResource resource = new ClassPathResource("cadreAndCompany.xls");

        ExcelReader reader = ExcelUtil.getReader(resource.getStream());
        Sheet rows = reader.getSheets().get(0);

        BigExcelWriter bigExcelWriter = new BigExcelWriter(rows);
        bigExcelWriter.passRows(3);//跳过表头

        // 一次性写出内容，使用默认样式，强制输出标题
        bigExcelWriter.write(list);
        //response为HttpServletResponse对象
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
        //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
        response.setHeader("Content-Disposition","attachment;filename=file.xlsx");
        ServletOutputStream out=response.getOutputStream();
        // 终止后删除临时文件
        //file.deleteOnExit();
        bigExcelWriter.flush(out, true);
        //此处记得关闭输出Servlet流
        IoUtil.close(out);
    }

2.导入

@PostMapping("/importExcel")
    @Transactional
    public Result importExcel(@RequestParam MultipartFile file,Integer importType,String cleanUnitId){
    ExcelReader reader = ExcelUtil.getReader(file.getInputStream());
        List<Sheet> sheets = reader.getSheets();
        Sheet rows = sheets.get(0);
        List<Map<String, String>> sheetData = ImportExcelUtil.getSheetData(rows);
}

package com.enterprise.util.excel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import java.math.BigDecimal;
import java.util.*;

//读取excel工具类
public class ImportExcelUtil {
    /**
     * 读取Sheet 的所有数据
     * @param rows
     * @return
     */
    public static List<Map<String, String>> getSheetData(Sheet rows){
        Map<Integer, String> tableHeader=new HashMap<Integer, String>();
        List<Map<String, String>> list=new ArrayList<Map<String, String>>();

        int rowNum=0;
        Iterator<Row> rowIterator = rows.rowIterator();
        while (rowIterator.hasNext()){

            Row row = rowIterator.next();
            Iterator<Cell> cellIterator = row.cellIterator();
            int column=0;
            Map<String, String> tdata=new HashMap<>();
            while(cellIterator.hasNext()){
                Cell next = cellIterator.next();
                int columnIndex = next.getColumnIndex();
                String cellValue = getCellValue(next);
                if(rowNum == 0){
                    //第0行,表头
                    tableHeader.put(column,cellValue);
                }else {
                    tdata.put(tableHeader.get(columnIndex),cellValue);
                }
                column++;
            }
            if(rowNum != 0){
                list.add(tdata);
            }
            rowNum ++;
        }
        return list;
    }

    private static String getCellValue(Cell cell) {
        CellType cellType = cell.getCellType();
        if (cellType == CellType.STRING){
            String stringCellValue = cell.getStringCellValue();
            return stringCellValue;
        }else if (cellType == CellType.NUMERIC){
            Double value = cell.getNumericCellValue();
            BigDecimal bd1 = new BigDecimal(Double.toString(value));
            // 去掉后面无用的零  如小数点后面全是零则去掉小数点
            String s = "";
            if (bd1.toPlainString().contains(".")){
                s = bd1.toPlainString().replaceAll("0+?$", "").replaceAll("[.]$", "");
            }else{
                s = bd1.toPlainString();
            }

            return s;
        }
        return "";
    }
}