private void openDirectoryToolStripMenuItem_Click(object sender, EventArgs e)
{
try {
String fname = "";
String regx1 = ".*";
string folderpath = "";
this.Cursor = Cursors.WaitCursor;
FolderBrowserDialog folderBrowserDialog1 = new FolderBrowserDialog();
if (folderBrowserDialog1.ShowDialog() == DialogResult.OK)
{
int x = 0;
folderpath = folderBrowserDialog1.SelectedPath;
//MessageBox.Show(folderpath);
ExcelWorkbook[] workbooks = ExcelWorkbookFinder.GetAllWorkbooks(folderpath);
foreach (ExcelWorkbook fwork in workbooks)
{
foreach (ExcelParser.ExcelSpreadsheet fsheet in fwork.Spreadsheets)
{
dbObject1.CreateTable(fsheet);
}
}
for (int i = 0; i < workbooks.Length; i++)
{
for (int j = 0; j < workbooks[i].Spreadsheets.Count; j++)
{
workSheetList.Items.Add(workbooks[i].Spreadsheets[j].Name);
if (i == 0 && j == 0 && x == 0)
{
fname = workbooks[i].Spreadsheets[j].Name;
}
x++;
}
}
currentTableName = fname;
dataGridView1.DataSource = dbObject1.ReadTable(workbooks[0].Spreadsheets[0].Name);
}
//MessageBox.Show(regx1);
//MessageBox.Show(folderpath);
dbObject1.ExecuteQuery("INSERT INTO sqlregularexp (regexp, directorypath) VALUES ('" + regx1 + "','" + folderpath + "');");
queryBox.Text = "Select * From [" + fname + "]";
this.Cursor = Cursors.Default;
}
catch (Exception ex)
{
MessageBox.Show("An Error Occurred: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void RefreshButton_Click(object sender, EventArgs e)
{
try {
String fname = "";
dbObject1.DropTables();
workSheetList.Items.Clear();
DataTable repopulate = dbObject1.ReadTable("sqlregularexp");
foreach (DataRow dr in repopulate.Rows)
{
int x = 0;
string regex = (string)dr[0];
string dpath = (string)dr[1];
// MessageBox.Show(regex);
// MessageBox.Show(dpath);
ExcelWorkbook[] workbooks = ExcelWorkbookFinder.GetAllWorkbooks(dpath, regex, true);
//ExcelParser.ExcelSpreadsheet spreadsheet = workbooks[0].First(s => s.Name == "test_Sheet1");
foreach (ExcelWorkbook fwork in workbooks)
{
foreach (ExcelParser.ExcelSpreadsheet fsheet in fwork.Spreadsheets)
{
dbObject1.CreateTable(fsheet);
workSheetList.Items.Add(fsheet.Name);
if (x == 0)
{
fname = workbooks[0].Spreadsheets[0].Name;
currentTableName = fname;
}
x++;
}
}
}
dataGridView1.DataSource = dbObject1.ReadTable(fname);
}
catch (Exception ex)
{
MessageBox.Show("An Error Occurred: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
private void openWorkbookToolStripMenuItem_Click(object sender, EventArgs e)
{
try {
OpenFileDialog openFileDialog1 = new OpenFileDialog();
String pubdirect = "";
StringBuilder regx2 = new StringBuilder();
openFileDialog1.InitialDirectory = "c:\\";
openFileDialog1.Filter = "All files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx";
openFileDialog1.FilterIndex = 2;
openFileDialog1.RestoreDirectory = true;
openFileDialog1.Multiselect = true;
String fname = "";
if (openFileDialog1.ShowDialog() == DialogResult.OK)
{
int x = 0;
int y = 0;
this.Cursor = Cursors.WaitCursor;
foreach (string fileName in openFileDialog1.FileNames)
{
if (y == 1)
{
regx2.Append("|");
}
regx2.Append(Path.GetFileNameWithoutExtension(fileName));
regx2.Append(@"\.xlsx");
y = 1;
//string strfilename = openFileDialog1.InitialDirectory + fileName;
string strdirect = Path.GetDirectoryName(fileName);
pubdirect = strdirect;
string justName = Path.GetFileName(fileName);
//MessageBox.Show(strdirect);
//MessageBox.Show(justName);
ExcelWorkbook[] workbooks = ExcelWorkbookFinder.GetAllWorkbooks(strdirect, justName, false);
//ExcelParser.ExcelSpreadsheet spreadsheet = workbooks[0].First(s => s.Name == "test_Sheet1");
foreach (ExcelWorkbook fwork in workbooks)
{
foreach (ExcelParser.ExcelSpreadsheet fsheet in fwork.Spreadsheets)
{
dbObject1.CreateTable(fsheet);
}
}
if (x == 0)
{
fname = workbooks[0].Spreadsheets[0].Name;
}
//toSend = displayfirst + + "]";
currentTableName = fname;
dataGridView1.DataSource = dbObject1.ReadTable(workbooks[0].Spreadsheets[0].Name);
workSheetList.Items.Add(workbooks[0].Spreadsheets[0].Name);
x++;
}
}
// MessageBox.Show(regx2.ToString());
// MessageBox.Show(pubdirect);
dbObject1.ExecuteQuery("INSERT INTO sqlregularexp (regexp, directorypath) VALUES ('" + regx2.ToString() + "','" + pubdirect + "');");
queryBox.Text = "Select * From [" + fname + "]";
this.Cursor = Cursors.Default;
}
catch (Exception ex)
{
MessageBox.Show("An Error Occurred: " + ex.Message.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}