C# 根据EXCEL自动生成oracle建表语句[通俗易懂]

大家好，欢迎来到IT知识分享网。

1. Excel示例
   
2. 支持多表生成，多加几个sheet即可
3. 生成效果
   
   直接放码

using Microsoft.Office.Interop.Excel;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;


namespace SQLCreate
{ 
   
    internal class Program
    { 
   
        public class TableModel
        { 
   
            
            public string tableName;
            public string tableCommit;
            public List<string> chName;
            public List<string> enName;
            public List<string> type;
            public List<string> commit;

            public TableModel()
            { 
   
                chName = new List<string>();
                enName = new List<string>();                
                type = new List<string>();
                commit = new List<string>();
            }
        }

        

        static void Main(string[] args)
        { 
   
            string excelPath = "./test.xlsx";

            List<TableModel> tables = ReadFromExcel(excelPath);

            OutputTxt(tables);
        }

        public static void OutputTxt(List<TableModel> tables)
        { 
   
            
            foreach (var item in tables)
            { 
   
                string str = $"CREATE table { 
     item.tableName}\n";
                str += "(\n";

                for (int i = 0; i < item.enName.Count; i++)
                { 
   
                    str += $"{ 
     item.enName[i]} { 
     item.type[i]},\n";
                }
                str += ");\n";

                str += $"comment on table { 
     item.tableName} is '{ 
     item.tableCommit}';\n";

                for (int i = 0; i < item.enName.Count; i++)
                { 
   
                    str += $"comment on table { 
     item.tableName}.{ 
     item.enName[i]} is '{ 
     item.commit[i]}';\n";
                }

                OutputFile($"./result_{ 
     DateTime.Now.ToString("yyyymm")}.txt", str);
            }
        }

        public static void OutputFile(string strFilePath, string strContent)
        { 
   
            StreamWriter swOut = new StreamWriter(strFilePath, true, Encoding.Default);
            swOut.WriteLine(strContent);
            swOut.Flush();
            swOut.Close();
        }



        public static List<TableModel> ReadFromExcel(string filePath)
        { 
   
            #region xls
            //HSSFWorkbook = new HSSFWorkbook(fs);
            # endregion

            FileStream fs = File.OpenRead(filePath);

            XSSFWorkbook wk = new XSSFWorkbook(fs);

            fs.Close();

            List<TableModel> tables = new List<TableModel>();

            for (int i = 0; i < wk.NumberOfSheets; i++)
            { 
   
                TableModel table = new TableModel();
                ISheet sheet = wk.GetSheetAt(i);

                string[] title = sheet.GetRow(0).GetCell(0).ToString().Split(' ');

                table.tableName = title[1];
                table.tableCommit = title[0];

                for (int j = 2; j <= sheet.LastRowNum; j++)
                { 
   
                    IRow row = sheet.GetRow(j);  
                    if (row != null)
                    { 
   
                        table.chName.Add(row.GetCell(0).ToString());
                        table.enName.Add(row.GetCell(1).ToString());
                        table.type.Add(row.GetCell(2).ToString());
                        table.commit.Add(row.GetCell(3).ToString());
                    }
                }
                tables.Add(table);
            }
            return tables;
        }
    }
}