首先申明，导出Excel没下面的写的复杂，这里引入的是项目中的实例，就action可能需要自己修改下，其他的地方可以直接拿来COPY。action我做的两个类，希望大家可以看的明白。
1：页面部分
导 出: <a href="#" οnclick="javascript:sllToexcel();">Excel </a>2：页面ＪＳ部分 传入两个参数
function sllToexcel()
{
sd=document.getElementById("startDate").value;
ed=document.getElementById("endDate").value;
window.location.href="sllToexcel.action?startDate="+sd+"&endDate="+ed;
}3：java中的spring中
<action name="sllToexcel" class="bjyearcountAction" method="sllToexcel">
<result name="error" type="dispatcher">/default/error.jsp</result>
</action>
4：Java中的action
public String sllToexcel() {
org_id = super.getLoginUser().getUser_organid();
OutputStream out = null;
String[] keys=new String []{"ORGNAME","name1","name2","name3","name4","name5","name6","name7","name8","name9"};
String[] title=new String []{"单位名称","总受理数","网上申请(件数)","网上申请(百分比)","窗口申请(件数)","窗口申请(百分比)","网上受理(件数)","网上受理(百分比)","窗口受理(件数)","窗口受理(百分比)"};
try {
ServletActionContext.getResponse().reset();
ServletActionContext.getResponse().setContentType(
"application/octet-stream");
ServletActionContext.getResponse().setHeader("Content-Disposition",
"attachment; filename=slltj.xls");
out = ServletActionContext.getResponse().getOutputStream();
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(out);
shouliList = birtReportService.getShouliBanjianList(startDate, endDate,
org_id, getLoginInfo().getUser().getUser_isadmin());
birtReportService.toExcel(wwb, shouliList, keys,title);
out.close();
} catch (Exception e) {
e.printStackTrace();
return ERROR;
}
return SUCCESS;
}
下面这个希望可以让大家更加明白action的处理过程实现类可以直接引用
上一个是在存储过程中就处理好顺序了，下面这个列子是直接在action 中处理顺序，用MAP
public String sbjToexcel() {
result = new ArrayList();
org_id = super.getLoginUser().getUser_organid();
OutputStream out = null;
String[] keys = new String[] { "bjlx", "sj", "bj" };
String[] title = new String[] { "办件类型", "收件", "办结" };
try {
SimpleDateFormat simpledateformat = new SimpleDateFormat(
"yyyy-MM-dd");
sbjList = birtReportService.getSbjList(simpledateformat
.parse(this.startDate), simpledateformat
.parse(this.endDate), super.getLoginUser()
.getUser_organid(), super.getLoginUser().getUser_isadmin());
if (sbjList.size() > 0) {
Hashtable m = (Hashtable) sbjList.get(0);
Hashtable crj = new Hashtable();
Hashtable jbj = new Hashtable();
Hashtable spj = new Hashtable();
Hashtable hj = new Hashtable();
crj.put("bjlx", "承诺件");
crj.put("sj", m.get("CHENGNUOJIAN1"));
crj.put("bj", m.get("CHENGNUOJIAN2"));
jbj.put("bjlx", "即办件");
jbj.put("sj", m.get("JIBANJIAN1"));
jbj.put("bj", m.get("JIBANJIAN2"));
spj.put("bjlx", "审批件");
spj.put("sj", m.get("SHENPIJIAN1"));
spj.put("bj", m.get("SHENPIJIAN2"));
hj.put("bjlx", "合计");
hj.put("sj", m.get("TOTAL1"));
hj.put("bj", m.get("TOTAL2"));
result.add(crj);
result.add(jbj);
result.add(spj);
result.add(hj);
}
ServletActionContext.getResponse().reset();
ServletActionContext.getResponse().setContentType(
"application/octet-stream");
ServletActionContext.getResponse().setHeader("Content-Disposition",
"attachment; filename=sbj.xls");
out = ServletActionContext.getResponse().getOutputStream();
jxl.write.WritableWorkbook wwb = Workbook.createWorkbook(out);
birtReportService.toExcel(wwb, result, keys, title);
out.close();
} catch (Exception e) {
e.printStackTrace();
return ERROR;
}
return SUCCESS;
}
5：Java中的实现类 getShouliBanjianList，toExcel 。
5.1 先getShouliBanjianList ，这里拿只是为了放好顺序，好导出的时候用到
public List getShouliBanjianList(String stratDate, String endDate,
Integer user_orgid, Integer isAdmin) throws Exception {
class tmp_AfficheRSP implements RowCallbackHandler {
public void processRow(ResultSet rs) throws SQLException {// new
// DecimalFormat("##.00%");
Hashtable tmp = new Hashtable();
tmp.put("ORGNAME", rs.getString(1));
tmp.put("name1", rs.getLong(2));
tmp.put("name2", rs.getLong(4));
tmp.put("name3",
rs.getLong(4) > 0 ? new java.text.DecimalFormat(
"##.00%").format(new Double(rs.getLong(4))
/ new Double(rs.getLong(3))) : new Double(0.0));
tmp.put("name4", rs.getLong(5));
tmp.put("name5",
rs.getLong(5) > 0 ? new java.text.DecimalFormat(
"##.00%").format(new Double(rs.getLong(5))
/ new Double(rs.getLong(3))) : new Double(0.0));
tmp.put("name6", rs.getLong(6));
tmp.put("name7",
rs.getLong(6) > 0 ? new java.text.DecimalFormat(
"##.00%").format(new Double(rs.getLong(6))
/ new Double(rs.getLong(2))) : new Double(0.0));
tmp.put("name8", rs.getLong(7));
tmp.put("name9",
rs.getLong(7) > 0 ? new java.text.DecimalFormat(
"##.00%").format(new Double(rs.getLong(7))
/ new Double(rs.getLong(2))) : new Double(0.0));
tmp.put("name10", rs.getLong(3));
tmp.put("name11", rs.getLong(8));
SDTestVo sDTestVo = new SDTestVo(rs.getString(1), new Integer(
String.valueOf((rs.getLong(2)))));
tmp.put("sDTestVo", sDTestVo);
// ret.add(sDTestVo);
ret.add(tmp);
}
public List getRet() {
return ret;
}
private List ret;
tmp_AfficheRSP() {
ret = new ArrayList();
}
}
tmp_AfficheRSP ret = new tmp_AfficheRSP();
if (BaseParameter.getDbType().equals("oracle"))
execProc("TJFXREPORTPKG.tjfx_slltj(?,?,?,?)", new Object[] {
stratDate, endDate, user_orgid, isAdmin }, ret);
else
execProc("tjfx_slltj(?,?,?,?)", new Object[] { stratDate, endDate,
user_orgid, isAdmin }, ret);
return ret.getRet();
}
5.2 toExcel 的实现方法
* @param ve 需导出的列表 需要导出格式的顺序排列列
* @param ve 需导出的列表表头标题列表 注释：此标题每列 需要导出格式的顺序排列列
*/
public void toExcel(jxl.write.WritableWorkbook wwb, List ve,String[] keysList , String[] titleList)
{
//if(ve==null||titleList==null)return;
int datalistsize=0;//数据list长度
int collistsize=0;//标题list长度
collistsize=titleList.length;
datalistsize=ve.size();
//if(datalistsize==0||collistsize==0)return;
Hashtable tta=null;
try
{
jxl.write.WritableSheet ws= wwb.createSheet("booksheet", 10);
/***********设置列宽*****************/
/**************设置单元格字体***************/
WritableFont NormalFont = new WritableFont(WritableFont.ARIAL,10);
WritableFont BoldFont = new WritableFont(WritableFont.ARIAL,14,WritableFont.BOLD);
/**************以下设置几种格式的单元格*************/
//用于标题
WritableCellFormat wcf_title = new WritableCellFormat(BoldFont);
wcf_title.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_title.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直对齐
wcf_title.setAlignment(Alignment.CENTRE); //水平对齐
wcf_title.setWrap(false); //是否换行
//用于正文左
WritableCellFormat wcf_left = new WritableCellFormat(NormalFont);
wcf_left.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_left.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直对齐
wcf_left.setAlignment(Alignment.CENTRE);
wcf_left.setWrap(false); //是否换行
//用于正文右
WritableCellFormat wcf_right = new WritableCellFormat(NormalFont);
wcf_right.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_right.setVerticalAlignment(VerticalAlignment.CENTRE); //垂直对齐
wcf_right.setAlignment(Alignment.CENTRE);
wcf_right.setWrap(false); //是否换行
//用于跨行
WritableCellFormat wcf_merge = new WritableCellFormat(NormalFont);
wcf_merge.setBorder(Border.ALL, BorderLineStyle.THIN); //线条
wcf_merge.setVerticalAlignment(VerticalAlignment.TOP); //垂直对齐
wcf_merge.setAlignment(Alignment.LEFT);
wcf_merge.setWrap(true); //是否换行
/**************单元格格式设置完成*******************/
if(collistsize>0){
for(int h=0;h<collistsize;h++){
ws.setColumnView(h, 20);
ws.addCell(new jxl.write.Label(h, 0, titleList[h],wcf_title));
for (int i= 0; i < ve.size(); i++)
{
tta= (Hashtable)ve.get(i);
ws.addCell(new jxl.write.Label(h, i+1, ""+ tta.get(keysList[h].trim()),wcf_left));
}
}
}
else {
for(int h=0;h<collistsize;h++){
ws.setColumnView(h, 20);
// ws.addCell(new jxl.write.Label(h, 0, titleList.get(h).toString(),wcf_title));
for (int i= 0; i < ve.size(); i++)
{
tta= (Hashtable)ve.get(i);
ws.addCell(new jxl.write.Label(h, i+1, ""+ tta.get(keysList[h].trim()),wcf_left));
}
}
}
wwb.write();
//关闭Excel工作薄对象
wwb.close();
// fos.close();
} catch (IOException e)
{} catch (RowsExceededException e)
{} catch (WriteException e)
{}}
6.在看tjfx_slltj这个存储过程
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [dbo].[tjfx_slltj] @startDate varchar(20),
@endDate varchar(20),
@orgid bigint,
@isAdmin bigint as
create table #TMP_ORGID_SAMELEARYEAR(org_id bigint,org_name varchar(50))
create table #TMP_CASESUM (DATENAME varchar(40), SHOUJIAN numeric,ORDERCLOM bigint)
--受理量、申请量临时表
create table #TMP_SLLTJ (
ORG_ID bigint,
SHOULIL numeric,
SHENQINGL numeric,
SJLYNAME VARCHAR(40),
ORG_NAME VARCHAR(500)
)
CREATE TABLE #tmp_Organ(
[ORG_ID] [numeric](18, 0) NOT NULL,
[ORG_PID] [numeric](18, 0) NULL,
[DISTNO] [varchar](50) NULL,
[ORG_LAYER] [numeric](18, 0) NULL,
[ORG_NAME] [varchar](200) NOT NULL,
[ORG_SUBNAME] [varchar](200) NULL,
[ORG_CODE] [varchar](100) NOT NULL,
[ORG_ADDR] [varchar](200) NULL,
[ORG_TEL] [varchar](200) NULL,
[ORG_FLAG] [numeric](18, 0) NULL DEFAULT ((0)),
[HAVECHILD] [numeric](18, 0) NULL,
[ISXZFW] [varchar](2) DEFAULT ('Y'),
[ORG_SEQS] [numeric](18, 0) NULL
)
declare
@tmpOrgId bigint,
@tmpOrgName varchar(300),
@ORG_ID bigint,
@SHOULIL numeric,
@SHENQINGL numeric
begin
--建立网上申请统计的临时表
--插入用来分组显示的同级的组织机构
if( @isAdmin = 1 ) begin
Insert into #TMP_ORGID_SAMELEARYEAR
select distinct ORG_ID,ORG_NAME from SYS_ORGAN
where ISXZFW ='Y'and ORG_LAYER=1 and ORG_FLAG=0
end
else begin
Insert into #TMP_ORGID_SAMELEARYEAR
select distinct ORG_ID,ORG_NAME from SYS_ORGAN
where ISXZFW ='Y'and ORG_FLAG=0 and ORG_ID=@orgid
Insert into #TMP_ORGID_SAMELEARYEAR
select distinct ORG_ID,ORG_NAME from SYS_ORGAN
where ISXZFW ='Y'and ORG_FLAG=0 and ORG_PID=@orgid
end
--对各下级单位逐个进行统计
--定义游标
declare org_list cursor for
select org_id,org_name from #TMP_ORGID_SAMELEARYEAR
--打开游标
open org_list
fetch next from org_list into @tmpOrgId,@tmpOrgName
while @@fetch_status=0 begin
--清理临时组织机构表
delete from #tmp_Organ
--获取该单位的所有下级单位
if(@orgid!=@tmpOrgId) begin insert into #tmp_Organ exec getUnderOrgan @tmpOrgId end --不是本身的时候
else begin insert into #tmp_Organ (ORG_ID,ORG_PID,ORG_LAYER,ORG_NAME,ORG_SUBNAME,ORG_CODE,ORG_ADDR,ORG_TEL,ORG_FLAG,HAVECHILD,ISXZFW,ORG_SEQS)
values(@tmpOrgId,@tmpOrgId,1,@tmpOrgName,@tmpOrgName,'','','',0,0,'Y',1)--是本身的时候
end
----统计“窗口”形式受理
--统计当前单位的申请量
set @SHENQINGL = (
select isnull(sum(c.CASENUM),0)
from XZFW_SHOUJIAN c
where
c.org_id in(
select ORG_ID from #tmp_Organ)
and convert(varchar(10),c.revdate,120) >= @startDate
and convert(varchar(10),c.revdate,120) <= @endDate
)
--统计当前单位的受理量
/*set @SHOULIL = (
select isnull(sum(c.CASENUM),0)
from XZFW_SHOULI c
where
c.orgid in(
select ORG_ID from #tmp_Organ)
and convert(varchar(10),c.startdate,120) >= @startDate
and convert(varchar(10),c.startdate,120) <= @endDate
and c.sjly='1'
)zhangfei修改受理量统计一级页面和二级页面数量的不统一*/
set @SHOULIL = (
select isnull(sum(c.CASENUM),0)
from XZFW_SHOULI c , XZFW_SHOUJIAN x
where
c.caseid=x.caseid and
c.orgid in(select ORG_ID from #tmp_Organ)
and convert(varchar(10),x.revdate,120) >= @startDate
and convert(varchar(10),x.revdate,120) <= @endDate
and c.sjly='1'
)
--插入到临时结果集
insert into #TMP_SLLTJ(ORG_ID,SHOULIL,SHENQINGL,SJLYNAME,ORG_NAME)
values(@tmpOrgId,@SHOULIL,@SHENQINGL,'窗口',@tmpOrgName)
----统计“网上”形式受理
--统计当前单位的申请量
set @SHENQINGL = (
select isnull(sum(1),0)
from
NET_CASE c , XZFW_SERVICE_BASE x
where
c.SERVICE_ID=x.SERVICEOID
and x.ORG_ID in(
select ORG_ID from #tmp_Organ)
and convert(varchar(10),c.APPLICANT_DATE,120) >= @startDate
and convert(varchar(10),c.APPLICANT_DATE,120) <= @endDate
)
--统计当前单位的受理量
/*set @SHOULIL = (
select isnull(sum(1),0)
from XZFW_SHOULI c
where
c.orgid in(
select ORG_ID from #tmp_Organ)
and convert(varchar(10),c.startdate,120) >= @startDate
and convert(varchar(10),c.startdate,120) <= @endDate
and c.sjly='0'
)zhangfei修改受理量统计一级页面和二级页面数量的不统一*/
set @SHOULIL = (
select isnull(sum(1),0)
from XZFW_SHOULI c , NET_CASE x
where c.caseid=x.SHOULI_NUMBER
and c.orgid in(select ORG_ID from #tmp_Organ)
and convert(varchar(10),x.APPLICANT_DATE,120) >= @startDate
and convert(varchar(10),x.APPLICANT_DATE,120) <= @endDate
)
--插入到临时结果集
insert into #TMP_SLLTJ(ORG_ID,SHOULIL,SHENQINGL,SJLYNAME,ORG_NAME)
values(@tmpOrgId,@SHOULIL,@SHENQINGL,'网上',@tmpOrgName)
--处理下一个单位
fetch next from org_list into @tmpOrgId,@tmpOrgName
end
--关闭游标
close org_list
deallocate org_list --释放游标
--返回结果集
select c.ORG_NAME as ORG_NAME,sum(c.SHOULIL) zs,
sum(c.SHENQINGL) as name0,
sum(case when c.SJLYNAME='网上'then c.SHENQINGL else 0 end)as name1 ,
sum(case when c.SJLYNAME='窗口' then c.SHENQINGL else 0 end)as name2 ,
sum(case when c.SJLYNAME='网上' then c.SHOULIL else 0 end) as name3 ,
sum(case when c.SJLYNAME='窗口' then c.SHOULIL else 0 end)as name4,
max(c.ORG_ID)
from #TMP_SLLTJ c
group by c.ORG_NAME
union
select t.ORG_NAME as ORG_NAME,0,0,0,0,0,0,t.ORG_ID
from #TMP_ORGID_SAMELEARYEAR t
where t.ORG_ID not in(select distinct ORG_ID from #TMP_SLLTJ )
end
7.导入的jar包
//这里是实现类中的导入
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
import java.io.IOException;
最后总结~~
其实Java导出Excel并没有这么复杂。我只是在这里引入项目的实际列子，其实大家只要懂的基本的原来就知道怎么来处理复杂的导出了，下面我就在介绍下导出TXT，CSV，原理都差不多。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
