Java將數據庫導出到Excel

用到了jxl組件

首先將要導出的數據庫表的數據封裝到一個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);

}
book.write();
book.close();
return true;
} catch (Exception e) {
System.out.println("異常信息：" + e.getMessage());
e.printStackTrace();
return false;
}
}
}