private bool SetMovementItem(int rowIndex, MovementItem evi, string cellText)
{
switch (rowIndex)
{
case 0:
evi.ExternalCode = cellText.Trim();
break;
case 1:
evi.SubCode = cellText.Trim();
break;
case 2:
evi.ShortName = cellText.Trim();
break;
case 3:
case 4:
case 5:
case 6:
case 7:
case 8:
int i = rowIndex - 3;
bool k = new string[] { "1", "Y", "A", "P" }.ToList().IndexOf(cellText) != -1;
bool k2 = new string[] { "0", "N", "B" }.ToList().IndexOf(cellText) != -1;
if (k || k2)
{
if (k)
{
evi.Limits[i] = true;
}
else
{
evi.Limits[i] = false;
}
}
break;
case 9:
evi.Name = cellText.Trim();
break;
default:
i = rowIndex - 10;
k = new string[] { "1", "Y", "A", "P" }.ToList().IndexOf(cellText) != -1;
k2 = new string[] { "0", "N", "B" }.ToList().IndexOf(cellText) != -1;
if (!k && !k2)
{
return(false);
}
if (k)
{
evi.Items[i].State = true;
}
else
{
evi.Items[i].State = false;
}
break;
}
return(true);
}
private IEnumerable <MovementItem> GetTableFromExcel(WorkbookPart workbookPart, SheetData sheetData, SharedStringTable sst, int operation, int type)
{
var m = new List <ItemValue>();
var rows = sheetData.Descendants <Row>().Skip(10);
int columnIndex = 0;
foreach (var r in rows)
{
var evi = new ItemValue();
columnIndex = 0;
foreach (Cell cell in r.Descendants <Cell>())
{
string cellText = string.Empty;
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
cellColumnIndex--; //zero based index
while (columnIndex < cellColumnIndex)
{
if (!SetItems(columnIndex, evi, cellText))
{
break;
}
columnIndex++;
}
columnIndex++;
var cellValue = cell.CellValue;
var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
if (cell.DataType?.Value == CellValues.SharedString)
{
text = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Convert.ToInt32(cell.CellValue.Text)).InnerText;
}
cellText = (text ?? string.Empty).Trim();
if (cell.StyleIndex != null)
{
var cellFormat = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[int.Parse(cell.StyleIndex.InnerText)] as CellFormat;
if (cellFormat != null)
{
var dateFormat = GetDateTimeFormat(cellFormat.NumberFormatId);
if (!string.IsNullOrEmpty(dateFormat))
{
if (double.TryParse(cellText, out var cellDouble))
{
var theDate = DateTime.FromOADate(cellDouble);
cellText = theDate.ToString("dd.MM.yyyy");
}
}
}
}
if (!SetItems(cellColumnIndex, evi, cellText) || cellColumnIndex == 1)
{
break;
}
}
if (!string.IsNullOrWhiteSpace(evi.Prefix))
{
m.Add(evi);
}
}
if (m.Count == 0)
{
yield break;
}
columnIndex = 2;
while (true)
{
int rowIndex = 0;
var ebi = new MovementItem()
{
Items = m.Select(p => new ItemValue()
{
Prefix = p.Prefix, Name = p.Name
}).ToArray(), Operation = operation, Type = type
};
foreach (var r in sheetData.Descendants <Row>())
{
foreach (Cell cell in r.Descendants <Cell>().Skip(columnIndex))
{
string cellText = string.Empty;
int cellColumnIndex = (int)GetColumnIndexFromName(GetColumnName(cell.CellReference));
cellColumnIndex--; //zero based index
if (columnIndex < cellColumnIndex)
{
SetMovementItem(rowIndex, ebi, cellText);
//columnIndex++;
}
//columnIndex++;
var cellValue = cell.CellValue;
var text = (cellValue == null) ? cell.InnerText : cellValue.Text;
if (cell.DataType?.Value == CellValues.SharedString)
{
text = workbookPart.SharedStringTablePart.SharedStringTable.Elements <SharedStringItem>().ElementAt(Convert.ToInt32(cell.CellValue.Text)).InnerText;
}
cellText = (text ?? string.Empty).Trim();
if (cell.StyleIndex != null)
{
var cellFormat = workbookPart.WorkbookStylesPart.Stylesheet.CellFormats.ChildElements[int.Parse(cell.StyleIndex.InnerText)] as CellFormat;
if (cellFormat != null)
{
var dateFormat = GetDateTimeFormat(cellFormat.NumberFormatId);
if (!string.IsNullOrEmpty(dateFormat))
{
if (double.TryParse(cellText, out var cellDouble))
{
var theDate = DateTime.FromOADate(cellDouble);
cellText = theDate.ToString("dd.MM.yyyy");
}
}
}
}
SetMovementItem(rowIndex, ebi, cellText);
break;
}
rowIndex++;
if (rowIndex > 1 && string.IsNullOrWhiteSpace(ebi.ExternalCode) && string.IsNullOrWhiteSpace(ebi.SubCode))
{
yield break;
}
}
//if (!string.IsNullOrWhiteSpace(evi.Prefix)) yield return ebi;
yield return(ebi);
columnIndex++;
}
}