/***********************************************************
场景模拟：某家公司需要对电脑已存在的员工信息集中生成Excel文档以便实时备案........
开发环境：window xp
开发工具：myEclipse m2-7.0
使用技术规范：j2EE规范
使用架构：MVC架构
使用技术和框架：jxl API,struts 2.0.6,hibernate 3.3,spring 2.5,jdbc
任务开发思路：
1.首先通过查询数据库得到员工信息
2.把员工信息对象存放到指定集合(List)中
3.编写一个专门对集合(List)操作后生成Excel的的工具对象
4.工具对象有三个行为(称为方法)：
4.1.对List中的数据进行分析后生成最终目标文档的方法
4.2.对最终文档的每行输入分析后的数据
4.3.对分析后的数据重新排练数据结构
5.把工具对象返回的以处理好的目标文档发送到客户端
*************************************************************/
下面开始编写：
1.实体类：Text.java /****此类为测试场景成员对象*****/
package com.**.POJO;
public class Text implements java.io.Serializable {
private static final long serialVersionUID = 1L;
private Integer textId;
private String textLeftName;
private String textRithName;
// Constructors
/** default constructor */
public Text() {
}
/** full constructor */
public Text(String textLeftName, String textRithName) {
this.textLeftName = textLeftName;
this.textRithName = textRithName;
}
// Property accessors
public Integer getTextId() {
return this.textId;
}
public void setTextId(Integer textId) {
this.textId = textId;
}
public String getTextLeftName() {
return this.textLeftName;
}
public void setTextLeftName(String textLeftName) {
this.textLeftName = textLeftName;
}
public String getTextRithName() {
return this.textRithName;
}
public void setTextRithName(String textRithName) {
this.textRithName = textRithName;
}
}
2.对应的hbm.xml：Text.hbm.xml
<hibernate-mapping>
<class name="com.**.POJO.Text" table="Text" schema="dbo" catalog="EXCEL">
<id name="textId" type="java.lang.Integer">
<column name="TextID" />
<generator class="native" />
</id>
<property name="textLeftName" type="java.lang.String">
<column name="TextLeftName" length="50" not-null="true" />
</property>
<property name="textRithName" type="java.lang.String">
<column name="TextRithName" length="50" not-null="true" />
</property>
</class>
</hibernate-mapping>
3.业务接口：TextTransanIn.java /******测试场景的业务规范接口*******/
package com.**.Intface;
import java.util.List;
import com.**.POJO.Text;
public interface TextTransanIn {
public Text findByText(Text t);
public List<Text> findByAll();
public boolean saveText(Text t);
public boolean attachText(Text t);
}
4.业务实现对象：TextTransanImp.java /**********实现了TextTransanIn才能拥有对业务处理的能力******/
package com.xuwang.Implement;
import java.util.List;
import com.**.DAO.TextDAO;
import com.**.Intface.TextTransanIn;
import com.**.POJO.Text;
public class TextTransanImp implements TextTransanIn {
private TextDAO textDAO;
public void setTextDAO(TextDAO textDAO) {
this.textDAO = textDAO;
}
public boolean attachText(Text t) {
try{
textDAO.merge(t);
return true;
}catch(Exception e){
return false;
}
}
public List<Text> findByAll() {
return textDAO.findAll();
}
public Text findByText(Text t) {
return textDAO.findById(t.getTextId());
}
public boolean saveText(Text t) {
try{
textDAO.save(t);
return true;
}catch(Exception e){
return false;
}
}
}
5.处理并发送目标文件入口点：TableAction.java /***********此对象用于接受到条件(也可以说是请求)，做出相应的目标文档*****/
package com.**.Action;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts2.ServletActionContext;
import com.opensymphony.xwork2.ActionSupport;
import com.**.Implement.TextTransanImp;
import com.**.POJO.Text;
import com.**.Util.ExcelWrite;
public class TableAction extends ActionSupport {
private static final String TYPE="aplication/vnd.ms-excel";
private static final String HEADER="Content-Disposition";
private static final long serialVersionUID = 1L;
public static long getSerialVersionUID() {
return serialVersionUID;
}
private TextTransanImp textTransanImp;
public void setTextTransanImp(TextTransanImp textTransanImp) {
this.textTransanImp = textTransanImp;
}
/**********************************
此处只是模拟场景需要，所以没有取传送的值，可以更具实际要求自己取的条件后在执行.
**********************************/
@SuppressWarnings("unchecked")
public String execute() throws IOException{
String[] title={"编号","姓氏","名字"};
List<Text> list=textTransanImp.findByAll();
HttpServletResponse response=ServletActionContext.getResponse();
response.setContentType(TYPE);
response.setHeader(HEADER,"inline; filename="+new String("人数统计.xls".getBytes("gb2312"),"iso8859-1"));
OutputStream out=response.getOutputStream();
ExcelWrite<Text> ew=new ExcelWrite<Text>();
ew.writeExcel(out, list, title, "人数统计");
out.close();
return null;
}
}
6.struts.xml
<!DOCTYPE struts PUBLIC
"-//Apache Software Foundation//DTD Struts Configuration 2.0//EN"
"http://struts.apache.org/dtds/struts-2.0.dtd">
<struts>
<constant name="struts.objectFactory" value="spring" />
<package name="info" extends="struts-default">
<action name="table" class="TableAction" />
</package>
</struts>
7.applicationContext.xml
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.0.xsd">
<bean>
<bean id="TextDAOTag" class="com.**.DAO.TextDAO">
<property name="sessionFactory">
<ref bean="sessionFactory" />
</property>
</bean>
<bean id="TextDAO" parent="transactionBase">
<property name="target">
<ref local="TextDAOTag" />
</property>
</bean>
<bean id="TextTransanImp"
class="com.**.Implement.TextTransanImp">
<property name="textDAO">
<ref local="TextDAOTag" />
</property>
</bean>
<bean id="TableAction" class="com.**.Action.TableAction" >
<property name="textTransanImp">
<ref local="TextTransanImp"/>
</property>
</bean>
</bean>
8.工具对象：ExcelWrite.java /*********此对象负责分析数据，排练数据，生成文档*************/
package com.xuwang.Util;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.util.List;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
public class ExcelWrite<T> {
int rowNum = 0;// 要写入得行号,默认是从0行开始
public boolean writeExcel(OutputStream out, List<T> datas, String[] title,
String titleName) {
try {
if (datas == null) {
throw new IllegalArgumentException("dates is null or not Object!");
}
WritableWorkbook work = Workbook.createWorkbook(out);
WritableSheet ws = work.createSheet(titleName, 0);
if (title != null) {
this.putRow(ws, rowNum, title);
rowNum++;
}
Object[] oo=new Object[title.length];
for (int i = 0; i < datas.size(); rowNum++,i++) {
Object o = datas.get(i);
Method[] m = o.getClass().getMethods();
this.putRow(ws, rowNum, this.settingBy(o, m,oo));
}
work.write();
work.close();
out.close();
return true;
} catch (Exception e) {
e.printStackTrace();
return false;
}
}
public void putRow(WritableSheet sheet, int rowNum, Object[] cell)
throws RowsExceededException, WriteException {
for (int j = 0; j < cell.length; j++) {
if(cell[j]!=null){
WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES,10, WritableFont.NO_BOLD, true);
WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);
Label lblCell = new Label(j, rowNum, cell[j].toString(), wcfF);
sheet.addCell(lblCell);
}
}
}
public Object[] settingBy(Object o, Method[] m,Object[] oo)
throws IllegalArgumentException, IllegalAccessException,InvocationTargetException {
int i=0;
for (int j = 0; j < m.length; j++) {
if (m[j].toString().indexOf("get") > 0) {
if (m[j].toString().indexOf("getClass") < 0) {
String str = m[j].invoke(o, null).toString();
if (str != null) {
oo[i] = str;
i++;
}
}
}
}
return oo;
}
}
以上全部任务就完成了，接下来我们来测试下：
测试类：MainText.java /******模拟客户端请求*******/
package com.**.Text;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.**.Implement.TextTransanImp;
import com.**.Intface.TextTransanIn;
import com.**.POJO.Text;
import com.**.Util.ExcelWrite;
public class MainText {
public static void main(String[] rgs) throws FileNotFoundException{
String[] title = { "textId", "textLeftName", "textRithName" };
ApplicationContext ic = new ClassPathXmlApplicationContext("applicationContext.xml");
TextTransanIn tt = (TextTransanImp) ic.getBean("TextTransanImp");
List<Text> list = tt.findByAll();
ExcelWrite<Text> ew = new ExcelWrite<Text>();
ew.writeExcel(new FileOutputStream(new File("d:/net.xls")), list, title, "text");
}
}
测试后将会在D盘创建名为net.xls的文档，最后我们就成功了！