跟上一个帖子一样。我们还是采用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:大功告成。