C# 操作Excel [不确定sheet name]

C#操作Excel我掌握两种方法
1. Microsoft.Jet.OLEDB.4.0
   操作简单，但不知道某种原因，部分Cell不能正确读取到．

2. Excel.ApplicationClass
   操作相对复杂，功能多一些．

遇到问题：由于方法1不能正确读取Excel数据，所以选择方法2．但是sheet name不知，方法二不能操作Excel，不能确定sheetName.
解决：使用方法1获取到Excel中所有SheetName，在方法2中使用这个结果．

Source:

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Collections;
using Excel;

namespace ImportIDPSCD
{
 ///
 /// Input of IDP and SCD Spending per day .
 ///
 class ImportIDPSCD
 {
  // IDP and SCD Spendin File Path
  private static string filePath = string.Empty;
  // DB Connect String
  private static string strDBManager = string.Empty;

  ///
  /// Input of IDP and SCD Spending per day .
  ///
  [STAThread]
  static void Main(string[] args)
  {
   // Get File Path
   filePath =  System.Configuration.ConfigurationSettings.AppSettings["XXX"];
   // Get DB Connecct
   strDBManager =  System.Configuration.ConfigurationSettings.AppSettings["YYY"];
   // Get File
   string[] fileEntries = Directory.GetFiles(filePath);
   foreach(string fileName in fileEntries)
   {
    // Get Excel File
    GetFileFull(fileName);
   }
  }

  ///
  /// Get Excel Data.
  ///
  /// file Name
  /// int
  private static int GetFile(string fileName)
  {
   // Read Excel File Connect
   string excelConnect = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + fileName + ";Extended Properties=Excel 8.0";
   string excelCom = string.Empty;
   OleDbConnection excelConn = new OleDbConnection(excelConnect);
   
   try
   {
    // Open Excel
    excelConn.Open();
    System.Data.DataTable excelDT = null;
    excelDT = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    int i = 0;
    // Add the sheet name to the string array.
    foreach(DataRow row in excelDT.Rows)
    {
     DataSet excelDataSet = new DataSet();
     // Get sheet Name.
     string sheetName = row["TABLE_NAME"].ToString();
     i++;
     excelCom = " SELECT * FROM [" + sheetName + "]";
     OleDbDataAdapter excelAdap = new OleDbDataAdapter(excelCom , excelConn);    
     // Get Excel Data
     excelAdap.Fill(excelDataSet, "sheet1");
     // Import Excel Data
     ImportFile(excelDataSet);
    }
   }
   catch(Exception ex)
   {
    Console.WriteLine(ex.Message);
    return -1;    
   }
   finally
   {
    if(excelConn != null)
    {
     excelConn.Close();
     excelConn = null;
    }
   }

   return 0;
  }

  ///
  /// Get ExcelFile
  ///
  /// File Name
  /// int
  private static int GetFileFull(string fileName)
  {
   // Excel Data
   DataSet allDS = new DataSet();
   // Sheets Name
   String[] excelSheetsName = null;
   // All Cell Value of a Sheet
   ArrayList allCellValue = new ArrayList();

   // Read Excel File Connect
   string excelConnect = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source =" + fileName + ";Extended Properties=Excel 8.0";
   string excelCom = string.Empty;
   OleDbConnection excelConn = new OleDbConnection(excelConnect);

   // Creates a new Excel Application
   Excel.Application excelApp = new Excel.ApplicationClass();
   // Makes Excel visible to the user.
   excelApp.Visible = false; 
   
   try
   {
    // Open Excel
    excelConn.Open();
    System.Data.DataTable excelDT = null;
    excelDT = excelConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    excelSheetsName = new String[excelDT.Rows.Count];
    int i = 0;
    // Add the sheet name to the string array.
    foreach(DataRow row in excelDT.Rows)
    {
     // Get sheet Name.
     excelSheetsName[i] = row["TABLE_NAME"].ToString();
     i++;     
    }

    // Get Excel Data Command
    excelCom = " SELECT * FROM [" + excelSheetsName[0] + "]";
    OleDbDataAdapter excelAdap = new OleDbDataAdapter(excelCom , excelConn);    
    // Get Excel Data
    excelAdap.Fill(allDS, "AllDS");

    // Close Open Object
    if(excelConn != null)
    {
     excelConn.Close();
     excelConn = null;
    }
    #region Execute Excel

    // Opens existing workbook
    string workbookPath = fileName;
    Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0,
     false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true,
     false,  0, true, false, false);
   
    // Gets the Worksheets collection
    Excel.Sheets excelSheets = excelWorkbook.Worksheets;

    // Gets Sheet Name for Worksheets
    string currentSheet = excelSheetsName[0].Replace("$", "").Replace("'", "");    
    Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelSheets.get_Item(currentSheet);

    // For each the sheet Data
    for(int index = 4; index < allDS.Tables[0].Rows.Count - 1; index++)
    {
     if(allDS.Tables[0].Rows[index][0] != null && !allDS.Tables[0].Rows[index][0].ToString().Equals(""))
     {
      // Gets cell
      int lineNumber = index + 2;
      Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A" + lineNumber, "G" + lineNumber);

      // Keep Data
      object [,] cellValue = (object[,])excelCell.Value2;
      allCellValue.Add(cellValue);           
     }     
    }

    // Import Data to DB
    ImportFile(allCellValue);
                #endregion
   }
   catch(Exception ex)
   {
    Console.WriteLine(ex.Message); 
   }
   finally
   {
    if(excelConn != null)
    {
     excelConn.Close();
     excelConn = null;
    }
    if(excelApp != null)
    {
     excelApp.Workbooks.Close();
     excelApp = null;
    }
   }

   return 0;
  }

  ///
  /// Import File Data into DB
  ///
  /// All Cell Data
  private static void ImportFile(ArrayList allAellValue)
  {
   // Declare variables
   OleDbConnection cn  = null;
   OleDbCommand cmd    = null;
   OleDbTransaction Trans = null;

   try
   {
    cn = new OleDbConnection(strDBManager);
    cn.Open();
    cmd = cn.CreateCommand();
    cmd.Connection = cn;
    Trans = cn.BeginTransaction();
    cmd.Transaction = Trans;

    cmd.CommandText = "eFund_ImportIDPSCD";
    cmd.CommandType = CommandType.StoredProcedure;

    for(int index = 0; index < allAellValue.Count; index++)
    {
     cmd.Parameters.Clear();
     object [,] cellValue = (object [,])allAellValue[index];
     OleDbParameter p1 = cmd.Parameters.Add("@SettAmoIDP",OleDbType.Double);
     OleDbParameter p2 = cmd.Parameters.Add("@SettAmoSCD",OleDbType.Double);
     OleDbParameter p3 = cmd.Parameters.Add("@AdjuAmoIDP",OleDbType.Double);
     OleDbParameter p4 = cmd.Parameters.Add("@AdjuAmoSCD",OleDbType.Double);
     OleDbParameter p5 = cmd.Parameters.Add("@DailyAmo",OleDbType.Double);
     OleDbParameter p6 = cmd.Parameters.Add("@ServiceAmo",OleDbType.Double);
     OleDbParameter p7 = cmd.Parameters.Add("@SettDate",OleDbType.DBTimeStamp);
     // Set Value
     p1.Value = Convert.ToDouble(cellValue[1,4]);   // SettlementAmountIDP
     p2.Value = Convert.ToDouble(cellValue[1,3]);   // SettlementAmountSCD
     p3.Value = Convert.ToDouble(cellValue[1,6]);   // AdjustmentAmountIDP
     p4.Value = Convert.ToDouble(cellValue[1,5]);   // AdjustmentAmountSCD
     p5.Value = Convert.ToDouble(cellValue[1,2]);   // DailyDepositAmount
     p6.Value = Convert.ToDouble(cellValue[1,7]);   // ServiceAmount
     p7.Value = Convert.ToDateTime(cellValue[1,1]);   // SettlementDate
    
     cmd.ExecuteNonQuery();     
    }
    Trans.Commit();
   }
   catch(Exception ex)
   {
    Trans.Rollback();
    Console.WriteLine(ex.Message);
    throw ex;
   }
   finally
   {
    if(cn != null)
    {
     cn.Close();
     cn = null;
    } 
   }
  }

  ///
  /// Import File Data into DB
  ///
  /// Data
  private static void ImportFile(DataSet excelDataSet)
  {
   // Declare variables
   OleDbConnection cn  = null;
   OleDbCommand cmd    = null;
   OleDbTransaction Trans = null;

   try
   {
    cn = new OleDbConnection(strDBManager);
    cn.Open();
    cmd = cn.CreateCommand();
    cmd.Connection = cn;
    Trans = cn.BeginTransaction();
    cmd.Transaction = Trans;

    cmd.CommandText = "XXX";
    cmd.CommandType = CommandType.StoredProcedure;

    for(int index = 4; index < excelDataSet.Tables[0].Rows.Count - 1; index++)
    {
     cmd.Parameters.Clear();
     if(excelDataSet.Tables[0].Rows[index][0] != null && !excelDataSet.Tables[0].Rows[index][0].ToString().Equals(""))
     {
      OleDbParameter p1 = cmd.Parameters.Add("@SettAmoIDP",OleDbType.Double);
      OleDbParameter p2 = cmd.Parameters.Add("@SettAmoSCD",OleDbType.Double);
      OleDbParameter p3 = cmd.Parameters.Add("@AdjuAmoIDP",OleDbType.Double);
      OleDbParameter p4 = cmd.Parameters.Add("@AdjuAmoSCD",OleDbType.Double);
      OleDbParameter p5 = cmd.Parameters.Add("@DailyAmo",OleDbType.Double);
      OleDbParameter p6 = cmd.Parameters.Add("@ServiceAmo",OleDbType.Double);
      OleDbParameter p7 = cmd.Parameters.Add("@SettDate",OleDbType.DBTimeStamp);
      p1.Value = excelDataSet.Tables[0].Rows[index][3];
      p2.Value = excelDataSet.Tables[0].Rows[index][2];
      p3.Value = excelDataSet.Tables[0].Rows[index][5];
      p4.Value = excelDataSet.Tables[0].Rows[index][4];
      p5.Value = excelDataSet.Tables[0].Rows[index][1];
      p6.Value = excelDataSet.Tables[0].Rows[index][6];
      p7.Value = excelDataSet.Tables[0].Rows[index][0];
    
      cmd.ExecuteNonQuery();      
     }     
    }
    Trans.Commit();
   }
   catch(Exception ex)
   {
    Trans.Rollback();
    Console.WriteLine(ex.Message);
   }
   finally
   {
    if(cn != null)
    {
     cn.Close();
     cn = null;
    } 
   }
  }
 }
}

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12476590/viewspace-263653/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12476590/viewspace-263653/