excle批量导入数据到数据库中

1.工具类：

package com.longjin.comm.utils;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.util.IOUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * 描述:ExcelUtil
 *
 * @author 何志鹏
 * @ClassName:ExcelUtil
 * @create 2019-05-27 10:31
 * Version 1.0
 */
public class ExcelUtil {

    public static List<String> importExcel(InputStream input, int startLine) {
        Workbook workbook = null;
        List<String> dataList = new ArrayList();

        try {
            workbook = WorkbookFactory.create(input);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }

        if (null != workbook) {
            Sheet readSheet = workbook.getSheetAt(0);
            //获取第一行数据
            int cellNum = null == readSheet.getRow(0) ? 0 : readSheet.getRow(0).getLastCellNum();
            Row readRow = null;
            Cell readCell = null;
            StringBuffer buff = new StringBuffer();
            for (int i = startLine; i < readSheet.getLastRowNum() + 1; i++) {
                buff.delete(0, buff.length());
                readRow = readSheet.getRow(i);
                if (readRow != null) {
                    for (int j = 0; j < cellNum; j++) {
                        readCell = readRow.getCell(j);
                        if (j < (cellNum - 1)) {
                            if (readCell != null) {
                                readCell.setCellType(Cell.CELL_TYPE_STRING);
                                buff.append(readCell.getStringCellValue() + ",");
                            } else {
                                buff.append(",");
                            }
                        } else {
                            if (readCell != null) {
                                readCell.setCellType(Cell.CELL_TYPE_STRING);
                                buff.append(StringUtils.isBlank(readCell.getStringCellValue()) ? "" : readCell.getStringCellValue());
                            } else {
                                buff.append(",");
                            }
                        }
                    }
                    dataList.add(buff.toString());
                }
            }

        }
        return dataList;
    }


    /**
     * 调用浏览器  导出Excle
     *
     * @param headers
     * @param dataList
     * @param response
     * @param fileName
     */
    public static void exportExcel(String[] headers, List<String> dataList, HttpServletResponse response, String fileName) {
        //声明一个工作谱
        HSSFWorkbook workbook = new HSSFWorkbook();
        //生成一个表格
        HSSFSheet sheet = workbook.createSheet("default");
        //默认为15字节
        sheet.setDefaultColumnWidth(15);
        //生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);


        HSSFFont font = workbook.createFont();
        font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

        style.setFont(font);

        HSSFRow row = sheet.createRow(0);
        HSSFCell cell = null;
        HSSFRichTextString text = null;

        for (int i = 0; i < headers.length; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(style);
            text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        if ((null != dataList) && (!dataList.isEmpty())) {
            String[] lineData = null;
            for (int j = 0; j < dataList.size(); j++) {
                row = sheet.createRow(1 + j);
                lineData = ((String) dataList.get(j)).split(",");
                if ((null != lineData) && (lineData.length > 0)) {
                    for (int m = 0; m < lineData.length; m++) {
                        cell = row.createCell(m);
                        text = new HSSFRichTextString(lineData[m]);
                        cell.setCellValue(text);
                    }
                }
            }
        }
        OutputStream outputStream = null;
        try {
            response.reset();
            response.setContentType("application/octet-stream;charset=UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + fileName);

            outputStream = response.getOutputStream();
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            IOUtils.closeQuietly(outputStream);
        }

    }

}

2.批量导入：

/**
 * 批量导入数据
 * @param multipart
 * @param jb
 * @return
 */
@Override
public Results importData(MultipartFile multipart, String timestamp) {
    Results rs = new Results();
    List<String> list = null;
    try {
        InputStream inputStream = multipart.getInputStream();
        list = ExcelUtil.importExcel(inputStream, 0);
    } catch (IOException e) {
        e.printStackTrace();
    }
    if (list.size() <= 0) {
        rs.setStatusCode(Contents.requestFail);
        rs.setStatusMsg(Contents.getMsg4Code(Contents.requestFail));
        rs.setTimestamp(timestamp);
        return  rs;
    }
    String[] titles = list.get(0).split(",");
    Boolean quotaInvoiceCode = false;
    Boolean quotaInvoiceNum = false;

    int quotaInvoiceCodeIndex = -1;
    int quotaInvoiceNumIndex = -1;
    int isEffectiveIndex = -1;

    for (int i = 0; i < titles.length; i++) {
        switch (titles[i].trim()) {
            case "定额发票编号":
                quotaInvoiceCode = true;
                quotaInvoiceCodeIndex = i;
                break;
            case "定额发票额度":
                quotaInvoiceNum = true;
                quotaInvoiceNumIndex = i;
                break;
            default:
                break;
        }
    }
    for(int i = 1; i<list.size();i++){
        String[] beans = list.get(i).split(",");
        CaseQuotaInvoice  caseQuotaInvoice = new CaseQuotaInvoice();
        if(beans==null||beans.length<=0){
            continue;
        }
        //判断是否有非法字符
        String regEx = "[ _`~!@#$%^&*()+=|{}':;',\\[\\].<>/?~！@#￥%……&*（）——+|{}【】‘；：”“’。，、？]|\n|\r|\t";
        Pattern p = Pattern.compile(regEx);
        Matcher m = p.matcher(beans[quotaInvoiceNumIndex]);
        Matcher n = p.matcher(beans[quotaInvoiceCodeIndex]);
        if(m.find()==true||n.find()==true){
            continue;
        }
        caseQuotaInvoice.setQuotaInvoiceNum(String.valueOf(beans[quotaInvoiceNumIndex]));
        caseQuotaInvoice.setQuotaInvoiceCode(beans[quotaInvoiceCodeIndex]);
        caseQuotaInvoice.setCreateTime(System.currentTimeMillis());
        caseQuotaInvoice.setIsEffective(1);
        int count = caseQuotaInvoiceDao.addCaseQuotaInvoice(caseQuotaInvoice);
        if(count==1){
            // 导入成功
            rs.setStatusCode(Contents.requestSuccess);
            rs.setStatusMsg(Contents.getMsg4Code(Contents.requestSuccess));
            rs.setTimestamp(timestamp);
            return rs;
        }

    }
    rs.setStatusCode(Contents.requestFail);
    rs.setStatusMsg(Contents.getMsg4Code(Contents.requestFail));
    rs.setTimestamp(timestamp);
    return  rs;
}

 

 <dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi</artifactId>
   <version>3.9</version>
</dependency>
<dependency>
   <groupId>xerces</groupId>
   <artifactId>xercesImpl</artifactId>
   <version>2.11.0</version>
</dependency>
<dependency>
   <groupId>org.apache.poi</groupId>
   <artifactId>poi-ooxml</artifactId>
   <version>3.9</version>
      </dependency>