如何以赢取的格式将数据从C#ASP.NET放入excl文件中 [英] How put data in excl file from C# ASP.NET in our won format

问题描述

我将网格视图与某些列和行中的数据绑定在一起(即6列和10行).现在我需要执行以下任务.
1.我需要将gridview数据导出到excel文件.导出的数据需要以特定格式放置在excel工作表中.即
1.标题列包含粗体和背景色(任何特定颜色).
2.根据某些条件，在每列的详细信息中，我需要使用任何背景色(单元格包含任何数据)对单元格进行带色字体.
我要求对此提供帮助.
谢谢
Mahesh

Hi,
I am binding the gridview with data in some columns and rows (i.e 6 columns and 10 rows). Now i need to perform the below tasks.
1. I need to export the gridview data to excel file. The exported data need to be placed in excel sheet with specific format. i.e
1. Header Columns contain Bold Font and Background color (any specific color).
2. In details for each column row, based on some conditions, i need to make a Colored the Font of cell with any Background color (The cell contain any data).
I request please help me on this.
Thanks
Mahesh

推荐答案

此处的示例代码.该代码直接在表单上完成.但是可轻松修改以与类和对象一起使用. ''已包含Windows窗体应用程序和ASP.NET应用程序)

1.对于Windows窗体应用程序...

Here''s sample code..This was done directly on the form..but its easily modifiable to be used with Classes and Objects..(I''ve included both Windows form app and ASP.NET app)

1. For Windows Form Application...

using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel; 
namespace WindowsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        
        // From Database to DataGridView
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cnn ;
            string connectionString = null;
            string sql = null;
            connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
            cnn = new SqlConnection(connectionString);
            cnn.Open();
            sql = "SELECT * FROM Product";
            SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
            DataSet ds = new DataSet();
            dscmd.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];
        }

        // From Datagrid View to Excel
        private void button2_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            object misValue = System.Reflection.Missing.Value;
            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            int i = 0;
            int j = 0; 
            for (i = 0; i <= dataGridView1.RowCount  - 1; i++)
            {
                for (j = 0; j <= dataGridView1.ColumnCount  - 1; j++)
                {
                    DataGridViewCell cell = dataGridView1[j, i];
                    xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
                }
            }
            xlWorkBook.SaveAs("csharp.net-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 c:\\csharp.net-informations.xls");
        }
        
/* Releasing System Resources (Interop Objects Garbage Collection) */
        
        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();
            }
        }
    }
}

2.对于ASP.NET应用程序..

2. For ASP.NET application..

private void toExcel()
   {
       Response.Clear();
       Response.Buffer = true;
       //Response.Charset = "UTF-8";
       Response.AppendHeader("Content-Disposition", "attachment;filename=schoolsinfo.xls");
       Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
       Response.ContentType = "application/ms-excel";
       this.EnableViewState = false;
       System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("EN-US", true);
       System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
       System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
       GridView1.RenderControl(oHtmlTextWriter);
       Response.Write(oStringWriter.ToString());
   }

此外，还需要注意一个重要的问题:从dataview到excel单元输出的数据格式.因此，如果Excel中输出的数据格式不正确，则可以将DataGrid1_ItemDataBound事件配置为inder以定义数据格式:

Besides, the important issue about this is needed to pay attention: the format of data which is from dataview to excel cell output. So if the data format output in Excel is incorrect, you can configure the DataGrid1_ItemDataBound Event to inder to define the data format:

private void DataGrid1_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        e.Item.Cells[0].Attributes.Add("style", "vnd.ms-excel.numberformat:@");// This can convert the data in Number to in Text.
        e.Item.Cells[1].Attributes.Add("style", "vnd.ms-excel.numberformat:@");
    }
}

如果以上帮助..接受答案并投票..:)

If the above helps..Accept answer and Vote..:)

查看此文章. 单元格格式中的技巧应该可以为您提供帮助.

Look at this article . The trick in Cell Formatting should help you.

这篇关于如何以赢取的格式将数据从C#ASP.NET放入excl文件中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！