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