/// <summary>
/// 加载Excel文件
/// </summary>
/// <param name="options">导入设置</param>
public void loadExcel(Program.Options options)
{
mOptions = options;
string excelPath = options.ExcelPath;
string excelName = Path.GetFileNameWithoutExtension(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 file is empty: " + excelPath);
}
// 取得数据
DataTable sheet = book.Tables[0];
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet is empty: " + excelPath);
}
//-- 确定编码
Encoding cd = new UTF8Encoding(false);
if (options.Encoding != "utf8-nobom")
{
foreach (EncodingInfo ei in Encoding.GetEncodings())
{
Encoding e = ei.GetEncoding();
if (e.HeaderName == options.Encoding)
{
cd = e;
break;
}
}
}
mEncoding = cd;
//-- 导出JSON
mJson = new JsonExporter(sheet, header, options.Lowcase, options.ExportArray, options.EsacapeNonAscii);
//-- 导出SQL
mSQL = new SQLExporter(excelName, sheet, header);
//-- 生成C#定义代码
mCSharp = new CSDefineGenerator(excelName, sheet);
//-- 生成C#定义代码
mTS = new TSDefineGenerator(excelName, sheet);
}
}
/// <summary>
/// 加载Excel文件
/// </summary>
/// <param name="options">导入设置</param>
public void loadExcel(Program.Options options)
{
mOptions = options;
//-- Excel File
string excelPath = options.ExcelPath;
string excelName = Path.GetFileNameWithoutExtension(excelPath);
//-- Header
int header = options.HeaderRows;
//-- Encoding
Encoding cd = new UTF8Encoding(false);
if (options.Encoding != "utf8-nobom")
{
foreach (EncodingInfo ei in Encoding.GetEncodings())
{
Encoding e = ei.GetEncoding();
if (e.HeaderName == options.Encoding)
{
cd = e;
break;
}
}
}
mEncoding = cd;
//-- Load Excel
ExcelLoader excel = new ExcelLoader(excelPath, header);
//-- C# 结构体定义
mCSharp = new CSDefineGenerator(excelPath, excel, options.ExcludePrefix);
//-- 导出JSON
mJson = new JsonExporter(excel, options.Lowcase, options.ExportArray, options.DateFormat, options.ForceSheetName, header, options.ExcludePrefix, options.CellJson);
}
static void Export(Options options)
{
string excelPath = options.ExcelPath;
int header = options.HeaderRows;
Console.WriteLine(string.Format("export {0}", excelPath));
// 加载Excel文件
using (FileStream excelFile = File.Open(excelPath, FileMode.Open, FileAccess.Read))
{
IExcelDataReader excelReader = null;
string extension = Path.GetExtension(excelPath);
if (string.IsNullOrEmpty(extension) == false)
{
extension = extension.ToLower();
}
if (extension == ".xlsx")
{
excelReader = ExcelReaderFactory.CreateOpenXmlReader(excelFile);
}
else
{
throw new Exception("仅支持xlsx格式。");
}
string fileName = Path.GetFileNameWithoutExtension(excelPath);
// 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");
}
// 取得数据
DataTable sheet = book.Tables[0];
if (sheet.Rows.Count <= 0)
{
throw new Exception("Excel Sheet中没有数据");
}
//-- 确定编码
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.json || options.xml)
{
JsonExporter exporter = new JsonExporter(sheet, header, options.Lowcase);
if (options.json)
{
exporter.SaveToJsonFile(string.Format("{0}/{1}.json", options.WorkOut, fileName), cd, options.ExportArray);
}
if (options.xml)
{
exporter.SaveToXmlFile(string.Format("{0}/{1}.xml", options.WorkOut, fileName), cd, options.ExportArray);
}
}
//-- 导出SQL文件
if (options.sqlite || options.mysql)
{
SQLExporter exporter = new SQLExporter(sheet, header);
if (string.IsNullOrEmpty(options.SQLPath))
{
options.SQLPath = string.Format("{0}/{1}", options.WorkOut, fileName);
}
exporter.SaveToFile(options, cd, fileName);
}
//-- 生成C#定义文件
if (options.csharp)
{
string excelName = Path.GetFileName(excelPath);
if (string.IsNullOrEmpty(options.SQLPath))
{
options.XmlPath = string.Format("{0}/{1}.cs", options.WorkOut, fileName);
}
CSDefineGenerator exporter = new CSDefineGenerator(sheet);
exporter.ClassComment = string.Format("// Generate From {0}", excelName);
exporter.SaveToFile(options.CSharpPath, cd);
}
if (options.csv)
{
if (string.IsNullOrEmpty(options.CSVPath))
{
options.CSVPath = string.Format("{0}/{1}.csv", options.WorkOut, fileName);
}
CSVExporter.SaveToCSVFile(sheet, options.CSVPath);
}
}
}
