下载数据库数据生成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; } }
声明：此博客为个人学习之用，如与其他作品雷同，纯属巧合，转载请指明出处！
