private void BtnGetSheets_Click(object sender, EventArgs e)
{
if (ValidationLogic.InvalidFile(TbxFilePath))
{
ErrorActions.InvalidFile();
}
else
{
try
{
/* using form inputs open the file and loop through all sheets within
* the workbook, adding the name of each sheet encountered into a list
* box that the user can select from. */
string file = TbxFilePath.Text.ToString();
ExcelThread.OpenWorkbook(file);
ExcelThread.GetSheets();
LbxSheetNames.Items.Clear();
for (int i = 1; i <= ExcelThread.Worksheets.Count; i++)
{
Microsoft.Office.Interop.Excel.Worksheet sheet = ExcelThread.Worksheets[i];
LbxSheetNames.Items.Add(sheet.Name.ToString());
}
// make the next stage of the process/application visible.
GrpSheetAndHeader.Visible = true;
}
catch
{
ErrorActions.FailOpenFile();
}
}
}
/* this method is designed to allow the user to perform a soft reset and reset
* the application to the state direcly after pressin the 'get headers or
* columns' button. The intent is to allow mutiple agings of the same data
* making use of different headers on subsquent agings. */
private void ReAgeCurrentFileToolStripMenuItem_Click(object sender, EventArgs e)
{
// subset of get sheets button event.
ExcelThread.Reset();
string file = TbxFilePath.Text.ToString();
ExcelThread.OpenWorkbook(file);
ExcelThread.GetSheets();
// subset of the get headers button event.
if (ValidationLogic.NoItemSelected(LbxSheetNames))
{
ErrorActions.NoSheetSelected();
}
else
{
string sheetWithData = LbxSheetNames.SelectedItem.ToString();
ExcelThread.Worksheet = ExcelThread.Worksheets[sheetWithData];
if (ValidationLogic.NoColumnsFound(ExcelThread.Worksheet))
{
ErrorActions.NoColumnsFound();
}
else
{
BuildHeaders();
PrepForAging();
}
}
}
public static string Address([ExcelReference] XlOper o)
{
ExcelThread xlt = ExcelThread.Current;
var ret = xlt.Call(Excel.Functions.GetCell, 1, o);
return(ret.AsString());
}
// the saving process writes a formated excel file to a location of the users choice.
private void BtnSave_Click(object sender, EventArgs e)
{
saveFileDialog.Filter = "Excel (*.xlsx)|*.xlsx";
saveFileDialog.ShowDialog();
// clear thread of our dataset connection and add a connection to our new in memory file.
ExcelThread.Reset();
ExcelThread.Workbook = ExcelThread.App.Workbooks.Add();
ExcelThread.Worksheet = ExcelThread.Workbook.Worksheets[1];
// Write the title and header information.
ExcelThread.Worksheet.Cells[1, 1] = "Aging results for file " +
TbxFilePath.Text.ToString();
ExcelThread.Worksheet.Cells[2, 1] = "being performed on " +
LbxDatesToAgeBy.SelectedItem.ToString() +
" from " +
DtpAnchorDate.Value.ToString("MM/dd/yyyy");
ExcelThread.Worksheet.Cells[4, 1] = "Days Included";
ExcelThread.Worksheet.Cells[4, 2] = "Summary of " +
LbxDataToAge.SelectedItem.ToString();
// Write the names and values of our aging buckets.
ExcelThread.Worksheet.Cells[5, 1] = "'000 - 030";
ExcelThread.Worksheet.Cells[6, 1] = "'031 - 060";
ExcelThread.Worksheet.Cells[7, 1] = "'061 - 090";
ExcelThread.Worksheet.Cells[8, 1] = "'091 - 120";
ExcelThread.Worksheet.Cells[9, 1] = "'121 - 150";
ExcelThread.Worksheet.Cells[10, 1] = "'151 - 180";
ExcelThread.Worksheet.Cells[11, 1] = "'181 - 270";
ExcelThread.Worksheet.Cells[12, 1] = "'271 - 360";
ExcelThread.Worksheet.Cells[13, 1] = "'361 - +++";
ExcelThread.Worksheet.Cells[5, 2] = AgedData.Day0To30;
ExcelThread.Worksheet.Cells[6, 2] = AgedData.Day31To60;
ExcelThread.Worksheet.Cells[7, 2] = AgedData.Day61To90;
ExcelThread.Worksheet.Cells[8, 2] = AgedData.Day91To120;
ExcelThread.Worksheet.Cells[9, 2] = AgedData.Day121To150;
ExcelThread.Worksheet.Cells[10, 2] = AgedData.Day151To180;
ExcelThread.Worksheet.Cells[11, 2] = AgedData.Day181To270;
ExcelThread.Worksheet.Cells[12, 2] = AgedData.Day271To360;
ExcelThread.Worksheet.Cells[13, 2] = AgedData.Day361Plus;
// perform some formatting to ensure good readability of the final file.
ExcelThread.Worksheet.Range["A:A"].ColumnWidth = 13;
ExcelThread.Worksheet.Range["B:B"].ColumnWidth = 16;
ExcelThread.Worksheet.Range["A4:A13"].HorizontalAlignment = HorizontalAlignment.Center;
ExcelThread.Worksheet.Range["B5:B13"].NumberFormat = "_($* #,##0.00_)";
// finally write our file from memory to our location on disk.
ExcelThread.Workbook.SaveAs(saveFileDialog.FileName);
}
public static Array Expand(Array a, int rowOffset = 0, int colOffset = 1, string refTxt = null)
{
ExcelThread xlt = ExcelThread.Current;
XlOper o = refTxt == null || refTxt == "" ? xlt.GetCaller() : xlt.Call(Excel.Functions.Indirect, refTxt);
var des = xlt.Call(Excel.Functions.Offset, o, rowOffset, colOffset, a.GetLength(0), a.GetLength(1));
Excel.AsyncRangeUpdate(xlt.RefText(des, false), a);
return(a);
//var app = Excel.Application;
//app.GetType().InvokeMember("Run", BindingFlags.InvokeMethod, null, app, new object[] { "QDefineRangeName", "hh" });
//object workbook = app.GetType().InvokeMember("ActiveWorkbook", BindingFlags.GetProperty, null, app, null);
//object properties = app.GetType().InvokeMember("BuiltinDocumentProperties", BindingFlags.GetProperty, null, workbook.GetType(), null);
//object property = app.GetType().InvokeMember("Item", BindingFlags.GetProperty, null, properties, new object[] { "Author" });
//object value = app.GetType().InvokeMember("Value", BindingFlags.GetProperty, null, property, null);
//xlt.CallPrivileged(Excel.Commands.DefineName, "hehe", "$A$2");
//xlt.CallUDF("QDefineRangeName", "hh");
}
private void ConfigureApplication()
{
// ensuring all properties are clear except for the 'App' Excel application pointer
ExcelThread.Reset();
// ensure form components are in a state ready to accept used input
DtpAnchorDate.Value = DateTime.Today;
RbtHeadNo.Checked = false;
RbtHeadYes.Checked = false;
GrpSheetAndHeader.Visible = false;
GrpColumnAndAging.Visible = false;
BtnGetHeaders.Visible = false;
TbxFilePath.ResetText();
saveFileDialog.Reset();
openFileDialog.Reset();
LbxSheetNames.Items.Clear();
PrepForAging(); /* broke out some of the application configuration to
* support 'reseting' the application in order to allow
* the user to age the same excel data set based on
* differing input conditions */
// ensure our results accumilator is ready for use.
AgedData.Zero();
}
/* Since this is only a one form application the decision was made to clean up
* the reserved excel process and thread no mater the method the form was
* closed. The intent was to attempt to catch and not leave orphaned process
* threads. */
private void AgingTool_FormClosing(object sender, FormClosingEventArgs e)
{
ExcelThread.Shutdown();
}
private void ResetApplicationToolStripMenuItem_Click(object sender, EventArgs e)
{
ConfigureApplication();
ExcelThread.Reset();
}
