工具类。
前台js调用。
window.location.href = url;
public class ExportExcelUtil {
private Map<String,Object> beans = new HashMap<String,Object>();
private String exportFileName = "export.xls";
public void setBeans(String key,Object obj){
//装载输入对象
beans.put(key, obj);
}
public ExportExcelUtil() {
//初始化日期格式化工具对象到beans里。
//excel模版调用方式:${dateUtil.formatToDate(entity.checkDate)}
// ${dateUtil.formatToDateTime(entity.checkDateTime)}
beans.put("dateUtil", new DateUtil());
}
public void exportExcel(String fileName){
try {
Struts2Utils.getRequest().setCharacterEncoding("UTF-8");
} catch (UnsupportedEncodingException e1) {
e1.printStackTrace();
}
String systemPath = this.getClass().getResource("/").getPath();
//模版文件路径
String templateFileName = systemPath + "templates/"+fileName+".xls";
// File outFile = new File(outFileName);
XLSTransformer transformer = new XLSTransformer();
try {
InputStream is = new BufferedInputStream(new FileInputStream(templateFileName));
// transformer.transformXLS(templateFileName, beans, outFileName);
//读取并解析模版文件,填充模版标记中对应的值
HSSFWorkbook workbook = (HSSFWorkbook) transformer.transformXLS(is, beans);
resetCellFormula(workbook);
//设置输出环境信息-
Struts2Utils.getResponse().reset();
//输出的xls文件名 格式 2013-04-01
String outFileName = DateUtil.formatToDate(new Date())+new String(fileName.getBytes("gb2312"),"ISO8859-1");
Struts2Utils.getResponse().setHeader("Content-Type","application/force-download");
Struts2Utils.getResponse().setHeader("Content-Type","application/vnd.ms-excel");
Struts2Utils.getResponse().setHeader("Content-Disposition", "attachment;success=true;filename="+outFileName+".xls");
//利用response获得流对象,写出excel文件
OutputStream os = Struts2Utils.getResponse().getOutputStream();
workbook.write(os);
is.close();
os.flush();
os.close();
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
public String getExportFileName() {
return exportFileName;
}
public void setExportFileName(String exportFileName) {
this.exportFileName = exportFileName;
}
//自动解析单元格里的公式。
public void resetCellFormula(HSSFWorkbook wb) {
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(wb);
//获取工作簿集合
int sheetNum = wb.getNumberOfSheets();
//遍历工作簿
for (int i = 0; i < sheetNum; i++) {
HSSFSheet sheet = wb.getSheetAt(i);
int rows = sheet.getLastRowNum() + 1;//获取总行数
for (int j = 0; j < rows; j++) {
HSSFRow row = sheet.getRow(j);
if (row == null)
continue;
int cols = row.getLastCellNum();//获取最后一列
for (int k = 0; k < cols; k++) {
HSSFCell cell = row.getCell(k);
if(cell!=null){
//如果单元格是公式,则直接解析公式内容
if (cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
cell.setCellFormula(cell.getCellFormula()); cell=e.evaluateInCell(cell);
}
} else {
continue;
}
}
}
}
}
前台js调用。
window.location.href = url;