以springMVC為例：
1.controller:
/**
* 導出
* @param request
* @param response
*/
@RequestMapping("/exportDiscountRatioExcel")
public void exportDiscountRatioExcel(HttpServletRequest request,HttpServletResponse response)
{
Map<String, Object> paramsMap = getPageParams(request);
try {
Workbook wb = manager.exportExcel(request, paramsMap);
String excelName = java.net.URLEncoder.encode("價格監(jiān)測-車型折扣對比", "UTF-8");
response.setContentType("application/vnd.ms-excel;charset=utf-8");
response.setHeader("Content-Disposition", "attachment;filename="+excelName+".xls" );
ServletOutputStream out = response.getOutputStream();
wb.write(out);
//先把EXCEL寫到臨時目錄，用來獲取文件大小，最后刪除
File f = new File(request.getSession().getServletContext().getRealPath("/") + "/demoExcel/demo.xls");
if(!f.exists())
{
f.createNewFile();
}
BufferedOutputStream s = new BufferedOutputStream(new FileOutputStream(f));
wb.write(s);
//關閉流
s.flush();
s.close();
out.flush();
out.close();
//記錄導出日志，并刪除臨時文件
paramsMap.put("exportSize", AppFrameworkUtil.getNum(f.length()/1024, 0));
logManager.updateModuleLog(paramsMap);
f.delete();
} catch (Exception e) {
e.printStackTrace();
}
}
2.serviceImpl:
/**
* 導出
*/
@Override
public Workbook exportExcel(HttpServletRequest request,Map<String, Object> paramsMap)
{
Workbook wb = null;
String json = (String) request.getSession().getAttribute(Constant.getExportExcelDataKey);
String languageType = (String) paramsMap.get("languageType");//導出語言
if(!AppFrameworkUtil.isEmpty(json))
{
JSONObject obj = (JSONObject) JSONObject.parse(json);
JSONArray gridObj = (JSONArray) obj.getJSONArray("grid");
try {
//讀取模塊路徑
String path = request.getSession().getServletContext().getRealPath("/");
wb = new HSSFWorkbook(new FileInputStream(new File(path+"excelExample/discountRatio.xls")));
//導圖
exportLineChartExcel(wb,wb.getSheet("DATA"),gridObj,languageType);
//導表格
exportOriginalData(wb,wb.getSheet("原數(shù)據(jù)"),gridObj,languageType,paramsMap);
} catch (Exception e) {
e.printStackTrace();
}
}
return wb;
}
/**
* 導出原始數(shù)據(jù)
* @param wb
* @param s
* @param gridObj
* @param paramsMap
*/
public void exportOriginalData(Workbook wb,Sheet s,JSONArray gridObj,String languageType,Map<String, Object> paramsMap)
{
String[] titles = null;//標題數(shù)組
if("EN".equals(languageType))
{
titles = new String[]{"Date","Code","Segment","Manufacture","Model","Engine Capacity","Transmission","Trim","Bodytype","Launch Date","Model Year","MSRP(RMB)"
,"TP（RMB）","Discount（RMB）","Discount Rate","VS Month","VS Change"};
}
else
{
titles = new String[]{"日期","車型編碼","級別","廠商","車型","排量","排檔方式","型號標識","車身形式","上市日期","年式","廠商指導價"
,"最低參考價加權(quán)值","折扣(元)","折扣率","VS上月","VS變化"};
}
int rowIndex = 0;//行號鎖引
int cellIndex = 0;//列鎖引
Row row = ExportExcelUtil.createRow(s, rowIndex, 400);
CellStyle titleStyle = ExportExcelUtil.getExcelTitleBackgroundStyle(wb);//表格標題樣式
CellStyle textStyle = ExportExcelUtil.getExcelFillTextStyle(wb);//內(nèi)容文本樣式
CellStyle thousandthStyle = ExportExcelUtil.getExcelFormatThousandthStyle(wb);//格式化千分位樣式
CellStyle percentageStyle = ExportExcelUtil.getExcelFillPercentageStyle(wb);//格式化百分號樣式
//保存線圖刻度最大值與最小值，供數(shù)據(jù)部使用
Cell cell = row.createCell(40);
ExportExcelUtil.setCellValueAndStyle(cell, AppFrameworkUtil.getNum(paramsMap.get("ymax").toString(), 0)+".0~", textStyle);
cell = row.createCell(41);
ExportExcelUtil.setCellValueAndStyle(cell, AppFrameworkUtil.getNum(paramsMap.get("ymin").toString(), 0)+".0~", textStyle);
cell = row.createCell(42);
ExportExcelUtil.setCellValueAndStyle(cell, languageType, textStyle);
//寫標題
for(String title : titles)
{
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, title, titleStyle);
s.setColumnWidth(cell.getColumnIndex(), 3000);
}
//寫數(shù)據(jù)
for(int i = 0; i < gridObj.size(); i++)
{
JSONObject obj = gridObj.getJSONObject(i);
row = ExportExcelUtil.createRow(s, ++rowIndex, 400);
cellIndex = 0;
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, obj.getString("yearMonth")+"~", textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, obj.getString("versionCode")+"~", textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, getValueByLanguageType(obj,languageType,"gradeName"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, getValueByLanguageType(obj,languageType,"manfName"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, getValueByLanguageType(obj,languageType,"subModelName"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, obj.getString("discharge"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, getValueByLanguageType(obj,languageType,"gearMode"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, getValueByLanguageType(obj,languageType,"typeId"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, getValueByLanguageType(obj,languageType,"bodyType"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, obj.getString("versionLaunchDate"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, obj.getString("modelYear"), textStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, obj.getString("msrp"), thousandthStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, AppFrameworkUtil.getNum(obj.getString("tp"),7), thousandthStyle);
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, AppFrameworkUtil.getNum(obj.getString("discount"),7), thousandthStyle);
cell = row.createCell(cellIndex++);
if("".equals(obj.getString("discountRate")) || "-".equals(obj.getString("discountRate"))){
ExportExcelUtil.setCellValueToPercentage(cell, "-", percentageStyle);
} else{
ExportExcelUtil.setCellValueToPercentage(cell, obj.getString("discountRate"), percentageStyle);
}
cell = row.createCell(cellIndex++);
ExportExcelUtil.setCellValueAndStyle(cell, AppFrameworkUtil.getNum(obj.getString("vsDiscount"),7), thousandthStyle);
cell = row.createCell(cellIndex++);
if("".equals(obj.getString("vsDiscountRate")) || "-".equals(obj.getString("vsDiscountRate"))){
ExportExcelUtil.setCellValueToPercentage(cell, "-", percentageStyle);
} else{
ExportExcelUtil.setCellValueToPercentage(cell, obj.getString("vsDiscountRate"), percentageStyle);
}
}
s.setDisplayGridlines(false);
}
