poi读取Excel内容

1. 工作中经常会用的Excel数据读取用来页面展示和数据存储
2. 以下为Springboot部分代码

package com.example.excel.controller;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.io.*;
import java.util.HashMap;
import java.util.Map;


@RestController
@RequestMapping("/excel")
public class ExcelToDBController {


    @PostMapping("/xxx")
    public Map xxx(@RequestParam String filePath,@RequestParam String newFileName) {
        Map map = new HashMap();
        InputStream is = null;
        try {
            is = new FileInputStream((filePath + "/"+newFileName));
            Workbook wb = null;
            if ((filePath + newFileName).endsWith(".xlsx")) {
                wb = new XSSFWorkbook(is);
            } else if ((filePath + newFileName).endsWith(".xls") || (filePath + newFileName).endsWith(".et")) {
                wb = new HSSFWorkbook(is);
            }
            int numberOfSheets = wb.getNumberOfSheets();
            File file = new File("F:\\xxxx.txt");
            for (int i=0;i<numberOfSheets;i++){
                //System.out.println(wb.getSheetAt(i).getSheetName());
                Sheet sheet = wb.getSheetAt(i);
                int rowSize = sheet.getLastRowNum() + 1;
                for (int j = 2; j < rowSize; j++) {
                    Row row = sheet.getRow(j);
                    int cellSize = row.getLastCellNum();
                    for (int m=0;m<cellSize;m++){
                        if (m!=0&&m!=5){
                            Cell cell = row.getCell(m);
                            String sex = "";
                            String payMent = "";
                            if (m==1||m==2||m==3||m==4){
                                sex = "1";//男性
                                if (m==1)
                                    payMent="Y10";
                                if (m==2)
                                    payMent="Y15";
                                if (m==3)
                                    payMent="Y20";
                                if (m==4)
                                    payMent="Y30";

                            }else if (m==6||m==7||m==8||m==9){
                                sex = "0";//女性
                                if (m==6)
                                    payMent="Y10";
                                if (m==7)
                                    payMent="Y15";
                                if (m==8)
                                    payMent="Y20";
                                if (m==9)
                                    payMent="Y30";
                            }
                            String aiClass = String.valueOf(i);
                            if (!"".equals(cell.toString())){
                                String sql = "INSERT INTO table_name(productCode,itemCode,itemDesc,age,sex,period,payMent,aiClass,rate,amount) " +
                                        "VALUES('xxx','xxx','xxx','"+(j-2)+"','"+sex+"','xxx','"+payMent+"','"+aiClass+"',"+cell.toString()+",'xxx');";
                                System.out.println(sql);
                                /*if (j%1000==0) {
                                    sql = sql + "\r\n" + "commit;";
                                }*/
                                toFile(file,sql);
                            }
                        }
                    }
                }
            }
            wb.close();
            is.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return map;
    }
    @PostMapping("/XXXX")
    public Map XXXX(@RequestParam String filePath,@RequestParam String newFileName) {
        Map map = new HashMap();
        InputStream is = null;
        try {
            is = new FileInputStream((filePath + "/"+newFileName));
            Workbook wb = null;
            if ((filePath + newFileName).endsWith(".xlsx")) {
                wb = new XSSFWorkbook(is);
            } else if ((filePath + newFileName).endsWith(".xls") || (filePath + newFileName).endsWith(".et")) {
                wb = new HSSFWorkbook(is);
            }
            int numberOfSheets = wb.getNumberOfSheets();
            //int numberOfSheets = 1;
            for (int i=0;i<numberOfSheets;i++){
                File file = new File("F:\\xxxxxx"+i+".txt");
                //System.out.println(wb.getSheetAt(i).getSheetName());
                Sheet sheet = wb.getSheetAt(i);
                int rowSize = sheet.getLastRowNum() + 1;
                for (int j = 1; j < rowSize; j++) {
                    Row row = sheet.getRow(j);
                    String sex = row.getCell(2).toString();
                    if ("男性".equals(sex)){
                        sex = "1";
                    }else if ("女性".equals(sex)){
                        sex = "0";
                    }
                    String age = row.getCell(3).toString();
                    age = age.substring(0,age.length()-2);
                    String payMent = "Y"+row.getCell(0).toString().substring(0,2);
                    String gpIndex = payMent+"/xxx"+"/"+sex+"/"+age;
                    int plyYear = new Double(row.getCell(4).getNumericCellValue()).intValue();
                    String sql = "INSERT INTO `table_name` (`riskCode`, `grade`,`gpIndex`,`payment`,`period`,`sex`,`age`,`plyYear`,`gpValue`) " +
                            "VALUES('xxx','"+String.valueOf(i)+"','"+gpIndex+"','"+payMent+"','xxxx','"+sex+"', '"+age+"',"+plyYear+",'"+row.getCell(5)+"');";
                    if (j%1000==0) {
                        sql = sql + "\r\n" + "commit;";
                    }
                    toFile(file,sql);
                    System.out.println(sql);
                }
            }
            wb.close();
            is.close();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return map;
    }
    public void toFile(File file,String data){
        BufferedWriter out = null;
        try {
            out = new BufferedWriter(new OutputStreamWriter(
                    new FileOutputStream(file, true)));
            out.write(data+"\r\n");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }
}

 

        <!--java poi-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.15</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.15</version>
        </dependency>

以上为直接输出sql到文件中，可以改成使用数据源直接存库