JAVA用POI实现EXCEL导入数据库,支持.xls与.xlsx两种格式

poi操作Excel 主要通过HSSF,XSSF两种方式。
HSSF只能解析.xls格式的excel文件，XSSF支持.xls与.xlsx两种格式。

方法：

1.获取浏览器上传的excel文件
2.根据excel文件格式解析excel文件，读取excel每一行数据并存放到集合里
3.批量写入数据库

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.alibaba.fastjson.JSONObject;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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.util.CollectionUtils;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;


public class ExportExcel {
    private String message = "";

    /**
     * 上传excel文件
     * @param excelFile
     * @return
     * @throws IOException
     */
    @RequestMapping(value = "/saveExcel", produces = { "application/json;charset=UTF-8" }, method = {
            RequestMethod.POST, RequestMethod.GET })
    @ResponseBody
    public String readXls(@RequestParam MultipartFile[] excelFile) throws IOException {
        long startTime=System.currentTimeMillis();
        String originalFilename;
        int r = 0;
        if (excelFile == null || excelFile.length == 0) {
            return JSONObject.toJSONString(new BaseModel("文件上传失败，请重试", r));
        }
        try {
            for (MultipartFile myfile : excelFile) {
                if (myfile.isEmpty()) {
                    return JSONObject.toJSONString(new BaseModel("文件为空", r));
                } else {
                    originalFilename = myfile.getOriginalFilename();
                    if (!(originalFilename.endsWith(".xls") || originalFilename.endsWith(".xlsx"))) {
                        return JSONObject.toJSONString(new BaseModel(
                                "文件格式错误，请重试", r));
                    }

                    List<Entity> list = null;
                    /*根据文件格式解析文件*/
                    if (originalFilename.endsWith(".xls")) {
                        list = readXLS(myfile);
                    } else if (originalFilename.endsWith(".xlsx")) {
                        list = readXLSX(myfile);
                    }
                    /*批量插入数据*/
                    if(!CollectionUtils.isEmpty(list)){
                        batchInsertData(list);
                    }

                }
            }

        } catch (Exception e) {
            e.printStackTrace();
            return JSONObject.toJSONString(new BaseModel(
                    "文件解析失败，请确认Excel表中的数据无误", r));
        }
        long hs=System.currentTimeMillis()-startTime;
        return JSONObject.toJSONString(new BaseModel(message+"导入数据总耗时："+hs+"毫秒", r));
    }

    /**
     * 解析.xls格式的excel文件
     * @param file
     * @return
     * @throws IOException
     */
    public List<Entity> readXLS(MultipartFile file) throws IOException {
        InputStream is = file.getInputStream();
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook(is);
        Entity entity;
        List<Entity> list = new ArrayList<>();
        // 循环工作表Sheet
        // 循环行Row
        int errorNum = 0;
        int okNum = 0;
        String errorMsg = "";
        HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
        for (int rowNum = 0; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
            if (hssfRow != null) {
                entity = new Entity();
                HSSFCell txt = hssfRow.getCell(0);
                if (txt == null) {
                    errorNum += 1;
                    errorMsg += "第" + (rowNum + 1) + "行（数据）格式错误,不能为空&";
                    continue;
                }
                txt.setCellType(XSSFCell.CELL_TYPE_STRING);
                if(StringUtils.isBlank(txt.getStringCellValue())){
                    errorNum += 1;
                    errorMsg += "第" + (rowNum + 1) + "行（数据）格式错误,不能为空&";
                    continue;
                }
                entity.setPhone(txt.getStringCellValue());
                entity.setStatus(1);
                entity.setCreate_time(new Date());
                list.add(entity);
                okNum += 1;
            }

        }
        message = "导入结果:&成功导入" + okNum + "条数据 失败" + errorNum + "条&错误记录:&"
                + errorMsg;

        if(is!=null){
            is.close();
        }
        return list;
    }

    /**
     * 解析.xlsx格式的excel文件
     * @param file
     * @return
     * @throws IOException
     */
    public List<Entity> readXLSX(MultipartFile file) throws IOException {
        InputStream is = file.getInputStream();
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(is);
        Entity entity;
        List<Entity> list = new ArrayList<>();
        // 循环工作表Sheet
        // 循环行Row
        int errorNum = 0;
        int okNum = 0;
        String errorMsg = "";
        XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
        for (int rowNum = 0; rowNum <= xssfSheet.getLastRowNum(); rowNum++) {
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            if (xssfRow != null) {
                entity = new Entity();
                XSSFCell txt = xssfRow.getCell(0);
                if (txt == null) {
                    errorNum += 1;
                    errorMsg += "第" + (rowNum + 1) + "行（数据）格式错误,不能为空&";
                    continue;
                }
                txt.setCellType(XSSFCell.CELL_TYPE_STRING);
                if(StringUtils.isBlank(txt.getStringCellValue())){
                    errorNum += 1;
                    errorMsg += "第" + (rowNum + 1) + "行（数据）格式错误,不能为空&";
                    continue;
                }
                entity.setPhone(txt.getStringCellValue());
                entity.setStatus(1);
                entity.setCreate_time(new Date());
                list.add(entity);
                okNum += 1;
            }
        }
        message = "导入结果:&成功导入" + okNum + "条数据 失败" + errorNum + "条&错误记录:&"
                + errorMsg;

        if(null!=is){
            is.close();
        }
        return list;
    }

    /**
     * 批量插入数据到数据库
     * */
    private int batchInsertData(List<Entity> list) {
        int result = 0;
        if (CollectionUtils.isEmpty(list)) {
            return result;
        }
        //每次最多插入1000条数据
        int beatchMaxSize = 1000;
        while (list.size() > 0) {
            if (list.size() < beatchMaxSize) {
                beatchMaxSize = list.size();
            }
            try {
                List<Entity> batchList = list.subList(0, beatchMaxSize);
                /*批量写入数据库的方法*/
                result  = batchSave(batchList);
            } catch (Exception e) {
                e.printStackTrace();
            }
            list.removeAll(list.subList(0, beatchMaxSize));
        }
        return result;
    }
}

前端发起请求：

<a onClick="document.getElementById('excelFile').click()">Excel导入</a>
<input id="excelFile" name="excelFile" type="file" onchange="saveFile(this);" style="display:none"  />

使用ajaxFileUpload上传excel文件:

<script>
  function saveFile(obj){
            var files = obj.files;
            for (i = 0; i < files.length; i++) {
                file = files[i]
                console.log(file);
            }
            $.ajaxFileUpload({
                url : '<%=path%>/saveExcel?saveFolder=/files/excel',
                secureuri : false,//安全协议
                type : 'post',
                dataType : 'json',
                fileElementId : "excelFile",
                async : false,//同步请求
                success : function(data) {
                    if(data.status == 0 || data.msg != ""){
                        alert('提示信息',data.msg.split("&amp;").join("<br/>"));
                    }else{
                        alert('提示信息',"导入成功");
                    }
                }
            });
        }
</script>

注意：页面input的name对应的字段名称要和后台controller接受请求的@RequestParam MultipartFile[]参数名一致，这里都为‘excelFile’，否则后台是获取不到数据的。

 

 

原文作者技术博客：https://www.jianshu.com/u/ac4daaeecdfe
95后前端妹子一枚，爱阅读，爱交友，将工作中遇到的问题记录在这里，希望给每一个看到的你能带来一点帮助。
欢迎留言交流