예제 #1
0
        public List <ExcelDataModel> Go()
        {
            var r = new List <ExcelDataModel>();

            if (ExcelPath == "")
            {
                return(r);
            }

            if (WorkSheetToRead < 1)
            {
                return(r);
            }

            if (!File.Exists(ExcelPath))
            {
                return(r);
            }

            using (var pck = new OfficeOpenXml.ExcelPackage())
            {
                using (var stream = File.OpenRead(ExcelPath))
                {
                    pck.Load(stream);
                }
                var ws = pck.Workbook.Worksheets.First(); //read the first workbook

                int totalRows = ws.Dimension.End.Row;
                int totalCols = ws.Dimension.End.Column;

                int rowStart = 1;
                if (IgnoreHeader)
                {
                    rowStart     = 2;
                    IgnoreHeader = false;
                }

                try
                {
                    for (int i = rowStart; i <= totalRows; i++)
                    {
                        var excelModel = new ExcelDataModel();
                        for (int j = 1; j <= totalCols; j++)
                        {
                            var v = "";
                            if (ws.Cells[i, j].Value != null)
                            {
                                v = ws.Cells[i, j].Value.ToString();
                            }

                            if (j == 6)
                            {
                                if (v.Length >= 10)
                                {
                                    v = v.Substring(0, 10);
                                }

                                ProcessExcelValue.Go(j, v, excelModel);
                            }
                            else
                            {
                                ProcessExcelValue.Go(j, v, excelModel);
                            }

                            switch (j) //Excel columns should be known
                            {
                            case 1:
                                excelModel.Id = Convert.ToInt32(v);
                                break;

                            case 2:
                                excelModel.Surname = v;
                                break;

                            case 3:
                                excelModel.FirstName = v;
                                break;

                            case 4:
                                excelModel.Cell = Convert.ToInt64(FormatCellNumber.ToInternational(v));
                                break;

                            case 5:
                                excelModel.Email = v;
                                break;
                            }
                        }
                        r.Add(excelModel);
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine(ex.Message);
                }
            }

            return(r);
        }
예제 #2
0
        public List <ExcelDataModel> Go()
        {
            var r = new List <ExcelDataModel>();

            if (ExcelPath == "")
            {
                return(r);
            }

            if (WorkSheetToRead < 1)
            {
                return(r);
            }

            if (!File.Exists(ExcelPath))
            {
                return(r);
            }

            var app = new Application();

            var wb = app.Workbooks.Open(
                ExcelPath, 0, true, 5, "", "", true,
                XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);

            var ws = (Worksheet)wb.Worksheets.get_Item(WorkSheetToRead);
            var range = ws.UsedRange;
            int rowCount, cellCount;

            for (rowCount = 1; rowCount <= range.Rows.Count; rowCount++)
            {
                if (IgnoreHeader)
                {
                    IgnoreHeader = false;
                    continue;
                }

                var excelModel = new ExcelDataModel();
                for (cellCount = 1; cellCount <= range.Columns.Count; cellCount++)
                {
                    try
                    {
                        var v = (range.Cells[rowCount, cellCount] as Range).Value2;
                        if (cellCount == 6)
                        {
                            //format date from 99999 to dd/MM/yyyy
                            var x = DateTime.FromOADate(v).ToShortDateString();
                            ProcessExcelValue.Go(cellCount, x, excelModel);
                        }
                        else
                        {
                            ProcessExcelValue.Go(cellCount, v, excelModel);
                        }
                    }
                    catch (Exception ex)
                    {
                        //TODO - log
                        Console.WriteLine(ex.Message);
                    }
                }
                r.Add(excelModel);
            }

            wb.Close(false, null, null); //no need to save the document as we just reading it
            app.Quit();

            Marshal.ReleaseComObject(ws);
            Marshal.ReleaseComObject(wb);
            Marshal.ReleaseComObject(app);

            return(r);
        }