2019独角兽企业重金招聘Python工程师标准>>> 
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();
}
}
}
}