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
///
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
///
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博客 ” ，链接：https://blog.itpub.net/12476590/viewspace-263653/ ，如需转载，请注明出处，否则将追究法律责任。
