J2EE项目中如何设计一个比较通用的Excel模板功能

在最近的项目开发中，发现，每个查询页面，都需要要去下载生成一个Excel文件的功能，并且，这个需求还是比较常见的，以前呢？就是单独为每个下载Excel，去定制写好下载功能了。。但是，渐渐发现，这种方式是不可取的，因为每个下载都要去设计一个下载功能，这样就无形中做了很多的重复劳动。。能不能写个通用的生成Excel模块呢？实际上就是要把重复处理的代码单独封装出来了！当然，这里参考了原来同事的写法后，在上面作了一些改进，代码量就大大减少了，如是就有了今天这遍“写个一个比较通用的Excel生成模板的方法”的文章来产生了，也希望的这遍文章，能对其它网友有所帮助！这里主要是用到了java反射方面的知识！

当然，要把的如下这种做法，想移植到自己的项目中去的话，稍作修改就可以了！
如下图

红色框中，有九个链接，都有对应的查询页面，每个查询页面得到的最终结果页面上，都会有一个“下载”的功能




根据下载的实际需求，生成模板的基本格式大致如下所示



对于上面的九个链接，也有如下九个模板文件

(每个模板文件的字段都各不相同的，相同的是“第一行是显示列名，第二行开始是以EL ${属性名}来表示的)








点击前面页面的“下载”功能后，显示结果页面效果大致如下所示











“下载”链接基本形式如下所示

<a href="DownDataPortalExcel?flag=dp_receiptjoborder" title="点击下载吧">下载</a>

也就是说用同一个Serverlet　DownDataPortalExcel来响应，根据flag标识的值来区分是下载的哪个模块

DownDataPortalExcel.java 文件中主要代码如下



public void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {

HttpSession session=request.getSession();

//新生成的Excel文件名
String filename="";
List<Object> list=null;

String flag=request.getParameter("flag");
//在途
if(flag.equals("dp_transit"))
{
filename="transit";
// List<TransitBean> list=null;
list=(ArrayList<Object>)session.getAttribute("transitbeanlist");
}
//进货
if(flag.equals("dp_receipt"))
{
filename="receipt";
//List<ReceiptBean> list=null;
list=(ArrayList<Object>)session.getAttribute("receiptbeanlist");
}
//出货
if(flag.equals("dp_issue"))
{
filename="issue";
//List<IssueBean> list=null;
list=(ArrayList<Object>)session.getAttribute("issuebeanlist");
}
//库存
if(flag.equals("dp_inventory"))
{
filename="inventory";
// List<InventoryBean> list=null;
list=(ArrayList<Object>)session.getAttribute("inventorybeanlist");
}
//sku control
if(flag.equals("dp_skucontrol"))
{
filename="skucontrol";
// List<SkuControlBean> list=null;
list=(ArrayList<Object>)session.getAttribute("skucontrolbeanlist");
}
//aging report
if(flag.equals("dp_agingreport"))
{
filename="agingreport";
// List<AgingReportBean> list=null;
list=(ArrayList<Object>)session.getAttribute("agingreportbeanlist");
}
//active skus
if(flag.equals("dp_activeskus"))
{
filename="activesku";
// List<ActiveSKUSBean> list=null;
list=(ArrayList<Object>)session.getAttribute("activeskusbeanlist");
}
//log search
if(flag.equals("dp_logsearch"))
{
filename="logsearch";
// List<LogSearchBean> list=null;
list=(ArrayList<Object>)session.getAttribute("logsearchbeanlist");
}
//receipt joborder 进货工作单
if(flag.equals("dp_receiptjoborder"))
{
filename="receiptjoborder";
// List<ReceiptJobOrderBean> list=null;
list=(ArrayList<Object>)session.getAttribute("receiptjoborderbeanlist");
}

//封装项目名 从web.xml中来取值
ServletContext globlctx=request.getSession().getServletContext();
String projectname=globlctx.getInitParameter("projectname"); //得到ishprojects

OutputStream os = response.getOutputStream();

response.reset(); // 清空输出流
response.setHeader("Content-disposition", "attachment; filename="+filename+".xls");// 设定输出文件头
response.setContentType("application/msexcel");//
WriteExcel we = new WriteExcel();
we.WriteDPDataToExcel(list, projectname, filename,os);
os.close();
}



WriteExcel.java 文件　中调用方法如下


//处理 在途 进货 出货 库存 skucontrol activesku agingreport logsearch receiptjoborder
public void WriteDPDataToExcel(List list,String projectname,String filename,OutputStream os)
{
try
{
String root = System.getProperty("user.dir");
String src = root.substring(0, root.lastIndexOf("\"))+ "\\webapps\"+projectname+"\\excelstemplate\"+filename+".xls";
String dst = root.substring(0, root.lastIndexOf("\"))+ "\\webapps\"+projectname+"\\excelstemplate\"+filename+"_temp.xls";
File srcfile=new File(src);
File dstfile=new File(dst);

JWriterExcelTemplet jwe = new JWriterExcelTemplet(srcfile,dstfile);

//向目标文件写入内容

if(list!=null)
{
//从第二行开始
int fromrow=1;
jwe.setValues(fromrow,list);
//将新生成的文件作为输入源，放到输出流中去输出来就可完成下载的过程了
File srfile=new File(dst);
FileInputStream fi=new FileInputStream(srfile);
byte[] buf = new byte[2048];
int hasRead=0;
while((hasRead=fi.read(buf))>0)
{
os.write(buf, 0, hasRead);
}
os.close();
dstfile.deleteOnExit();
}
}
catch(Exception e)
{
e.printStackTrace();
}
}



通用的处理Excel文件模板类JWriterExcelTemplet代码如下



public class JWriterExcelTemplet {

private Workbook srcWorkbook = null;

private WritableWorkbook destWorkbook = null;

private WritableSheet sheet = null;

private OutputStream os = null;

public JWriterExcelTemplet() {
}


public JWriterExcelTemplet(File src, File dest) {
try {
srcWorkbook = Workbook.getWorkbook(src);
destWorkbook = Workbook.createWorkbook(dest, srcWorkbook);
sheet = destWorkbook.getSheet(0);
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}

public JWriterExcelTemplet(File src,OutputStream os) {
try {
this.os = os;
srcWorkbook = Workbook.getWorkbook(src);
destWorkbook = Workbook.createWorkbook(os, srcWorkbook);
sheet = destWorkbook.getSheet(0);
} catch (IOException e) {
e.printStackTrace();
} catch (BiffException e) {
e.printStackTrace();
}
}

public OutputStream getOs() {
return os;
}

public void setOs(OutputStream os) {
this.os = os;
}

public void setValues(int fromrow,List list) {
try {
//记录从Excel文件第几行开始搜索EL ${}的名字
int row = fromrow;
int col=30;

for (int j = 0; j < col; j++)
{
//设置可以写的单元格
WritableCell cell = sheet.getWritableCell(j,row);
if (cell.getType() == CellType.LABEL)
{
Label label = (Label) cell;
//当单元格中没有出现以$开头的字符时，不执行后面的语句，继续开始循环
if (!label.getString().startsWith("${"))
{
continue;
}

//下面这句就得到了${}中{和}之间的内容了
String vs = getField(label.getString());
//将List中相应的所有数据全部插入到Excel文件中的某一列中
insertCells(vs,row,j,list);
}
}
destWorkbook.write();
destWorkbook.close();
srcWorkbook.close();

} catch (WriteException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}


public String getField(String arg) {
if (arg.startsWith("${") && arg.endsWith("}"))
{
return arg.substring(arg.indexOf("{") + 1, arg.indexOf("}"));
}
return "";
}




public void insertCells(String fieldRef,int row,int col,List list) {
try {
Object object = null;
//只取list的第一个元素的值
object = (Object)list.get(0);
Class classType = object.getClass();
//getDeclaredFields() 包括公共、保护、默认（包）访问和私有字段
Field[] fields = classType.getDeclaredFields();
for (int i = 0; i < fields.length; i++)
{
Field f = fields[i];
String fValue = f.getName();

if (fValue.equalsIgnoreCase(fieldRef))
{

String firstLetter = fValue.substring(0, 1).toUpperCase();
String methodName = "get" + firstLetter+ fValue.substring(1);

//这里的classType实际上就是上面的类名
Method method = classType.getMethod(methodName, new Class[] {});
if (list.size() == 1)
{
Object obj = method.invoke(object, new Object[]{});
addCell(row,col,obj == null ? "":obj.toString()); //加入值
}
else
{
//这里要作一个判断，因为记录行，超过了Excel 2003 sheet文件最大行数65535行数后，会报错

//如超过，就只取65530行记录生成到Excel文件中了
int size=list.size();
if(size>=65535)
{
size=65530;
}
for (int k = 0; k < size; k++)
{
Object obj = method.invoke((Object)list.get(k), new Object[]{});
addCell(row,col,obj == null ? "":obj.toString()); //加入值
row ++;
}
}
}
else
{
continue;
}
}
} catch (NoSuchMethodException e) {
e.printStackTrace();
} catch (IllegalArgumentException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (InvocationTargetException e) {
e.printStackTrace();
}
}

public void addCell(int row,int col,String value) {
try {
Label label = new Label(col,row,value);

sheet.addCell(label);
} catch (Exception e) {
e.printStackTrace();
}
}

public static void main(String[] args) {
@SuppressWarnings("unused")
JWriterExcelTemplet jwe = new JWriterExcelTemplet(new File("D:\\aa.xls"), new File("D:\\xy.xls"));

}

public WritableWorkbook getDestWorkbook() {
return destWorkbook;
}

public void setDestWorkbook(WritableWorkbook destWorkbook) {
this.destWorkbook = destWorkbook;
}

public WritableSheet getSheet() {
return sheet;
}

public void setSheet(WritableSheet sheet) {
this.sheet = sheet;
}

public Workbook getSrcWorkbook() {
return srcWorkbook;
}

public void setSrcWorkbook(Workbook srcWorkbook) {
this.srcWorkbook = srcWorkbook;
}
}