postman上传excel，java后台读取excel生成到指定位置进行备份，并且把excel中的数据添加到数据库
最近要做个前端网页上传excel，数据直接添加到数据库的功能。。在此写个读取excel的demo。
首先新建springboot的web项目 导包，读取excel可以用poi也可以用jxl，这里本文用的是poi
poi的 pom依赖如下：
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.17</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.17</version>
</dependency>
service层的实现类为：
@Service public class ExcelServiceImpl implements ExcelService { @Autowired private CorpTaxService corpTaxService; @Override public void read(HttpServletRequest request, HttpServletResponse response, MultipartFile file) { String fileName = ""; String filePath = "D://"; if (file != null && !file.isEmpty()){ fileName = file.getOriginalFilename(); }else { throw new CECException(501, "上传的文件为空，请重新选择。"); } BigDecimal tenThsoud = new BigDecimal(10000); ArrayList<CorpTax> list = new ArrayList<>(); //excel文件路径 // String excelPath2 = "F:\\工作簿2.xlsx"; try { FileInputStream fis = null; if (file != null && !file.isEmpty()) { //判断文件是否存在 String[] split = file.getOriginalFilename().split("\\."); //.是特殊字符，需要转义！！！！！ Workbook wb; //根据文件后缀（xls/xlsx）进行判断 if ( "xls".equals(split[1])){ //fis = new FileInputStream(excel); //文件流对象 wb = new HSSFWorkbook(file.getInputStream()); }else if ("xlsx".equals(split[1])){ wb = new XSSFWorkbook(file.getInputStream()); }else { System.out.println("文件类型错误!"); throw new CECException(501, "文件类型错误"); } //开始解析 Sheet sheet = wb.getSheetAt(0); //读取sheet 0 int firstRowIndex = sheet.getFirstRowNum()+1; //第一行是列名，所以不读 int lastRowIndex = sheet.getLastRowNum(); System.out.println("firstRowIndex: "+firstRowIndex); System.out.println("lastRowIndex: "+lastRowIndex); for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行 System.out.println("rIndex: " + rIndex); Row row = sheet.getRow(rIndex); if (row != null) { CorpTax corpTax = new CorpTax(); corpTax.setCorpName(row.getCell(0).getStringCellValue()); corpTax.setTaxData(new BigDecimal(row.getCell(1).getNumericCellValue()).divide(tenThsoud, 6, RoundingMode.HALF_UP)); corpTax.setFiscalRet(new BigDecimal(row.getCell(2).getNumericCellValue()).divide(tenThsoud, 6,RoundingMode.HALF_UP)); corpTax.setIndustry(row.getCell(3).toString()); corpTax.setCorpType(row.getCell(4).toString()); corpTax.setCorpBelong(row.getCell(5).toString()); corpTax.setCorpYear((int)row.getCell(6).getNumericCellValue()); corpTax.setCorpMonth((int)row.getCell(7).getNumericCellValue()); System.out.println(corpTax); list.add(corpTax);
// 往数据库库添加数据，这里就是一个王数据库的添加操作 int insert = corpTaxService.insert(corpTax); System.out.println(insert); int firstCellIndex = row.getFirstCellNum(); int lastCellIndex = row.getLastCellNum(); for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { //遍历列 Cell cell = row.getCell(cIndex); if (cell != null) { System.out.println(cell.toString()); } } } } wb.close(); } else { System.out.println("找不到指定的文件"); } } catch (Exception e) { e.printStackTrace(); } // 把excel生成到指定位置进行备份。 FileInputStream fileInputStream; try { fileInputStream = (FileInputStream) file.getInputStream(); BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream); FileOutputStream fileOutputStream = new FileOutputStream(filePath + fileName); BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(fileOutputStream); byte[] bytes = new byte[1024]; while (bufferedInputStream.read(bytes) != -1){ bufferedOutputStream.write(bytes); } bufferedOutputStream.flush(); bufferedOutputStream.close(); bufferedInputStream.close(); } catch (IOException e) { e.printStackTrace(); } for (CorpTax corpTax : list){ System.out.println(corpTax); } } public List<CorpTax> test(){ BigDecimal tenThsoud = new BigDecimal(10000); ArrayList<CorpTax> list = new ArrayList<>(); //excel文件路径 String excelPath2 = "F:\\工作簿2.xlsx"; try { FileInputStream fis = null; //String encoding = "GBK"; File excel = new File(excelPath2); if (excel.isFile() && excel.exists()) { //判断文件是否存在 String[] split = excel.getName().split("\\."); //.是特殊字符，需要转义！！！！！ Workbook wb; //根据文件后缀（xls/xlsx）进行判断 if ( "xls".equals(split[1])){ fis = new FileInputStream(excel); //文件流对象 wb = new HSSFWorkbook(fis); }else if ("xlsx".equals(split[1])){ wb = new XSSFWorkbook(excel); }else { System.out.println("文件类型错误!"); return null; } //开始解析 Sheet sheet = wb.getSheetAt(0); //读取sheet 0 int firstRowIndex = sheet.getFirstRowNum()+1; //第一行是列名，所以不读 int lastRowIndex = sheet.getLastRowNum(); System.out.println("firstRowIndex: "+firstRowIndex); System.out.println("lastRowIndex: "+lastRowIndex); for(int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { //遍历行 System.out.println("rIndex: " + rIndex); Row row = sheet.getRow(rIndex); if (row != null) { CorpTax corpTax = new CorpTax(); corpTax.setCorpName(row.getCell(0).getStringCellValue()); corpTax.setTaxData(new BigDecimal(row.getCell(1).getNumericCellValue()).divide(tenThsoud, 6, RoundingMode.HALF_UP)); corpTax.setFiscalRet(new BigDecimal(row.getCell(2).getNumericCellValue()).divide(tenThsoud, 6,RoundingMode.HALF_UP)); corpTax.setIndustry(row.getCell(3).toString()); corpTax.setCorpType(row.getCell(4).toString()); corpTax.setCorpBelong(row.getCell(5).toString()); corpTax.setCorpYear((int)row.getCell(6).getNumericCellValue()); corpTax.setCorpMonth((int)row.getCell(7).getNumericCellValue()); System.out.println(corpTax); list.add(corpTax); // int insert = corpTaxService.insert(corpTax); int firstCellIndex = row.getFirstCellNum(); int lastCellIndex = row.getLastCellNum(); for (int cIndex = firstCellIndex; cIndex < lastCellIndex; cIndex++) { //遍历列 Cell cell = row.getCell(cIndex); if (cell != null) { System.out.println(cell.toString()); } } } } wb.close(); // fis.close(); } else { System.out.println("找不到指定的文件"); } } catch (Exception e) { e.printStackTrace(); } return list; } }
上面这个实现类包含两个方法，上面的那个方法是，可以把excel生成到D盘进行备份，并且读取其中的数据，添加到数据库的（这里读取excel中的数据添加到数据库，是我根据自己的数据库格式，剪切了excel就只剩下了8列，而且每一列的格式都是手动处理的，并没有使用工具类进行处理，网友想要用工具类进行处理，可以参考https://www.cnblogs.com/zhanghaoliang/p/6526089.html）。
controller层就是一个简单的对service层的调用
@RequestMapping("excel")
@RestController
public class ExcelController {
@Autowired
private ExcelService excelService;
@RequestMapping(value = "upload",method = RequestMethod.POST)
public String upLoadExcel(HttpServletRequest request, HttpServletResponse response, @RequestParam(value = "file") MultipartFile file){
excelService.read(request, response, file);
return "ok";
}
}
参考文章：https://www.cnblogs.com/zhanghaoliang/p/6526089.html
https://blog.csdn.net/gxx_csdn/article/details/79085713
https://www.cnblogs.com/cx-code/p/9111336.html
下面说一下，过程中的坑;
1 org.springframework.beans.BeanInstantiationException 这是因为
HttpServletRequest request, HttpServletResponse response 写成了
HttpRequest request, HttpResponse response
