springmvc导出excel到本地，弹出下载路径选择框
说明一点：因ajax发送不接收流的返回，所以弹框实现了，就不能发送ajax，但是不发ajax我们无法得到一些错误信息。所以在此我们第一步去校验，如果校验有问题，返回ajax接收。如果没问题，我们再发送一次，直接发送一个地址。此时我们只需要弹框保存，其他的操作就不需要了。这个问题纠结了我好久，百度了很多说得好，但是没有一个完整的例子。今天给大家发送我做的完整代码供学习
/**
* NkBuyMainService
*/
myApp.factory('nikeBuyService', ['$resource', '$http', function($resource, $http) {
return new nikeBuyService($resource, $http);
}]);
function nikeBuyService(resource, http) {
/**
* 生成nike buy
*/
this.exportExcel = function(scope) {
console.log("...........nikeBuyService.exportExcel.start...........");
// window.location = "nikebuy/exportNikeBuyToExcel"
http({
method : 'GET',
url : "nikebuy/exportNikeBuyToExcel",
headers : {
'Content-Type' : undefined
},
}).success(function(data, status, headers, config) {
console.log("...........nikeBuyService.exportExcel.data..........." + data.resultCode);
var resultCode = data.resultCode;
if (resultCode === "200") {
window.location = "nikebuy/exportNikeBuyToLocal";
} else {
}
package cn.com.nike.controller;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.io.Serializable;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import cn.com.nike.service.NikeBuyService;
import cn.com.nike.util.UtilDateTime;
import cn.com.nike.util.Constants.ConstantsData;
import cn.com.nike.util.Constants.ConstantsResult;
import cn.com.nike.util.common.ResultModel;
@Controller
@RequestMapping("/nikebuy")
public class NikeBuyController implements Serializable {
private static final long serialVersionUID = 2732216546553695880L;
private static final Logger log = LoggerFactory.getLogger(NikeBuyController.class);
@Resource
NikeBuyService nikeBuyService;
public static HttpServletResponse resp = null;
/**
* 导出nike_buy到本地
*
* @param request
* @param response
* @return
* @throws IOException
*/
@RequestMapping(value = "/exportNikeBuyToLocal", method = RequestMethod.GET)
@ResponseBody
public ResultModel exportNikeBuyToLocal(HttpServletRequest request, HttpServletResponse response) throws IOException {
log.info(UtilDateTime.getTimestamp(System.currentTimeMillis()) + ".exportNikeBuyToExcel.start...");
long starC = System.currentTimeMillis();
XSSFWorkbook workbook = null;
OutputStream outstream = null;
try {
log.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportExcel.start..导出nikebuy到本地开始..");
long a = System.currentTimeMillis();
// excel文件流输出到浏览器，选择下载路径
File f = new File(request.getSession().getServletContext().getRealPath("/")
+ ConstantsData.SYSTEM_NIKEBUY_PATH + ConstantsData.MODEL_Name + ConstantsData.CONS_SUFFIX_2007);
workbook = new XSSFWorkbook(new FileInputStream(f));
outstream = response.getOutputStream();
response.reset();
response.setHeader("Content-disposition", "attachment; filename=" + ConstantsData.MODEL_Name + "_"
+ UtilDateTime.nowDateToString() + ConstantsData.CONS_SUFFIX_2007);
response.setContentType("application/x-download");
workbook.write(outstream);
long b = System.currentTimeMillis();
log.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportExcel.end..导出nikebuy到本地结束，无异常，共耗时:{}....", (b - a));
} catch (Exception e) {// 发生不可预知异常，在此截获异常信息，并返回客户操作不成功
ConstantsResult.setResultModel(ConstantsResult.SERVICE_ERROR_CODE, ConstantsResult.SERVICE_ERROR);
log.debug(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exception...导出nike buy失败", e);
return ConstantsResult.resultModel;
}
// 如果导出成功，封装成功信息，客户选择下载路径
ConstantsResult.setResultModel(ConstantsResult.SERVICE_SUCCESS_CODE, ConstantsResult.SERVICE_SUCCESS_MSG);
long endC = System.currentTimeMillis();
log.info(UtilDateTime.getTimestamp(System.currentTimeMillis()) + ".exportNikeBuyToExcel.end..总耗时：{},出参={}",
(endC - starC), ConstantsResult.resultModel);
return ConstantsResult.resultModel;
}
/**
* 生成excel
*
* @param request
* @param response
* @return
* @throws IOException
*/
@RequestMapping(value = "/exportNikeBuyToExcel", method = RequestMethod.GET)
@ResponseBody
public ResultModel exportNikeBuyToExcel(HttpServletRequest request, HttpServletResponse response) throws IOException {
log.info(UtilDateTime.getTimestamp(System.currentTimeMillis()) + ".exportNikeBuyToExcel.start...");
long starC = System.currentTimeMillis();
try {
// 检查表是否存在，不存在直接返回错误信息
// if (nikeBuyService.checkDB()) {
// return ConstantsResult.resultModel;
// }
// 如果三张表都存在，执行导出nike buy到excel，如果导出不成功，直接返回错误信息
String path = request.getSession().getServletContext().getRealPath("/"); // 模板文件的相对路径webapp/files/xlsprint/nikeBuy.xlsx
if (nikeBuyService.exportNikeBuyToExcel(path, response)) {
return ConstantsResult.resultModel;
}
} catch (Exception e) {// 发生不可预知异常，在此截获异常信息，并返回客户操作不成功
ConstantsResult.setResultModel(ConstantsResult.SERVICE_ERROR_CODE, ConstantsResult.SERVICE_ERROR);
log.debug(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exception...导出nike buy失败", e);
return ConstantsResult.resultModel;
}
// 如果导出成功，封装成功信息，客户选择下载路径
ConstantsResult.setResultModel(ConstantsResult.SERVICE_SUCCESS_CODE, ConstantsResult.SERVICE_SUCCESS_MSG);
long endC = System.currentTimeMillis();
log.info(UtilDateTime.getTimestamp(System.currentTimeMillis()) + ".exportNikeBuyToExcel.end..总耗时：{},出参={}",
(endC - starC), ConstantsResult.resultModel);
return ConstantsResult.resultModel;
}
}
package cn.com.nike.service.impl;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.annotation.Resource;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import cn.com.nike.dao.EntityDao;
import cn.com.nike.service.NikeBuyService;
import cn.com.nike.util.UtilDateTime;
import cn.com.nike.util.UtilFile;
import cn.com.nike.util.UtilValidate;
import cn.com.nike.util.Constants.ConstantsData;
import cn.com.nike.util.Constants.ConstantsResult;
@SuppressWarnings("rawtypes")
@Service("nikeBuyService")
public class NikeBuyServiceImpl implements NikeBuyService {
private static final Logger logger = LoggerFactory.getLogger(NikeBuyServiceImpl.class);
List list = null;
@Resource
private EntityDao entityDao;
/**
* 导出nike buy到excel
*
* @throws Exception
*
* @throws FileNotFoundException
*/
public boolean exportNikeBuyToExcel(String path, HttpServletResponse response) throws Exception {
logger.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportNikeBuyToExcel.start...");
long start = System.currentTimeMillis();
try {
// 数据库读取数据，如果数据列表为空，返回结果信息，如果不为空，继续以下操作
if (this.getNikeBuyData()) {
return true;
}
// 循环读取数据生成excel,如果错误，直接返回错误结果
if (this.exportExcel(path, response)) {
return true;
}
} catch (Exception e) {// 如果发生未知异常，抛出异常，在controller中捕获，并返回合理错误信息
ConstantsResult.setResultModel(ConstantsResult.SERVICE_ERROR_CODE, e.getMessage());
logger.debug(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportNikeBuyToExcel.exception.nikebuy导出异常.异常信息为={}", e);
throw new Exception(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportNikeBuyToExcel.exception.nikebuy导出异常.异常信息为={}", e);
} //
long end = System.currentTimeMillis();
logger.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportNikeBuyToExcel.end.导出excel成功，共耗时={}", (end - start));
return false;
}
/**
* 生成excel文件
*
* @return
* @throws Exception
*/
private boolean exportExcel(String path, HttpServletResponse response) throws Exception {
logger.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportExcel.start..生成nikebuy开始...path={}", path);
long start = System.currentTimeMillis();
InputStream is = null;
try {
is = new FileInputStream(new File(
path + ConstantsData.MODEL_PATH + ConstantsData.MODEL_Name + ConstantsData.CONS_SUFFIX_2007));
XSSFWorkbook workbook = new XSSFWorkbook(is);
XSSFSheet sheet = workbook.getSheetAt(0); // 获取到第一个工作表
XSSFRow xssfRow = sheet.getRow(0);// 获取第一行的总列数
int celCount = xssfRow.getLastCellNum();
for (int rowNum = 4; rowNum < list.size() + 4; rowNum++) {
Map dataMap = (Map) list.get(rowNum - 4);
XSSFRow row = sheet.createRow(rowNum);
for (int i = 0; i < celCount; i++) {
XSSFCell cell = row.createCell(i, XSSFCell.CELL_TYPE_STRING);
cell.setCellValue(dataMap.get(i) + "");
}
}
// 保存excel到服务器，上传ftp时使用
this.saveFileToLocal(path, workbook);
} catch (Exception e) {
ConstantsResult.setResultModel(ConstantsResult.SERVICE_ERROR_CODE, e.getMessage());
logger.debug(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportExcel.exception.生成nikebuy异常.异常信息为={}", e);
throw new Exception(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportExcel.exception.生成nikebuy异常.异常信息为={}", e);
}
long end = System.currentTimeMillis();
logger.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.exportExcel.end..生成nikebuy无异常..共耗时={}", (end - start));
return false;
}
/**
* 将生成的excel存入服务器，供发送ftp时使用 exePah + ConstantsData.MODEL_Name + "_" +
* UtilDateTime.nowDateToString() + ConstantsData.CONS_SUFFIX_2007
*
* @throws Exception
*/
private void saveFileToLocal(String path, XSSFWorkbook workbook) throws Exception {
logger.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.saveFileToLocal.start..保存nikebuy到服务器开始...path={}", path);
long start = System.currentTimeMillis();
FileOutputStream out = null;
try {
String exePah = path + ConstantsData.SYSTEM_NIKEBUY_PATH;
// 删除文件
UtilFile.deleteFile(new File(exePah));
// 存入服务器（上传时用）
out = new FileOutputStream(new File(exePah + ConstantsData.MODEL_Name + ConstantsData.CONS_SUFFIX_2007));
workbook.write(out);
} catch (Exception e) {
ConstantsResult.setResultModel(ConstantsResult.SERVICE_ERROR_CODE, e.getMessage());
logger.debug(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.saveFileToLocal.exception.保存nikebuy到服务器.异常信息为={}", e);
throw new Exception(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.saveFileToLocal.exception.保存nikebuy到服务器异常.异常信息为={}", e);
} finally {
if (out != null) {
out.flush();
out.close();
}
}
long end = System.currentTimeMillis();
logger.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.saveFileToLocal.end..保存nikebuy到服务器结束..无异常，共耗时：{}", (end - start));
}
/**
* 数据库获取nikebuy要导出的数据
*
* @return
* @throws Exception
*/
@SuppressWarnings("unchecked")
private boolean getNikeBuyData() throws Exception {
list = new ArrayList();// 数据库读取的nikebuy数据
long star = System.currentTimeMillis();
logger.info(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.getNikeBuyData.start........");
try {
Map map = new HashMap();
for (int i = 0; i < 200; i++) {
map.put(i, "aa");
}
list.add(map);
} catch (Exception e) {
logger.debug(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.getNikeBuyData.exception....nikebuy查询数据库异常,异常信息为:{}", e);
throw new Exception(UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.getNikeBuyData.exception....nikebuy查询数据库异常,异常信息为:{}", e);
}
if (UtilValidate.isEmpty(list)) {
ConstantsResult.setResultModel(ConstantsResult.SERVICE_ERROR_CODE, ConstantsResult.CEL_NULL_ERROR);// 这种情况（除查询语句错误以外）基本不存在
return true;
}
long end = System.currentTimeMillis();
logger.info(
UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.getNikeBuyData.end.....获取nikebuy成功,数据共{}条，耗时：{}",
list.size(), (end - star));
return false;
}
/**
* 检查三个表是否存在 Buy Plan，QAP，Size Master
*/
@Override
public boolean checkDB() {
long starS = System.currentTimeMillis();
logger.info(UtilDateTime.getTimestamp(System.currentTimeMillis()) + ".exportNikeBuyToExcel.checkDB.start.");
StringBuffer buffer = new StringBuffer();
int flag = 0;
try {
// 检查Buy Plan
if (!entityDao.exindex(ConstantsData.SIZEMASTER_DB_NAME)) {
buffer.append("Buy Plan");
flag = 1;
}
// 检查QAP
if (!entityDao.exindex(ConstantsData.QAP_DB_NAME)) {
if (flag == 1) {
buffer.append("AND");
} else {
flag = 1;
}
buffer.append("QAP");
}
// 检查Size Master
if (!entityDao.exindex("sizemaster")) {
if (flag == 1) {
buffer.append("AND");
} else {
flag = 1;
}
buffer.append("Size Master");
}
if (flag == 1) {
buffer.append("\thave not import");
ConstantsResult.setResultModel(ConstantsResult.SERVICE_ERROR_CODE, buffer.toString());
return true;
}
} catch (Exception e) {
}
long endS = System.currentTimeMillis();
logger.info(
UtilDateTime.getTimestamp(System.currentTimeMillis())
+ ".exportNikeBuyToExcel.checkDB.end.检查三张表结果={},共耗时:{}",
ConstantsResult.resultModel, (endS - starS));
return false;
}
}// class end
}).error(function(data, status, headers, config) {
console.log("...........nikeBuyService.exportExcel.error...........");
})
console.log("...........nikeBuyService.exportExcel.end...........");
};
/**
* 发送nike buy到ftp
*/
this.sendNikeBuyToSTP = function(scope) {
// window.location = "nikebuy/exportNikeBuyToLocal";
};
};
