java代码实现Excel的导入以及批量数据入库操作

工作当中经常会遇到poi的操作以及批量数据导入库中，使用PLSQL或者Navicat这两种工具导入数据量大的时候也很耗费时间 以下是我个人项目当中总结的POI操作和批量数据入库

导入POI的坐标或者下载jar包导入项目

		<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>

首先一个简单的前台页面用来上传文件

<form id="imageForm" name="imageForm" method="post" enctype="multipart/form-data" action="/njTest/inputExcelAll.jhtml">
    <h3>选择一个文件:</h3>
    <input id="file" type="file" name="file" accept="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, application/vnd.ms-excel"/>
    <br/>
    <input type="submit" value="上传" />
    <span style="color: red">增量上传数据</span>
</form>
<c:if test="${res != null}">
    成功导入：${res}
    <br>
</c:if>

后端代码实现

/**
     * Excel数据批量导入库中
     * @param file
     * @param request
     * @return
     */
    @RequestMapping("/njTest/inputExcelAll.jhtml")
    public String inputExcelAll(@RequestParam("file") MultipartFile file, HttpServletRequest request,ModelMap modelMap) throws Exception {
        String originalFilename = file.getOriginalFilename();// 原文件名字
        logger.info("文件名：" + originalFilename);
        List<List<Object>> dataList = ImportExcelUtils.importExcel(file);
        try {
            String url = "jdbc:oracle:thin:@ip:1521:表名";
            String user = "";			//数据库连接用户名
            String password = "";		//数据库链接密码
            StringBuffer sql = new StringBuffer();
            sql.append("insert into njtest(id,name,age,number) values (?,?,?,?)");
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = (Connection) DriverManager.getConnection(url,user,password);
            //关闭事务自动提交;首先批量操作的时候需要把事务自动提交关闭  要不然会默认更新一条提交一次
            con.setAutoCommit(false);
            Long startTime = System.currentTimeMillis();
            PreparedStatement pst = (PreparedStatement) con.prepareStatement(sql.toString());
            for (int i = 1; i < dataList.size(); i++) {
                //防止把excel当中设置过单元格格式为文本添加进来
                if (StringUtils.isNotEmpty(dataList.get(i).get(0).toString())){
                    pst.setString(1, dataList.get(i).get(0).toString());
                    pst.setString(2, dataList.get(i).get(1).toString());
                    pst.setString(3, dataList.get(i).get(2).toString());
                    pst.setString(4, dataList.get(i).get(3).toString());
                    // 把一个SQL命令加入命令列表
                    pst.addBatch();
                }
            }
            // 执行批量更新
            pst.executeBatch();
            // 语句执行完毕，提交本事务
            con.commit();
            Long endTime = System.currentTimeMillis();
            //查看更新数据所需要的时间
            System.out.println("用时：" + (endTime - startTime));
            pst.close();
            con.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        modelMap.addAttribute("res",dataList.size()-1);
        return "forward:/software/importExcelinit.jhtml";
    }

操作Excel的工具类

package com.tmg.utils;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.List;

/**
 * @Description excel 导入数据
 */
public class ImportExcelUtils {
    public static List<List<Object>> importExcel(MultipartFile file) throws IOException {
        String fileName = file.getOriginalFilename();
        String extension = fileName.lastIndexOf(".") == -1 ? "" : fileName.substring(fileName.lastIndexOf(".") + 1);
        if ("xls".equals(extension)) {
            return read2003Excel(file.getInputStream());
        } else if ("xlsx".equals(extension)) {
            return read2007Excel(file.getInputStream());
        } else {
            throw new IOException("不支持的文件类型");
        }
    }

    /**
     * 读取 office 2003 excel
     *
     * @throws IOException
     * @throws FileNotFoundException
     */
    private static List<List<Object>> read2003Excel(InputStream is) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        HSSFWorkbook hwb = new HSSFWorkbook(is);
        //读取Excel当中的第一张表
        HSSFSheet sheet = hwb.getSheetAt(0);
        Object value = null;
        HSSFRow row = null;
        HSSFCell cell = null;
        for (int i = sheet.getFirstRowNum(); i <= sheet.getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    cell.setCellValue("1");
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        //  System.out.println(i+"行"+j+" 列 is String type");
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = nf.format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }
    /**
     * 读取Office 2007 excel
     */
    private static List<List<Object>> read2007Excel(InputStream is) throws IOException {
        List<List<Object>> list = new LinkedList<List<Object>>();
        // 构造 XSSFWorkbook 对象，strPath 传入文件路径
        XSSFWorkbook xwb = new XSSFWorkbook(is);
        //int numberOfSheets = xwb.getNumberOfSheets(); 获取excel有多少个页签从1开始获取
        //Sheet sheetAt = xwb.getSheetAt(numberOfSheets);
		//int rowNum = sheetAt.getLastRowNum();
		//Row row1 = sheetAt.getRow(rowNum);
		//short cellNum = row1.getLastCellNum();
		//获取第几行第几列数据
		//ell cell1 = sheetAt.getRow(rowNum).getCell(cellNum);
        // 读取第一章表格内容
        XSSFSheet sheet = xwb.getSheetAt(0);
        Object value = null;
        XSSFRow row = null;
        XSSFCell cell = null;
        for (int i = sheet.getFirstRowNum(); i <= sheet
                .getPhysicalNumberOfRows(); i++) {
            row = sheet.getRow(i);
            if (row == null) {
                continue;
            }
            List<Object> linked = new LinkedList<Object>();
            short lastCellNum = row.getLastCellNum();
            for (int j = row.getFirstCellNum(); j <= lastCellNum; j++) {
                cell = row.getCell(j);
                if (cell == null) {
                    cell = row.createCell(j);
                    cell.setCellValue("");
                }
                DecimalFormat df = new DecimalFormat("0");// 格式化 number String 字符
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");// 格式化日期字符串
                SimpleDateFormat sdf1 = new SimpleDateFormat("yyyy-MM-ddHH:mm:ss");// 格式化日期字符串
                DecimalFormat nf = new DecimalFormat("0");// 格式化数字
                switch (cell.getCellType()) {
                    case XSSFCell.CELL_TYPE_STRING:
                        value = cell.getStringCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_NUMERIC:
                        if ("@".equals(cell.getCellStyle().getDataFormatString())) {
                            value = df.format(cell.getNumericCellValue());
                        } else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
                            value = nf.format(cell.getNumericCellValue());
                        } else {
                            value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                        }
                        break;
                    case XSSFCell.CELL_TYPE_BOOLEAN:
                        value = cell.getBooleanCellValue();
                        break;
                    case XSSFCell.CELL_TYPE_BLANK:
                        value = "";
                        break;
                    default:
                        value = cell.toString();
                }
                linked.add(value);
            }
            list.add(linked);
        }
        return list;
    }
}

以上是自己对excel的导入功能和批量添加操作 欢迎一起讨论