需求，前端用户上传Excel文件，后台读取解析文件，做一系列处理后插入数据库。
1.前台代码：
<!DOCTYPE html> <html lang="en"> <head> <meta charset="UTF-8"> <title>导入</title> </head> <style> .fileinput-button input { position: absolute; right: 0px; top: 0px; } </style> <body> <p>请上传.xlsx或.xls格式Excel文件</p> <hr/> <form id="importer" action="/api/improtExcel" method="post" enctype="multipart/form-data"> <span class=""> <span>上传</span> <input type="file" name="excel"> </span> <input class="fileinput-button" type="submit" name="Submit" value="提交"/> </form> </body> </html>
2.controller
import org.apache.poi.openxml4j.exceptions.InvalidFormatException; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.web.bind.annotation.PostMapping; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; import org.springframework.web.multipart.MultipartFile; import org.springframework.web.multipart.MultipartHttpServletRequest; import javax.servlet.http.HttpServletRequest; import java.io.IOException; @RestController @RequestMapping(value = "/excel", produces = "text/plain;charset=UTF-8") public class ExcelController { @PostMapping("/improt") public String improtExcel(HttpServletRequest request) throws IOException, InvalidFormatException { MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request; MultipartFile file = multipartRequest.getFile("excel"); xxxx(file);//执行业务代码 } }
3.读取Excel
/*------------------------------------------------------ExcelUtil------------------------------------------------------------------------*/
//.xls和.xlsx格式的读取方式不同，需要做一个判断然后返回WorkBook对象
public static Workbook readExcel(MultipartFile file) throws IOException { Workbook workbook = null; if (null == file) { throw new RuntimeException("File can not be null！"); } //判断excel类型 .xls 或则 .xlsx格式 String fileName = file.getOriginalFilename(); String excelFormat = fileName.substring(fileName.lastIndexOf(".")); if (".xls".equals(excelFormat)) { workbook = new HSSFWorkbook(file.getInputStream()); } else if (".xlsx".equals(excelFormat)) { workbook = new XSSFWorkbook(file.getInputStream()); } else { throw new RuntimeException("File format can only be .xls or .xlsx"); } return workbook; }
/*------------------------------------------------------业务代码------------------------------------------------------------------------*/
//读sheet、row
public String importExcel(MultipartFile file) throws IOException {
Workbook workbook = ExcelUtil.readExcel(file);
Sheet sheet = workbook.getSheetAt(0);//读取第一个sheet
Row header = sheet.getRow(2);//读取头行数据
companyId = header.getCell(0).toString();
companeName = header.getCell(1).toString();
List<InsertInVo> insertInVos = new ArrayList<>();
for (int i = 4; i <= sheet.getLastRowNum(); i++) {
Row row = sheet.getRow(i);
String departmentNo = row.getCell(0).toString();//不应该这样写，容易出现nullpointerException，应该先判断空
String departmentName = row.getCell(1).toString();
String projectNo = row.getCell(2).toString();
String projectName = row.getCell(3).toString();
String bookNo = row.getCell(4).toString();
String bookName = row.getCell(5).toString();
......}