基於Js和Java+MyBatis實現xlsx\xls文檔的導入下載、導出 背景: 實現xlsx\xls文檔的導入、導出 導入效果： 導出效果： 導出效果圖 1、導入、下載 1.1、前臺 <div style="margin-left: 15px"> <input type="file" ...
基於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('dcexcel','')"></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、最後
如果這篇文章幫助到您的話，可以請作者喝杯咖啡，以便更有動力的分享。感謝。
