function downloadHref(){
//location.href('http://localhost:8080/CGR/PS103/downExels?chk=123');
var param = "PS103/downExels?chk=123";
location.href=$("base").attr("href")+param;
}
/**
* <TT><PRE>
* <p> 下载処理.</p>
* @param request
* @param response
* @throws Exception
* </PRE></TT>
*/
@RequestMapping("downExels")
@ResponseBody
public Object download(HttpServletRequest request, HttpServletResponse response,Cgr_Evaluate cgr_Evaluate,String chk,HttpSession session) throws Exception {
Map<String, Object> retMap=new HashMap<>();
if(chk==null){
//月份处理
int years=Integer.valueOf(cgr_Evaluate.getEvaluateMonth());
years+=1;
cgr_Evaluate.setYeaersDateFrom(cgr_Evaluate.getEvaluateMonth()+"-04");
cgr_Evaluate.setYeaersDateTO(years+"-03");
List<Cgr_Evaluate> list=ps103Service.findBody(cgr_Evaluate);
if(list.size()<1){
retMap.put("result", "erro");
return retMap;
}else{
retMap.put("result", "succes");
session.setAttribute("sessionList",list );
return retMap;
}
}
String templateFolder = request.getSession().getServletContext().getRealPath("excel");
String fullPathName = templateFolder + "/" + "年经销商管理表.xlsx";
// Excel初期化
Workbook wb = makeWorkBook(fullPathName);
//数据处理
@SuppressWarnings("unchecked")
List<Cgr_Evaluate> dataList=this.getDateList((List<Cgr_Evaluate>)session.getAttribute("sessionList"));
if (dataList != null && !dataList.isEmpty()) {
Sheet sheet = wb.getSheetAt(0);
Row srcRow = sheet.getRow(3);
int rowIndex = 3;
int i=0;
for (Cgr_Evaluate map : dataList) {
if (rowIndex > 3) {
copyRows(rowIndex, sheet, srcRow, false);
}
setCellValue(sheet, rowIndex, 0, ++i);
setCellValue(sheet, rowIndex, 1, map.getBareaName());
setCellValue(sheet, rowIndex, 2, map.getOfficeName());
setCellValue(sheet, rowIndex, 3, map.getDealerName());
setCellValue(sheet, rowIndex, 4, map.getDealerTypeBs());
setCellValue(sheet, rowIndex, 5, map.getDealerTypeCp());
setCellValue(sheet, rowIndex, 6, "");
setCellValue(sheet, rowIndex, 7, map.getUserName());
setCellValue(sheet, rowIndex, 8, map.getCkCount4()==null?"":getInt(map.getCkCount4()));
setCellValue(sheet, rowIndex, 9, map.getRank4());
setCellValue(sheet, rowIndex, 10, map.getCkCount5()==null?"":getInt(map.getCkCount5()));
setCellValue(sheet, rowIndex, 11, map.getRank5());
setCellValue(sheet, rowIndex, 12, map.getCkCount6()==null?"":getInt(map.getCkCount6()));
setCellValue(sheet, rowIndex, 13, map.getRank6());
setCellValue(sheet, rowIndex, 14, map.getCkCount7()==null?"":getInt(map.getCkCount7()));
setCellValue(sheet, rowIndex, 15, map.getRank7());
setCellValue(sheet, rowIndex, 16, map.getCkCount8()==null?"":getInt(map.getCkCount8()));
setCellValue(sheet, rowIndex, 17, map.getRank8());
setCellValue(sheet, rowIndex, 18, map.getCkCount9()==null?"":getInt(map.getCkCount9()));
setCellValue(sheet, rowIndex, 19, map.getRank9());
setCellValue(sheet, rowIndex, 20, map.getCkCount10()==null?"":getInt(map.getCkCount10()));
setCellValue(sheet, rowIndex, 21, map.getRank10());
setCellValue(sheet, rowIndex, 22, map.getCkCount11()==null?"":getInt(map.getCkCount11()));
setCellValue(sheet, rowIndex, 23, map.getRank11());
setCellValue(sheet, rowIndex, 24, map.getCkCount12()==null?"":getInt(map.getCkCount12()));
setCellValue(sheet, rowIndex, 25, map.getRank12());
setCellValue(sheet, rowIndex, 26, map.getCkCount1()==null?"":getInt(map.getCkCount1()));
setCellValue(sheet, rowIndex, 27, map.getRank1());
setCellValue(sheet, rowIndex, 28, map.getCkCount2()==null?"":getInt(map.getCkCount2()));
setCellValue(sheet, rowIndex, 29, map.getRank2());
setCellValue(sheet, rowIndex, 30, map.getCkCount3()==null?"":getInt(map.getCkCount3()));
setCellValue(sheet, rowIndex, 31, map.getRank3());
setCellValue(sheet, rowIndex, 32, map.getCkCountSum());
setCellValue(sheet, rowIndex, 33, map.getRankSum());
rowIndex++;
}
}
// 配送派单信息_时间
String tempOutPath = request.getSession().getServletContext().getRealPath("temp/download");
String outFullName = tempOutPath + "/" + "年经销商管理表" + "_" + DateFormatUtils.format(new Date(), "yyyyMMddHHmmss") + ".xlsx";;
// wb.setForceFormulaRecalculation(true);
try {
OutputStream os = new FileOutputStream(outFullName);
wb.write(os);
os.close();
} catch (Exception e) {
e.printStackTrace();
}
download(outFullName, request, response);
return null;
}
/**
* 下载excel(导出)
*
* @param path
* @param response
*/
public void download(String path, HttpServletRequest request, HttpServletResponse response) {
try {
File file = new File(path);
String fileName = file.getName();
InputStream fis = new BufferedInputStream(new FileInputStream(path));
byte[] buffer = new byte[fis.available()];
fis.read(buffer);
fis.close();
response.reset();
String outName = "";
String agent = request.getHeader("USER-AGENT");
if (null != agent && -1 != agent.indexOf("MSIE") || null != agent && -1 != agent.indexOf("Trident")) {// ie
String name = java.net.URLEncoder.encode(fileName, "UTF8");
outName = name;
} else if (null != agent && -1 != agent.indexOf("Mozilla")) {// 火狐,chrome等
outName = new String(fileName.getBytes("UTF-8"), "iso-8859-1");
}
response.addHeader("Content-Disposition", "attachment;filename=" + outName);
response.addHeader("Content-Length", "" + file.length());
OutputStream toClient = new BufferedOutputStream(response.getOutputStream());
response.setContentType("application/vnd.ms-excel;charset=UTF-8");
toClient.write(buffer);
toClient.flush();
toClient.close();
} catch (IOException ex) {
ex.printStackTrace();
}
}
/**
* <TT><PRE>
* <p>makeWorkBook. </p>
* @param fullPathName 模板文件
* @return 結果
* @throws Exception 例外
* </PRE></TT>
*/
protected Workbook makeWorkBook(String fullPathName) throws Exception {
FileInputStream inStream = null;
Workbook wb = null;
// 模板文件
File excelFile = new File(fullPathName);
// 该模板文件不存在时,
if (!excelFile.exists()) {
throw new Exception("模板文件不存在");
}
// 模板文件读取
inStream = new FileInputStream(excelFile);
wb = WorkbookFactory.create(inStream);
inStream.close();
return wb;
}
/**
* <TT><PRE>
* <p>行拷贝. </p>
* @param startRow 开始行
* @param currentSheet 模板文件
* @param sourceRow 模板文件
* @param isCopyVal 值拷贝判断标识
* </PRE></TT>
*/
protected void copyRows(int startRow, Sheet currentSheet, Row sourceRow, boolean isCopyVal) {
int columnCount = sourceRow.getLastCellNum();
Row newRow = currentSheet.createRow(startRow);
newRow.setHeight(sourceRow.getHeight());
for (int j = 0; j < columnCount; j++) {
Cell templateCell = sourceRow.getCell(j);
if (templateCell != null) {
Cell newCell = newRow.createCell(j);
copyCell(templateCell, newCell, isCopyVal);
}
}
}
/**
* <TT><PRE>
* <p>单元格拷贝. </p>
* @param currentSheet 模板文件
* @param srcRowIdx 拷贝元行
* @param srcColIdx 拷贝元列
* @param distRowIdx 目标单元格行
* @param distColIdx 目标单元格列
* @param isCopyVal 值拷贝判断标识
* </PRE></TT>
*/
protected void copyCell(Sheet currentSheet, int srcRowIdx, int srcColIdx, int distRowIdx, int distColIdx, boolean isCopyVal) {
Row srcRow = currentSheet.getRow(srcRowIdx);
Row distRow = currentSheet.getRow(distRowIdx);
Cell srcCell = srcRow.getCell(srcColIdx);
// cell未生成时,先生成cell对象
if (srcCell == null) {
srcCell = srcRow.createCell(srcColIdx);
}
Cell distCell = distRow.getCell(distColIdx);
// cell未生成时,先生成cell对象
if (distCell == null) {
distCell = distRow.createCell(distRowIdx);
}
copyCell(srcCell, distCell, isCopyVal);
}
/**
* <TT><PRE>
* <p>单元格拷贝. </p>
* @param srcCell 拷贝元
* @param distCell 目标单元格
* @param isCopyVal 值拷贝判断标识
* </PRE></TT>
*/
protected void copyCell(Cell srcCell, Cell distCell, boolean isCopyVal) {
distCell.setCellStyle(srcCell.getCellStyle());
if (isCopyVal) {
if (srcCell.getCellComment() != null) {
distCell.setCellComment(srcCell.getCellComment());
}
int srcCellType = srcCell.getCellType();
distCell.setCellType(srcCellType);
if (srcCellType == Cell.CELL_TYPE_NUMERIC) {
if (DateUtil.isCellDateFormatted(srcCell)) {
distCell.setCellValue(srcCell.getDateCellValue());
} else {
distCell.setCellValue(srcCell.getNumericCellValue());
}
} else if (srcCellType == Cell.CELL_TYPE_STRING) {
distCell.setCellValue(srcCell.getRichStringCellValue());
} else if (srcCellType == Cell.CELL_TYPE_BLANK) {
// nothing21
} else if (srcCellType == Cell.CELL_TYPE_BOOLEAN) {
distCell.setCellValue(srcCell.getBooleanCellValue());
} else if (srcCellType == Cell.CELL_TYPE_ERROR) {
distCell.setCellErrorValue(srcCell.getErrorCellValue());
} else if (srcCellType == Cell.CELL_TYPE_FORMULA) {
distCell.setCellFormula(srcCell.getCellFormula());
}
}
}
/**
* <TT><PRE>
* <p> 项目值出力函数. </p>
* @param sheet 表
* @param rowIndex 行番号
* @param colIndex 列番号
* @param value 値
* </PRE></TT>
*/
protected void setCellValue(Sheet sheet, int rowIndex, int colIndex, Object value) {
if (value == null) {
return;
}
Row row = sheet.getRow(rowIndex);
if (row == null) {
row = sheet.createRow(rowIndex);
}
Cell cell = row.getCell(colIndex);
if (cell == null) {
cell = row.createCell(colIndex);
}
if (value instanceof String) {
cell.setCellValue(value.toString());
} else if (value instanceof Double) {
cell.setCellValue((Double) value);
} else if (value instanceof Integer) {
cell.setCellValue((Integer) value);
} else if (value instanceof Float) {
cell.setCellValue((Float) value);
} else if (value instanceof Boolean) {
cell.setCellValue((Boolean) value);
} else if (value instanceof java.util.Date | value instanceof java.sql.Date) {
cell.setCellValue((Date) value);
}
}