主要的难点在service层解析excel表格数据这里，因为java这里有好多种数据，一旦excel表格里面不是java对应的数据类型就会报错。还有就是在数据库批量导入的时候mybatis有大小限制的
首先是导入前的下载模板
public class HolidayConfigController {
@GetMapping(value = "/downloadTemplate")
public void downloadTemplate(HttpServletResponse response, HttpServletRequest request){
log.info("HolidayController.downloadTemplate success Enter this method");
try {
//生成Excel文件
HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
HSSFSheet sheet = hssfWorkbook.createSheet("Sheet0");
// 设置表格默认列宽度为15个字节
sheet.setDefaultColumnWidth((short)15);
HSSFRow headRow = sheet.createRow(0);
headRow.createCell(0).setCellValue("节假日日期");
headRow.createCell(1).setCellValue("节假日描述");
HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum()+1);
dataRow.createCell(0).setCellValue("2018-10-01");
dataRow.createCell(1).setCellValue("国庆节");
dataRow.createCell(2).setCellValue("请严格按此格式录入数据");
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
String filename = "节假日数据导入模板.xls";
String agent = request.getHeader("user-agent");//获得游览器
String downloadFilename = FileUtil.encodeDownloadFilename(filename, agent); //使用工具类解决文件乱码的问题
response.addHeader("Content-Disposition", "attachment;filename="+downloadFilename);
ServletOutputStream outputStream = response.getOutputStream();
hssfWorkbook.write(outputStream);
//关闭
hssfWorkbook.close();
} catch (IOException e) {
log.error("HolidayController.downloadTemplate Exception",e);
}
}
使用FileUtil工具类处理因浏览器不同导致的乱码问题
public class FileUtil {
/**
* 下载文件时，针对不同浏览器，进行附件名的编码
*
* @param filename
* 下载文件名
* @param agent
* 客户端浏览器
* @return 编码后的下载附件名
* @throws IOException
*/
public static String encodeDownloadFilename(String filename, String agent)
throws IOException {
if (agent.contains("Firefox")) { // 火狐浏览器
filename = "=?UTF-8?B?"
+ new BASE64Encoder().encode(filename.getBytes("utf-8"))
+ "?=";
filename = filename.replaceAll("\r\n", "");
} else { // IE及其他浏览器
filename = URLEncoder.encode(filename, "utf-8");
filename = filename.replace("+"," ");
}
return filename;
}
}
其次是导入Execl数据，这里分2003版和2007版两个版本，同时用一种解析方式解析
@PostMapping(value = "/importExecl")
public PojoResult importExecl(@RequestParam("file")MultipartFile file){
try{
InputStream inputStream = file.getInputStream();
if(!inputStream.markSupported()) {
inputStream = new PushbackInputStream(inputStream, 8);
}
if(POIFSFileSystem.hasPOIFSHeader(inputStream)) {
log.info("HolidayController.importExecl Execl is 2003 and below");
Workbook workbook = new HSSFWorkbook(new POIFSFileSystem(file.getInputStream()));
String importXls = HolidayConfigService .importXlsOrXlsx(workbook);
if("success".equals(importXls)){
log.info("HolidayController.importExecl Execl data import successfully");
return PojoResultUtil.success(Boolean.TRUE);
}
log.error("HolidayController.importExecl Execl data import failed");
ProductionMessageCode.BATCH_IMPORT_FAILURE.setMessage(importXls);
return PojoResultUtil.fail(ProductionMessageCode.BATCH_IMPORT_FAILURE);
}else if(POIXMLDocument.hasOOXMLHeader(inputStream)){
log.info("HolidayController.importExecl Execl is version 2007 and above");
Workbook workbook = new XSSFWorkbook(file.getInputStream());
String importXls = HolidayConfigService .importXlsOrXlsx(workbook);
if("success".equals(importXls)){
log.info("HolidayController.importExecl Execl data import successfully");
return PojoResultUtil.success(Boolean.TRUE);
}
log.error("HolidayController.importExecl Execl data import failed");
ProductionMessageCode.BATCH_IMPORT_FAILURE.setMessage(importXls);
return PojoResultUtil.fail(ProductionMessageCode.BATCH_IMPORT_FAILURE);
}else {
log.error("HolidayController.importExecl Execl Format does not exist");
return PojoResultUtil.fail(ProductionMessageCode.EXECL_NOT_EXISTS);
}
}catch (Exception e){
log.error("HolidayController.importExecl Execl data import Execption",e);
return PojoResultUtil.fail(ProductionMessageCode.QUERY_FAIL);
}
}
然后是service层的解析方式
public class HolidayConfigService {
@Transactional
public String importXlsOrXlsx(Workbook workbook){
UserInfo userInfo = UserInfoThreadLocal.get();
//获取Excel第一个sheet页
DataFormatter formatter = new DataFormatter();
List<HolidayConfigDO> holidayConfigs = new ArrayList<>();
//定义错误信息记录集合
List<String> list = new ArrayList<>();
for(int numOfSheet = 0;numOfSheet < workbook.getNumberOfSheets(); numOfSheet++){
Sheet sheets =workbook.getSheetAt(numOfSheet);
list.add("第Sheet" + numOfSheet +"页");
//遍历行row
for(int rowNum = 0; rowNum<=sheets.getLastRowNum();rowNum++){
Row row = sheets.getRow(rowNum);
if(row.getRowNum()==0){
//如果是第一行直接跳出本次循环
continue;
}
HolidayConfigDO holidayConfigDO = new HolidayConfigDO();
//将每一行数据的每一单元格数据取出
try {
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
String dateCellValue = dateFormat.format(row.getCell(0).getDateCellValue());
if(StringUtils.isEmpty(dateCellValue)){
list.add("第"+(rowNum+1)+"行，第1格节假日日期不能为空");
}else{
Date day = dateFormat.parse(dateCellValue);
holidayConfigDO.setDay(day);
}
} catch (Exception e) {
list.add("第"+(rowNum+1)+"行，第1格节假日日期格式错误");
}
String description = formatter.formatCellValue(row.getCell(1));
if(StringUtils.isEmpty(description)){
list.add("第"+(rowNum+1)+"行，第2格节假日描述不能为空");
}else{
holidayConfigDO.setDescription(description);
}
holidayConfigDO.setDeleted(false);
holidayConfigDO.setType((byte)1);
holidayConfigDO.setCreator(userInfo.getEmpId());
holidayConfigDO.setModifier(userInfo.getEmpId());
holidayConfigs.add(holidayConfigDO);
}
//如果没有错误就将错误记录集合数据清空
if(!CollectionUtils.isEmpty(list) && list.size() == 1){
list.remove(0);
}
}
if(!CollectionUtils.isEmpty(list)){
return list.toString();
}
holidayConfigManager.batchInsertHolidayConfig(holidayConfigs);
return "success";
}
在附上批量插入数据的Mapper.xml文件
<insert id="batchInsertHolidayConfig">
insert into dcc_holiday_config
(gmt_create, gmt_modified, creator, modifier,day,type,is_deleted,description)
values
<foreach collection ="holidayConfigs" item="holidayConfig" index= "index" separator =",">
(
current_timestamp,
current_timestamp,
#{holidayConfig.creator,jdbcType=VARCHAR},
#{holidayConfig.modifier,jdbcType=VARCHAR},
#{holidayConfig.day,jdbcType=DATE},
#{holidayConfig.type,jdbcType=TINYINT},
#{holidayConfig.deleted,jdbcType=BIT},
#{holidayConfig.description,jdbcType=VARCHAR}
)
</foreach >
ON DUPLICATE KEY UPDATE
gmt_create = values (gmt_create),
gmt_modified = values (gmt_modified),
creator = values (creator),
modifier = values (modifier),
day = values (day),
type = values (type),
is_deleted = values (is_deleted),
description = values (description)
</insert>
网友评论