//Workbooks.OpenText Filename:= _ //"C:\Customers\Aston Martin\AML_Logs\EaseWorks_0228.log", Origin:=437, _ //startRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ //ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False _ //, Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array(Array(1, 1 _ //), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _ //Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1)), _ //TrailingMinusNumbers:=True // With ActiveSheet.QueryTables.Add(Connection:= _ // "TEXT;C:\Customers\Aston Martin\AML_Logs\AMLViewEase.log", Destination:=Range _ // ("$B$5")) // .CommandType = 0 // .Name = "AMLViewEase" // .FieldNames = True // .RowNumbers = False // .FillAdjacentFormulas = False // .PreserveFormatting = True // .RefreshOnFileOpen = False // .RefreshStyle = xlInsertDeleteCells // .SavePassword = False // .SaveData = True // .AdjustColumnWidth = True // .RefreshPeriod = 0 // .TextFilePromptOnRefresh = False // .TextFilePlatform = 437 // .TextFileStartRow = 1 // .TextFileParseType = xlDelimited // .TextFileTextQualifier = xlTextQualifierDoubleQuote // .TextFileConsecutiveDelimiter = False // .TextFileTabDelimiter = False // .TextFileSemicolonDelimiter = False // .TextFileCommaDelimiter = False // .TextFileSpaceDelimiter = False // .TextFileOtherDelimiter = "|" // .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1) // .TextFileTrailingMinusNumbers = True // .Refresh BackgroundQuery:=False //End With private void CreateLogWorkSheet(string fileName) { XlHlp.DisplayInWatchWindow(string.Format("{0}()", MethodBase.GetCurrentMethod().Name)); //string sheetName = XlHlp.SafeSheetName("Log-RenameMe"); //Worksheet ws = XlHlp.NewWorksheet(sheetName, beforeSheetName: "FIRST"); //XlHlp.XlLocation insertAt = new XlHlp.XlLocation(ws, row: 5, column: 2); Globals.ThisAddIn.Application.Workbooks.OpenText(Filename: fileName, StartRow: 1, DataType: XlTextParsingType.xlDelimited, Tab: false, Semicolon: false, Comma: false, Space: false, Other: true, OtherChar: "|"); Worksheet ws = Globals.ThisAddIn.Application.ActiveSheet; // Rows("1:1").Select // Selection.Insert Shift:= xlDown, CopyOrigin:= xlFormatFromLeftOrAbove // Columns("A:A").Select // Selection.Insert Shift:= xlToRight, CopyOrigin:= xlFormatFromLeftOrAbove Range rng = ws.Rows["1:1"]; rng.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove); rng.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove); rng.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove); rng.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove); rng.Insert(Shift: XlInsertShiftDirection.xlShiftDown, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove); rng = ws.Columns["A:A"]; rng.Insert(Shift: XlInsertShiftDirection.xlShiftToRight, CopyOrigin: XlInsertFormatOrigin.xlFormatFromLeftOrAbove); XlHlp.XlLocation insertAt = new XlHlp.XlLocation(ws, row: 5, column: 1); insertAt.MarkStart(XlHlp.MarkType.GroupTable); CreateLogWorkSheetHeader(insertAt); // Do some formatting. May want to pull this out into a separate method, FormatSheetColumns() if do more rng = ws.Columns["B:B"]; rng.NumberFormat = "m/d/yyyy h:mm:ss.000"; //insertAt.ClearOffsets(); rng = ws.Cells[5, 1]; Int32 lastRow = rng.SpecialCells(XlCellType.xlCellTypeLastCell).Row; Int32 lastColumn = rng.SpecialCells(XlCellType.xlCellTypeLastCell).Column; XlHlp.DisplayInWatchWindow(string.Format("{0}() row:({1}) col:({2})", MethodBase.GetCurrentMethod().Name, lastRow, lastColumn)); // TODO(crhodes) // This would be a good thing to add to ExcelUtilities // Add a row number to table so can resort if necessary. Table starts at 5, first data row at 6 for (int i = 6, rowNumber = 1; i <= lastRow; i++, rowNumber++) { ws.Cells[i, 1].Value = rowNumber; } insertAt.SetRow(lastRow + 1); insertAt.MarkEnd(VNC.AddinHelper.Excel.MarkType.GroupTable, string.Format("tbl_{0}", ws.Name)); rng = ws.Columns["E:K"]; rng.Columns.Group(); ws.Outline.SummaryColumn = XlSummaryColumn.xlSummaryOnLeft; ws.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove; ws.Cells[6, 14].Select(); ws.Application.ActiveWindow.FreezePanes = true; SaveLogFile(ws.Application.ActiveWorkbook, fileName); //Selection.Columns.Group //Selection.Columns.Group //Range("D3").Select //ActiveSheet.Outline.ShowLevels RowLevels:= 0, ColumnLevels:= 3 //ActiveSheet.Outline.ShowLevels RowLevels:= 0, ColumnLevels:= 2 //ActiveSheet.Outline.ShowLevels RowLevels:= 0, ColumnLevels:= 1 //With ActiveSheet.Outline // .AutomaticStyles = False // .SummaryRow = xlAbove // .SummaryColumn = xlLeft //End With }
private static string CopyRowsToNewWorkSheet(Worksheet sourceWs, int startRow, int endRow, string sheetName) { sheetName = XlHlp.SafeSheetName(sheetName); Worksheet destinationWS = XlHlp.NewWorksheet(sheetName, afterSheetName: "LAST"); XlHlp.XlLocation insertAt = new XlHlp.XlLocation(destinationWS, row: 5, column: 1); insertAt.MarkStart(XlHlp.MarkType.GroupTable); CreateLogWorkSheetHeader(insertAt); insertAt.AddRowX(); // HACK(crhodes) // // There are 14 columns normally, grab five more just in case // We can do better than this by finding the last active column. // Extra columns can appear when an exception messes up the formatting of the log file. Range sourceRng; sourceRng = sourceWs.Range[sourceWs.Cells[startRow, 1], sourceWs.Cells[endRow, 19]]; //.Cells[startRow, 1]; sourceRng.Copy(); insertAt.GetCurrentRange().PasteSpecial(); destinationWS.Activate(); // Things come over messed up. Unfreeze //Globals.ThisAddIn.Application.ActiveWindow.FreezePanes = false; Range rng; rng = destinationWS.Cells[5, 1]; Int32 lastRow = rng.SpecialCells(XlCellType.xlCellTypeLastCell).Row; Int32 lastColumn = rng.SpecialCells(XlCellType.xlCellTypeLastCell).Column; XlHlp.DisplayInWatchWindow(string.Format("{0}() row:({1}) col:({2})", MethodBase.GetCurrentMethod().Name, lastRow, lastColumn)); insertAt.SetRow(lastRow + 1); insertAt.SetColumn(lastColumn); insertAt.MarkEnd(VNC.AddinHelper.Excel.MarkType.GroupTable, string.Format("tbl_{0}", destinationWS.Name)); //rng = destinationWS.Columns["E:K"]; //rng.Columns.Group(); destinationWS.Columns["E:K"].Columns.Group(); //destinationWS.Cells[6, 14].Select(); //destinationWS.Application.ActiveWindow.FreezePanes = true; // TODO(crhodes) // Figure out what to do to make outlining work. //sourceWs.Outline.SummaryColumn = XlSummaryColumn.xlSummaryOnLeft; //sourceWs.Outline.SummaryRow = XlSummaryRow.xlSummaryAbove; return(sheetName); }