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