日期:2014-05-16  浏览次数:20539 次

将数据库的数据导出到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)
			/