c# 代码创建Excel文件 并且 将数据导出Excel

 导入Microsoft.Office.Interop.Excel.dll

using System.IO;

 #region 创建Excel
    /// <summary>
    /// 创建Excel
    /// </summary>
    /// <param name="fileName">文件名称</param>
    /// <param name="sheetName">sheet名称</param>
    private void CreateExcel(string fileName,string sheetName)
    {
        Microsoft.Office.Interop.Excel.ApplicationClass my = new Microsoft.Office.Interop.Excel.ApplicationClass();

        my.Visible = true;
        object objMissing = System.Reflection.Missing.Value;

 

        //打开工作簿   
        Microsoft.Office.Interop.Excel.Workbook mybook = (Microsoft.Office.Interop.Excel.Workbook)my.Workbooks.Add(1);   //   1表示只建一个表   

        //mybook.Worksheets.Add(objMissing,objMissing,1,objMissing);//添加sheet   
        if (sheetName == "")
        {
            sheetName = "steet1";
        }
        else
        {
            ((Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1]).Name = sheetName;//将sheet1的名称改为zhu   
        } 
        Microsoft.Office.Interop.Excel.Worksheet mysheet = (Microsoft.Office.Interop.Excel.Worksheet)mybook.Worksheets[1];
        ((Microsoft.Office.Interop.Excel.Range)mysheet.Cells[2, 3]).EntireRow.Insert(0,0);     //添加行   

        //保存   
        mybook.SaveAs("d://" + fileName + ".xls", objMissing, objMissing, objMissing,   //Excel.XlSaveAsAccessMode.xlShared   
        objMissing, objMissing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared,
        objMissing, objMissing, objMissing,
        objMissing, objMissing);
        mybook = null;
        my.Quit();
        my = null;
    }
    #endregion

//------------------------------------------------------------------
 #region 导入Excel
    /// <summary>
    /// 导入Excel
    /// </summary>
    /// <param name="dv">数据源</param>
    /// <param name="path">路径</param>
    /// <param name="title1">标题</param>
    /// <param name="heji">合计</param>
    public void inputExcel(DataSet dv, string path, string title1, string heji)
    {
        try
        {
            FileInfo fi = new FileInfo(path);
            fi.Delete();
        }
        catch
        {
        }

        Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
        object oMissing = System.Reflection.Missing.Value;
        Microsoft.Office.Interop.Excel.Workbook book = excel.Workbooks.Add(oMissing);

        int rowIndex = 3;
        int colIndex = 0;

        excel.Cells[1, 1] = title1;

        foreach (DataColumn dc in dv.Tables[0].Columns)
        {
            colIndex++;
            excel.Cells[rowIndex, colIndex] = dc.ColumnName;
            ((Microsoft.Office.Interop.Excel.Range)excel.Cells[rowIndex, colIndex]).ColumnWidth = 18.8;
        }

        foreach (DataRow row in dv.Tables[0].Rows)
        {
            rowIndex++;
            colIndex = 0;
            foreach (DataColumn dc in dv.Tables[0].Columns)
            {
                colIndex++;
                excel.Cells[rowIndex, colIndex] = row[dc.ColumnName].ToString();
                ((Microsoft.Office.Interop.Excel.Range)excel.Cells[rowIndex, colIndex]).ColumnWidth = 18.8;
            }
        }
        excel.Cells[rowIndex + 1, colIndex] = heji;

        Microsoft.Office.Interop.Excel.Sheets sheets = book.Worksheets;
        Microsoft.Office.Interop.Excel._Worksheet worksheet = (Microsoft.Office.Interop.Excel._Worksheet)sheets.get_Item(1);

        Microsoft.Office.Interop.Excel.Range range;
        range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, colIndex]);
        range.MergeCells = true;//合并   
        range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中   
        range.Font.Bold = true;
        range.Font.Size = 14;

        book.Saved = true;
        excel.UserControl = false;

        worksheet.SaveAs(path, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

        excel.Quit();
        oMissing = null;
        sheets = null;
        worksheet = null;
        book = null;
        excel = null;
        GC.Collect();
    }
    #endregion

//---------------------------------------------------------------
    #region 绑定数据
    private DataSet bangding()
    {
        using (SqlConnection conn = new SqlConnection("server=.;uid=sa;pwd=;database=pubs"))
        {
            conn.Open();
            string sql = "select job_id as 编号,job_desc as 排序,min_lvl as 最小, max_lvl as 最大 from jobs";
            SqlDataAdapter dar = new SqlDataAdapter(sql, conn);
            DataSet ds = new DataSet();
            dar.Fill(ds);

            this.GridView1.DataSource = ds;
            this.GridView1.DataBind();
            return ds;
        }
    }
    #endregion
//------------------------------------------------------------------

#region 获取合计
    private int num()
    {
        int num = 0;
        for (int i = 0; i < bangding().Tables[0].Rows.Count; i++)
        {
            num += Convert.ToInt32(bangding().Tables[0].Rows[i][3].ToString());
        }
        return num;
    }
    #endregion

//-------------------------------------------------------------------
发表于 @ 2008年12月13日　15:31:00 | 评论( 3 ) | 编辑| 举报| 收藏

旧一篇:c# 代码创建Excel文件 | 新一篇:Div 层的拖动 脚本lxl_sports 发表于2008年12月13日 15:39:07  IP:举报删除
直接拷入 直接可以使用lxl_sports 发表于2008年12月13日 15:39:58  IP:举报删除
直接拷入就可以使用~~！lxl_sports 发表于2008年12月13日 15:40:00  IP:举报删除
直接拷入就可以使用~~！

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/lxl_sports/archive/2008/12/13/3510389.aspx