private void BtnGenerateSQL_Click(object sender, EventArgs e)
{
string excelPath = this.TxtExcel.Text.Trim();
int header = 3;
// 加载Excel文件
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
// The result of each spreadsheet will be created in the result.Tables
excelReader.IsFirstRowAsColumnNames = true;
DataSet book = excelReader.AsDataSet();
// 数据检测
if (book.Tables.Count < 1)
{
throw new Exception("Excel文件中没有找到Sheet: " + excelPath);
}
foreach (DataTable sheet in book.Tables)
{
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet中没有数据: " + excelPath);
}
String SQLPath = Path.Combine(TxtSQLPath.Text.Trim(), sheet.TableName + ".sql");
SQLExporter exporterForSQL = new SQLExporter(sheet, header);
exporterForSQL.SaveToFile(SQLPath, new UTF8Encoding(false), this.ChkGenerateCreateTableScript.Checked);
}
}
MessageBox.Show("Generate completed.", "Excel2Json", MessageBoxButtons.OK);
}
private static void Export2Sql(string excelPath, int header, string encoding, string sqlPath)
{
if (sqlPath == null || sqlPath.Length <= 0)
{
return;
}
DataTable dt = ReadExcel(excelPath);
Encoding en = GetEncoding(encoding);
//-- 导出SQL文件
sqlPath = FormatPath(sqlPath);
SQLExporter exporter = new SQLExporter(dt, header);
exporter.SaveToFile(sqlPath, en);
}
/// <summary>
/// 根据命令行参数，执行Excel数据导出工作
/// </summary>
/// <param name="options">命令行参数</param>
private static void Run(Options options)
{
string excelPath = options.ExcelPath;
int header = options.HeaderRows;
// 加载Excel文件
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
// The result of each spreadsheet will be created in the result.Tables
excelReader.IsFirstRowAsColumnNames = true;
DataSet book = excelReader.AsDataSet();
// 数据检测
if (book.Tables.Count < 1)
{
throw new Exception("Excel文件中没有找到Sheet: " + excelPath);
}
// 取得数据
DataTable sheet = book.Tables[0];
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet中没有数据: " + excelPath);
}
//-- 确定编码
Encoding cd = new UTF8Encoding(false);
if (options.Encoding != "utf8-nobom")
{
foreach (EncodingInfo ei in Encoding.GetEncodings())
{
Encoding e = ei.GetEncoding();
if (e.EncodingName == options.Encoding)
{
cd = e;
break;
}
}
}
//-- 导出JSON文件
if (options.JsonPath != null && options.JsonPath.Length > 0)
{
JsonExporter exporter = new JsonExporter(sheet, header, options.Lowcase);
exporter.SaveToFile(options.JsonPath, cd, options.ExportArray);
}
//-- 导出SQL文件
if (options.SQLPath != null && options.SQLPath.Length > 0)
{
SQLExporter exporter = new SQLExporter(sheet, header);
exporter.SaveToFile(options.SQLPath, cd);
}
//-- 生成C#定义文件
if (options.CSharpPath != null && options.CSharpPath.Length > 0)
{
string excelName = Path.GetFileName(excelPath);
CSDefineGenerator exporter = new CSDefineGenerator(sheet);
exporter.ClassComment = string.Format("// Generate From {0}", excelName);
exporter.SaveToFile(options.CSharpPath, cd);
}
}
}
/// <summary>
/// 根据命令行参数，执行Excel数据导出工作
/// </summary>
/// <param name="options">命令行参数</param>
private static void Run(Options options)
{
string excelPath = options.ExcelPath;
int header = options.HeaderRows;
// 加载Excel文件
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
// Reading from a OpenXml Excel file (2007 format; *.xlsx)
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
// The result of each spreadsheet will be created in the result.Tables
excelReader.IsFirstRowAsColumnNames = true;
DataSet book = excelReader.AsDataSet();
// 数据检测
if (book.Tables.Count < 1)
{
throw new Exception("Excel文件中没有找到Sheet: " + excelPath);
}
// 取得数据
DataTable sheet = book.Tables[0];
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet中没有数据: " + excelPath);
}
//-- 确定编码
Encoding cd = new UTF8Encoding(false);
if (options.Encoding != "utf8-nobom")
{
foreach (EncodingInfo ei in Encoding.GetEncodings())
{
Encoding e = ei.GetEncoding();
if (e.EncodingName == options.Encoding)
{
cd = e;
break;
}
}
}
//-- 导出JSON文件
if (options.JsonPath != null && options.JsonPath.Length > 0)
{
JsonExporter exporter = new JsonExporter(sheet, header, options.Lowcase);
exporter.SaveToFile(options.JsonPath, cd);
}
//-- 导出SQL文件
if (options.SQLPath != null && options.SQLPath.Length > 0)
{
SQLExporter exporter = new SQLExporter(sheet, header);
exporter.SaveToFile(options.SQLPath, cd);
}
//-- 生成C#定义文件
if (options.CSharpPath != null && options.CSharpPath.Length > 0)
{
string excelName = Path.GetFileName(excelPath);
CSDefineGenerator exporter = new CSDefineGenerator(sheet);
exporter.ClassComment = string.Format("// Generate From {0}", excelName);
exporter.SaveToFile(options.CSharpPath, cd);
}
}
}