/// <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 }