hi guys,
i have some data in sql data table. i fetch data from sql in asp.net controls now i want to generate a excel file with fetching data fom sql.
help me in this topic.
Thanks
Ajay
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
arjunPosted Jan 4, 2013, 2:03 AM
1.add reference to u r project
microsoft.office.interop.excel
then follow the below code
using
excel=Microsoft.Office.Interop.Excel;
excel.
Application xlapp;
excel.
Workbook xlwb;
excel.
Worksheet xlws;
object misvalue = System.Reflection.Missing.Value;
xlapp =
new excel.ApplicationClass();
xlwb = xlapp.Workbooks.Add(misvalue);
xlws = (excel.
Worksheet)xlwb.Worksheets.get_Item(1);
////////////////////////////////////////////
/////////to save column names///////////////
//////////////////////////////////////////////
//for (int i = 0; i < dataGridView1.Columns.Count; i++)
//{
// int x = i;
// x = x + 1;
// xlws.Cells[1, x] = (string)dataGridView1.Columns[i].Name;
//}
///////////////////////////////////////////////
//////////to save data from datagridview///////
///////////////////////////////////////////////
int q = 0;
for (int a = 2; a <= 2; a++)//take dataset table of row count to check loop condition
{
for (int b = 1; b <= dataGridView1.Columns.Count; b++)
{
xlws.Cells[a, b] = dataGridView1.Rows[0].Cells[q].Value.ToString();
//only columns are saves not rows
q++;
/////////////////////////////////////////////////////////////////////
////convert any type of data to string type and save in excel file///
/////////////////////////////////////////////////////////////////////
}
/////////////////////////////////////////////////////////////////
//take another value to increase row count then save rows data///
/////////////////////////////////////////////////////////////////
}
//xlws.Cells[1, 2] = h2;
//xlws.Cells[1, 3] = h3;
//xlws.Cells[2, 1] = s;
//xlws.Cells[2, 2] = s1;
//xlws.Cells[2, 3] = s2;
// xlwb.SaveAs("C:\\Users\\"+xxx +"\\Desktop\\excel\\arjun.xls", excel.XlFileFormat.xlWorkbookNormal, misvalue, misvalue, misvalue, misvalue, excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue, misvalue, misvalue);
xlwb.SaveAs(
"" + saveFileDialog1.FileName + ".xls", excel.XlFileFormat.xlWorkbookNormal, misvalue, misvalue, misvalue, misvalue, excel.XlSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue, misvalue, misvalue);
xlwb.Close(
true, misvalue, misvalue);
xlapp.Quit();
MessageBox.Show("created");
}
fill datagridview with the data which u wanna store in excel
Satyapriya NayakPosted Jan 1, 2013, 9:02 AM