Click here to Skip to main content
15,886,518 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I want to export 4 columns of all 4 rows from table 'clientinfo' to an excel spreadsheet. It works leaving the first row of the table. However, the data table 'dtMainSQLData' shows all the 4 rows intact. The Spreadsheet shows only 3 rows.
My code;

SqlDataAdapter da = new SqlDataAdapter("select clientname,clientaddress1,clientaddress2,clientaddress3 from clientinfo", 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\\RAJENDRAN\\Desktop\\raj2.xls");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
}
Posted

you for statement should be started in zero man!

C#
for(int i = 0 ...)
for(int j = 0 ...)
 
Share this answer
 
Comments
Vedat Ozan Oner 24-Jan-14 6:52am    
I don't agree. No such necessity.
EduChapow 24-Jan-14 8:30am    
Yes man, i'm wrong.
i dont se Rows.Count + 1;

see ya
a small change is enough :)

C#
if (i == 1)
    ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
ExcelApp.Cells[i+1, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString();


changes:
1- 'else' removed.
2- rows started from 2 on excel sheet.

ps: use the debugger, the easiest way.
 
Share this answer
 
v2
Comments
S.Rajendran from Coimbatore 24-Jan-14 7:01am    
Thanks. It works.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900