示例#1
0
        private static List <ModelTypeTempSheetModel> ReadExcel(UploadFileImportModel model)
        {
            ModelTypeUploadModel  modelTypeUploadModel = new ModelTypeUploadModel();
            List <List <string> > rowValues            = new List <List <string> >();
            string fileName = model.FileName;
            List <ModelTypeTempSheetModel> sheetModels = new List <ModelTypeTempSheetModel>();
            ModelTypeTempSheetModel        sheetModel;

            using (SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Open(fileName, false))
            {
                WorkbookPart workbookPart = spreadsheetDocument.WorkbookPart;
                int          sheetCount   = 0;
                foreach (Sheet sheet in workbookPart.Workbook.Sheets)
                {
                    sheetModel = new ModelTypeTempSheetModel();
                    WorksheetPart worksheetPart = (WorksheetPart)(workbookPart.GetPartById(sheet.Id));
                    Worksheet     worksheet     = workbookPart.WorksheetParts.First().Worksheet;
                    SheetData     sheetData     = worksheet.GetFirstChild <SheetData>();

                    List <Row>    rows       = sheetData.Descendants <Row>().ToList();
                    List <string> preRow     = new List <string>();
                    List <string> cellValues = new List <string>();

                    //Find index header
                    int indexMainEquipStart        = 0;
                    int indexPNoStart              = 0;
                    int indexTypeStart             = 0;
                    int indexVinStart              = 0;
                    int indexEngineSerialNoStart   = 0;
                    int indexErrorDescriptionStart = 0;
                    int inedexRowOfHeader_Start    = 6;
                    int indexRowOfHeader_End       = 8;

                    foreach (Cell cell in rows.ElementAt(5).Descendants <Cell>())
                    {
                        if (new[] { "A6", "B6", "C6", "E6" }.Contains(cell.CellReference.Value))
                        {
                            string value = GetCellValue(workbookPart, sheet, cell.CellReference);
                            switch (cell.CellReference.Value)
                            {
                            case "A6":
                                sheetModel.YM = value;
                                break;

                            case "B6":
                                sheetModel.Model = value;
                                break;

                            case "C6":
                                sheetModel.Door = value;
                                break;

                            case "E6":
                                sheetModel.Plant = value;
                                break;
                            }
                        }
                    }

                    for (var i = inedexRowOfHeader_Start; i < indexRowOfHeader_End; i++)
                    {
                        foreach (Cell cell in rows.ElementAt(i).Descendants <Cell>())
                        {
                            if (GetCellValue(workbookPart, sheet, cell.CellReference) == "MAIN EQUIPMENT")
                            {
                                indexMainEquipStart = GetColumnIndex(cell.CellReference);
                            }
                            if (GetCellValue(workbookPart, sheet, cell.CellReference) == "P.No.")
                            {
                                indexPNoStart = GetColumnIndex(cell.CellReference);
                            }
                            if (GetCellValue(workbookPart, sheet, cell.CellReference) == "TYPE")
                            {
                                indexTypeStart = GetColumnIndex(cell.CellReference);
                            }
                            if (GetCellValue(workbookPart, sheet, cell.CellReference) == "VIN")
                            {
                                indexVinStart = GetColumnIndex(cell.CellReference);
                            }
                            if (GetCellValue(workbookPart, sheet, cell.CellReference) == "ENGINE SERIAL No.")
                            {
                                indexEngineSerialNoStart = GetColumnIndex(cell.CellReference);
                            }
                            if (GetCellValue(workbookPart, sheet, cell.CellReference) == "Error Description")
                            {
                                indexErrorDescriptionStart = GetColumnIndex(cell.CellReference);
                            }
                        }
                    }

                    ModelTypeTempRowModel              modelTypeTempRowModel;
                    ModelTypeTempEngineModel           engineModel;
                    List <ModelTypeTempEquipmentModel> equipmentModels;
                    ModelTypeTempEquipmentModel        equipmentModel;
                    List <ModelTypeTempTypeModel>      typeModels;
                    ModelTypeTempTypeModel             typeModel;

                    for (var i = 9; i < rows.Count; i++)
                    {
                        modelTypeTempRowModel = new ModelTypeTempRowModel();
                        equipmentModels       = new List <ModelTypeTempEquipmentModel>();
                        typeModel             = new ModelTypeTempTypeModel();
                        typeModels            = new List <ModelTypeTempTypeModel>();
                        cellValues            = new List <string>();
                        engineModel           = new ModelTypeTempEngineModel();

                        modelTypeTempRowModel.RowNo = i + 1;
                        foreach (Cell cell in rows.ElementAt(i).Cast <Cell>())
                        {
                            string currentColumn    = GetColumnName(cell.CellReference);
                            int    currentIndex     = GetColumnIndex(cell.CellReference);
                            string currentCellValue = GetCellValue(workbookPart, sheet, cell.CellReference);
                            int    sequence         = 1;

                            #region Engine
                            if (new[] { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J" }.Contains(GetColumnName(cell.CellReference)))
                            {
                                #region  Replace Value
                                if (cell.CellReference == "A" + (i + 1))
                                {
                                    if (string.IsNullOrEmpty(currentCellValue))
                                    {
                                        currentCellValue = preRow[0];
                                    }
                                }
                                if (cell.CellReference == "B" + (i + 1))
                                {
                                    if (string.IsNullOrEmpty(currentCellValue))
                                    {
                                        currentCellValue = preRow[1];
                                    }
                                }
                                if (cell.CellReference == "C" + (i + 1))
                                {
                                    if (string.IsNullOrEmpty(currentCellValue))
                                    {
                                        currentCellValue = preRow[2];
                                    }
                                }
                                if (cell.CellReference == "D" + (i + 1))
                                {
                                    if (string.IsNullOrEmpty(currentCellValue))
                                    {
                                        currentCellValue = preRow[3];
                                    }
                                }
                                if (cell.CellReference == "E" + (i + 1))
                                {
                                    if (string.IsNullOrEmpty(currentCellValue))
                                    {
                                        currentCellValue = preRow[4];
                                    }
                                }
                                #endregion

                                switch (GetColumnName(cell.CellReference))
                                {
                                case "A":
                                    engineModel.SS = currentCellValue;
                                    break;

                                case "B":
                                    engineModel.DISP = currentCellValue;
                                    break;

                                case "C":
                                    engineModel.COMCARB = currentCellValue;
                                    break;

                                case "D":
                                    engineModel.Grade = currentCellValue;
                                    break;

                                case "E":
                                    engineModel.Mis = currentCellValue;
                                    break;

                                case "F":
                                    engineModel.ModelCode01 = currentCellValue;
                                    break;

                                case "G":
                                    engineModel.ModelCode02 = currentCellValue;
                                    break;

                                case "H":
                                    engineModel.ModelCode03 = currentCellValue;
                                    break;

                                case "I":
                                    engineModel.ModelCode04 = currentCellValue;
                                    break;

                                case "J":
                                    engineModel.ModelCode05 = currentCellValue;
                                    break;
                                }
                            }
                            #endregion

                            #region MAIN EQUIPMENT
                            string columnEndGetEquipment = GetColumnName(GetMergeCellEndPosition(workbookPart, sheet, "K7"));
                            int    indexMainEquipEnd     = GetColumnIndex(columnEndGetEquipment);

                            if (currentIndex >= indexMainEquipStart && currentIndex <= indexMainEquipEnd) // Start K Column
                            {
                                equipmentModel = new ModelTypeTempEquipmentModel
                                {
                                    EquipmentName  = GetCellValue(workbookPart, sheet, currentColumn + 9),
                                    EquipmentValue = currentCellValue,
                                    Sequence       = sequence
                                };

                                sequence++;
                                equipmentModels.Add(equipmentModel);
                            }
                            #endregion

                            #region PNo
                            if (currentIndex == indexPNoStart)
                            {
                                modelTypeTempRowModel.PNo = currentCellValue;
                            }
                            #endregion

                            #region TYPE
                            if (currentIndex >= indexTypeStart && currentIndex <= indexVinStart - 1)
                            {
                                typeModel = new ModelTypeTempTypeModel
                                {
                                    ModelType = GetCellValue(workbookPart, sheet, currentColumn + 9),
                                    ModelCode = currentCellValue,
                                    Sequence  = sequence
                                };
                                typeModels.Add(typeModel);
                            }
                            #endregion

                            #region VIN
                            if (currentIndex == indexVinStart)
                            {
                                modelTypeTempRowModel.VIN = currentCellValue;
                            }
                            #endregion

                            cellValues.Add(currentCellValue);
                        }
                        // End Cell
                        preRow = cellValues;
                        rowValues.Add(cellValues);
                        modelTypeTempRowModel.modelTypeTempEngines.Add(engineModel);
                        modelTypeTempRowModel.modelTypeTempEquipmentModels.AddRange(equipmentModels);
                        modelTypeTempRowModel.modelTypeTempTypeModels.AddRange(typeModels);
                        sheetModel.modelTypeTempRowModels.Add(modelTypeTempRowModel);
                    }
                    //End  Row
                    sheetModel.SheetNo = ++sheetCount;
                    sheetModels.Add(sheetModel);
                }
            }
            return(sheetModels);
        }