项目中需要做导出功能,要支持excel,pdf导出。
excel导出我选择使用poi;pdf导出我选择使用iText,在此单独做记录。
先说说在设计与开发中的问题,从整体上把握设计思路。
思路(一)
在页面json拼装数据,在后台导出pdf文件到客户端。
本例子只是简单的导出列表数据,没有涉及到图片等其他复杂格式文件。所以还是比较简单的。
这个设计思路是最理想的。
在后台获取前台数据,把这些数据直接生成pdf文件流输出到response中去,
不会在服务器留下垃圾文件。在客户端会弹出文件保存框,直接保存到客户端本地。
-------------------
这个思路很好,但是在导出到客户端时我遇到问题,就是弹不出文件保存框,页面没什么反应。用ajax回调函数
弹出返回数据是一堆乱码。
基于此原因,我改用
思路(二)
把pdf文件生成在服务器下的一个目录下,在ajax回调函数中使用这个拼装成访问路径,
这样可以下载文件。如果浏览器总是弹出阻止下载文件的弹出提示,可以在浏览器工具--internet 选项--
安全--自定义级别--下载,改一下:文件下载:启用;文件下载的自动提示:启用。都是启用就可以了,
这样就不会在弹出了。
这样也有问题,1会在服务器下生成许多垃圾文件,需要维护人员定期的删除,不然硬盘就不够用了。
2在客户端访问生成的文件会是zip文件或者是没有后缀的文件格式,或者直接在浏览器直接打开了。
如果是zip或者是其他格式不被浏览器识别,解决问题的方法就是在web.xml中设置mime类型。
这个就不细说了,网上一大堆。如果直接在浏览器打开pdf文件,那可以在你电脑安装的pdf阅读器设置里
把在浏览器中打开这一项去掉。
这样还是会有问题:
1,web.xml设置的mime类型对整个工程都是有效地,在这里我想让浏览器下载这个类型的文件而不是打开,所以
我设置了mime;而在其他地方就是需要浏览器打开这个类型的文件提供在线阅读,而不是弹出下载的文件选择框。
所以设置mime类型是会发生冲突的。
2,在pdf阅读器设置取消掉“在浏览器打开”这一项不太符合用户习惯,用户使用这个功能还需要设置自己电脑上的其他
软件,这个太不合理了。
这样的问题,我决定改变访问方式,使用下载,而不是直接给文件在服务器的地址直接访问。
思路(三)
可以在服务器路径下生成pdf文件,对于访问方式采用下载方式,下载完成后再删除此文件。
这和思路(一)区别不是很大,之所以采用这种方式,是因为在设置文件类型时,
用代码设置response.setContentType("application/pdf");
response.addHeader("Content-Disposition","attachment;filename="+serfilename.trim());
serfilename是生成的临时pdf文件的名字,随机的,这两行代码必须在调用输入输出流之前,
因为只要一调用流往客户端写文件,就会弹出文件保存框,这两行代码还没走到,所以是无效的。
如果写在调用流之前,这个文件的名字是在调用流之后才生成的文件名。
所以,读写的逻辑自己定。
你的方法如果能封装OutpuStream,那就把response对象也传入你的方法中,并在调用流之前先调用这两行代码。
如果你的方法不能封装OutpuStream,那就先把文件写在服务器下路径,生成文件后,调用这两行代码,
然后自己再写读写文件到response。只要保证在读写流调用前就可以。
所以思路(三)和思路(一)是差不多的。
------------------
总之,
1,在web.xml中不要设置mime,在程序中能控制的就不要在控制全局的web.xml中设置。
2,文件的访问最好用下载方式,不要直接贴个url做成链接。
3,不要在服务器下生成垃圾文件。临时的文件用过后要删除。最好别生成临时文件。
-------------------
pdf导出我采用方式(三),生成临时文件,下载后删除。
excel导出我采用方式(一),直接写到客户端。
这是最后的代码,采用servlet方式。
web.xml 注意注释掉的部分
- <servlet>
- <description>ExportExcelServletDescription</description>
- <display-name>ExportExcelServlet</display-name>
- <servlet-name>ExportExcelServlet</servlet-name>
- <servlet-class>org.hd.report.serv.ExportExcelServlet</servlet-class>
- </servlet>
- <servlet-mapping>
- <servlet-name>ExportExcelServlet</servlet-name>
- <url-pattern>/ExportExcelServlet</url-pattern>
- </servlet-mapping>
- <servlet>
- <description>ExportPdfServletDescription</description>
- <display-name>ExportPdfServlet</display-name>
- <servlet-name>ExportPdfServlet</servlet-name>
- <servlet-class>org.hd.report.serv.ExportPdfServlet</servlet-class>
- </servlet>
- <servlet-mapping>
- <servlet-name>ExportPdfServlet</servlet-name>
- <url-pattern>/ExportPdfServlet</url-pattern>
- </servlet-mapping>
- <!--
- <mime-mapping>
- <extension>doc</extension>
- <mime-type>application/msword</mime-type>
- </mime-mapping>
- <mime-mapping>
- <extension>docx</extension>
- <mime-type>application/vnd.openxmlformats-officedocument.wordprocessingml.document</mime-type>
- </mime-mapping>
- <mime-mapping>
- <extension>xls</extension>
- <mime-type>application/msexcel</mime-type>
- </mime-mapping>
- <mime-mapping>
- <extension>xlsx</extension>
- <mime-type>application/vnd.openxmlformats-officedocument.spreadsheetml.sheet</mime-type>
- </mime-mapping>
- <mime-mapping>
- <extension>pdf</extension>
- <mime-type>application/pdf</mime-type>
- </mime-mapping>
- -->
pdf
- package org.hd.report.serv;
- import java.io.BufferedInputStream;
- import java.io.BufferedOutputStream;
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.IOException;
- import java.io.InputStream;
- import java.io.OutputStream;
- import javax.servlet.ServletException;
- import javax.servlet.http.HttpServlet;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import org.hd.util.ExportExcelUtil;
- import org.hd.util.ExportPdfUtil;
- /**
- * Servlet implementation class PdfItextServlet
- */
- public class ExportPdfServlet extends HttpServlet {
- private static final long serialVersionUID = 1L;
- public ExportPdfServlet() {
- super();
- }
- protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
- doPost(request,response);
- }
- protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
- downLoadPdfService(request,response);
- }
- protected void downLoadPdfService(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
- request.setCharacterEncoding("UTF-8");
- response.setCharacterEncoding("UTF-8");
- // response.setContentType("text/xml");
- String thename = request.getParameter("dd") != null ? request.getParameter("dd") : "";
- String resjson= request.getParameter("resjson") != null ? request.getParameter("resjson").trim() : "";
- resjson=java.net.URLDecoder.decode(resjson,"UTF-8");
- System.out.println("resjson from page--"+resjson);
- String clientFileName="";
- String pdfonserver=ExportPdfUtil.createPdfWithJson(resjson);
- // String pdfonserver= request.getParameter("pdfonserver") != null ? request.getParameter("pdfonserver") : "";
- pdfonserver=java.net.URLDecoder.decode(pdfonserver,"UTF-8");
- String serfilename=pdfonserver.substring(pdfonserver.lastIndexOf("/")+1);//xx.pdf
- //如果是变生成文件,边写入输出流到servlet,客户端会在输出流写入的时候就弹出保存文件框,setContentType,addHeader所以必须在输出流之前调用。
- // response.setContentType("octets/stream");
- response.setContentType("application/pdf");
- // response.setContentType("application/x-msdownload");//对pdf这个也可以
- response.addHeader("Content-Disposition","attachment;filename="+serfilename.trim());
- String wholeServerPath=ExportPdfUtil.TEMP_PDF_PATH+"\\"+serfilename;
- File fileOnServer=new File(wholeServerPath.trim());
- if(!fileOnServer.exists()){
- System.out.println("文件未找到。");
- }else{
- InputStream is=new BufferedInputStream(new FileInputStream(fileOnServer));
- OutputStream os=new BufferedOutputStream(response.getOutputStream());
- byte[] buf=new byte[1024];
- while((is.read(buf))!=-1){
- os.write(buf);
- os.flush();
- }
- if(os!=null){
- os.flush();
- os.close();
- }
- if(is!=null){
- is.close();
- }
- if(fileOnServer.exists()){
- boolean flag=fileOnServer.delete();
- if(flag){
- System.out.println(fileOnServer.getPath()+"完成删除。");
- }
- }
- }
- }
- }
excel
- package org.hd.report.serv;
- import java.io.IOException;
- import java.io.OutputStream;
- import javax.servlet.ServletException;
- import javax.servlet.http.HttpServlet;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import org.hd.util.ExportExcelUtil;
- /**
- * Servlet implementation class PdfItextServlet
- */
- public class ExportExcelServlet extends HttpServlet {
- private static final long serialVersionUID = 1L;
- public ExportExcelServlet() {
- super();
- }
- protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
- doPost(request,response);
- }
- protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
- makeExcelService(request,response);
- }
- protected void makeExcelService(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
- request.setCharacterEncoding("UTF-8");
- response.setCharacterEncoding("UTF-8");
- String thename = request.getParameter("dd") != null ? request.getParameter("dd") : "";
- String resjson= request.getParameter("resjson") != null ? request.getParameter("resjson") : "";
- resjson=java.net.URLDecoder.decode(resjson,"UTF-8");
- System.out.println("servlet---resjson--"+resjson);
- OutputStream os=response.getOutputStream();
- String pdfonserver=ExportExcelUtil.exportExcelFromAllStr(resjson, null, os,response);
- pdfonserver=java.net.URLDecoder.decode(pdfonserver,"UTF-8");
- String serfilename=pdfonserver.substring(pdfonserver.lastIndexOf("/")+1);//xx.xlsx
- //如果是变生成文件,边写入输出流到servlet,客户端会在输出流写入的时候就弹出保存文件框,setContentType,addHeader所以必须在输出流之前调用。
- //因为本例输出流已经封装到ExportExcelUtil的方法中,所以就把response传入,在方法内设置。
- // response.setContentType( "application/vnd.ms-excel");//2003
- // response.setContentType("application/ms-excel"); //2003
- // response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//2007
- // response.setContentType("octets/stream");//压缩文件
- // response.setContentType("application/x-msdownload");//压缩文件
- // response.addHeader("Content-Disposition","attachment;filename="+serfilename.trim());
- // System.out.println(serfilename+"成功导出到客户端");
- }
- }
核心代码,工具类:
pdf:
- /*
- * This class is part of the book "iText in Action - 2nd Edition"
- * written by Bruno Lowagie (ISBN: 9781935182610)
- * For more info, go to: http://itextpdf.com/examples/
- * This example only works with the AGPL version of iText.
- */
- package org.hd.util;
- import java.io.File;
- import java.io.FileNotFoundException;
- import java.io.FileOutputStream;
- import java.io.IOException;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- import net.sf.json.JSONObject;
- import org.hd.util.BlobUtil;
- import com.itextpdf.text.BaseColor;
- import com.itextpdf.text.Document;
- import com.itextpdf.text.DocumentException;
- import com.itextpdf.text.Element;
- import com.itextpdf.text.Font;
- import com.itextpdf.text.Paragraph;
- import com.itextpdf.text.Phrase;
- import com.itextpdf.text.Rectangle;
- import com.itextpdf.text.pdf.BaseFont;
- import com.itextpdf.text.pdf.PdfPCell;
- import com.itextpdf.text.pdf.PdfPRow;
- import com.itextpdf.text.pdf.PdfPTable;
- import com.itextpdf.text.pdf.PdfWriter;
- public class ExportPdfUtil {
- /** The resulting PDF file. */
- public static final String RESULT = "D:/column_widths.pdf";
- public static String WEBCONTENT_PATH="";
- public static String BASE_PATH="";
- public static String TEMP_PDF_PATH="";//临时pdf目录
- public static String DirTypePdf="pdf";
- static{
- String clapath=BlobUtil.class.getResource("/").getPath();
- File claFile=new File(clapath);
- String basePath=claFile.getParentFile().getParentFile().getPath();
- ExportPdfUtil.WEBCONTENT_PATH=basePath;
- ExportPdfUtil.BASE_PATH=basePath+"\\tempExport";
- ExportPdfUtil.TEMP_PDF_PATH=ExportPdfUtil.BASE_PATH+"\\pdf";
- File tempBase=new File(ExportPdfUtil.BASE_PATH);
- if(!tempBase.exists()){
- tempBase.mkdir();
- }
- File tempPdf=new File(ExportPdfUtil.TEMP_PDF_PATH);
- if(!tempPdf.exists()){
- tempPdf.mkdir();
- }
- /*
- */
- System.out.println("--ExportPdfUtil.WEBCONTENT_PATH--"+ExportPdfUtil.WEBCONTENT_PATH);
- System.out.println("--ExportPdfUtil.BASE_PATH--"+ExportPdfUtil.BASE_PATH);
- System.out.println("--ExportPdfUtil.TEMP_PDF_PATH--"+ExportPdfUtil.TEMP_PDF_PATH);
- }
- //直接使用json创建pdf文件
- public static String createPdfWithJson(String resjson){
- String info="";
- JSONObject jo=JSONObject.fromObject(resjson);
- String title=(String)jo.get("title");
- String resTableheaders=(String)jo.get("resTableheaders");
- String resList=(String)jo.get("resList");
- String[] resTableheadersArr=ExportExcelUtil.getResTableheadersFromAllStr(resTableheaders);
- String[][] resListArr=ExportExcelUtil.getResListFromAllStr(resList);
- info=ExportPdfUtil.createPdf(title, resTableheadersArr, resListArr);
- return info;
- }
- /**
- * 写文件,创建pdf
- * @param filepath
- * @param document
- * @return
- */
- public static String createPdf(String title,String[] titles,String[][] dataSet){
- String respath=ExportPdfUtil.TEMP_PDF_PATH;
- String filename=ExportPdfUtil.getRandomDateStr();
- Document document = new Document();
- Rectangle rectangle=new Rectangle(1024,900);
- document.setPageSize(rectangle);
- if(document==null||filename==null||filename.trim().equals("")){
- return "error";
- }
- try {
- String abpath1=respath+"\\"+filename.trim()+".pdf";
- respath="tempExport/pdf/"+filename.trim()+".pdf";;
- PdfWriter.getInstance(document, new FileOutputStream(abpath1));
- document.open();
- //标题设置
- String titlestr=title==null?"":title;
- // Phrase phtitle=new Phrase(titlestr,ExportPdfUtil.getChineseFont());
- Paragraph phtitle2=new Paragraph(titlestr,ExportPdfUtil.getChineseFont());
- phtitle2.setAlignment(Element.ALIGN_CENTER);
- document.add(phtitle2);
- PdfPTable table = ExportPdfUtil.createTable(title,titles, dataSet);
- table.setSpacingBefore(15);
- table.setSpacingAfter(15);
- document.add(table);
- document.close();
- } catch (FileNotFoundException e) {
- e.printStackTrace();
- } catch (DocumentException e) {
- e.printStackTrace();
- }
- System.out.println("web路径:"+respath);
- return respath;
- }
- public static PdfPTable createTable(String title,String[] titles,String[][] dataSet) throws DocumentException {
- if(titles==null||titles.length==0){
- return null;
- }
- PdfPTable table = new PdfPTable(titles.length);
- //表的样式设置--对齐
- table.setWidthPercentage(90);
- table.setHorizontalAlignment(Element.ALIGN_CENTER);
- //表头设置
- float[] headerLength=new float[titles.length];
- for(int i=0;i<headerLength.length;i++){
- String ti=titles[i]==null?"":titles[i];
- int len=ti.length();
- headerLength[i]=15*len+20;
- }
- table.setTotalWidth(headerLength);
- table.setLockedWidth(true);
- PdfPCell cell;
- //表头
- for(int k=0;k<titles.length;k++){
- String str=titles[k]==null?"":titles[k];
- cell = new PdfPCell(new Phrase(str,ExportPdfUtil.getChineseFont()));
- cell.setHorizontalAlignment(Element.ALIGN_CENTER);
- cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
- cell.setNoWrap(true);
- cell.setColspan(1);
- BaseColor bc=new BaseColor(95,158,160);
- cell.setBackgroundColor(bc);
- table.addCell(cell);
- }
- //表数据
- for(int i=0;i<dataSet.length;i++){
- String[] row=dataSet[i];
- if(row==null||row.length==0){
- continue;
- }
- for(int k=0;k<row.length;k++){
- String str=row[k]==null?"":row[k];
- cell = new PdfPCell(new Phrase(str,ExportPdfUtil.getChineseFontForTableCellOne()));
- cell.setHorizontalAlignment(Element.ALIGN_CENTER);
- cell.setVerticalAlignment(Element.ALIGN_MIDDLE);
- cell.setNoWrap(true);
- cell.setColspan(1);
- table.addCell(cell);
- }
- }
- return table;
- }
- public static Font getChineseFont(){
- BaseFont bf;
- Font fontChinese=null;
- try {
- bf = BaseFont.createFont("STSongStd-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);
- fontChinese=new Font(bf,12,Font.NORMAL);
- } catch (DocumentException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- return fontChinese;
- }
- public static Font getChineseFontForTableCellOne(){
- BaseFont bf;
- Font fontChinese=null;
- try {
- bf = BaseFont.createFont("STSongStd-Light", "UniGB-UCS2-H", BaseFont.NOT_EMBEDDED);
- fontChinese=new Font(bf,10,Font.NORMAL);
- } catch (DocumentException e) {
- e.printStackTrace();
- } catch (IOException e) {
- e.printStackTrace();
- }
- return fontChinese;
- }
- //随机日期字符串
- public static String getRandomDateStr(){
- String res="";
- SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
- res=sdf.format(new Date());
- String a=String.valueOf((new Date()).getTime());
- return res+"pdf".trim()+a;
- }
- /**
- * Main method.
- * @param args no arguments needed
- * @throws DocumentException
- * @throws IOException
- */
- public static void main(String[] args) throws IOException, DocumentException {
- String title="呼入汇总11";
- String[] titles=new String[]{"t1表头","t2表头","t3表头","t4表头","t5表头"};
- String[][] dataSet=new String[2][5];
- dataSet[0]=new String[]{"d11数据","d12数据","d13数据","d14数据","d15数据"};
- dataSet[1]=new String[]{"d21数据","d22数据","d23数据","d24数据","d25数据"};
- ExportPdfUtil.createPdf(title,titles,dataSet);
- }
- }
excel:
- package org.hd.util;
- import net.sf.json.JSONObject;
- import org.apache.poi.xssf.usermodel.*;
- import org.apache.poi.ss.util.CellRangeAddress;
- import org.apache.poi.ss.usermodel.*;
- import org.apache.poi.hssf.usermodel.HSSFWorkbook;
- import java.text.SimpleDateFormat;
- import java.util.Date;
- import java.util.Map;
- import java.util.HashMap;
- import java.io.File;
- import java.io.FileNotFoundException;
- import java.io.FileOutputStream;
- import java.io.OutputStream;
- import javax.servlet.http.HttpServletResponse;
- public class ExportExcelUtil {
- public static String WEBCONTENT_PATH="";
- public static String BASE_PATH="";
- public static String TEMP_EXCEL_PATH="";//临时excel目录
- public static String TEMP_WORD_PATH="";//临时word目录
- public static String DirTypeExcel="excel";
- public static String DirTypeWord="word";
- static{
- String clapath=BlobUtil.class.getResource("/").getPath();
- File claFile=new File(clapath);
- String basePath=claFile.getParentFile().getParentFile().getPath();
- ExportExcelUtil.WEBCONTENT_PATH=basePath;
- ExportExcelUtil.BASE_PATH=basePath+"\\tempExport";
- ExportExcelUtil.TEMP_EXCEL_PATH=ExportExcelUtil.BASE_PATH+"\\excel";
- ExportExcelUtil.TEMP_WORD_PATH=ExportExcelUtil.BASE_PATH+"\\word";
- File tempBase=new File(ExportExcelUtil.BASE_PATH);
- if(!tempBase.exists()){
- tempBase.mkdir();
- }
- File tempExcel=new File(ExportExcelUtil.TEMP_EXCEL_PATH);
- if(!tempExcel.exists()){
- tempExcel.mkdir();
- }
- File tempWord=new File(ExportExcelUtil.TEMP_WORD_PATH);
- if(!tempWord.exists()){
- tempWord.mkdir();
- }
- /*
- --ExportExcelUtil.WEBCONTENT_PATH--D:\MyProject\my-workSpace\ROOT\WebContent
- --ExportExcelUtil.BASE_PATH--D:\MyProject\my-workSpace\ROOT\WebContent\tempExport
- --ExportExcelUtil.TEMP_EXCEL_PATH--D:\MyProject\my-workSpace\ROOT\WebContent\tempExport\excel
- --ExportExcelUtil.TEMP_WORD_PATH--D:\MyProject\my-workSpace\ROOT\WebContent\tempExport\word
- --ExportExcelUtil.TEMP_PDF_PATH--D:\MyProject\my-workSpace\ROOT\WebContent\tempExport\pdf
- */
- System.out.println("--ExportExcelUtil.WEBCONTENT_PATH--"+ExportExcelUtil.WEBCONTENT_PATH);
- System.out.println("--ExportExcelUtil.BASE_PATH--"+ExportExcelUtil.BASE_PATH);
- System.out.println("--ExportExcelUtil.TEMP_EXCEL_PATH--"+ExportExcelUtil.TEMP_EXCEL_PATH);
- System.out.println("--ExportExcelUtil.TEMP_WORD_PATH--"+ExportExcelUtil.TEMP_WORD_PATH);
- }
- /*
- resjson--{"title":"呼入汇总","resTableheaders":"日期,坐席总数,呼入总数,总失败次数,咨询次数,转移次数 ,总通话时长,平均通话时长,平均振铃次数,平均振铃时长","resList":"2012-10-17,5,36,3,3,0,54.20,1.64,0,0.11|2012-10-18,5,32,5,1,0,47.10,1.81,0,0.12|2012-10-19,5,32,3,2,0,37.45,1.29,0,0.12|2012-10-22,5,54,6,5,0,92.87,1.98,0,0.09|"}
- title--呼入汇总
- resTableheaders--日期,坐席总数,呼入总数,总失败次数,咨询次数,转移次数 ,总通话时长,平均通话时长,平均振铃次数,平均振铃时长
- resList--2012-10-17,5,36,3,3,0,54.20,1.64,0,0.11|2012-10-18,5,32,5,1,0,47.10,1.81,0,0.12|2012-10-19,5,32,3,2,0,37.45,1.29,0,0.12|2012-10-22,5,54,6,5,0,92.87,1.98,0,0.09|
- *
- resjson--{"title":"呼入汇总","resTableheaders":"日期,坐席总数,呼入总数,总失败次数,咨询次数,转移次数 ,总通话时长,平均通话时长,平均振铃次数,平均振铃时长","resList":""}
- title--呼入汇总
- resTableheaders--日期,坐席总数,呼入总数,总失败次数,咨询次数,转移次数 ,总通话时长,平均通话时长,平均振铃次数,平均振铃时长
- resList--
- */
- public static String exportExcelFromAllStr(String resjson,String clientFileName,OutputStream os,HttpServletResponse response){
- String info="";
- JSONObject jo=JSONObject.fromObject(resjson);
- String title=(String)jo.get("title");
- String resTableheaders=(String)jo.get("resTableheaders");
- String resList=(String)jo.get("resList");
- System.out.println("title--"+title);
- System.out.println("resTableheaders--"+resTableheaders);
- ExportExcelUtil.printResListFromAllStr(ExportExcelUtil.getResListFromAllStr(resList));
- //导出
- Workbook wb=ExportExcelUtil.createWorkbook(null,title, ExportExcelUtil.getResTableheadersFromAllStr(resTableheaders), ExportExcelUtil.getResListFromAllStr(resList));
- String res=ExportExcelUtil.exportExcelFileOnWeb(wb, clientFileName, os, response);
- if(res==null||res.trim().equals("error")){
- info="error";
- }else{
- info=res.trim();
- }
- return info;
- }
- public static String exportExcelFromAllStrOnServerDownLoad(String resjson,String clientFileName){
- String infopath="";
- JSONObject jo=JSONObject.fromObject(resjson);
- String title=(String)jo.get("title");
- String resTableheaders=(String)jo.get("resTableheaders");
- String resList=(String)jo.get("resList");
- System.out.println("title--"+title);
- System.out.println("resTableheaders--"+resTableheaders);
- ExportExcelUtil.printResListFromAllStr(ExportExcelUtil.getResListFromAllStr(resList));
- //导出
- Workbook wb=ExportExcelUtil.createWorkbook(null,title, ExportExcelUtil.getResTableheadersFromAllStr(resTableheaders), ExportExcelUtil.getResListFromAllStr(resList));
- String res=ExportExcelUtil.exportExcelFile(wb, clientFileName, null);
- if(res==null||res.trim().equals("error")){
- infopath="error";
- }else{
- infopath=res.trim();
- }
- return infopath;
- }
- //resTableheaders--日期,坐席总数,呼入总数,总失败次数,咨询次数,转移次数 ,总通话时长,平均通话时长,平均振铃次数,平均振铃时长
- public static String[] getResTableheadersFromAllStr(String resTableheaders){
- String[] arrResTableheaders=resTableheaders.split(",");
- return arrResTableheaders;
- }
- //resList--2012-10-17,5,36,3,3,0,54.20,1.64,0,0.11|2012-10-18,5,32,5,1,0,47.10,1.81,0,0.12|2012-10-19,5,32,3,2,0,37.45,1.29,0,0.12|2012-10-22,5,54,6,5,0,92.87,1.98,0,0.09|
- public static String[][] getResListFromAllStr(String resList){
- String[][] res=null;
- String[] arrObjList=null;
- String[] arrObj=null;
- if(resList==null||resList.trim().equals("")){
- }else{
- arrObjList=resList.split("suolong");
- if(arrObjList!=null&&arrObjList.length>=1){
- String vo=(String)arrObjList[0];
- arrObj=vo.split(",");
- if(arrObj!=null&&arrObj.length>=1){
- res=new String[arrObjList.length][arrObj.length];
- //放数据
- for(int i=0;i<arrObjList.length;i++){
- String vo2=(String)arrObjList[i];
- String[] arrObj2=vo2.split(",");
- for(int k=0;k<arrObj2.length;k++){
- res[i][k]=arrObj2[k];
- }
- }
- }
- }else{
- }
- }
- return res;
- }
- //打印表格数据
- public static void printResListFromAllStr(String[][] res){
- if(res==null){
- System.out.println("res[][] is null");
- }else{
- System.out.println("res[][] 行数:"+res.length);
- if(res.length>=1){
- System.out.println("res[][] 列数:"+res[0].length);
- for(int i=0;i<res.length;i++){
- String line="";
- for(int k=0;k<res[i].length;k++){
- line+="res["+i+"]["+k+"]"+res[i][k]+",";
- }
- line+="\r\n";
- System.out.println(line);
- }
- }
- }
- }
- public static void main(String[] ss){
- String[] titles1={"日期","平均振铃时长平均振铃时长","总失败次数"};
- String[][] sample_data1={
- {"2012-09-08 18:12:33 kk","12","13"},
- {"21","22","23"}
- };
- Workbook wb=ExportExcelUtil.createWorkbook(null,"gg2", titles1, sample_data1);
- String res=ExportExcelUtil.exportExcelFile(wb, "测试22",null);
- System.out.print("res");
- }
- /**
- * 导出到客户端本地
- * @param wb
- * @param fileName
- * @param os
- * @return
- */
- public static String exportExcelFileOnWeb(Workbook wb,String fileName,OutputStream os,HttpServletResponse response){
- String info="";
- if(wb==null){
- info="error";
- }else{
- String fileName2 = "";
- if(fileName==null||fileName.trim().equals("")){
- fileName2=ExportExcelUtil.getRandomDateStr()+".xls";
- }else{
- fileName2=fileName.trim()+".xls";
- }
- if(wb instanceof XSSFWorkbook) {
- fileName2 += "x";
- }
- try {
- info="tempExport/excel/"+fileName2;
- // response.setContentType( "application/vnd.ms-excel");//2003
- // response.setContentType("application/ms-excel"); //2003
- response.setContentType( "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");//2007
- // response.setContentType("octets/stream");//压缩文件
- // response.setContentType("application/x-msdownload");//压缩文件
- response.addHeader("Content-Disposition","attachment;filename="+info.trim());
- wb.write(os);
- os.flush();
- os.close();
- System.out.println("成功导出到客户端");
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- return info;
- }
- /**
- * 根据Workbook和文件名导出文件
- * @param wb
- * @param fileName
- * tempExport/excel/xxx.xlsx
- */
- public static String exportExcelFile(Workbook wb,String fileName,String webPath){
- String info="";
- if(wb==null){
- info="error";
- }else{
- String fileName2 = "";
- if(fileName==null||fileName.trim().equals("")){
- fileName2=ExportExcelUtil.getRandomDateStr()+".xls";
- }else{
- fileName2=fileName.trim()+".xls";
- }
- if(wb instanceof XSSFWorkbook) fileName2 += "x";
- String wholeFilePath="";
- if(webPath==null||webPath.trim().equals("")){
- wholeFilePath=ExportExcelUtil.TEMP_EXCEL_PATH+"\\"+fileName2;
- }else{
- wholeFilePath=webPath.trim()+"\\"+fileName2;
- }
- wholeFilePath=wholeFilePath.replace("\\", "/");
- FileOutputStream out;
- try {
- out = new FileOutputStream(wholeFilePath);
- wb.write(out);
- out.close();
- info="tempExport/excel/"+fileName2;
- System.out.println("成功导出到服务器的临时目录下:"+wholeFilePath);
- System.out.println("返回server路径"+info);
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- return info;
- }
- //随机日期字符串
- public static String getRandomDateStr(){
- String res="";
- SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMddHHmmss");
- res=sdf.format(new Date());
- String a=String.valueOf((new Date()).getTime());
- return res+"excel".trim()+a;
- }
- //业务逻辑,产生一个工作簿
- public static Workbook createWorkbook(String typeparam,String tableHeader,String[] titles,String[][] sample_data){
- Workbook wb=null;
- try{
- if(typeparam==null){
- wb = new XSSFWorkbook();
- }
- else if(typeparam.length() > 0 && typeparam.equals("2003")) {wb = new HSSFWorkbook();}
- else {wb = new XSSFWorkbook();}
- Map<String, CellStyle> styles = createStyles(wb);
- String tableHeader2="";
- if(tableHeader==null||tableHeader.trim().equals("")){
- tableHeader2=ExportExcelUtil.getRandomDateStr();
- }else{
- tableHeader2=tableHeader.trim();
- }
- Sheet sheet = wb.createSheet(tableHeader2);
- PrintSetup printSetup = sheet.getPrintSetup();
- printSetup.setLandscape(true);
- sheet.setFitToPage(true);
- sheet.setHorizontallyCenter(true);
- //title row
- Row titleRow = sheet.createRow(0);
- titleRow.setHeightInPoints(35);
- Cell titleCell = titleRow.createCell(0);
- titleCell.setCellValue(tableHeader2);
- titleCell.setCellStyle(styles.get("title"));
- sheet.addMergedRegion(CellRangeAddress.valueOf("$A$1:$L$1"));
- //header row
- Row headerRow = sheet.createRow(1);
- headerRow.setHeightInPoints(30);
- Cell headerCell;
- if(titles!=null){
- for (int i = 0; i < titles.length; i++) {
- headerCell = headerRow.createCell(i);
- headerCell.setCellValue(titles[i]);
- headerCell.setCellStyle(styles.get("header"));
- }
- }
- // data row
- if(sample_data!=null){
- for (int i = 0; i < sample_data.length; i++) {
- Row row = sheet.createRow(2+i);
- row.setHeightInPoints(20);
- for (int j = 0; j < sample_data[i].length; j++) {
- Cell dataCell=row.createCell(j);
- dataCell.setCellStyle(styles.get("cell"));
- if(sample_data[i][j] == null) continue;
- if(sample_data[i][j] instanceof String) {
- dataCell.setCellValue((String)sample_data[i][j]);
- } else {
- dataCell.setCellValue(sample_data[i][j].toString());
- }
- }
- }
- }
- //finally set column widths, the width is measured in units of 1/256th of a character width
- if(titles!=null){
- for (int i = 0; i < titles.length; i++) {
- int tlen1=(titles[i]==null)?7:titles[i].length();
- int tlen=(tlen1<7)?7:tlen1;
- int len=(tlen*256*3)+5;
- sheet.setColumnWidth(i, len); //6 characters wide
- }
- }
- }catch(Exception ee){
- ee.printStackTrace();
- }
- return wb;
- }
- /**公用的css样式集,放在一个map中。
- * Create a library of cell styles
- */
- private static Map<String, CellStyle> createStyles(Workbook wb){
- Map<String, CellStyle> stylesMap = new HashMap<String, CellStyle>();
- CellStyle style;
- Font titleFont = wb.createFont();
- titleFont.setFontHeightInPoints((short)18);
- titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- style.setFont(titleFont);
- stylesMap.put("title", style);
- Font monthFont = wb.createFont();
- monthFont.setFontHeightInPoints((short)11);
- monthFont.setColor(IndexedColors.BLACK.getIndex());
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- style.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setFont(monthFont);
- style.setWrapText(true);
- style.setBorderRight(CellStyle.BORDER_THIN);
- style.setBorderTop(CellStyle.BORDER_THIN);
- stylesMap.put("header", style);
- style = wb.createCellStyle();
- Font cell= wb.createFont();
- cell.setColor(IndexedColors.BLUE.getIndex());
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setWrapText(true);
- style.setBorderRight(CellStyle.BORDER_THIN);
- style.setRightBorderColor(IndexedColors.BLACK.getIndex());
- style.setBorderLeft(CellStyle.BORDER_THIN);
- style.setLeftBorderColor(IndexedColors.BLACK.getIndex());
- style.setBorderTop(CellStyle.BORDER_THIN);
- style.setTopBorderColor(IndexedColors.BLACK.getIndex());
- style.setBorderBottom(CellStyle.BORDER_THIN);
- style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
- style.setFillForegroundColor(IndexedColors.BLUE.getIndex());
- stylesMap.put("cell", style);
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
- stylesMap.put("formula", style);
- style = wb.createCellStyle();
- style.setAlignment(CellStyle.ALIGN_CENTER);
- style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
- style.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
- style.setFillPattern(CellStyle.SOLID_FOREGROUND);
- style.setDataFormat(wb.createDataFormat().getFormat("0.00"));
- stylesMap.put("formula_2", style);
- return stylesMap;
- }
- }
页面访问,之前设置过setContentType,没有弹出文件保存框的原因也找到了。
就是下载的请求不能用ajax,(或许需要其他设置,这里我做的时候就是不行),
请求使用window.location.href就可以弹出保存框,使用ajax就没反应,在回调函数中
返回的是乱码。原因不详,总之别用ajax调用下载文件的servlert请求。
- function pdfservlet(){
- var resjson=getResJson();
- var urlpath1=path+'/ExportPdfServlet?dd='+Math.random()+'&resjson='+resjson;
- var urlpath2=encodeURI(urlpath1);
- var urlpath=encodeURI(urlpath2);
- window.location.href=urlpath;
- }
- function excelServlet(){
- var resjson=getResJson();
- //var urlpath1=path+'/PdfItextServlet?dd='+Math.random()+'&resjson='+resjson;
- var urlpath1=path+'/ExportExcelServlet?dd='+Math.random()+'&resjson='+resjson;
- var urlpath2=encodeURI(urlpath1);
- var urlpath=encodeURI(urlpath2);
- window.location.href=urlpath;
- }
思路(二)是发请求在服务器生成文件,ajax访问返回这个文件的url,在回调函数中请求这个url。
思路不好,还是可行的。代码如下:
- package org.hd.report.action.pdf;
- import java.io.OutputStream;
- import java.io.PrintWriter;
- import java.util.List;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import net.sf.json.JSONObject;
- import org.apache.struts2.ServletActionContext;
- import org.hd.report.model.GreenPageVo;
- import org.hd.report.service.ReportService;
- import org.hd.report.service.RptService;
- import org.hd.util.ExportExcelUtil;
- import org.hd.util.ExportPdfUtil;
- import org.rd.framework.common.container.ContainerManager;
- import org.rd.framework.query.support.PaginationSupport;
- import org.rd.framework.struts.action.CommonAction;
- import com.opensymphony.xwork2.ActionContext;
- //呼入汇总
- public class ExportTableDataToPdfAction extends CommonAction{
- private RptService rptService = (RptService)ContainerManager.getComponent(RptService.BEAN_ID);
- public String exportPdfInSummary() throws Exception{
- ActionContext ctx = ActionContext.getContext();
- HttpServletResponse response = (HttpServletResponse) ctx.get(ServletActionContext.HTTP_RESPONSE);
- HttpServletRequest request = (HttpServletRequest) ctx.get(ServletActionContext.HTTP_REQUEST);
- response.setCharacterEncoding("UTF-8");
- // response.setContentType("application/pdf");
- // response.setContentType("text/xml");
- PrintWriter out = response.getWriter();
- String resjson= request.getParameter("resjson") != null ? request.getParameter("resjson").trim() : "";
- resjson=java.net.URLDecoder.decode(resjson,"UTF-8");
- System.out.println("resjson from page--"+resjson);
- String clientFileName="";
- String infopath=ExportPdfUtil.createPdfWithJson(resjson);
- out.println(infopath);
- return NONE;
- }
- public String execute() throws Exception{
- return SUCCESS;
- }
- }
excel
- package org.hd.report.action.excel;
- import java.io.OutputStream;
- import java.io.PrintWriter;
- import java.util.List;
- import javax.servlet.http.HttpServletRequest;
- import javax.servlet.http.HttpServletResponse;
- import net.sf.json.JSONObject;
- import org.apache.struts2.ServletActionContext;
- import org.hd.report.model.GreenPageVo;
- import org.hd.report.service.ReportService;
- import org.hd.report.service.RptService;
- import org.hd.util.ExportExcelUtil;
- import org.rd.framework.common.container.ContainerManager;
- import org.rd.framework.query.support.PaginationSupport;
- import org.rd.framework.struts.action.CommonAction;
- import com.opensymphony.xwork2.ActionContext;
- //呼入汇总
- public class ExportTableDataToExcelAction extends CommonAction{
- private RptService rptService = (RptService)ContainerManager.getComponent(RptService.BEAN_ID);
- public String exportExcelInSummary() throws Exception{
- ActionContext ctx = ActionContext.getContext();
- HttpServletResponse response = (HttpServletResponse) ctx.get(ServletActionContext.HTTP_RESPONSE);
- HttpServletRequest request = (HttpServletRequest) ctx.get(ServletActionContext.HTTP_REQUEST);
- response.setCharacterEncoding("UTF-8");
- // response.setContentType( "application/vnd.ms-excel");
- // response.addHeader("Content-Disposition","attachment;filename=output.xlsx");
- // response.setContentType("application/ms-excel");
- // response.addHeader("Content-Disposition","attachment;filename=output.xlsx");
- // response.setContentType("octets/stream");
- // response.addHeader("Content-Disposition","attachment;filename=output.xlsx");
- PrintWriter out = response.getWriter();
- // OutputStream os=response.getOutputStream();
- String resjson= request.getParameter("resjson") != null ? request.getParameter("resjson").trim() : "";
- resjson=java.net.URLDecoder.decode(resjson,"UTF-8");
- System.out.println("resjson from page--"+resjson);
- String clientFileName="";
- // String info=ExportExcelUtil.exportExcelFromAllStr(resjson, clientFileName, os);
- String infopath=ExportExcelUtil.exportExcelFromAllStrOnServerDownLoad(resjson, clientFileName);
- out.println(infopath);
- return NONE;
- }
- public String execute() throws Exception{
- return SUCCESS;
- }
- }
struts.xml
- <!-- 导出 -->
- <action name="exportExcelInSummary" class="org.hd.report.action.excel.ExportTableDataToExcelAction" method="exportExcelInSummary">
- </action>
- <action name="exportPdfInSummary" class="org.hd.report.action.pdf.ExportTableDataToPdfAction" method="exportPdfInSummary">
- </action>
页面调用:
- function excelAction(){
- var resjson=getResJson();
- //var urlpath1=path+'/PdfItextServlet?dd='+Math.random()+'&resjson='+resjson;
- var urlpath1=path+'/report/exportExcelInSummary.action?dd='+Math.random()+'&resjson='+resjson;
- var urlpath2=encodeURI(urlpath1);
- var urlpath=encodeURI(urlpath2);
- //alert(urlpath);
- $.ajax({
- type: "POST",
- url: urlpath,
- success: excelActionBack
- });
- }
- function excelActionBack(data){
- alert(data+"------");
- var data1=$.trim(data);
- var downpath=path+"/"+data1;
- window.location.href=downpath;
- }
- function pdfAction(){
- var resjson=getResJson();
- //var urlpath1=path+'/PdfItextServlet?dd='+Math.random()+'&resjson='+resjson;
- var urlpath1=path+'/report/exportPdfInSummary.action?dd='+Math.random()+'&resjson='+resjson;
- var urlpath2=encodeURI(urlpath1);
- var urlpath=encodeURI(urlpath2);
- //alert(urlpath);
- $.ajax({
- type: "POST",
- url: urlpath,
- success:pdfActionBack
- });
- }
- function pdfActionBack(data){
- alert(data+"------");
- var data1=$.trim(data);
- var downpath=path+"/"+data1;
- window.location.href=downpath;
- }
getResJson()是拼装json数据的方法,业务逻辑。
至于请求的url都调用了2遍encodeURI(),就是为了防止中文参数乱码。
记住必须是2次。