controller
/**
*
* @Description (导出申请单)
* @author feizhou
* @Date 2018年6月21日下午4:33:02
* @version 1.0.0
* @param resquest
* @param response
*/
@RequestMapping(value = "/PushLogController/exportDateByModel.do")
public void exportDateByModel(HttpServletRequest resquest,HttpServletResponse response,String[] applicationNOs) {
// String jsonStr=null;
Map<String, Object> paramMap = RequestUtil.getUrlParam(resquest);
paramMap.put("applicationNOs", applicationNOs);
paramMap.put("startRow",1);
try {
pushLogService.exportDateByModel(paramMap,resquest,response);
} catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
}
}
service
@Override
public void exportDateByModel(Map<String, Object> paramMap,HttpServletRequest request,HttpServletResponse response) {
String fileName=(String) paramMap.get("fileName");
Integer startRow=(Integer) paramMap.get("startRow");
//模板路径
String modelURL=Constans.applicationModel;
List<List<String >>listData=null;
listData=bulidListData(paramMap);
if(StringUtil.isEmpty(listData)){
throw new MyException("所选的申请单没有还款计划");
}
if(StringUtil.isEmpty(fileName)){
fileName="model.xlsx";
}
ExcelUtil.objListToExcel(listData, response, request, fileName, startRow,modelURL);
}
构建数据
//构建导出需要的数据
private List<List<String>> bulidListData(Map<String, Object> paramMap) {
// TODO Auto-generated method stub
List<List<String>> listData=new ArrayList<List<String>>();
//获取数据
List<Application> applications = applicationDao.selectList(paramMap);
for (Application application : applications) {
List<String> data=new ArrayList<String>();
data.add(application.getApplicationNO());
data.add(application.getCustomerID() );
data.add(application.getGys_borrowsq());
data.add(application.getUser_phone() );
listData.add(data);
}
return listData;
}
导出的核心代码
/**
* @Description (通过excel模板,导出excel数据)
* @author feizhou
* @Date 2017年12月14日 下午3:00:52
* @version 1.0.0
* @param listData
* @param response
* @param fileName
*/
public static void objListToExcel(List<List<String>> listData, HttpServletResponse response,
HttpServletRequest request, String fileName,Integer startRow,String modelURL) {
// 获取项目发布的路径
String projectPath = request.getSession().getServletContext().getRealPath("");
// 模板文件路径
String modelPath = projectPath + modelURL;
// Excel2007版本的Excel文件需要使用XSSFWorkbook来读取,如下:
XSSFWorkbook wb = getWorkBookGivenFileHandle(modelPath);
// 获得当前sheet工作表//默认第一个个
XSSFSheet sheet = wb.getSheetAt(0);
if (StringUtil.isNotEmpty(wb)) {
try {
XSSFRow row = null;
// 设置内容
//获取开始行之后的第一行的所有单元格样式
row = sheet.getRow(startRow);
XSSFCellStyle rowStyle = row.getRowStyle();
short height = row.getHeight();
// boolean zeroHeight = row.getZeroHeight();
// row.get
int m = listData.get(0).size();
List<XSSFCellStyle> listCellStyle=new ArrayList<XSSFCellStyle>();
for (int j = 0; j < m; j++) {
XSSFCell cell = row.getCell(j);
XSSFCellStyle cellStyle = cell.getCellStyle();
listCellStyle.add(cellStyle);
}
//加载数据
for (int i = 0; i < listData.size(); i++) {
List<String> rowValues = listData.get(i);
row = sheet.createRow(i + startRow);
row.setRowStyle(rowStyle);
row.setHeight(height);
for (int j = 0; j < rowValues.size(); j++) {
XSSFCell cell = row.createCell(j);
String value = rowValues.get(j);
boolean isNum = StringUtil.isNum(value);
if (isNum) {
cell.setCellValue(Double.parseDouble(value));
cell.setCellStyle(listCellStyle.get(j));
} else {
cell.setCellValue(value);
cell.setCellStyle(listCellStyle.get(j));
}
}
}
//对不同的浏览器下载excel的中文乱码处理
String filename =StringUtil.doMessyCode(request, fileName);
response.setHeader("Content-Disposition", "attachment; filename=" + filename);
OutputStream out = response.getOutputStream();
// response.reset();//清空输出流
wb.write(out);// 写入File
out.flush();
out.close();
} catch (Exception e) {
e.printStackTrace();
}
} else {// 直接抛出异常
throw new MyException("找不到对应的模板,所以导不出数据");
}
}