基于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、最后
如果这篇文章帮助到您的话，可以请作者喝杯咖啡，以便更有动力的分享。感谢。
