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