基于Js和Java实现xlsx\xls文档的导入和下载

2024-03-20 11:14 由大沐沐沐发表于 #后端开发

基于Js和Java+MyBatis实现xlsx\xls文档的导入下载、导出

背景:

实现xlsx\xls文档的导入、导出

导入效果：

导出效果：

导出效果图

1、导入、下载

1.1、前台

<div style="margin-left: 15px">
    <input type="file" id="selectFile" name="selectFile" />
    <button id="search" onclick="getData()" type="button" class="primary">
        <span class="icon_find">查询</span>
    </button>
    <button type="button" onclick="Import();return false;"><span class="icon_import">导入</span></button>

    <a id="dcexcel" href="javascript:__doPostBack(&#39;dcexcel&#39;,&#39;&#39;)"></a>
    <input type="hidden" name="hfQueryString" id="hfQueryString" />
    <button onclick="downTemplate()" type="button"><span class="icon_light">模板下载</span></button>
</div>



<script type="text/javascript">
    
 	$(function () {
        // 绑定事件 扩展:有bind（绑定事件），就有unbind（解除事件），
        $('#selectFile').bind('change', function () {
            ajaxFileUpload();
            $("#selectFile").replaceWith($("#selectFile").clone(true));
        })
	})
    
    function ajaxFileUpload() {
        if ($('#selectFile').val() == '') {
            top.Dialog.alert("请先选择导入文件后导入！", null, null, null, null);
            return;
        }

        var formDate = new FormData()
        formDate.append("myFile", $("#selectFile")[0].files[0])
        $.ajax({
            url: getBasePath() + "/xxxxxx/importExcel",
            data: formDate,
            type: "POST",
            dataType: "JSON",
            contentType: false,
            processData: false,
            beforeSend: function () {
                top.Dialog.alert("文件导入中，请耐心等候", null, null, null, null);
            },
            success: function (data) {
                if (data.success) {
                    top.Dialog.alert(data.msg, null, null, null, null);
                } else {
                    top.Dialog.alert(data.msg, null, null, null, null);
                }
            }
        })

    }

    //导入
    function Import() {
        $('#selectFile').click();
    }


    //下载模版
    function downTemplate() {
        window.location.href = getBasePath() + '/xxxxxx/download'
    }
    
</script>

1.2、后台

1.2.1、实体类


import java.io.Serializable;


public class TbXXXXXXEntity implements Serializable {

	private static final long serialVersionUID = 1L;

	/**
	 * 主键
	 */
	private Integer id;
	/**
	 * 第一列数据编码
	 */
	private String columnDataCode1;
	/**
	 * 第一列数据
	 */
	private String columnData1;
	/**
	 * 第二列数据
	 */
	private String columnData2;
	/**
	 * 第三列数据
	 */
	private String columnData3;
	/**
	 * 第四列数据
	 */
	private String columnData4;
	/**
	 * 第五列数据
	 */
	private String columnData5;
	/**
	 * 年份
	 */
	private Integer year;
	/**
	 * 单位
	 */
	private String dw;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getYear() {
		return year;
	}

	public void setYear(Integer year) {
		this.year = year;
	}

	public String getColumnDataCode1() {
		return columnDataCode1;
	}

	public void setColumnDataCode1(String columnDataCode1) {
		this.columnDataCode1 = columnDataCode1;
	}

	public String getColumnData1() {
		return columnData1;
	}

	public void setColumnData1(String columnData1) {
		this.columnData1 = columnData1;
	}

	public String getColumnData2() {
		return columnData2;
	}

	public void setColumnData2(String columnData2) {
		this.columnData2 = columnData2;
	}

	public String getColumnData3() {
		return columnData3;
	}

	public void setColumnData3(String columnData3) {
		this.columnData3 = columnData3;
	}

	public String getColumnData4() {
		return columnData4;
	}

	public void setColumnData4(String columnData4) {
		this.columnData4 = columnData4;
	}

	public String getColumnData5() {
		return columnData5;
	}

	public void setColumnData5(String columnData5) {
		this.columnData5 = columnData5;
	}

	public String getDw() {
		return dw;
	}

	public void setDw(String dw) {
		this.dw = dw;
	}
}

1.2.2、导入实体类

import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelIgnore;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import java.io.Serializable;


@ExcelTarget("TbXXXXXXExcelDto")
public class TbXXXXXXExcelDto implements Serializable {
	private static final long serialVersionUID = 121871957378211533L;

	/**
	 * 第一列数据
	 */
	@Excel(name = "第一列数据")
	private String columnData1;
	/**
	 * 第二列数据
	 */
	@Excel(name = "第二列数据")
	private String columnData2;
	/**
	 * 第三列数据
	 */
	@Excel(name = "第三列数据")
	private String columnData3;
	/**
	 * 第四列数据
	 */
	@Excel(name = "第四列数据")
	private String columnData4;
	/**
	 * 第五列数据
	 */
	@Excel(name = "第五列数据")
	private String columnData5;
    
	/**
	 *  主键
	 */
	@ExcelIgnore
	private Integer id;
	/**
	 * 第一列数据编码
	 */
	@ExcelIgnore
	private String columnDataCode1;
	/**
	 * 单位
	 */
	@ExcelIgnore
	private String dw;
	/**
	 * 年限
	 */
	@Excel(name = "年限")
	private Integer year;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public Integer getYear() {
		return year;
	}

	public void setYear(Integer year) {
		this.year = year;
	}

	public String getColumnData1() {
		return columnData1;
	}

	public void setColumnData1(String columnData1) {
		this.columnData1 = columnData1;
	}

	public String getColumnData2() {
		return columnData2;
	}

	public void setColumnData2(String columnData2) {
		this.columnData2 = columnData2;
	}

	public String getColumnData3() {
		return columnData3;
	}

	public void setColumnData3(String columnData3) {
		this.columnData3 = columnData3;
	}

	public String getColumnData4() {
		return columnData4;
	}

	public void setColumnData4(String columnData4) {
		this.columnData4 = columnData4;
	}

	public String getColumnData5() {
		return columnData5;
	}

	public void setColumnData5(String columnData5) {
		this.columnData5 = columnData5;
	}

	public String getDw() {
		return dw;
	}

	public void setDw(String dw) {
		this.dw = dw;
	}
}

1.2.3、控制层

    /**
     * 模板下载
     *
     * @param response response对象
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response) {
        TbXXXXXXService.download(response);
    }

    /**
     * 导入
     *
     * @param file 文件对象
     * @return 返回状态
     */
    @PostMapping("/importExcel")
    public Map<String, Object> importExcel(@RequestParam("myFile") MultipartFile file) {
        return tbXXXXXXService.importExcel(file);
    }

1.2.4、服务层


    Map<String, Object> importExcel(MultipartFile file);


    List<TbXXXXXXEntity> getList(Map<String, Object> params);

1.2.5、服务实现层

1、表格样式：

基本情况
单位：测试单位	2024年
第一列数据	第二列数据	第三列数据	第四列数据	第五列数据

实现代码

    /**
     * 模板下载
     *
     * @param response response对象
     */
    @Override
    public void download(HttpServletResponse response) {
        response.setContentType("application/octet-stream");
        try {
            response.setHeader("Content-disposition", "attachment;filename=" + new String("XXXXXX模板.xlsx".getBytes("utf-8"), "ISO8859-1"));
            response.setCharacterEncoding("UTF-8");
            ClassPathResource classPathResource = new ClassPathResource("./static/template/XXXXXX.xlsx");
            FileInputStream fileInputStream = new FileInputStream(classPathResource.getFile());
            OutputStream outputStream = response.getOutputStream();
            BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
            BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);
            XSSFWorkbook workBook = new XSSFWorkbook(bufferedInputStream);
            workBook.write(bufferedOutputStream);
            fileInputStream.close();
            outputStream.close();
            bufferedInputStream.close();
            bufferedOutputStream.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    /**
     * Excel表格导入
     *
     * @param file 文件对象
     * @return 返回状态
     */
    @Transactional
    @Override
    public Map<String, Object> importExcel(MultipartFile file) {
        Map<String, Object> resultMap = new HashMap<>();

        String originalFilename = file.getOriginalFilename();
        String substring = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
        if (!"xls".equals(substring) && !"xlsx".equals(substring)) {
            resultMap.put("success", false);
            resultMap.put("msg", "文件格式错误，请上传xls、xlsx文件！");
            return resultMap;
        }

        try (InputStream inputStream = file.getInputStream();
             Workbook workbook = new XSSFWorkbook(inputStream)) {

            Sheet sheet = workbook.getSheetAt(0); // 获取第一个工作表
            Iterator<Row> rowIterator = sheet.iterator();

            String dw = null;
            String year = null;
            // 跳过标题行
            if (rowIterator.hasNext()) {
                // 跳过第一行
                rowIterator.next();
                // 跳过第二行，并获取第二行数据
                Row next = rowIterator.next();
                // 查看第二行数据
                dw = next.getCell(0).getStringCellValue();
                year = next.getCell(2).getStringCellValue();
                // 跳过第三行
                rowIterator.next();
            }
			
            // 单独获取第二行数据
            if (StringUtils.isNotBlank(dw)){
                dw = dw.replace("单位：", "");
            }
            if (StringUtils.isNotBlank(year)){
                year = year.replace("年", "");
            }
            // 获取列标题
            Map<Integer, String> headerMap = new HashMap<>();
            Row headerRow = sheet.getRow(2);
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell headerCell = headerRow.getCell(i);
                headerMap.put(i, headerCell.getStringCellValue());
            }

            List<TbXXXXXXExcelDto> list = new LinkedList<>();
            // 解析数据行
            while (rowIterator.hasNext()) {
                Row next = rowIterator.next();
                // 获取第一列数据
                String columnData1 = next.getCell(0).getStringCellValue();
                // 获取第二列数据
                String columnData2 = next.getCell(1).getStringCellValue();
                // 获取第三列数据
                String columnData3 = next.getCell(2).getStringCellValue();
                String columnData4 = next.getCell(3).getStringCellValue();
                String columnData5 = next.getCell(4).getStringCellValue();

                TbXXXXXXExcelDto dto = new TbXXXXXXExcelDto();
                dto.setcolumnData1(columnData1);
                dto.setColumnData2(columnData2);
                dto.setColumnData3(columnData3);
                dto.setColumnData4(columnData4);
                dto.setColumnData5(columnData5);
                dto.setDw(dw);
                if (StringUtils.isNotBlank(year)){
                    dto.setYear(Integer.valueOf(year));
                }

                list.add(dto);

                if (list.size() % 10 == 0){
                    tbXXXXXXDao.insertBatch(list);
                    list.clear();
                }
            }
            if (!list.isEmpty()){
                tbXXXXXXDao.insertBatch(list);
            }
            list.clear();
            resultMap.put("success", true);
            resultMap.put("msg", "导入成功！");
            return resultMap;

        } catch (IOException e) {
            // 处理文件读取异常
            e.printStackTrace();
            resultMap.put("success", false);
            resultMap.put("msg", "解析失败！请检查字段格式后重新导入！");
        }
        return resultMap;
    }

2、表格样式：

人员信息记录导出
姓名	学历	政治面貌	性别	民族	在岗状态	隶属部门	职业	入职日期
测试人员	本科	群众	女	汉族	在岗	综合部	保管人员	2023-10-12

实现代码：

    /**
     * Excel表格导入
     *
     * @param file 文件对象
     * @return 返回状态
     */
    @Transactional
    @Override
    public Map<String, Object> importExcel(MultipartFile file) {
        Map<String, Object> resultMap = new HashMap<>();
        String originalFilename = file.getOriginalFilename();
        String substring = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
        if (!"xls".equals(substring) && !"xlsx".equals(substring)) {
            resultMap.put("success", false);
            resultMap.put("msg", "文件格式错误，请上传xls、xlsx文件！");
            return resultMap;
        }
        try {
            InputStream inputStream = file.getInputStream();
            File tempFile = File.createTempFile(file.getOriginalFilename(), ".tmp");
            FileOutputStream fileOutputStream = new FileOutputStream(tempFile);
            byte[] buffer = new byte[1024];
            int bytesRead = 0;
            while ((bytesRead = inputStream.read(buffer)) != -1) {
                fileOutputStream.write(buffer, 0, bytesRead);
            }
            fileOutputStream.close();
            inputStream.close();
            tempFile.deleteOnExit();

            // 解析Excel数据
            ImportParams params = new ImportParams();
            params.setTitleRows(1);
            params.setHeadRows(1);
            List<TbXxxExcelDto> excelList = ExcelImportUtil.importExcel(tempFile, TbXxxExcelDto.class, params);
		
            // 查询身份证信息
            List<String> sfzhmList = xxxMapper.getSfzhm();

            // 检查重复数据
            if (!CollectionUtils.isEmpty(excelList) && !CollectionUtils.isEmpty(sfzhmList)) {
                for (TbXxxExcelDto excelDto : excelList) {
                    int repeat = 0;
                    // 遍历身份证信息
                    for (String sfzhm : sfzhmList) {
                        if (sfzhm.equals(excelDto.getSfzhm())) {
                            repeat = 1;
                            resultMap.put("success", false);
                            resultMap.put("msg", "导入失败！系统中已存在相同的人员信息，请检查后重新导入！");
                            break;
                        }
                    }
                    if (repeat == 1) {
                        return resultMap;
                    }
                }
            }

            // 查询民族数据
            List<Xxx> mzList = xxxMapper.findByCatalogCode("A1xx");

            // 匹配数据
            if (!CollectionUtils.isEmpty(excelList)) {

                excelList.forEach(excelDto -> {
                    if (StringUtils.isNotBlank(excelDto.getMz())) {
                        for (Xxx mz : mzList) {
                            if (mz.getKeyName().equals(excelDto.getMz())) {
                                excelDto.setMz(mz.getKeyValue());
                            }
                        }
                    }
					
                    // 获取城市信息
                    if (StringUtils.isNotBlank(excelDto.getCounty())) {
                        TbSysArea area = xxxMapper.findAreaByName(excelDto.getCounty());
                        if (area != null) {
                            excelDto.setXzqhdm(area.getAreaCode());
                        }
                    }

                    Timestamp timestamp = new Timestamp(LocalDateTime.now().toInstant(ZoneOffset.UTC).toEpochMilli());
                    // 插入时间
                    excelDto.setSyntime(timestamp);
                });
                // sql Server 一次最多支持2100个参数，需要分批插入,按每80条插入,每条25个参数, subList包括首部不包括尾部
                // 商
                int quotient = excelList.size() / 80;
                // 余数
                int remainder = excelList.size() % 80;
                int row = 0;
                for (int i = 0; i < quotient; i++) {
                    if (row == 0) {
                        // 批量插入
                        xxxMapper.insertBatch(excelList.subList(0, 80));
                        row = row + 80;
                    } else {
                        xxxMapper.insertBatch(excelList.subList(row, row + 80));
                        row = row + 80;
                    }
                }
                if (remainder != 0) {
                    xxxMapper.insertBatch(excelList.subList(quotient * 80, quotient * 80 + remainder));
                }
                resultMap.put("success", true);
                resultMap.put("msg", "导入成功！");
                return resultMap;
            }
            resultMap.put("success", true);
            resultMap.put("msg", "空文件，无须解析！");
        } catch (Exception e) {
            e.printStackTrace();
            resultMap.put("success", false);
            resultMap.put("msg", "解析失败！请检查字段格式后重新导入！");
        }
        return resultMap;
    }

1.2.6、Dao层

    /**
     * 批量插入信息
     *
     * @param list 信息
     * @return 受影响的行数
     */
    Integer insertBatch(@Param("list") List<TbXXXXXXExcelDto> list);

1.2.7、mapper层

<select id="insertBatch" parameterType="java.util.List" resultType="java.lang.Integer">
    INSERT INTO tb_ex_xxxxxx
        ( year,columnDataCode1,columnData1,columnData2,columnData3,columnData4,columnData5,dw ) VALUES
    <foreach collection="list" item="item" separator="," index="item">
        ( #{item.year}, #{item.columnDataCode1}, #{item.columnData1},  #{item.columnData2},
        #{item.columnData3},#{item.columnData4}, #{item.columnData5},#{item.dw} )
    </foreach>
</select>

2、导出

2.1、前台


<button id="exportButton" onclick="exportDate();return false;"><span class="icon_export">导出</span></button>
    
<script type="text/javascript">
//导出
function exportDate() {
       window.location.href = getBaseUrl() + "/Xxxreport/export?cmd=export&OrgCode=" + orgCode;
       // top.Dialog.alert("导出操作已经开始执行,请耐心等候,请勿重复点击导出按钮!", function () {
       // })
}
    
</script>

2.2、后台

1.2.1、实体类


import cn.afterturn.easypoi.excel.annotation.Excel;
import java.io.Serializable;
import java.util.Date;

public class TbXxxEntity implements Serializable {
	private static final long serialVersionUID = 1L;

	/**
	 * 
	 */
	private String id;
	/**
	 * 
	 */
	private String name;
	/**
	 * 
	 */
	private String content;

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}
}

1.2.2、导出实体类


import cn.afterturn.easypoi.excel.annotation.Excel;
import cn.afterturn.easypoi.excel.annotation.ExcelTarget;
import java.io.Serializable;

/**
 * 
 * 
 */
@ExcelTarget("TbXxxreportVo")
public class TbXxxreportVo implements Serializable {
	private static final long serialVersionUID = 121871957378211532L;

	private String id;
	/**
	 * 名称
	 */
	@Excel(name = "名称", width = 18)
	private String name;

	/**
	 * 描述
	 */
	@Excel(name = "描述", width = 18)
	private String content;

	public String getId() {
		return id;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getContent() {
		return content;
	}

	public void setContent(String content) {
		this.content = content;
	}

}

1.2.3、控制层

    @GetMapping("/export")
    public void export(HttpServletResponse response, @RequestParam String OrgCode) throws IOException {
        tbxxxreportService.export(response, OrgCode);
    }

1.2.4、服务层

    void export(HttpServletResponse response, String OrgCode) throws IOException;

1.2.5、服务实现层

    @Override
    public void export(HttpServletResponse response, String OrgCode) throws IOException {
        HashMap<String, Object> map = new HashMap<>();
        map.put("OrgCode", OrgCode);
        List<TbXxxreportVo> list = tbXxxreportDao.getExport(map);

        ExportParams exportParams = new ExportParams("XXX导出", "XXX导出");
        exportParams.setStyle(ExcelStyleType.BORDER.getClazz());
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, TbXxxreportVo.class, list);
        String fileName = "XXX.xls";
        response.reset();
        response.setContentType("application/octet-stream");
        response.setHeader("Content-disposition", "attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
        response.flushBuffer();
        workbook.write(response.getOutputStream());
    }

1.2.6、Dao层

   List<TbXxxreportVo> getExport(Map<String, Object> params);

1.2.7、mapper层

    <!--导出-->
    <select id="getExport"  resultType="com.XX.XX.vo.TbXxxreportVo" parameterType="java.util.Map">
        select name, content from Tb_Xxx
        <where>
            1 = 1
            <if test="OrgCode!= null and OrgCode != ''">
                and OrgCode = #{OrgCode}
            </if>
        </where>
    </select>

3、最后

如果这篇文章帮助到您的话，可以请作者喝杯咖啡，以便更有动力的分享。感谢。