内外网数据导出

---

前言

个人工作日志：外网数据的导出。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、思路？

1.通过配置文件的方式，将所要操作的数据表存入；
2.创建解析XML文件的方法，将xml配置文件中的表信息解析出来；
3.解析过后将sql数据存至集合中，遍历集合数据，将数据导出至Excel表中；
4.对导出的历史数据，做成可下载方式，便于查看历史导出内容；

二、步骤

1.解析XML

创建一个实体类用于接收解析的XML中表的信息：

解析XML代码如下（示例）：

  /**
     * 获取配置sql 集合
     * @return
     */
    private List<Table> getConfigSql() throws Exception{
        List<Table> tableList=new ArrayList();
        Element rootElement= new SAXReader().read(Thread.currentThread().getContextClassLoader().getResourceAsStream("exportExcel_to_inside.xml")).getRootElement();
        Iterator it = rootElement.elementIterator();
        while (it.hasNext()) {
            Element table = (Element) it.next();

            List<Attribute> tableAttrs = table.attributes();
            Table tableEntity=new Table();
            for (Attribute attr : tableAttrs) {
                PropertyUtils.setProperty(tableEntity,attr.getName(),attr.getValue());
            }
            Iterator itt = table.elementIterator();
            while (itt.hasNext()) {
                Element child = (Element) itt.next();

                //如果包含子table
                if(child.getName().equals("chlidTables")){
                    Iterator chlidTableIt = child.elementIterator();
                    while (chlidTableIt.hasNext()) {
                        //维护childTable数据
                        Element chlidTableEle = (Element) chlidTableIt.next();
                        List<Attribute> childTableAttrs = chlidTableEle.attributes();
                        Table chlidTable=new Table();
                        for (Attribute attr : childTableAttrs) {
                            PropertyUtils.setProperty(chlidTable,attr.getName(),attr.getValue());
                        }
                        Iterator chlidTableItt = chlidTableEle.elementIterator();
                        while (chlidTableItt.hasNext()) {//获取childTable的下级标签值
                            Element chlidTableEleChildEle = (Element) chlidTableItt.next();
                            PropertyUtils.setProperty(chlidTable,chlidTableEleChildEle.getName(),chlidTableEleChildEle.getStringValue());
                        }
                        tableEntity.getChildTableList().add(chlidTable);
                    }

                }else{
                    PropertyUtils.setProperty(tableEntity,child.getName(),child.getStringValue());
                }

            }
            tableList.add(tableEntity);
        }
        return  tableList;
    }

2.XML配置文件

<!--数据表-->
<?xml version="1.0" encoding="UTF-8"?>
<root>
    <table name="change_term_record" id="nId" isUpdateLock="true"
            filed="nId,nPartyId,vcGroupNo,vcName,nPeriod,nStatus,dtBegin,dtEnd,dtOffice,dtLeave,isDelete"
           property="nId,nPartyId,vcGroupNo,vcName,nPeriod,nStatus,dtBegin,dtEnd,dtOffice,dtLeave,isDelete"
           condition=" nStatus=-1 and isExport=0 and isDelete=0 ">
        <select>select {filed} from change_term_record where  nStatus=-1 and isExport=0 and isDelete=0</select>
        <chlidTables>
            <chlidTable name="change_term_candidate" id="nId"
                        filed="nId,nPersonId,nPostId,nProcessId,nPoll,isElect,createTime,updateTime,isDelete"
                        property="nId,nPersonId,nPostId,nProcessId,nPoll,isElect,createTime,updateTime,isDelete">
                <select>select {filed} from change_term_candidate where nProcessId in ({id})</select>
            </chlidTable>
            <chlidTable name="change_term_member" id="nId"
                        filed="nId,nProcessId,nPersonId,vcPostName,nPoll,vcDicId,createTime,updateTime,isDelete"
                        property="nId,nProcessId,nPersonId,vcPostName,nPoll,vcDicId,createTime,updateTime,isDelete">
                <select>select {filed} from change_term_member where nProcessId in ({id})</select>
            </chlidTable>
            <chlidTable name="change_term_post" id="nId"
                        filed="nId,nProcessId,vcPostName,nOrder,nType,createTime,updateTime,isDelete"
                        property="nId,nProcessId,vcPostName,nOrder,nType,createTime,updateTime,isDelete">
                <select>select {filed} from change_term_post where nProcessId in ({id})</select>
            </chlidTable>
            <chlidTable name="change_term_result" id="nId"
                        filed="nId,nControlId,nStepId,nProcessId,vcResult,createTime,updateTime,isDelete"
                        property="nId,nControlId,nStepId,nProcessId,vcResult,createTime,updateTime,isDelete">
                <select>select {filed} from change_term_result where nProcessId in ({id})</select>
            </chlidTable>
            <!--<chlidTable name="change_term_step_record" id="nId">
                <select>select * from change_term_step_record where nProcessId = </select>
            </chlidTable>-->
        </chlidTables>
    </table>
</root>

3.导出方法

代码如下（示例）：

 @Override
    public BaseResult exchangeToExcelData(HttpSession session, HttpServletRequest request,HttpServletResponse response) {
        Exchange exchange=this.createExchange(session,1);
        try {
            this.doDataExport(session,request,response,exchange);
        }catch (Exception e ){
            e.printStackTrace();
            exchange.setStatus(2);
            exchange.setError(WebUtil.getErrorLog(e));
            return new BaseResult(500,e instanceof Exception ?e.getLocalizedMessage():"发生错误","");
        }finally {
            exchangeMapper.updateExchange(exchange);
        }
        return new BaseResult(exchange);
    }

 private void doDataExport(HttpSession session,HttpServletRequest request,HttpServletResponse response, Exchange exchange)throws Exception{
        List<Table> tableList=this.getConfigSql();//解析xml 并添加exchange记录
        if(CollectionUtils.isNotEmpty(tableList)){

            String fileName=UUID.getUUID()+"exchange.xlsx";
            String filePath=exportPath+File.separator+fileName;
            exchange.setFileName(fileName);
            exchange.setFilePath(filePath);
            String realPath = session.getServletContext().getRealPath("/");
            String inputFile = realPath+excelPath;
            try{
                int a = exportExcel(inputFile,tableList,exchange); //导出excel表
                if (a == 0){
                    throw new NoExchangeDataException("没有最新数据可导出");
                }else if (a == -1){
                    throw new DataExchangeException("导出数据错误！");
                }
            }catch (DataExchangeException e){
                TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
                throw e;
            }
        }else{
            throw new DataExchangeException("未读取到导出配置文件exportExcel_to_inside.xml！");
        }
    }

  /**
     *  导出excel表
     * @param tableList
     * @return
     */
    private int exportExcel(String inputFile,List<Table> tableList, Exchange exchange){
        try {
            //1 创建Excel文件
            FileInputStream fis=new FileInputStream(inputFile);//创建Excel文件的输入流对象
            XSSFWorkbook workbook = new XSSFWorkbook(fis);//根据模板创建Excel工作簿
            //2 根据tableList创建sheet
            int count = doSheet(tableList, workbook, exchange);
            if (count == 0) return 0;
            //6 创建Excel文件输出流对象
            String outputFile= exchange.getFilePath();
            OutputStream os = new FileOutputStream(outputFile);
            workbook.write(os);
            fis.close();
            os.close();
            workbook = null;
        } catch (IOException e) {
            e.printStackTrace();
            throw new DataExchangeException("输出流文件路径异常！");
        }
        return 1;

    }

private int doSheet(List<Table> tableList, XSSFWorkbook workbook, Exchange exchange) {
        int count = 0;
        int sheetIndex = 0;
        String now = WebUtil.dateToString(new Date(), "yyyy-MM-dd HH:mm:ss");
        for(int a = 0; a < tableList.size(); a++){

            Table table = tableList.get(a);

            //通过table 拿到每个导出表的集合
            String sql = table.getSelect();
            sql = sql.replace("{filed}",table.getFiled());  //  替换表字段
            table.setSelect(sql);
            List<Map<String,Object>> resultList = commonMapper.mapSql(sql);
            if (resultList.size() == 0) continue;

            count += resultList.size();

            XSSFSheet sheet;
            if (sheetIndex > (workbook.getNumberOfSheets()-1)){
                sheet = workbook.createSheet();//创建工作簿
            }else {
                sheet = workbook.getSheetAt(sheetIndex);
            }
            workbook.setSheetName(sheetIndex,table.getName());//给指定的sheet命名
            sheetIndex ++;

            //3 维护表第一行综合信息
            doSheetFirstLine(sheet, table, resultList.size());

            //4 维护表第二行表头
            doSheetSecondLine(sheet, table);

            //5 生成基本数据
            //循环resultList  嵌入导出数据
            String idValue = ""; //   表中id数组
            for (int i = 0; i < resultList.size(); i++) {
                XSSFRow row = sheet.createRow(i + 2); //  创建行 放入数据
                Map<String, Object> tableMap = resultList.get(i);
                //  获取每一层数据 并循环
                String[] strings =table.getProperty().split(",");
                for (int j = 0; j < strings.length; j++) {
                   String value = tableMap.get(strings[j]) == null ? "" :tableMap.get(strings[j]).toString();

                   if (table.getId().equals(strings[j])){ //  当循环到主键时
                        if (i == 0){
                            idValue = value;
                        }else{
                            idValue = idValue + "," +value;
                        }
                   }

                    XSSFCell cell = row.createCell(j);
                    cell.setCellValue(value);
                }
            }

            if (table.getChildTableList().size() > 0){ //6  对含有子表数据生成
                int child = doChildSheet(sheetIndex,table.getChildTableList(), workbook,idValue);
                if (child < 0) return -1;
                sheetIndex = child;
            }

            //7 标记导出数据
            int updateCount = doUpdate(table, exchange, resultList, now);
            if (updateCount < 0) return -1;
        }
        return count;
    }

private void doSheetFirstLine(XSSFSheet sheet, Table table, int size) {
        XSSFRow row = sheet.createRow(0); //  创建第一行 放入数据表基本信息
        //   1表名
        row.createCell(0);
        XSSFCell cell1 = sheet.getRow(0).getCell(0);
        cell1.setCellValue(table.getName());
        //   2数目
        row.createCell(1);
        XSSFCell cell2 = sheet.getRow(0).getCell(1);
        cell2.setCellValue(size+"");
        //   3主键
        row.createCell(2);
        XSSFCell cell3 = sheet.getRow(0).getCell(2);
        cell3.setCellValue(table.getId());
        //   4语句
        row.createCell(3);
        XSSFCell cell4 = sheet.getRow(0).getCell(3);
        cell4.setCellValue(table.getSelect());
    }

private void doSheetSecondLine(XSSFSheet sheet, Table table) {
        //拿到属性的数组
        String[] strings =table.getProperty().split(",");
        XSSFRow row = sheet.createRow(1); //  创建第二行 放入数据表属性
        for (int j = 0; j < strings.length; j++) {
            XSSFCell cell = row.createCell(j);
            cell.setCellValue(strings[j]);
        }
    }

private int doUpdate(Table table, Exchange exchange, List<Map<String,Object>> resultList, String now) {
        if(table.getIsUpdateLock().equals("true")){
            String condition = table.getCondition();

            StringBuffer updateSql = new StringBuffer();


            if(table.getUpdateType().equals("isExport")){
                updateSql.append("UPDATE `").append(table.getName()).append("` SET isExport = ").append(exchange.getnId()).append(" WHERE ").append(condition);
            }else{
                updateSql.append("UPDATE `").append(table.getName()).append("` SET isExport = ").append(exchange.getnId()).append(",createTime = '").append(now).append("',updateTime = '").append(now).append("' WHERE ").append(condition);
            }

            int update =  commonMapper.updateSql(updateSql.toString());
            //   导出数据与修改数据不一致时 错误 不能继续执行
            if (update != resultList.size())
                return -1;

        }
        return 1;
    }

 //   子表sheet
    private int doChildSheet(int sheetIndex,List<Table> tableChildList,XSSFWorkbook workbook,String idValue){
        for(int a = 0; a < tableChildList.size(); a++){
            Table table = tableChildList.get(a);

            //通过table 拿到每个导出表的集合
            String sql = table.getSelect();
            sql = sql.replace("{filed}",table.getFiled());  //  替换表字段
            sql = sql.replace("{id}",idValue);  //   替换唯一主键内容
            table.setSelect(sql);
            List<Map<String,Object>> resultList = commonMapper.mapSql(sql);
            if (resultList.size() == 0) continue;

            XSSFSheet sheet;
            if (sheetIndex > (workbook.getNumberOfSheets()-1)){
                sheet = workbook.createSheet();//创建工作簿
            }else {
                sheet = workbook.getSheetAt(sheetIndex);
            }
            workbook.setSheetName(sheetIndex,table.getName());//给指定的sheet命名
            sheetIndex ++;

            //3 维护表第一行综合信息
            doSheetFirstLine(sheet, table, resultList.size());

            //4 维护表第二行表头
            doSheetSecondLine(sheet, table);

            //5 生成基本数据
            //循环resultList  嵌入导出数据
            for (int i = 0; i < resultList.size(); i++) {
                XSSFRow row = sheet.createRow(i + 2); //  创建行 放入数据
                Map<String, Object> tableMap = resultList.get(i);
                //  获取每一层数据 并循环
                String[] strings =table.getProperty().split(",");
                for (int j = 0; j < strings.length; j++) {
                    XSSFCell cell = row.createCell(j);
                    String value = tableMap.get(strings[j]) == null ? "" :tableMap.get(strings[j]).toString();
                    cell.setCellValue(value);
                }
            }

        }
        return sheetIndex;
    }

---

总结

提示：这里对文章进行总结：
以上就是今天要讲的内容，本文仅仅简单介绍了个人工作中对Excel导出的相关内容。具体导出Excel的方法请查看API文档。