/// <summary> /// Uses Excel automation to get sheet names, extremely slow compared to OpenXml below /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void cmdGetSheetNamesAutomation_Click(object sender, EventArgs e) { var example = new ExcelBaseExample(); var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx"); var sheetNames = example.GetWorkSheetNames(fileName); sheetNames.Insert(0, "Select sheet"); cboSheetNamesAutomation.DataSource = sheetNames; }
/// <summary> /// Uses excel automation to read targeted cells in a specific worksheet. /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void cmdSimpleOpenReadExample_Click(object sender, EventArgs e) { txtResults.Clear(); if (cboCustomerSheetNames.Text == "Select sheet") { MessageBox.Show("Please select a sheet name"); return; } var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx"); /* * A very simply example to read cells in the dictionary */ var example = new ExcelBaseExample { ReturnDictionary = new Dictionary <string, object>() { { "A1", null }, { "B2", null }, { "B3", null }, { "B4", null }, { "C2", null }, { "C4", null }, { "C5", null } } }; /* * Read the cells in the Excel file and iterate the * cell data to a textBox. */ example.ReadCells(fileName, cboCustomerSheetNames.Text); if (example.HasErrors) { txtResults.Text = example.ExceptionInfo.ToString(); } else { foreach (var kvp in example.ReturnDictionary) { txtResults.AppendText($"{kvp}{Environment.NewLine}"); } txtResults.SelectionStart = 1; txtResults.ScrollToCaret(); } }
/// <summary> /// Get last used row and column for a sheet using Excel automation /// </summary> /// <param name="sender"></param> /// <param name="e"></param> /// <remarks> /// This code sample also implements a delegate which reports progress /// to the listbox on this form. This is an alternate to running the /// Visual Studio debugger stepping through code looking for issues. /// /// It does not rule out using the debugger all the time just an alternate /// for some operations. /// </remarks> private void cmdUsedRowsColumns_Click(object sender, EventArgs e) { listBox1.Items.Clear(); var example = new ExcelBaseExample(); example.ProgressUpdated += new EventHandler <ExaminerEventArgs>(ShowProgress); var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Demo.xlsx"); var sheetName = "Sheet1"; var result = example.UsedRowsColumns(fileName, "Sheet1"); txtUsedRowsColumns.Text = $"{Path.GetFileName(fileName)}.{sheetName} {result}"; }