POI 导入、导出 Excel文件到数据库

1.导入Excel文件到数据的类

     后台代码：

 

 @Overried 
public void upFile(MultipartFile file){
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        try {
            InputStream in = file.getInputStream();
            Workbook work = new XSSFWorkbook(in);
            //第一个sheet
            Sheet sheet1 =  work.getSheetAt(0);

            //有效行数
            int rowNubms = sheet1.getPhysicalNumberOfRows();
            for(int i=1;i<rowNubms;i++){
                Row rowi = sheet1.getRow(i);

                if(rowi == null){
                    continue;
                }
                //创建数据库表对应的实体类
                FalseOfferings offer = new FalseOfferings();
                    Cell cell1 = rowi.getCell(0);
                    Cell cell2 = rowi.getCell(1);
                    Cell cell3 = rowi.getCell(2);
                    Cell cell4 = rowi.getCell(3);
                    Cell cell5 = rowi.getCell(4);
                    Cell cell6 = rowi.getCell(5);

                    //此处判断为null，以防get值的时候 报空指针
                    Long offeringId = (cell1 != null)?Long.parseLong(getCellValue(cell1)):null;
                    String offeringName =(cell2 != null)? getCellValue(cell2):null;
                    Date createDate = (cell3 != null)?format.parse(getCellValue(cell3)):null;
                    Long categoryId = (cell4 != null)?Long.parseLong(getCellValue(cell4)):null;
                    String fromdbs = (cell5 != null)?getCellValue(cell5):null;
                    String area = (cell6 != null)?getCellValue(cell6):null;

                //填充实体类
                offer.setOfferingId(offeringId);
                offer.setOfferingName(offeringName);
                offer.setCategoryId(categoryId);
                offer.setFromdbs(fromdbs);
                offer.setArea(area);
                offer.setCreateDate(createDate);
                //使用JPA 保存到数据库中
                falseOfferResposity.save(offer);
                in.close();  //关闭流
 }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //根据Excel表格类型，使用不同的方法去取值
    private String getCellValue(Cell cell){
        String value ="";
        switch (cell.getCellType()){
            case Cell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;
            case  Cell.CELL_TYPE_NUMERIC:
                value = String.valueOf(cell.getNumericCellValue());
                break;
            case  Cell.CELL_TYPE_BLANK:
                value = "";
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                value = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_ERROR:
                value = String.valueOf(cell.getErrorCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = String.valueOf(cell.getCellFormula());
                break;
        }
        return value;
    }

前台代码  HTML：

 
基于原有的file类型的input样式比较丑，所以此处使用假的 input 文本框 充当 

<div>
            <input type="text" name="wenJian" readonly="readonly" id="wenJian"/>
            <button ng-click="ctrl.liuLan()">浏览</button>
            <button ng-click="ctrl.daoRu()">确认导入</button>

            <form id="upForm" action="fileTariff/upFile" method="post" enctype="multipart/form-data" style="display: none">
                <input type="file" name="file"  accept=".xls,.xlsx"  id="file"/>
            </form>
 </div>

<div>
            <input type="text" name="wenJian" readonly="readonly" id="wenJian"/>
            <button ng-click="ctrl.liuLan()">浏览</button>
            <button ng-click="ctrl.daoRu()">确认导入</button>

            <form id="upForm" action="fileTariff/upFile" method="post" enctype="multipart/form-data" style="display: none">
                <input type="file" name="file"  accept=".xls,.xlsx"  id="file"/>
            </form>
 </div>

前台JS 用的是AngularJS 框架，代码如下，大致就是用来触发事件，向后台发送请求的


              self.liuLan = function(){
                    $('#file').click();
                };
                $('#file').change(function() {
                    $('#wenJian').val($('#file').val()) ;
                });
                self.daoRu = function(){
                    $('#upForm').submit();
                };


              self.liuLan = function(){
                    $('#file').click();
                };
                $('#file').change(function() {
                    $('#wenJian').val($('#file').val()) ;
                });
                self.daoRu = function(){
                    $('#upForm').submit();
                };

 

2.导出为Excel文件：

 

/**
         * POI : 导出数据，存放于Excel中
         * @param os 输出流 (action: OutputStream os = response.getOutputStream();)
         * @param employeeInfos 要导出的数据集合
         */
        public static void exportEmployeeByPoi(OutputStream os, List<EmployeeInfo> employeeInfos) {
            try {
                //创建Excel工作薄
                HSSFWorkbook book = new HSSFWorkbook();
                //在Excel工作薄中建一张工作表
                HSSFSheet sheet = book.createSheet("员工信息");
                //第一行为标题行
                HSSFRow row = sheet.createRow(0);//创建第一行
                HSSFCell cell0 = row.createCell(0);
                HSSFCell cell1 = row.createCell(1);
                HSSFCell cell2 = row.createCell(2);
                HSSFCell cell3 = row.createCell(3);
                HSSFCell cell4 = row.createCell(4);
                //定义表头单元格为字符串类型
                cell0.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell1.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell2.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell3.setCellType(HSSFCell.CELL_TYPE_STRING);
                cell4.setCellType(HSSFCell.CELL_TYPE_STRING);
                //在单元格中输入数据
                cell0.setCellValue("员工编号");
                cell1.setCellValue("员工姓名");
                cell2.setCellValue("员工性别");
                cell3.setCellValue("出生日期");
                cell4.setCellValue("身份证号");
                //循环导出数据到excel中
                for(int i = 0; i < employeeInfos.size(); i++) {
                    EmployeeInfo employeeInfo = employeeInfos.get(i);
                    //创建第i行
                    HSSFRow rowi = sheet.createRow(i + 1);
                    //在第i行的相应列中加入相应的数据
                    rowi.createCell(0).setCellValue(employeeInfo.getEmployeeNumber());
                    rowi.createCell(1).setCellValue(employeeInfo.getFullName());
                    //处理性别(M:男 F:女)
                    String sex = null;
                    if("M".equals(employeeInfo.getSex())) {
                        sex = "男";
                    }else {
                        sex = "女";
                    }
                    rowi.createCell(2).setCellValue(sex);
                    //对日期的处理
                    if(employeeInfo.getDateOfBirth() != null && !"".equals(employeeInfo.getDateOfBirth())){
                        java.text.DateFormat format1 = new SimpleDateFormat("yyyy-MM-dd");
                        rowi.createCell(3).setCellValue(format1.format(employeeInfo.getDateOfBirth()));
                    }
                    rowi.createCell(4).setCellValue(employeeInfo.getNationalIdentifier());
                }
                //写入数据 把相应的Excel 工作簿存盘
                book.write(os);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }