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