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

已经通过联合查询将结果集存储在 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个回答

先,这是我对自己的需求而使用的逻辑,若有可以完美的地方方便告诉下小白。
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 }

qq_38470623
一抹蓝2 不过 有ajax提交from的 方法
2 年多之前 回复
qq_38470623
一抹蓝2 导出不能用ajax
2 年多之前 回复
qq_29586781
心晴天 可已将链接发给我么?行号看着很扎眼
2 年多之前 回复

你这个实现需要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行和列就行了

qq_29586781
心晴天 好的,我看下
2 年多之前 回复

前端时间正好做过。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;
}
qq_29586781
心晴天 非常感谢,正在迷惑中,我等下试试
2 年多之前 回复

图片说明

m0_37946870
qq 1120972968 你要是map的的话 改改遍历就可以了
2 年多之前 回复

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;
}

}


Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问
相关内容推荐