设为首页收藏本站
开启辅助访问

在路上

 找回密码
 立即注册
在路上 › 站点首页 ›学习 › 查看内容

通过读取excel文件生成sql语句

2017-2-7 13:39| 发布者: zhangjf| 查看: 453| 评论: 0

摘要: 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 jav ...
  1. package com.dj.dao.utils;
  2. import java.io.BufferedReader;
  3. import java.io.File;
  4. import java.io.FileInputStream;
  5. import java.io.FileOutputStream;
  6. import java.io.FileReader;
  7. import java.io.FileWriter;
  8. import java.io.IOException;
  9. import java.io.InputStream;
  10. import java.io.OutputStreamWriter;
  11. import java.io.Writer;
  12. import java.text.SimpleDateFormat;
  13. import java.util.ArrayList;
  14. import java.util.Date;
  15. import java.util.List;
  16. import javax.persistence.EntityManager;
  17. import javax.persistence.PersistenceContext;
  18. import javax.persistence.Query;
  19. import jxl.Cell;
  20. import jxl.Sheet;
  21. import jxl.Workbook;
  22. import jxl.format.Alignment;
  23. import jxl.format.Border;
  24. import jxl.format.BorderLineStyle;
  25. import jxl.write.Label;
  26. import jxl.write.WritableCellFormat;
  27. import jxl.write.WritableFont;
  28. import jxl.write.WritableSheet;
  29. import jxl.write.WritableWorkbook;
  30. public class ExcelToSql {
  31. public static void main(String[] args) throws Exception {
  32. // ArrayList<String> tableNames = excelToSql();
  33. //exceldel1();
  34. /*ArrayList<String> tableNames = new ArrayList<String>();
  35. tableNames.add("T_UBMP_SUPPLIEREXPERIENCEINFO");
  36. copyFile(tableNames);*/
  37. //getTables();
  38. 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");
  39. SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式
  40. System.out.println(df.format(new Date()));
  41. }
  43. @PersistenceContext
  44. private static EntityManager entityManager;
  45. public static void getTables() {
  46. Query query = entityManager
  47. .createNativeQuery("select name,remarks from sysibm.systables where type='T' and creator = 'UBMPOA' AND NAME LIKE 'T_UBMP_%'");
  48. List result = query.getResultList();
  49. System.out.println(result);
  50. System.out.println(((Object[])result.get(0))[0]);
  51. for(int index = 0; index < result.size(); index ++){
  52. String tableName = ((Object[])result.get(index))[0].toString();
  53. String tableNamedesc = ((Object[])result.get(index))[1].toString();
  54. System.out.println(tableName+":"+tableNamedesc);
  55. }
  56. }
  58. public static void excelToSql() throws Exception{
  59. jxl.Workbook readwb = null;
  61. FileOutputStream os = null;
  62. String file = "D:\sanxia\doc\trunk\新oa\数据库设计\已分配\乌商项目管理平台表结构汇总1.xls";
  63. String file2 = "D:\sanxia\doc\trunk\新oa\数据库设计\已分配\乌商项目管理平台表结构汇总2.xls";
  64. InputStream instream = new FileInputStream(file);
  65. readwb = Workbook.getWorkbook(instream);
  68. FileOutputStream os1 = new FileOutputStream(file2);// 创建一个输出流
  69. WritableWorkbook writewb = Workbook.createWorkbook(os1);
  70. WritableSheet sheet1 = writewb.createSheet("汇总", 0);
  71. int rowhz = 0;
  72. // for (int sheetNum = 2; sheetNum < 5; sheetNum++) {
  73. for (int sheetNum = 1; sheetNum < readwb.getNumberOfSheets(); sheetNum++) {
  75. Sheet readsheet = readwb.getSheet(sheetNum);
  76. int rsRows = readsheet.getRows();
  77. int colnum = readsheet.getColumns();
  78. for(int row = 0; row < rsRows; row ++){
  79. if(row == 0){
  80. sheet1.mergeCells(0, rowhz, 5, rowhz);
  81. }
  82. for(int col = 0; col < colnum; col ++){
  83. Cell str = readsheet.getCell(col, row);
  84. // CellFormat st = str.getCellFormat();
  85. WritableFont font1 = new WritableFont(WritableFont.createFont("Microsoft Sans Serif"),9);
  86. WritableCellFormat cellFormat1 = new WritableCellFormat(font1);
  87. cellFormat1.setAlignment(Alignment.LEFT);
  88. cellFormat1.setBackground((jxl.format.Colour.LIGHT_TURQUOISE));
  89. cellFormat1.setBorder(Border.ALL,BorderLineStyle.THIN);
  90. Label label = new Label(col, rowhz, str.getContents().toString(),cellFormat1);
  91. //Label label1 = new Label(col, rowhz, "123");
  93. sheet1.addCell(label);
  95. }
  96. rowhz = rowhz +1;
  98. }
  99. System.out.println(sheetNum);
  100. rowhz = rowhz + 2;
  101. System.out.println(rowhz);
  102. }
  103. writewb.write();
  104. os1.flush();
  106. readwb.close();
  107. try {
  108. if (writewb != null)
  109. writewb.close();
  110. if (os1 != null)
  111. os1.close();
  112. } catch (IOException e) {
  113. e.printStackTrace();
  114. }
  119. /*
  120. * //利用已经创建的Excel工作薄,创建新的可写入的Excel工作薄 jxl.write.WritableWorkbook wwb
  121. * = Workbook.createWorkbook(new File( "F:/红楼人物1.xls"), readwb);
  122. * //读取第一张工作表 jxl.write.WritableSheet ws = wwb.getSheet(0);
  123. * //获得第一个单元格对象 jxl.write.WritableCell wc = ws.getWritableCell(0,
  124. * 0); //判断单元格的类型, 做出相应的转化 if (wc.getType() == CellType.LABEL) {
  125. * Label l = (Label) wc; l.setString("新姓名"); } //写入Excel对象
  126. * wwb.write(); wwb.close();
  127. */
  130. }
  133. public static ArrayList<String> exceldel1(){
  134. ArrayList<String> tableNames = new ArrayList<String>();
  135. jxl.Workbook readwb = null;
  136. try {
  137. System.out.println("开始读取");
  138. // 构建Workbook对象, 只读Workbook对象
  139. // 直接从本地文件创建Workbook
  140. // InputStream instream = new FileInputStream("D:\sanxia\doc\trunk\新oa\数据库设计\已分配\乌商项目管理平台表结构汇总xsy.xls");
  141. InputStream instream = new FileInputStream("D:\sanxia\doc\trunk\新oa\数据库设计\待审查\张维科\数据库最新设计xsy.xls");
  142. readwb = Workbook.getWorkbook(instream);
  143. String path = "D:\sanxia\doc\trunk\新oa\数据库设计\待审查\张维科\20160112.sql";
  144. for (int sheetNum = 0; sheetNum < readwb.getNumberOfSheets(); sheetNum++) {
  145. // Sheet的下标是从0开始
  146. // 获取第一张Sheet表
  147. Sheet readsheet = readwb.getSheet(sheetNum);
  148. // 获取Sheet表中所包含的总列数
  149. int rsColumns = readsheet.getColumns();
  150. // 获取Sheet表中所包含的总行数
  151. int rsRows = readsheet.getRows();
  152. // 获取指定单元格的对象引用
  153. String sql = "";
  154. String tableName = "";
  155. String PK = "";
  156. for (int i = 0; i < rsRows; i++) {
  157. Cell[] str = readsheet.getRow(i);
  158. if (i == 0) {
  159. tableName = str[0].getContents().toString();
  160. int startIndex = tableName.indexOf("（");
  161. if (-1 == startIndex) {
  162. startIndex = tableName.indexOf("(");
  163. }
  164. int endIndex = tableName.indexOf("）");
  165. if (-1 == endIndex) {
  166. endIndex = tableName.indexOf(")");
  167. }
  168. // System.out.println(tableName+":"+startIndex+"|"+endIndex);
  169. tableName = tableName.substring(startIndex + 1,
  170. endIndex);
  171. if (tableName == null || "".equals(tableName.trim())) {
  172. System.out.println("表名未知，请检查");
  173. break;
  174. }
  175. tableName = tableName.toUpperCase();
  177. tableNames.add(tableName);
  178. System.out.println("tableName:" + tableName);
  179. //sql = sql + "drop table " + tableName + ";n";
  180. sql = sql + "nCREATE TABLE " + tableName;
  181. sql = sql + "n(n";
  182. }
  183. if (i == 1) {
  184. continue;
  185. }
  186. if (i >= 2) {
  187. if (i < 6) {
  188. if (str[5].getContents().toString().contains("PK")) {
  189. PK = PK + "," + str[1].getContents().toString();
  190. }
  191. }
  192. String colName = String.format("%-30s", str[1]
  193. .getContents().toString());
  194. String colSize = String.format("%-20s", str[3]
  195. .getContents().toString());
  196. if ("M".equals(str[4].getContents().toString())
  197. || "m".equals(str[4].getContents().toString())
  198. || "y".equals(str[4].getContents().toString())
  199. || "Y".equals(str[4].getContents().toString())) {
  200. sql = sql + "t" + colName + " " + colSize
  201. + " NOT NULL ,n";
  202. } else {
  203. sql = sql + "t" + colName + " " + colSize
  204. + " ,n";
  205. }
  206. }
  207. }
  208. if (PK.length() < 1) {
  209. System.out.println("表主键无主键,请自行添加,注意最后一列去掉逗号");
  210. } else {
  211. sql = sql + "tt PRIMARY KEY (" + PK.substring(1)
  212. + ") n";
  213. }
  214. sql = sql + ");n";
  215. // sql = sql +
  216. // "CREATE INDEX "+tableName+".INDEX1 ON "+tableName+"(KEY1,KEY2); --请自行替换KEY1、KEY2n";
  217. for (int i = 0; i < rsRows; i++) {
  218. Cell[] str = readsheet.getRow(i);
  219. if (i == 0) {
  220. String tableNamePre = str[0].getContents().toString();
  221. int startIndex = tableNamePre.indexOf("（");
  222. if (-1 == startIndex) {
  223. startIndex = tableNamePre.indexOf("(");
  224. }
  225. tableNamePre = tableNamePre.substring(0, startIndex);
  226. ;
  227. if (tableNamePre == null
  228. || "".equals(tableNamePre.trim())) {
  229. System.out.println("表名称未知，请检查");
  230. break;
  231. }
  232. String comment = tableNamePre;
  233. sql = sql + "COMMENT ON TABLE " + tableName + " IS '"
  234. + comment + "';n";
  235. }
  236. if (i == 1) {
  237. continue;
  238. }
  239. if (i >= 2) {
  240. String remark = str[5].getContents().toString();
  241. remark = remark.replace("'", "");
  242. remark = remark.replace(""", "");
  243. remark = remark.replace(",", "");
  244. remark = remark.replace("，", "");
  245. remark = remark.replace("。", "");
  246. remark = remark.replace(".", "");
  247. String comment = "";
  248. if ("".equals(remark.trim())) {
  249. comment = str[2].getContents().toString();
  250. } else {
  251. comment = str[2].getContents().toString() + ": "
  252. + remark;
  253. }
  254. sql = sql + "COMMENT ON COLUMN " + tableName + "."
  255. + str[1].getContents().toString() + " IS '"
  256. + comment + "';n";
  257. }
  258. }
  259. sql = sql.toUpperCase();
  261. String pathbak = "D:\sanxia\doc\trunk\新oa\系统设计\数据库设计\sql\compare\";
  262. File file = new File(path);
  263. //File file = new File(path + tableName + ".sql");
  264. // if(file.exists()){
  265. // file = new File(pathbak + tableName + ".sql");
  266. // System.err.println(tableName+"文件已存在,请在比较文件中查看");
  267. // }
  268. Writer outTxt = new OutputStreamWriter(new FileOutputStream(
  269. file, true), "unicode");
  270. outTxt.write(sql);
  271. outTxt.close();
  272. }
  276. /*
  277. * //利用已经创建的Excel工作薄,创建新的可写入的Excel工作薄 jxl.write.WritableWorkbook wwb
  278. * = Workbook.createWorkbook(new File( "F:/红楼人物1.xls"), readwb);
  279. * //读取第一张工作表 jxl.write.WritableSheet ws = wwb.getSheet(0);
  280. * //获得第一个单元格对象 jxl.write.WritableCell wc = ws.getWritableCell(0,
  281. * 0); //判断单元格的类型, 做出相应的转化 if (wc.getType() == CellType.LABEL) {
  282. * Label l = (Label) wc; l.setString("新姓名"); } //写入Excel对象
  283. * wwb.write(); wwb.close();
  284. */
  285. } catch (Exception e) {
  286. e.printStackTrace();
  287. } finally {
  288. readwb.close();
  289. }
  291. return tableNames;
  292. }
  294. /**
  295. * 复制单个文件
  296. *
  297. * @param oldPath
  298. * String 原文件路径 如：c:/fqf.txt
  299. * @param newPath
  300. * String 复制后路径 如：f:/fqf.txt
  301. * @return boolean
  302. */
  303. public static void copyFile(ArrayList<String> tableNames) {
  305. String path = "F:\wlmq_manage\workspace\ubmpoa\src\com\dj\action\uccb\projectBuild\";
  306. String sourceFile = "BuildreqAction.java";
  307. File modActionFile = new File(path+sourceFile);
  308. if(!modActionFile.exists()){
  309. System.out.print("原Action文件不存在");
  310. return;
  311. }
  312. for(String tableName:tableNames){
  314. try {
  315. String beanName = tableName.substring(7,8).toUpperCase()+tableName.substring(8).toLowerCase();
  316. String newFileName = beanName+"Action.java";
  317. //InputStream inStream = new FileInputStream(path+sourceFile); // 读入原文件
  318. //FileOutputStream fs = new FileOutputStream(path+newFileName);
  319. FileReader fr=new FileReader(path+sourceFile);
  320. BufferedReader br=new BufferedReader(fr);
  322. FileWriter write = new FileWriter(path+newFileName) ;
  323. while(br.readLine()!=null){
  324. String s=br.readLine();
  325. System.out.println(s);
  326. write.write(s+"n");
  327. /* if(s == null ){
  328. continue;
  329. }else if("".equals(s.trim())){
  330. write.write(s+"n");
  331. }else{
  332. write.write(s.replace("Buildreq", beanName)+"n");
  333. }*/
  334. }
  335. br.close();
  336. fr.close();
  337. write.close();
  338. } catch (Exception e) {
  339. System.out.println("复制单个文件操作出错");
  340. e.printStackTrace();
  341. }
  342. }
  343. }
  345. }
复制代码

来自： http://my.oschina.net/u/2540218/blog/602464

收藏 邀请
上一篇：Dubbox入门示例（注册服务器使用zookeeper）下一篇：Servlet 生命周期

最新评论

相关分类

小黑屋|在路上 ( 蜀ICP备15035742号-1 )  

;

GMT+8, 2025-7-9 07:51

Copyright 2015-2025 djqfx

返回顶部