Excel读取数据存入mysql数据库好使的工具类

日常开发中经常遇到业务人员给出excle需要录入数据库的情况，有了这个方便的改改就可以了。

1.ExcelUtils

package xxx.excel.test;


import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author victorydeng
 * @Classname ExcelUtils
 * @Description TODO
 * @Date 2020/6/2 11:04
 * @Created by victorydeng
 */
public class ExcelUtil {
    public static List<List<String>> excelToList(InputStream inputStream) {
        List<List<String>> dataLst = new ArrayList<>();
        try {
            Workbook workbook = null;
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            /** 得到第一个sheet */
            Sheet sheet = workbook.getSheetAt(0);
            /** 得到Excel的行数 */
            int totalRows = sheet.getPhysicalNumberOfRows();
            /** 得到Excel的列数 */
            int totalCells = 0;
            if (totalRows >= 1 && sheet.getRow(0) != null) {
                totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
            }
            /** 循环Excel的行 */
            for (int r = 1; r < totalRows; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }
                List<String> rowLst = new ArrayList<String>();
                /** 循环Excel的列 */
                for (int c = 0; c < totalCells; c++) {
                    Cell cell = row.getCell(c);
                    String cellValue = "";
                    if (null != cell) {
                        HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
                        cellValue = hSSFDataFormatter.formatCellValue(cell);
                        // 以下是判断数据的类型
                        switch (cell.getCellType()) {
                            case Cell.CELL_TYPE_NUMERIC: // 数字
                                short format = cell.getCellStyle().getDataFormat();
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    SimpleDateFormat sdf = null;
                                    sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                                    double value = cell.getNumericCellValue();
                                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                                    cellValue = sdf.format(date);
                                } else {
                                    BigDecimal bd = new BigDecimal(cell.getNumericCellValue());
                                    cellValue = bd.toPlainString(); // 数值 这种用BigDecimal包装再获取plainString，可以防止获取到科学计数值
                                }
                                break;
                            case Cell.CELL_TYPE_STRING: // 字符串
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_BOOLEAN: // Boolean
                                cellValue = cell.getBooleanCellValue() + "";
                                break;
                            case Cell.CELL_TYPE_FORMULA: // 公式
                                cellValue = cell.getCellFormula() + "";
                                break;
                            case Cell.CELL_TYPE_BLANK: // 空值
                                cellValue = "";
                                break;
                            case Cell.CELL_TYPE_ERROR: // 故障
                                cellValue = "非法字符";
                                break;
                            default:
                                cellValue = cell.getStringCellValue();
                                break;
                        }
                    }
                    rowLst.add(cellValue);
                }
                /** 保存第r行的第c列 */
                dataLst.add(rowLst);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return dataLst;
    }

    @SuppressWarnings("resource")
    public static void writer(String path, String fileName,String fileType,List<List<String>> list,String titleRow[]) throws Exception {
        Workbook wb = null;
        String excelPath = path+File.separator+fileName+"."+fileType;
        File file = new File(excelPath);
        Sheet sheet =null;
        //创建工作文档对象
        if (!file.exists()) {
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook();

            } else if(fileType.equals("xlsx")) {

                wb = new XSSFWorkbook();
            } else {
                throw new Exception("文件格式不正确");
            }
            //创建sheet对象
            sheet = (Sheet) wb.createSheet("sheet1");
            OutputStream outputStream = new FileOutputStream(excelPath);
            wb.write(outputStream);
            outputStream.flush();
            outputStream.close();

        } else {
            if (fileType.equals("xls")) {
                wb = new HSSFWorkbook();

            } else if(fileType.equals("xlsx")) {
                wb = new XSSFWorkbook();

            } else {
                throw new Exception("文件格式不正确");
            }
        }
        //创建sheet对象
        if (sheet==null) {
            sheet = (Sheet) wb.createSheet("sheet1");
        }

        //添加表头
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        row.setHeight((short) 540);
        cell.setCellValue("数据统计清单");    //创建第一行

        CellStyle style = wb.createCellStyle(); // 样式对象
        // 设置单元格的背景颜色为淡蓝色
        style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);

        style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直
        style.setAlignment(CellStyle.ALIGN_CENTER);// 水平
        style.setWrapText(true);// 指定当单元格内容显示不下时自动换行

        cell.setCellStyle(style); // 样式，居中

        Font font = wb.createFont();
        font.setBoldweight(Font.BOLDWEIGHT_BOLD);
        font.setFontName("宋体");
        font.setFontHeight((short) 280);
        style.setFont(font);
        // 单元格合并
        // 四个参数分别是：起始行，起始列，结束行，结束列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3));
        sheet.autoSizeColumn(5200);

        row = sheet.createRow(1);    //创建第二行
        for(int i = 0;i < titleRow.length;i++){
            cell = row.createCell(i);
            cell.setCellValue(titleRow[i]);
            cell.setCellStyle(style); // 样式，居中
          //  sheet.setColumnWidth(i, 20 * 256);
        }
        row.setHeight((short) 540);

        //循环写入行数据
        for (int i = 0; i < list.size(); i++) {
            row = (Row) sheet.createRow(i+2);
            row.setHeight((short) 500);
            row.createCell(0).setCellValue(( list.get(i)).get(0));
            row.createCell(1).setCellValue(( list.get(i)).get(1));
            row.createCell(2).setCellValue(( list.get(i)).get(2));
            row.createCell(3).setCellValue(( list.get(i)).get(3));
        }

        //创建文件流
        OutputStream stream = new FileOutputStream(excelPath);
        //写入数据
        wb.write(stream);
        //关闭文件流
        stream.close();
    }

    public static String getString(List<String> list) {
        StringBuffer sb = new StringBuffer();
        list.forEach(e->{
            sb.append("," + e);
        });
        return  sb.toString();
    }

    public static void main(String[] args) throws Exception {
        ExcelUtil excelUtil = new ExcelUtil();
//        List<List<String>> lists = excelUtil.excelToList(new FileInputStream(new File("E:\temp\test.xlsx")));
//        //读取案由的
//        for (int i = 0; i < lists.size(); i++) {
//            System.out.println(i+"--行---" + excelUtil.getString(lists.get(i)));
//            if(i!=0) {
//                JDBCHelper.saveCase2(lists.get(i).get(0), lists.get(i).get(1), lists.get(i).get(2), lists.get(i).get(3), lists.get(i).get(4));
//            }
//        }

        List<List<String>> listccc = excelUtil.excelToList(new FileInputStream(new File("D:\ccc.xlsx")));
        List<List<String>> listddd = excelUtil.excelToList(new FileInputStream(new File("D:\ddd.xlsx")));
        Map<String,Integer> hashMap = new HashMap<>();
        List<List<String>> ccc = new ArrayList<>();

        listddd.forEach(e->{
            hashMap.put(e.get(0),Integer.parseInt(e.get(1)));
        });
        listccc.forEach(e->{
            Integer temp = 0;
            if(hashMap.containsKey(e.get(0))) {
                temp = hashMap.get(e.get(0));
            }
            List<String> temp2 = new ArrayList<>();
            temp2.add(0,e.get(0));
            temp2.add(1,e.get(1));
            temp2.add(2,temp.toString());
            Integer sum = Integer.parseInt(e.get(1)) +temp;
            float size = (float) Integer.parseInt(e.get(1)) / sum;
            DecimalFormat df = new DecimalFormat("0.00%");
            String filesize = df.format(size);//返回的是String类型的
            temp2.add(3,  filesize );
            ccc.add(temp2);

        });
        String dir = "D:";

        String title[] = {"日期","人工接通","排队离开","接通率"};

        writer(dir, "test", "xlsx",ccc,title);






    }
}

2.JDBCHelper类 

package xxx.excel.test;

/**
 * @Classname JDBCHelper
 * @Description TODO
 * @Date 2019/8/22 16:51
 * @Created by victorydeng
 */

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

//得到con
public class JDBCHelper {

  //开启con
  public static Connection getCon() {
    try {
      Class.forName("com.mysql.jdbc.Driver");
      //localhost--test:
      Connection con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/dbgirl?useUnicode=true&characterEncoding=UTF-8", "root", "123456");
      if (con != null) {
        return con;
      }
    } catch (Exception e) {
      e.printStackTrace();
    }
    return null;
  }

  //关闭con
  public static void CloseCon(Connection con) {
    if (con != null) {
      try {
        con.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }

  public static List<User> queryCaseList() {
    PreparedStatement pst = null;
    Connection con=JDBCHelper.getCon();
    String sql="SELECT * FROM billconsults ";
    List<User> list1=new ArrayList<>() ;
    try {
      pst = con.prepareStatement(sql);
      java.sql.ResultSet sqlRst =pst.executeQuery();
      while (sqlRst.next()){
        User user = new User();
        user.setId(sqlRst.getInt(1));
        user.setOther_num(sqlRst.getString(2));
        user.setName_consult(sqlRst.getString(3));
        user.setWay_consult(sqlRst.getString(4));

        user.setPhone_consult(sqlRst.getString(5));
        user.setPaperwork_consult(sqlRst.getString(6));

        user.setPaperwork_num_consult(sqlRst.getString(7));

        user.setBusiness_type(sqlRst.getString(8));
        user.setTime_consult(sqlRst.getString(9));

        user.setBegin_time_consult(sqlRst.getString(10));
        user.setEnd_time_consult(sqlRst.getString(11));
        user.setPerson_type(sqlRst.getString(12));

        user.setIndustry_type(sqlRst.getString(13));
        user.setContent_type(sqlRst.getString(14));

        user.setAddress_accident(sqlRst.getString(15));

        user.setReply(sqlRst.getString(16));

        user.setName_reply(sqlRst.getString(17));
        user.setPosition_reply(sqlRst.getString(18));

        user.setWay_source(sqlRst.getString(19));
        user.setResult(sqlRst.getString(20));

        user.setStatus(sqlRst.getString(21));



        list1.add(user);
      }
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(con!=null){
        JDBCHelper.CloseCon(con);
      }
    }
    return  list1;
  }

  public static void saveCase2(String s1,String s2, String s3, String s4, String s5) {
    PreparedStatement pst = null;
    Connection con=JDBCHelper.getCon();
    String sql="insert into anyou_type(id,aybh,aymc,fdm,lb,ver) values (0,?,?,? ?,?)";
    try {
      pst = con.prepareStatement(sql);
      pst.setString(1 , s1);
      pst.setString(2 , s2);
      pst.setString(3 , s3);
      pst.setString(4 , s4);
      pst.setString(5 , s5);
      pst.execute();
    } catch (Exception e) {
      e.printStackTrace();
    }finally{
      if(con!=null){
        JDBCHelper.CloseCon(con);
      }
    }
  }

    public static void saveCase3(String s1,String s2, String s3, String s4, String s5,String s6,String s7) {
        PreparedStatement pst = null;
        Connection con=JDBCHelper.getCon();
        String sql=" insert into anyou_zhengyi(id,zybh,aybh,pxh,zywt,stcx,gpgd,yhdm) values (0,?,?,? ,?,?,?,?);";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1 , s1);
            pst.setString(2 , s2);
            pst.setString(3 , s3);
            pst.setString(4 , s4);
            pst.setString(5 , s5);
            pst.setString(6 , s6);
            pst.setString(7 , s7);
            pst.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(con!=null){
                JDBCHelper.CloseCon(con);
            }
        }
    }

    public static void saveCase4(String s1,String s2, String s3, String s4, String s5) {
        PreparedStatement pst = null;
        Connection con=JDBCHelper.getCon();
        String sql="insert into anyou_type(id,aybh,aymc,fdm,lb,ver) values (0,?,?,? ?,?)";
        try {
            pst = con.prepareStatement(sql);
            pst.setString(1 , s1);
            pst.setString(2 , s2);
            pst.setString(3 , s3);
            pst.setString(4 , s4);
            pst.setString(5 , s5);
            pst.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(con!=null){
                JDBCHelper.CloseCon(con);
            }
        }
    }
}

实体类User：

@Data
public class User {


    private Integer id;

    private String other_num;

    private String name_consult;

    private String way_consult;

    private String phone_consult;

    private String paperwork_consult;

    private String paperwork_num_consult;
    private String business_type;
    private String time_consult;
    private String begin_time_consult;
    private String end_time_consult;
    private String person_type;
    private String industry_type;
    private String content_type;
    private String address_accident;
    private String reply;
    private String name_reply;
    private String position_reply;
    private String way_source;
    private String result;
    private String status;


}

pom.xml需要添加mysql驱动和poi依赖。

最后

以上就是要减肥向日葵为你收集整理的Excel读取数据存入mysql数据库好使的工具类的全部内容，希望文章能够帮你解决Excel读取数据存入mysql数据库好使的工具类所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。