예제 #1
0
        private void button2_Click(object sender, EventArgs e)
        {
            IXLWorkbook  wb = new XLWorkbook();                  //Create workbook used closedXML
            IXLWorksheet ws = wb.Worksheets.Add("Sample Sheet"); //In workbook create worksheet and give name

            //To delete worksheet ---> wb.Worksheet("Sample Sheet").Delete();


            //Give headers using textbox inputs

            ws.Cell(1, 1).Value = box_0101.Text;
            ws.Cell(1, 2).Value = box_0102.Text;
            ws.Cell(1, 3).Value = box_0103.Text;
            ws.Cell(1, 4).Value = box_0104.Text;
            ws.Cell(1, 5).Value = box_0105.Text;
            ws.Cell(1, 6).Value = box_0106.Text;
            ws.Cell(1, 7).Value = box_0107.Text;
            ws.Cell(1, 8).Value = box_0108.Text;

            //select range in cells
            //IXLRange rng = ws.Range("A1:H1");
            var firstCell = ws.FirstCellUsed();
            var lastCell  = ws.LastCellUsed();
            var range     = ws.Range(firstCell.Address, lastCell.Address);

            range.Style.Border.OutsideBorder = XLBorderStyleValues.Medium;


            ws.Row(1).Sort();

            wb.SaveAs(@"Y:\Liverpool projects\Windows form app\Ruth\test.xlsx");
        }
예제 #2
0
        public static Dictionary <string, string> ReadExcel(string path, int fCell, int fRow)
        {
            try
            {
                using (XLWorkbook workBook = new XLWorkbook(path))
                {
                    IXLWorksheet workSheet = workBook.Worksheet(1);
                    var          range     = workSheet.Range(workSheet.FirstCellUsed(), workSheet.LastCellUsed());
                    var          dict      = new Dictionary <string, string>();
                    var          i         = 0;

                    foreach (var item in range.Rows())
                    {
                        if (i >= fRow)
                        {
                            dict.TryAdd(item.Cell(fCell).Value.ToString(), item.Cell(fCell + 2).Value.ToString());
                        }
                        i++;
                    }

                    return(dict);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
예제 #3
0
        public object  ExcelToJson(string excel_path, string isHeader, string importStartRow)
        {
            List <Dictionary <string, object> > resultJson = new List <Dictionary <string, object> >();

            List <string> header = new List <string>();

            XLWorkbook   workbook  = new XLWorkbook(excel_path);
            IXLWorksheet worksheet = workbook.Worksheets.Worksheet(1);

            var firstCell = worksheet.FirstCellUsed();
            var lastCell  = worksheet.LastCellUsed();
            var firstRow  = worksheet.FirstRow();

            int hearderColumnsCount = lastCell.Address.ColumnNumber;

            if (isHeader == "checked")
            {
                for (int col = 1; col <= hearderColumnsCount; col++)
                {
                    header.Add(worksheet.Cell(1, col).Value.ToString());
                }
            }
            else
            {
                Dictionary <string, object> jo = new Dictionary <string, object>();

                for (int col = 1; col <= hearderColumnsCount; col++)
                {
                    string id = "col_" + col.ToString();
                    header.Add(id);
                    jo.Add(id, id);
                }
                resultJson.Add(jo);
            }
            int row = 1;

            foreach (IXLRow dr in worksheet.Rows())
            {
                if (row != 1)
                {
                    if (int.Parse(importStartRow) > row)
                    {
                        row += 1;
                        continue;
                    }
                }

                Dictionary <string, object> jo = new Dictionary <string, object>();
                jo.Add("sort_order", row);
                var i = 1;
                foreach (var head in header)
                {
                    jo.Add(head, dr.Cell(i).Value.ToString());
                    i += 1;
                }
                resultJson.Add(jo);
                row += 1;
            }
            return(resultJson);
        }
예제 #4
0
        private DataTable TransferExcelToDataTable(IXLWorksheet xlWorksheet)
        {
            var datatable = new DataTable();
            var range     = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed());

            int col = range.ColumnCount();
            int row = range.RowCount();

            // add columns hedars
            datatable.Clear();

            for (int i = 1; i <= col; i++)
            {
                IXLCell column = xlWorksheet.Cell(1, i);
                datatable.Columns.Add(column.Value.ToString());
            }

            // add rows data
            int firstHeadRow = 0;

            foreach (var item in range.Rows())
            {
                if (firstHeadRow != 0)
                {
                    var array = new object[col];
                    for (int y = 1; y <= col; y++)
                    {
                        array[y - 1] = item.Cell(y).Value;
                    }
                    datatable.Rows.Add(array);
                }
                firstHeadRow++;
            }
            return(datatable);
        }
예제 #5
0
        private int GetRowCount(IXLWorksheet worksheet)
        {
            var mainFirstTableCell = worksheet.FirstCellUsed();
            var mainLastTableCell  = worksheet.LastCellUsed();

            return(mainFirstTableCell == null || mainLastTableCell == null
                ? 1
                : worksheet.Range(mainFirstTableCell.Address, mainLastTableCell.Address).RowCount() + 1);
        }
예제 #6
0
        public static void ReadFromXml(string filePath)
        {
            XLWorkbook   wb = new XLWorkbook(filePath);
            IXLWorksheet ws = wb.Worksheets.First();

            IXLTable table = ws.Range(ws.FirstCellUsed(), ws.LastCellUsed()).AsTable();

            /*string s = "";
             * table.DataRange.CellsUsed().Count();
             * table.DataRange.Rows().ForEach(row =>
             * {
             *  s += "\n";
             *  row.Cells().ToList().ForEach(cell =>
             *  {
             *      s += cell.GetString()+"\t|||\t";
             *  });
             * });
             * if (true)
             * {
             *
             * }*/

            TimeTable orar      = new TimeTable();
            IXLCell   grupaCell = null;

            #region grupa

            //cauta coloana care contine grupa
            IXLRangeColumn grupaColoana = table.DataRange.FindColumn(column =>
            {
                grupaCell = column.Cells()?.FirstOrDefault(cell => cell.GetString().Equals("Grupa"));
                return(grupaCell != null);
                //return column.Cells().Any(cell => cell.GetString().Equals("Grupa"));
            });
            int id = 1;
            //insereaza grupele care apar in excel in orar
            grupaColoana.CellsUsed().Where(cell => !cell.GetString().Equals("Grupa")).ForEach(cell =>
            {
                orar.Groups.Add(new GroupDto(id++, cell.GetString()));
            });

            #endregion

            table.Range(grupaCell.Address.RowNumber + 1, grupaCell.Address.ColumnNumber + 2,
                        table.LastCellUsed().Address.RowNumber, table.LastCellUsed().Address.ColumnNumber)
            .CellsUsed().Where(cell => !table.Cell(cell.Address.RowNumber, 3).IsEmpty()).ForEach(cell =>
            {
                string[] split = cell.GetString().Split(",".ToCharArray());
                //Programare prog = new Programare();

                string materie    = split[0];
                string tipMaterie = split[1];
                string sala       = split[2];
                string profesor   = split[3];
            });
        }
예제 #7
0
        //Read Excel data to generic list - overloaded version 2.
        public static IList <T> GetDataToList <T>(string filePath, string sheetName, Func <IList <string>, T> addRecord)
        {
            List <T> resultList = new List <T>();

            using (XLWorkbook wB = new XLWorkbook(filePath))
            {
                IXLWorksheet wS = wB.Worksheet(1);
                //DataTable dt = new DataTable();
                //bool firstRow = true;
                //Used for sheet row data to be added through delegation.

                var firstRow = true;
                int lastRow  = wS.LastRowUsed().RowNumber();
                var rows     = wS.Rows(1, lastRow);
                foreach (IXLRow row in rows)
                {
                    if (firstRow)
                    {
                        ////foreach (IXLCell cell in row.Cells())
                        ////{
                        ////    dt.Columns.Add(cell.Value.ToString());
                        ////}
                        firstRow = false;
                        continue;
                    }

                    if (row.IsEmpty())
                    {
                        continue;
                    }
                    var rowData = new List <string>();
                    //else
                    //{
                    //dt.Rows.Add();
                    //int i = 0;
                    //rowData.Clear();
                    foreach (IXLCell cell in row.Cells(wS.FirstCellUsed().Address.ColumnNumber, wS.LastCellUsed().Address.ColumnNumber))
                    {
                        rowData.Add(cell.Value.ToString());
                    }
                    //foreach (IXLCell cell in row.Cells())
                    //{
                    //    rowData.Add(cell.Value.ToString());
                    //}
                    resultList.Add(addRecord(rowData));
                }


                // }
                return(resultList);
            }
        }
예제 #8
0
        public void LastCellFromMerge()
        {
            var          wb = new XLWorkbook();
            IXLWorksheet ws = wb.Worksheets.Add("Sheet");

            ws.Range("B2:D4").Merge();

            string first = ws.FirstCellUsed(true).Address.ToStringRelative();
            string last  = ws.LastCellUsed(true).Address.ToStringRelative();

            Assert.AreEqual("B2", first);
            Assert.AreEqual("D4", last);
        }
        private static void CreateTable <TData>(IXLWorksheet worksheet, IList <ExcelColumnInfo <TData> > excelColumnInfos)
        {
            var firstCell = worksheet.FirstCellUsed();
            var lastCell  = worksheet.LastCellUsed();
            var range     = worksheet.Range(firstCell.Address, lastCell.Address);

            foreach (var excelColumnInfo in excelColumnInfos.Where(x => x.DataFormat != null))
            {
                var index = excelColumnInfos.IndexOf(excelColumnInfo) + 1;
                range.Column(index).Style.NumberFormat.Format = excelColumnInfo.DataFormat;
            }

            range.CreateTable();

            worksheet.Columns().AdjustToContents();
        }
예제 #10
0
        public static void AreWorksheetsContentEquals(IXLWorksheet expected, IXLWorksheet actual)
        {
            if (expected == actual)
            {
                return;
            }

            Assert.AreEqual(expected.CellsUsed(XLCellsUsedOptions.All).Count(), actual.CellsUsed(XLCellsUsedOptions.All).Count(), "Cells used count failed");

            IXLCell expectedFirstCellUsed = expected.FirstCellUsed(XLCellsUsedOptions.All);
            IXLCell actualFirstCellUsed   = actual.FirstCellUsed(XLCellsUsedOptions.All);

            Assert.AreEqual(expectedFirstCellUsed.Address, actualFirstCellUsed.Address, "First cell used failed");
            IXLCell expectedLastCellUsed = expected.LastCellUsed(XLCellsUsedOptions.All);
            IXLCell actualLastCellUsed   = actual.LastCellUsed(XLCellsUsedOptions.All);

            Assert.AreEqual(expectedLastCellUsed.Address, actualLastCellUsed.Address, "Last cell used failed");

            IXLRange range = expected.Range(expectedFirstCellUsed, expectedLastCellUsed);

            foreach (IXLCell expectedCell in range.Cells())
            {
                IXLCell actualCell = actual.Cell(expectedCell.Address);
                if (expectedCell.HasFormula)
                {
                    Assert.AreEqual(expectedCell.FormulaA1, actualCell.FormulaA1, $"Cell {expectedCell.Address} FormulaA1 failed.");
                    //// For some reason sometimes the formulas "FormulaR1C1" are different although the formulas "FormulaA1" are match
                    //Assert.AreEqual(expectedCell.FormulaR1C1, actualCell.FormulaR1C1, $"Cell {expectedCell.Address} FormulaR1C1 failed.");
                    Assert.AreEqual(expectedCell.FormulaReference, actualCell.FormulaReference, $"Cell {expectedCell.Address} FormulaReference failed.");
                }
                else
                {
                    Assert.AreEqual(expectedCell.Value, actualCell.Value, $"Cell {expectedCell.Address} Value failed.");
                }
                Assert.AreEqual(expectedCell.DataType, actualCell.DataType, $"Cell {expectedCell.Address} DataType failed.");
                Assert.AreEqual(expectedCell.Active, actualCell.Active, $"Cell {expectedCell.Address} Active failed.");
                AreColumnsEquals(expectedCell.WorksheetColumn(), actualCell.WorksheetColumn(), $"Column {actualCell.WorksheetColumn().RangeAddress} {{0}} failed.");
                AreRowEquals(expectedCell.WorksheetRow(), actualCell.WorksheetRow(), $"Row {actualCell.WorksheetRow().RangeAddress} {{0}} failed.");
                AreCellsStyleEquals(expectedCell.Style, actualCell.Style, $"Cell {expectedCell.Address} Style {{0}} failed.");
                AreCellsCommentEquals(expectedCell.Comment, actualCell.Comment, $"Cell {expectedCell.Address} Comment {{0}} failed.");
            }

            AreMergedRangesEquals(expected.MergedRanges, actual.MergedRanges);
            AreNamedRangesEquals(expected.NamedRanges, actual.NamedRanges);
            ArePageSetupEquals(expected.PageSetup, actual.PageSetup, "PageSetup {0} failed.");
        }
        //TODO:
        //Throw Exception
        public DataTable InsertTblRegisteredStudents(String FilePath)
        {
            DataTable dt = null;

            using (XLWorkbook workBook = new XLWorkbook(FilePath))
            {
                //Read the first Sheet from Excel file.
                IXLWorksheet workSheet = workBook.Worksheet(1);
                //Create a new DataTable.
                dt = new DataTable();
                //Loop through the Worksheet rows.
                bool firstRow = true;
                foreach (IXLRow row in workSheet.Rows())
                {
                    if (!row.Cell(1).IsEmpty())
                    {
                        //Use the first row to add columns to DataTable.
                        if (firstRow)
                        {
                            foreach (IXLCell cell in row.Cells())
                            {
                                dt.Columns.Add(cell.Value.ToString());
                            }
                            firstRow = false;
                        }

                        else
                        {
                            //Add rows to DataTable.
                            dt.Rows.Add();
                            int i = 0;
                            foreach (IXLCell cell in row.Cells(workSheet.FirstCellUsed().Address.ColumnNumber, workSheet.LastCellUsed().Address.ColumnNumber))
                            {
                                dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                                i++;
                            }
                        }
                    }
                }
            }
            return(dt);
        }
예제 #12
0
        /// <summary>
        /// To the data table.
        /// </summary>
        /// <param name="xlWorksheet">The xl worksheet.</param>
        /// <returns>DataTable.</returns>
        public static DataTable ToDataTable(this IXLWorksheet xlWorksheet)
        {
            var datatable  = new DataTable();
            var errorFound = false;

            try
            {
                var range = xlWorksheet.Range(xlWorksheet.FirstCellUsed(), xlWorksheet.LastCellUsed()).RangeUsed();

                var columnCount = range.ColumnCount();
                var rowCount    = range.RowCount();
                datatable.Clear();

                //
                // Create our columns bases on the first row which contains the itemName and category names
                //
                try
                {
                    if (rowCount < 1)
                    {
                        return(new DataTable());
                    }
                    var goodColumnCount = 0;
                    for (var i = 1; i <= columnCount; i++)
                    {
                        if (xlWorksheet.Cell(1, i).Value.ToString().GetClsString().IsNullOrEmpty())
                        {
                            break;
                        }
                        goodColumnCount++;
                        datatable.Columns.Add(new DataColumn
                        {
                            DataType   = Type.GetType("System.String"),
                            ColumnName = xlWorksheet.Cell(1, i).Value.ToString().GetClsString(),
                            Caption    = xlWorksheet.Cell(1, i).Value.ToString()
                        }
                                              );
                    }
                    columnCount = goodColumnCount;
                }
                catch (Exception ex)
                {
                    NLogger.Warn("Unable to create the data table columns. {0}", ex.Message);
                    return(new DataTable());
                }

                //
                // Copy cell data into our datatable.
                //
                if (rowCount < 2)
                {
                    return(datatable);
                }
                for (var i = 2; i <= rowCount; i++)
                {
                    if (range.Row(i).Cell(1).Value.ToString().IsNullOrEmpty())
                    {
                        continue;
                    }
                    var array = new object[columnCount];
                    for (var y = 1; y <= columnCount; y++)
                    {
                        try
                        {
                            if (range.Row(i).Cell(y).HasFormula)
                            {
                                NLogger.Warn("Unable to process cells which contain a forumlas (to remove formulas: copy -> paste special values). Check on Sheet {0} row {1} column {2}", xlWorksheet.Name, i, y);
                                errorFound = true;
                                continue;
                            }
                            array[y - 1] = range.Row(i).Cell(y).Value.ToString();
                            if (y == 1)
                            {
                                //if (array[y-1].ToString().IsEqualTo("Spurlock - SP03 Bed Ash Silo Chute", true)) System.Diagnostics.Debugger.Break();
                                NLogger.Trace(array[y - 1]);
                            }
                        }
                        catch (Exception ex)
                        {
                            errorFound = true;
                            NLogger.Warn("Error on Sheet {0} row {1} column {2}", xlWorksheet.Name, i, y);
                            NLogger.Trace(ex.Message);
                        }
                    }
                    if (ArrayHasValue(array))
                    {
                        datatable.Rows.Add(array);
                    }
                }
                if (errorFound)
                {
                    throw new Exception("There are value errors in the spreadsheet.");
                }
            }
            catch (Exception ex)
            {
                NLogger.Warn(ex.Message);
                datatable = new DataTable();
            }
            return(datatable);
        }
예제 #13
0
        private DataTable ConvertWorksheetToDataTable(IXLWorksheet worksheet)
        {
            var dataTable = new DataTable();

            List <int> formattedColumnNumbers = new List <int>();

            IXLRange range = worksheet.Range(worksheet.FirstCellUsed(), worksheet.LastCellUsed());

            int columnCount = range.ColumnCount();

            dataTable.Clear();

            for (int i = 1; i <= columnCount; i++)
            {
                IXLCell cell = worksheet.Cell(1, i);

                Type dataTableColumnType = GetDataType(range, cell.Address.ColumnNumber);

                dataTable.Columns.Add(cell.Value.ToString(), dataTableColumnType);
            }

            int firstHeadRow = 0;

            foreach (var row in range.Rows())
            {
                if (firstHeadRow != 0)
                {
                    var array = new object[columnCount];

                    for (int y = 1; y <= columnCount; y++)
                    {
                        if (!row.Cell(y).HasFormula)
                        {
                            if (row.Cell(y).Value == null || string.IsNullOrEmpty(row.Cell(y).Value.ToString()))
                            {
                                array[y - 1] = null;
                            }
                            else if (formattedColumnNumbers.Contains(row.Cell(y).Address.ColumnNumber))
                            {
                                array[y - 1] = row.Cell(y).GetFormattedString();
                            }
                            else if (row.Cell(y).DataType == XLDataType.DateTime)
                            {
                                DateTime dateTime = row.Cell(y).GetDateTime();

                                array[y - 1] = dateTime.TimeOfDay.Ticks == 0
                                    ? dateTime.ToString("MM/dd/yyyy")
                                    : dateTime.ToString();
                            }
                            else
                            {
                                row.Cell(y).SetDataType(XLDataType.Text);

                                array[y - 1] = row.Cell(y).Value;
                            }
                        }
                    }

                    dataTable.Rows.Add(array);
                }

                firstHeadRow++;
            }

            return(dataTable);
        }
예제 #14
0
        private void prepDashboardSheets(IXLWorksheet sheet)
        {
            /*.
             * can use htis for both types of sheets.
             *  Regular: List.Count -1 (end of list: should be Month) List.Count -2 (last value: Should be provider name)
             *  Diabetes: List.Count -1 (end of list: Month) List.Count -3 (blank space between provider name an dmonth on this one)
             */

            _worksheet = sheet;
            //use this one to keep track of provider name and row number. then send it to metrics to dashboard and do accordignly
            Dictionary<String, IXLRangeRow> providerRows = new Dictionary<String, IXLRangeRow>();

            if (_worksheet != null)
            {
                var firstCell = _worksheet.FirstCellUsed();
                var lastCell = _worksheet.LastCellUsed();
                _worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);

                if (_worksheet_range != null)
                {
                    int nRows = _worksheet_range.RowCount();
                    int nCols = _worksheet_range.ColumnCount();
                    for (int i = 1; i < nRows + 1; i++)
                    {
                        var row = _worksheet_range.Row(i);
                        var newRow = _worksheet_range.Row(i + 1);
                        string value = row.Cell(1).Value as string;

                        if (value != null)
                        {
                            foreach (string provider in providers)
                            {
                                if (value.Contains(provider))
                                {
                                    if (_worksheet == _dashboard.Worksheet(2))//add a new row for the depression sheets
                                    {
                                        newRow = _worksheet_range.Row(i + 3);
                                        newRow.InsertRowsBelow(1);
                                        var blankRow = _worksheet_range.Row(i + 4);
                                        blankRow.Style.NumberFormat.NumberFormatId = 0;
                                        providerRows.Add(value, blankRow);

                                    }
                                    else //add a new row for every other sheet in the dashboard: Asthma, Diabetes, Cardiovascular, Preventive
                                    {
                                        newRow = _worksheet_range.Row(i + 2);//this gets us int he right area and then insert the row above
                                        newRow.InsertRowsBelow(1); //try to insert rows after we have metrics and tehn insert metrics into cells then insert row
                                        var blankRow = _worksheet_range.Row(i + 3);
                                        blankRow.Style.NumberFormat.NumberFormatId = 0;
                                        providerRows.Add(value, blankRow);
                                    }
                                    break; //break out of the foreach provider loop, we already found one, we wont find another match, time to go to the next row instead
                                }
                            }
                        }
                    }
                   MetricsToDashboard(providerRows, _worksheet);//figure out what we need to send to this method , worksheet, provider / row dict etc.
                }
            }
        }
        public ViewResult saveToDatabase(HttpPostedFileBase excelFile)
        {
            try
            {
                //Save the uploaded Excel file.
                string path = Server.MapPath("~/Uploads/");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }

                excelFile.SaveAs(path + Path.GetFileName(excelFile.FileName));


                using (XLWorkbook workBook = new XLWorkbook(path + Path.GetFileName(excelFile.FileName)))
                {
                    String columnsNamesQuery = String.Format("CREATE TABLE {0} (", excelFile.FileName.Split('.')[0]);
                    String insertQuery       = String.Format("INSERT INTO {0} (", excelFile.FileName.Split('.')[0]);

                    IXLWorksheet workSheet = workBook.Worksheet(1);

                    bool firstRow = true;
                    foreach (IXLRow row in workSheet.Rows())
                    {
                        if (firstRow)
                        {
                            foreach (IXLCell cell in row.Cells())
                            {
                                columnsNamesQuery += "[" + cell.Value.ToString() + "] VARCHAR(MAX),";
                                insertQuery       += "[" + cell.Value.ToString() + "],";
                            }

                            insertQuery        = insertQuery.Remove(insertQuery.Length - 1, 1);
                            insertQuery       += ") VALUES (";
                            columnsNamesQuery  = columnsNamesQuery.Remove(columnsNamesQuery.Length - 1, 1);
                            columnsNamesQuery += ");";

                            //Creating data table
                            Command.Connection = Connection;
                            Connection.Open();
                            Command.CommandText = columnsNamesQuery;
                            Command.ExecuteNonQuery();
                            Connection.Close();

                            firstRow = false;
                        }
                        else
                        {
                            String rowsQuery = insertQuery;
                            foreach (IXLCell cell in row.Cells(workSheet.FirstCellUsed().Address.ColumnNumber, workSheet.LastCellUsed().Address.ColumnNumber))
                            {
                                if (cell.IsEmpty())
                                {
                                    rowsQuery += "NULL,";
                                }
                                else
                                {
                                    rowsQuery += "'" + cell.Value.ToString() + "' ,";
                                }
                            }
                            rowsQuery  = rowsQuery.Remove(rowsQuery.Length - 1, 1);
                            rowsQuery += ");";

                            //Insert data into database
                            Command.Connection = Connection;
                            Connection.Open();
                            Command.CommandText = rowsQuery;
                            Command.ExecuteNonQuery();
                            Connection.Close();
                        }
                    }
                }
                return(View("Success"));
            }
            catch (Exception e)
            {
                ViewBag.error = "ERROR: " + e.Message + "<br />";
                return(View("Index"));
            }
        }
예제 #16
0
        //カレントSheetのデータ範囲開始行を取得
        private int getStartRow()
        {
            var first = _currentWs.FirstCellUsed();

            return(first.WorksheetRow().RowNumber());
        }
예제 #17
-1
        private List<String> ReturnMetricsRow(IXLWorksheet sheet)
        {
            List<String> metricNames = new List<string>();

            _worksheet = sheet;
            if (_worksheet != null)
            {
                var firstCell = _worksheet.FirstCellUsed();
                var lastCell = _worksheet.LastCellUsed();
                _worksheet_range = _worksheet.Range(firstCell.Address, lastCell.Address);

                if (_worksheet_range != null)
                {
                    int nRows = _worksheet_range.RowCount();
                    int nCols = _worksheet_range.ColumnCount();
                    for (int i = 1; i < nRows + 1; i++)
                    {
                        var row = _worksheet_range.Row(i);
                        var newRow = _worksheet_range.Row(i + 1);
                        string value = row.Cell(1).Value as string;

                        if (value == "Month")
                        {
                            var metricRow = _worksheet_range.Row(i);
                            for (int x = 1; x <= metricRow.CellCount(); x++)
                            {
                                metricNames.Add(metricRow.Cell(x).Value.ToString());
                            }
                            break;
                        }
                    }

                }
            }
            return metricNames;
        }