基于Js和Java实现xlsx\xls文档的导入和下载
基于Js和Java+MyBatis实现xlsx\xls文档的导入下载、导出配景:
实现xlsx\xls文档的导入、导出
导入效果：
https://img2024.cnblogs.com/blog/3295506/202403/3295506-20240315094215011-1705463562.png
https://img2024.cnblogs.com/blog/3295506/202403/3295506-20240315094201226-220861041.png
导出效果：
https://img2024.cnblogs.com/blog/3295506/202403/3295506-20240315094503110-57047663.png
导出效果图
1、导入、下载
1.1、前台
<input type="file" id="selectFile" name="selectFile" />
<button id="search" onclick="getData()" type="button" >
查询
</button>
<button type="button" onclick="Import();return false;">导入</button>
<a id="dcexcel" target="_blank" href="https://www.cnblogs.com/javascript:__doPostBack('dcexcel','')"></a>
<input type="hidden" name="hfQueryString" id="hfQueryString" />
<button onclick="downTemplate()" type="button">模板下载</button>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;
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;">导出</button>
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、末了
如果这篇文章帮助到您的话，可以请作者喝杯咖啡，以便更有动力的分享。感谢。
https://img2024.cnblogs.com/blog/3295506/202403/3295506-20240320113613160-1714073514.jpg
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]