下载数据库的数据 成Excel文件:
思路: 1,先查询出你要保存(下载)的数据
2,把数据通过遍历查询出来放入集合中
3,把遍历出来的数据写入Excel文件中 文件便创建成功。
代码如下:
jsp:
<button id="go_download" type="button" class="btn btn-success"><i class="fa fa-arrow-circle-o-down">下载</i>
</button>
js:
//是form 表单提交 传参优化了一下
$("#go_download").on("click", function() {
var formData = $("#searchForm").serializeArray();//把form里面的数据序列化成数组
var getStr = "";
formData.forEach(function (e) {
if(e.value != '00'){
getStr += e.name;
getStr += "=";
getStr += e.value;
getStr += "&";
}
});
var url = "yeepayFenrun/yeepayFenrunDataDownLoad.do?"+getStr;
window.location=url;
});
control:
/**
* 下载
*/
@RequestMapping(value = "yeepayFenrunDataDownLoad.do")
public void listDownLoad(HttpServletRequest request,
HttpServletResponse response) {
String startdate = request.getParameter("startdate");
String enddate = request.getParameter("enddate");
String start = request.getParameter("start");
String length = request.getParameter("length");
if(LOG.isInfoEnabled()){
LOG.info("下载");
LOG.info("start:" + start);
LOG.info("length:" + length);
}
Map<String, Object> map = new HashMap<String, Object>();
StringBuffer buffer = new StringBuffer();
if(StringHandler.isNotBlank(startdate)){
buffer.append(startdate);
buffer.append(" 00:00:00");
map.put("startdate", buffer.toString());
}
if(StringHandler.isNotBlank(enddate)){
buffer.setLength(0);
buffer.append(enddate);
buffer.append(" 23:59:59");
map.put("enddate", buffer.toString());
}
if(StringHandler.isNotBlank(start)){
map.put("start", Integer.valueOf(start));
}
if(StringHandler.isNotBlank(length)){
map.put("length", Integer.valueOf(length));
}
try {
List<YeepayFenrunData> list = yeepayFenrunDataService.getInfoDownLoad(map);
//文件名
String fileName =
DateUtil.getSimpleDateFormat(DateUtil.DATE_FORMAT_2).format(new Date())+".xls";
String[] title = {"订单号","创建时间"};
List<String[]> dataList = new ArrayList<String[]>();
for (YeepayFenrunData yeepayFenrunData : list) {
String[] cellArr = new String[title.length];
cellArr[0] = yeepayFenrunData.getSys_trade_no();
cellArr[1] = DateUtil.getSimpleDateFormat(DateUtil.DATE_FORMAT_1).format(yeepayFenrunData.getUpdate_time());
dataList.add(cellArr);
}
boolean flag = ExcelUtil.writeExcel(response, fileName, title, dataList);
if(flag){
LOG.info(fileName+",文件创建成功");
}else{
LOG.info(fileName+",文件创建失败");
}
} catch (Exception e) {
if(LOG.isErrorEnabled())
LOG.error(e.getMessage());
}
}
Excel工具类方法:
/**
* 写Excel文件
* @param response : 响应对象
* @param fileName : 下载文件名称
* @param title : 标题
* @param dataList : 内容
* @return
* @throws Exception
*/
public static boolean writeExcel(HttpServletResponse response,String fileName,String[] title,List<String[]> dataList) throws Exception {
boolean flag = false;
try {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFCellStyle hssfTitleCellStyle = getHSSFTitleStyle(wb);
int pageSize = 65535;
int listSize = dataList.size();
int sheetSize = 0;
if(listSize % pageSize == 0){
sheetSize = listSize/pageSize;
}else{
sheetSize = (listSize/pageSize)+1;
}
if(sheetSize == 0){
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 600);//目的是想把行高设置成25px
row.setRowStyle(hssfTitleCellStyle);
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeightInPoints(20);
//title
for (int i = 0;i < title.length;i++) {
HSSFCell cell = row.createCell(i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(hssfTitleCellStyle);
cell.setCellValue(title[i]);
}
}else{
int start = 0;
int end = 0;
for(int s=0;s<sheetSize;s++){
start = s * pageSize;
if(s == (sheetSize-1))
end = listSize;
else
end = (s+1) * pageSize;
HSSFSheet sheet = wb.createSheet();
HSSFRow row = sheet.createRow(0);
row.setHeight((short) 600);//目的是想把行高设置成25px
row.setRowStyle(hssfTitleCellStyle);
sheet.setDefaultColumnWidth(20);
sheet.setDefaultRowHeightInPoints(20);
//title
for (int i = 0;i < title.length;i++) {
HSSFCell cell = row.createCell(i);
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(hssfTitleCellStyle);
cell.setCellValue(title[i]);
}
//context
HSSFCellStyle hssfCellStyle = getHSSFCellStyle(wb);
int row_index = 1;
for(int j = start;j < end;j++){
HSSFRow data_row = sheet.createRow(row_index);
row_index ++;
String[] cellList = dataList.get(j);
int len = cellList.length;
for (int k=0;k<len;k++) {
HSSFCell cell = data_row.createCell(k);
String value = cellList[k];
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
cell.setCellStyle(hssfCellStyle);
cell.setCellValue(value);
}
}
}
}
response.reset();
response.setHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes(), "iso8859-1"));
ServletOutputStream out = response.getOutputStream();
wb.write(out);
// 弹出下载对话框
out.close();
flag = true;
} catch (Exception e) {
throw e;
}
return flag;
}
过程就是如此。