- java把数据从数据库导入到excel
- package com.madhouse.sys.util;
- import java.io.File;
- import java.io.IOException;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.ResultSetMetaData;
- import java.sql.SQLException;
- import jxl.Workbook;
- import jxl.write.Label;
- import jxl.write.WritableSheet;
- import jxl.write.WritableWorkbook;
- import jxl.write.WriteException;
- import jxl.write.biff.RowsExceededException;
- import org.apache.commons.logging.Log;
- import org.apache.commons.logging.LogFactory;
- import com.mysql.jdbc.Connection;
- import com.mysql.jdbc.Statement;
- /**
- * <li>把数据导入到Excel公用类</li>
- * </br> This is about <code>ExcelUtil</code>
- *
- * @author hjy273
- * @version 1.0
- * @date Sep 6, 2008 9:52:52 PM
- */
- public class ExcelUtil {
- private static Log log = LogFactory.getLog(ExcelUtil.class);
- public ExcelUtil() {
- }
- public boolean DB2Excel(ResultSet rs) {
- boolean flag = false;
- WritableWorkbook workbook = null;
- WritableSheet sheet = null;
- Label label = null;
- // 创建Excel表
- try {
- workbook = Workbook.createWorkbook(new File("e:/_report/output.csv"));
- //workbook = Workbook.createWorkbook(os);
- // 创建Excel表中的sheet
- sheet = workbook.createSheet("First Sheet", 0);
- // 向Excel中添加数据
- ResultSetMetaData rsmd = rs.getMetaData();
- int columnCount = rsmd.getColumnCount();
- String colName = null;
- int row = 0;
- // 添加标题
- for (int i = 0; i < columnCount; i++) {
- colName = rsmd.getColumnName(i + 1);
- label = new Label(i, row, colName);
- // log.debug("标题:"+i+"---"+row +"---"+ colName);
- sheet.addCell(label);
- }
- row++;
- log.debug("写入标题成功");
- while (rs.next()) {
- for (int i = 0; i < columnCount; i++) {
- label = new Label(i, row, rs.getString(i + 1));
- log.debug("行:"+i+"---"+row +"---"+ rs.getString(i+1));
- sheet.addCell(label);
- }
- row++;
- }
- log.debug("写入内容成功");
- // 关闭文件
- workbook.write();
- workbook.close();
- log.info("数据成功写入Excel");
- flag = true;
- } catch (SQLException e) {
- log.debug(e.getMessage());
- } catch (RowsExceededException e) {
- log.debug(e.getMessage());
- } catch (WriteException e) {
- log.debug(e.getMessage());
- } catch (IOException e) {
- log.debug(e.getMessage());
- } finally {
- try {
- workbook.close();
- } catch (Exception e) {
- }
- }
- return flag;
- }
- /**
- * 测试方法
- *
- * @param args
- */
- public static void main(String[] args) {
- // write your code
- try {
- Class.forName("org.gjt.mm.mysql.Driver").newInstance();
- Connection conn = (Connection) DriverManager
- .getConnection(
- "jdbc:mysql://localhost:3306/firewall?useUnicode=true&characterEncoding=utf8",
- "root", "root");
- Statement st = (Statement) conn.createStatement();
- ResultSet rs = st
- .executeQuery("select * from firewall.tb_operator");
- if (!new ExcelUtil().DB2Excel(rs)){
- log.info("数据写入失败");
- }
- rs.close();
- st.close();
- conn.close();
- } catch (Exception e) {
- e.printStackTrace();
- }
- }
- }
- //该片段来自于http://www.codesnippet.cn/detail/220820135300.html
来源: http://www.codesnippet.cn/detail/220820135300.html