I Need To Add Default Coulmns In Microsoft.Office.Interop.Excel

Contents hide

1 I need to add default coulmns in Microsoft.Office.Interop.Excel

Problem Description:

Hello I wanted to set default column names in my program.Ex: For the first column the [1,1] need to set as "A", and for 2nd "B", for 3rd "C". Here is the code:

private void btnExport_Click(object sender, EventArgs e)
{
    Microsoft.Office.Interop.Excel.Application xla = new Microsoft.Office.Interop.Excel.Application();

    xla.Visible = true;

    Microsoft.Office.Interop.Excel.Workbook wb = xla.Workbooks.Add(Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);

    Microsoft.Office.Interop.Excel.Worksheet ws = (Microsoft.Office.Interop.Excel.Worksheet)xla.ActiveSheet;

    int i = 1;

    int j = 1;

    foreach(ListViewItem comp in listView1.Items)
    {
        ws.Cells[i, j] = comp.Text.ToString();

        foreach(ListViewItem.ListViewSubItem drv in comp.SubItems)
        {
            ws.Cells[i, j] = drv.Text.ToString();
            j++;

        }
        
        j = 1;

        i++;
    }

}

I am new in C# and I need to help to create default rows in my excel sheet, my excel shows like:
enter image description here

Solution – 1

change your code so that i starts from 2, and add filling columns from A to Z in your foreach(ListViewItem comp in listView1.Items) loop

something like this:

//(... skipped ...)

int i = 2;
int j = 1;

foreach(ListViewItem comp in listView1.Items)
{
    ws.Cells[1, j] = = ((char)('@' + j)).ToString();
    ws.Cells[i, j] = comp.Text.ToString();

    //... rest of your code ...

So, j variable is starting from 1 and goes to number of items in your ListView. In each iteration code is converting @ character to its ASCII code (@ is 64) and adds j value to it (getting 65, 66, 67 etc). Resulting value is ASCII code for A (65), B (66), C (67)… and that value is being set as cell’s value.

Code that sets column works with first row, so it’s ws.Cells[1, j]

Rate this post