Java将数据库导出到Excel

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