///<summary>ExcelOpen</summary>
public static void ExcelOpen
(
string filenameExcel,
ExcelConnectionType excelConnectionType,
out DataSet dataSet,
out string exceptionMessage
)
{
string excelConnectionString = null;
HttpContext httpContext = HttpContext.Current;
OleDbDataAdapter oleDbDataAdapter = null;
OleDbCommand oleDbCommand = null;
OleDbConnection oleDbConnection = null;
dataSet = null;
exceptionMessage = null;
switch (excelConnectionType)
{
case ExcelConnectionType.OLEDB:
excelConnectionString = String.Format(ExcelConnectionString[(int)excelConnectionType], filenameExcel);
break;
case ExcelConnectionType.ODBC:
excelConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + filenameExcel +
";DefaultDir=" + Path.GetDirectoryName(filenameExcel) + ';';
break;
}
try
{
oleDbConnection = new OleDbConnection(excelConnectionString);
oleDbConnection.Open();
oleDbCommand = new OleDbCommand("SELECT * FROM [sheet1$]", oleDbConnection);
oleDbDataAdapter = new OleDbDataAdapter();
oleDbDataAdapter.SelectCommand = oleDbCommand;
dataSet = new DataSet();
oleDbDataAdapter.Fill(dataSet);
}
catch (Exception ex)
{
exceptionMessage = ex.Message;
}
finally
{
//Clean up.
if (oleDbConnection != null)
{
oleDbConnection.Close();
}
}
if (httpContext == null && exceptionMessage != null)
{
System.Console.WriteLine(exceptionMessage);
}
}
public void AddNewExcelConnection(string ConnectionName, string ExcelFile, ExcelConnectionType ConnectionType = ExcelConnectionType.NPOI, bool isWrite = true)
{
if (ConnectionType == ExcelConnectionType.NPOI)
{
if (!Pool_ExcelConnection.ContainsKey(ConnectionName))
{
ExcelConnection_NPIO newItem = new ExcelConnection_NPIO(ConnectionName);
if (newItem.ConnectToExcel(ExcelFile, true))
{
Pool_ExcelConnection.Add(ConnectionName, newItem);
}
}
}
}
public void AddNewExcelConnection(string ConnectionName, string ExcelFile, ExcelConnectionType ConnectionType = ExcelConnectionType.NPOI, bool isWrite = true)
{
if (ConnectionType == ExcelConnectionType.NPOI)
{
if (!Pool_ExcelConnection.ContainsKey(ConnectionName))
{
ExcelConnection_NPIO newItem = new ExcelConnection_NPIO(ConnectionName);
if (newItem.ConnectToExcel(ExcelFile, true))
Pool_ExcelConnection.Add(ConnectionName, newItem);
}
}
}
///<summary>ExcelOpen</summary>
public static void ExcelOpen
(
string filenameExcel,
ExcelConnectionType excelConnectionType,
out string exceptionMessage
)
{
string excelConnectionString = null;
HttpContext httpContext = HttpContext.Current;
IDataReader dataReader = null;
OleDbCommand oleDbCommand = null;
OleDbConnection oleDbConnection = null;
exceptionMessage = null;
switch (excelConnectionType)
{
case ExcelConnectionType.OLEDB:
excelConnectionString = String.Format(ExcelConnectionString[(int)excelConnectionType], filenameExcel);
break;
case ExcelConnectionType.ODBC:
excelConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=" + filenameExcel +
";DefaultDir=" + Path.GetDirectoryName(filenameExcel) + ';';
break;
}
try
{
oleDbConnection = new OleDbConnection(excelConnectionString);
oleDbConnection.Open();
oleDbCommand = new OleDbCommand("SELECT * FROM [sheet1$]", oleDbConnection);
dataReader = oleDbCommand.ExecuteReader();
while (dataReader.Read())
{
//Console.WriteLine(reader.GetInt32(0) + ", " + reader.GetString(1));
for (int fieldIndex = 0; fieldIndex < dataReader.FieldCount; ++fieldIndex)
{
System.Console.Write(dataReader[fieldIndex]);
}
System.Console.WriteLine();
}
}
catch (Exception ex)
{
exceptionMessage = ex.Message;
}
finally
{
//Clean up.
if (dataReader != null)
{
dataReader.Close();
}
if (oleDbConnection != null)
{
oleDbConnection.Close();
}
}
if (httpContext == null && exceptionMessage != null)
{
System.Console.WriteLine(exceptionMessage);
}
}