Excel报表开发（主要讲Excel的导入和导出）

一、Excel数据导入

　　　　　　连接字符串Excel2003版:

OleDbConnection conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Extended properties=\"Excel 8.0;\";Data Source=" + path);

　　　　　　连接字符串Excel2007版：

string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + path + ";" + "Extended Properties=Excel 12.0;";
            OleDbConnection conn = new OleDbConnection(strConn);

 

　　　　　　1、将Excel的数据导入数据库，后台代码

　　　　　　　　　　

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.OleDb;

namespace 操作excel
{
    public partial class excelToDataBase : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        public string ExportExcel(string path)
        {
            OleDbConnection conn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Extended properties=\"Excel 8.0;\";Data Source=" + path);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select* from [sheet1$]", 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());
                

            }
            int rowcount = DbHelper.ExecuteSql(sql);
            if (rowcount>0)
            {
                
                return "ok";
            }
            else
            {
                
                return "no";
            }

       
        }

        protected void btnExport_Click(object sender, EventArgs e)
        {
            string path = Server.MapPath("student.xls");
            if (ExportExcel(path) == "ok")
            {
                this.lbltext.Text="导入数据成功!";
            }
            else
            {
                this.lbltext.Text = "导入失败";
            }
        }
    }
}

前台代码：

      
    <asp:Button ID="btnExport" runat="server" Text="导入数据" 
        onclick="btnExport_Click" /><br />
        <asp:Label ID="lbltext" runat="server" Text=""></asp:Label>

 　　　　　　2、将Excel数据导入Gridview

 

    public partial class excelToGridView : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string path = Server.MapPath("~/student.xls");
            gvExcel.DataSource = Getexcel(path);
             gvExcel.DataBind();   

        }

        public DataSet Getexcel(string path)
        { 
            OleDbConnection conn=new OleDbConnection ("provider=Microsoft.Jet.OLEDB.4.0;Extended properties=\"Excel 8.0;\";Data Source="+path);
            conn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select* from [sheet1$]", conn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            conn.Close();
            
            return ds;
        }

二、Excel对象模型

　　　　需要右键添加引用Excel  Library

　　　　1、将数据库中的数据写入Excel，操作服务器（保存到服务器）

 protected void btnExport_Click(object sender, EventArgs e)
        {
            //创建Excel Application对象
            Excel.Application app = new Excel.Application();
            //创建workBook对象
            Excel.Workbook workbook = app.Workbooks.Add(true);//需要获取模板
            //创建worksheet对象
            Excel.Worksheet worksheet = workbook.Worksheets[1];
            //设置工作表名称
            worksheet.Name = "学生信息";
            DataSet ds = DbHelper.Query("select stuNo as 学好,stuName as 姓名,stuSex as 性别,sruClassNo as 班级 from student");
            //创建Excel名
            for (int col = 0; col < ds.Tables[0].Columns.Count; col++)
            {
                worksheet.Cells[1, col + 1] = ds.Tables[0].Columns[col].ColumnName;

            }
            int row = 2;
        //创建Excel行
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                {
                    worksheet.Cells[row, j + 1] = ds.Tables[0].Rows[i][j].ToString();
                }
                row++;
            }
        //保存
            workbook.SaveAs(Server.MapPath("~/Excel/student.xls"));
            app.Workbooks.Close();
        //退出Excel
            app.Quit();
        }

　　　　2、将数据库中的数据写入Excel，操作客户端（保存到客户端）

　　　　需要使用Aspose.Cells组件

 DataSet ds = DbHelper.Query("select stuNo,stuName,stuSex,sruClassNo from student");
            if (ds.Tables[0].Rows.Count>0)
            {
                Workbook wb = new Workbook();
                Worksheet ws = wb.Worksheets.Add("学生信息");
                Cells cells = ws.Cells;
                cells["A1"].PutValue("学号");
                cells["B1"].PutValue("姓名");
                cells["C1"].PutValue("性别");
                cells["D1"].PutValue("班级");
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                    {
                        cells[i + 1, (byte)j].PutValue(ds.Tables[0].Rows[i][j]);
                    }
                }

                SaveOptions so = wb.SaveOptions;
                so.SaveFormat = SaveFormat.Auto;
                wb.Save(HttpContext.Current.Response,"stud.xls",ContentDisposition.Attachment,so);
            }