CODEWITHSUNDEEP
c#excel
roa765
roa765

Reputation: 295

C# Store named ranges from excel header first row

I am new to C# working with excel. I have an excel file and need to loop through the first row each column and store the value of this column as an excel named range for that column address. I have the below so far :

var fileinfo = new FileInfo(path);
        if (fileinfo.Exists)
        {
            using (ExcelPackage p = new ExcelPackage(fileinfo))
            {
                var worksheet = p.Workbook.Worksheets[1];

                int iRowCnt = worksheet.Dimension.End.Row;
                int iColCnt = worksheet.Dimension.End.Column;

                for (int i=1; i <= iColCnt; i++ )
                {

                }

            }
        }

N.B each column value is the header which i want to store as the named range in excel

Upvotes: 0

Views: 118

Answers (1)

Josue Barrios
Josue Barrios

Reputation: 502

I assume that you are using the epplus package. This is the code that I use to convert an Excel document to a datatable.

public static DataTable LoadFromStreamExcel(String _path, bool _has_header)
{
    DataTable _datatable = new DataTable();
    var fileinfo = new FileInfo(_path);
    using (ExcelPackage pack = new ExcelPackage(fileinfo))
    {
        ExcelWorksheet ws = pack.Workbook.Worksheets.First();
        foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
        {
            _datatable.Columns.Add(_has_header ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
        }
        var startRow = _has_header ? 2 : 1;
        for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
        {
            var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
            DataRow row = _datatable.Rows.Add();
            foreach (var cell in wsRow)
            {
                row[cell.Start.Column - 1] = cell.Value;
            }
        }
    }
    return _datatable;
}

Upvotes: 1

Related Questions