做项目的时候遇到这样的需求:
一个表单,输入信息点击查询得到一个table,然后导出table里的内容到excel,让用户下载。如下图:

需要解决的问题:
1、如何让一个form提交两次?(因为导出excel按钮是要根据查询后的结果导出)
解决办法:第一次提交用jQuery的submit,第二次提交用onclick=”doExport()”
<form class="form-horizontal" id="searchForm">
。。。
</form>
<script>
function doExport(){
document.getElementById("searchForm").action ='<%=basePath%>exportLessonBuildStatisticExcel.htm';
document.getElementById("searchForm").submit();
}
$(function(){
$('#searchForm').submit(function(e) {
$.post('certifApply.json', $(this).serialize(), function(response) {
$(".table-responsive").empty();
if (response.applyList != null && response.applyList != '' ) {
$('.table-responsive').append('<table class="table table-striped table-bordered table-hover" id="dataTables-example"></table>');
var html = '<thead><tr><th>批次</th><th>学期</th><th>课程名称</th><th>课程编号</th><th>教师</th><th>教工号</th><th>申请级别</th><th>建设情况</th></tr></thead>';
$.each(response.applyList, function (n, apply) {
html += '<tr><td>' + apply.applyBatch + '</td>';
html += '<td>' + apply.termcode +'</td>';
html += '<td>' + apply.title +'</td>';
html += '<td>' + apply.courseCode +'</td>';
html += '<td>' + apply.chiefTeacherName +'</td>';
html += '<td>' + apply.teacherid +'</td>';
html += '<td>' + apply.applyLevel +'</td>';
html += '<td><a target="_blank" href="lessonBuildStatistic.htm?siteid='+apply.siteid+'&termcode='+apply.termcode+'&teacherid='+apply.teacherid+'">详情链接</a></td></tr>';
});
$('#dataTables-example').append(html);
$('#dataTables-example').dataTable( {
"oLanguage": {
"sLengthMenu": "每页显示 _MENU_ 条记录",
"sZeroRecords": "抱歉, 没有找到",
"sInfo": "从 _START_ 到 _END_ /共 _TOTAL_ 条数据",
"sInfoEmpty": "没有数据",
"sInfoFiltered": "(从 _MAX_ 条数据中检索)",
"oPaginate": {
"sFirst": "首页",
"sPrevious": "前一页",
"sNext": "后一页",
"sLast": "尾页"
},
"sZeroRecords": "没有检索到数据",
"sProcessing": "<img src='./loading.gif' />"
}
}
);
$('.table-responsive').append('<a class="btn btn-success" type="button" onclick="doExport()">导出课程建设详情Excel</a>')
} else {
$('.table-responsive').append("没有查找到结果")
}
});
e.preventDefault();
});
});
</script>
2、导出的文件是html后缀不是xls
解决办法:
response.setHeader("Content-Disposition",attachment;filename=" + new String((fileName + ".xls").getBytes("gb2312"), "ISO-8859-1"));
3、如何导出excel?
首先你需要一个工具类,如下:
import java.io.OutputStream
import java.lang.reflect.Method
import java.text.SimpleDateFormat
import java.util.Date
import java.util.List
import java.util.regex.Matcher
import java.util.regex.Pattern
import org.apache.poi.hssf.usermodel.HSSFCell
import org.apache.poi.hssf.usermodel.HSSFCellStyle
import org.apache.poi.hssf.usermodel.HSSFClientAnchor
import org.apache.poi.hssf.usermodel.HSSFFont
import org.apache.poi.hssf.usermodel.HSSFPatriarch
import org.apache.poi.hssf.usermodel.HSSFRichTextString
import org.apache.poi.hssf.usermodel.HSSFRow
import org.apache.poi.hssf.usermodel.HSSFSheet
import org.apache.poi.hssf.usermodel.HSSFWorkbook
import org.apache.poi.hssf.util.HSSFColor
import org.apache.poi.hssf.util.Region
public class ExcelUtil<T> {
public void exportExcel(String title, String[] headers, String[] columns,
List<T> result, OutputStream out, String pattern, int N)
throws Exception {
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook()
HSSFCellStyle style = getCellStyle1(workbook)
HSSFCellStyle titleStyle = getTitleStyle1(workbook)
int totalSize = result.size()
for (int sheetNum = 0
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(title + (sheetNum + 1))
// 设置表格默认列宽度为20个字节
sheet.setDefaultColumnWidth((short) 20)
// 声明一个画图的顶级管理器
HSSFPatriarch patriarch = sheet.createDrawingPatriarch()
sheet.addMergedRegion(new Region(0, (short) 0, 0,
(short) (headers.length - 1)))
HSSFRow rowHeader = sheet.createRow(0)
HSSFCell cellHeader = rowHeader.createCell((short) 0)
HSSFRichTextString textHeader = new HSSFRichTextString(title)
cellHeader.setCellStyle(titleStyle)
cellHeader.setCellValue(textHeader)
HSSFRow row = sheet.createRow(1)
for (int i = 0
HSSFCell cell = row.createCell((short) i)
cell.setCellStyle(style)
HSSFRichTextString text = new HSSFRichTextString(headers[i])
cell.setCellValue(text)
}
// 遍历集合数据,产生数据行
if (result != null) {
int index = 2
// for(T t:result){
for (int m = sheetNum * N
&& m < totalSize
T t = result.get(m)
// Field[] fields = t.getClass().getDeclaredFields()
row = sheet.createRow(index)
index++
for (short i = 0
HSSFCell cell = row.createCell(i)
// Field field = fields[i]
// String fieldName = field.getName()
String fieldName = columns[i]
String getMethodName = "get"
+ fieldName.substring(0, 1).toUpperCase()
+ fieldName.substring(1)
Class tCls = t.getClass()
Method getMethod = tCls.getMethod(getMethodName, new Class[] {})
// getMethod.getReturnType().isInstance(obj)
Object value = getMethod.invoke(t, new Class[] {})
String textValue = null
if (value == null) {
textValue = ""
} else if (value instanceof Date) {
Date date = (Date) value
SimpleDateFormat sdf = new SimpleDateFormat(pattern)
textValue = sdf.format(date)
} else if (value instanceof byte[]) {
// 有图片时,设置行高为60px
row.setHeightInPoints(60)
// 设置图片所在列宽度为80px,注意这里单位的一个换算
sheet.setColumnWidth(i, (short) (35.7 * 80))
// sheet.autoSizeColumn(i)
byte[] bsValue = (byte[]) value
HSSFClientAnchor anchor = new HSSFClientAnchor(0,
0, 1023, 255, (short) 6, index, (short) 6,
index)
anchor.setAnchorType(2)
patriarch.createPicture(anchor, workbook
.addPicture(bsValue,
HSSFWorkbook.PICTURE_TYPE_JPEG))
} else {
// 其它数据类型都当作字符串简单处理
textValue = value.toString()
}
if (textValue != null) {
Pattern p = Pattern.compile("^//d+(//.//d+)?$")
Matcher matcher = p.matcher(textValue)
if (matcher.matches()) {
// 是数字当作double处理
cell.setCellValue(Double.parseDouble(textValue))
} else {
HSSFRichTextString richString = new HSSFRichTextString(
textValue)
// HSSFFont font3 = workbook.createFont()
// font3.setColor(HSSFColor.BLUE.index)
// richString.applyFont(font3)
cell.setCellValue(richString)
}
}
}
}
}
}
workbook.write(out)
// 清空并关闭输出流
out.flush()
out.close()
}
private HSSFCellStyle getCellStyle1(HSSFWorkbook workbook) {
// 生成一个样式
HSSFCellStyle style = workbook.createCellStyle()
// 设置这些样式
style.setFillForegroundColor(HSSFColor.GOLD.index)
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
style.setBorderBottom(HSSFCellStyle.BORDER_THIN)
style.setBorderLeft(HSSFCellStyle.BORDER_THIN)
style.setBorderRight(HSSFCellStyle.BORDER_THIN)
style.setBorderTop(HSSFCellStyle.BORDER_THIN)
style.setAlignment(HSSFCellStyle.ALIGN_CENTER)
// 生成一个字体
HSSFFont font = workbook.createFont()
font.setColor(HSSFColor.VIOLET.index)
// font.setFontHeightInPoints((short) 12)
font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
// 把字体应用到当前的样式
style.setFont(font)
// 指定当单元格内容显示不下时自动换行
style.setWrapText(true)
return style
}
private HSSFCellStyle getTitleStyle1(HSSFWorkbook workbook) {
// 产生表格标题行
// 表头的样式
HSSFCellStyle titleStyle = workbook.createCellStyle()
titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER_SELECTION)
titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)
// 设置字体
HSSFFont titleFont = workbook.createFont()
titleFont.setFontHeightInPoints((short) 15)
titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD)
// titleFont.setFontName("黑体")
titleStyle.setFont(titleFont)
return titleStyle
}
public HSSFCellStyle getCellStyle2(HSSFWorkbook workbook) {
// 生成并设置另一个样式
HSSFCellStyle style2 = workbook.createCellStyle()
style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index)
style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND)
style2.setBorderBottom(HSSFCellStyle.BORDER_THIN)
style2.setBorderLeft(HSSFCellStyle.BORDER_THIN)
style2.setBorderRight(HSSFCellStyle.BORDER_THIN)
style2.setBorderTop(HSSFCellStyle.BORDER_THIN)
style2.setAlignment(HSSFCellStyle.ALIGN_CENTER)
style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER)
// 生成另一个字体
HSSFFont font2 = workbook.createFont()
font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL)
// 把字体应用到当前的样式
style2.setFont(font2)
return style2
}
}
然后在Spring MVC中contorllor里加入这样的方法

js这样写
function doExport(){
document.getElementById("searchForm").action='<%=basePath%>xxx.htm';
document.getElementById("searchForm").submit();
}
总结一下:网上帖子很多但大多数没啥用,这里分享给大家也不枉了花了2天的时间解决这个问题。