首先是控制层Controller
/**
* 导出状态
*/
private boolean excelFlag = true;
/**
* 导出excel
*
* @param response
* @param keyword 关键字
* @param type 类型
* @param state 状态
* @param startTime 开始时间
* @param endTime 结束时间
*/
@RequestMapping(value = "/exportExcel")
public void exportOrder(HttpServletResponse response,
String keyword, String type, String state,
String startTime, String endTime) {
SearchVO vo = new SearchVO ();
// 查询参数
vo.setKeyword(keyword);
vo.setState(state);
vo.setType(type);
vo.setStartTime(startTime);
vo.setEndTime(endTime);
excelFlag = true;
String[] header = {"姓名", "地址", "联系电话", "订单金额", "商品名", "购买日期", "订单状态", "订单类型", "创建时间"};
String[] columsNames = {"customer_name", "address","phone", "order_money", "product_name", "buy_date","order_state", "order_type", "create_time"};
List<Map<String, Object>> dataList = service.search(vo);
ExcelUtils.exportExcel("订单列表.xlsx", "sheet", header, columsNames, dataList, response);
excelFlag = false;
}
/**
* 获取导出状态
*
* @return
*/
@RequestMapping("/getExcelFlag")
@ResponseBody
public String getExportFlag() {
return String.valueOf(excelFlag);
}
excel工具类
/**
* excel工具类
*/
public class ExcelUtils {
private static final Logger log = LoggerFactory.getLogger(ExcelUtils.class);
/**
* 导出excel
* @param fileName 文件名（*.xsl）
* @param sheetName sheet名
* @param headers 头部
* @param columsNames 实体属性名
* @param dataList 数据列表
* @param response
*/
public static void exportExcel(String fileName, String sheetName, String[] headers, String[] columsNames, List<Map<String, Object>> dataList, HttpServletResponse response) {
XSSFWorkbook wb = new XSSFWorkbook();
// 创建头部样式
XSSFCellStyle headerStyle = wb.createCellStyle();
// 创建字体样式
XSSFFont headerFont = wb.createFont();
// 字体加粗：0x2bc
headerFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);
// 设置字体类型：宋体
headerFont.setFontName("宋体");
// 设置字体大小：12
headerFont.setFontHeightInPoints((short) 12);
// 为头部样式设置字体样式
headerStyle.setFont(headerFont);
// 设置头部居中
headerStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
// 创建body样式
XSSFCellStyle bodyStyle = wb.createCellStyle();
// 设置body居左
bodyStyle.setAlignment(XSSFCellStyle.ALIGN_LEFT);
// 创建字体样式
XSSFFont bodyFont = wb.createFont();
// 设置字体类型
bodyFont.setFontName("宋体");
// 设置字体大小
bodyFont.setFontHeightInPoints((short) 10);
// 为body样式设置字体样式
bodyStyle.setFont(bodyFont);
// 在workbook中添加一个sheet,对应Excel文件中的sheet
XSSFSheet sheet = wb.createSheet(sheetName);
// 在sheet中添加表头第0行
XSSFRow row = sheet.createRow(0);
// 声明列对象
XSSFCell cell = null;
// 创建标题行
for (int i = 0; i < headers.length; i++) {
cell = row.createCell(i);
cell.setCellStyle(headerStyle);
cell.setCellValue(headers[i]);
}
// 创建excel内容
for (int i = 0; i < dataList.size(); i++) {
// 行
row = sheet.createRow(i + 1);
// 数据填入列
for (int j = 0; j < columsNames.length; j++) {
Map<String, Object> map = dataList.get(i);
Object obj = map.get(columsNames[j]);
String columsValue = StringUtils.EMPTY;
columsValue = obj == null ? "" : obj.toString();
// 列
cell = row.createCell(j);
cell.setCellStyle(bodyStyle);
cell.setCellValue(columsValue);
}
}
// 必须在单元格设值以后进行
// 设置为根据内容自动调整列宽
for (int k = 0; k < dataList.size(); k++) {
sheet.autoSizeColumn(k);
}
// 处理中文不能自动调整列宽的问题
setSizeColumn(sheet, dataList.size());
setResponseHeader(response, fileName);
OutputStream out = null;
try {
out = response.getOutputStream();
wb.write(out);
out.close();
out = null;
String str = "导出" + fileName + "成功！";
log.info(str);
} catch (IOException e) {
String str = "导出" + fileName + "失败！";
log.error(str);
e.printStackTrace();
} finally {
if (out != null) {
try {
out.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}
jsp
<button type="button" class="export-excel" id="export"
onclick="exportExcelAll();">导出Excel</button>
js
//导出excel
function exportExcelAll() {
let startTime = $("#start").val();
let endTime = $("#end").val();
$(document).an_dialog({
massage: {
type: '提示',
content: "导出订单日期：" + startTime + "至" + endTime,
},
buttons: [{
text: '确认',
cls: "success",
handler: function (e) {
var myloading = andy.loading('top', '请稍等，正在导出数据...', function () {
});
submitForm();
//location.href = contextPath + 'order/export/exportExcel?startTime=' + startTime + '&endTime='
var excelTimer = setInterval(function () {
$.get(contextPath + "/order/export/getExcelFlag", function (r) {
if (r == 'false') {
myloading.close();
clearInterval(excelTimer);
}
});
}, 1000);
e.data.an_dialog("close");
}
}, {
text: '取消',
handler: function (e) {
e.data.an_dialog("close");
}
}]
});
}
// form表单提交
function submitForm(){
var orderState = $("#orderState ").val();
var orderType = $("#orderType ").val();
var keySearch = encodeURI(encodeURI(encodeURI($("#keySearch ").val().trim())));
var startTime= $("#start").val();
var endTime= $("#end").val();
// 构建form表单
var form = $("<form>");
form.attr('style', 'display:none');
form.attr('target', '');
form.attr('method', 'post');
form.attr('action', contextPath + '/order/export/exportExcel');
var input1 = $('<input>');
input1.attr('type', 'hidden');
input1.attr('name', 'keyword');
input1.attr('value', keySearch); /* JSON.stringify($.serializeObject($('#searchForm'))) */
var input2 = $('<input>');
input2.attr('type', 'hidden');
input2.attr('name', 'state');
input2.attr('value', orderState);
var input3 = $('<input>');
input3.attr('type', 'hidden');
input3.attr('name', 'type');
input3.attr('value', orderType);
var input4 = $('<input>');
input4.attr('type', 'hidden');
input4.attr('name', 'startTime');
input4.attr('value', startTime);
var input5 = $('<input>');
input5.attr('type', 'hidden');
input5.attr('name', 'endTime');
input5.attr('value', endTime);
$('body').append(form);
form.append(input1);
form.append(input2);
form.append(input3);
form.append(input4);
form.append(input5);
form.submit();
form.remove();
}
这里出现一个小问题，location.href这里get请求，报400错误。参数类型和参数名都能对上，去掉几个参数之后也能进请求。单独用url请求时，发现后面的参数被截取掉了。所以这里改用form表单提交方式实现。
网友评论