public void ReadExcel(string fileName, bool ignoreFirstLine)
{
int ignoreLine = 0;
if (ignoreFirstLine)
{
ignoreLine = 1;
}
using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(fileName, true))
{
//Get workbookpart
WorkbookPart workbookPart = excelDocument.WorkbookPart;
//then access to the worksheet part
IEnumerable <WorksheetPart> worksheetPart = workbookPart.WorksheetParts;
foreach (WorksheetPart WSP in worksheetPart)
{
//find sheet data
IEnumerable <SheetData> sheetData = WSP.Worksheet.Elements <SheetData>();
// Iterate through every sheet inside Excel sheet
foreach (SheetData SD in sheetData)
{
IEnumerable <Row> row = SD.Elements <Row>(); // Get the row IEnumerator
foreach (Row r in SD.Elements <Row>())
{
if (r.RowIndex > ignoreLine)
{
NAVObject navObj = CreateNAVObject(workbookPart, r);
if (navObj.FieldName != null)
{
UpdateObjectData(navObj);
UpdateReferences(navObj);
}
}
}
}
}
}
}
public static IEnumerable <TablesModel> ReadModelList(string filepath)
{
using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(filepath, false))
{
WorkbookPart wbPart = spreadsheetDocument.WorkbookPart;
var theSheets = wbPart.Workbook;
var tableData = new List <TablesModel> {
};
var _sheet = new Dictionary <string, string>(); // sheet id, sheet name
foreach (Sheet item in theSheets.Sheets)
{
System.Console.WriteLine($"Sheet Name: {item.Name}");
IEnumerable <Sheet> _sheets =
wbPart.Workbook.GetFirstChild <Sheets>().
Elements <Sheet>().Where(s => s.Name == item.Name);
if (_sheets?.Count() == 0)
{
//return null;
}
string relationshipId = _sheets?.First().Id.Value;
WorksheetPart parts = (WorksheetPart)wbPart.GetPartById(relationshipId);
var _parts = new List <WorksheetPart>();
_parts.Add(parts);
foreach (WorksheetPart WSP in _parts)
{
//find sheet data
IEnumerable <SheetData> sheetData = WSP.Worksheet.Elements <SheetData>();
// Iterate through every sheet inside Excel sheet
foreach (SheetData SD in sheetData)
{
IEnumerable <Row> row = SD.Elements <Row>(); // Get the row IEnumerator
var rowData = row.Where(o => !string.IsNullOrWhiteSpace(o.InnerText));
foreach (var r in rowData)
{
var _cell = r.Descendants <Cell>()
.Select(o => GetCellText(o, wbPart.SharedStringTablePart.SharedStringTable)).ToList();
var t = new TablesModel
{
Key = (_cell[1] != null) ? _cell[1].Trim() : "",
Column = (_cell[2] != null) ? _cell[2].Trim() : "",
DataType = (_cell[3] != null) ? _cell[3].Trim() : "",
IsNull = (_cell[4] != null) ? _cell[4].Trim() : "",
ColumnName = (_cell[5] != null) ? _cell[5].Trim() : "",
DefaultValue = (_cell[6] != null) ? _cell[6].Trim() : "",
Comment = (_cell[7] != null) ? _cell[7].Trim() : "",
};
tableData.Add(t);
//Console.WriteLine(t);
//Console.WriteLine(String.Join("\t", _cell));
}
}
}
}
return(tableData);
}
}