在日常网页中点击按钮下载excel的例子很多,下面就是完成此功能的关键步骤。
1.在html页面添加按钮用来点击触发下载excel 的事件。
2.给按钮绑定事件,js代码
var elemIF = document.createElement("iframe");//创建dom对象
elemIF.src = "http://localhost:8080:excelTest/downExcel.do";//下载excel的后台请求地址
elemIF.style.display = "none";//此元素不会被显示
document.body.appendChild(elemIF);
3.通过请求跳转到后台代码,下面为生成excel的代码。
/**
* 数据写入excle,
* @param fileName 文件名
* @param list<map> excel中的数据
* @param titleRow excel第一行
* @param beanName list中map中的key值,此处beanName和titleRow一一对应
* @return
* @throws IOException
*/
public Workbook writer(String fileName,List<Map<String, Object>> list,String titleRow[],String beanName[]) throws IOException {
Workbook wb = new HSSFWorkbook();
Sheet sheet =(Sheet) wb.createSheet("sheet1");
CellStyle style = wb.createCellStyle(); // 样式对象
// 设置单元格的背景颜色为淡蓝色
style.setFillForegroundColor(HSSFColor.PALE_BLUE.index);
style.setAlignment(HorizontalAlignment.LEFT);// 水平剧中
style.setWrapText(true);// 指定当单元格内容显示不下时自动换行
Row row = sheet.createRow(0); //创建第一行
Cell cell = row.createCell(0);
for(int i = 0;i < titleRow.length;i++){
cell = row.createCell(i);
cell.setCellValue(titleRow[i]);
cell.setCellStyle(style); // 样式,居中
sheet.setColumnWidth(i, 20 * 256);
}
row.setHeight((short) 540);
//循环写入行数据
for (int i = 1; i < list.size()+1; i++) {
row = (Row) sheet.createRow(i);
row.setHeight((short) 500);
for (int j = 0; j < beanName.length; j++) {
Cell celldate = row.createCell(j);
celldate.setCellStyle(style);
celldate.setCellValue(list.get(i-1).get(beanName[j])+"");
}
}
return wb;
}
4.selvet把上面方法生成的excel返回前台。
Workbook wb = public Workbook writer(fileName,list,titleRow,beanName)
outputStream = response.getOutputStream();
request.setCharacterEncoding("utf-8");
response.setCharacterEncoding("utf-8");
response.setContentType("application/x-msdownload");
log.info("attachment;filename="+filename+"");
response.addHeader("Content-Disposition", "attachment;filename="+filename+"");//filename为excel文件的名
wb.write(outputStream);
response.getOutputStream().close();
log.info("下载excel结束");
生成并下载excel的关键步骤到这里就结束了。
import目录:
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
需要的jar包:poi-3.17.jar
参考数据:
生成excel方法writer的参数样例:
String[] titleRow = {"交易日期","平台订单号","支付机构订单号"};
String[] beanName = {"tran_date","out_trade_no","trans_id"};
String fileName="测试excel.xls";
List<Map<String, Object>> list = new ArrayList<Map<String,Object>>();
Map<String,Object> map = new HashMap<String,Object>();
map.put("tran_date", "123");
map.put("out_trade_no", "1231111");
map.put("trans_id", "123333");
list.add(map);
生成excel样例: