使用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(List034        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}