如何在单击按钮的情况下将数据库表数据导出到C＃中的excel文件中？ [英] How to Export Database table data into excel file in C# on button click?
问题描述
嗨
如何将数据库表数据导出到excel文件中。
我有按钮点击代码，该代码为空excel文件。
现在，当我点击按钮时，数据库表数据将数据导出到excel文件中。
i需要一些帮助来完成这个步骤。
private void button1_Click_1（object sender，EventArgs e）
{
Microsoft.Office.Interop.Excel.Application xl = default（Microsoft.Office.Interop.Excel.Application）;
xl = new Microsoft.Office.Interop.Excel.Application（）;
Microsoft.Office.Interop.Excel.Workbook wb = default（Microsoft.Office.Interop.Excel.Workbook）;
wb = xl.Workbooks.Add（）;
Microsoft.Office.Interop.Excel.Worksheet ws = default（Microsoft.Office.Interop.Excel.Worksheet）;
ws = wb.ActiveSheet;
xl.Visible = t rue;
}
谢谢奖励
sam.198979
Hi
How to export database table data into excel file.
I have button click code which gets empty excel file.
Now when i click on button the database table data export the data into excel file.
i need some help to do this steps.
private void button1_Click_1(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application xl = default(Microsoft.Office.Interop.Excel.Application);
xl = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook wb = default(Microsoft.Office.Interop.Excel.Workbook);
wb = xl.Workbooks.Add();
Microsoft.Office.Interop.Excel.Worksheet ws = default(Microsoft.Office.Interop.Excel.Worksheet);
ws = wb.ActiveSheet;
xl.Visible = true;
}
Thanks Regrades
sam.198979
推荐答案
private void ExportToExcel（string strFileName，GridView gv）
{
Response.ClearContent（）;
Response.AddHeader（content-disposition，附件; filename =+ strFileName）;
Response.ContentType =application / excel;
System.IO.StringWriter sw = new System.IO.StringWriter（）;
HtmlTextWriter htw = new HtmlTextWriter（sw）;
gv.RenderControl（htw）;
Response.Write（sw.ToString（））;
Response.End（）;
}
protected void btnexport_Click （对象发送者，EventArgs e）
{
DataTable dt = new DataTable（）;
SqlConnection objcon = new SqlConnection（System.Configuration。 ConfigurationManager.ConnectionStrings [connstr]。ToString（））;
SqlDataAdapter objda = new SqlDataAdapter（select * from attendance，objcon）;
objda.Fill（ dt）;
gvreport.DataSource = dt;
gvreport.DataBind（）;
ExportToExcel（Report.xls，gvreport）;
gvreport = null;
gvreport.Dispose（）;
}
public override void VerifyRenderingInServerForm（Control control）
{
/ *确认在运行时为指定的ASP.NET
服务器控件呈现HtmlForm控件。 * /
}
private void ExportToExcel(string strFileName, GridView gv)
{
Response.ClearContent();
Response.AddHeader("content-disposition", "attachment; filename=" + strFileName);
Response.ContentType = "application/excel";
System.IO.StringWriter sw = new System.IO.StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);
gv.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();
}
protected void btnexport_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
SqlConnection objcon = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connstr"].ToString());
SqlDataAdapter objda = new SqlDataAdapter("select * from attendance", objcon);
objda.Fill(dt);
gvreport.DataSource = dt;
gvreport.DataBind();
ExportToExcel("Report.xls", gvreport);
gvreport = null;
gvreport.Dispose();
}
public override void VerifyRenderingInServerForm(Control control)
{
/* Confirms that an HtmlForm control is rendered for the specified ASP.NET
server control at run time. */
}
I tried this code it's working.
private void button1_Click(object sender, EventArgs e)
{
SqlConnection cnn;
string connectionstring = null;
string sql = null;
string data = null;
int i = 0;
int j = 0;
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Microsoft.Office.Interop.Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
connectionstring ="Data Source=IN-WTS-SAM;Initial Catalog=MSNETDB;Integrated Security=True;Pooling=False";
cnn = new SqlConnection(connectionstring);
cnn.Open();
sql = "SELECT * FROM Emp";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);
for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
xlWorkSheet.Cells[i + 1, j + 1] = data;
}
}
xlWorkBook.SaveAs("informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file D:\\Sam-informations.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
But in Excel it's displaying only columns data only.
I Want with column names also in excel file.
can anyone help.
谢谢
sam.198979
Thanks
sam.198979
我知道，已经有一段时间回答但还没有帮助别人：
对于那些想要在Excel工作表中添加列标题的人：
I know, it's been a while to answer but yet to help others:
For those who want to add column header in the excel sheet:
foreach (DataTable dt in ds.Tables)
{
foreach (DataColumn dc in dt.Columns)
{
columnName.Add(dc.ColumnName.ToString());
}
}
for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
xlWorkSheet.Cells[1, j + 1] = columnName[j].ToString();
}
这篇关于如何在单击按钮的情况下将数据库表数据导出到C＃中的excel文件中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
