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