/// <summary> /// A helper function used to retrieve individual login information from /// a workspace-specific spreadsheet. /// </summary> /// <param name="worksheetName"></param> /// <returns></returns> private List <Common.WorkspaceInfo.LoginInfo> GetLoginInfoFromSpreadsheet(string worksheetName) { // Error checking: make sure that the required worksheet can be found if (!this.Open()) { throw new Exception("Failed to open Excel workbook"); } Worksheet worksheet = this.GetWorksheetByName(worksheetName); if (worksheet == null) { throw new Exception("Failed to open worksheet: '" + worksheetName + "'"); } // Create the output element List <Common.WorkspaceInfo.LoginInfo> loginList = new List <Common.WorkspaceInfo.LoginInfo>(); // Do some math and set up the "column header" range (used to determine which field is which) int numColumns = worksheet.UsedRange.Columns.Count; int numRows = worksheet.UsedRange.Rows.Count; int dataStartRowNum = 2; int dataEndRowNum = dataStartRowNum + numRows; string headerStartCell = "A1"; string headerEndCell = GetExcelColumnFromIndex(numColumns) + "1"; Range headerRange = worksheet.get_Range(headerStartCell, headerEndCell); // Iterate through all of the data rows (i.e., workspaces) in the worksheet for (int rowNum = dataStartRowNum; rowNum < dataEndRowNum; rowNum++) { // Create a range for this row of cells string dataStartCell = "A" + rowNum.ToString(); string dataEndCell = GetExcelColumnFromIndex(numColumns) + rowNum.ToString(); Range dataRange = worksheet.get_Range(dataStartCell, dataEndCell); // Initialize the various values of the workspace configuration object string name = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_WMX_USERNAME).Column); if (string.IsNullOrEmpty(name)) { // Stop reading through the worksheet once we've found a row without any workspace listed break; } string username = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_DB_USERNAME).Column); string password = GetValueAsText(dataRange, FindCellMatchingText(headerRange, C_DB_PASSWORD).Column); bool isEncrypted = GetValueAsBool(dataRange, FindCellMatchingText(headerRange, C_IS_ENCRYPTED).Column); // Only base64-encoded encrypted passwords are supported by this // utility, so unencode the password before sending it along to // the WMX object if (isEncrypted) { byte[] pwBytes = System.Convert.FromBase64String(password); password = System.Text.UTF8Encoding.UTF8.GetString(pwBytes); } Common.WorkspaceInfo.LoginInfo loginInfo = new Common.WorkspaceInfo.LoginInfo(); loginInfo.WmxUsername = name; loginInfo.DatabaseUsername = username; loginInfo.DatabasePassword = password; loginInfo.IsPasswordEncrypted = isEncrypted; loginList.Add(loginInfo); } return(loginList); }
/// <summary> /// Saves all of the specified workspace information to an Excel spreadsheet /// </summary> /// <param name="workspaceInfo">A list containing the information for each workspace</param> public void SaveWorkspacesToSpreadsheet(IList <Common.WorkspaceInfo> workspaceInfo) { // Error checking: delete the required worksheet if it already exists if (!this.Open()) { WmauError error = new WmauError(WmauErrorCodes.C_EXCEL_WORKBOOK_ERROR); m_gpMessages.AddError(error.ErrorCodeAsInt, error.Message); throw new Exception(error.Message); } Worksheet worksheet = this.GetWorksheetByName(C_DB_WORKSHEET_NAME); if (worksheet != null) { worksheet.Delete(); } // Create a new worksheet with the appropriate header row worksheet = m_workbook.Worksheets.Add( Type.Missing, Type.Missing, 1, XlWBATemplate.xlWBATWorksheet) as Worksheet; worksheet.Name = C_DB_WORKSHEET_NAME; Range headerRange = CreateHeaderRow(worksheet, C_HEADER_COLUMNS); // Get a mapping of which attribute name maps to which excel column Dictionary <string, int> columnMap = new Dictionary <string, int>(); for (int i = 0; i < C_HEADER_COLUMNS.Length; i++) { Range tempRange = FindCellMatchingText(headerRange, C_HEADER_COLUMNS[i]); columnMap[C_HEADER_COLUMNS[i]] = tempRange.Column; } // Fill in the contents of the worksheet based on the info for each of the // workspaces int currentRow = 2; foreach (Common.WorkspaceInfo workspace in workspaceInfo) { // Get a handle to the correct range string startCell = "A" + currentRow.ToString(); string endCell = GetExcelColumnFromIndex(C_HEADER_COLUMNS.Length) + currentRow.ToString(); Range tempRange = worksheet.get_Range(startCell, endCell); // Set the values of the cells within the range SetValueAsText(tempRange, columnMap[C_DB_ALIAS], workspace.Name); SetValueAsText(tempRange, columnMap[C_SERVER], workspace.Server); SetValueAsText(tempRange, columnMap[C_INSTANCE], workspace.Instance); SetValueAsText(tempRange, columnMap[C_DATABASE], workspace.Database); SetValueAsText(tempRange, columnMap[C_VERSION], workspace.Version); SetValueAsText(tempRange, columnMap[C_OS_AUTH], workspace.UseOsAuthentication.ToString()); SetValueAsText(tempRange, columnMap[C_INDIVIDUAL_LOGINS], workspace.UseIndividualLogins.ToString()); if (!workspace.UseOsAuthentication && !workspace.UseIndividualLogins) { if (workspace.Logins.Count > 0) { Common.WorkspaceInfo.LoginInfo tempLogin = workspace.Logins.ElementAt(0); SetValueAsText(tempRange, columnMap[C_USERNAME], tempLogin.DatabaseUsername); // Save out the password in base64 format so as to not end up putting binary // info into the Excel spreadsheet byte[] passwordAsBytes = System.Text.UTF8Encoding.UTF8.GetBytes(tempLogin.DatabasePassword); string password64 = System.Convert.ToBase64String(passwordAsBytes); SetValueAsText(tempRange, columnMap[C_PASSWORD], password64); SetValueAsText(tempRange, columnMap[C_IS_ENCRYPTED], true.ToString()); } } else if (workspace.UseIndividualLogins) { SaveLoginInfoToSpreadsheet(workspace.Name, workspace.Logins); } // This "auto fit" shouldn't really be done in here, but the range already // exists, so... tempRange.EntireColumn.AutoFit(); currentRow++; } Save(); }