/// <summary>
/// Ensure the worksheet only has rows with actual data. Remove cells with blanks, total rows, etc.
/// </summary>
/// <param name="ws"></param>
private void CleanOutExtraDataOnWorksheet(Worksheet ws)
{
Common.WriteToDebugWindow(String.Format("CleanOutExtraDataOnWorksheet()"));
switch (FileType)
{
case "FundServiceFees":
case "FundAdvisoryFees":
// TODO: May need different end columns. For now presume the 6 from fees spreadsheets.
// Take the switch(FileType) code from GetOutputFileName();
Range dataRange = ws.Range[ws.Cells[1, 1], ws.Cells[RecordCount, 6]];
dataRange.Copy();
Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.Add();
((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Paste();
// Quietly delete the source worksheet to avoid confusion.
ExcelHlp.DeleteSheet(ws);
break;
case "CashManagementFees":
break;
case "VITsFees":
ProcessVITsFees(ws);
break;
default:
MessageBox.Show("Processing of Manual FileType: {0} not currently supported", FileType);
break;
}
}
private Workbook OpenManualFile()
{
string initialDirectory = String.Format(@"{0}\{1}\{2}",
Path, RelativePath, "Original_Files");
Common.WriteToDebugWindow(String.Format(@"OpenFile: {0}", initialDirectory));
Workbook wb = null;
wb = Globals.ThisAddIn.Application.Workbooks.Open(
Filename: ExcelHlp.GetOpenFileName(initialDirectory),
UpdateLinks: false,
ReadOnly: true);
if (wb != null)
{
pnlTask_OpenFile.BackColor = Color.Green;
pnlTask_ConfirmAsOfDate.BackColor = Color.White;;
}
else
{
pnlTask_OpenFile.BackColor = Color.Red;
}
return(wb);
}
private void SaveControlFile()
{
Common.WriteToDebugWindow(String.Format("SaveControlFile:{0},{1},{2},{3:F2}",
CycleDate.ToShortDateString(), CurrentDate.ToShortDateString(), RecordCount, TotalAmount));
string fileName = null;
string initialDirectory = OutputPath;
if (null != (fileName = GetControlFileName()))
{
string outputFile = ExcelHlp.GetSaveFileName(initialDirectory, fileName);
FileInfo fileInfo = new FileInfo(outputFile);
OutputPath = fileInfo.DirectoryName;
using (StreamWriter writer = new StreamWriter(outputFile))
{
// TODO:(crhodes) Remove TotalAmount for CMF output
writer.Write(String.Format("{0},{1},{2},{3:F2}",
CycleDate.ToShortDateString(), CurrentDate.ToShortDateString(), RecordCount, TotalAmount));
writer.Close();
}
pnlTask_SaveControlFile.BackColor = Color.Green;
}
else
{
pnlTask_SaveControlFile.BackColor = Color.Red;
}
}
void _ExcelApplication_WorkbookBeforePrint(Microsoft.Office.Interop.Excel.Workbook Wb, ref bool Cancel)
{
DisplayInWatchWindow(WorkbookBeforePrint++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
if (ExcelHlp.HasCustomFooter())
{
Actions.Excel_PageFormatting.AddFooter();
}
}
private void GetITRInformation()
{
Range currentCell = Globals.ThisAddIn.Application.ActiveCell;
int firstRow = currentCell.Row;
//Dim lastRow As Integer = currentCell.SpecialCells(Constants.xlLastCell).Row
int lastRow = ExcelHlp.FindLast_PopulatedRow_InColumn(currentCell);
//Dim foundRows As Hashtable = New Hashtable()
Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
Range itrRow = default(Range);
//Debug.Print(String.Format("{0} - {1}", firstRow, lastRow))
//Dim ta As ApplicationDSTableAdapters.sp_ITRDetailTableAdapter = New ApplicationDSTableAdapters.sp_ITRDetailTableAdapter()
Data.ApplicationDS.sp_ITRDetailRow itrDataRow = null;
//Dim itrDataTable As ApplicationDS.sp_ITRDetailDataTable
//Dim tr As ApplicationDS.sp_ITRDetailRow
for (int i = firstRow; i <= lastRow; i++)
{
itrRow = activeSheet.Cells[i, currentCell.Column];
Common.ApplicationDS.sp_ITRDetail.ImportRow(itrDataRow);
itrRow.Cells[1, 2].Value = itrDataRow.Subject;
itrRow.Cells[1, 3].Value = itrDataRow.Application.Replace("/Life IT/Technical Services/", "");
try
{
itrRow.Cells[1, 4].Value = itrDataRow.Description.Length;
// Current Condition
}
catch (Exception)
{
itrRow.Cells[1, 4].Value = "<none>";
}
try
{
itrRow.Cells[1, 5].Value = itrDataRow.Justification.Length;
// Desired Outcome
}
catch (Exception)
{
itrRow.Cells[1, 5].Value = "<none>";
}
try
{
itrRow.Cells[1, 6].Value = itrDataRow.Comments.Length;
// Comments
}
catch (Exception)
{
itrRow.Cells[1, 6].Value = "<none>";
}
}
}
private void SearchRight()
{
Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
Range activeCell = Globals.ThisAddIn.Application.ActiveCell;
int matchRow = activeCell.Row;
int matchColumn = ExcelHlp.FindNext_PopulatedColumn_InRow(activeCell);
activeSheet.Cells[matchRow, matchColumn].Select();
}
private void SearchUp()
{
Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
Range activeCell = Globals.ThisAddIn.Application.ActiveCell;
int matchRow = ExcelHlp.FindPrevious_PopulatedRow_InColumn(activeCell);
int matchColumn = activeCell.Column;
activeSheet.Cells[matchRow, matchColumn].Select();
}
void _ExcelApplication_WorkbookNewSheet(Microsoft.Office.Interop.Excel.Workbook Wb, object Sh)
{
DisplayInWatchWindow(WorkbookNewSheet++, System.Reflection.MethodInfo.GetCurrentMethod().Name);
if (ExcelHlp.HasCustomTableOfContents())
{
DisplayInWatchWindow(1, System.Reflection.MethodInfo.GetCurrentMethod().Name + " - AddTableOfContents()");
Actions.Excel_TableOfContents.CreateTableOfContents();
}
}
public static void CreateTableOfContents()
{
Worksheet tableOfContents;
int row = 3; // Starting Row
int column = 1; // Starting Column
Boolean currentSheetProtectionMode;
Workbook activeWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
try
{
tableOfContents = activeWorkbook.Sheets["Table of Contents"];
}
catch (Exception)
{
tableOfContents = ExcelHlp.NewWorksheet("Table of Contents", beforeSheetName: "FIRST");
//tableOfContents = activeWorkbook.Sheets.Add();
//tableOfContents.Name = "Table of Contents";
}
tableOfContents.Columns["A:A"].ClearContents();
foreach (Worksheet sheet in activeWorkbook.Sheets)
{
switch (sheet.Name)
{
case "Table of Contents":
break;
default:
// Unprotect the sheet before adding the hyperlink
currentSheetProtectionMode = ExcelHlp.ProtectSheet(sheet, false);
sheet.Cells[1, 1].Value = "Table of Contents";
sheet.Hyperlinks.Add(Anchor: sheet.Cells[1, 1], Address: "", SubAddress: "'" + tableOfContents.Name + "'!A1", TextToDisplay: tableOfContents.Name);
// Then restore the setting
ExcelHlp.ProtectSheet(sheet, currentSheetProtectionMode);
// Now update the Table of Contents Sheet
tableOfContents.Cells[row, column].Value = sheet.Name;
tableOfContents.Hyperlinks.Add(Anchor: tableOfContents.Cells[row, column], Address: "", SubAddress: "'" + sheet.Name + "'!A1", TextToDisplay: sheet.Name);
row += 1;
break;
}
}
tableOfContents.Columns["A:A"].EntireColumn.AutoFit();
if (!ExcelHlp.HasCustomTableOfContents())
{
ExcelHlp.CustomTableOfContentsExists(true);
}
}
private void SaveOutputFile()
{
string outputFileName = GetDefaultOutputFileName();
var _with8 = Globals.ThisAddIn.Application;
outputFileName = ExcelHlp.GetSaveFileName(Common.cDEFAULT_FOLDER, outputFileName, "Enter Save As Name");
if (outputFileName.Length > 0)
{
_with8.ActiveWorkbook.SaveAs(Filename: outputFileName, FileFormat: XlFileFormat.xlOpenXMLWorkbook, CreateBackup: false);
}
}
private static void DuplicateInputSheet()
{
Workbook activeWorkbook = Globals.ThisAddIn.Application.ActiveWorkbook;
var _with5 = Globals.ThisAddIn;
ExcelHlp.DuplicateWorksheet("SourceITRs", "ITRInfo", "SourceITRs");
activeWorkbook.Sheets["ITRInfo"].Columns[Common.cITRInfo_CommentColumns].Delete();
ExcelHlp.DuplicateWorksheet("SourceITRs", "ITRInfoWithResources", "ITRInfo");
activeWorkbook.Sheets["ITRInfoWithResources"].Columns[Common.cITRITRInfoWithResources_CommentColumns].Delete();
ExcelHlp.DuplicateWorksheet("SourceITRs", "FormatedITRs", "SourceITRs");
}
private void LoadInfoFromWorksheet_FileControl()
{
CountFC_ClaimRecord = ExcelHlp.FindLast_PopulatedRow_InColumn(_wsFileControl.Range["A1"]) - 1;
// Need populate these as sum of values.
Range sumRange;
//string sumRangeString;
//sumRangeString = string.Format("D2:D{0}", CountFC_ClaimRecord + 1);
sumRange = _wsFileControl.Range[string.Format("D2:D{0}", CountFC_ClaimRecord + 1)];
CountFC_ClaimPayment = (int)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
//sumRangeString = string.Format("E2:E{0}", CountFC_ClaimRecord + 1);
//sumRange = _wsFileControl.Range[sumRangeString];
sumRange = _wsFileControl.Range[string.Format("E2:E{0}", CountFC_ClaimRecord + 1)];
CountFC_XOBUDSCode = (int)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("F2:F{0}", CountFC_ClaimRecord + 1)];
ClaimPaymentTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("G2:G{0}", CountFC_ClaimRecord + 1)];
LoanPaymentTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("H2:H{0}", CountFC_ClaimRecord + 1)];
InterestPaymentTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("I2:I{0}", CountFC_ClaimRecord + 1)];
CareGiverTrainingTotal = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("J2:J{0}", CountFC_ClaimRecord + 1)];
TransitionPaymentTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("K2:K{0}", CountFC_ClaimRecord + 1)];
FederalWithholdingTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("L2:L{0}", CountFC_ClaimRecord + 1)];
StateWitholdingTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("M2:M{0}", CountFC_ClaimRecord + 1)];
CityWithholdingTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("N2:N{0}", CountFC_ClaimRecord + 1)];
CheckAmountTotalAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
sumRange = _wsFileControl.Range[string.Format("O2:O{0}", CountFC_ClaimRecord + 1)];
XOBUDSCodeAmount = (float)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
}
/// <summary>
/// Ensure the worksheet only has rows with actual data. Remove cells with blanks, total rows, etc.
/// </summary>
/// <param name="ws"></param>
private void CleanOutExtraDataOnWorksheet(Worksheet ws)
{
Common.WriteToDebugWindow(String.Format("CleanOutExtraDataOnWorksheet()"));
// TODO: May need different end columns. For now presume the 6 from fees spreadsheets.
// Take the switch(FileType) code from GetOutputFileName();
Range dataRange = ws.Range[ws.Cells[1, 1], ws.Cells[RecordCount, 6]];
dataRange.Copy();
Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.Add();
((Worksheet)Globals.ThisAddIn.Application.ActiveSheet).Paste();
// Quietly delete the source worksheet to avoid confusion.
ExcelHlp.DeleteSheet(ws);
}
private void ProcessFundServiceFees(Worksheet ws)
{
Common.WriteToDebugWindow(String.Format("ProcessFundServiceFees()"));
// Delete the top two rows
((Range)ws.Rows[1]).Delete();
((Range)ws.Rows[1]).Delete();
// Remove the commas
((Range)ws.Columns["E:E"]).NumberFormat = "0.00";
// Determine the number of rows
RecordCount = ExcelHlp.FindLast_PopulatedRow_InColumn(ws.Range["A1"]);
// Replace the Fund Codes
UpdateFundCodes(ws);
// Add a new column
ws.Columns["A:A"].Insert(Shift: XlDirection.xlToRight, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove);
// And populate with As of Date
DateTime asOfDate = DateTime.Parse(dtpAsOfDate.Text);
ws.Range[ws.Cells[1, 1], ws.Cells[RecordCount, 1]].Value = asOfDate.ToShortDateString();
// Gather some information for the control file
Range sumRange = ws.Range[string.Format("F1:F{0}", RecordCount)];
TotalAmount = (double)Globals.ThisAddIn.Application.WorksheetFunction.Sum(sumRange);
CleanOutExtraDataOnWorksheet(ws);
Common.WriteToDebugWindow(string.Format("CycleDate:{0},CurrentDate:{1},RecordCount={2},TotalAmount={3}",
CycleDate, CurrentDate, RecordCount, TotalAmount));
}
private void GetRowColInfo()
{
Range rng = Globals.ThisAddIn.Application.ActiveCell;
txtLastRowSpecial.Text = rng.SpecialCells(XlCellType.xlCellTypeLastCell).Row.ToString();
txtLastColSpecial.Text = rng.SpecialCells(XlCellType.xlCellTypeLastCell).Column.ToString();
// TODO (crhodes): Make FindXXXPopulated{Row,Col} take an enum: First, Next,Previous, Last
txtFirstRowSearch.Text = ExcelHlp.FindFirst_PopulatedRow_InColumn(rng).ToString();
txtFirstColSearch.Text = ExcelHlp.FindFirst_PopulatedColumn_InRow(rng).ToString();
txtPreviousRowSearch.Text = ExcelHlp.FindPrevious_PopulatedRow_InColumn(rng).ToString();
txtPreviousColSearch.Text = ExcelHlp.FindPrevious_PopulatedColumn_InRow(rng).ToString();
txtNextRowSearch.Text = ExcelHlp.FindNext_PopulatedRow_InColumn(rng).ToString();
txtNextColSearch.Text = ExcelHlp.FindNext_PopulatedColumn_InRow(rng).ToString();
txtLastRowSearch.Text = ExcelHlp.FindLast_PopulatedRow_InColumn(rng).ToString();
txtLastColSearch.Text = ExcelHlp.FindLast_PopulatedColumn_InRow(rng).ToString();
}
private void SaveDetailFile()
{
Common.WriteToDebugWindow(String.Format(@"SaveDetailFile: {0} - {1}\{2}", FileType, Path, RelativePath));
string fileName = null;
string initialDirectory = String.Format(@"{0}\{1}", Path, RelativePath);
Workbook wb = Globals.ThisAddIn.Application.ActiveWorkbook;
if (null != (fileName = GetOutputFileName()))
{
string outputFile = ExcelHlp.GetSaveFileName(initialDirectory, fileName);
FileInfo fileInfo = new FileInfo(outputFile);
OutputPath = fileInfo.DirectoryName;
wb.SaveAs(Filename: outputFile, FileFormat: XlFileFormat.xlCSV);
pnlTask_SaveDetailFile.BackColor = Color.Green;
}
else
{
pnlTask_SaveDetailFile.BackColor = Color.Red;
}
}
public static void AddFooter()
{
Globals.ThisAddIn.Application.PrintCommunication = false;
Workbook workBook;
//Worksheet workSheet;
StringBuilder sb = new StringBuilder();
try
{
workBook = Globals.ThisAddIn.Application.ActiveWorkbook;
ExcelHlp.CalculationsOff();
foreach (Worksheet workSheet in workBook.Sheets)
{
sb.Length = 0;
sb.Append("&5&Z&F"); // Five point font, path, and filename
sb.AppendFormat("\nCreated: {0} By: {1}",
ExcelHlp.GetBuiltInPropertyValue(workBook, "Creation Date"),
ExcelHlp.GetBuiltInPropertyValue(workBook, "Author"));
sb.AppendFormat("\nLast Saved: {0} By: {1}",
ExcelHlp.GetBuiltInPropertyValue(workBook, "Last Save Time"),
ExcelHlp.GetBuiltInPropertyValue(workBook, "Last author"));
sb.AppendFormat("\nLast Printed: {0}",
ExcelHlp.GetBuiltInPropertyValue(workBook, "Last Print Date"));
if (Common.DebugMode)
{
Common.WriteToDebugWindow("LeftFooter:>" + sb.ToString() + "<");;
}
workSheet.PageSetup.LeftFooter = sb.ToString();
workSheet.PageSetup.CenterFooter = "";
sb.Length = 0;
sb.Append("&5&P - &N"); // Five point font, page of pages
sb.Append("\nTitle :");
sb.Append(ExcelHlp.GetBuiltInPropertyValue(workBook, "Title"));
sb.Append("\nSubject: ");
sb.Append(ExcelHlp.GetBuiltInPropertyValue(workBook, "Subject"));
if (Common.DebugMode)
{
Common.WriteToDebugWindow("RightFooter:>" + sb.ToString() + "<");;
}
workSheet.PageSetup.RightFooter = sb.ToString();
// Indicate we have added a custom footer.
// This gets checked in the BeforeClose event.
if (!ExcelHlp.HasCustomFooter())
{
ExcelHlp.CustomFooterExists(true);
}
}
ExcelHlp.CalculationsOn();
}
catch (Exception ex)
{
MessageBox.Show("AddFooter:" + ex.ToString());
}
Globals.ThisAddIn.Application.PrintCommunication = true;
}
private void MergeDuplicateRows()
{
Range currentCell = Globals.ThisAddIn.Application.ActiveCell;
int firstRow = currentCell.Row;
//Dim lastRow As Integer = currentCell.SpecialCells(Constants.xlLastCell).Row
int lastRow = ExcelHlp.FindLast_PopulatedRow_InColumn(currentCell);
Dictionary <string, Range> foundITRs = new Dictionary <string, Range>();
Dictionary <string, string> duplicateITRs = new Dictionary <string, string>();
Worksheet activeSheet = Globals.ThisAddIn.Application.ActiveSheet;
List <string> rowsToDelete = new List <string>();
Range rng = default(Range);
Debug.Print(string.Format("{0} - {1}", firstRow, lastRow));
for (int i = lastRow; i >= firstRow; i += -1)
{
rng = activeSheet.Cells[i, currentCell.Column];
//Debug.Print(string.Format("{0} - {1} - {2}", i, rng.Value, rng.Cells[1, 7].Value));
if (foundITRs.ContainsKey(rng.Value))
{
if (duplicateITRs.ContainsKey(rng.Value))
{
// This is not the first duplicate. Add resource if not there already.
if (!duplicateITRs[rng.Value].Contains(rng.Cells[1, 7].Value))
{
duplicateITRs[rng.Value] = duplicateITRs[rng.Value] + ", " + rng.Cells[1, 7].Value;
}
}
else
{
// Add the Resource from the duplicate row
duplicateITRs[rng.Value] = rng.Cells[1, 7].Value;
}
rowsToDelete.Add(string.Format("{0}:{1}", i, i));
}
else
{
foundITRs[rng.Value] = rng;
}
}
//foreach (KeyValuePair<string, Range> row in foundITRs)
//{
// //Debug.Print(string.Format("{0} - {1} - {2}", row.Value.Row, row.Value.Cells[1, 1].Value, row.Value.Cells[1, 7].Value));
//}
foreach (KeyValuePair <string, string> row in duplicateITRs)
{
//Debug.Print(string.Format("{0} - {1}", row.Key, row.Value));
string resources = activeSheet.Cells[foundITRs[row.Key].Row, 7].Value;
if (row.Value.Contains(resources))
{
activeSheet.Cells[foundITRs[row.Key].Row, 7].Value = row.Value;
}
else
{
activeSheet.Cells[foundITRs[row.Key].Row, 7].Value = resources + ", " + row.Value;
}
}
foreach (string row in rowsToDelete)
{
//Debug.Print(row);
activeSheet.Rows[row].Delete();
}
}
private void LoadInfoFromWorksheet_Claims()
{
CountWS_ClaimRecord = ExcelHlp.FindLast_PopulatedRow_InColumn(_wsClaims.Range["A1"]) - 1;
}
private void LoadInfoFromWorksheet_Payments()
{
CountWS_ClaimPayment = ExcelHlp.FindLast_PopulatedRow_InColumn(_wsPayments.Range["A1"]) - 1;
}
private void LoadInfoFromWorksheet_XOBInfo()
{
CountWS_XOBUDSCode = ExcelHlp.FindLast_PopulatedRow_InColumn(_wsXOBInfo.Range["A1"]) - 1;
}
