Excel示例
支持多表生成,多加几个sheet即可
- 生成效果
直接放码
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
# 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;
}
}
}