How can I pass and format an SQL table into an excel using C#

Question

Currently I have a table with 6 rows and 14 columns. I'm trying to pass that table to my excel document and I have no problem doing that. My problem is that I can't format it the way I want. Idealy I want to have 3 rows, blank space and 3 rows again, but I can't do that. This is the function I'm currently using to format the Sql table. Basically it writes in excel all the rows consecutively. Instead of doing that I want it to have a black row between row 3 and row 4.

If someone could help I'd very thankful.

private int Export_putDataGeneric(Excel.Worksheet sh, DataTable ds, String D_ReferenceDate, int starting_row = 5, int[] column_mapping = null, bool isNumber = true)
{
    int curr_row = 0;

    if (column_mapping == null)
    {
        column_mapping = new int[ds.Columns.Count];
        int start_char = 2;
        for (int c = 0; c < ds.Columns.Count; c++)
        {
            column_mapping[c] = start_char;
            start_char++;
        }
    }

    var data = new Object[ds.Rows.Count, column_mapping[ds.Columns.Count - 1] - column_mapping[0] + 1];

    foreach (DataRow row in ds.Rows)
    {
        for (int c = 0; c < ds.Columns.Count; c++)
        {
            data[curr_row, column_mapping[c] - column_mapping[0]] = row[c];
        }
        curr_row++;
    }

    int end_row = starting_row + ds.Rows.Count - 1;

    Excel.Range beginWrite = sh.Cells[starting_row, column_mapping[0]] as Excel.Range;
    Excel.Range endWrite = sh.Cells[end_row, column_mapping[ds.Columns.Count - 1]] as Excel.Range;
    Excel.Range sheetData = sh.Range[beginWrite, endWrite];

    sheetData.Value2 = data;

    if (isNumber) sheetData.NumberFormat = "#,##0.00";

    Marshal.ReleaseComObject(beginWrite);
    Marshal.ReleaseComObject(endWrite);
    Marshal.ReleaseComObject(sheetData);

    beginWrite = null;
    endWrite = null;
    sheetData = null;

    return end_row;
}

score 1 · Answer 1 · answered Jul 29 '19 at 11:55

1

You can try using Range.Offset.

Check out the Microsoft Documentation

This question on SO might also help.

answered Jul 29 '19 at 11:55

GetFookedWeeb

21
1
9

I put this code but it tells me that I can't assign a method group to what it seems a local variable.What type of variable must it be then? var nextRange = excelApp.ActiveCell.Offset[skipRows, skipCells].Select(); – Bia Jul 29 '19 at 14:26

How can I pass and format an SQL table into an excel using C#

1 Answers1