下载数据库数据生成excel文件

下载数据库数据生成excel文件：
package com.wisdragon.controller.dataQuality;

import com.google.gson.JsonArray;
import com.google.gson.JsonElement;
import com.google.gson.JsonObject;
import com.google.gson.JsonParser;
import com.wisdragon.comm.constant.Constant;
import com.wisdragon.model.comm.SysActnInfo;
import com.wisdragon.model.dataVerified.MetabaseField;
import com.wisdragon.model.dataVerified.MetabaseTable;
import com.wisdragon.model.dataVerified.Verified;
import com.wisdragon.service.comm.ISysActnInfoService;
import com.wisdragon.service.dataVerified.IMetabaseTableService;
import com.wisdragon.service.dataVerified.IVerifiedService;
import com.wisdragon.utils.JsonMapper;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.net.URLEncoder;
import java.util.*;

import static com.wisdragon.utils.HttpClientUtil.postRestful;
import static com.wisdragon.utils.MbQuery.CreateJson;


/**
 * Description:excel数据校验下载结果
 * @author 韩波
 * @create 2018/10/19
 */
@Controller
@RequestMapping("/downloadExcel/")
public class DownloadTableToExcelController {
    private Workbook wb;
    private Sheet sheet;
    private Row row;
    private Cell cell;
    private CellStyle cellStyle;
    @Autowired
    IVerifiedService verifiedService;
    @Autowired
    IMetabaseTableService metabaseTableService;
    @Autowired
    ISysActnInfoService sysActnInfoService;
    private static final Logger log = LogManager.getLogger(DownloadTableToExcelController.class);
    @ResponseBody
    @RequestMapping(method = {RequestMethod.GET, RequestMethod.POST},value = "downloadExcel/**")
    public int download(HttpServletResponse response, HttpSession session,
                        Integer id) {
        //从session中获得headerMap
        Map<String, String> headerMap = (Map<String, String>)session.getAttribute("headerMap");
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>...headerMap:"+headerMap);

        //通过id获取当前的一行verified信息
        Verified verified=verifiedService.getVerifiedById(id);
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>...verified:"+new JsonMapper().toJson(verified));
        //通过tableId获得对应的数据库的id
        MetabaseTable table=metabaseTableService.getTableById(verified.getTableId());
        log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>...tableDB:"+table.getDbId());
        try {
            //获得表格的名称
            MetabaseTable tableName = metabaseTableService.getTableById(verified.getTableId());
            String fileName = new String( tableName.getName()+".xlsx");
            //获得要下载的表格中列的名称
            List<MetabaseField> colomnName=verifiedService.getAllTablesColomn(verified.getTableId());
            List<String> title = new ArrayList<>();
            for (MetabaseField name:colomnName) {
                title.add(name.getName());
            }
            //获得要下载的表格中的各个数据
            List<String> tableDate = new ArrayList<>();
            List<String> listError = new ArrayList<>();
            //创建查询的Sql语句对应的json对象                                    null==verified.getGenerateSql()
            org.json.JSONObject myJson = new org.json.JSONObject(CreateJson(tableName.getDbId(),null,Constant.MB_LOGINTYPE));
            //通过code获得对应的url
            SysActnInfo urlByCode = sysActnInfoService.getUrlByCode(Constant.MB_EXESQL);
            System.out.println(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>:"+urlByCode.getActnUrl());
            //执行sql，获得查询到的数据
            String res3 = postRestful(urlByCode.getActnUrl(),headerMap,myJson.toString());
            //将得到的数据进行解析
            JsonParser jp = new JsonParser();
            //将json字符串转化成json对象
            JsonObject jo = jp.parse(res3).getAsJsonObject();
            JsonObject rows = jo.get("data").getAsJsonObject();
            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>." + rows);
            Set<Map.Entry<String, JsonElement>> jsonSet = rows.entrySet();
            Iterator<Map.Entry<String, JsonElement>> it = jsonSet.iterator();
            while (it.hasNext()) {
                Map.Entry<String, JsonElement> jsonElementEntry = it.next();
                String key = jsonElementEntry.getKey();
                if (key!=null&&"rows".equals(key)) {
                    JsonElement je = jsonElementEntry.getValue();
                    JsonArray jsonArray = je.getAsJsonArray();
                    log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>jsonArray.size():" + jsonArray.size());
                    for (int i = 0; i < jsonArray.size(); i++) {
                        JsonElement jtmp = jsonArray.get(i);
                        JsonArray jsTmp = jtmp.getAsJsonArray();
                        for (int j = 0; j < jsTmp.size()-1; j++) {
                            JsonElement jetmp = jsTmp.get(j);
                            tableDate.add(jetmp.toString().replace("\"",""));
                            log.info("===i:" + i + "===j:" + j + "-----jetmp:" + jetmp);
                        }
                        log.info("-----jtmp" + jtmp);
                        String sError = jsTmp.toString();
                        String element =sError.substring(sError.lastIndexOf(",")+1,sError.length()-1);
                        log.info("============================================element:" + element);
                        listError.add(element);
                        log.info("============================================listError:" + listError);


                    }
                }
            }
            log.info(">>>>>>>>>>>>>>>>>>>>>>>>>>red:"+tableDate);
            createExcel(title, tableDate);
            ByteArrayOutputStream baos = new ByteArrayOutputStream();
            wb.write(baos);
            response.addHeader("Content-Disposition",   "attachment;filename="+ URLEncoder.encode(fileName, "GBK"));
            response.setContentLength(baos.size());
            ServletOutputStream sos = response.getOutputStream();
            baos.writeTo(sos);
            baos.close();
            sos.flush();

        } catch (IOException e) {
            e.printStackTrace();
        }
        return 1;
    }

    public void createExcel(List<String> titleList, List<String> redList) {
        wb = new XSSFWorkbook();
        sheet = wb.createSheet();
        //行号
        int startRow = 0;
        int startcol = 0;
        //列号
        int endcol = 0;
        //字体
        Font font = wb.createFont();
        //字体高度
        font.setFontHeightInPoints((short)20);
        //字体颜色
        font.setColor(Font.COLOR_NORMAL);
        //单元格
        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setWrapText(true);
        endcol = titleList.size();
        log.info(titleList.toString());
        startRow = addData(titleList, endcol, endcol, startRow);
        log.info(startRow);
        startRow = addData(redList, endcol, endcol, startRow);
        log.info(startRow);

    }
    public int addData(List<String> list, int start, int end, int srow) {
        XSSFDataValidationHelper xdvh;
        XSSFDataValidationConstraint xdvC;
        CellRangeAddressList reg;
        DataValidation dv;
        for(int i=0 ; i<list.size(); i++) {
            if(start == end) {
                start = 0;
                row = sheet.createRow(srow++);
            }
            cellStyle = wb.createCellStyle();
            if(Integer.toString(2).equals(list.get(i))) {
                cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                xdvh = new XSSFDataValidationHelper((XSSFSheet) sheet);
                xdvC = (XSSFDataValidationConstraint) xdvh.createCustomConstraint("error");
                reg = new CellRangeAddressList(srow-1, srow-1, i, i);
                dv = xdvh.createValidation(xdvC, reg);
                dv.createPromptBox("错误提示：","错误内容");
                dv.setShowPromptBox(true);
                sheet.addValidationData(dv);
            }
            cell = row.createCell(start++);
            cell.setCellValue(list.get(i));
            cell.setCellStyle(cellStyle);
        }
        return srow;
    }

}

声明：此博客为个人学习之用，如与其他作品雷同，纯属巧合，转载请指明出处！