通过读取excel文件生成sql语句
jopen
8年前
package com.dj.dao.utils; import java.io.BufferedReader; import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.FileReader; import java.io.FileWriter; import java.io.IOException; import java.io.InputStream; import java.io.OutputStreamWriter; import java.io.Writer; import java.text.SimpleDateFormat; import java.util.ArrayList; import java.util.Date; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import javax.persistence.Query; import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.format.Alignment; import jxl.format.Border; import jxl.format.BorderLineStyle; import jxl.write.Label; import jxl.write.WritableCellFormat; import jxl.write.WritableFont; import jxl.write.WritableSheet; import jxl.write.WritableWorkbook; public class ExcelToSql { public static void main(String[] args) throws Exception { // ArrayList<String> tableNames = excelToSql(); //exceldel1(); /*ArrayList<String> tableNames = new ArrayList<String>(); tableNames.add("T_UBMP_SUPPLIEREXPERIENCEINFO"); copyFile(tableNames);*/ //getTables(); System.out.println("select name,remarks from sysibm.systables where type='T' and creator = 'UBMPOA' AND NAME LIKE 'T_UBMP\\_%\\_%' escape '\\' order by name asc"); SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式 System.out.println(df.format(new Date())); } @PersistenceContext private static EntityManager entityManager; public static void getTables() { Query query = entityManager .createNativeQuery("select name,remarks from sysibm.systables where type='T' and creator = 'UBMPOA' AND NAME LIKE 'T_UBMP_%'"); List result = query.getResultList(); System.out.println(result); System.out.println(((Object[])result.get(0))[0]); for(int index = 0; index < result.size(); index ++){ String tableName = ((Object[])result.get(index))[0].toString(); String tableNamedesc = ((Object[])result.get(index))[1].toString(); System.out.println(tableName+":"+tableNamedesc); } } public static void excelToSql() throws Exception{ jxl.Workbook readwb = null; FileOutputStream os = null; String file = "D:\\sanxia\\doc\\trunk\\新oa\\数据库设计\\已分配\\乌商项目管理平台表结构汇总1.xls"; String file2 = "D:\\sanxia\\doc\\trunk\\新oa\\数据库设计\\已分配\\乌商项目管理平台表结构汇总2.xls"; InputStream instream = new FileInputStream(file); readwb = Workbook.getWorkbook(instream); FileOutputStream os1 = new FileOutputStream(file2);// 创建一个输出流 WritableWorkbook writewb = Workbook.createWorkbook(os1); WritableSheet sheet1 = writewb.createSheet("汇总", 0); int rowhz = 0; // for (int sheetNum = 2; sheetNum < 5; sheetNum++) { for (int sheetNum = 1; sheetNum < readwb.getNumberOfSheets(); sheetNum++) { Sheet readsheet = readwb.getSheet(sheetNum); int rsRows = readsheet.getRows(); int colnum = readsheet.getColumns(); for(int row = 0; row < rsRows; row ++){ if(row == 0){ sheet1.mergeCells(0, rowhz, 5, rowhz); } for(int col = 0; col < colnum; col ++){ Cell str = readsheet.getCell(col, row); // CellFormat st = str.getCellFormat(); WritableFont font1 = new WritableFont(WritableFont.createFont("Microsoft Sans Serif"),9); WritableCellFormat cellFormat1 = new WritableCellFormat(font1); cellFormat1.setAlignment(Alignment.LEFT); cellFormat1.setBackground((jxl.format.Colour.LIGHT_TURQUOISE)); cellFormat1.setBorder(Border.ALL,BorderLineStyle.THIN); Label label = new Label(col, rowhz, str.getContents().toString(),cellFormat1); //Label label1 = new Label(col, rowhz, "123"); sheet1.addCell(label); } rowhz = rowhz +1; } System.out.println(sheetNum); rowhz = rowhz + 2; System.out.println(rowhz); } writewb.write(); os1.flush(); readwb.close(); try { if (writewb != null) writewb.close(); if (os1 != null) os1.close(); } catch (IOException e) { e.printStackTrace(); } /* * //利用已经创建的Excel工作薄,创建新的可写入的Excel工作薄 jxl.write.WritableWorkbook wwb * = Workbook.createWorkbook(new File( "F:/红楼人物1.xls"), readwb); * //读取第一张工作表 jxl.write.WritableSheet ws = wwb.getSheet(0); * //获得第一个单元格对象 jxl.write.WritableCell wc = ws.getWritableCell(0, * 0); //判断单元格的类型, 做出相应的转化 if (wc.getType() == CellType.LABEL) { * Label l = (Label) wc; l.setString("新姓名"); } //写入Excel对象 * wwb.write(); wwb.close(); */ } public static ArrayList<String> exceldel1(){ ArrayList<String> tableNames = new ArrayList<String>(); jxl.Workbook readwb = null; try { System.out.println("开始读取"); // 构建Workbook对象, 只读Workbook对象 // 直接从本地文件创建Workbook // InputStream instream = new FileInputStream("D:\\sanxia\\doc\\trunk\\新oa\\数据库设计\\已分配\\乌商项目管理平台表结构汇总xsy.xls"); InputStream instream = new FileInputStream("D:\\sanxia\\doc\\trunk\\新oa\\数据库设计\\待审查\\张维科\\数据库最新设计xsy.xls"); readwb = Workbook.getWorkbook(instream); String path = "D:\\sanxia\\doc\\trunk\\新oa\\数据库设计\\待审查\\张维科\\20160112.sql"; for (int sheetNum = 0; sheetNum < readwb.getNumberOfSheets(); sheetNum++) { // Sheet的下标是从0开始 // 获取第一张Sheet表 Sheet readsheet = readwb.getSheet(sheetNum); // 获取Sheet表中所包含的总列数 int rsColumns = readsheet.getColumns(); // 获取Sheet表中所包含的总行数 int rsRows = readsheet.getRows(); // 获取指定单元格的对象引用 String sql = ""; String tableName = ""; String PK = ""; for (int i = 0; i < rsRows; i++) { Cell[] str = readsheet.getRow(i); if (i == 0) { tableName = str[0].getContents().toString(); int startIndex = tableName.indexOf("（"); if (-1 == startIndex) { startIndex = tableName.indexOf("("); } int endIndex = tableName.indexOf("）"); if (-1 == endIndex) { endIndex = tableName.indexOf(")"); } // System.out.println(tableName+":"+startIndex+"|"+endIndex); tableName = tableName.substring(startIndex + 1, endIndex); if (tableName == null || "".equals(tableName.trim())) { System.out.println("表名未知，请检查"); break; } tableName = tableName.toUpperCase(); tableNames.add(tableName); System.out.println("tableName:" + tableName); //sql = sql + "drop table " + tableName + ";\n"; sql = sql + "\nCREATE TABLE " + tableName; sql = sql + "\n(\n"; } if (i == 1) { continue; } if (i >= 2) { if (i < 6) { if (str[5].getContents().toString().contains("PK")) { PK = PK + "," + str[1].getContents().toString(); } } String colName = String.format("%-30s", str[1] .getContents().toString()); String colSize = String.format("%-20s", str[3] .getContents().toString()); if ("M".equals(str[4].getContents().toString()) || "m".equals(str[4].getContents().toString()) || "y".equals(str[4].getContents().toString()) || "Y".equals(str[4].getContents().toString())) { sql = sql + "\t" + colName + " " + colSize + " NOT NULL ,\n"; } else { sql = sql + "\t" + colName + " " + colSize + " ,\n"; } } } if (PK.length() < 1) { System.out.println("表主键无主键,请自行添加,注意最后一列去掉逗号"); } else { sql = sql + "\t\t PRIMARY KEY (" + PK.substring(1) + ") \n"; } sql = sql + ");\n"; // sql = sql + // "CREATE INDEX "+tableName+".INDEX1 ON "+tableName+"(KEY1,KEY2); --请自行替换KEY1、KEY2\n"; for (int i = 0; i < rsRows; i++) { Cell[] str = readsheet.getRow(i); if (i == 0) { String tableNamePre = str[0].getContents().toString(); int startIndex = tableNamePre.indexOf("（"); if (-1 == startIndex) { startIndex = tableNamePre.indexOf("("); } tableNamePre = tableNamePre.substring(0, startIndex); ; if (tableNamePre == null || "".equals(tableNamePre.trim())) { System.out.println("表名称未知，请检查"); break; } String comment = tableNamePre; sql = sql + "COMMENT ON TABLE " + tableName + " IS '" + comment + "';\n"; } if (i == 1) { continue; } if (i >= 2) { String remark = str[5].getContents().toString(); remark = remark.replace("'", ""); remark = remark.replace("\"", ""); remark = remark.replace(",", ""); remark = remark.replace("，", ""); remark = remark.replace("。", ""); remark = remark.replace(".", ""); String comment = ""; if ("".equals(remark.trim())) { comment = str[2].getContents().toString(); } else { comment = str[2].getContents().toString() + ": " + remark; } sql = sql + "COMMENT ON COLUMN " + tableName + "." + str[1].getContents().toString() + " IS '" + comment + "';\n"; } } sql = sql.toUpperCase(); String pathbak = "D:\\sanxia\\doc\\trunk\\新oa\\系统设计\\数据库设计\\sql\\compare\\"; File file = new File(path); //File file = new File(path + tableName + ".sql"); // if(file.exists()){ // file = new File(pathbak + tableName + ".sql"); // System.err.println(tableName+"文件已存在,请在比较文件中查看"); // } Writer outTxt = new OutputStreamWriter(new FileOutputStream( file, true), "unicode"); outTxt.write(sql); outTxt.close(); } /* * //利用已经创建的Excel工作薄,创建新的可写入的Excel工作薄 jxl.write.WritableWorkbook wwb * = Workbook.createWorkbook(new File( "F:/红楼人物1.xls"), readwb); * //读取第一张工作表 jxl.write.WritableSheet ws = wwb.getSheet(0); * //获得第一个单元格对象 jxl.write.WritableCell wc = ws.getWritableCell(0, * 0); //判断单元格的类型, 做出相应的转化 if (wc.getType() == CellType.LABEL) { * Label l = (Label) wc; l.setString("新姓名"); } //写入Excel对象 * wwb.write(); wwb.close(); */ } catch (Exception e) { e.printStackTrace(); } finally { readwb.close(); } return tableNames; } /** * 复制单个文件 * * @param oldPath * String 原文件路径 如：c:/fqf.txt * @param newPath * String 复制后路径 如：f:/fqf.txt * @return boolean */ public static void copyFile(ArrayList<String> tableNames) { String path = "F:\\wlmq_manage\\workspace\\ubmpoa\\src\\com\\dj\\action\\uccb\\projectBuild\\"; String sourceFile = "BuildreqAction.java"; File modActionFile = new File(path+sourceFile); if(!modActionFile.exists()){ System.out.print("原Action文件不存在"); return; } for(String tableName:tableNames){ try { String beanName = tableName.substring(7,8).toUpperCase()+tableName.substring(8).toLowerCase(); String newFileName = beanName+"Action.java"; //InputStream inStream = new FileInputStream(path+sourceFile); // 读入原文件 //FileOutputStream fs = new FileOutputStream(path+newFileName); FileReader fr=new FileReader(path+sourceFile); BufferedReader br=new BufferedReader(fr); FileWriter write = new FileWriter(path+newFileName) ; while(br.readLine()!=null){ String s=br.readLine(); System.out.println(s); write.write(s+"\n"); /* if(s == null ){ continue; }else if("".equals(s.trim())){ write.write(s+"\n"); }else{ write.write(s.replace("Buildreq", beanName)+"\n"); }*/ } br.close(); fr.close(); write.close(); } catch (Exception e) { System.out.println("复制单个文件操作出错"); e.printStackTrace(); } } } }