.net导出数据到Excel (安装office excel环境情况)

今天公司要求将商城的产品信息分类导入到Excel表格中，查了些资料，终于解决，希望能对以后的工作有所帮助

首先，.net 自带的有操作Office 的方法，但在使用前需要先添加 COM 引用

 

 引用添加之后，就可以使用.net 自带的各种方法了。
操作类：
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using Excel = Microsoft.Office.Interop.Excel;

/// <summary>
///CreateExcel 的摘要说明
/// </summary>
public class CreateExcel
{
    public CreateExcel()
    {

    }

    /// <summary>
    /// 将数据集中的数据导出到EXCEL文件
    /// </summary>
    /// <param name="dataSet">输入数据集</param>
    /// <param name="fileName">EXCEL文件路径</param>
    /// <returns></returns>
    public bool DataSetToExcel(DataSet dataSet, string fileName)
    {
        DataTable dataTable = dataSet.Tables[0];
        int rowNumber = dataTable.Rows.Count;//不包括字段名
        int columnNumber = dataTable.Columns.Count;
        int colIndex = 0;

        if (rowNumber == 0)
        {
            return false;
        }

        //建立Excel对象

        Excel.ApplicationClass oExcel = new Excel.ApplicationClass();
        object oMissing = System.Reflection.Missing.Value;

        oExcel.Workbooks.Add(oMissing);
        Excel.Workbook oBook = oExcel.Workbooks[1];
        Excel.Worksheet oSheet = (Excel.Worksheet)oBook.Sheets[1];
        oSheet.Name = "365电气商城产品信息";

        Excel.Range rg;

 //生成字段
        for (int j = 0; j < columnNumber; j++)
        {
            rg = ((Excel.Range)oSheet.Cells[1, j + 1]);
            rg.FormulaR1C1 = dataTable.Columns[j].ColumnName;
        }

 //填充内容
        for (int i = 0; i < rowNumber; i++)
        {
            for (int j = 0; j < columnNumber; j++)
            {
                rg = ((Excel.Range)oSheet.Cells[i + 2, j + 1]);
                rg.FormulaR1C1 = dataTable.Rows[i][j].ToString();
            }
        }
        rg = null;

        oBook.SaveAs(fileName, Excel.XlFileFormat.xlExcel9795, oMissing, oMissing, oMissing, oMissing, Excel.XlSaveAsAccessMode.xlExclusive,
        oMissing, oMissing, oMissing, oMissing, oMissing);
        oExcel.Workbooks.Close();
        oExcel.Quit();

        oSheet = null;
        oBook = null;
        oExcel = null;

        GC.Collect();

        return true;
    }
}


aspx页面：
  <asp:Button ID="BtnPrint" runat="server" Text="确定" OnClick="BtnPrint_Click" />

后台代码CS：
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.IO;
using System.Reflection;
using System.Text;


    SHOP.BLL.ProductInfo proInfo = new SHOP.BLL.ProductInfo();
    SHOP.BLL.ProductTypeA proA = new SHOP.BLL.ProductTypeA();
    SHOP.BLL.ProductTypeB proB = new SHOP.BLL.ProductTypeB();
    SHOP.BLL.ProductTypeC proC = new SHOP.BLL.ProductTypeC();

    protected void BtnPrint_Click(object sender, EventArgs e)
    {
        //DataSet dsA_Name = proA.getProductTypeA();
        DataTable dtA_Name = proA.getProductTypeA();

        //创建分类文件夹
        for (int i = 0; i < dtA_Name.Rows.Count; i++)
        {
            string file = MapPath("../Excel/" + dtA_Name.Rows[i]["ptaname"].ToString());
            //判断文件夹是否存在
            if (!Directory.Exists(file))
            {
                Directory.CreateDirectory(file);          //创建文件夹
            }
        }

        //得到1、3级分类名称
        DataSet dsC_Name = proC.GetProACName();
        DataTable dtC_Name = dsC_Name.Tables[0];
        for (int i = 0; i < dtC_Name.Rows.Count; i++)
        {
            string path = MapPath("../Excel/" + dtC_Name.Rows[i]["ptaname"].ToString() + "/" + dtC_Name.Rows[i]["ptbname"].ToString() + "_" + dtC_Name.Rows[i]["ptcname"].ToString() + ".xls");
            //得到分类下的产品信息
            DataSet ds_info = proInfo.GetProCInfo(dtC_Name.Rows[i]["ptcid"].ToString());
            //创建类
            CreateExcel excel = new CreateExcel();
            bool flag = excel.DataSetToExcel(ds_info, path);
            if (flag == true)
                Page.ClientScript.RegisterStartupScript(this.GetType(), "dqwe", "<script>alert('OK')</script>");
        }
    }