Ok, I am trying to figure out the best way to accomplish this in code. I have a spreadsheet with numerous tabs and what I want to do is in C# read the name of each sheet and then its corresponding rows of data. Currently, this is what I have:
Code:
if (!DirectoryIsEmpty(DropZone.ToString()))
{
DirectoryInfo di = new DirectoryInfo(DropZone);
FileInfo[] fi = di.GetFiles();
// step through each found file
foreach (FileInfo fiTemp in fi)
{
// Connection String to Excel Workbook
string excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + DropZone + "\\" + fiTemp + "'; Extended Properties=\"Excel 8.0; HDR=YES; IMEX=1;\"";
string SheetName = string.Empty;
// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnStr))
{
try
{
string curaddy2 = string.Empty;
string priaddy2 = string.Empty;
connection.Open();
DataTable dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt != null)
{
string[] excelSheets = new String[dt.Rows.Count];
int i = 0;
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
#region ExcelReading
SheetName = excelSheets[i].ToString();
OleDbCommand command = new OleDbCommand("SELECT [Name], [Address], [City], [State], [Zip] FROM '" + SheetName.ToString() + "'", connection);
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = DBUtils.DBString;
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
// Columns get copied from sheet and into bulk copy statement
logger.MyLogFile("" + DateTime.Now.ToString() + " Bulk Copy: ", "ID Number " + dr.GetValue(0).ToString() + "");
// now that matrix is created, do the insert
bulkCopy.DestinationTableName = "Table";
bulkCopy.WriteToServer(dr);
logger.MyLogFile("" + DateTime.Now.ToString() + " Bulk Copy: ", "All values inserted as burst");
}
}
#endregion
i++;
}
}
dt.Dispose();
}
catch (Exception ex)
{
logger.MyLogFile("" + DateTime.Now.ToString() + " Importer \n", "Import failed, error code: " + ex.ToString() + "");
}
finally
{
connection.Close();
}
}
}