/// <summary>
/// Converts all sheets to a DataSet
/// </summary>
/// <param name="self">The IExcelDataReader instance</param>
/// <param name="configuration">An optional configuration object to modify the behavior of the conversion</param>
/// <returns>A dataset with all workbook contents</returns>
public static DataSet AsDataSet(this IExcelDataReader self, ExcelDataSetConfiguration configuration = null)
{
if (configuration == null)
{
configuration = new ExcelDataSetConfiguration();
}
self.Reset();
var tableIndex = -1;
var result = new DataSet();
do
{
tableIndex++;
if (configuration.FilterSheet != null && !configuration.FilterSheet(self, tableIndex))
{
continue;
}
var tableConfiguration = configuration.ConfigureDataTable != null
? configuration.ConfigureDataTable(self)
: null;
if (tableConfiguration == null)
{
tableConfiguration = new ExcelDataTableConfiguration();
}
var table = AsDataTable(self, tableConfiguration);
result.Tables.Add(table);
}while (self.NextResult());
result.AcceptChanges();
if (configuration.UseColumnDataType)
{
FixDataTypes(result);
}
self.Reset();
return(result);
}
protected override void Execute(CodeActivityContext context)
{
var useHeaderRow = false;
if (UseHeaderRow != null)
{
useHeaderRow = UseHeaderRow.Get(context);
}
System.Data.DataSet result = null;
var filename = Filename.Get(context);
filename = Environment.ExpandEnvironmentVariables(filename);
using (var stream = System.IO.File.Open(filename, System.IO.FileMode.Open, System.IO.FileAccess.Read))
{
// Auto-detect format, supports:
// - Binary Excel files (2.0-2003 format; *.xls)
// - OpenXml Excel files (2007 format; *.xlsx)
var conf = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = useHeaderRow
}
};
using (var reader = ExcelDataReader.ExcelReaderFactory.CreateReader(stream))
{
// Choose one of either 1 or 2:
// 1. Use the reader methods
//do
//{
// while (reader.Read())
// {
// // reader.GetDouble(0);
// }
//} while (reader.NextResult());
// 2. Use the AsDataSet extension method
result = reader.AsDataSet(conf);
}
}
if (DataSet != null)
{
context.SetValue(DataSet, result);
}
}
private void buttonAddExcell_Click(object sender, EventArgs e)
{
if (comboBoxTableList.SelectedIndex == -1)
{
MessageBox.Show("Lütfen Tablo Seçimi Yapınız !");
return;
}
tableName = comboBoxTableList.SelectedItem.ToString();
OpenFileDialog openFile = new OpenFileDialog();
openFile.ShowDialog();
textBoxExcelFilePath.Text = openFile.FileName;
string filePath = textBoxExcelFilePath.Text;
strDate = date.ToString("dd/MM/yyyy");
#region ocly_product
if (tableName == "ocly_product")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eProduct = new DataTable();
dtOc5eProduct = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eProduct.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(product_id,model,quantity,stock_status_id,image,manufacturer_id,shipping,price,tax_class_id,date_available,weight_class_id,length_class_id,subtract,minimum,sort_order,status,date_added,date_modified,bulkpriceupdate)" + "VALUES('"
+ dtOc5eProduct.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][1].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][2].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][3].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][4].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][5].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][6].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][7].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][8].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][9].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][10].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][11].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][12].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][13].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][14].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][15].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][16].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][17].ToString() + "',"
+ "'" + dtOc5eProduct.Rows[i][18].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
#region ocly_product_description
else if (tableName == "ocly_product_description")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eProductDescription = new DataTable();
dtOc5eProductDescription = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eProductDescription.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(product_id,language_id,name,description,tag,meta_title,meta_description,meta_keyword)" + "VALUES('"
+ dtOc5eProductDescription.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eProductDescription.Rows[i][1].ToString() + "',"
+ "'" + dtOc5eProductDescription.Rows[i][2].ToString() + "',"
+ "'" + dtOc5eProductDescription.Rows[i][3].ToString() + "',"
+ "'" + dtOc5eProductDescription.Rows[i][4].ToString() + "',"
+ "'" + dtOc5eProductDescription.Rows[i][5].ToString() + "',"
+ "'" + dtOc5eProductDescription.Rows[i][6].ToString() + "',"
+ "'" + dtOc5eProductDescription.Rows[i][7].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
#region ocly_product_image
else if (tableName == "ocly_product_image")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eProductImage = new DataTable();
dtOc5eProductImage = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eProductImage.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(product_image_id,product_id,image)" + "VALUES('"
+ dtOc5eProductImage.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eProductImage.Rows[i][1].ToString() + "',"
+ "'" + dtOc5eProductImage.Rows[i][2].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
#region ocly_product_related
else if (tableName == "ocly_product_related")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eProductRelated = new DataTable();
dtOc5eProductRelated = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eProductRelated.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(product_id,related_id)" + "VALUES('"
+ dtOc5eProductRelated.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eProductRelated.Rows[i][1].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
#region ocly_product_special
else if (tableName == "ocly_product_special")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eProductSpecial = new DataTable();
dtOc5eProductSpecial = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eProductSpecial.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(product_special_id,product_id,customer_group_id,price,bulkpriceupdate)" + "VALUES('"
+ dtOc5eProductSpecial.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eProductSpecial.Rows[i][1].ToString() + "',"
+ "'" + dtOc5eProductSpecial.Rows[i][2].ToString() + "',"
+ "'" + dtOc5eProductSpecial.Rows[i][3].ToString() + "',"
+ "'" + dtOc5eProductSpecial.Rows[i][4].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
#region ocly_product_to_category
else if (tableName == "ocly_product_to_category")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eProductToCategory = new DataTable();
dtOc5eProductToCategory = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eProductToCategory.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(product_id,category_id)" + "VALUES('"
+ dtOc5eProductToCategory.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eProductToCategory.Rows[i][1].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
#region ocly_product_to_layout
else if (tableName == "ocly_product_to_layout")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eProductToLayout = new DataTable();
dtOc5eProductToLayout = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eProductToLayout.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(product_id,store_id,layout_id)" + "VALUES('"
+ dtOc5eProductToLayout.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eProductToLayout.Rows[i][1].ToString() + "',"
+ "'" + dtOc5eProductToLayout.Rows[i][2].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
#region ocly_product_to_store
else if (tableName == "ocly_product_to_store")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eProductToStore = new DataTable();
dtOc5eProductToStore = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eProductToStore.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(product_id,store_id)" + "VALUES('"
+ dtOc5eProductToStore.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eProductToStore.Rows[i][1].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
#region ocly_seo_url
else if (tableName == "ocly_seo_url")
{
Cursor.Current = Cursors.WaitCursor;
FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read);
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
var result = new ExcelDataReader.ExcelDataSetConfiguration
{
ConfigureDataTable = _ => new ExcelDataReader.ExcelDataTableConfiguration
{
UseHeaderRow = true
}
};
var dataSet = excelReader.AsDataSet(result);
DataTable dtOc5eSeoUrl = new DataTable();
dtOc5eSeoUrl = dataSet.Tables[0];
connection.Open();
for (int i = 0; i < dtOc5eSeoUrl.Rows.Count; i++)
{
string strSQL = "INSERT INTO " + tableName + "(seo_url_id,store_id,language_id,query,keyword)" + "VALUES('"
+ dtOc5eSeoUrl.Rows[i][0].ToString() + "',"
+ "'" + dtOc5eSeoUrl.Rows[i][1].ToString() + "',"
+ "'" + dtOc5eSeoUrl.Rows[i][2].ToString() + "',"
+ "'" + dtOc5eSeoUrl.Rows[i][3].ToString() + "',"
+ "'" + dtOc5eSeoUrl.Rows[i][4].ToString() + "'"
+ ")";
var objCmd = new MySqlCommand(strSQL, connection);
var sendData = objCmd.ExecuteNonQuery();
}
MessageBox.Show(tableName + " Tablosuna Excel Kayıtları Eklendi !");
textBoxExcelFilePath.Text = String.Empty;
connection.Close();
Cursor.Current = Cursors.Default;
}
#endregion
}
