基於Js和Java實現xlsxxls文件的匯入和下載

基於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、最後

如果這篇文章幫助到您的話，可以請作者喝杯咖啡，以便更有動力的分享。感謝。