C# Excel Dynamic Range Starting At Headers

Question

I have an excel spreadsheet that is formatted to look nice; Column A is left blank, Row 1 has a big title for the document, Row 2 has a disclaimer, rows 3 & 4 are merged and centered with row 2. My data starts at B:5. The document is then parsed using C# and added to my database. Right now I just manually delete column A plus rows 3 & 4 before running my parser but I would like to not have to manually delete the extra white space. I know I can manually hard code my range to start at B:5 but I want something that is future proof. So if I change the document around my parser won't have to be updated. I would like to set the range on the parser to be dynamic. Is there any way to have my code set the range starting with the column and row of my first header? Here is my current code.

            Range xlRange = null;
            Sheets xlSheets = null;
            InitializeExcelConnection(fileName, 1, out xlRange, out xlSheets, worker);
            if (xlRange != null)
            {
                int nRows = xlRange.Rows.Count;
                int nCols = xlRange.Columns.Count;
                total_rows = nRows;
                if (nCols > MAX_COLS)
                {
                    nCols = MAX_COLS;
                }
                ary = new string[nCols];

                for (int iRow = 1; iRow <= nRows; iRow++)
                {
                    Output(string.Format("Now processing row {0}", iRow), worker);
                    recordFlag = true;
                    for (i = 0; i < nCols; i++)
                    {
                        ary[i] = null;
                    }
                    i = 0;
                    for (int iCount = 1; iCount <= nCols && iCount <= MAX_COLS; iCount++, i++)
                    {
                        xlRange = (Microsoft.Office.Interop.Excel.Range)ExcelWorksheet.Cells[iRow, iCount];
                        if (!titalDoneFlag && iCount == 1)
                        {
                            if (xlRange == null)
                            {
                                recordFlag = false;
                                break;
                            }
                            if (xlRange.Text == null)
                            {
                                recordFlag = false;
                                break;
                            }
                            s = xlRange.Text.ToString();
                            if (string.IsNullOrEmpty(s))
                            {
                                recordFlag = false;
                                break;
                            }
                            s = s.Trim();
                            if (string.IsNullOrEmpty(s))
                            {
                                recordFlag = false;
                                break;
                            }
                        }

Maybe this can help you http://stackoverflow.com/questions/1284388/how-to-get-the-range-of-occupied-cells-in-excel-sheet — Sybren, May 09 '17 at 20:27
Look into naming your top left header cell as an Excel range(worksheet scope is easiest). Then you can get the range in C# by: `Excel.Range topLeftHeaderCell = mySheet.get_Range("myNamedRange"); ` Then you can get the row, column etc — MacroMarc, May 09 '17 at 22:06

score 0 · Answer 1 · edited Oct 20 '20 at 18:28

Using EPPlus Package

Check the Crack Version for the DLL

using (ExcelPackage excel = new ExcelPackage())
{
    excel.Workbook.Worksheets.Add("Sheet1");
    var headerRow = new List<string[]>();
    headerRow.Add(arr);
    string headerRange = "A1:" + Char.ConvertFromUtf32(headerRow[0].Length + 64) + "1";
    var worksheet = excel.Workbook.Worksheets["Sheet1"];
    worksheet.Cells[headerRange].LoadFromArrays(headerRow);
    FileInfo excelFile = new FileInfo(filename);
    excel.SaveAs(excelFile);
}

C# Excel Dynamic Range Starting At Headers

1 Answers1