java的excel解析入库_Excel文件上传并解析入库——工具类（未处理获取合并单元格）的值）...

Excel文件上传并解析的工具类ExcelUploadUtil.java

package com.example.demo.controller;

import lombok.extern.slf4j.Slf4j;

import org.apache.commons.lang.StringUtils;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;

import org.apache.poi.ss.usermodel.*;

import org.springframework.stereotype.Component;

import org.springframework.web.multipart.MultipartFile;

import java.io.File;

import java.io.FileInputStream;

import java.io.IOException;

import java.io.InputStream;

import java.text.DecimalFormat;

import java.util.ArrayList;

import java.util.HashMap;

import java.util.List;

import java.util.Map;

/**

* @Description: Excel文件解析工具类

* @Author: zhaowang

* @Date: 2018/9/25 11:16

* @Version: 1.0

*/

@Slf4j

@Component

public class ExcelUploadUtil {

/**

*

* @param file 上传的文件

* @param path 文件保存路径

* @return

*/

// TODO 获取合并单元格的值

public Map>> upload(MultipartFile file,String path) {

//创建读取excel的类(区分excel2003和2007文件)

Workbook workbook = createWorkBook(file,path);

//excel每一行看做一个List,作为value;sheet页数作为key

Map>> map = new HashMap<>();

// 得到sheet页

for(int i = 0;i

Sheet sheet = workbook.getSheetAt(i);

// 得到Excel的行数

int totalRows = sheet.getPhysicalNumberOfRows();

// 得到Excel的列数(前提是有行数)

int totalCells = 0;

if (totalRows > 1 && sheet.getRow(0) != null) {

totalCells = sheet.getRow(0).getPhysicalNumberOfCells();

}

// 循环Excel行数

List> list = new ArrayList<>();

for (int r = 1; r < totalRows; r++) {

Row row = sheet.getRow(r);

if (row == null) {

continue;

}

// 循环Excel的列

if (row != null) {

List super Object> valueList = new ArrayList<>();

for (int c = 0; c < totalCells; c++) {

Cell cell = row.getCell(c);

DecimalFormat df = new DecimalFormat("0");

try {

if (null != cell) {

if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {

String value = df.format(cell.getNumericCellValue());

valueList.add(value);

} else if (cell.getCellType() == Cell.CELL_TYPE_STRING) {

if (!StringUtils.isEmpty(cell.getStringCellValue())) {

valueList.add(cell.getStringCellValue());

}

} else if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {

valueList.add("");

} else if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {

valueList.add(cell.getBooleanCellValue());

} else if(cell.getCellType() == Cell.CELL_TYPE_ERROR) {

valueList.add("非法字符");

} else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {

valueList.add(cell.getCellFormula());

};

}

} catch (Exception e) {

log.error("\n==========解析Excel单元格异常==========", e);

}

}

list.add(valueList);

}

}

map.put(i, list);

}

return map;

}

public Workbook createWorkBook(MultipartFile file, String path) {

multipartToFile(file,path);

File f = createNewFile(file,path);

Workbook workbook = null;

try {

InputStream is = new FileInputStream(f);

workbook = WorkbookFactory.create(is);

is.close();

} catch (InvalidFormatException | IOException e) {

log.error("\n==========文件流转换为Workbook对象异常============", e);

}

return workbook;

}

public void multipartToFile(MultipartFile multfile, String path) {

File file = createNewFile(multfile,path);

try {

multfile.transferTo(file);

} catch (IOException e) {

log.error("\n上传的文件保存失败");

}

}

public File createNewFile(MultipartFile multfile,String path) {

String fileName = multfile.getOriginalFilename();

File file = new File(path + fileName);

File parentFile = file.getParentFile();

if(!parentFile.exists()) parentFile.mkdirs();

try {

file.createNewFile();

} catch (IOException e) {

log.error("\n新文件创建失败");

}

return file;

}

}

image.gif

利用工具类将解析的结果保存入库示例

ExcelUploadController.java

package com.example.demo.controller;

import com.example.demo.entity.Test;

import com.example.demo.service.TestService;

import lombok.extern.slf4j.Slf4j;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.transaction.annotation.Propagation;

import org.springframework.transaction.annotation.Transactional;

import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RequestMethod;

import org.springframework.web.bind.annotation.RequestParam;

import org.springframework.web.bind.annotation.RestController;

import org.springframework.web.multipart.MultipartFile;

import java.util.ArrayList;

import java.util.List;

import java.util.Map;

/**

* @Description:

* @Author: zhaowang

* @Date: 2018/9/29 22:33

* @Version: 1.0

*/

@RestController

@Slf4j

@RequestMapping("demo/excel")

public class ExcelUploadController {

@Autowired

private TestService testService;

@Autowired

private ExcelUploadUtil excelUploadUtil;

@Transactional(value = "transactionManager",propagation = Propagation.REQUIRED)

@RequestMapping(value = "/upload", method = {RequestMethod.POST})

public void upload(@RequestParam("file") MultipartFile file) {

long startTime = System.currentTimeMillis();

String path = "D:/upload";

Map>> map = excelUploadUtil.upload(file,path);

//将解析出的数据批量插入或更新到数据库中

List allList = new ArrayList<>();

for(Integer i:map.keySet()) {

List> valueList = map.get(i);

for(List super Object> subList:valueList) {

Test test = new Test();

for(Object o:subList) {

int length = subList.size();

if(length>0) {

for (int k = 0; k < length; k++) {

if (k == 0) {

test.setFirst_menu((String) subList.get(0));

continue;

}else if (k == 1 ) {

if(k < length) {

Integer report_id = subList.get(1) == null ? null : Integer.valueOf((String) subList.get(1));

test.setReport_id(report_id);

} else {

test.setReport_id(null);

}

continue;

} else if (k == 2 ) {

if(k < length) test.setReport_name((String) subList.get(2));

else test.setReport_name(null);

continue;

} else if (k == 3 ) {

if(k < length) test.setFund_column((String) subList.get(3));

else test.setFund_column(null);

continue;

} else if (k == 4) {

if(k < length) test.setColum((String) subList.get(4));

else test.setColum(null);

continue;

}

}

}

}

allList.add(test);

}

}

testService.saveAll(allList);

long endTime = System.currentTimeMillis();

log.info("\n解析Excel文件并入库的总时间为:{}",(endTime-startTime));

}

}

image.gif

解析Excel具体做法

将Execl每一行的记录看做一个List super Object> ,一个sheet页的所有list则为List>。将每个sheet页的内容保存到Map中，key为sheet页码，vaue为List>。解析完Excel，获得并返回HashMap。然后根据model，遍历map的value。逐一set给实体属性，获得所有对象，最后批量插入model。

注意事项

1.如果项目基于SpringBoot，内置MultipartFile的bean,因此不需要额外配置。

2.建议添加事务回滚注解，防止因执行方法抛出异常导致产生脏数据。