qq_29586781
心晴天
采纳率53.8%
2018-05-15 01:55 阅读 1.2k

ssm 中导出联合查询结果集的 Excel

40

已经通过联合查询将结果集存储在 Map 中,
现在需要将 map中的部分数据作为 excel 的列名,然后导出 excel,
网上搜索安利,大都是获取 Bean 中的数据,很少有以查询的结果集作为数据源的,
谢谢,

谢谢大家的帮助,这个问题解决了,参考了: ‘不会代码的小白’ 的安利
[SSM 数据库数据导出excel](https://www.cnblogs.com/xswz/p/7257658.html "")
    //实现类
    @Override
    public List<Map<String, String>> 
    selectAllAssetInlibraryInfo(String pkweeksetmenu) {
            List<Map<String, String>> getFspendByTem=
                    new ArrayList<Map<String, String>>();
            getFspendByTem = this.ifspendrecordService.
                    getFspendByTemStr(pkweeksetmenu);
            List<Map<String, String>> mapList = 
                    new ArrayList<Map<String,String>>();
            if (getFspendByTem.size() >0) {
            for (Map<String, String> map : getFspendByTem) {
             Map<String, String> mapA = new HashMap<String, String>();
                String payamount = (String)map.get("payamount");
                String weekmenun = map.get("setmenu");//套餐名称
                String menun = map.get("menu");//菜单名称
                String menuname = weekmenun + "  " +menun;
                mapA.put("手机号", map.get("phone"));
                mapA.put("中文名", map.get("psnname"));
                mapA.put("退餐详情", menuname);
                mapA.put("退款金额", payamount);
                mapList.add(mapA);
            }
        }
        return mapList;
    }
//controller
    @RequestMapping("exportcs")
    public ModelAndView exportcs(HttpServletRequest request,
                String pkweekmenu, ModelMap map) throws Exception{
        List<Map<String,String>> list =
                fweeksetmenuService.selectAllAssetInlibraryInfo(pkweekmenu);
            String[] titles={"手机号","中文名","退餐详情","退款金额"};
            ViewExcel excel=new ViewExcel(titles);
            map.put("excelList", list);
            return new ModelAndView(excel,map);
    }

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

5条回答 默认 最新

  • soulout soulout 2018-05-15 01:57

    先,这是我对自己的需求而使用的逻辑,若有可以完美的地方方便告诉下小白。
    apache的poi MAVEN


    org.apache.poi
    poi
    3.16

    1、前端页面,伪异步(页面不刷新)
    为什么不用ajax呢?
    JQuery的ajax函数的返回类型只有xml、text、json、html等类型,没有“流”类型。所以就用js做个form表单请求
    上代码()

    1 function exportExcel(){
    2 var myurl="${context}/assetInLibrary/export";
    3 var form=$("

    ");
    4 form.attr("style","display:none");
    5 form.attr("method","post");
    6 form.attr("action",myurl);
    7 $("body").append(form);
    8 }

    2、在工具包中创建ViewExcel,继承AbstractExcelView
    先上代码

    1 public class ViewExcel extends AbstractExcelView {
    2
    3 private String[] titles;
    4

    5 //传入指定的标题头
    6 public ViewExcel(String[] titles) {
    7 this.titles=titles;
    8 }
    9

    10 @Override
    11 protected void buildExcelDocument(Map model,
    12 HSSFWorkbook workbook, HttpServletRequest request,
    13 HttpServletResponse response) throws Exception {
    14 //获取数据
    15 List> list = (List>) model.get("excelList");
    16 //在workbook添加一个sheet
    17 HSSFSheet sheet = workbook.createSheet();
    18 sheet.setDefaultColumnWidth(15);
    19 HSSFCell cell=null;
    20 //遍历标题
    21 for (int i = 0; i < titles.length; i++) {
    22 //获取位置
    23 cell = getCell(sheet, 0, i);
    24 setText(cell, titles[i]);
    25 }
    26 //数据写出
    27 for (int i = 0; i < list.size(); i++) {
    28 //获取每一个map
    29 Map map=list.get(i);
    30 //一个map一行数据
    31 HSSFRow row = sheet.createRow(i+1);
    32 for (int j = 0; j < titles.length; j++) {
    33 //遍历标题,把key与标题匹配
    34 String title=titles[j];
    35 //判断该内容存在mapzhong
    36 if(map.containsKey(title)){
    37 row.createCell(j).setCellValue(map.get(title));
    38 }
    39 }
    40 }
    41 //设置下载时客户端Excel的名称

    42 String filename = new SimpleDateFormat("yyyy-MM-dd").format(new Date())+".xls";

    43 response.setContentType("application/vnd.ms-excel");

    44 response.setHeader("Content-disposition", "attachment;filename=" + filename);
    45 OutputStream ouputStream = response.getOutputStream();

    46 workbook.write(ouputStream);

    47 ouputStream.flush();

    48 ouputStream.close();

    49 }
    50
    51 }

    在构造函数中传进来需导出的titles,也就是excel中的标题头,这个逻辑会有点麻烦,因为我是创建Map,让dao中查出来的数据根据我的Map(‘title’,'value')进行封装,且title要存在于传进来的titles中,剩下看源码就能明白
    3、service中的数据封装

    1 public List> selectAllAssetInlibraryInfo() {
    2 List list = assetInlibraryMapper.selectByExample(null);
    3 List> mapList=new ArrayList>();
    4 for (AssetInlibrary assetInlibrary : list) {
    5 Map map=new HashMap();
    6 map.put("编号", assetInlibrary.getId()+"");
    7 map.put("资产名称", assetInlibrary.getTitle());
    8 AssetType assetType = assetTypeMapper.selectByPrimaryKey(assetInlibrary.getAssetTypeId());
    9 map.put("资产类型", assetType.getTitle());
    10 AssetBrand assetBrand = assetBrandMapper.selectByPrimaryKey(assetInlibrary.getAssetBrandId());
    11 map.put("资产品牌", assetBrand.getTitle());
    12 AssetStorage assetStorage = assetStorageMapper.selectByPrimaryKey(assetInlibrary.getAssetStorageId());
    13 map.put("资产存放地点", assetStorage.getTitle());
    14 AssetProvider assetProvider = assetProviderMapper.selectByPrimaryKey(assetInlibrary.getAssetProviderId());
    15 map.put("资产供应商", assetProvider.getTitle());
    16 mapList.add(map);
    17 }
    18 return mapList;
    19 }

    4、controller中的数据交互

    1 @RequestMapping("/assetInLibrary/export")
    2 public ModelAndView export(ModelMap map) throws Exception{
    3 List> list = assetInLibraryService.selectAllAssetInlibraryInfo();
    4 String[] titles={"编号","资产名称","资产类型","资产品牌","资产存放地点","资产供应商"};
    5 ViewExcel excel=new ViewExcel(titles);
    6 map.put("excelList", list);
    7 return new ModelAndView(excel,map);
    8 }

    点赞 1 评论 复制链接分享
  • a718089112 a718089112 2018-05-15 02:09

    你这个实现需要2个点:
    1,map循环遍历,参考:https://blog.csdn.net/zhu1qiu/article/details/71170850
    2,生成execl,每个jar包写法不一样,具体需要看你引用的jar包了,给你一个poi的例子,参考:https://blog.csdn.net/evangel_z/article/details/7332535

    你在map循环遍历时,创建execl行和列就行了

    点赞 1 评论 复制链接分享
  • u012119658 hh20 2018-05-15 02:24

    前端时间正好做过。jxl.jar
    获取数据:
    public void getData() throws IOException {
    response.setCharacterEncoding("utf-8");
    JSONObject json = new JSONObject();
    try {
    HashMap param = ParameterUtil
    .convertRequestParameter(request);
    HashMap map = prd.selectOne(
    "templateEntry.getQueryCfg", param);
    map.put("acct", param.get("acct"));
    List> list = null;
    if (param.get("pagination").equals("true")) {
    RowBoundsPerPage rb = new RowBoundsPerPage(rows * (page - 1),
    rows, true);
    list = prd.selectList("templateEntry.getData", map, rb);
    json.put("total", rb.getTotal());
    } else {
    list = prd.selectList("templateEntry.getData", map);
    }
    json.put("rows", list);
    } catch (Exception e) {
    e.printStackTrace();
    json = JSONObject.fromObject("{total:0,rows:[]}");
    }
    response.getWriter().write(json.toString());
    }

    模板生成
    public void getTemplate() throws IOException {
        response.setCharacterEncoding("utf-8");
        try {
            JSONArray data = JSONArray.fromObject(request.getParameter("data"));
            String columns = request.getParameter("columns");
            String frozen = request.getParameter("frozen");
            String ft = request.getParameter("fileType");
            JSONArray fields = JSONArray.fromObject(request
                    .getParameter("fields"));
            String name = new String(request.getParameter("fileName").getBytes(
                    "GBK"), "ISO-8859-1");
            response.reset();
            response.setContentType("application/ms-excel");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + name + "." + ft);
            OutputStream os = response.getOutputStream();
            if (!ft.equals("csv")) {
                JSONObject j = JSONObject.fromObject(request
                        .getParameter("hidden"));
                WriteXlsTable(columns, frozen, fields, j, data, os);
            } else {
                WriteCsvTable(columns, frozen, fields, data, os);
            }
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
            response.getWriter().write("error");
        }
    }
    
    写xls
    public void WriteXlsTable(String cols, String frozen, JSONArray fields,
            JSONObject hidden, JSONArray data, OutputStream os)
            throws WriteException, IOException {
        int DEFAULT_CELL_WIDTH = 10;
        WritableWorkbook wwb = Workbook.createWorkbook(os);
        WritableSheet ws = wwb.createSheet("sheet1", 0);
        // 写标题栏
        XlsUtil.writeTitle(ws, frozen);
        int y = XlsUtil.writeTitle(ws, cols);
        // 写数据
        WritableCellFormat wcf = new WritableCellFormat();
        wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
        Integer[] colsWidth = new Integer[fields.size()];
        for (int i = 0; i < data.size(); i++) {
            JSONObject row = data.getJSONObject(i);
            for (int j = 0; j < fields.size(); j++) {
                String field = fields.getString(j);
                Object val = row.get(field);
                if (val == null)
                    val = "";
                if (val instanceof Integer || val instanceof BigDecimal
                        || val instanceof Float || val instanceof Double)
                    ws.addCell(new Number(j, i + y, Double.valueOf(val
                            .toString()), wcf));
                else
                    ws.addCell(new Label(j, i + y, val.toString(), wcf));
                if (hidden.containsKey(field))
                    colsWidth[j] = 0;
                else {
                    int w1 = val.toString().getBytes().length;
                    int w2 = colsWidth[j] == null ? DEFAULT_CELL_WIDTH
                            : colsWidth[j];
                    colsWidth[j] = w2 < w1 ? w1 : w2;
                }
            }
        }
        for (int i = 0; i < colsWidth.length; i++) {
            if (colsWidth[i] != null)
                ws.setColumnView(i, colsWidth[i]);
            else
                ws.setColumnView(i, DEFAULT_CELL_WIDTH);
        }
        wwb.write();
        wwb.close();
        os.flush();
        os.close();
    }
    
    写title
    public static int writeTitle(WritableSheet ws, String titleCfg)
            throws WriteException {
        if (titleCfg == null || titleCfg.trim().equals(""))
            return 0;
    
        List<List<String[]>> title = new ArrayList<List<String[]>>();
        String[] trs = titleCfg.split("!");
        for (int i = 0; i < trs.length; i++) {
            String[] tds = trs[i].split(";");
            List<String[]> l = new ArrayList<String[]>();
            for (int j = 0; j < tds.length; j++) {
                l.add(tds[j].split(","));
            }
            title.add(l);
        }
        WritableCellFormat wcf = new WritableCellFormat();
        wcf.setAlignment(Alignment.CENTRE);
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf.setBorder(Border.ALL, BorderLineStyle.THIN);
        wcf.setWrap(true);
        int pos = ws.getColumns(), x = pos, y = 0, minRspan = 0;
        for (List<String[]> tr : title) {
            for (String[] td : tr) {
                x = setCellOffset(ws.getMergedCells(), x, y);
                int rowspan = Integer.valueOf(td[1]);
                int colspan = Integer.valueOf(td[2]);
                if (rowspan > 1 || colspan > 1)
                    ws.mergeCells(x, y, x + colspan - 1, y + rowspan - 1);
                if (td[3].equals("false")) // 非隐藏列
                    ws.addCell(new Label(x, y, td[0], wcf));
                minRspan = minRspan == 0 ? rowspan
                        : (rowspan < minRspan ? rowspan : minRspan);
                x += colspan;
            }
            x = pos;
            y += minRspan;
        }
        return y;
    }
    
    点赞 评论 复制链接分享
  • m0_37946870 联系 qq: 1120972968 2018-05-15 02:45

    图片说明

    点赞 评论 复制链接分享
  • qq_20989105 Little Coder 2018-05-15 03:12

    poi是可以实现的,自己写方法,这是我以前写的一个工具类,稍微改一下就好了。
    package com.util;
    import java.io.File;
    import java.io.FileNotFoundException;
    import java.io.FileOutputStream;
    import java.io.IOException;
    import java.io.OutputStream;
    import java.lang.reflect.Method;
    import java.net.URLEncoder;
    import java.text.DecimalFormat;
    import java.util.List;

    import javax.servlet.http.HttpServletResponse;

    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFCellStyle;
    import org.apache.poi.hssf.usermodel.HSSFFont;
    import org.apache.poi.hssf.usermodel.HSSFPalette;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.CellStyle;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddress;

    public class ExportExcel {
    HttpServletResponse response;
    // 文件名
    private String fileName ;
    //文件保存路径
    private String fileDir;
    //sheet名
    private int rowheigth = 1400;

    private String formtitle="教师课表";
    
    //表头字体
    private String titleFontType = "Arial Unicode MS";
    //表头背景色
    private String titleBackColor = "";
    //表头字号
    private short titleFontSize = 12;
    //添加自动筛选的列 如 A:M
    private String address = "";
    //正文字体
    private String contentFontType = "Arial Unicode MS";
    //正文字号
    private short contentFontSize = 11;
    //设置列的公式
    private String colFormula[] = null;
    
    private String data[][] = null;
    
    
    private HSSFWorkbook workbook = null;
    
    public ExportExcel(String fileDir){
         this.fileDir = fileDir;
         workbook = new HSSFWorkbook();
    }
    
    public ExportExcel(HttpServletResponse response,String fileName){
         this.response = response;
         this.fileName = fileName;
         workbook = new HSSFWorkbook();
    }
    /**
     * 设置表头字体.
     * @param titleFontType
     */
    public void setTitleFontType(String titleFontType) {
        this.titleFontType = titleFontType;
    }
    public void setFormtitle(String formtitle) {
        this.formtitle = formtitle;
    }
    /**
     * 设置表头背景色.
     * @param titleBackColor 十六进制
     */
    public void setTitleBackColor(String titleBackColor) {
        this.titleBackColor = titleBackColor;
    }
    public void setData(String[][] data) {
        this.data = data;
    }
    /**
     * 设置表头字体大小.
     * @param titleFontSize
     */
    public void setTitleFontSize(short titleFontSize) {
        this.titleFontSize = titleFontSize;
    }
    /**
     * 设置表头自动筛选栏位,如A:AC.
     * @param address
     */
    public void setAddress(String address) {
        this.address = address;
    }
    /**
     * 设置正文字体.
     * @param contentFontType
     */
    public void setContentFontType(String contentFontType) {
        this.contentFontType = contentFontType;
    }
    /**
     * 设置正文字号.
     * @param contentFontSize
     */
    public void setContentFontSize(short contentFontSize) {
        this.contentFontSize = contentFontSize;
    }
    /**
     * 设置列的公式 
     * @param colFormula  存储i-1列的公式 涉及到的行号使用@替换 如A@+B@
     */
    public void setColFormula(String[] colFormula) {
        this.colFormula = colFormula;
    }
    /*
     * 设置行高
     */
    public void setRowheigth(int rowheigth) {
        this.rowheigth = rowheigth;
    }
    /**
     * 写excel.
     * @param titleColumn  对应bean的属性名
     * @param titleName   excel要导出的表名
     * @param titleSize   列宽
     * @param dataList  数据
     */
    public void wirteExcel(String sheetName[],String titleName[][],int titleSize[],String[][][] dataList){
        //添加Worksheet(不添加sheet时生成的xls文件打开时会报错)
        OutputStream out = null;
        try {
            out = response.getOutputStream();
            fileName = fileName+".xls";
            response.setContentType("application/x-msdownload");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + URLEncoder.encode(fileName, "UTF-8"));
        } catch (IOException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
    
    
        for(int sheetindex = 0; sheetindex < 20; sheetindex++){
            Sheet sheet = workbook.createSheet(sheetName[sheetindex]);  
            try {    
    
                //大标题
                Row titleRow = workbook.getSheet(sheetName[sheetindex]).createRow(0); 
                titleRow.setHeight((short) 700);
                HSSFCellStyle titleStyle1 = workbook.createCellStyle();  
                titleStyle1 = (HSSFCellStyle) setFont(titleStyle1, "Microsoft YaHei UI", (short) 20);
                titleStyle1.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
                titleStyle1.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中
                Cell cell1=titleRow.createCell(0);
                cell1.setCellStyle(titleStyle1);
                cell1.setCellValue(formtitle+"——"+sheetName[sheetindex]);
                CellRangeAddress region=new CellRangeAddress(0, 0, 0, 7);
                sheet.addMergedRegion(region);
    
                //表头
                Row titleNameRow = workbook.getSheet(sheetName[sheetindex]).createRow(1); 
                HSSFCellStyle titleStyle = workbook.createCellStyle();  
                titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, titleFontType, (short) titleFontSize);
                titleStyle = (HSSFCellStyle) setColor(titleStyle, titleBackColor, (short)10);
                for(int i = 0;i < 8;i++){
                    sheet.setColumnWidth(i, titleSize[i]*256); //设置宽度   
                    Cell cell = titleNameRow.createCell(i);
                    cell.setCellStyle(titleStyle);
                    cell.setCellValue(titleName[sheetindex][i].toString());
                }
    
                //为表头添加自动筛选
                if(!"".equals(address)){
                    CellRangeAddress c = (CellRangeAddress) CellRangeAddress.valueOf(address);
                    sheet.setAutoFilter(c);
                }
    
                //通过反射获取数据并写入到excel中
                if(dataList!=null){
                    //设置样式
                    HSSFCellStyle dataStyle = workbook.createCellStyle();  
                    titleStyle = (HSSFCellStyle) setFontAndBorder(titleStyle, contentFontType, (short) contentFontSize);
                    titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
                    titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 
                    HSSFCellStyle titleStyle0 = workbook.createCellStyle();  
                    titleStyle0 = (HSSFCellStyle) setFontAndBorder1(titleStyle0, contentFontType, (short) contentFontSize);
                    titleStyle0.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中
                    titleStyle0.setWrapText(true);
                    for(int rowIndex = 2;rowIndex<8;rowIndex++){
                        Row dataRow = workbook.getSheet(sheetName[sheetindex]).createRow(rowIndex);
                        dataRow.setHeight((short) rowheigth);
                        for(int columnIndex = 0;columnIndex<8;columnIndex++){
                            Cell cell = dataRow.createCell(columnIndex);
                            cell.setCellStyle(titleStyle0);
                            cell.setCellValue(dataList[sheetindex][rowIndex-2][columnIndex]);
                        }
    
                    }
    
                }
    
    
            } catch (Exception e) {
                e.printStackTrace();
            }
    
        }
        try {
            workbook.write(out);
            out.close();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }
    
    /**
     * 将16进制的颜色代码写入样式中来设置颜色
     * @param style  保证style统一
     * @param color 颜色:66FFDD
     * @param index 索引 8-64 使用时不可重复
     * @return
     */
    public CellStyle setColor(CellStyle style,String color,short index){
        if(color!=""&&color!=null){
            //转为RGB码
            int r = Integer.parseInt((color.substring(0,2)),16);   //转为16进制
            int g = Integer.parseInt((color.substring(2,4)),16);
            int b = Integer.parseInt((color.substring(4,6)),16);
            //自定义cell颜色
            HSSFPalette palette = workbook.getCustomPalette(); 
            palette.setColorAtIndex((short)index, (byte) r, (byte) g, (byte) b);
    
            style.setFillPattern(CellStyle.SOLID_FOREGROUND); 
            style.setFillForegroundColor(index);
        }
        return style;   
    }
    
    /**
     * 设置字体并加外边框
     * @param style  样式
     * @param style  字体名
     * @param style  大小
     * @return
     */
    public CellStyle setFontAndBorder(CellStyle style,String fontName,short size){
        HSSFFont font = workbook.createFont();  
        font.setFontHeightInPoints(size);    
        font.setFontName(fontName); 
        font.setBold(true);
        style.setFont(font);
        style.setBorderBottom(CellStyle.BORDER_THIN); //下边框    
        style.setBorderLeft(CellStyle.BORDER_THIN);//左边框    
        style.setBorderTop(CellStyle.BORDER_THIN);//上边框    
        style.setBorderRight(CellStyle.BORDER_THIN);//右边框   
        return style;
    }
    public CellStyle setFontAndBorder1(CellStyle style,String fontName,short size){
        HSSFFont font = workbook.createFont();  
        font.setFontHeightInPoints(size);    
        font.setFontName(fontName); 
        style.setFont(font);
        style.setBorderBottom(CellStyle.BORDER_THIN); //下边框    
        style.setBorderLeft(CellStyle.BORDER_THIN);//左边框    
        style.setBorderTop(CellStyle.BORDER_THIN);//上边框    
        style.setBorderRight(CellStyle.BORDER_THIN);//右边框   
        return style;
    }
    public CellStyle setFont(CellStyle style,String fontName,short size){
        HSSFFont font = workbook.createFont();  
        font.setFontHeightInPoints(size);    
        font.setFontName(fontName); 
        font.setBold(true);
        style.setFont(font);
        return style;
    }
    /**
     * 删除文件
     * @param fileDir
     * @return
     */
    public boolean deleteExcel(){
        boolean flag = false;
        File file = new File(this.fileDir);
        // 判断目录或文件是否存在  
        if (!file.exists()) {  // 不存在返回 false  
            return flag;  
        } else {  
            // 判断是否为文件  
            if (file.isFile()) {  // 为文件时调用删除文件方法  
                file.delete();
                flag = true;
            } 
        }
        return flag;
    }
    /**
     * 删除文件
     * @param fileDir
     * @return
     */
    public boolean deleteExcel(String path){
        boolean flag = false;
        File file = new File(path);
        // 判断目录或文件是否存在  
        if (!file.exists()) {  // 不存在返回 false  
            return flag;  
        } else {  
            // 判断是否为文件  
            if (file.isFile()) {  // 为文件时调用删除文件方法  
                file.delete();
                flag = true;
            } 
        }
        return flag;
    }
    

    }

    
    
    点赞 评论 复制链接分享

相关推荐