导出数据到excel

最新推荐文章于 2023-08-01 12:39:20 发布

rouye2009

最新推荐文章于 2023-08-01 12:39:20 发布

阅读量95

点赞数

分类专栏： js、jQuery与jsp页面文章标签： Excel OS JavaScript jQuery SQL ViewUI

本文链接：https://blog.csdn.net/rouye2009/article/details/83775459

版权

js、jQuery与jsp页面专栏收录该内容

4 篇文章 0 订阅

订阅专栏

两种方法：一种是用js的方法，这种方法简单，直接导出页面数据，但是此方法不支持分页，一种是在后台写，支持分页

一：js的方法：

<%@ page contentType="text/html;charset=UTF-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>EB页面导出为EXCEL文档的方法</title>
<SCRIPT LANGUAGE="JavaScript">
<!--
function AutomateExcel() {
// Start Excel and get Application object.
var oXL = new ActiveXObject("Excel.Application");
// Get a new workbook.
var oWB = oXL.Workbooks.Add();
var oSheet = oWB.ActiveSheet;
var table = document.all.data;
var hang = table.rows.length;
var lie = table.rows(0).cells.length;

// Add table headers going cell by cell.
for (i=0;i<hang;i++){
  for (j=0;j<lie;j++){
   oSheet.Cells(i+1,j+1).Value = table.rows(i).cells(j).innerText;
  }

}
oXL.Visible = true;
oXL.UserControl = true;
}
//-->
</SCRIPT>
</head>

二是在后台写java代码：

jsp页面的jQuery：

   $(document).ready(function(){
         //导出信息
        $("#menuExport").click(function(){
           $("#f1").attr("action","case!export.action");
            $("#f1").submit();
            $("#f1").attr("action","case!doquery.action");
        });
   });
  </script>

导出按钮：

Action中的export方法：

private Page<AllEntity> pageExcel = new Page<AllEntity>(10000, true);

public void export() throws Exception {
try {
StringBuffer sqlBuffer = new StringBuffer("select t.case_id,t.title,t.case_type,t.relation_range from zft_case_general t where 1=1 ");

   String sql = sqlBuffer.toString();
   pageExcel = manager.getZft(pageExcel, sql);
   List<ZftAllEntity> caseList = pageExcel.getResult();

   String fname = "案件数据";// Excel文件名
   fname = new String(fname.getBytes("GBK"), "ISO-8859-1");// 解决文件中文名称问题
   OutputStream os = Struts2Utils.getResponse().getOutputStream();// 取得输出流

   Struts2Utils.getResponse().reset();// 清空输出流
   Struts2Utils.getResponse().setHeader("Content-disposition",
     "attachment; filename=" + fname + ".xls");// 设定输出文件头
   Struts2Utils.getResponse().setContentType("application/msexcel");// 定义输出类型
   CaseExcel eb = new CaseExcel();
   eb.exportExcel(os, caseList);// 调用生成excel文件bean
  } catch (Exception e) {
   System.out.println(e);
  }

}

CaseExcel 的代码：

package com.sdcncsi.web.datainterface;

import java.io.OutputStream;
import java.util.List;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import com.sdcncsi.common.util.DictUtil;
import com.sdcncsi.entity.AllEntity;
import com.sdcncsi.entity.zftsys.Dict;

public class CaseExcel {

private AllEntity entity;

public AllEntity getEntity() {
return entity;
}

public void setEntity(AllEntity entity) {
this.entity = entity;
}

public String exportExcel(OutputStream os, List<AllEntity> caseList) throws Exception {

  WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件
  String tmptitle = "案件记录"; // 标题
  WritableSheet wsheet = wbook.createSheet("第一页", 0); // sheet名称
  // 设置excel标题
  WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10,
    WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
    Colour.BLACK);
  WritableCellFormat wcfFC = new WritableCellFormat(wfont);
  wsheet.addCell(new Label(3, 0, tmptitle, wcfFC));

  // 开始生成主体内容
  wsheet.addCell(new Label(0, 1, "标题"));
  wsheet.addCell(new Label(1, 1, "执法案件来源"));
  wsheet.addCell(new Label(2, 1, "执法案件性质"));

  entity = new AllEntity();

  for (int i = 0; i < caseList.size(); i++) {
   wsheet.addCell(new Label(0, i + 2, ( caseList.get(i)).getTitle()));
   wsheet.addCell(new Label(1, i + 2, ( caseList.get(i)).getCase_type()));
  }
  // 主体内容生成结束
  wbook.write(); // 写入文件
  wbook.close();
  os.close();
  return "success";
}

}