Search code examples
sqlvb.netoledbexport-to-excel

Writing an existing Excel file from db but with different headers

I have a problem that i can't solve even if i looked everywhere for a solution. I got an excel that have to be written from a table in my db, problem is that the headers in my db are named different than the headers in my excel file and I need to keep them like that. Another problem is that the index of the columns are also diffrent...It would be great if i could make a query for each column and write the result to the excel file by indicating the column's index, something like that:

Dim column1 As String = "SELECT  [columnName] from [tableName] "

and then 

Sheets("SheetName").Columns("ColumnName").value = column1

obviously it doesn't work it's just to let you know my idea...

Solution

  • Build your SQL query to match the order of the excel file and then fill in your result like 

    for (int i = 0; i < table.Columns.Count; i++)
{
Sheets("SheetName").Columns(i).value = table.Columns[i].ColumnName 
}

    and add another for-loop for the rows on the table.Rows to fill in the values