Export to excel from sql server database not working properly

Question

I have a working code that exports SQL Server data to Excel.

public static void ExportToExcel(DataTable dt)
{
        // Create sql connection string
        string conString = "Data Source=DELL\\SQLSERVER1;Trusted_Connection=True;DATABASE=Test;CONNECTION RESET=FALSE";
        SqlConnection sqlCon = new SqlConnection(conString);
        sqlCon.Open();

        SqlDataAdapter da = new SqlDataAdapter("select * from tStudent", sqlCon);
        System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
        da.Fill(dtMainSQLData);
        DataColumnCollection dcCollection = dtMainSQLData.Columns;

        // Export Data into EXCEL Sheet
        Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
        ExcelApp.Application.Workbooks.Add(Type.Missing);

        //ExcelApp.Cells.CopyFromRecordset(objRS);
        for (int i = 1; i < dtMainSQLData.Rows.Count + 1; i++)
        {
            for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
            {
                if (i == 1)
                    ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
                else
                    ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString();
            }
        }
        ExcelApp.ActiveWorkbook.SaveCopyAs("C:/Users/Administrator.CAMO/Downloads/FtpFilesStorage/Sheet1.xlsx");
        ExcelApp.ActiveWorkbook.Saved = true;
        ExcelApp.Quit();

        Console.WriteLine(".xlsx file Exported succssessfully.");
}

And I am using this method in Main() like this:

System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
ExportToExcel(dtMainSQLData);

This works fine but the first row is not copied or exported from the original table in SQL Server.

Your for is wrong it should be for (int i = 0; i < dtMainSQLData.Rows.Count; i++ and you'll have to off set it when you write e.g. ExcelApp.Cells[i+1, j] — Conrad Frix
– Conrad Frix, Commented Apr 1, 2014 at 18:18
thanks Conrad Frix... It did work. But now I am not able to export the last row. I changed my code to following after your suggestion to this: for (int i = 0; i < dtMainSQLData.Rows.Count; i++) { for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++) { if (i == 0) ExcelApp.Cells[i + 1, j] = dcCollection[j - 1].ToString(); else ExcelApp.Cells[i + 1, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString(); } } Plz tell wht mstak i'm makin. — user2525244
– user2525244, Commented Apr 1, 2014 at 19:13

Conrad Frix · Accepted Answer · 2014-04-01 19:52:18Z

2

You're experiencing one-off errors because of the fact that ExcelApp.Cells isn't zero based and you have a header row.

To combat this you can use foreach and separate out your header export to a separate block. It does take a little bit more code but I find it easier to deal with and less error prone

int i = 1;

int j = 1;   
//header row
foreach(DataColumn col in dtMainSQLData.Columns)
{  
  ExcelApp.Cells[i, j]  = col.ColumnName;
  j++;
}

i++;
//data rows
foreach(DataRow row  in dtMainSQLData.Rows)
{
    for (int k = 1; k < dtMainSQLData.Columns.Count + 1; k++)
    {
         ExcelApp.Cells[i, k]  = row[k-1].ToString();
    }
    i++;
}

edited Apr 1, 2014 at 19:52

answered Apr 1, 2014 at 19:36

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2525244 Over a year ago

Using following I get an error saying "object does not contain a definition for column name and no extension for 'ColumnName' " Cannot apply indexing with [] to an expression of type `object' at row[k-1] Regards.

Conrad Frix Over a year ago

I changed it to use the actual data types instead of var although I don't see why that's needed

user2525244 Over a year ago

Hi Conrad Frix. Can you help me with this one. stackoverflow.com/questions/22797917/… Thanks.

gorf13 · Accepted Answer · 2018-03-02 17:50:47Z

0

Your code is fine, except you just need to remove your "else" block and increment the starting row of your Excel spreadsheet:

for (int i = 1; i < dtMainSQLData.Rows.Count + 1; i++)
{
    for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
    {
        if (i == 1)
            ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();

        ExcelApp.Cells[i + 1, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString();
    }
}

answered Mar 2, 2018 at 17:50

gorf13

1

Collectives™ on Stack Overflow

Export to excel from sql server database not working properly

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related