DBA数据库字典

package com.fhc.bestone;

import java.io.FileOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFHyperlink;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.maodajun.javaeye6.ExcleFile;

public class ExcelWorker {
	static Connection conn ＝new MyConn();

	/**
	 * @param args
	 * @throws Exception 
	 */
	public static void main(String[] args) throws Exception {
		ExcleFile e = new ExcleFile();
		HSSFWorkbook wb = e.getBook("云南项目数据库设计.xls");
		//从表纸中提取信息
		List tablelist = new ArrayList<String>();
		HSSFSheet sheet = wb.getSheet("表名页");
		for(int i =0 ;sheet.getRow(i)!=null ; i ++){
			 HSSFCell tmp = sheet.getRow(i)
							.getCell((short)1);
			 String str = tmp.toString();
			System.out.println(tmp.getRichStringCellValue().toString());
			tablelist.add(str);
			//新建样本sheet
			HSSFSheet tmpsheet = wb.cloneSheet(wb.getSheetIndex("样本DB"));
                       //sheet改名
			wb.setSheetName(wb.getSheetIndex(tmpsheet), str);
			//sheet加连接
			HSSFHyperlink link  = new HSSFHyperlink(HSSFHyperlink.LINK_DOCUMENT);
			link.setAddress("'"+str+"'!A1");
			tmp.setHyperlink(link);
			//添加英文表名
			HSSFSheet clonesheet = wb.getSheet(str);			
			tmpsheet.getRow(1).getCell(9).setCellValue(new HSSFRichTextString(str));
			
			Statement s =  conn.createStatement();
			Statement s1 = conn.createStatement();
			Statement s2 = conn.createStatement();
			//表名的注释
			ResultSet rs = s.executeQuery("select * from USER_TAB_COLUMNS WHERE TABLE_NAME='"+str+"'");
			//列名
			ResultSet rs1 = s1.executeQuery("select COMMENTS from user_tab_comments WHERE TABLE_NAME='"+str+"'");
			if(rs1.next()){
				String tablecomm= rs1.getString(1);				
				System.out.println(tablecomm);
				sheet.getRow(i)
				.getCell((short)4)
				.setCellValue(new HSSFRichTextString(tablecomm));
			}
			int j = 0 ;
			while(rs.next()){				
				String col = rs.getString(2);
				//列名的注释
				ResultSet rs2 = s2.executeQuery("select comments from user_col_comments WHERE TABLE_NAME='"+str+"'"+"  and COLUMN_NAME='"+col+"'");
				rs2.next();
				String comm =rs2.getString(1);
				System.out.println("\t"+col+ "\t "+comm);
				rs2.close();
				HSSFRow r = clonesheet.getRow(7+j);
				HSSFCell c = null;
				if(r!=null) c = r.getCell(3);
				if(c!=null){
					clonesheet.getRow(7+j).getCell(3).setCellValue(new HSSFRichTextString(col));
					clonesheet.getRow(7+j).getCell(2).setCellValue(new HSSFRichTextString(comm));
				}
				j++;
			}
			rs.close();
			s.close();
			s2.close();
		}
		//存储此表
	     FileOutputStream fileOut = new FileOutputStream("云南项目数据库设计.xls");
	     wb.write(fileOut);
	     fileOut.close();
	}
	

}

没有测试没有重构。。。
有没有手快的兄弟帮忙重构一下。？写个测试？

评论

2 楼 xiao-qiang163 2010-04-06  

这个代码写得太次了！  咋看咋恶心，不想多评论了！

1 楼 YuLimin 2008-08-12  

懒人。。。半仙。。。