通过C#和ASP.NET通过动态生成Excel将Datareader转换为excel文件 [英] Datareader to excel file using C# with ASP.NET by dynamically generating excel

问题描述

我刚刚开始在ASP.NET中进行编码.我是新来的论坛.这是我的第一篇文章.

我是一名学生，在我的IT部门担任网络开发人员.作为我工作的一部分.

1)我将数据从数据库传输到datareader.

2)首先，我使用存储的过程从一个表中读取列名，并使用htmlcontent.Append()转换为htmlcontent.

3)我正在从另一个表中读取数据，然后再次使用新行和制表符空格将数据附加到htmlcontent.

终于可以正常工作了(它正在创建Excel工作表并插入数据)，但是每个数据字段都没有固定在Excel工作表的单元格中.

empIdempnameempadate

233464646muralikrishna04/08/10像这样.

它像文本一样打印，无法将每列都适合excell工作表的每个单元格以及数据.我正在发送代码.

我在C#中需要它.

Hi,

I just started coding in ASP.NET. I am new to forums. This is my first post.

I am a student and working asa web devolper in my unibersity IT department. As part of my work..

1) i got the data from database to datareader.

2) First i am reading the column names from one table by using stored proceadure, in to htmlcontent using htmlcontent.Append().

3) I am reading the data from another table and once again i appended data to htmlcontent with with new line and tab spaces.

Finally its working(it is creating excel sheet and inserting data) but each datafield is not fixing in to cell of a excel sheet.

empIdempnameempadate

233464646muralikrishna04/08/10 so on like this.

it is printing like a text not fitting each column to each cell of excell sheet and the data also. I am sending my code.

I need it in C#.

protected void Button1_Click(object sender, EventArgs e)
    {
        string strErrorMessage, sep, strFileName, SuccessFlag,strSuccessFlag;
        sep = "";
        SuccessFlag = "";
        strErrorMessage = "";
        strFileName = "Task_List";
        StringBuilder htmlcontent = new StringBuilder();
        int i;
        DataAccess objDataAccess1 = new DataAccess();
        SqlCommand objCommand1 = new SqlCommand();
        objDataAccess1.Connection_Open("BTB");
        try
        {
            objCommand1.Connection = objDataAccess1.m_sqlConnection;
            Label1.Text = objCommand1.Connection.State.ToString();
            objCommand1.CommandText = "fetch_col";
            objCommand1.CommandType = CommandType.StoredProcedure;
            reader = objCommand1.ExecuteReader();
        }
        catch
        {

        }
        
        int n;
        string sValue = String.Empty;
        if (reader.HasRows)
        {
            sValue = "\t";
            while (reader.Read())
            {
                
                for (n = 0; n <reader.FieldCount; n++)
                {
                    htmlcontent.Append(reader.GetValue(n) + sValue);
                }
            }
            htmlcontent.Append("\n");
        }
        reader = null;
        try
        {
            strSuccessFlag = "";
           strErrorMessage = "";
           DataAccess objDataAccess = new DataAccess();
           SqlCommand objCommand = new SqlCommand();
           try
           {
               objDataAccess.Connection_Open("BTB");
               objCommand.Connection=objDataAccess.m_sqlConnection;
               objCommand.CommandText = "test1";
               objCommand.CommandType = CommandType.StoredProcedure;
               objCommand.Parameters.Add(new SqlParameter("@p_emp_id", SqlDbType.VarChar, 10));
               objCommand.Parameters["@p_emp_id"].Value = strEmpId;
               reader=objCommand.ExecuteReader();
           }
           catch
           {

           }
           //finally
           //{
           //    objCommand =null;
           //    objDataAccess = null;
           //    reader = null;
           //}
           //DataTable dt = reader.GetSchemaTable();
            
           if (reader.HasRows)
           {
               sValue = "\t";
               while (reader.Read())
               {

                   for (n = 0; n < reader.FieldCount; n++)
                   {
                       htmlcontent.Append(reader.GetValue(n) + sValue + sValue + sValue + sValue + sValue + sValue);
                   }
                   htmlcontent.Append("\n");
               }
               
           }

        }
        catch
        {
 
        }
        string strcontent, strcontent1;
        strcontent = htmlcontent.ToString();
        
        strcontent1 = fCleanString(strcontent);
        strcontent1 = strcontent.Replace("\n", System.Environment.NewLine);
        strcontent1 = strcontent1.Replace("\t", "    ");
       
        Response.ContentEncoding = System.Text.Encoding.UTF8;
        HttpContext.Current.Response.ClearContent();
        HttpContext.Current.Response.ClearHeaders();
        HttpContext.Current.Response.Charset = "";
        HttpContext.Current.Response.Buffer = true;
        HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
        HttpContext.Current.Response.AddHeader("content-disposition", "attachment;" + "filename=" + strFileName + ".xls");

        //HttpContext.Current.Response.Write();
        HttpContext.Current.Response.Flush();
        HttpContext.Current.Response.Close();

    }
    public string fCleanString(string sstring)
    {
        string sReturn;
         int i;
        sReturn = "";
        for(i=1;i<sstring.Length;i++)
        {   string  a=Mid(sstring, i, 1);
        byte[] asciiBytes = Encoding.ASCII.GetBytes(a);
           //j= Convert.ToInt32(a);
        if (asciiBytes[0] >= 32 && asciiBytes[0] <= 177)
            sReturn = sReturn + Mid(sstring, i, 1);

        }
           return sReturn;
    }
    public static string Mid(string param, int startIndex, int length)
    {
        //start at the specified index in the string ang get N number of
        //characters depending on the lenght and assign it to a variable
        string result = param.Substring(startIndex, length);
        //return the result of the operation
        return result;
    }

推荐答案

也许会有所帮助， C#用于将数据导出到CSV/Excel文件的类库 [ ^ ]

Maybe this will help, C# class library for exporting data to CSV/Excel file[^]

这篇关于通过C#和ASP.NET通过动态生成Excel将Datareader转换为excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！