前端:
function exportData(){
window.open("/vippay/beauty/exportOrderList.do?timeArea="+$("#starttime").val());
}
后台:
Controller端
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
@RequestMapping(value="/exportOrderList")
@ResponseBody
public void exportExcel(HttpServletRequest request,HttpServletResponse response){
String timeArea = request.getParameter("timeArea");//形似2016/09/09 - 2016/09/19
String beginTime;
String endTime;
if(null==timeArea||timeArea.equals("")){
beginTime = DateHelper.format(new Date(),"yyyy-MM-dd");
endTime = beginTime;
}else{
beginTime = timeArea.substring(0,10).replace("/", "-");
endTime = timeArea.substring(13).replace("/", "-");
}
Map<String, Object> param = new HashMap<String, Object>();
param.put("beginTime", beginTime);
param.put("endTime", endTime);
List<Order> orderList = orderService.queryOrderListByParam(param); //获取数据库数据
try {
DateFormat format = new SimpleDateFormat("yyyyMMddhhmmss");
String fileName = format.format(new Date())+".xls";//excel文件命名
String realPath = request.getServletContext().getRealPath("/view/beauty/orderList.xls"); //template的地址
HSSFWorkbook wb = new ExcelWriter().writeOrderListToFile(realPath,fileName,orderList);
String headStr = "attachment; filename=\"" + fileName + "\"";
response.setContentType("APPLICATION/OCTET-STREAM");
response.setHeader("Content-Disposition", headStr);
OutputStream out = response.getOutputStream();
wb.write(out);
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
ExcleWriter类
import java.io.File;
import java.io.FileInputStream;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
public class ExcelWriter {
public static HSSFWorkbook writeOrderListToFile(String templatePath,String fileName,List<Order> orderList) throws Exception{
//excel模板路径
File fi=new File(templatePath);
POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi));
//读取excel模板
HSSFWorkbook wb = new HSSFWorkbook(fs);
if(orderList==null||orderList.size()<0){
return wb;
}
Iterator<MltyPayOrder> iterator = orderList.iterator();
int rowIndex = 2;//前两行是固定格式
HSSFSheet sheet = wb.getSheetAt(0);
while(iterator.hasNext()){
MltyPayOrder order = iterator.next();
Row row = sheet.createRow(rowIndex++);
Cell cell0 = row.createCell(0); //总共5栏
cell0.setCellValue(order.getUserName());
Cell cell1 = row.createCell(1);
cell1.setCellValue(order.getSecPhone());
Cell cell2 = row.createCell(2);
cell2.setCellValue(order.getSecOrderNo());
Cell cell3 = row.createCell(3);
cell3.setCellValue(order.getProductId());
Cell cell4 = row.createCell(4);
cell4.setCellValue(order.getRealPrice());
Cell cell5 = row.createCell(5);
cell5.setCellValue(order.getPayTimeString());
}
return wb;
}
}