首先将要导出的数据库表的数据封装到一个list集合中,然后循环遍历该list集合
再应用jxl组件将这些数据写入excel文件。
具体的看代码吧
package com.ybhacker.mailbox.util;
import Java.io.File;
import java.util.List;
import jxl.*;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.Label;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import com.ybhacker.mailbox.model.BoxList;
/**
* Excel操作
*
* @author Windows7
*
*/
public class ExcelOperationUtil {
/**
* 保存数据内容到excel
* @param list
* @param savepath
* @return
*/
public boolean readDataToExcelFile(List<BoxList> list, String savepath) {
try {
WritableWorkbook book = Workbook.createWorkbook(new File(savepath));
WritableSheet sheet = book.createSheet("SHELL", 0);
// 设置字体样式
jxl.write.WritableFont font = new jxl.write.WritableFont(
WritableFont.ARIAL, 15, WritableFont.BOLD, false,
UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);
jxl.write.WritableCellFormat cellFormat = new jxl.write.WritableCellFormat(
font);
cellFormat.setAlignment(Alignment.CENTRE);
cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);// 设置单元格内容两端对齐
cellFormat.setBackground(Colour.GRAY_25);// 背景颜色
Label label_title = new Label(0, 0, "WEBSHELL收信箱子系统V2.0",
cellFormat);
sheet.mergeCells(0, 0, 4, 0);// 合并第一行的第1个到第5个单元格
sheet.setRowView(0, 600, false);// 设置第一行的行高
Label label_id = new Label(0, 1, "ID");
Label label_url = new Label(1, 1, "木马地址");
Label label_script = new Label(2, 1, "脚本类型");
Label label_pass = new Label(3, 1, "密码");
Label label_host = new Label(4, 1, "域名");
Label label_google = new Label(5, 1, "谷歌权重");
Label label_baidu = new Label(6, 1, "百度权重");
Label label_indexed = new Label(7, 1, "收录总数");
Label label_createtime = new Label(8, 1, "创建时间");
Label label_sell = new Label(9, 1, "是否出售");
sheet.setColumnView(4, 15);// 设置列宽
sheet.addCell(label_title);
sheet.addCell(label_id);
sheet.addCell(label_url);
sheet.addCell(label_script);
sheet.addCell(label_pass);
sheet.addCell(label_host);
sheet.addCell(label_google);
sheet.addCell(label_baidu);
sheet.addCell(label_indexed);
sheet.addCell(label_createtime);
sheet.addCell(label_sell);
for (int i = 0; i < list.size(); i++) {// 遍历数据对象的集合，将所有信息导出到Excel
BoxList temp = (BoxList) list.get(i);
String script = "未知";
String createtime = temp.getnCreateTime().toString();
if (temp.getnScript() == 1) {
script = "ASP";
}
if (temp.getnScript() == 2) {
script = "PHP";
}
if (temp.getnScript() == 3) {
script = "ASPX";
}
if (temp.getnScript() == 4) {
script = "JSP";
}
String Sell = "正常";
if (temp.isnSell()) {
Sell = "已售";
}
Label id_value = new Label(0, i + 2, temp.getId() + "");
Label url_value = new Label(1, i + 2, temp.getnUrl());
Label script_value = new Label(2, i + 2, script);
Label pass_value = new Label(3, i + 2, temp.getnPass());
Label host_value = new Label(4, i + 2, temp.getnHost());
Label google_value = new Label(5, i + 2, temp.getnGoogle());
Label baidu_value = new Label(6, i + 2, temp.getnBaidu());
Label indexed_value = new Label(7, i + 2, temp.getnIndexed());
Label createtime_value = new Label(8, i + 2, createtime);
Label sell_value = new Label(9, i + 2, Sell);
sheet.addCell(id_value);
sheet.addCell(url_value);
sheet.addCell(script_value);
sheet.addCell(pass_value);
sheet.addCell(host_value);
sheet.addCell(google_value);
sheet.addCell(baidu_value);
sheet.addCell(indexed_value);
sheet.addCell(createtime_value);
sheet.addCell(sell_value);