将数据库的数据导出到excel表格中。

跟上一个帖子一样。我们还是采用struts来帮助我们完成。

1:先在jsp页面定义一个导出链接。

 <a href="downloadFile.action?name=user.xls">导出数据库</a>

2：在struts中配置我们的action

<package name="excel" extends="struts-default">
		 <action name="downloadFile" class="action.DownExcel">
			<result type="stream">
				<param name="contentDisposition">attachment;filename=${filename}</param>
				<param name="inputName">downloadFile</param>
			</result>
		</action>					
</package>

3：书写我们的action喽

package action;

import java.io.File;
import java.io.InputStream;
import java.util.List;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import model.Person;

import org.apache.struts2.ServletActionContext;

import com.opensymphony.xwork2.ActionSupport;

import dao.PersonDAO;
import dao.impl.PersonDAOImpl;

public class DownExcel extends ActionSupport {
	private String name;
	private String filename;

	public String getFilename() {
		return filename;
	}

	public void setFilename(String filename) {
		this.filename = filename;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public InputStream getDownloadFile() {
		createExcel(getAllUser());

		try {
			this.filename = name;

			return ServletActionContext.getServletContext()
					.getResourceAsStream("/upload/" + name);
			
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	@Override
	public String execute() throws Exception {
		System.out.println("downExcel execute()");

		return SUCCESS;
	}
		
	//在服务器端创建一个excel文件。并写入从数据库读出数据

	private void createExcel(List<Person> list) {

		String root = ServletActionContext.getRequest().getRealPath("/upload");
		try {

			File file = new File(root, name);
			if (file!=null) {
				file.delete();
			}
			// 打开文件
			WritableWorkbook book = Workbook.createWorkbook(new File(root, name));
			// 生成名为“第一页”的工作表，参数0表示这是第一页
			WritableSheet sheet = book.createSheet("第一页", 0);
			
			//创建小字体：Arial,大小为12号,非粗体，非斜体
			WritableFont wf = new WritableFont(WritableFont.ARIAL, 12,WritableFont.NO_BOLD, false);
			//字体颜色为红色
			wf.setColour(jxl.format.Colour.BLACK);
			
			//创建单元格格式：设置水平对齐为居中对齐
			jxl.write.WritableCellFormat CwcfF = new jxl.write.WritableCellFormat(wf);
			CwcfF.setAlignment(jxl.write.Alignment.CENTRE);

			// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
			// 以及单元格内容为test
			Label label1 = new Label(0, 0, "序号", CwcfF);
			Label label2 = new Label(1, 0, "用户名", CwcfF);
			Label label3 = new Label(2, 0, "密码", CwcfF);
			Label label4 = new Label(3, 0, "年龄", CwcfF);
			Label label5 = new Label(4, 0, "生日", CwcfF);
			

			// 将定义好的单元格添加到工作表中
			sheet.addCell(label1);
			sheet.addCell(label2);
			sheet.addCell(label3);
			sheet.addCell(label4);
			sheet.addCell(label5);

			/**//*
				 * 生成一个保存数字的单元格 必须使用Number的完整包路径，否则有语法歧义 单元格位置是第二列，第一行，值为789.123
				 */
			// jxl.write.Number number = new jxl.write.Number(1, 0, 555.12541);
			// sheet.addCell(number);

			//创建小字体：Arial,大小为12号,非粗体，非斜体
			 wf = new WritableFont(WritableFont.ARIAL, 12,WritableFont.NO_BOLD, false);
			//字体颜色为红色
			wf.setColour(jxl.format.Colour.GRAY_50);
			
			for (int i = 0; i < list.size(); i++) {
				Person person = list.get(i);
				if (person != null) {
					sheet.addCell(new jxl.write.Number(0, i + 1, i + 1, CwcfF));
					sheet.addCell(new Label(1, i + 1, person.getUsername(), CwcfF));
					sheet.addCell(new Label(2, i + 1, person.getPassword(), CwcfF));
					sheet.addCell(new jxl.write.Number(3, i + 1, person.getAge(), CwcfF));
					sheet.addCell(new Label(4, i + 1, person.getRegisterDate() + "", CwcfF));
					 
				}
			}

			// 写入数据并关闭文件
			book.write();
			book.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	//从数据库读取所有数据。并且做一个一个List返回
	private List<Person> getAllUser() {
		PersonDAO dao = new PersonDAOImpl();
		return dao.listAllPersons();
	}
}

4：大功告成。