java jxl将数据库中数据导出为excel

package demo;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;

import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 将数据库里面的内容导入到excel中,可以分页
 * 用到jxl.jar ojdbc.jar
 * @author gr
 * 
 */

public class ExcelImportDemo {
	static ResultSet rs = null;
	static ResultSetMetaData rsData = null;
	static int sheetIndex = 0; // 计数器,起始为0
	static int perPageSize = 60000;// excel2003每张表最大为65536行
	static String fileName = "text.xls";// 新建的表格名称
	static String URL = "jdbc:oracle:thin:@192.168.0.1:1521:orcl";// 数据库IP地址
	static String DriverName = "oracle.jdbc.driver.OracleDriver";
	static String username = "test";// 用户
	static String password = "test";// 密码

	public static void main(String[] args) throws Exception {
		connectOracle();

	}

	static void connectOracle() throws SQLException, ClassNotFoundException {
		Connection conn = null;
		Class.forName(DriverName);
		long begintime = System.currentTimeMillis();
		try {
			System.out.println("数据库连接开始:" + new Date(System.currentTimeMillis()));
			conn = DriverManager.getConnection(URL, username, password);
			Statement state = conn.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_UPDATABLE);
			String sql = "select * from pnr_user";
			rs = state.executeQuery(sql);
			rsData = rs.getMetaData();// 获得所需要的rs信息

			rs.last();
			int size = rs.getRow();// 列数
			rs.beforeFirst();
			int sheetsize = size / perPageSize + 1;// sheetsize是sheet的总页数

			writeexcel(rs);// 新建一个excel,并且写入第一张表,每次只能写一张表
			for (int i = 1; i < sheetsize; i++) {
				System.out.println(2);
				updateexcel(rs);// 写入后面的后面的表
			}
		} catch (Exception e) {
			System.out.println("数据库连接错误");
			e.printStackTrace();
		} finally {
			conn.close();
			System.out.println("数据库连接关闭!用时:"
					+ (System.currentTimeMillis() - begintime) + "ms");
		}
	}

	static void writeexcel(ResultSet rs) throws IOException, Exception,
			WriteException {
		WritableWorkbook book = Workbook.createWorkbook(new File(fileName));
		WritableSheet sheet = book.createSheet("第" + (sheetIndex + 1) + "页",
				sheetIndex);
		int i = 1;// 每一页设为第一行
		while (rs.next()) {
			for (int j = 1; j <= rsData.getColumnCount(); j++) {
				Label label = new Label(j - 1, i, rs.getString(j) == null ? ""
						: rs.getString(j).toString());
				if (i == 1) {// 标题只加载第一次
					Label titleLabel = new Label(j - 1, 0, rsData
							.getColumnName(j));
					sheet.addCell(titleLabel);
				}
				sheet.addCell(label);
			}
			i++;
			if (i > perPageSize) {
				break;
			}
		}
		sheetIndex++;
		book.write();
		book.close();
	}

	static void updateexcel(ResultSet rs) throws BiffException, IOException,
			RowsExceededException, WriteException, SQLException {
		Workbook wb = Workbook.getWorkbook(new File(fileName));
		WritableWorkbook book = Workbook.createWorkbook(new File(fileName), wb);

		WritableSheet sheet = book.createSheet("第" + (sheetIndex + 1) + "页 ",
				sheetIndex);
		int i = 1;// 每一页设为第一行
		while (rs.next()) {
			for (int j = 1; j <= rsData.getColumnCount(); j++) {
				Label label = new Label(j - 1, i, rs.getString(j) == null ? ""
						: rs.getString(j).toString());
				if (i == 1) {// 标题只加载第一次
					Label titleLabel = new Label(j - 1, 0, rsData
							.getColumnName(j));
					sheet.addCell(titleLabel);
				}
				sheet.addCell(label);
			}
			i++;
			if (i > perPageSize) {
				break;
			}
		}
		sheetIndex++;
		book.write();
		book.close();

	}
}

　　

转载于:https://www.cnblogs.com/imggr/p/8949493.html