Пример #1
0
        //Read excel data by looping each record
        public static List <WorkItemWithIteration> ReadDataFromExcel(string excelFilePath)
        {
            List <WorkItemWithIteration> workItemIteration = new List <WorkItemWithIteration>();

            try
            {
                Microsoft.Office.Interop.Excel.Application appExl   = new Microsoft.Office.Interop.Excel.Application();
                Microsoft.Office.Interop.Excel.Workbook    workbook = appExl.Workbooks.Open(excelFilePath);
                Microsoft.Office.Interop.Excel._Worksheet  NwSheet  = workbook.Sheets[1];
                Microsoft.Office.Interop.Excel.Range       ShtRange = NwSheet.UsedRange;

                int rowCount = ShtRange.Rows.Count;
                int colCount = ShtRange.Columns.Count;

                int       rowindex = 0;
                DataTable dt       = new DataTable();
                for (int i = 5; i <= rowCount; i++)
                {
                    if (ShtRange.Cells[i, 1] != null && ShtRange.Cells[i, 1].Value2 != null)
                    {
                        if (ShtRange.Cells[i, 1].Value2.ToString() == "Row Labels")
                        {
                            rowindex = i;
                        }
                    }
                    if (i > rowindex)
                    {
                        for (int j = 2; j <= colCount - 1; j++)
                        {
                            if (ShtRange.Cells[i, j] != null && ShtRange.Cells[i, j].Value2 != null)
                            {
                                WorkItemWithIteration wrkitem = new WorkItemWithIteration();
                                if (ShtRange.Cells[i, 1] != null && ShtRange.Cells[i, 1].Value2 != null)
                                {
                                    wrkitem.UserName = ShtRange.Cells[i, 1].Value2.ToString();
                                }

                                if (ShtRange.Cells[rowindex, 1] != null && ShtRange.Cells[rowindex, 1].Value2 != null)
                                {
                                    wrkitem.IterationPath = ShtRange.Cells[rowindex, j].Value2.ToString();
                                }

                                if (ShtRange.Cells[i, j] != null && ShtRange.Cells[i, j].Value2 != null)
                                {
                                    wrkitem.Value = Convert.ToDouble(ShtRange.Cells[i, j].Value2.ToString() == "" ? 0 : ShtRange.Cells[i, j].Value2.ToString());
                                    workItemIteration.Add(wrkitem);
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteFileToDisk("ReadDataFromExcel", ex.Message + Environment.NewLine + ex.StackTrace);
            }
            return(workItemIteration);
        }
Пример #2
0
        //Read data by looping DataTable record
        public static List <WorkItemWithIteration> ReadDataFromDataTable(DataTable dt)
        {
            List <WorkItemWithIteration> workItemIteration = new List <WorkItemWithIteration>();

            try
            {
                int rowindex = 0;
                for (int i = 5; i < dt.Rows.Count; i++)
                {
                    if (dt.Rows[i][1].ToString() == "Row Labels")
                    {
                        rowindex = i;
                    }
                    if (i > rowindex)
                    {
                        for (int j = 0; j < dt.Columns.Count - 1; j++)
                        {
                            WorkItemWithIteration wrkitem = new WorkItemWithIteration();
                            if (dt.Rows[i][0] != null)
                            {
                                wrkitem.UserName = dt.Rows[i][0].ToString();
                            }

                            if (dt.Rows[rowindex][0] != null)
                            {
                                wrkitem.IterationPath = dt.Rows[rowindex][j].ToString();
                            }

                            if (dt.Rows[i][j] != null)
                            {
                                wrkitem.Value = Convert.ToDouble(dt.Rows[i][j].ToString() == "" ? 0 : dt.Rows[i][j]);
                                workItemIteration.Add(wrkitem);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                WriteFileToDisk("ReadDataFromDataTable", ex.Message + Environment.NewLine + ex.StackTrace);
            }
            return(workItemIteration);
        }