public static Xlsx Create(string filePath)
{
FileInfo newFile = new FileInfo(filePath);
using (ExcelPackage pck = new ExcelPackage(newFile))
{
try
{
string name = Path.GetFileNameWithoutExtension(filePath);
name = name.Substring(0, 1).ToUpper() + name.Substring(1);
ExcelWorkbook workBook = pck.Workbook;
if (workBook != null)
{
Xlsx excel = new Xlsx(filePath, name);
int cnt = workBook.Worksheets.Count;
var enumerator = workBook.Worksheets.GetEnumerator();
bool isSuccess = true;
while (enumerator.MoveNext())
{
ExcelWorksheet tDS = enumerator.Current;
XlsxSheet sheet = XlsxSheet.Create(tDS, excel);
if (sheet == null)
{
isSuccess = false;
MainEntry.Log(string.Format("Sheet:{0} failed", tDS.Name));
break;
}
excel.AddSeet(sheet);
}
if (isSuccess)
{
return(excel);
}
MainEntry.Log(string.Format("ExcelWorkbook Failed, Not Parse Sheet Right: {0}", filePath));
}
else
{
MainEntry.Log(string.Format("ExcelWorkbook null, Not Parse Right: {0}", filePath));
}
return(null);
}
catch (Exception e)
{
MainEntry.Log(string.Format("ErrorInfo: {0}, StackTrace: {1}, filePath: {2}", e.Message, e.StackTrace, filePath));
}
}
return(null);
}
private static bool ExportXlsxFile(string filePath)
{
Xlsx xlsx = Xlsx.Create(filePath);
if (xlsx != null)
{
xlsx.InitializeRecord();
bool res = ExportCS(xlsx);
res &= ExportXml(xlsx);
return(res);
}
else
{
MainEntry.Log(string.Format("ExportXlsxFile Wrong: {0}", filePath));
return(false);
}
}
public static XlsxSheet Create(ExcelWorksheet tDS, Xlsx parent)
{
XlsxSheet sheet = new XlsxSheet(parent, tDS.Name);
List <string> variableNames;
List <DataTypeEnum> variableTypes;
List <DataSideEnum> variableSides;
string managerName = null;
string keys = null;
MainEntry.Log(string.Format("-------------------SheetName: {0} Begin", tDS.Name));
List <XlsxRow> datas = CleanRowAndCols(tDS, sheet, out managerName, out keys, out variableNames, out variableTypes, out variableSides);
if (datas != null)
{
sheet.Set(managerName, keys, variableNames, variableTypes, variableSides, datas);
MainEntry.Log(string.Format("-------------------SheetName: {0} End", tDS.Name));
return(sheet);
}
MainEntry.Log(string.Format("*******************SheetName: {0} failed", tDS.Name));
return(null);
}
private static List <XlsxRow> CleanRowAndCols(ExcelWorksheet tDS, XlsxSheet sheet, out string managerName, out string keys, out List <string> variableNames, out List <DataTypeEnum> variableTypes, out List <DataSideEnum> variableSides)
{
variableNames = new List <string>();
variableTypes = new List <DataTypeEnum>();
variableSides = new List <DataSideEnum>();
managerName = null;
keys = null;
ExcelRange range = tDS.Cells;
object[,] values = (object[, ])range.Value;
int rows = values.GetLength(0);
int cols = values.GetLength(1);
HashSet <int> skipCols = new HashSet <int>();
HashSet <int> skipRows = new HashSet <int>();
if (rows > 0 && cols > 0)
{
// List,Group<ColName>,Map<ColName>,Map<ColName1, ColName2>
string value = (string)values[SHEET_DATA_MANAGER, 0];
if (value == null)
{
MainEntry.Log(string.Format("****************SheetName: {0} Wrong At [0, 0] End", tDS.Name));
return(null);
}
ParseManagerType(value, ref managerName, ref keys);
if (managerName == null)
{
MainEntry.Log(string.Format("****************SheetName: {0} Wrong ManagerName {1} End", tDS.Name, value));
return(null);
}
}
for (int i = 0; i < cols; ++i)
{
if (values[VARIABLE_SIDE_ROW, i] == null)
{
MainEntry.Log(string.Format("skip null col: [{0}, {1}], maybe last col", VARIABLE_SIDE_ROW, i));
skipCols.Add(i);
continue;
}
string v = values[VARIABLE_SIDE_ROW, i].ToString().Trim().ToUpper();
DataSideEnum side = EnumUtils.StringToEnum <DataSideEnum>(v);
if (side == DataSideEnum.SKIP)
{
MainEntry.Log(string.Format("skip flag col: [{0}, {1}]", VARIABLE_SIDE_ROW, i));
skipCols.Add(i);
continue;
}
if (values[VARIABLE_NAME_ROW, i] == null)
{
// log
MainEntry.Log(string.Format("Not Define Variable Name: [{0}, {1}]", VARIABLE_NAME_ROW, i));
return(null);
}
if (values[VARIABLE_TYPE_ROW, i] == null)
{
// log
MainEntry.Log(string.Format("Not Define Variable Type: [{0}, {1}]", VARIABLE_TYPE_ROW, i));
return(null);
}
variableSides.Add(side);
v = values[VARIABLE_NAME_ROW, i].ToString().Trim();
variableNames.Add(v);
v = values[VARIABLE_TYPE_ROW, i].ToString().Trim();
try
{
DataTypeEnum dataType = EnumUtils.StringToEnum <DataTypeEnum>(v.ToUpper());
variableTypes.Add(dataType);
}
catch (Exception e)
{
MainEntry.Log(string.Format("Not Define Variable Side: [{0}, {1}] = {2}, Error: {3}", VARIABLE_TYPE_ROW, i, v, e.Message));
return(null);
}
}
for (int i = VALUE_START_ROW; i < rows; ++i)
{
if (values[i, 0] != null)
{
string v = values[i, 0].ToString().Trim();
if (v.StartsWith(SKIP_ROW))
{
MainEntry.Log(string.Format("skip flag row: [{0}, 0]", i));
skipRows.Add(i);
}
}
else
{
MainEntry.Log(string.Format("skip null row: [{0}, 0], maybe last row", i));
skipRows.Add(i);
}
}
List <XlsxRow> originalDatas = new List <XlsxRow>();
int rowIndex = 0;
for (int i = VALUE_START_ROW; i < rows; ++i)
{
if (skipRows.Contains(i))
{
continue;
}
List <string> rowData = new List <string>();
XlsxRow row = new XlsxRow(rowIndex++, rowData, sheet);
originalDatas.Add(row);
for (int j = 0; j < cols; ++j)
{
if (skipCols.Contains(j))
{
continue;
}
rowData.Add(values[i, j] != null ? values[i, j].ToString() : null);
}
}
return(originalDatas);
}