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;
  }
 }
}