Excel文件读写

      对于c/s模式的开发，读写Excel文件是很经常的事情，这里我介绍一下通过Oledb方式对Excel文件进行读写的方法，相比通过操作单元格的方式，应该在性能和效率上有很大优势。

首先、提供一个excel文件操作引擎类。

using System;
using System.Data;
using System.Data.OleDb;
using System.ComponentModel;
using System.ComponentModel.Design;

 public class FileEngineExcel : System.ComponentModel.Component
 {
  #region 私有变量

  private OleDbConnection conn = null;
  private OleDbCommand cmd = null;
  #endregion

  #region 私有方法

  private string formatValue(object val)
  {
   if (val == null)
    return string.Empty;
   else if (val is DateTime)
    return ((DateTime)val).ToString(@"yyyy-MM-dd HH:mm:ss");
   else
    return val.ToString();
  }
  #endregion

  #region 构造方法

  public FileEngineExcel()
  {
   conn = new OleDbConnection();
   cmd = new OleDbCommand("", conn);
  }
  public FileEngineExcel(System.ComponentModel.IContainer container) : this()
  {
   container.Add(this);
  }
  #endregion

  #region 销毁方法

  protected override void Dispose(bool disposing)
  {
   if (disposing)
   {
    conn.Dispose();
    cmd.Dispose();
   }   
   base.Dispose (disposing);
  }
  #endregion

  #region 打开关闭EXCEL文件

  public void Open(string fileName, bool readOnly)
  {
   if (conn.State != ConnectionState.Closed)
    conn.Close();

   conn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = " + fileName +"; Extended Properties = 'Excel 8.0; HDR=Yes;" + (readOnly ? " IMEX=1;" : "") + "'";
   conn.Open();
  }
  public void Open(string fileName)
  {
   Open(fileName, false);
  }
  public void Close()
  {
   conn.Close();
  }
  #endregion

  #region 表(SHEET)操作

  public void CreateTable(string table, string[] columnNames)
  {
   string cmdTxt = "CREATE TABLE " + table + "(" + columnNames[0] + " VARCHAR";
   for (int i = 1; i < columnNames.Length; i ++)
    cmdTxt += ", " + columnNames[i] + " VARCHAR";
   cmd.CommandText = cmdTxt + ")";

   cmd.ExecuteNonQuery();
  }
  public bool IsTableExist(string table)
  {
   cmd.CommandText = "SELECT count(*) FROM " + table + " WHERE 1 > 2";
   try
   {
    object ret = cmd.ExecuteScalar();
   }
   catch
   {
    return false;
   }
   return true;
  }
  public void DropTable(string table)
  {
   cmd.CommandText = "DROP TABLE " + table;
   cmd.ExecuteNonQuery();
  }
  public string[] GetTableSchema(string table)
  {
   cmd.CommandText = "SELECT * FROM " + table + " WHERE 1 > 2";
   IDataReader reader = cmd.ExecuteReader();
   DataTable schemaTable = reader.GetSchemaTable();
   reader.Close();    
   
   string[] cols = new string[schemaTable.Rows.Count];
   for (int i = 0; i < cols.Length; i ++)
    cols[i] = schemaTable.Rows[i]["ColumnName"].ToString();

   return cols;
  }
  #endregion

  #region 按行插入数据

  public void BeginInsert(string table, string[] cols)
  {
   if (cols == null || cols.Length <= 0)
    cols = this.GetTableSchema(table);

   string cmdTxt = "";
    
   cmdTxt = "INSERT INTO " + table + " (" + cols[0];
   for (int i = 1; i < cols.Length; i ++)
    cmdTxt += ", " + cols[i];

   cmdTxt += ") VALUES (" + "@" + cols[0];
   for (int i = 1; i < cols.Length; i ++)
    cmdTxt += ", @" + cols[i];

   cmd.CommandText = cmdTxt + ")";

   cmd.Parameters.Clear();
   for (int i = 0; i < cols.Length; i ++)
    cmd.Parameters.Add(cols[i], OleDbType.VarChar);
  }
  public void BeginInsert(string table)
  {
   BeginInsert(table, null);
  }
  public void Insert(object[] values)
  {
   for (int i = 0; i < values.Length; i ++)
    cmd.Parameters[i].Value = formatValue(values[i]);
   cmd.ExecuteNonQuery();
  }
  #endregion

  #region 按行读取数据

  public IDataReader GetDataReader(string table, string[] cols)
  {
   string cmdTxt = "SELECT ";

   if (cols == null || cols.Length <= 0)
   {
    cmdTxt += "*";
   }
   else
   {
    cmdTxt += cols[0];
    for (int i = 1; i < cols.Length; i ++)
     cmdTxt += ", " + cols[i];
   }

   cmd.CommandText = cmdTxt + " FROM " + table;
   
   return cmd.ExecuteReader();
  }
  public IDataReader GetDataReader(string table)
  {
   return GetDataReader(table, null);
  }
  #endregion

  #region 导出Excel扩展

  public void CreateTable(string table, string[] columnNames,string[] types)
  {

   string cmdTxt = "CREATE TABLE " + table + "(" + columnNames[0] + this.getFieldType(types[0]);
   for (int i = 1; i < columnNames.Length; i++)
   {   
    cmdTxt += ", " + columnNames[i].Replace("(%)","百分比") + this.getFieldType(types[i]);
   }
   cmd.CommandText = cmdTxt + ")";

   cmd.ExecuteNonQuery();
  }

  public void BeginInsert(string table, string[] cols,string[] types)
  {
   if (cols == null || cols.Length <= 0)
    cols = this.GetTableSchema(table);

   string cmdTxt = "";
    
   cmdTxt = "INSERT INTO " + table + " (" + cols[0];
   for (int i = 1; i < cols.Length; i ++)
    cmdTxt += ", " + cols[i].Replace("(%)","百分比");

   cmdTxt += ") VALUES (" + "@" + cols[0];
   for (int i = 1; i < cols.Length; i ++)
    cmdTxt += ", @" + cols[i].Replace("(%)","百分比");

   cmd.CommandText = cmdTxt + ")";

   cmd.Parameters.Clear();
   for (int i = 0; i < cols.Length; i ++)
   {
    cmd.Parameters.Add(cols[i], this.getFiledOleType(types[i]));   
   }
  }

  public string[] GetTableType(DataTable table)
  {
   int count = table.Columns.Count;
   string[] colTypes = new string[count];
   for (int i = 0; i < count; i ++)
    colTypes[i] = table.Columns[i].DataType.ToString();

   return colTypes;
  }

  public void InsertVal(object[] values)
  {
   for (int i = 0; i < values.Length; i ++)
   {
    cmd.Parameters[i].Value = this.getValue(values[i]);    
   }
   cmd.ExecuteNonQuery();
  }

  private string getFieldType(string type)
  {
   if (type == "System.Int32")
    return " Int";
   else
    if (type == "System.Decimal")
    return " Decimal(19,9)";
   else
    if (type == "System.DateTime")
    return " DateTime";
   else
    //if (type == "System.String")
    return " Varchar";
  }

  private OleDbType getFiledOleType(string type)
  {
   if (type == "System.Int32")
    return OleDbType.Integer;
   else
    if (type == "System.Decimal")
    return OleDbType.Decimal;
   else
    if (type == "System.DateTime")
    return OleDbType.Date;
   else
    //if (type == "System.String")
    return OleDbType.VarChar;
  }

  private object getValue(object val)
  {
   if (val != null && val.ToString().Trim() != "")
   {
    if (val is DateTime)
     return ((DateTime)val).ToString(@"yyyy-MM-dd HH:mm:ss");
    else if (val is Int32)
    {
     return Int32.Parse(val.ToString());
    }
    else if (val is Decimal)
    {
     return decimal.Parse(val.ToString());
    }
    else
    {
     return val.ToString();
    }
   }
   else
    return System.DBNull.Value;
   
  }

  #endregion

 }
其次、提供导入Excel文件示例。

private void importExcel()
  {
   FileEngineExcel excelReader = null;
   OleDbDataReader dr = null;

   try
   {
    excelReader = new FileEngineExcel();
    excelReader.Open(this.FilePath,true);
    dr = (OleDbDataReader)excelReader.GetDataReader("[Sheet2$]");
    
    
    while (dr.Read())
    {
     
    }
   }
   finally
   {
    dr.Close();
    excelReader.Close();
    excelReader.Dispose();
   }                

  }

再次、提供导出Excel文件示例。

private static bool ExportToXls(DataTable table, string fileName)
  {
   FileEngineExcel engine = new FileEngineExcel();

   engine.Open(fileName);
   try
   {
    if (engine.IsTableExist(table.TableName))
     engine.DropTable(table.TableName);

    string[] cols = new string[table.Columns.Count];
    for (int i = 0; i < cols.Length; i ++)
     cols[i] = (table.Columns[i].Caption == null || table.Columns[i].Caption.Length <= 0) ? table.Columns[i].ColumnName : table.Columns[i].Caption;

    string[] types = engine.GetTableType(table);

    engine.CreateTable(table.TableName, cols,types);

    engine.BeginInsert(table.TableName, cols,types);

    int j = 0;
    for (int i = 0; i < table.DefaultView.Count; i ++)
    {     
     if (SqlInt64.Mod(i+1,50000) == 0)
     {
      j++;
      engine.CreateTable(table.TableName + j.ToString(), cols,types);
      engine.BeginInsert(table.TableName + j.ToString(), cols,types);
     }

     engine.InsertVal(table.DefaultView[i].Row.ItemArray);
    }

   }
   catch(Exception ex)
   {
    Prompts.PromptException(ex.Message, ex);
    return false;
   }
   finally
   {
    engine.Close();
    engine.Dispose();
   }

   return true;
  }