欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2023-12-13 10:09:40
本文实例讲述了c#自定义导出数据到excel的类。分享给大家供大家参考。具体如下: c#自定义excel操作类,可以用于将datatable导出到excel文件,从exc...

本文实例讲述了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#程序设计有所帮助。

上一篇:

下一篇: