最近好几张报表的excel导出功能,两个系统实现方法不一样,先把这两种方法记下来,方便以后复用^_^
1.使用JSP导出excel
导出按钮实现代码:
<img src="<%=path%>/Img/button/button_excel_enabled.gif" οnclick="exportExcel()" />
function exportExcel(){
document.form1.action="pactReportAjax_getPactScoresReportExcel.action ";
document.form1.target="_blank";
document.form1.submit();
}
导出excel的页面实现代码:
<%@ page language="java" pageEncoding="utf-8"%>
<%@ taglib prefix="s" uri="/struts-tags"%>
<%@page contentType="application/msexcel" %>
<%String path = request.getContextPath();
request.setCharacterEncoding( "UTF-8");
String fileName = new String("报表名称".getBytes("GBK"),"ISO-8859-1");
response.setHeader("Content-disposition","attachment; filename="+fileName+".xls" );
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<style type="text/css">
table{font-size:12px;}
th{height:115px}
.xlsText{mso-number-format:"\@";}
.xlsNum{mso-number-format:"0\.00" 2 Decimals;}
.xlsNum2{mso-number-format:"$\#\,\#\#0\.00";}
</style>
</head>
<body >
<table border=1px width="100%" >
<tr>
<th>内部编号</th>
<th>合同编码</th>
<th>售后项目(8位)</th>
<th>售后项目(5位)</th>
<th>售前PA号</th>
<th>合同名称</th>
<th>签约日期</th>
</tr>
<s:iterator value="reportList" id="pactBaseinfo" status="pactInfo">
<tr >
<td class='xlsText'>
<s:property value="pact_id"/>
</td>
<td >
<div class='xlsText'>
<s:property value="pact_code"/>
</div>
</td>
<td class='xlsText'>
<s:property value="project_code"/>
</td>
<td class='xlsText'>
<s:property value="project_code2"/>
</td>
<td class='xlsText'>
<s:property value="p_project_code"/>
</td>
<td class='xlsText'>
<s:property value="pact_name"/>
</td>
<td>
<s:date name="sign_date" format="yyyy-MM-dd"/>
</td>
<td class='xlsNum2'>
<s:if test='exist_freetax == "1"'>
是
</s:if>
<s:else>
否
</s:else>
</td>
</tr>
</s:iterator>
</table>
</body>
</html>
2.使用Java导出excel
导出按钮实现:
<a href='#' class="g_a"> <span class="g_b" id="a_excel"><span
class="g_c"><font color="black">导出到excel</font> </span> </span> </a>
//导出excle
function doExcel(){
var form = $('#listForm');
var url=getRootPath() + "/mreport/buReportForm_doExcel.action?method=sumarry";
form.attr("action",url);
form.submit();
}
java导出实现代码:
/**
* 导出到excel
*
* @return
* @throws Exception
*/
public String doExcel() throws Exception {
HttpServletRequest request = ServletActionContext.getRequest();
HttpSession session = request.getSession();
HttpServletResponse response = ServletActionContext.getResponse();
AiiEmployeeInfoCurV userLoginInfo = (AiiEmployeeInfoCurV) session.getAttribute("USERINFO");
String loginAlert = "";
if (userLoginInfo == null) {
loginAlert = "alert('登陆失败!')";
return "loginError";
}
Long pid = userLoginInfo.getPersonID();
BUReportFormVO buvo = new BUReportFormVO();
buvo.setProjectCode(projectCode);
buvo.setProjectName(projectName);
buvo.setManagerID(managerID);
buvo.setAreaID(areaID);
buvo.setProvinceID(provinceID);
buvo.setOrgID(orgID);
buvo.setPersonID(pid.toString());
log.debug("parameter is projectCode=" + projectCode + "projectName=" + projectName + "managerID" + managerID + "areaID=" + areaID + "provinceID=" + provinceID + "orgID="
+ orgID + "pid=" + pid);
String method = request.getParameter("method");
String date = new SimpleDateFormat("yyyyMMdd").format(new Date());
if (StringUtils.isNotEmpty(method) && "projectManager".equals(method)) {
buvo.setTitle("XX报表" + (date));
buvo.setReportType("manager");
} else {
buvo.setTitle("XXX报表" + (date));
buvo.setReportType("bu");
}
buvo.setCompanyID(companyId);
buvo.setBuID(sbuId);
resultList = buReportFormService.resultSetToExcel(buvo);
log.debug("resultList size is " + resultList.size());
ExcelBean eb = new ExcelBean();
String FIELDS = "projectCode-XXX编码|labbudget-XX预算-money|laboractual-截止到当前累计发生人工日-money|leavelabor-截止到当前剩余人工日-money|laborpercent_new-截止到当前累计发生人工日占总预算比例-percent|"
+ "ssfbudget-XXX费-money|wbfbudget-外包费-money|pxfbudget-培训费-money|ssfactual-实施费-money|wbfactual-外包费-money|"
+ "pxfactual-XXX费-money|leavessf-实施费-money|leavewbf-外包费-money|leavepxf-培训费-money|costpercent_new-截止到当前累计发生成本 占总直接费用预算的比例-percent|pocNew-项目截止到当前POC(供参考)-percent|"
+ "mgrApproveTran-项目经理审核通过-money|mgrUnApproveTran-XX审核-money|accruedactual-XXX预提/冲预提-money|thisMonthUnAcc-当月冲预提-money|actualMgrApproveZTAndCYTAndThisMonth-实际+在途(项目经理审核通过) +当月冲预提-money|"
+ "costpercent1_new-(XXX当月冲预提) /总预算-percent";
eb.setTITLE(buvo.getTitle());
eb.setResultList(resultList);
eb.setFIELDS(FIELDS.split("\\|"));
HttpServletResponse resp = ServletActionContext.getResponse();
response.setContentType("application/octet-stream;");
String filename = null;
try {
filename = java.net.URLEncoder.encode(buvo.getTitle().toString(), "UTF-8") + ".xls";
} catch (UnsupportedEncodingException e) {
log.debug("export excel fail", e);
e.printStackTrace();
return "error";
}
response.addHeader("Content-Disposition", "attachment;filename=" + filename);
eb.setOut(response.getOutputStream());
// eb.setFileUrl("F:/BudgetNew/WebRoot/excelModel/BU报表查询.xls");
String saveUrl = "";
if (StringUtils.isNotEmpty(method) && "projectManager".equals(method)) {
saveUrl = request.getRealPath("/") + "excelModel" + File.separator + "报表查询.xls";
} else {
saveUrl = request.getRealPath("/") + "excelModel" + File.separator + "XX报表查询.xls";
}
eb.setFileUrl(saveUrl);
setEB(eb);
doExportByTemplet();
return "blank";
}
// 根据模板文件导出Excle(为了导出合计)
public void doExportByTemplet() throws Exception {
splitFields();
prepareHeadByTemplet();
writeDataTwo();
wb.write(EB.getOut());
EB.getOut().flush();
EB.getOut().close();
}
private void splitFields() throws Exception {
String[] fields = EB.getFIELDS();
String[] tempfied;
HEADS = new ArrayList<String>();
CELLS = new ArrayList<String>();
CELLDATATYPES = new ArrayList<Integer>();
CELLDATAFORMATS = new ArrayList<Short>();
for (String field : fields) {
tempfied = field.split("-");
HEADS.add(tempfied[1]);
CELLS.add(tempfied[0]);
if (3 == tempfied.length) {
if (tempfied[2].equalsIgnoreCase("percent")) {
CELLDATAFORMATS.add(HSSFDataFormat.getBuiltinFormat(PERCENT_FORMAT));
} else if (tempfied[2].equalsIgnoreCase("money")) {
CELLDATAFORMATS.add(HSSFDataFormat.getBuiltinFormat(MONEY_FORMAT));
} else {
CELLDATAFORMATS.add((short) 0);
}
CELLDATATYPES.add(HSSFCell.CELL_TYPE_NUMERIC);
} else {
CELLDATATYPES.add(HSSFCell.CELL_TYPE_STRING);
CELLDATAFORMATS.add((short) 0);
}
}
}
private void prepareHeadByTemplet() throws Exception {
try {
String fileUrl = EB.getFileUrl();
File f = new File(fileUrl);
InputStream in = new FileInputStream(f);
wb = new HSSFWorkbook(in);
HSSFSheet sheet = wb.getSheetAt(0);
beginRowNum = sheet.getLastRowNum() + 1;
} catch (Exception e) {
e.printStackTrace();
}
}
private void writeDataTwo() throws Exception {
// 货币格式
HSSFCellStyle styleMoney = null;
// 百分比格式
HSSFCellStyle stylePercent = null;
HSSFCellStyle style = wb.createCellStyle();
style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
style.setBorderRight(HSSFCellStyle.BORDER_THIN);
style.setBorderTop(HSSFCellStyle.BORDER_THIN);
// 设置这些样式
// style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
// 生成一个字体
HSSFFont fontH = wb.createFont();
fontH.setFontName("宋体");
fontH.setFontHeightInPoints((short) 9);
style.setFont(fontH);
int rows = beginRowNum;
int cursheet = 0;
HttpServletRequest request = ServletActionContext.getRequest();
HSSFSheet sheet = wb.getSheetAt(cursheet);
String method = request.getParameter("method");
List<Map<String, Object>> rslist = EB.getResultList();
for (Map<String, Object> rs : rslist) {
if (rows > SINGLESHEET_MAXROWS) {
cursheet++;
sheet = wb.getSheetAt(cursheet);
rows = beginRowNum; //
}
HSSFRow row = sheet.createRow(rows);
for (int j = 0; j < CELLS.size(); j++) {
HSSFCell cell = row.createCell(j);
Object cellValue = rs.get(CELLS.get(j));
cell.setCellType(CELLDATATYPES.get(j));
if (CELLDATAFORMATS.get(j) == HSSFDataFormat.getBuiltinFormat(MONEY_FORMAT)) {
if (styleMoney == null) {
styleMoney = wb.createCellStyle();
styleMoney.cloneStyleFrom(style);
styleMoney.setDataFormat(HSSFDataFormat.getBuiltinFormat(MONEY_FORMAT));
}
cell.setCellStyle(styleMoney);
} else if (CELLDATAFORMATS.get(j) == HSSFDataFormat.getBuiltinFormat(PERCENT_FORMAT)) {
if (stylePercent == null) {
stylePercent = wb.createCellStyle();
stylePercent.cloneStyleFrom(style);
stylePercent.setDataFormat(HSSFDataFormat.getBuiltinFormat(PERCENT_FORMAT));
}
if (cellValue != null && StringUtils.isNotEmpty(String.valueOf(cellValue))) {
cellValue = com.linkage.framework.pub.utils.StringUtils.formatDou(cellValue) * 0.01;
}
cell.setCellStyle(stylePercent);
} else {
cell.setCellStyle(style);
}
if (null == cellValue || StringUtils.isBlank(String.valueOf(cellValue))) {
continue;
}
if (HSSFCell.CELL_TYPE_NUMERIC == CELLDATATYPES.get(j).intValue()) {
cell.setCellValue(Double.parseDouble(String.valueOf(cellValue)));
} else {
if (null != rs.get(CELLS.get(j))) {
cell.setCellValue(objToStr(cellValue));
}
}
style.setDataFormat(CELLDATAFORMATS.get(j));
}
rows++;
if (rows == rslist.size() + 2) {
HSSFRow row2 = sheet.createRow(rows + 1);
for (int j = 0; j < CELLS.size(); j++) {
HSSFCell cell = row2.createCell(j);
cell.setCellStyle(style);
cell.setCellType(CELLDATATYPES.get(j));
styleMoney = wb.createCellStyle();
styleMoney.cloneStyleFrom(style);
styleMoney.setDataFormat(HSSFDataFormat.getBuiltinFormat(MONEY_FORMAT));
cell.setCellStyle(styleMoney);
}
if ("sumarry".equals(method) || "projectManager".equals(method)) {
HSSFRow rowTit = sheet.getRow(rows + 1);
if (resultList.size() > 0) {
rowTit.getCell(9).setCellValue("合计");
rowTit.getCell(25).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allMgrApproveTran").toString()));
rowTit.getCell(26).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allMgrUnApproveTran").toString()));
rowTit.getCell(27).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allaccruedactual").toString()));
rowTit.getCell(28).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allThisMonthUnAcc").toString()));
rowTit.getCell(29).setCellValue(Double.parseDouble(((Map) resultList.get(0)).get("allActualMgrApproveZTAndCYTAndThisMonth").toString()));
rowTit.getCell(30).setCellValue("");
}
}
}
}
}
/**
* 对象转字符串
*
* @param obj
* @return
*/
private String objToStr(Object obj) {
try {
if (obj.getClass().getName().equals("java.util.Date")) {
String str = DateUtil.format((java.util.Date) obj, "yyyy-MM-dd");
if (str.endsWith(" 00:00:00")) {
return str.substring(0, str.length() - 9);
}
return str;
} else {
return String.valueOf(obj);
}
} catch (Exception e) {
System.out.println(e.toString());
e.printStackTrace();
return String.valueOf(obj);
}
}
3.存储过程调用:
public List queryBUReportForm(BUReportFormVO vo) {
List resultList = new ArrayList();
DecimalFormat df = new DecimalFormat("#,###,###,##0.00");
try {
String sql = "{call proc_get_XXX_report(?,?,?,?,?,?,?,?,?,?)}";
ResultSet rs = FrameworkApplication.getBaseJdbcDAO().queryForResultSet(
sql,
new String[] { vo.getPersonID(), vo.getProjectCode(), vo.getProjectName(), vo.getManagerID(), vo.getAreaID(), vo.getProvinceID(), vo.getOrgID(),
vo.getReportType(), vo.getCompanyID(), vo.getBuID() });
int i = 0;
if (null != rs) {
while (rs.next()) {
Map map = new HashMap();
map.put("projectID", defaultIfEmpty(rs.getString("PROJECT_ID"), ""));
map.put("costpercent1_new", costpercent1.replaceAll("%", ""));
String poc = rs.getString("poc");
map.put("poc", poc);
// map.put("pocNew", poc.replaceAll("%", ""));
map.put("pocNew", poc.substring(0, poc.indexOf("%")));
//项目本位币种
map.put("projfuncCurrencyCode", rs.getString("projfunc_currency_code"));
i++;
if (i == 1) {
// 合计
map.put("alllabbudget", rs.getDouble("alllabbudget"));
map.put("alllabactual", rs.getDouble("alllabactual"));
map.put("allActualMgrApproveZTAndCYTAndThisMonth", rs.getDouble("allactual_mgr_approve_zt_cyt_this_month"));
map.put("allaccruedactual", rs.getDouble("allaccruedactual"));
}
resultList.add(map);
}
}
} catch (Exception e) {
log.debug("query Budget.dbo.proc_get_project_report error", e);
e.printStackTrace();
}
return resultList;
}