使用c＃导出exell表中的数据 [英] to export the data in exell sheet using c#

问题描述

大家好，



我使用带有c＃的asp.net生成了一个应用程序。



这个概念是，当我点击按钮时，数据已经显示excel表，但在我的应用程序中它得到的几个错误，如不正确的语法接近数据。使用存储过程从数据库中检索数据，请给我一个这个应用程序的解决方案..



我的代码是：

Hi all,

I have generated one application using asp.net with c#.

The concept is,

when i click button, the data has been display excel sheet, but in my application its getting few errors like "incorrect syntax near datadownload". using stored procedure am retrieving the data from database, please give me a solution for this app..

my code is:

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.IO;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page 
{
    string s = ConfigurationManager.AppSettings["const"].ToString();
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    private DataTable Getdata(SqlCommand cmd)
    {
        DataTable dt = new DataTable();
        
        SqlConnection con = new SqlConnection(s);
        SqlDataAdapter da = new SqlDataAdapter();
        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;
        try
        {
            con.Open();
            da.SelectCommand = cmd;
            da.Fill(dt);
            return dt;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            con.Close();
            da.Dispose();
            con.Dispose();

        }
    }
    protected void download_Click(object sender, EventArgs e)
    {
       
            SqlConnection con = new SqlConnection(s);
            SqlCommand cmd = new SqlCommand("datadownload", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@Destinations", SqlDbType.VarChar);
           
            if (con.State == ConnectionState.Closed)
            {
                con.Open();
                cmd.ExecuteNonQuery();

            }
            con.Close();         
        DataTable dt = Getdata(cmd);

        GridView gv1 = new GridView();
        gv1.AllowPaging = false;
        gv1.DataSource = dt;
        gv1.DataBind();

        Response.Clear();
        Response.Buffer = true;
        Response.AddHeader("content-disposition", "attachment;filename=GridViewExport.xls");
        Response.Charset = "";
        Response.ContentType = "application/vnd.ms-excel";
        StringWriter sw = new StringWriter();
        HtmlTextWriter hw = new HtmlTextWriter(sw);

        for (int i = 0; i < gv1.Rows.Count; i++)
        {
            gv1.Rows[i].Attributes.Add("class", "textmode");

        }
        gv1.RenderControl(hw);
        string style = @"<style> .textmode { mso-number-format:\@; } </style>";
        Response.Write(style);
        Response.Output.Write(sw.ToString());
        Response.Flush();
        Response.End();
    }
}

推荐答案

希望这个例子可以帮助你....



您需要将此（Microsoft.Office.Interop.Excel）参考添加到您可以在添加参考中找到的解决方案 - > .Net选项卡

Hope this example help you....

You need to add this ( Microsoft.Office.Interop.Excel)reference to your solution you can find it in Add Reference-->.Net Tab

using System.ComponentModel;
private void btnGenerateExcel_Clickddd(object sender, EventArgs e)
{
// Start the BackgroundWorker.

backgroundWorker1.RunWorkerAsync();
 

// creating Excel Application
Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
 
// creating new WorkBook within Excel application
Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
 
// creating new Excelsheet in workbook
Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
 
// see the excel sheet behind the program
app.Visible = false;
 
// get the reference of first sheet. By default its name is Sheet1.
// store its reference to worksheet
worksheet = workbook.Sheets["Sheet1"];
worksheet = workbook.ActiveSheet;
 
// changing the name of active sheet
worksheet.Name = "Inspection Order Detail";
 


// storing header part in Excel
for (int i = 1; i < dataGridView1.Columns.Count + 1; i++)
{
worksheet.Cells[1, i] = dataGridView1.Columns[i - 1].HeaderText; // Header in Excel Sheet which u want to show
}
 
// storing Each row and column value to excel sheet
for (int i = 0; i < dataGridView1.Rows.Count - 1; i++)
{
for (int j = 0; j < dataGridView2.Columns.Count; j++) 
{
worksheet.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString(); // in ur case you can give first three colums
 
}
} 
 
// save the application
workbook.SaveAs("C:\\Users\\xxxxxx\\Desktop\\Temp\\output.xlsx", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
 
// Exit from the application
app.Quit();
 
releaseObject(worksheet);

releaseObject(workbook);
releaseObject(app);
 
MessageBox.Show("Excel file created , you can find the file C:\\Users\\xxxxxxx\\Desktop\\Temp\\output.xlsx");
}

这篇关于使用c＃导出exell表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！