1、Html代码
1 <script type="text/javascript"> 2 3 function doExportExc(){ 4 tableForm.action='<%=path %>/RentTransfer/RentExcel!exportExcel.action'; 5 tableForm.submit(); 6 } 7 </script> 8 9 10 11 <a href="#" class="easyui-linkbutton" data-options="plain:true,iconCls:'icon-edit'" onclick="doExportExc()">导出EXCEL</a>
2、struts配置文件
<!-- 租金交接清单 --> <package name="RentTransfer" extends="struts-default" namespace="/RentTransfer"> <action name="RentExcel" class="com.netcom.shyj.finance.action.RentTransferAction" method="exportExcel"> <result name="success" type="stream"> <!-- 下载文件的类型，如果你不知道是什么格式，可以去 tomcat\conf\web.xml下找 --> <param name="contentType">application/vnd.ms-excel</param> <!-- 返回流 excelStream为action中的流变量名称 --> <param name="inputName">excelStream</param> <!-- attachment 这个位置的参数挺特殊的，可以设置成下载时，是否出现个下载提示框，或者直接下载之类的。 fileName指定生成的文件名字(适合动态生成文件名，比如做报表时，一般都要说是几月的统计数据之类)为action中变量--> <param name="contentDisposition"> attachment;filename=${excelFileName} </param> <param name="bufferSize">1024</param> </result> </action> </package>
3、Action类
package com.netcom.shyj.finance.action; import java.io.ByteArrayInputStream; import java.io.ByteArrayOutputStream; import java.io.IOException; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import java.util.Map; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import net.sf.json.JSONArray; import net.sf.json.JSONObject; import org.apache.poi.hssf.usermodel.HSSFCell; import org.apache.poi.hssf.usermodel.HSSFCellStyle; import org.apache.poi.hssf.usermodel.HSSFRow; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.struts2.ServletActionContext; import org.apache.struts2.interceptor.ServletRequestAware; import org.apache.struts2.interceptor.ServletResponseAware; import org.apache.struts2.interceptor.SessionAware; import com.netcom.nkestate.framework.html.HtmlTD; import com.netcom.nkestate.framework.html.HtmlTableUtil; import com.netcom.nkestate.framework.html.TableProperty; import com.netcom.nkestate.framework.util.DateUtil; import com.netcom.shyj.accountbook.vo.AccountBookVO; import com.netcom.shyj.common.BaseAction; import com.netcom.shyj.finance.bo.RentTransferBO; import freemarker.log.Logger; public class RentTransferAction extends BaseAction implements SessionAware ,ServletRequestAware { private Map<String , Object> session; public String exportExcel(){ try { //第一步，创建一个webbook，对应一个Excel文件 HSSFWorkbook wb = new HSSFWorkbook(); //第二步，在webbook中添加一个sheet，对应Excel文件中的 sheet HSSFSheet sheet = wb.createSheet("sheet1"); //第三步，在sheet中添加表头第0行，注意老版本poi对Excel的行数列数有限制 HSSFRow row = sheet.createRow(0); //第四步，创建单元格样式：居中 HSSFCellStyle style = wb.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //第五步，创建表头单元格，并设置样式 HSSFCell cell; //设置列的宽度 sheet.setColumnWidth(0, 3766); sheet.setColumnWidth(1, 3766); sheet.setColumnWidth(2, 3766); sheet.setColumnWidth(3, 3766); sheet.setColumnWidth(4, 4766); sheet.setColumnWidth(5, 3766); sheet.setColumnWidth(6, 3766); sheet.setColumnWidth(7, 6766); sheet.setColumnWidth(8, 3766); sheet.setColumnWidth(9, 3766); sheet.setColumnWidth(10, 6766); sheet.setColumnWidth(11, 3766); cell = row.createCell(0); cell.setCellValue("实收时间"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue("承租人"); cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue("社区"); cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue("支付类型"); cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue("合同号"); cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue("房租/月"); cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue("房屋押金"); cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue("租赁期"); cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue("应收时间"); cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue("金额"); cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue("资金大类—资金小类"); cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue("合同签订方式"); cell.setCellStyle(style); //第六步，写入实体数据，实际应用中这些数据从数据库得到 SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd"); RentTransferBO bo = new RentTransferBO(); List<AccountBookVO> list = new ArrayList<AccountBookVO>(); list = bo.query(startDate, endDate, blockID, inOut, settleState, null, metaOrder, user); int count = 1; double amountCount=0.00; for(AccountBookVO vo : list){ //创建一行 row = sheet.createRow(count); cell = row.createCell(0); cell.setCellValue(vo.getRealFeeDate()==null?"":fmt.format(vo.getRealFeeDate()));//实收时间 cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(vo.getAttribute("BName")+"");//承租人 cell.setCellStyle(style); cell = row.createCell(2); cell.setCellValue(vo.getAttribute("blockName")+"");//社区 cell.setCellStyle(style); cell = row.createCell(3); cell.setCellValue(vo.getAttribute("payType_dict_name")==null?"":vo.getAttribute("payType_dict_name")+"");//支付类型 cell.setCellStyle(style); cell = row.createCell(4); cell.setCellValue(vo.getContractNo());//合同号 cell.setCellStyle(style); cell = row.createCell(5); cell.setCellValue(vo.getAttribute("rentValue")+"");//房租/月 cell.setCellStyle(style); cell = row.createCell(6); cell.setCellValue(vo.getAttribute("deposit")+"");//房屋押金 cell.setCellStyle(style); cell = row.createCell(7); cell.setCellValue(vo.getAttribute("rentDate")+"");//租赁期 cell.setCellStyle(style); cell = row.createCell(8); cell.setCellValue(fmt.format(vo.getPlanDate()));//应收时间 cell.setCellStyle(style); cell = row.createCell(9); cell.setCellValue(vo.getAmount()+"");//金额 cell.setCellStyle(style); cell = row.createCell(10); cell.setCellValue(vo.getAttribute("feeKind_dict_name")+" "+(vo.getAttribute("feeType_dict_name")==null?"":vo.getAttribute("feeType_dict_name")));//资金大类-资金小类 cell.setCellStyle(style); cell = row.createCell(11); cell.setCellValue(vo.getSignTypeStr());//合同签订方式 cell.setCellStyle(style); count++; amountCount+=vo.getAmount(); } //总计金额 row = sheet.createRow(count); cell = row.createCell(0); cell.setCellValue("总计金额"); cell.setCellStyle(style); cell = row.createCell(1); cell.setCellValue(amountCount+""); cell.setCellStyle(style); //第七步，将文件存到流中 ByteArrayOutputStream os = new ByteArrayOutputStream(); wb.write(os); byte[] fileContent = os.toByteArray(); ByteArrayInputStream is = new ByteArrayInputStream(fileContent); String today = DateUtil.getSysDateToString(); excelStream = is; //文件流 excelFileName = "RentTransfer-"+today+".xls"; //设置下载的文件名 } catch(Exception e) { e.printStackTrace(); } return "success"; } private InputStream excelStream; //输出流变量 private String excelFileName; //下载文件名 public InputStream getExcelStream() { return excelStream; } public void setExcelStream(InputStream excelStream) { this.excelStream = excelStream; } public String getExcelFileName() { return excelFileName; } public void setExcelFileName(String excelFileName) { this.excelFileName = excelFileName; } public Map<String , Object> getSession() { return session; } public void setSession(Map<String , Object> session) { this.session = session; } @Override public void setServletRequest(HttpServletRequest arg0) { // TODO Auto-generated method stub } }
