C#自定义导出数据到Excel的类实例

本文实例讲述了c#自定义导出数据到excel的类。分享给大家供大家参考。具体如下：

c#自定义excel操作类，可以用于将datatable导出到excel文件，从excel文件读取数据。

using system;
using system.io;
using system.data;
using system.collections;
using system.data.oledb;
using system.web;
using system.web.ui;
using system.web.ui.webcontrols;
namespace dotnet.utilities
{
  /// <summary>
  /// excel操作类
  /// </summary>
  /// microsoft excel 11.0 object library
  public class excelhelper
  {
    #region 数据导出至excel文件
    /// </summary>
    /// 导出excel文件，自动返回可下载的文件流
    /// </summary>
    public static void datatable1excel(system.data.datatable dtdata)
    {
      gridview gvexport = null;
      httpcontext curcontext = httpcontext.current;
      stringwriter strwriter = null;
      htmltextwriter htmlwriter = null;
      if (dtdata != null)
      {
        curcontext.response.contenttype = "application/vnd.ms-excel";
        curcontext.response.contentencoding = system.text.encoding.getencoding("gb2312");
        curcontext.response.charset = "utf-8";
        strwriter = new stringwriter();
        htmlwriter = new htmltextwriter(strwriter);
        gvexport = new gridview();
        gvexport.datasource = dtdata.defaultview;
        gvexport.allowpaging = false;
        gvexport.databind();
        gvexport.rendercontrol(htmlwriter);
        curcontext.response.write("<meta http-equiv=\"content-type\" content=\"text/html;charset=gb2312\"/>" + strwriter.tostring());
        curcontext.response.end();
      }
    }
    /// <summary>
    /// 导出excel文件，转换为可读模式
    /// </summary>
    public static void datatable2excel(system.data.datatable dtdata)
    {
      datagrid dgexport = null;
      httpcontext curcontext = httpcontext.current;
      stringwriter strwriter = null;
      htmltextwriter htmlwriter = null;
      if (dtdata != null)
      {
        curcontext.response.contenttype = "application/vnd.ms-excel";
        curcontext.response.contentencoding = system.text.encoding.utf8;
        curcontext.response.charset = "";
        strwriter = new stringwriter();
        htmlwriter = new htmltextwriter(strwriter);
        dgexport = new datagrid();
        dgexport.datasource = dtdata.defaultview;
        dgexport.allowpaging = false;
        dgexport.databind();
        dgexport.rendercontrol(htmlwriter);
        curcontext.response.write(strwriter.tostring());
        curcontext.response.end();
      }
    }
    /// <summary>
    /// 导出excel文件，并自定义文件名
    /// </summary>
    public static void datatable3excel(system.data.datatable dtdata, string filename)
    {
      gridview dgexport = null;
      httpcontext curcontext = httpcontext.current;
      stringwriter strwriter = null;
      htmltextwriter htmlwriter = null;
      if (dtdata != null)
      {
        httputility.urlencode(filename, system.text.encoding.utf8);
        curcontext.response.addheader("content-disposition", "attachment;filename=" + httputility.urlencode(filename, system.text.encoding.utf8) + ".xls");
        curcontext.response.contenttype = "application nd.ms-excel";
        curcontext.response.contentencoding = system.text.encoding.utf8;
        curcontext.response.charset = "gb2312";
        strwriter = new stringwriter();
        htmlwriter = new htmltextwriter(strwriter);
        dgexport = new gridview();
        dgexport.datasource = dtdata.defaultview;
        dgexport.allowpaging = false;
        dgexport.databind();
        dgexport.rendercontrol(htmlwriter);
        curcontext.response.write(strwriter.tostring());
        curcontext.response.end();
      }
    }
    /// <summary>
    /// 将数据导出至excel文件
    /// </summary>
    /// <param name="table">datatable对象</param>
    /// <param name="excelfilepath">excel文件路径</param>
    public static bool outputtoexcel(datatable table, string excelfilepath)
    {
      if (file.exists(excelfilepath))
      {
        throw new exception("该文件已经存在！");
      }
      if ((table.tablename.trim().length == 0) || (table.tablename.tolower() == "table"))
      {
        table.tablename = "sheet1";
      }
      //数据表的列数
      int colcount = table.columns.count;
      //用于记数，实例化参数时的序号
      int i = 0;
      //创建参数
      oledbparameter[] para = new oledbparameter[colcount];
      //创建表结构的sql语句
      string tablestructstr = @"create table " + table.tablename + "(";
      //连接字符串
      string connstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + ";extended properties=excel 8.0;";
      oledbconnection objconn = new oledbconnection(connstring);
      //创建表结构
      oledbcommand objcmd = new oledbcommand();
      //数据类型集合
      arraylist datatypelist = new arraylist();
      datatypelist.add("system.decimal");
      datatypelist.add("system.double");
      datatypelist.add("system.int16");
      datatypelist.add("system.int32");
      datatypelist.add("system.int64");
      datatypelist.add("system.single");
      //遍历数据表的所有列，用于创建表结构
      foreach (datacolumn col in table.columns)
      {
        //如果列属于数字列，则设置该列的数据类型为double
        if (datatypelist.indexof(col.datatype.tostring()) >= 0)
        {
          para[i] = new oledbparameter("@" + col.columnname, oledbtype.double);
          objcmd.parameters.add(para[i]);
          //如果是最后一列
          if (i + 1 == colcount)
          {
            tablestructstr += col.columnname + " double)";
          }
          else
          {
            tablestructstr += col.columnname + " double,";
          }
        }
        else
        {
          para[i] = new oledbparameter("@" + col.columnname, oledbtype.varchar);
          objcmd.parameters.add(para[i]);
          //如果是最后一列
          if (i + 1 == colcount)
          {
            tablestructstr += col.columnname + " varchar)";
          }
          else
          {
            tablestructstr += col.columnname + " varchar,";
          }
        }
        i++;
      }
      //创建excel文件及文件结构
      try
      {
        objcmd.connection = objconn;
        objcmd.commandtext = tablestructstr;
        if (objconn.state == connectionstate.closed)
        {
          objconn.open();
        }
        objcmd.executenonquery();
      }
      catch (exception exp)
      {
        throw exp;
      }
      //插入记录的sql语句
      string insertsql_1 = "insert into " + table.tablename + " (";
      string insertsql_2 = " values (";
      string insertsql = "";
      //遍历所有列，用于插入记录，在此创建插入记录的sql语句
      for (int colid = 0; colid < colcount; colid++)
      {
        if (colid + 1 == colcount) //最后一列
        {
          insertsql_1 += table.columns[colid].columnname + ")";
          insertsql_2 += "@" + table.columns[colid].columnname + ")";
        }
        else
        {
          insertsql_1 += table.columns[colid].columnname + ",";
          insertsql_2 += "@" + table.columns[colid].columnname + ",";
        }
      }
      insertsql = insertsql_1 + insertsql_2;
      //遍历数据表的所有数据行
      for (int rowid = 0; rowid < table.rows.count; rowid++)
      {
        for (int colid = 0; colid < colcount; colid++)
        {
          if (para[colid].dbtype == dbtype.double && table.rows[rowid][colid].tostring().trim() == "")
          {
            para[colid].value = 0;
          }
          else
          {
            para[colid].value = table.rows[rowid][colid].tostring().trim();
          }
        }
        try
        {
          objcmd.commandtext = insertsql;
          objcmd.executenonquery();
        }
        catch (exception exp)
        {
          string str = exp.message;
        }
      }
      try
      {
        if (objconn.state == connectionstate.open)
        {
          objconn.close();
        }
      }
      catch (exception exp)
      {
        throw exp;
      }
      return true;
    }
    /// <summary>
    /// 将数据导出至excel文件
    /// </summary>
    /// <param name="table">datatable对象</param>
    /// <param name="columns">要导出的数据列集合</param>
    /// <param name="excelfilepath">excel文件路径</param>
    public static bool outputtoexcel(datatable table, arraylist columns, string excelfilepath)
    {
      if (file.exists(excelfilepath))
      {
        throw new exception("该文件已经存在！");
      }
      //如果数据列数大于表的列数，取数据表的所有列
      if (columns.count > table.columns.count)
      {
        for (int s = table.columns.count + 1; s <= columns.count; s++)
        {
          columns.removeat(s);  //移除数据表列数后的所有列
        }
      }
      //遍历所有的数据列，如果有数据列的数据类型不是 datacolumn，则将它移除
      datacolumn column = new datacolumn();
      for (int j = 0; j < columns.count; j++)
      {
        try
        {
          column = (datacolumn)columns[j];
        }
        catch (exception)
        {
          columns.removeat(j);
        }
      }
      if ((table.tablename.trim().length == 0) || (table.tablename.tolower() == "table"))
      {
        table.tablename = "sheet1";
      }
      //数据表的列数
      int colcount = columns.count;
      //创建参数
      oledbparameter[] para = new oledbparameter[colcount];
      //创建表结构的sql语句
      string tablestructstr = @"create table " + table.tablename + "(";
      //连接字符串
      string connstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + ";extended properties=excel 8.0;";
      oledbconnection objconn = new oledbconnection(connstring);
      //创建表结构
      oledbcommand objcmd = new oledbcommand();
      //数据类型集合
      arraylist datatypelist = new arraylist();
      datatypelist.add("system.decimal");
      datatypelist.add("system.double");
      datatypelist.add("system.int16");
      datatypelist.add("system.int32");
      datatypelist.add("system.int64");
      datatypelist.add("system.single");
      datacolumn col = new datacolumn();
      //遍历数据表的所有列，用于创建表结构
      for (int k = 0; k < colcount; k++)
      {
        col = (datacolumn)columns[k];
        //列的数据类型是数字型
        if (datatypelist.indexof(col.datatype.tostring().trim()) >= 0)
        {
          para[k] = new oledbparameter("@" + col.caption.trim(), oledbtype.double);
          objcmd.parameters.add(para[k]);
          //如果是最后一列
          if (k + 1 == colcount)
          {
            tablestructstr += col.caption.trim() + " double)";
          }
          else
          {
            tablestructstr += col.caption.trim() + " double,";
          }
        }
        else
        {
          para[k] = new oledbparameter("@" + col.caption.trim(), oledbtype.varchar);
          objcmd.parameters.add(para[k]);
          //如果是最后一列
          if (k + 1 == colcount)
          {
            tablestructstr += col.caption.trim() + " varchar)";
          }
          else
          {
            tablestructstr += col.caption.trim() + " varchar,";
          }
        }
      }
      //创建excel文件及文件结构
      try
      {
        objcmd.connection = objconn;
        objcmd.commandtext = tablestructstr;
        if (objconn.state == connectionstate.closed)
        {
          objconn.open();
        }
        objcmd.executenonquery();
      }
      catch (exception exp)
      {
        throw exp;
      }
      //插入记录的sql语句
      string insertsql_1 = "insert into " + table.tablename + " (";
      string insertsql_2 = " values (";
      string insertsql = "";
      //遍历所有列，用于插入记录，在此创建插入记录的sql语句
      for (int colid = 0; colid < colcount; colid++)
      {
        if (colid + 1 == colcount) //最后一列
        {
          insertsql_1 += columns[colid].tostring().trim() + ")";
          insertsql_2 += "@" + columns[colid].tostring().trim() + ")";
        }
        else
        {
          insertsql_1 += columns[colid].tostring().trim() + ",";
          insertsql_2 += "@" + columns[colid].tostring().trim() + ",";
        }
      }
      insertsql = insertsql_1 + insertsql_2;
      //遍历数据表的所有数据行
      datacolumn datacol = new datacolumn();
      for (int rowid = 0; rowid < table.rows.count; rowid++)
      {
        for (int colid = 0; colid < colcount; colid++)
        {
          //因为列不连续，所以在取得单元格时不能用行列编号，列需得用列的名称
          datacol = (datacolumn)columns[colid];
          if (para[colid].dbtype == dbtype.double && table.rows[rowid][datacol.caption].tostring().trim() == "")
          {
            para[colid].value = 0;
          }
          else
          {
            para[colid].value = table.rows[rowid][datacol.caption].tostring().trim();
          }
        }
        try
        {
          objcmd.commandtext = insertsql;
          objcmd.executenonquery();
        }
        catch (exception exp)
        {
          string str = exp.message;
        }
      }
      try
      {
        if (objconn.state == connectionstate.open)
        {
          objconn.close();
        }
      }
      catch (exception exp)
      {
        throw exp;
      }
      return true;
    }
    #endregion
    /// <summary>
    /// 获取excel文件数据表列表
    /// </summary>
    public static arraylist getexceltables(string excelfilename)
    {
      datatable dt = new datatable();
      arraylist tableslist = new arraylist();
      if (file.exists(excelfilename))
      {
        using (oledbconnection conn = new oledbconnection("provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" + excelfilename))
        {
          try
          {
            conn.open();
            dt = conn.getoledbschematable(oledbschemaguid.tables, new object[] { null, null, null, "table" });
          }
          catch (exception exp)
          {
            throw exp;
          }
          //获取数据表个数
          int tablecount = dt.rows.count;
          for (int i = 0; i < tablecount; i++)
          {
            string tablename = dt.rows[i][2].tostring().trim().trimend('$');
            if (tableslist.indexof(tablename) < 0)
            {
              tableslist.add(tablename);
            }
          }
        }
      }
      return tableslist;
    }
    /// <summary>
    /// 将excel文件导出至datatable(第一行作为表头)
    /// </summary>
    /// <param name="excelfilepath">excel文件路径</param>
    /// <param name="tablename">数据表名，如果数据表名错误，默认为第一个数据表名</param>
    public static datatable inputfromexcel(string excelfilepath, string tablename)
    {
      if (!file.exists(excelfilepath))
      {
        throw new exception("excel文件不存在！");
      }
      //如果数据表名不存在，则数据表名为excel文件的第一个数据表
      arraylist tablelist = new arraylist();
      tablelist = getexceltables(excelfilepath);
      if (tablename.indexof(tablename) < 0)
      {
        tablename = tablelist[0].tostring().trim();
      }
      datatable table = new datatable();
      oledbconnection dbcon = new oledbconnection(@"provider=microsoft.jet.oledb.4.0;data source=" + excelfilepath + ";extended properties=excel 8.0");
      oledbcommand cmd = new oledbcommand("select * from [" + tablename + "$]", dbcon);
      oledbdataadapter adapter = new oledbdataadapter(cmd);
      try
      {
        if (dbcon.state == connectionstate.closed)
        {
          dbcon.open();
        }
        adapter.fill(table);
      }
      catch (exception exp)
      {
        throw exp;
      }
      finally
      {
        if (dbcon.state == connectionstate.open)
        {
          dbcon.close();
        }
      }
      return table;
    }
    /// <summary>
    /// 获取excel文件指定数据表的数据列表
    /// </summary>
    /// <param name="excelfilename">excel文件名</param>
    /// <param name="tablename">数据表名</param>
    public static arraylist getexceltablecolumns(string excelfilename, string tablename)
    {
      datatable dt = new datatable();
      arraylist colslist = new arraylist();
      if (file.exists(excelfilename))
      {
        using (oledbconnection conn = new oledbconnection("provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" + excelfilename))
        {
          conn.open();
          dt = conn.getoledbschematable(oledbschemaguid.columns, new object[] { null, null, tablename, null });
          //获取列个数
          int colcount = dt.rows.count;
          for (int i = 0; i < colcount; i++)
          {
            string colname = dt.rows[i]["column_name"].tostring().trim();
            colslist.add(colname);
          }
        }
      }
      return colslist;
    }
  }
}

希望本文所述对大家的c#程序设计有所帮助。