excel转换成oracle建表脚本

  1 package excel2sql;
  2 
  3 import java.io.File;
  4 import java.io.FileInputStream;
  5 import java.io.FileNotFoundException;
  6 import java.io.IOException;
  7 import java.util.ArrayList;
  8 import java.util.List;
  9 
 10 import org.apache.poi.hssf.usermodel.HSSFCell;
 11 import org.apache.poi.hssf.usermodel.HSSFRow;
 12 import org.apache.poi.hssf.usermodel.HSSFSheet;
 13 import org.apache.poi.hssf.usermodel.HSSFWorkbook;
 14 import org.apache.poi.ss.usermodel.Row;
 15 
 16 public class Excel2Sql {
 17 
 18     private HSSFWorkbook workbook;// 工作簿
 19 
 20     /**
 21      * 构造器-获取工作簿
 22      * @param file
 23      */
 24     public Excel2Sql(File file) {
 25         try {
 26             // 获取工作薄workbook
 27             workbook = new HSSFWorkbook(new FileInputStream(file));
 28         } catch (FileNotFoundException e) {
 29             e.printStackTrace();
 30         } catch (IOException e) {
 31             e.printStackTrace();
 32         }
 33     }
 34 
 35     /**
 36      * 获取sheet中所有数据
 37      * @param sheetNumber
 38      * @return
 39      */
 40     private List<List<Object>> getSheetDatas(int sheetNumber) {
 41         
 42         //sheet中数据结果集
 43         List<List<Object>> result = new ArrayList<List<Object>>();
 44 
 45         // 获得指定的sheet
 46         HSSFSheet sheet = workbook.getSheetAt(sheetNumber);
 47         
 48         // 获得sheet总行数
 49         int rowCount = sheet.getLastRowNum();
 50         
 51         if (rowCount < 1) {
 52             return result;
 53         }
 54 
 55         // 遍历行row
 56         for (int rowIndex = 0; rowIndex <= rowCount; rowIndex++) {
 57             // 获得行对象
 58             HSSFRow row = sheet.getRow(rowIndex);
 59             if (null != row) {
 60                 List<Object> rowData = new ArrayList<Object>();
 61                 // 获取对应行单元格总数
 62                 int cellCount = row.getLastCellNum();
 63                 // 遍历列cell
 64                 for (int cellIndex = 0; cellIndex < cellCount; cellIndex++) {
 65                     //Missing cells are returned as null, Blank cells are returned as normal
 66                     HSSFCell cell = row.getCell(cellIndex, Row.RETURN_NULL_AND_BLANK);
 67                     // 获得指定单元格中的数据
 68                     Object cellValue = this.getCellValue(cell);
 69 
 70                     rowData.add(cellValue);
 71                 }
 72                 result.add(rowData);
 73             }
 74         }
 75 
 76         return result;
 77     }
 78 
 79     /**
 80      * 获取cell值
 81      * @param cell
 82      * @return
 83      */
 84     private Object getCellValue(HSSFCell cell) {
 85         Object result = null;
 86         if (cell != null) {
 87             // 单元格类型：Numeric:0;String:1;Formula:2;Blank:3;Boolean:4;Error:5
 88             switch (cell.getCellType()) {
 89             case HSSFCell.CELL_TYPE_NUMERIC:
 90                 result = cell.getNumericCellValue();
 91                 break;
 92             case HSSFCell.CELL_TYPE_STRING:
 93                 result = cell.getStringCellValue();
 94                 break;
 95             case HSSFCell.CELL_TYPE_FORMULA:
 96                 result = cell.getNumericCellValue();
 97                 break;
 98             case HSSFCell.CELL_TYPE_BLANK:
 99                 result = null;
100                 break;
101             case HSSFCell.CELL_TYPE_BOOLEAN:
102                 result = cell.getBooleanCellValue();
103                 break;
104             case HSSFCell.CELL_TYPE_ERROR:
105                 result = null;
106                 break;
107             }
108         }
109         return result;
110     }
111 
112     /**
113      * excel格式转换成建表脚本
114      * @param tableName 表名
115      * @param tableComment 表注释
116      * @return SQL 建库脚本
117      */
118     public String excel2Sql(String tableName, String tableComment) {
119         //获取sheet数据
120         List<List<Object>> datas = this.getSheetDatas(0);
121         //主键
122         String primary = "";
123         //字段列
124         int column = 0;
125         //类型列
126         int type = 1;
127         //是否为空列
128         int cannull = 2;
129         //默认值列
130         int defaultValue = 3;
131         //是否主键
132         int pk = 4;
133         //注释列
134         int comment = 5;
135         //注释
136         StringBuffer comments = new StringBuffer();
137         //表名注释
138         comments.append("comment on table "+tableName+" is '"+tableComment+"';\n");
139         //建表SQL语句
140         StringBuffer sql = new StringBuffer();
141         sql.append("create table " + tableName + "(\n");
142         //前两行为说明
143         for (int i = 2; i < datas.size(); i++) {
144             //对应行数据
145             List<Object> row = datas.get(i);
146             //字段项 0
147             sql.append(String.valueOf(row.get(column)) + " ");
148             //类型 1
149             sql.append(String.valueOf(row.get(type)) + " ");
150             //默认值 3
151             if(null!=row.get(defaultValue)){
152                 sql.append(" default " +row.get(defaultValue));
153             }
154             //是否为空 2
155             if("N".equalsIgnoreCase(String.valueOf(row.get(cannull)))){
156                 sql.append(" not null");
157             }
158             if(i < datas.size() - 1){
159                 sql.append(",");
160             }
161             //是否主键 4
162             if(null != row.get(pk) && "Y".equalsIgnoreCase(String.valueOf(row.get(pk)))){
163                 //设置主键
164                 primary = "alter table "+tableName+" add constraint PK_"+tableName+"_ID primary key (" + String.valueOf(row.get(column))                          + ");";
165             }
166             //增加注释
167             comments.append("comment on column ").append(tableName).append(".").append(String.valueOf(row.get(column))).append(" is '").                        append(String.valueOf(row.get(comment))).append("';\n");
168             sql.append("\n");
169         }
170         //结束
171         sql.append(");\n");
172         //添加注释
173         sql.append(comments);
174         //添加主键
175         sql.append(primary);
176         return sql.toString();
177     }
178 }

excel示例：

转载于:https://www.cnblogs.com/baifeilong/p/3626275.html