Java实现Oracle导出多表数据到Excel

   


1.引jar包jxl.jar(放在lib路径下面),这个包网上自己下载

2.读取orale数据代码

package test;
import java.io.File;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import Util.JDBC;
import jxl.Workbook;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
 * oracle数据导入到excel中
 * 
 * @author de
 */
public class DBtoExcel extends excelToOracle {
	static excelToOracle excel0 = new excelToOracle();
	static JDBC jdbc = new JDBC();
	// ‘得到结果集的结构信息,比如字段数、字段名等。
	static ResultSetMetaData data1;
	static ResultSetMetaData data3;
	// ‘创建可写工作表
	static WritableSheet sheet;
	// ‘设置写入字体
	static WritableFont font1, font2, fontKey;
	// ‘设置CellFormat
	static WritableCellFormat format1, format2, format3, format4, format5, formatKey;
	// ‘创建真实写入的 Excel 工作薄对象
	static WritableWorkbook book = null;
	// ‘列名
	static String columnName = null;
	// ‘列数
	static int columnCount = 0;
	// ‘列数
	static int keyColumnCount = 0;
	// ‘x 行
	static int x = 1;
	// ‘表名数量
	static int m = 0;
	// ‘表名
	static String myArray[] = { "TESTCUST", "TESTCUST2" };
	static ResultSet rs1;
	static ResultSet rs2;
	static ResultSet rs3;
	// ‘comment
	static String comment = null;
	// ‘primaryKey
	static String primarykey = null;
	// ‘取数据库数据
	public static void main(String[] args) throws Exception {
		System.out.println(myArray.length);
		// ‘写入execl操作
		writeExecl(myArray);
	}
	// ‘写入execl操作
	public static void writeExecl(Object[] myArray) {
		Date now = new Date();
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		String nowdate = df.format(now);
		try {
			// ‘根据路径生成excel文件
			book = Workbook.createWorkbook(new File(nowdate + ".xls"));
		} catch (IOException e) {
			// System.out.println("生成excel文件失败,请稍后重试!")
			e.printStackTrace();
		}
		System.out.println(nowdate + ".xls");
		// ‘创建一个sheet名为"第一页"的工作表,参数0表示这是第一
		sheet = book.createSheet("第一页", 0);
		// ‘设置表样式
		tableStyle();
		String selSql = null;
		String commentsSql = null;
		String primaryKeySql = null;

		// ‘循环取表
		for (m = 0; m < myArray.length; m++) {
			// ‘主键取得
			primaryKeySql = "SELECT T1.COLUMN_NAME FROM USER_CONS_COLUMNS T1, USER_CONSTRAINTS T2 WHERE T1.CONSTRAINT_NAME = T2.CONSTRAINT_NAME AND T2.CONSTRAINT_TYPE = 'P' AND T2.TABLE_NAME = '"
					+ myArray[m] + "'";
			System.out.println(primaryKeySql);
			rs3 = jdbc.executeQuery(primaryKeySql);
			// ‘获取主键字段
			try {
				data3 = rs3.getMetaData();
				// ‘获得所有列的数目及实际列数
				keyColumnCount = data3.getColumnCount();
				// System.out.println("keyColumnCount: " + keyColumnCount);
				while (rs3.next()) {
					for (int i = 1; i <= keyColumnCount; i++) {
						// primarykey
						primarykey = rs3.getString(i);
						System.out.println("primarykey: " + primarykey);
					}
				}
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			// ‘Sql 根据主键 primarykey 排序
			selSql = "select * from " + myArray[m] + " ORDER BY " + primarykey;
			// ‘comments取得
			commentsSql = "select T2.comments FROM USER_TAB_COLUMNS T1 INNER JOIN USER_COL_COMMENTS T2 ON T2.COLUMN_NAME = T1.COLUMN_NAME AND T2.TABLE_NAME = T1.TABLE_NAME WHERE T1.TABLE_NAME = '"
					+ myArray[m] + "'  ORDER BY T1.COLUMN_ID";
			rs1 = jdbc.executeQuery(selSql);
			rs2 = jdbc.executeQuery(commentsSql);
			// ‘得到结果集的结构信息,比如字段数、字段名等。
			try {
				data1 = rs1.getMetaData();
				// ‘获得所有列的数目及实际列数
				columnCount = data1.getColumnCount();
			} catch (SQLException e) {
				// System.out.println("EXCEL写入失败,请稍后重试!");
				e.printStackTrace();
			}
			// ‘创建表
			createTable();
		}
		// ‘写入数据,关闭
		writeORclose();
	}
	// ‘创建表
	public static void createTable() {
		// ‘列名
		int z = 1;
		// ‘序号
		int num = 1;
		for (int i = 1; i <= columnCount; i++) {
			// ‘写入表名
			Label labelD = new Label(1, x, String.valueOf(myArray[m]), format4);
			try {
				sheet.addCell(labelD);
				// ‘获得指定列的列名
				columnName = data1.getColumnName(i);
				Label labelA = null;
				// ‘列名 = 主键名 字体颜色 为红色
				if (columnName.equals(primarykey)) {
					// ‘创建单元格 Label(列,行,字段名称 ,xx);
					labelA = new Label(1, x + 1, columnName, formatKey);
				} else {
					// ‘创建单元格 Label(列,行,字段名称 ,xx); 字体颜色 为黑色
					labelA = new Label(i, x + 1, columnName, format1);
				}
				// ‘添加到行中;
				sheet.addCell(labelA);
				// ‘写入TABLE
				Label labelE = new Label(0, x, constants.table, format2);
				sheet.addCell(labelE);
				// ‘写入COLUMN
				Label labelF = new Label(0, x + 1, constants.column, format2);
				sheet.addCell(labelF);
				// ‘写入項目名
				Label labelG = new Label(0, x + 2, constants.itemname, format2);
				sheet.addCell(labelG);
			} catch (RowsExceededException e) {
				// System.out.println("EXCEL单元设置创建失败!");
				e.printStackTrace();
			} catch (WriteException e) {
				// System.out.println("EXCEL写入失败,请稍后重试!");
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		// ‘項目名
		for (int i = 1; i <= columnCount; i++) {
			try {
				while (rs2.next()) {
					// comments
					comment = rs2.getString(i);
					Label labelB = new Label(z, x + 2, comment, format3);
					sheet.addCell(labelB);
					z++;
				}
			} catch (RowsExceededException e) {
				// System.out.println("EXCEL单元设置创建失败!");
				e.printStackTrace();
			} catch (WriteException e) {
				// System.out.println("EXCEL写入失败,请稍后重试!");
				e.printStackTrace();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		// ‘列值
		try {
			while (rs1.next()) {
				for (int i = 1; i <= columnCount; i++) {
					// ‘获得指定列的列值
					String columnValue = rs1.getString(i);
					System.out.println("获得列" + i + "的字段值:" + columnValue);
					// ‘Label(列,行,字段名称 ,xx);
					Label labelC = new Label(i, x + 3, columnValue, format5);
					sheet.addCell(labelC);
					// ‘写入序号
					Label labelH = new Label(0, x + 3, String.valueOf(num), format2);
					sheet.addCell(labelH);

				}
				x++;
				num++;
			}
		} catch (RowsExceededException e) {
			// System.out.println("EXCEL单元设置创建失败!");
			e.printStackTrace();
		} catch (WriteException e) {
			// System.out.println("EXCEL写入失败,请稍后重试!");
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		// ‘行数+6
		x = x + 6;
	}
	// ‘写入数据,关闭
	public static void writeORclose() {
		// ‘写入数据并关闭文件
		try {
			book.write();
			book.close();
			System.out.println("创建EXECL文件成功!");
		} catch (IOException e) {
			// System.out.println("EXCEL写入失败,请稍后重试!");
			e.printStackTrace();
		} catch (WriteException e) {
			// System.out.println("EXCEL写入失败,请稍后重试!");
			e.printStackTrace();
		}
	}
	// ‘设置表样式
	public static void tableStyle() {
		// ‘去掉整个sheet中的网格线
		sheet.getSettings().setShowGridLines(false);
		// ‘给sheet电子版中所有的列设置默认的列的宽度;
		sheet.getSettings().setDefaultColumnWidth(10);
		// ‘设置字体为宋体,12号字,加粗,颜色为黑色
		font1 = new WritableFont(WritableFont.createFont("标准"), 12, WritableFont.NO_BOLD);
		font2 = new WritableFont(WritableFont.createFont("标准"), 12, WritableFont.BOLD);
		fontKey = new WritableFont(WritableFont.createFont("标准"), 12, WritableFont.NO_BOLD);
		// ‘字体颜色
		try {
			font1.setColour(Colour.BLACK);
			font2.setColour(Colour.BLUE);
			fontKey.setColour(Colour.RED);
			format1 = new WritableCellFormat(font1);
			format2 = new WritableCellFormat(font1);
			format3 = new WritableCellFormat(font1);
			format4 = new WritableCellFormat(font2);
			format5 = new WritableCellFormat(font1);
			formatKey = new WritableCellFormat(fontKey);
			// ‘设置文字对齐方式;
			format4.setAlignment(jxl.format.Alignment.LEFT);
			// ‘设置垂直居中;
			format4.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
			// ‘设置背景颜色;
			format1.setBackground(Colour.SKY_BLUE);
			format2.setBackground(Colour.GRAY_25);
			format3.setBackground(Colour.LIGHT_TURQUOISE);
			formatKey.setBackground(Colour.SKY_BLUE);
			// ‘设置边框;
			// ‘设置表头表格边框样式
			// ‘整个表格线为粗线、黑色
			format1.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			format2.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			format3.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			format5.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
			formatKey.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);

		} catch (WriteException e) {
			// System.out.println("EXCEL写入失败,请稍后重试!");
			e.printStackTrace();
		}
	}
}
数据库连接的工具类在此博客的–Java实现Excel多个表数据导入到Oracle
3.写入execl数据表图,
本文如有问题请多多指教

发表评论