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); } }