C# 操作excel 的一点心得体会!

C# 操作excel 的一点心得体会!
引用命名空间

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;
using Excel;

代码

namespace TestExcel
{
    /**//// <summary>
    /// ExcelManage 的摘要说明。
    /// </summary>
    public partial class ExcelManage : System.Web.UI.Page
    {
        private SqlConnection conn;
        private SqlCommand cmd;
        private SqlDataAdapter ad;

        protected void Page_Load(object sender, System.EventArgs e)
        {
            // 在此处放置用户代码以初始化页面
        }
        /**//// <summary>
        /// 预览excel文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button1_Click(object sender, System.EventArgs e)
        {

            Application myExcel = new Application();//要在组件服务中添加network service(IIS 6),而不是asp.net(IIS 5);还要把标识改为"交互式用户"
            try
            {
                myExcel.Visible = true;
                myExcel.UserControl = true;//打开EXCEL
                string strURL = Server.MapPath("ExcelFile\\TempExcel.xls");
                myExcel.Application.Workbooks.Add(strURL);//加载选择的文件的内容
                myExcel.ActiveWorkbook.PrintPreview(true);


            }
            catch (Exception ex)
            {
                Response.Write("<script language=javascript>alert('" + ex.Message.ToString() + "')</script>");
            }
            finally
            {
                if (myExcel.Visible == true)
                {
                    myExcel.Visible = false;//关闭EXCEL  
                }
                myExcel.Quit();
                myExcel = null;
            }
        }
        /**//// <summary>
        /// 将数据导入excel文件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button2_Click(object sender, System.EventArgs e)
        {
            ApplicationClass myExcel = new ApplicationClass();//要在组件服务中添加network service(IIS 6),而不是asp.net(IIS 5);还要把标识改为"交互式用户"                
            try
            {
                myExcel.Application.Workbooks.Add(true);//加载选择的文件的内容
                //        myExcel.Rows.HorizontalAlignment= xlVAlignCenter ;
                myExcel.Visible = true;
                //写标题
                myExcel.ActiveCell.Cells[1, 3] = "操作excel示例";
                Excel.Range range;
                range = (Excel.Range)myExcel.ActiveCell.Cells[1, 3];
                range.Font.Name = "新宋体";
                range.Font.Bold = true;
                range.Font.Size = 10;

                //填入数据
                string ConnectionStr = "server=(local);uid=sa;pwd=sa;database=pubs";
                conn = new SqlConnection(ConnectionStr);
                string strsql = "select * from Employee";
                cmd = new SqlCommand(strsql, conn);
                ad = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                ad.Fill(ds);
                int col = 0;
                int row = 0;
                for (col = 0; col < ds.Tables[0].Columns.Count; col++)
                {
                    myExcel.ActiveCell.Cells[2, col + 1] = ds.Tables[0].Columns[col].Caption;
                    range = (Excel.Range)myExcel.ActiveCell.Cells[2, col + 1];
                    range.Font.Name = "新宋体";
                    range.Font.Bold = true;
                    range.Font.Size = 8;
                }

                for (row = 0; row < ds.Tables[0].Rows.Count; row++)
                {
                    for (col = 0; col < ds.Tables[0].Columns.Count; col++)
                    {
                        myExcel.ActiveCell.Cells[row + 3, col + 1] = ds.Tables[0].Rows[row][col].ToString();
                        range = (Excel.Range)myExcel.ActiveCell.Cells[row + 3, col + 1];
                        range.Font.Name = "新宋体";
                        range.Font.Size = 8;
                    }

                }
                string ExcelFileName = "ExcelFile\\TempExcel.xls";
                string FilePhysicialPathName = Request.PhysicalApplicationPath;

                //生成的Excel文件名
                string objectExcelFileName = Path.Combine(FilePhysicialPathName, ExcelFileName);

                if (File.Exists(objectExcelFileName))
                {
                    File.Delete(objectExcelFileName);
                }
                //将文件保存
                myExcel.Save(Server.MapPath("ExcelFile\\TempExcel.xls"));
            }
            catch (Exception ex)
            {
                Response.Write("<script language=javascript>alert('" + ex.Message.ToString() + "')</script>");
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                if (myExcel.Visible == true)
                {
                    myExcel.Visible = false;
                }
                myExcel.DisplayAlerts = false;
                myExcel.Quit();
                myExcel.DisplayAlerts = true;
                myExcel = null;
            }


        }
        /**//// <summary>
        /// 文件下载1
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button3_Click(object sender, EventArgs e)
        {
            string fileExcel = "TempExcel.xls";
            if (fileExcel != "")
            {
                //获得文件的存储路径
                string path = Server.MapPath("ExcelFile\\" + fileExcel);
                System.IO.FileInfo file = new System.IO.FileInfo(path);
                if (file.Exists)
                {
                    Response.Clear();
                    //通知浏览器下载文件而不是打开
                    Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode(file.Name, System.Text.Encoding.UTF8));
                    Response.AddHeader("Content-Length", file.Length.ToString());
                    Response.ContentType = "application/octet-stream";
                    Response.Filter.Close();
                    Response.WriteFile(file.FullName);
                    Response.End();
                }
                else
                {
                    Response.Write("This file does not exist.");
                }
            }

        }
        /**//// <summary>
        /// 文件下载2
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        protected void Button4_Click(object sender, EventArgs e)
        {
            string fileExcel = "TempExcel.xls";
            //取得的文件存储路径
            string filePath = Server.MapPath("ExcelFile\\");
            string fileName = filePath + "\\" + fileExcel;
            //以字符流的形式下载文件
            FileStream fs = new FileStream(fileName, FileMode.Open);
            byte[] bytes = new byte[(int)fs.Length];
            fs.Read(bytes, 0, bytes.Length);
            fs.Close();
            Response.ContentType = "application/octet-stream";
            //通知浏览器下载文件而不是打开
            Response.AddHeader("Content-Disposition", "attachment;  filename=" + HttpUtility.UrlEncode(fileExcel, System.Text.Encoding.UTF8));
            Response.BinaryWrite(bytes);
            Response.Flush();
            Response.End();
        }
}
}