ssm 中导出联合查询结果集的 Excel
已经通过联合查询将结果集存储在 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);
}
先，这是我对自己的需求而使用的逻辑，若有可以完美的地方方便告诉下小白。
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 }
你这个实现需要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行和列就行了
前端时间正好做过。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;
}
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;
}
}