public void VerifyElement(string filePath, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, false))
{
ConnectionsPart connectionsPart = package.WorkbookPart.ConnectionsPart;
X15.Connection connection = connectionsPart.Connections.Descendants <X15.Connection>().First();
X15.OleDbPrpoperties oleDbPrpoperties = connection.OleDbPrpoperties;
log.Verify(oleDbPrpoperties.Connection == this.ConnectionString, "OleDbPrpoperties Connection value is not change.");
X15.DbCommand dbCommand = oleDbPrpoperties.DbCommand;
log.Verify(this.DBCommandText == dbCommand.Text, "DBCommandText value is not change.");
}
}
public void EditElement(string filePath, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true))
{
ConnectionsPart connectionsPart = package.WorkbookPart.ConnectionsPart;
X15.Connection connection = connectionsPart.Connections.Descendants <X15.Connection>().First();
X15.OleDbPrpoperties oleDbPrpoperties = connection.OleDbPrpoperties;
oleDbPrpoperties.Connection = this.ConnectionString;
log.Pass("Edited the OleDbPrpoperties connection");
X15.DbCommand dbCommand = oleDbPrpoperties.DbCommand;
dbCommand.Text = this.DBCommandText;
log.Pass("Edited the DbCommand.");
}
}
public void AddElement(string filePath, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true))
{
ConnectionsPart connectionsPart = package.WorkbookPart.ConnectionsPart;
int connectionNum = connectionsPart.Connections.Descendants <X15.Connection>().Count();
X15.Connection x15connection = new X15.Connection()
{
Id = this.X15ConnectionId, AutoDelete = this.X15ConnectionAutoDelete
};
x15connection.OleDbPrpoperties = new X15.OleDbPrpoperties()
{
Connection = this.ConnectionString
};
x15connection.OleDbPrpoperties.DbCommand = new X15.DbCommand()
{
Text = this.DBCommandText
};
ConnectionExtension connectionExtension = new ConnectionExtension()
{
Uri = this.ConnectionExtUri
};
Connection connection = new Connection()
{
Id = this.ConnectionId, ConnectionFile = this.ConnectionOdcFile, Name = this.ConnectionName, Type = this.ConnectionType, RefreshedVersion = this.ConnectionRefreshedVersion, MinRefreshableVersion = this.ConnectionMinRefreshedVersion, Background = this.ConnectionBackground
};
ConnectionExtensionList connectionExtensionList = new ConnectionExtensionList();
connectionExtension.AppendChild <X15.Connection>(x15connection);
log.Pass("Added the X15.Connection.");
connectionExtensionList.AppendChild <ConnectionExtension>(connectionExtension);
log.Pass("Added the ConnectionExtension.");
connection.AppendChild <ConnectionExtensionList>(connectionExtensionList);
log.Pass("Added the ConnectionExtensionList.");
package.WorkbookPart.ConnectionsPart.Connections.Append(connection);
log.Pass("Added the Connection.");
}
}
public void DeleteElement(string filePath, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true))
{
ConnectionsPart connectionsPart = package.WorkbookPart.ConnectionsPart;
X15.Connection connection = connectionsPart.Connections.Descendants <X15.Connection>().First();
X15.OleDbPrpoperties oleDbPrpoperties = connection.OleDbPrpoperties;
oleDbPrpoperties.DbCommand.Remove();
log.Pass("Deleted The DbCommand.");
oleDbPrpoperties.Remove();
log.Pass("Deleted The OleDbPrpoperties.");
connection.Parent.Parent.Parent.Remove();
log.Pass("Deleted The Connection.");
}
}
public void VerifyConnection(string filePath, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, false))
{
ConnectionsPart connectionsPart = package.WorkbookPart.ConnectionsPart;
X15.Connection connection = connectionsPart.Connections.Descendants <X15.Connection>().Where(e => e.Descendants <X15.OleDbPrpoperties>().Count() > 0).First();
log.Verify(connection != null, "Unable to obtain the X15.Connection");
X15.OleDbPrpoperties oleDbPrpoperties = connection.OleDbPrpoperties;
log.Verify(oleDbPrpoperties != null, "Unable to obtain the X15.OleDbPrpoperties");
X15.DbTables dbTables = oleDbPrpoperties.DbTables;
string connectionString = oleDbPrpoperties.Connection;
log.Verify(connectionString != null, "Unable to obtain the X15.OleDbPrpoperties on Connection");
X15.DbCommand dbCommand = oleDbPrpoperties.DbCommand;
log.Verify(dbCommand != null, "Unable to obtain the X15.DbCommand");
}
}
public void VerifyAddedElement(string filePath, VerifiableLog log)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, true))
{
ConnectionsPart connectionsPart = package.WorkbookPart.ConnectionsPart;
X15.Connection connection = null;
foreach (X15.Connection x15connection in connectionsPart.Connections.Descendants <X15.Connection>())
{
if (x15connection.OleDbPrpoperties != null)
{
connection = x15connection;
}
}
log.Verify(connection.OleDbPrpoperties != null, "Missing X15.OleDbPrpoperties element.");
log.Verify(connection.OleDbPrpoperties.Connection == this.ConnectionString, "OleDbPrpoperties Connection value is not change.");
log.Verify(connection.OleDbPrpoperties != null, "Missing X15.DbCommand element.");
log.Verify(connection.OleDbPrpoperties.DbCommand.Text == this.DBCommandText, "OleDbPrpoperties Connection value is not change.");
}
}
private void DetermineConnections(WorkbookPart workbookPart)
{
if (workbookPart.ConnectionsPart != null)
{
ConnectionsPart connectionPart = workbookPart.ConnectionsPart;
foreach (Connection connection in connectionPart.Connections)
{
var connectionInfo = new Connections
{
Description = connection.Description,
Name = connection.Name,
ConnectionProperties = new ConnectionProperties()
};
DatabaseProperties databaseProperties = connection.DatabaseProperties;
connectionInfo.ConnectionProperties.Command = databaseProperties.Command.InnerText;
connectionInfo.ConnectionProperties.ConnectionDetails = databaseProperties.Connection.InnerText;
this.connections.Add(connectionInfo);
}
this.workbook.HasDataConnections = true;
}
}
public ConnectionTestEntities(string filePath)
{
using (SpreadsheetDocument package = SpreadsheetDocument.Open(filePath, false))
{
ConnectionsPart connectionsPart = package.WorkbookPart.ConnectionsPart;
foreach (Connection connection in connectionsPart.Connections)
{
if (connection.Id == 1)
{
this.ConnectionId = connection.Id;
this.ConnectionOdcFile = connection.ConnectionFile;
this.ConnectionName = connection.Name;
this.ConnectionType = connection.Type;
this.ConnectionRefreshedVersion = connection.RefreshedVersion;
this.ConnectionMinRefreshedVersion = connection.MinRefreshableVersion;
this.ConnectionBackground = connection.Background;
}
}
X15.Connection x15connection = connectionsPart.Connections.Descendants <X15.Connection>().FirstOrDefault();
if (x15connection == null)
{
throw new Exception("Unable to obtain the X15.Connection.");
}
this.X15ConnectionId = x15connection.Id;
this.X15ConnectionAutoDelete = x15connection.AutoDelete;
ConnectionExtension connectionExtension = new ConnectionExtension(x15connection.Parent.OuterXml);
this.ConnectionExtUri = connectionExtension.Uri;
X15.OleDbPrpoperties oleDbPrpoperties = x15connection.OleDbPrpoperties;
this.ConnectionString = oleDbPrpoperties.Connection;
X15.DbCommand dbCommand = oleDbPrpoperties.DbCommand;
this.DBCommandText = dbCommand.Text;
}
}
protected void Page_Load(object sender, EventArgs e)
{
//load url arguments in variables
string INSTANCE_NAME = @Request.QueryString["instance"];
string CATALOG_NAME = Request.QueryString["catalog"];
string CUBE_NAME = Request.QueryString["cube"];
//Create Excel file name
string ConnectionName = INSTANCE_NAME.Replace("\\", "_") + "_" + CATALOG_NAME + "_" + CUBE_NAME;
Response.Write(ConnectionName);
//Create Workbook
string filename = Server.MapPath(@"tmp/" + ConnectionName + ".xlsx");
// Create a spreadsheet document by supplying the filepath.
// By default, AutoSave = true, Editable = true, and Type = xlsx.
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
// Add a WorkbookPart to the document.
WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
workbookpart.Workbook = new Workbook();
// Add a WorksheetPart to the WorkbookPart.
WorksheetPart worksheetPart = workbookpart.AddNewPart <WorksheetPart>();
worksheetPart.Worksheet = new Worksheet(new SheetData());
// Add Sheets to the Workbook.
Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild <Sheets>(new Sheets());
// Append a new worksheet and associate it with the workbook.
Sheet sheet = new Sheet()
{
Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "mySheet"
};
sheets.Append(sheet);
//Add a connectionPart to the workbookpart
ConnectionsPart connectionsPart1 = workbookpart.AddNewPart <ConnectionsPart>();
Connections connections1 = new Connections();
Connection connection1 = new Connection()
{
Id = (UInt32Value)1U, KeepAlive = true, Name = ConnectionName, Type = (UInt32Value)5U, RefreshedVersion = 5, Background = true
};
DatabaseProperties databaseProperties1 = new DatabaseProperties()
{
Connection = "Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=" + CATALOG_NAME + ";Data Source=" + @INSTANCE_NAME + ";MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error", Command = CUBE_NAME, CommandType = (UInt32Value)1U
};
OlapProperties olapProperties1 = new OlapProperties()
{
SendLocale = true, RowDrillCount = (UInt32Value)1000U
};
connection1.Append(databaseProperties1);
connection1.Append(olapProperties1);
connections1.Append(connection1);
connectionsPart1.Connections = connections1;
//Add a PivottableCache part
PivotTableCacheDefinitionPart pivotTableCacheDefinitionPart1 = workbookpart.AddNewPart <PivotTableCacheDefinitionPart>();
PivotCacheDefinition pivotCacheDefinition1 = new PivotCacheDefinition()
{
SaveData = false, BackgroundQuery = true, SupportSubquery = true, SupportAdvancedDrill = true
};
pivotTableCacheDefinitionPart1.PivotCacheDefinition = pivotCacheDefinition1;
workbookpart.Workbook.Save();
// Close the document.
spreadsheetDocument.Close();
Response.Clear();
Response.AddHeader("content-disposition", "attachment; filename=" + @"D:\MyBI\SSAS\SSAS2012_MyBI\tmp\test.xlsx");
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.WriteFile(Server.MapPath(@"tmp/" + ConnectionName + ".xlsx"));
Response.End();
}
