Excel导出Fuction

方法一、1.在JSP上设定打印按钮
<!—-align:表示在页面上所在的位置
<input>标签可定义输入域的开始，在其中用户可输入数据。
http://www.w3school.com.cn/tags/tag_input.asp
onclick:当button单击时触发的Function.
-->

<td align="right"><input type="button" value="Excel导出"
 onclick="fun_excelReport()" /></td>

在<script language=”javascript”></script>中
添加如下Function.

Function fun_excelReport()
{
<!—-pageForm:<Form action=……name="pagedForm" > -->
document.pagedForm.action="<%= request.getContextPath()%>/excelReport.do";
document.pagedForm.submit();	
<!—- 整个页面进行执行 -->
document.pagedForm.action="<%=request.getContextPath()%>/distBudgetDistributeConfigureAction.do"
}
2．在struts

中
<!—-path:指定.do的对象
Forward:定义返回的变量。来指向页面。
-->

<action path=”/excelReport” scope=”request”>
<forward name=”displayPage” path=”/jsp/distbudgetdistribute/show.jsp”/>
</action>

3．在spring中

<bean name="/excelReport" parent="actionTemplate">
<property name="target">
<bean class="com.suning.biz.distbudgetdistribute.web.action.ExcelReportAction">
<property name="distBudgetDistributeConfigureService" ref="distBudgetDistributeConfigureService" />
</bean>
</property>
</bean>

4．Action

/**Response.setContentType的意思是网址返回的内容类型
*charset=gb2312这个是用中文的编码来解释页面;如果你的页面里有中文就要注意了.一定要注意编码的格式.要不然会有乱码的.*/
response.setContentType("application/vnd.ms-excel;charset=GB2312");
/*文件下载，指定默认名srxljl
Response.AddHeader("content-type","application/x-msdownload");
Response.AddHeader("Content-Disposition","attachment;filename=要下载的文件名.rar");*/
response.addHeader("Content-Disposition", "inline;filename=\""
+ fileName + "\"");

方法二、页面上的Excel打印，问题在于客户端没有安装office excel时会无法导出；用户体验性很差

<input name="exportBtn" type="button" class="button" value="导出" onclick="exportToExcel();"/>

<table class="tb_result" id="PrintA" border="0" cellspacing="1" cellpadding="1" align="center">
</table>

//指定页面区域内容导入Excel
 function exportToExcel()
 {
 	var oXL;
 	try {
 		oXL = new ActiveXObject("Excel.Application");
 	} catch(e) {
 		try {
 			oXL = new ActiveXObject("ET.Application");
 		} catch(e) {
 			alert( "您的电脑没有安装Microsoft Excel软件！ ");
 			return;
 		}
 	}
	//var oXL = new ActiveXObject("Excel.Application");
	var oWB = oXL.Workbooks.Add();
	var oSheet = oWB.ActiveSheet;
	var sel=document.body.createTextRange();
	sel.moveToElementText(PrintA);
	sel.select();
	sel.execCommand("Copy");
	oSheet.Paste();
	oXL.Visible = true;
 }

方法三、后台打印Excel

/**
* 导出Excel
*/
public String export() {
LevelBean bean = null;
String[] ss = null;
List<String[]> list = new ArrayList<String[]>();
//pageList:从数据库中得到的数据
for (int i = 0; i < pageList.size(); i++) {
   bean = (LevelBean) pageList.get(i);
   ss = new String[4];
   ss[0] = bean.getTypeName();
   ss[1] = bean.getTypeDetail();
   ss[2] = "人数";
   ss[3] = bean.getAmount();
   list.add(ss);
}
String workSheet = "层级管理";// 输出的excel文件工作表名
String fileName = "CJGL.xls";
String[] title = { "", "", "", "" };// excel工作表的标题
Integer[] width = { 15, 35, 15, 15 };
export(workSheet, fileName, title, width, list);
return null;
}

/**
* 导出Excel表
* @param workSheet 输出的excel文件工作表名
* @param fileName excel文件名
* @param title excel工作表的标题
* @param width 数据所占大小
* @param list 数据
*/
public void export(String workSheet, String fileName, String[] title, Integer[] width, List<String[]> list) {
HttpServletResponse response = ServletActionContext.getResponse();
response.setCharacterEncoding("UTF-8");
response.setContentType("application/vnd.ms-excel;chartset=utf-8");
response.addHeader("Content-Disposition", "attachment; filename=" + fileName);
OutputStream os = null;
WritableWorkbook workbook = null;
try {
  os = response.getOutputStream();
  workbook = Workbook.createWorkbook(os);
  //生成名为“第一页”的工作表，参数0表示这是第一页 
  WritableSheet sheet = workbook.createSheet(workSheet, 0); // 添加第一个工作表
  jxl.write.Label label;

  //合并单元格 
  sheet.mergeCells(0, 0, title.length - 1, 1);// 左上角到右下角
  jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.ARIAL, 18, WritableFont.BOLD, false);
  jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
  wcfF.setAlignment(jxl.format.Alignment.CENTRE);// 把水平对齐方式指定为居中

  //在Label对象的构造子中指名单元格位置是第一列第一行(0,0) 　　
  //以及单元格内容为workSheet
  sheet.addCell(new Label(0, 0, workSheet, wcfF));////将定义好的单元格添加到工作表中 
  for (int i = 0; i < title.length; i++) {
     // Label(列号,行号 ,内容 )
    label = new jxl.write.Label(i, 2, title[i]); // put the title
    sheet.addCell(label);
    sheet.setColumnView(i, width[i]);
  }
  for (int i = 0; i < list.size(); i++) {
    String[] ss = list.get(i);
    for (int j = 0; j < ss.length; j++) {
      sheet.addCell(new Label(j, i + 3, ss[j]));
    }
  }
//写入数据并关闭文件 
workbook.write();
workbook.close();
try {
  if (os != null) {
    os.flush();
    os.close();
    os = null;
  }
} catch (Exception e) {
  e.printStackTrace();
}
} catch (Exception e) {
  e.printStackTrace();
}
}

四、方式四 JavaScript+CSS打印技术

  <a href="#" onClick="window.print()">JavaScript脚本打印</a>

五、jxl导出excel表(用repord.zip中的jar包)

package com.jshx.common.excelModule;

import java.io.IOException;
import java.util.Map;

import com.jshx.core.base.action.BaseAction;

public class BaseExcel extends BaseAction{
	public String exportExcel(String filename, Map dataMap, String model) throws IOException{
		ExportExcel exportExcel = new ExportExcel();
		exportExcel.export(filename, dataMap, model, getResponse());
		return null;
	}

}

package com.jshx.common.excelModule;

import java.io.IOException;
import java.io.InputStream;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExportExcel {

	public void export(String filename, Map dataMap, String model, HttpServletResponse response) throws IOException{
		XLSTransformer transformer = new XLSTransformer();
		HSSFWorkbook workbook = null;
		InputStream in;
		try {
			in = getClass().getResource(model).openStream();
			workbook = transformer.transformXLS(in, dataMap);
			outExcel(workbook, response, filename);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	protected void outExcel(HSSFWorkbook workbook, HttpServletResponse response,
		    String filename) {
		   response.setContentType("application ns.ms-excel");
		   response.setHeader("Expires", "0");
		   response.setHeader("Cache-Control",
		     "must-revalidate, post-check=0, pre-check=0");
		   response.setHeader("Pragma", "public");
		   response.setHeader("Content-disposition", "attachment;filename="+filename+".xls");

		   try {
				workbook.write(response.getOutputStream());
		   } catch (ParsePropertyException e) {
				e.printStackTrace();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
}

public void report() throws Exception{
		Map<String, Object> paraMap = new HashMap<String, Object>();
		paraMap.put("deptCodes", this.getLoginUserDepartment().getChildDeptIds());
		if(null != jshxSales){
		    //设置查询条件，开发人员可以在此增加过滤条件
		}
		resultList = jshxSalesService.reportJshxSalesByMap(paraMap);
		Map dataMap = new HashMap();
		dataMap.put("statList", resultList);
		exportExcel("jshxSales", dataMap, "jshxSales.xls");
	}

function report_jshxProgram(){
            document.myform.action = "jshxProgram_report.action";
            document.myform.submit();
        }