Poi文件读取exel内容，存入数据库

首先在pom文件添加汉字转拼音。

<dependency>
    <groupId>com.belerweb</groupId>
    <artifactId>pinyin4j</artifactId>
    <version>2.5.0</version>
</dependency>

 

package com.sec.jyfrgl.utils;

import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import net.sourceforge.pinyin4j.PinyinHelper;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

public class PoiUtils {

    /**
     *
     * @param filePath 文件路径
     * @return
     */
    public static List<Map<String,String>> getExel(String filePath){
        Workbook wb =null;
        Sheet sheet = null;
        Row row = null;
        Row hang = null;
        List<Map<String,String>> list = null;
        String cellData = null;
        String hq = null;
        wb = readExcel(filePath);
        if(wb != null){
            //用来存放表中数据
            list = new ArrayList<Map<String,String>>();
            //获取第一个sheet
            int shu = wb.getActiveSheetIndex();
            sheet = wb.getSheetAt(0);

          //  System.out.println(shu);
            //获取最大行数
            int rownum = sheet.getPhysicalNumberOfRows();
        //    System.out.println(rownum);
            //获取第一行
            row = sheet.getRow(0);
            //获取最大列数
            int colnum = row.getPhysicalNumberOfCells();
            System.out.println(colnum);
           // String[] columns1 = new String[colnum];
           // String[] columns2 = new String[colnum];
            ArrayList<String> list4 = new ArrayList<>();  //总计和
            ArrayList<String> list2 = new ArrayList<>();   //合并单元格的集合
            ArrayList<String> list3 = new ArrayList<>();
            ArrayList<String> list5 = new ArrayList<>();
            String al=(String)getCellFormatValue(row.getCell(0));

            if(al.contains("20")){
                int wz = al.indexOf("20");
                hq=al.substring(wz,wz+4);
            }else if(al.contains("二0")){
                int wz = al.indexOf("二0");
                hq=al.substring(wz,wz+4);
            }
            hang = sheet.getRow(3); //从第三行开始获取
            int lie = hang.getPhysicalNumberOfCells();
            for (int i = 0; i<rownum; i++) {
                Map<String,String> map = new LinkedHashMap<String,String>();
                row = sheet.getRow(i);
                if(row !=null){


                    for (int j=0;j<lie;j++){

                        cellData = (String) getCellFormatValue(row.getCell(j));
                        if("序号".equals(cellData.replace(" ","").replace("\n",""))){
                           int sheetMergeCount = sheet.getNumMergedRegions();
                            Row row1 = sheet.getRow(i);

                            for (int k = 0; k <lie ; k++) {
                                String cellData1 = (String) getCellFormatValue(row1.getCell(k));

                                String aa = getPinYinHeadChar(cellData1);
                                String quzhi = aa.replace(" ", "").replace("\n", "");

                                if(quzhi.equals("")){
                                    quzhi="a"+k;
                                }
                                list4.add(quzhi);
                            }

                        for (int k = 0; k < sheetMergeCount; k++) {
                        CellRangeAddress range = sheet.getMergedRegion(k);
                        int firstColumn = range.getFirstColumn();
                        int lastColumn = range.getLastColumn();
                        int firstRow = range.getFirstRow();
                        int lastRow = range.getLastRow();
                        if(i >= firstRow && i <= lastRow){
                        if(1 >= firstColumn && 1 <= lastColumn){

                            Row row2 = sheet.getRow(lastRow);
                            for (int l = 0; l <colnum ; l++) {
                                String cellData1 = (String) getCellFormatValue(row2.getCell(l));
                                String aa = getPinYinHeadChar(cellData1);
                                list2.add(aa.replace(" ","").replace("\n",""));
                            }

                            String str = ""; //索引
                            String str1 = ""; //参数
                            for (int l = 0; l <list2.size() ; l++) {
                                String cs = list2.get(l);
                                if(!"".equals(cs.trim())){
                                    str+=l+",";
                                    str1+=cs+",";
                                }
                            }
                            String[] strs = str.split(",");
                            String[] str1s = str1.split(",");
                            for (int l = 0; l <strs.length ; l++) {
                                    list5.add(list4.get(Integer.parseInt(strs[l])));
                            }
                            list4.removeAll(list5);
                            for (int l = 0; l < strs.length; l++) {
                                list4.add(Integer.parseInt(strs[l]),str1s[l]);
                            }
                        }
                        }
                        }
                        }

                        try{
                            map.put(list4.get(j), cellData);
                        }catch (Exception e){
                           // e.printStackTrace();
                        }

                    }
                }else{
                    break;
                }
                list.add(map);
            }
        }
        for (int i = 0; i <list.size() ; i++) {
            Map<String, String> map = list.get(i);
            map.put("cbrq",hq);
        }

        return list;

    }


    //读取excel
    public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));

        InputStream is = null;
        try {

            is = new FileInputStream(filePath);

            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else if(".csv".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else{
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
           e.printStackTrace();
        }
        return wb;
    }
    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC:{
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    cellValue =cell.getStringCellValue();
                    break;
                }
                case Cell.CELL_TYPE_FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else{
                        //数字
                        cellValue =cell.getStringCellValue();
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
       // System.out.println(cellValue);
        return cellValue;
    }

    public static void main(String[] args) {
        String filePath = "C:\\Users\\Administrator\\Desktop\\各地市院汇总表.xls";

        List<Map<String,String>> list = getExel(filePath); //拿到所有list
      //  System.out.println(list);


        List<?> realList = getRealList(list, Map.class);
        //System.out.println(realList);
        realList.remove(0);
        for (Object map4:realList) {
            System.out.println(map4);
        }
    }

    /**
     * 获得真实list
      */
    public static List<?> getRealList(List<Map<String,String>> list, java.lang.Class<?> t ){
        //遍历解析出来的list
        Iterator<Map<String, String>> iter = list.iterator();

        while(iter.hasNext()){
            Map<String, String> b = iter.next();

            if(b.containsKey(null) ||  "".equals(b.get("xm")))
            {
                iter.remove();
            }


        }




       // list.remove(0);
        List list2 = new ArrayList<>();
        for (Map<String,String> map : list) {

            //1.检测数据的有效性
            StringBuffer sb = new StringBuffer();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if (!StringUtils.isEmpty(entry.getValue())) {
                    sb.append(entry.getValue());
                }
            }
            if (sb.length() < 10) {
                continue;
            }

            list2.add(JSON.parseObject(JSON.toJSONString(map),t));
        }
        return list2;
    }



    /**
             * 提取每个汉字的首字母
             * @param str
             * @return
             */
            public static String getPinYinHeadChar(String str){
                String convert = "";
                for (int i = 0; i < str.length(); i++) {
                    char word = str.charAt(i);
                    //提取汉字的首字母
                    String[] pinyinArray = PinyinHelper.toHanyuPinyinStringArray(word);
                    if (pinyinArray != null){
                        convert += pinyinArray[0].charAt(0);
                    }else{
                        convert += word;
                    }
                }
                return convert.toLowerCase();
            }


    public static int getMergedRegionIndex(Sheet sheet, int row, int column) {
        int sheetMergeCount = sheet.getNumMergedRegions();

        for (int i = 0; i < sheetMergeCount; i++) {
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();

            if (row >= firstRow && row <= lastRow) {
                if (column >= firstColumn && column <= lastColumn) {
                    return i;
                }
            }
        }

        return 0;
    }



}