Пример #1
0
        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);
        }
Пример #2
0
        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++;
            }
        }