MS-excel insert operation do using c#

Posted by Jayakumarv under C# on 5/16/2011 | Points: 10 | Views : 2546 | Status : [Member] | Replies : 1
Hai
i have excel sheet i want put data into sheet from datatable or dataset
but the table structure different like datatable column is row of excel sheet
and datatable row is column of excel sheet and column and row order also
differ. how i do this task?.




Responses

Posted by: Ndebata on: 5/16/2011 [Member] Starter | Points: 25

Up
0
Down
Hi

Try to add a reference to Microsoft.Office.Interop.Excel.
Then create an Excel workbook at add your data table.
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();

if (xlApp == null)
{
Console.WriteLine("EXCEL could not be started. Check that your office installation and project references are correct.");
return;
}
xlApp.Visible = true;
Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Worksheet ws = (Worksheet)wb.Worksheets[1];
if (ws == null)
{
Console.WriteLine("Worksheet could not be created. Check that your office installation and project references are correct.");
return;
}
//Create a sample data table
System.Data.DataTable dt=new System.Data.DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Column1");
dt.Columns.Add("Column2");
dt.Columns.Add("Column3");
dt.Rows.Add(new object[] { "Row1", "Row1Column1", "Row1Column2", "Row1Column3" });
dt.Rows.Add(new object[] { "Row2", "Row2Column1", "Row2Column2", "Row2Column3" });
dt.Rows.Add(new object[] { "Row3", "Row3Column1", "Row3Column2", "Row3Column3" });
dt.Rows.Add(new object[] { "Row4", "Row4Column1", "Row4Column2", "Row4Column3" });
dt.Rows.Add(new object[] { "Row5", "Row5Column1", "Row5Column2", "Row5Column3" });

for (int i = 1; i <= dt.Columns.Count; i++)
{
ws.Cells[i, 1] = dt.Columns[i - 1].ColumnName;
for (int j = 1; j <= dt.Rows.Count; j++)
{
ws.Cells[i, j+1] = dt.Rows[j - 1][i - 1];
}
}

* Please refer to know more
http://msdn.microsoft.com/en-us/library/ms173186(v=vs.80).aspx

Jayakumarv, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response