页面样式,这里结合通过选择下拉框进行导出:
<div class="panel-body">
<div class="form-inline">
<label>选择小区:</label>
<div class="form-group">
<select id="areas" class="selectpicker" multiple data-live-search="true" data-live-search-placeholder="Search" data-actions-box="true" data-style="btn-default btn-sm" data-size="5">
<option th:each="haAreas : ${haAreasList}"
th:value="${haAreas.areaId}" th:text="${haAreas.areaNo}+':'+${haAreas.haName}"></option>
</select>
</div>
<button type="button" id="templateExport" class="btn btn-default btn-sm btnMargin">导出</button>
</div>
</div>
当点击时传值跳转方法,不要用ajax跳转,无反应:
$("#templateExport").click(function(){
var areaId = $("#areas").val();
var loadLayerIndex = "";
if($("#areas").val() == null){
window.open(get_excel_model);
return false;
}
window.location.href="/room/CustomerExport?areaId="+areaId;
});
下面创建一个导出Excel工具类:
package com.ktamr.common.utils.export;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
public class ExcelUtil {
/**
* 导出Excel
* @param sheetName sheet名称
* @param title 标题
* @param values 内容
* @param wb HSSFWorkbook对象
* @return
*/
public static HSSFWorkbook getHSSFWorkbook(String sheetName, String []title, String [][]values, HSSFWorkbook wb){
// 第一步,创建一个HSSFWorkbook,对应一个Excel文件
if(wb == null){
wb = new HSSFWorkbook();
}
// 第二步,在workbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet(sheetName);
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
//声明列对象
HSSFCell cell = null;
//创建标题
for(int i=0;i<title.length;i++){
cell = row.createCell(i);
cell.setCellValue(title[i]);
cell.setCellStyle(style);
}
//创建内容
for(int i=0;i<values.length;i++){
row = sheet.createRow(i + 1);
for(int j=0;j<values[i].length;j++){
//将内容按顺序赋给对应的列对象
row.createCell(j).setCellValue(values[i][j]);
}
}
return wb;
}
}
Collectorller控制器代码:
//发送响应流方法
public void setResponseHeader(HttpServletResponse response, String fileName) {
try {
try {
fileName = new String(fileName.getBytes(), "UTF-8");
} catch (UnsupportedEncodingException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setContentType("application/octet-stream;charset=UTF-8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.addHeader("Pargam", "no-cache");
response.addHeader("Cache-Control", "no-cache");
} catch (Exception ex) {
ex.printStackTrace();
}
}
@RequestMapping("/CustomerExport")
@ResponseBody
public void customerExport(HttpServletResponse response, Integer areaId) throws Exception {
List<HaRoom> rooms = haRoomService.customExport(areaId);
//excel标题
String[] title = {"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20"};
//excel文件名
String fileName = "kt-userTable" + System.currentTimeMillis() + ".xls";
//sheet名
String sheetName = "sheet1";
String[][] content = new String[rooms.size()][];
for (int i = 0; i < rooms.size(); i++) {
content[i] = new String[title.length];
HaRoom obj = rooms.get(i);
//捕捉异常,如果值为空赋空值
try {
content[i][0] = obj.getHaCustom().getCustNo();
} catch (Exception e) {
e.printStackTrace();
content[i][0] = "";
}
try {
content[i][1] = obj.getHaCustom().getName();
} catch (Exception e) {
e.printStackTrace();
content[i][1] = "";
}
content[i][2] = obj.getHaArea().getHaName();
content[i][3] = obj.getHaBuilding().getName();
content[i][4] = obj.getName();
try {
content[i][5] = String.valueOf(obj.getHaMeter().getMeterNumber());
} catch (Exception e) {
e.printStackTrace();
content[i][5] = "";
}
try {
content[i][6] = obj.getHaMeter().getMeterChannel().toString();
} catch (Exception e) {
e.printStackTrace();
content[i][6] = "";
}
try {
content[i][7] = obj.getHaMeter().getMeterSequence().toString();
} catch (Exception e) {
e.printStackTrace();
content[i][7] = "";
}
try {
content[i][8] = String.valueOf(obj.getHaMeter().getVendorId());
} catch (Exception e) {
e.printStackTrace();
content[i][8] = "";
}
try {
content[i][9] = obj.getHaCentor().getCentorNo();
} catch (Exception e) {
e.printStackTrace();
content[i][9] = "";
}
try {
content[i][10] = obj.getHaCollector().getNconf().toString();
} catch (Exception e) {
e.printStackTrace();
content[i][10] = "";
}
try {
content[i][11] = obj.getHaMetertype().getName();
} catch (Exception e) {
e.printStackTrace();
content[i][11] = "";
}
try {
content[i][12] = String.valueOf(obj.getHaMeter().getIsBranch());
} catch (Exception e) {
e.printStackTrace();
content[i][12] = "";
}
try {
content[i][13] = String.valueOf(obj.getHaMeter().getGnumber());
} catch (Exception e) {
e.printStackTrace();
content[i][13] = "";
}
try {
content[i][14] = String.valueOf(obj.getHaMeter().getStartTime());
} catch (Exception e) {
e.printStackTrace();
content[i][14] = "";
}
try {
content[i][15] = obj.getHaPricestandard().getName();
} catch (Exception e) {
e.printStackTrace();
content[i][15] = "";
}
try {
content[i][16] = String.valueOf(obj.getHaMeter().getRate());
} catch (Exception e) {
e.printStackTrace();
content[i][16] = "";
}
try {
content[i][17] = obj.getHaCustom().getSex();
} catch (Exception e) {
e.printStackTrace();
content[i][17] = "";
}
try {
content[i][18] = obj.getHaCustom().getMobil();
} catch (Exception e) {
e.printStackTrace();
content[i][18] = "";
}
try {
content[i][19] = String.valueOf(obj.getHaCustom().getBalance());
} catch (Exception e) {
e.printStackTrace();
content[i][19] = "";
}
}
//创建HSSFWorkbook
HSSFWorkbook wb = ExcelUtil.getHSSFWorkbook(sheetName, title, content, null);
//响应到客户端
try {
this.setResponseHeader(response, fileName);
OutputStream os = response.getOutputStream();
wb.write(os);
os.flush();
os.close();
} catch (Exception e) {
e.printStackTrace();
}
}
导出Excel效果图: