/// <summary>
/// Example to get used range via OpenXml
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdUsedRangeOpenXml_Click(object sender, EventArgs e)
{
var helper = new OpenXmlExamples();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx");
var sheetName = "Customers";
var customers = helper.ClassToExcelReaderServiceReader(fileName);
var result = helper.UsedRange(fileName, sheetName);
txtUsedRangeOpenXml.Text = $"{Path.GetFileName(fileName)}.{sheetName} {result}";
}
/// <summary>
/// Uses OpenXml to read sheet names, extremely fast,
/// the vb.net xml example is just as fast.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdGetSheetNamesXml_Click(object sender, EventArgs e)
{
var helper = new OpenXmlExamples();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx");
var sheetNames = helper.SheetNames(fileName);
if (helper.IsSuccessFul)
{
sheetNames.Insert(0, "Select sheet");
cboGetSheetNamesXml.DataSource = sheetNames;
}
else
{
Dialogs.ExceptionDialog(helper.LastException);
}
}
/// <summary>
/// Create a new Excel file in the application folder with
/// a hard coded file name and sheet name to keep it simple.
///
/// If you run the code, create the file, open the file via
/// Windows Explorer then try to create we fail as the file
/// is in use.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdCreateWithOpenXml_Click(object sender, EventArgs e)
{
var helper = new OpenXmlExamples();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Cust.xlsx");
var sheetName = "Customers";
helper.CreateNewFile(fileName, sheetName);
if (helper.IsSuccessFul)
{
Dialogs.InformationDialog("Excel file created");
}
else
{
Dialogs.ExceptionDialog(helper.LastException);
}
}
/// <summary>
/// Example using a wrapper library over OpenXml to read a specific worksheet
/// where the data will match up to a concrete class, in this case Customer.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdReadUsingWrapperLibOverOpenXml_Click(object sender, EventArgs e)
{
var helper = new OpenXmlExamples();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx");
var customers = helper.ClassToExcelReaderServiceReader(fileName);
var f = new frmCustomers(customers);
try
{
f.ShowDialog();
}
finally
{
f.Dispose();
}
}
/// <summary>
/// Setup
/// * Get fresh copies of Excel files.
/// * Get sheet names from Customers.xlsx via OpenXml
/// * Setup a timer to watch for Excel processes open.
/// This assumes you don't have Excel open outside this app.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void Form1_Shown(object sender, EventArgs e)
{
timerForExcel.Enabled = true;
/*
* Start with a clean slate in regards to base Excel files
*/
_fileOperations.RemoveExcelFiles();
/*
* These files were not copied over
*/
_fileOperations.RemoveOtherExcelFiles(new[]
{
"Cust.xlsx",
"PeopleDemo.xlsx",
"BadPeopleDemo.xlsx",
"Customers.xml"
});
/*
* Copy files from base file folder
*/
_fileOperations.CopyExcelFiles();
if (_fileOperations.HasException)
{
MessageBox.Show($"Issues with file delete/copy{Environment.NewLine}{_fileOperations.LastExceptionMessage}");
}
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Customers.xlsx");
/*
* There is also an automated method that does the
* same as this in ExcelBase class. This method was
* used as it's faster than using automation.
*/
var helper = new OpenXmlExamples();
var sheetNames = helper.SheetNames(fileName);
sheetNames.Insert(0, "Select sheet");
cboCustomerSheetNames.DataSource = sheetNames;
}
/// <summary>
/// Using OpenXML create a new Excel file, setup a WorkSheet
/// and populate the WorkSheet with data read from SQL-Server
/// using two tables joined together.
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void cmdCreateAndFillWithOpenXml_Click(object sender, EventArgs e)
{
var ops = new DataOperations();
var people = ops.GetPeople();
var xmlOps = new OpenXmlExamples();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "PeopleDemo.xlsx");
var sheetName = "People";
xmlOps.CreateExcelDocPopulateWithPeople(fileName, sheetName, people);
if (xmlOps.IsSuccessFul)
{
Dialogs.InformationDialog("Excel file created and populated");
}
else
{
Dialogs.ExceptionDialog(xmlOps.LastException);
}
}
//
private void ReadLargeTable()
{
var helper = new OpenXmlExamples();
var fileName = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Large.xlsx");
var customers = helper.ClassToExcelReaderServiceReaderForLargeTable(fileName);
}
