OleDB Adding Columns, Inserting values, and other data manipulation for Excel files

[clidd34]

First, a little background for the application Im trying to write. I would like to take a spreadsheet, check the UPC value from a single row & column pulled from the spreadsheet against a database, then return a price value if the value is found and insert the price value in to a new column on the same row. I am attempting to do this via the OleDb library. When I can insert the column and insert the desired UPC value in to a variable I shouldnt have any trouble using it in my mySql query.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.Windows.Resources;
using Microsoft.Win32;
using System.IO;
using System.Data.OleDb;
using System.ComponentModel;
using System.Data.Sql;
using Microsoft.Office.Interop.Excel;
using MySql;


namespace WpfApplication2
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
public partial class MainWindow : System.Windows.Window
{
public MainWindow()
{
InitializeComponent();

}


private void File_Selection_Click(object sender, RoutedEventArgs e)
{
// Open the file selection window
OpenFileDialog openFileDialog1 = new OpenFileDialog();
// Set initial directory
openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
// Set default file name
openFileDialog1.FileName = "";
// Filter file type from selection results
openFileDialog1.Filter = "Microsoft Excel Worksheet (.xlsx)|*.xlsx";

if (openFileDialog1.ShowDialog() == true)
{
// Assign chosen filename & location to variable "File_Name"
this.File_Name.Text = openFileDialog1.FileName;
}
}

public void Load_Button_Click(object sender, RoutedEventArgs e)
{
// Create & initialize "PathConn" variable with instructions for file to load
string PathConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File_Name.Text + ";Extended Properties=\"Excel 8.0;HDR=Yes;\";";
// Construct OleDbConnection object using instructions in PathConn string
OleDbConnection conn = new OleDbConnection(PathConn);

// Construct OleDbDataAdapter. Instructions to load all data from specified sheet
OleDbDataAdapter myDataAdapter = new OleDbDataAdapter("Select * from [" + Sheet.Text + "$]", conn);

// Construct DataTable object
System.Data.DataTable dt = new System.Data.DataTable();
//
myDataAdapter.AcceptChangesDuringFill.GetType();
// Fill myDataAdapter with DataTable object contents
myDataAdapter.Fill(dt);

dataGrid1.ItemsSource = dt.DefaultView;
dataGrid1.SelectionUnit = DataGridSelectionUnit.Cell;



}


public void Process_Click(object sender, RoutedEventArgs e)
{
CreateParameters();
MessageBox.Show("Process Button Click Detected");


}
public void CreateParameters()
{

OleDbConnection createConnection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + File_Name.Text + ";Extended Properties=\"Excel 12.0 Xml;HDR=YES\";");
createConnection.Open();
OleDbCommand addCommand = new OleDbCommand();

//OleDbParameterCollection paramCollection = addCommand.Parameters;
addCommand.Parameters.Add(new OleDbParameter("New Column", OleDbType.LongVarChar));
OleDbDataReader reader = addCommand.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader[0].ToString());
}
reader.Close();

}
}
}




The code above the Process_Click() method is all functioning as expected. The train wreck begins when I try to set up an OleDbCommand to add the new column. My problem is a typical noob problem of me just not knowing what I dont know.

My questions are as follows:

What commands are required to open the OleDbConnection?

What is the correct syntax to insert the new column, select the value from the single cell, then add the database value to the new column?


I realize its a lot to digest, and a big challenge Im requesting help with. But I have spent countless days plugging away and trying to find answers. Any help with this challenge would be greatly appreciated.

Continue reading...