第6章 Excel报表开发

Excel 对象的说明:
Application对象:它是Excel中最大的对象.
Workbook对象:表示一个Excel工作薄文件,用来创建一个Excel.
WorkSheet对象:它包含于Workbook中,表示一个Excel工作表,一个Workbook中可以包含多个WorkSheet.
Range对象:它包含于WorkSheet中,表示一个或多个连续的单元格.

读取或写入Excel需要引入的命名空间是:Microsoft.Office.Interop.Excel.
using Excel=Microsoft.Office.Interop.Excel; 顺便说一下:这是using的用法之一.
using的用法之二:释放对象
如COM对象,Graphics类,Sqlconnection,水印等对像必须释放.

Excel实例一:将数据库的数据导出Excel(保存在服务器)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Reflection;

namespace practice
{
    public partial class ExcelYuan : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Excel.Application app = new Excel.Application();    
            Excel.Workbook book = app.Workbooks.Add(true);    //创建Excel空工作薄
            Excel.Worksheet sheet=(Excel.Worksheet)book.Worksheets[1];    //创建Excel新工作表
            sheet.Name = "学员信息";    //设置工作表名称
            DataSet ds = SqlHelper.GetDataSet("select * from stuInfo");    从数据库绑定数据

        //创建Excel列名
            for (int col = 0; col < ds.Tables[0].Columns.Count; col++)
            {
                sheet.Cells[1, col + 1] = ds.Tables[0].Columns[col].ColumnName;
            }
        //创建Excel行
            for (int row = 0; row < ds.Tables[0].Rows.Count; row++)
            {
                for (int col = 0; col < ds.Tables[0].Columns.Count; col++)
                {
                    sheet.Cells[row + 2, col + 1] = ds.Tables[0].Rows[row][col].ToString();
                }
            }
            book.SaveAs(Server.MapPath("~/Upload/messInfo.xls"),Missing.Value,Missing.Value,"bbb");    //文件只读密码设为"bbb"
            app.Workbooks.Close();    
            app.Quit();    //关闭释放资源

        }
    }
}

Excel实例二:引用Aspose.Cells组件创建Excel(保存在客户端)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Aspose.Cells;
using System.Data;
using Excel = Microsoft.Office.Interop.Excel;

namespace practice
{
    public partial class Aspose_cells : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Workbook wb = new Workbook();
            Worksheet ws = wb.Worksheets.Add("员工信息");
            Cells cell = ws.Cells;
            cell["A1"].PutValue("编号");
            cell["B1"].PutValue("姓名");
            cell["C1"].PutValue("性别");
            cell["D1"].PutValue("年龄");
            DataSet ds = SqlHelper.GetDataSet("select * from stuInfo");
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                { 
                    cell[i+1,(byte)j].PutValue(ds.Tables[0].Rows[i][j]);
                }
            }
            SaveOptions so = wb.SaveOptions;
            so.SaveFormat = SaveFormat.Excel97To2003;
            wb.Save(Response,"~/Upload/text.xls",ContentDisposition.Attachment,so);
        }
    }
}

Excel实例三:将Excel中的数据导入数据库中
页面设计:1个Grview和1个Button;

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Text;
using System.Diagnostics;
using System.IO;
using Microsoft.Office.Interop.Excel;
using Excel = Microsoft.Office.Interop.Excel;

namespace practice
{
    public partial class ExcelToGrview : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        public DataSet GetData(string filepath)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";Data Source="+filepath);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet2$]",conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        public bool ExecuteNonquery(string filepath)
        {
            OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\";Data Source=" + filepath);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [Sheet2$]",conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            string sql = string.Empty;
            for(int i=0;i<ds.Tables[0].Rows.Count;i++)
            {
                sql += string.Format("insert into student values('{0}','{1}','{2}','{3}');", ds.Tables[0].Rows[i][0].ToString(), ds.Tables[0].Rows[i][1].ToString(), ds.Tables[0].Rows[i][2].ToString(), ds.Tables[0].Rows[i][3].ToString());
            }
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            return cmd.ExecuteNonQuery() > 0 ? true : false;
           
        }

        protected void btnData_Click(object sender, EventArgs e)
        {
            string path = Server.MapPath("~/student.xls");
            if (ExecuteNonquery(path))
            {
                Response.Write("<script>alert('数据导入成功')</script>");
            }
            else
            {
                Response.Write("<script>alert('数据导入成功')</script>");
            }
        }
    }

  
}