java 从mysql 导出到excel_JAVA实现在数据库导出到EXCEL并下载

package basedao;

import jxl.*;

import jxl.write.*;

import java.io.*;

import java.util.Iterator;

import java.util.Collection;

import vo.studentVo;

import database.GeneralDAO;

import database.Database;

/**

*

导出学生的用户名和密码到EXCEL表:

*

*

使用JXL包,实现对EXCEL文件的导出:

*

*

Copyright: Copyright (c) 2008

*

*

e21:

*

* @author 胡保林

* @version 1.0

*/

public class importtoExcelDao {

private String zkStuTable = "xs_2008";

Database db = new Database("");

private GeneralDAO dao = null;

public importtoExcelDao() {

dao = new GeneralDAO(db, zkStuTable);

}

public Collection getStudents(String xxcode) { //取学校的学生姓名，用户名，密码;

Collection col = null;

try {

col = dao.findDatas("XH,XM,MM", "xx=" + xxcode, null, studentVo.class);

return col;

} catch (Exception ex) {

}

return col;

}

public void importUserPwd(String filename, Iterator it) { //filename是生成文件的路径及用户名;it是内容, Iterator it;

try {

//open file.

WritableWorkbook book = Workbook.createWorkbook(new File(filename));

WritableSheet sheet = book.createSheet("学生用户名及密码表", 0);

Label label = new Label(0, 0, "姓名");

sheet.addCell(label);

label = new Label(1, 0, "用户名");

sheet.addCell(label);

label = new Label(2, 0, "密码");

sheet.addCell(label);

int row = 1;

for (; it.hasNext(); ) {

studentVo vo = (studentVo) it.next();

Label xm = new Label(0, row, vo.getXm());

sheet.addCell(xm);

Label username = new Label(1, row, vo.getXh());

sheet.addCell(username);

Label pwd = new Label(2, row, vo.getMm());

sheet.addCell(pwd);

// LabelCell label=new jxl.write.Label(0,row,vo.getXm());

// jxl.write.Number number = new jxl.write.Number(0, row,xm);

//add defined cell above to sheet instance.

// sheet.addCell(number);

//add defined all cell above to case.

row++;

}

book.write();

book.close();

} catch (Exception e) {

e.printStackTrace();

}

}

}package action.importdb;

import org.apache.struts.action.ActionMapping;

import org.apache.struts.action.ActionForm;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.ActionForward;

import org.apache.struts.action.Action;

import basedao.importtoExcelDao;

import java.util.Collection;

import java.util.Iterator;

import java.io.IOException;

import java.io.BufferedOutputStream;

import java.io.FileInputStream;

import util.*;

import java.io.InputStream;

public class importUserPwdAction extends Action {

public ActionForward execute(ActionMapping mapping, ActionForm form,

HttpServletRequest request,

HttpServletResponse response) throws

IOException {

importtoExcelDao dao = new importtoExcelDao();

String schoolcode = "420902026006"; //学校代码;

Collection col = dao.getStudents(schoolcode); //取学校的学生;

if (col != null) {

System.out.println("col is " + col.size());

Iterator it = col.iterator();

//dao.importUserPwd("d:/Test.xls",it);

String filename = schoolcode + ".xls"; //学校代码命名的excel表;

config con = new config();

String filepath = servlet.getServletContext().getRealPath("/") +

con.downloadPath + filename; //生成excel文件的路径;

dao.importUserPwd(filepath, it);

InputStream inStream = new FileInputStream(filepath);

response.reset();

response.setContentType("bin");

response.addHeader("Content-Disposition",

"attachment;   filename=\"" + filename + "\"");

byte[] b = new byte[100];

int len;

while ((len = inStream.read(b)) > 0) {

response.getOutputStream().write(b, 0, len);

}

inStream.close();

}

return null;

}

}