在工作中遇到需要使用Excel导入数据到数据库中的需求，因此需要使用Java解析Excel文件，并把解析完成后的每一条记录循环插入到数据库中，同时还需要对Excel文件中每一列的数据做格式校验，校验完成后才执行插入的操作。
因为是前台上传Excel，后台接口中需要接收对应的文件，然后将其转换为Workbook对象，交由业务层进行解析。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
@RequestMapping("/batchUpload")
@ResponseBody
public String batchUpload(HttpServletRequest request, HttpServletResponse response)throws Exception{
Writer out = null;
Workbook workbook = null;
try {
response.setContentType("text/html;charset=UTF-8;type=json");
out = new BufferedWriter(new OutputStreamWriter(response.getOutputStream()));
CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
request.getSession().getServletContext());
if (multipartResolver.isMultipart(request)) {
MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
Iterator<String> iter = multiRequest.getFileNames();
while (iter.hasNext()) {
MultipartFile file = multiRequest.getFile(iter.next());
if (file != null) {
workbook = ExcelUtil.readExcelByMultipartFile(file);
}
}
}
excelService.batchUpload(out,workbook);
return ResponseEntity.success(null,"success");
}catch (Exception e){
logger.error("batchUpload抛出了异常: ", e.getMessage()+e);
throw new Exception(e);
}
}
|
ExcelUtil工具类的作用是将接收到的Excel文件转换成Workbook，然后进行解析操作，上传的文件支持以xls和xlsx结尾。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
public static Workbook readExcelByMultipartFile(MultipartFile item) throws Exception {
try {
InputStream is = null;
String fileName = item.getOriginalFilename().substring(item.getOriginalFilename().lastIndexOf("\\")+1);
String suffix = fileName.substring(fileName.lastIndexOf("."));
is = item.getInputStream();
if (".xls".equals(suffix)) {
return new HSSFWorkbook(is);
} else if (".xlsx".equals(suffix)) {
return new XSSFWorkbook(is);
} else {
throw new RuntimeException("文件格式错误!");
}
} catch (IOException e) {
throw new Exception("文件上传错误!");
}
}
|
进入Service层对Workbook对象进行操作，创建数据库对应的Object，将每一列的数据set到对象中。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
@Override
public void batchUpload(Writer out, Workbook workbook) throws Exception{
try {
Sheet sheet = workbook.getSheetAt(0);
int totalRows=sheet.getLastRowNum()+1;
int totalCells=0;
if(totalRows>=1 && sheet.getRow(0) != null){
totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
}
List<Object> dataList = new ArrayList<>();
Object object = null;
for (int r = 1; r < totalRows; r++) {
Row row = sheet.getRow(r);
if (row == null) continue;
object = new Object();
for(int c = 0; c <=totalCells; c++){
Cell cell = row.getCell(c);
if (c == 0){
if(cell==null){
throw new RuntimeException("请填写序号");
}
cell.setCellType(Cell.CELL_TYPE_STRING);
String id = cell.getStringCellValue();
object.setId(id.replaceAll(" ",""));
}
}
dataList.add(object);
}
for (Object object : dataList) {
excelMapper.addList(object);
}
}catch (Exception e){
logger.error("batchUpload: " + e.getMessage() + e);
throw new Exception(e.getMessage());
}
}
|
Service层解析完成后，循环调用mapper的插入方法，一个一个将对象插入到数据库中。Excel批量导入介绍完了，下一次总结将数据库的数据导出为Excel。
