JS及POI将页面数据导出EXCLE文件的几种方法

JS实现的三种方法如下：

1、js文件：exportExcel.js

//导出方法1:把表格中的内容提取出来，利用IE的另存为.csv的格式。
 function getXlsFromTbl(inTblId, inTblContainerId, title, rownumbers) {
     try {
         var allStr = "";
         var curStr = "";
         //alert("getXlsFromTbl");
         if (inTblId != null && inTblId != "" && inTblId != "null") {
             curStr = getTblData($('#' + inTblId), $('#' + inTblContainerId), rownumbers);
         }
         if (curStr != null) {
             allStr += curStr;
         }
         else {
             alert("你要导出的表不存在！");
             return;
         }
         var fileName = getExcelFileName(title);
         doFileExport(fileName, allStr);
     }
     catch (e) {
         alert("导出发生异常:" + e.name + "->" + e.description + "!");
     }
 }
 function getTblData(curTbl, curTblContainer, rownumbers) {

     var outStr = "";
     if (curTbl != null) {
         var rowdata = curTbl.getRowData();
         var Lenr = 1;

         for (i = 0; i < Lenr; i++) {
             //var Lenc = curTbl.rows(i).cells.length;
             var th;
             if (rownumbers == true) {
                 th = curTblContainer.find('TH:not(:first-child)');
             }
             else {
                 th = curTblContainer.find('TH');
             }
             th.each(function(index, element) {
                 //alert($(element).text());
                 //取得每行的列数
                 var j = index + 1;
                 var content = $(element).text();
                 //alert(j + "|" + content);
                 outStr += content+ "\t";
                 //赋值

             });
             outStr += "\r\n";
         }
         var tmp = "";
         for (i = 0; i < rowdata.length; i++) {
             var row = eval(rowdata[i]);
            
             for (each in row) {
             //此程序需将科学计数法的单元格弄成文本格式--没办法
              if(row[each].length > 14){
               outStr += "'"+row[each]+"'" + "\t";
              }
              else outStr += row[each] + "\t";
              
                
             }
             outStr += "\r\n";
         }

     }
     else {
         outStr = null;
         alert(inTbl + "不存在!");
     }
     return outStr;
 }
 function getExcelFileName(title) {
     var d = new Date();
     var curYear = d.getYear();
     var curMonth = "" + (d.getMonth() + 1);
     var curDate = "" + d.getDate();
     var curHour = "" + d.getHours();
     var curMinute = "" + d.getMinutes();
     var curSecond = "" + d.getSeconds();
     if (curMonth.length == 1) {
         curMonth = "0" + curMonth;
     }
     if (curDate.length == 1) {
         curDate = "0" + curDate;
     }
     if (curHour.length == 1) {
         curHour = "0" + curHour;
     }
     if (curMinute.length == 1) {
         curMinute = "0" + curMinute;
     }
     if (curSecond.length == 1) {
         curSecond = "0" + curSecond;
     }
     var fileName = title + "_" + curYear + curMonth + curDate + "_"
             + curHour + curMinute + curSecond + ".html";
     //alert(fileName);
     return fileName;
 }
 function doFileExport(inName, inStr) {
     var xlsWin = null;
     if (!!document.all("HideFrm")) {
         xlsWin = HideFrm;
     }
     else {
         var width = 60;
         var height = 40;
         var openPara = "left=" + (window.screen.width / 2 - width / 2)
                 + ",top=" + (window.screen.height / 2 - height / 2)
                 + ",scrollbars=no,width=" + width + ",height=" + height;
         xlsWin = window.open("", "_blank", openPara);
     }
     xlsWin.document.write(inStr);
     xlsWin.document.close();
     xlsWin.document.execCommand('Saveas', true, inName);
     alert("导出成功！");
     xlsWin.close();
 }
 
//导出方法2:通过遍历表格，给EXCEL中相应的单元格赋值。
 function method2(tableid) //读取表格中每个单元到EXCEL中
 {
     var curTbl = document.getElementById(tableid);
     var oXL = new ActiveXObject("Excel.Application");
      //创建AX对象excel
     var oWB = oXL.Workbooks.Add();
     //获取workbook对象
     var oSheet = oWB.ActiveSheet;
     //激活当前sheet
     var Lenr = curTbl.rows.length;
     //取得表格行数
    
     var k=1;
     var rownumbers=true;
     for (i = 0; i < Lenr; i++)
     {
         var Lenc = curTbl.rows(i).cells.length;
        
         //
         if(i==0){
          var inTblContainerId='ordersId';
          var curTblContainer=$('#' + inTblContainerId);
          var th;
          if (rownumbers == true) {
                 th = curTblContainer.find('TH:not(:first-child)');
             }
             else {
                 th = curTblContainer.find('TH');
             }
            
             th.each(function(index, element) {
                 //alert($(element).text());
                 //取得每行的列数
                 var j = index + 1;
                 var content = $(element).text();
                 //alert(j + "|" + content);
                 oSheet.Cells(i + 1, j + 1).value = $(element).text();

             });
         
         }else{
        
         //取得每行的列数
          for (j = 0; j < Lenc; j++)
          {
           oSheet.Columns(k).NumberFormatLocal="@";
     k=k+1;
              oSheet.Cells(i + 1, j + 1).value = curTbl.rows(i).cells(j).innerText;
               //赋值
              
           }
          }
     }
    
     oXL.Visible = true;
     //设置excel可见属性
 }
 
/******导出方法3:直接拷贝整个表格到EXCEL中******/
 var idTmr;
        function method1(tableid) {//整个表格拷贝到EXCEL中

            var curTbl = document.getElementById(tableid);
            var oXL = new ActiveXObject("Excel.Application");
            //创建AX对象excel
            var oWB = oXL.Workbooks.Add();
            //获取workbook对象
            var xlsheet = oWB.Worksheets(1);
            //激活当前sheet
            var sel = document.body.createTextRange();
            sel.moveToElementText(curTbl);
            //把表格中的内容移到TextRange中
            sel.select();
            //全选TextRange中内容
            sel.execCommand("Copy");
            //复制TextRange中内容 
            xlsheet.Paste();
            //粘贴到活动的EXCEL中      
            oXL.Visible = true;
            //设置excel可见属性
            var company = "HL";
            if (company == "Baidu") {
                company = "百度";
            }
            else if (company == "Google") {
                 company = "谷歌";
            }
            else {
                company = "孙悟空";
            }

            try {
                var fname = oXL.Application.GetSaveAsFilename(company+"将当前页面的数据导出到Excel.xls", "Excel Spreadsheets (*.xls), *.xls");
            } catch (e) {
                print("Nested catch caught " + e);
            } finally {
                oWB.SaveAs(fname);

                oWB.Close(savechanges = false);
                //xls.visible = false;
                oXL.Quit();
                oXL = null;
                //结束excel进程，退出完成
                //window.setInterval("Cleanup();",1);
                idTmr = window.setInterval("Cleanup();", 1);

            }
        }
       
        function Cleanup() {
            window.clearInterval(idTmr);
            CollectGarbage();
        }

 2、Jsp页面片段：queryorders.jsp

...

<li><input type="button" id="exportBtn" value="导出EXCEL"/></li>

...

<div id="ordersId"><table id="orderListTable"></table></div> 

...

3、操作JS 代码片段：queryorders.js

 $('#exportBtn').click(function() {
 
       //方法1：提取前台表格的数据，拼成串后。另存为csv格式文件
       //getXlsFromTbl('orderListTable', 'ordersId', title, true);
      
       //方法2：直接copy当前页面TABLE数据，另存XLS格式文件。
       // method1('orderListTable');
      
       //方法3：提取页面TABLE 的数据，将数据一一赋值到EXCLE 文件。前提：客户端装有EXCEL ，且开放Axtive控件
       method2('orderListTable');
        
       }); 

 

------------------------以下是通过POI将数据生成EXCEL文件放到服务器的指定目录------------------------

package com.longshine.util;
import java.io.BufferedInputStream;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import javax.swing.JOptionPane;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import com.longshine.demo.po.Orders;

/**
 * 利用开源组件POI3.0.2动态导出EXCEL文档 转载时请保留以下信息，注明出处！
 *
 * @author leno
 * @version v1.0
 * @param <T>
 *            应用泛型，代表任意一个符合javabean风格的类
 *            注意这里为了简单起见，boolean型的属性xxx的get器方式为getXxx(),而不是isXxx()
 *            byte[]表jpg格式的图片数据
 */
public class ExportExcel<T>
{
 public void exportExcel(Collection<T> dataset, OutputStream out)
 {
  exportExcel("测试POI导出EXCEL文档", null, dataset, out, "yyyy-MM-dd");
 }

 public void exportExcel(String[] headers, Collection<T> dataset,
   OutputStream out)
 {
  exportExcel("超人POI导出的EXCEL文档", headers, dataset, out, "yyyy-MM-dd");
 }

 public void exportExcel(String[] headers, Collection<T> dataset,
   OutputStream out, String pattern)
 {
  exportExcel("测试POI导出EXCEL文档", headers, dataset, out, pattern);
 }

 /**
  * 这是一个通用的方法，利用了JAVA的反射机制，可以将放置在JAVA集合中并且符号一定条件的数据以EXCEL 的形式输出到指定IO设备上
  *
  * @param title
  *            表格标题名
  * @param headers
  *            表格属性列名数组
  * @param dataset
  *            需要显示的数据集合,集合中一定要放置符合javabean风格的类的对象。此方法支持的
  *            javabean属性的数据类型有基本数据类型及String,Date,byte[](图片数据)
  * @param out
  *            与输出设备关联的流对象，可以将EXCEL文档导出到本地文件或者网络中
  * @param pattern
  *            如果有时间数据，设定输出格式。默认为"yyy-MM-dd"
  */
 @SuppressWarnings("unchecked")
 public void exportExcel(String title, String[] headers,
   Collection<T> dataset, OutputStream out, String pattern)
 {
  // 声明一个工作薄
  HSSFWorkbook workbook = new HSSFWorkbook();
  // 生成一个表格
  HSSFSheet sheet = workbook.createSheet(title);
  // 设置表格默认列宽度为15个字节
  sheet.setDefaultColumnWidth((short) 15);
  // 生成一个样式
  HSSFCellStyle style = workbook.createCellStyle();
  // 设置这些样式
  style.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
  style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  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 font = workbook.createFont();
  font.setColor(HSSFColor.VIOLET.index);
  font.setFontHeightInPoints((short) 12);
  font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
  // 把字体应用到当前的样式
  style.setFont(font);
  // 生成并设置另一个样式
  HSSFCellStyle style2 = workbook.createCellStyle();
  style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
  style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
  style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
  style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
  style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
  style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
  style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
  // 生成另一个字体
  HSSFFont font2 = workbook.createFont();
  font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
  // 把字体应用到当前的样式
  style2.setFont(font2);

  // 声明一个画图的顶级管理器
  HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
  // 定义注释的大小和位置,详见文档
  HSSFComment comment = patriarch.createComment(new HSSFClientAnchor(0,
    0, 0, 0, (short) 4, 2, (short) 6, 5));
  // 设置注释内容
  comment.setString(new HSSFRichTextString("可以在POI中添加注释！"));
  // 设置注释作者，当鼠标移动到单元格上是可以在状态栏中看到该内容.
  comment.setAuthor("leno");

  // 产生表格标题行
  HSSFRow row = sheet.createRow(0);
  for (short i = 0; i < headers.length; i++)
  {
   HSSFCell cell = row.createCell(i);
   cell.setCellStyle(style);
   HSSFRichTextString text = new HSSFRichTextString(headers[i]);
   cell.setCellValue(text);
  }

  // 遍历集合数据，产生数据行
  Iterator<T> it = dataset.iterator();
  int index = 0;
  while (it.hasNext())
  {
   index++;
   row = sheet.createRow(index);
   T t = (T) it.next();
   System.out.println(t.getClass());
   // 利用反射，根据javabean属性的先后顺序，动态调用getXxx()方法得到属性值
   Field[] fields = t.getClass().getDeclaredFields();
   System.out.println("fields.length="+fields.length);
   for (short i = 0; i < fields.length; i++)
   {
    HSSFCell cell = row.createCell(i);
    cell.setCellStyle(style2);
    Field field = fields[i];
    String fieldName = field.getName();
    String getMethodName = "get"
      + fieldName.substring(0, 1).toUpperCase()
      + fieldName.substring(1);
    System.out.println("getMethodName="+getMethodName);
    try
    {
     Class tCls = t.getClass();
     Method getMethod = tCls.getMethod(getMethodName,
       new Class[]
       {});
     Object value = getMethod.invoke(t, new Object[]
     {});
     // 判断值的类型后进行强制类型转换
     String textValue = null;
     // if (value instanceof Integer) {
     // int intValue = (Integer) value;
     // cell.setCellValue(intValue);
     // } else if (value instanceof Float) {
     // float fValue = (Float) value;
     // textValue = new HSSFRichTextString(
     // String.valueOf(fValue));
     // cell.setCellValue(textValue);
     // } else if (value instanceof Double) {
     // double dValue = (Double) value;
     // textValue = new HSSFRichTextString(
     // String.valueOf(dValue));
     // cell.setCellValue(textValue);
     // } else if (value instanceof Long) {
     // long longValue = (Long) value;
     // cell.setCellValue(longValue);
     // }
     if (value instanceof Boolean)
     {
      boolean bValue = (Boolean) value;
      textValue = "男";
      if (!bValue)
      {
       textValue = "女";
      }
     }
     else if (value instanceof Date)
     {
      Date date = (Date) value;
      SimpleDateFormat sdf = new SimpleDateFormat(pattern);
      textValue = sdf.format(date);
     }
     else if (value instanceof byte[])
     {
      // 有图片时，设置行高为60px;
      row.setHeightInPoints(60);
      // 设置图片所在列宽度为80px,注意这里单位的一个换算
      sheet.setColumnWidth(i, (short) (35.7 * 80));
      // sheet.autoSizeColumn(i);
      byte[] bsValue = (byte[]) value;
      HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0,
        1023, 255, (short) 6, index, (short) 6, index);
      anchor.setAnchorType(2);
      patriarch.createPicture(anchor, workbook.addPicture(
        bsValue, HSSFWorkbook.PICTURE_TYPE_JPEG));
     }
     else
     {
      // 其它数据类型都当作字符串简单处理
      textValue = value.toString();
     }
     // 如果不是图片数据，就利用正则表达式判断textValue是否全部由数字组成
     if (textValue != null)
     {
      Pattern p = Pattern.compile("^//d+(//.//d+)?$");
      Matcher matcher = p.matcher(textValue);
      if (matcher.matches())
      {
       // 是数字当作double处理
       cell.setCellValue(Double.parseDouble(textValue));
      }
      else
      {
       HSSFRichTextString richString = new HSSFRichTextString(
         textValue);
       HSSFFont font3 = workbook.createFont();
       font3.setColor(HSSFColor.BLUE.index);
       richString.applyFont(font3);
       cell.setCellValue(richString);
      }
     }
    }
    catch (SecurityException e)
    {
     e.printStackTrace();
    }
    catch (NoSuchMethodException e)
    {
     e.printStackTrace();
    }
    catch (IllegalArgumentException e)
    {
     e.printStackTrace();
    }
    catch (IllegalAccessException e)
    {
     e.printStackTrace();
    }
    catch (InvocationTargetException e)
    {
     e.printStackTrace();
    }
    finally
    {
     // 清理资源
    }
   }
  }
  try
  {
   workbook.write(out);
  }
  catch (IOException e)
  {
   e.printStackTrace();
  }
 }
 public static void main(String[] args)
 {
  //测试工单查询
  ExportExcel<Orders> ex = new ExportExcel<Orders>();
  String[] headers =
  { "订单流失号", "业务类型", "客户名称", "客户地址", "服务号码" ,"证件类型","证件号码","联系电话","缴费项目","缴费金额","套餐ID","套餐名称",
    "服务列表","工单状态","工位资源信息","局向","测量室"};
  List<Orders> dataset = new ArrayList<Orders>();
  dataset.add(new Orders("111111","aaa","bbb","ccc","111" ,"111","证件号码","联系电话","缴费项目","缴费金额","套餐ID","套餐名称",
      "服务列表","工单状态","工位资源信息","局向","测量室"));
  try
  {
   OutputStream out = new FileOutputStream("c://a.xls");
   ex.exportExcel(headers, dataset, out);
   out.close();
   JOptionPane.showMessageDialog(null, "导出成功!");
   System.out.println("excel导出成功！");
  }
  catch (FileNotFoundException e)
  {
   e.printStackTrace();
  }
  catch (IOException e)
  {
   e.printStackTrace();
  }
 }
 }

 

 当然上头的代码要能完全执行还需要一个实体类：Orders 。get/set 变量即使headers 里的字段。如果是从数据取数据，可以同数据库字段。