POI excel和数据库之间数据的导入导出

本文记录一下在将多个sheet表导入数据库，工作簿的sheet中一条数据对应的是表中一个字段里内容，数据库数据导出时，将表中一个字段里内容相同的放在一个sheet页中的学习过程；通俗的来说就是将多个工作簿转换成一个工作簿，此过程经由DB暂存了一下数据。
导出过程中，思路是：创建一个工作簿，在工作簿内新建一个sheet，初始值为空；取出数据库中所有sheet页需要的内容，对其进行遍历，创建两个list，一个存放表头信息（keysList），一个存放sheet信息（sheetList），进行if条件判断，若keys不存在于keysList内时，将该keys存于keysList，并新建一个sheet页，并将该sheet页信息存于sheetList中；若keys存在于keysList内时，不对keysList和sheetList做任何操作，判断该sheet页是否存在于sheetList中，存在即在其后追加该条数据即可。关键点：由于只是在keys不存在与keysList时对keysList和sheetList做任何操作，所以它们的索引值肯定是一样的，即可通过keys的内容indexof其索引值，从而去获得sheetList内的sheet。

（一）Excel导入数据库

public JSONObject uploadCusData(String ruleid, MultipartFile xlsfile){

    JSONObject jsonObject = new JSONObject();
    try {
        if (xlsfile != null) {
            Workbook wb = WorkbookFactory.create(xlsfile.getInputStream());
            for (int numSheet = 0; numSheet < wb.getNumberOfSheets(); numSheet++) {
                Sheet sheet = wb.getSheetAt(numSheet);
                if (sheet == null) {
                    continue;
                }
                int rowNum = sheet.getLastRowNum(); //得到表的行数
                int cellNum = 0;
                Cell cell = null;
                ArrayList<String> key = new ArrayList<String>();

                //JSONObject excelContents = new JSONObject();

                jsonObject.put("msg", "导入成功，共导入" + rowNum + "个用户");
                for (int i = 0; i <= rowNum; i++) {    //从有数据的一行开始
                    DialingList dialingList = new DialingList();    //定义类对象
                    JSONArray excelContents = new JSONArray();  //接受excel每一行的数据
                    Row row = sheet.getRow(i);  //得到第i行数据
                    cellNum = sheet.getRow(i).getLastCellNum(); //一行表格中单元格数量
                    JSONObject cellContents = new JSONObject();
                    if (i == 0) {    //将第一行的各单元取出，存入数组列表中，为以后的json数据里的key值做准备
                        for (int j = 0; j < cellNum; j++) {
                            cell = row.getCell(j);
                            if (cell != null) {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                key.add(cell.getStringCellValue());
                                System.out.println(key);
                            }
                        }
                    } else {
                        int k = 0;  //遍历keys内的key值时的索引值
                        dialingList.setRuleid(ruleid);
                        for (int j = 0; j < cellNum; j++) { //控制列数
                            cell = row.getCell((short) j);
                            if (cell != null) {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cellContents.put(key.get(k).toString(), cell.getStringCellValue());
                                k++;
                            }
                        }
                        excelContents.add(cellContents);
                        dialingList.setExcelcontents(excelContents.toString());
                        iDialingListDao.save(dialingList);
                    }
                }
            }
            jsonObject.put("state", "success");
        }
    }catch (FileNotFoundException e) {
        e.printStackTrace();
    } catch (IOException e) {
        e.printStackTrace();
    } catch (InvalidFormatException e) {
        e.printStackTrace();
    }
    return jsonObject;
}

（二）数据库导出excel

//将数据库内的excelcontents的数据导出至Excel
public JSONObject outputDialingList(){
    JSONObject jsonObject = new JSONObject();
    try{
        HSSFWorkbook hwb = new HSSFWorkbook();  //创建一个workbook（一个excel文件）
        List<DialingList> dialingLists = iDialingListDao.findAll();
        ArrayList<String> keysList = new ArrayList<String>();   //存放每条数据的key值
        ArrayList<HSSFSheet> sheetList = new ArrayList<HSSFSheet>();
        HSSFSheet sheet = null;//在workbook中添加一个sheet，对应excel文件中sheet

        for (int i = 0;i < dialingLists.size();i++) {
            String excelcontents = dialingLists.get(i).getExcelcontents();  //取出数据库excelcontents字段对应的内容，字符串接受
            JSONArray excelcontentsArray = JSONArray.fromObject(excelcontents);  //将excelcontents转换成JSONArray
            String excelcontents1 = excelcontentsArray.get(0).toString();    //取出excelcontents里第一个值，字符串接受
            JSONObject strjson = JSONObject.fromObject(excelcontents1);     //将excelcontents1转换成JSONObject
            Iterator<String> sIterator = strjson.keys();
            ArrayList<String> keys = new ArrayList<>();         //存放一条数据的key值
            while (sIterator.hasNext()) {
                String key = sIterator.next();
                keys.add(key);
            }//为了防止表头顺序问题，最好添加 Collections.sort(keys);将keys进行排序

            String str = "";
            for (String str1 : keys){
                str += str1;
            }
            log.warn(str);
            if (!keysList.contains(str)){   //keyList中不存在key
                keysList.add(str);
                sheet = hwb.createSheet("自动外呼名单"+i);
                HSSFRow hssfRow = sheet.createRow(0);  //创建第一行
                for (int j = 0; j < keys.size(); j++) {
                    String key = keys.get(j);
                    HSSFCell hssfCell = hssfRow.createCell(j);
                    hssfCell.setCellValue(key);
                }
                hssfRow = sheet.createRow(1);
                for (int k = 0;k < strjson.size();k++){
                    HSSFCell hssfCell = hssfRow.createCell(k);
                    String key = keys.get(k);
                    hssfCell.setCellValue(strjson.getString(key));
                }
                sheetList.add(keysList.indexOf(str),sheet);
            }else{                          //keyList中存在key
                if (sheetList.contains(sheet)){
                    sheet = sheetList.get(keysList.indexOf(str));
                    HSSFRow hssfRow = sheet.createRow(sheet.getLastRowNum() + 1);
                    for (int j = 0; j < keys.size(); j++) {
                        String key = keys.get(j);
                        HSSFCell hssfCell = hssfRow.createCell(j);
                        hssfCell.setCellValue(strjson.getString(key));
                    }
                }
            }
            log.warn("sheetList:"+sheetList);
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        Date d = new Date();
        FileOutputStream fos = new FileOutputStream(new File("F://test//" +"自动外呼名单"+sdf.format(d)+"-"+Math.random()*100+".xls"));//先 new 出文件存放的位置
        hwb.write(fos);;//写入
        fos.close();//关闭资源
        jsonObject.put("msg","导出成功！");
    } catch (Exception e){
        e.printStackTrace();
    }
    return jsonObject;