java POI 之Excel导入详解

来源：互联网发布：草图大师mac破解版编辑：程序博客网时间：2024/06/01 16:50

POI+Excel导入

1.Jar包导入

所需要导入的jar包

commons-fileupload-1.3.1.jar

commons-io-2.4.jar

dom4j-1.6.1.jar

poi-3.14.jar

poi-ooxml-3.14.jar

poi-ooxml-schemas-3.14.jar

xmlbeans-2.6.0.jar

2.界面设计

通过类型为file的input选择计算机文件, 其中input是通过kendoUpload 定义文件上传的方法

//定义文件上传之后调用后台的upload方法.async:{    saveUrl: baseUrl+"/upload?${_csrf.parameterName}=${_csrf.token}",    autoUpload: false},

//选择文件按钮<div class="panel-body">  <div class="row">    <input type="file" name="files" id="importExcelFile">  </div></div>//显示错误信息div<div class="panel-body">  <div class="row">    <span id="errorMessage"></span>  </div></div>

<script id="fileTemplate" type="text/x-kendo-template">        <div>            <p style="margin:0px;">#=name#&nbsp;&nbsp;&nbsp;#=parseInt(size/1024)#kb</p>            <button type='button' class='k-upload-action' style='position: absolute; top: 8px; right: 0;'></button>        </div></script>       var baseUrl = "${base.contextPath}/cux/gxp/mq/basic";        $("#importExcelFile").kendoUpload({            <!--add by xiaotong.wu@hand-china.com 2017/11/7-->            template: kendo.template($('#fileTemplate').html()),            <!--add end-->            async:{                saveUrl: baseUrl+"/upload?${_csrf.parameterName}=${_csrf.token}",                autoUpload: false            },             multiple: false,            localization: {                select: "请选择文件",                uploadSelectedFiles: "上传",                headerStatusUploading: "上传中...",                headerStatusUploaded: "文件上传成功!"            },            error:function (e) {                kendo.ui.showInfoDialog({                    title: '提示信息',                    message: '导入失败!'                });            },            success:function (e) {                if(e.response.success){                     kendo.ui.showInfoDialog({                         title: '提示信息',                         message: '添加成功'                     }).done(function (event) {                         if (event.button == 'OK') {                              window.parent.$("#excelWindow").data("kendoWindow").close();                              window.parent.$("#Grid").data("kendoGrid").dataSource.page(1);                         }                     })                }else{                     kendo.ui.showErrorDialog({                         title: '提示信息',                         message: '导入失败!<br/>'                     }).done(function (event) {                         $("#errorMessage").html('<h5>失败信息:</h5><br/>' + e.response.message)                     });                }            }        });

整体界面如下图所示:

3.POI解析Excel

项目中需要导入多个sheet页, 下边介绍多个sheet导入的情况:

MultipartFile files: 接收上传的文件参数

String fileName = files.getOriginalFilename() : 获取文件名称

XSSFWorkbook(java.io.InputStream is) : 官方解释是Constructs a XSSFWorkbook object, by buffering the whole stream into memory and then opening an OPCPackage object for it.

XSSFWorkbook wb = new XSSFWorkbook(files.getInputStream()) 根据一个给定的file, 通过缓冲整个输入流到内存中,建立一个工作簿, 工作簿中的内容就是接收到的excel文件.

XSSFSheet sheetName = wb.getSheetAt(i) : 定位到某个sheet页

int endRowNo = sheetName.getLastRowNum(); 获得最后一条数据的行号即判断有多少行数据

XSSFRow nRow = sheetName.getRow(0); 获得sheet的第一行

short endColNo = nRow.getLastCellNum(); 根据获得的第一行，来获得sheet中共有多少列数据

Cell nCell = nRow.getCell(j); 获得这一行第j 个单元格

nCell.getCellType() 判断单元格里数据类型

单元格类型

在实际工作中，我们处理的Excel数据都不止限于字符型数据，更多的是数字、日期、甚至公式等。下面是单元格类型说明：以下单元格的类型，可以通过getCellType()方法获得，返回值为int。

类型 CELL_TYPE_BLANK 空值（cell不为空） CELL_TYPE_BOOLEAN 布尔 CELL_TYPE_ERROR 错误 CELL_TYPE_FORMULA 公式 CELL_TYPE_STRING 字符串 CELL_TYPE_NUMERIC 数值

如果单元格中是公式 如: 总成绩是多个成绩的总和,是一个公式, 则需要通过cell.getCellFormula()取单元格公式。

如果单元格值是数字: 则取值的时候是通过cell.getNumericCellValue()方法

如果单元格值是日期类型: Excel中的Date类型以Double型数字存储的，表示当前时间与1900年1月1日相隔的天数。所以在Excel中判断单元格格式为NUMERIC类型还需要进一步判断是否为日期类型。在读取日期单元格时需要调用HSSFDateUtil的isCellDateFormatted方法，来判断该Cell的数据格式是否是Date类型，然后通过HSSFCell的getDateCellValue方法获取Date。或者通过HSSFDateUtil的getJavaDate()方法获取Date

if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){      if(HSSFDateUtil.isCellDateFormatted(cell)){          Date date = cell.getDateCellValue();          SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");         e.add(dateFormat.format(date));     }}

case HSSFCell.CELL_TYPE_NUMERIC:if (HSSFDateUtil.isCellDateFormatted(nCell)) {    double d = nCell.getNumericCellValue();    Date date = HSSFDateUtil.getJavaDate(d);    SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");    e.add(dformat.format(date));}

如果单元格值是自定义类型:在Excel中有许多种数据格式，并且支持用户自定义格式。如下图。可以通过HSSFDataFormat类进行操作。getBuiltinFormat(short index)方法根据编号返回内置数据类型，getBuiltinFormat(java.lang.Stringformat)方法根据数据类型返回其编号，getBuiltinFormats()返回整个内置的数据格式列表。

HSSFDataFormat的数据格式:

内置数据类型编号 “General” 0 “0” 1 “0.00” 2 “#,##0” 3 “#,##0.00” 4 “(

#,##0_);(#,##0)” 5 “($#,##0_);(#,##0)” 6 “(

#,##0.00);(#,##0.00)” 7 “(

#,##0.00_);(#,##0.00)” 8 “0%” 9 “0.00%” 0xa “0.00E+00” 0xb “# ?/?” 0xc “# ??/??” 0xd “m/d/yy” 0xe “d-mmm-yy” 0xf “d-mmm” 0x10 “mmm-yy” 0x11 “h:mm AM/PM” 0x12 “h:mm:ss AM/PM” 0x13 “h:mm” 0x14 “h:mm:ss” 0x15 “m/d/yy h:mm” 0x16 保留为过国际化用 0x17 - 0x24 “(#,##0_);(#,##0)” 0x25 “(#,##0_);Red” 0x26 “(#,##0.00_);(#,##0.00)” 0x27 “(#,##0.00_);Red” 0x28 “(*#,##0_);_(*(#,##0);($* \”-\”);(@_)” 0x29 “(*#,##0.00);(*(#,##0.00);(*\”-\”??);(@_)” 0x2a “(*#,##0.00_);_(*(#,##0.00);($*\”-\”??);(@_)” 0x2b “(*#,##0.00_);_(*(#,##0.00);($*\”-\”??);(@_)” 0x2c “mm:ss” 0x2d “[h]:mm:ss” 0x2e “mm:ss.0” 0x2f “##0.0E+0” 0x30 “@” - This is text format 0x31

@RequestMapping(value = {"/cux/gxp/mq/basic/upload"}, method = {RequestMethod.POST})    @ResponseBody    public ResponseData upload(HttpServletRequest request, MultipartFile files) throws Exception {        IRequest requestContext = this.createRequestContext(request);        String fileName = files.getOriginalFilename();        XSSFWorkbook wb = new XSSFWorkbook(files.getInputStream());        ResponseData responseData = new ResponseData();        XSSFRow nRow = null;        Cell nCell = null;        byte beginRowNo = 1;//定义拿取数据的起始行0开始        //新建list存放sheet中的数据        ArrayList dataList1 = new ArrayList();        ArrayList dataList2 = new ArrayList();        ArrayList dataList3 = new ArrayList();        ArrayList dataList4 = new ArrayList();      //循环workbook中所有sheet         for(int i=0; i<4; i++){            XSSFSheet sheetName = wb.getSheetAt(i);    //获得sheet            int endRowNo = sheetName.getLastRowNum();  //获得最后一条数据的行号即有多少行数据            nRow = sheetName.getRow(0);                //获得sheet的title            short endColNo = nRow.getLastCellNum();    //根据获得的title，来获得sheet中共有多少列数据          //循环该sheet中的有数据的每一行              for(int rData = beginRowNo; rData <= endRowNo; ++rData) {                 nRow = sheetName.getRow(rData);                ArrayList e = new ArrayList();                if(nRow==null){ nRow = sheetName.createRow(rData); }              //循环该行的每一个单元格                  for (int j = 0; j < endColNo; ++j) {                    nCell = nRow.getCell(j);                    if (nCell != null) {                        switch (nCell.getCellType()) {                            case HSSFCell.CELL_TYPE_NUMERIC:                                if (HSSFDateUtil.isCellDateFormatted(nCell)) {                                    double d = nCell.getNumericCellValue();                                    Date date = HSSFDateUtil.getJavaDate(d);                                    SimpleDateFormat dformat = new SimpleDateFormat("yyyy-MM-dd");                                    e.add(dformat.format(date));                                } else {                                    NumberFormat nf = NumberFormat.getInstance();                                    nf.setGroupingUsed(false);//true时的格式：1,234,567,890                                    if ((Double) nCell.getNumericCellValue() != null) {                                        e.add(nf.format(nCell.getNumericCellValue()));//数值类型的数据为double，所以数值类型需要转换一下                                    } else {                                        e.add("");                                    }                                }                                break;                            case HSSFCell.CELL_TYPE_STRING:                                if (StringUtils.isNotBlank(nCell.getStringCellValue())) {                                    e.add(nCell.getStringCellValue());                                } else {                                    e.add("");                                }                                break;                            default:                                e.add("");                        }                    } else {                        e.add("");                    }                }                //拿到文件中所有的值                if(i == 0) dataList1.add(e);                if(i == 1) dataList2.add(e);                if(i == 2) dataList3.add(e);                if(i == 3) dataList4.add(e);            }        }        //判断工作簿是否为空        if(dataList1.size()==0 && dataList2.size()==0 && dataList3.size()==0 && dataList4.size()==0){            responseData.setMessage(fileName + "的所有sheet是空的！");            responseData.setSuccess(false);            wb.close();            return responseData;        }        ResponseData var24 = new ResponseData();        ResponseData var25;        //清除最后一行无效数据        Long start1 = System.currentTimeMillis();        clearInvalidUploadData(dataList1);        clearInvalidUploadData(dataList2);        clearInvalidUploadData(dataList3);        clearInvalidUploadData(dataList4);        Long end1 = System.currentTimeMillis();        Long time1 = end1-start1;        //提示sheet中存在的重复数据        try {            Long start2 = System.currentTimeMillis();            checkDuplicateData(dataList1,new int[]{0,1,6},"物料资质头信息");            //checkDuplicateData(dataList2,new int[]{0,1},"物料资质行信息");            //checkDuplicateData(dataList3,new int[]{0,1,2,3},"物料生产商资质");            checkDuplicateData(dataList4,new int[]{0,1,2},"物料诊疗范围");            Long end2 = System.currentTimeMillis();            Long time2 = end2-start2;        } catch (Exception e) {            e.printStackTrace();            var24.setMessage(e.getMessage());            var24.setSuccess(false);            var25 = var24;            return var25;        }        try {          //调用service方法将数据插入数据库            service.insertAllValue(requestContext, dataList1,dataList2,dataList3,dataList4,request);            return new ResponseData(true);        } catch (RuntimeException var21) {            //var24.setMessage("导入失败！出现异常错误:"+var21.getLocalizedMessage());            var24.setMessage(var21.getLocalizedMessage());            var24.setSuccess(false);            var25 = var24;        } catch (Exception var22) {            //var22.printStackTrace();            var24.setMessage(var22.getMessage());            var24.setSuccess(false);            var25 = var24;            return var25;        } finally {            wb.close();        }        return var25;    }

//清除多sheet导入时最后一行无效数据    private void clearInvalidUploadData(List listRow){        LOOP:        for (int i = (listRow.size()-1); i >= 0 ; i--) {            boolean eFlag = false;  //标记行数据            ArrayList<String> list = (ArrayList<String>) listRow.get(i);            for(String str:list){                if(!str.equals("")){                    eFlag=true;                    break LOOP;                }            }            if(!eFlag){                listRow.remove(i);            }        }    }

阅读全文

0 0