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();
}
// Generates content of connectionsPart1.
private void GenerateConnectionsPart1Content(ConnectionsPart connectionsPart1)
{
Connections connections1 = new Connections();
Connection connection1 = new Connection(){ Id = (UInt32Value)1U, ConnectionFile = "C:\\Users\\pberruti\\Documents\\My Data Sources\\xlextdat105 Timestamps\\DAT105 Timestamp - Foodmart 2000 account.odc", Name = "DAT105 Timestamp - Foodmart 2000 account", Type = (UInt32Value)100U, RefreshedVersion = 5, MinRefreshableVersion = 5, Background = true };
ConnectionExtensionList connectionExtensionList1 = new ConnectionExtensionList();
ConnectionExtension connectionExtension1 = new ConnectionExtension(){ Uri = "{DE250136-89BD-433C-8126-D09CA5730AF9}" };
connectionExtension1.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.Connection connection2 = new X15.Connection(){ Id = "d500245d-c151-4de3-9436-83c6e82b7f22", AutoDelete = true };
X15.OleDbPrpoperties oleDbPrpoperties1 = new X15.OleDbPrpoperties(){ Connection = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=xlextdat105;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PB07641564SHIP;Use Encryption for Data=False;Tag with column collation when possible=False;Initial Catalog=Foodmart 2000" };
X15.DbCommand dbCommand1 = new X15.DbCommand(){ Text = "SELECT *, suser_name() CurrentUser, current_timestamp RefreshTime FROM \"Foodmart 2000\".\"dbo\".\"account\"" };
oleDbPrpoperties1.Append(dbCommand1);
connection2.Append(oleDbPrpoperties1);
connectionExtension1.Append(connection2);
connectionExtensionList1.Append(connectionExtension1);
connection1.Append(connectionExtensionList1);
Connection connection3 = new Connection(){ Id = (UInt32Value)2U, KeepAlive = true, Name = "ModelConnection_Query", Description = "Model", Type = (UInt32Value)5U, RefreshedVersion = 5, MinRefreshableVersion = 3, SaveData = true };
DatabaseProperties databaseProperties1 = new DatabaseProperties(){ Connection = "Data Model Connection", Command = "Query", CommandType = (UInt32Value)3U };
ConnectionExtensionList connectionExtensionList2 = new ConnectionExtensionList();
ConnectionExtension connectionExtension2 = new ConnectionExtension(){ Uri = "{DE250136-89BD-433C-8126-D09CA5730AF9}" };
connectionExtension2.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.Connection connection4 = new X15.Connection(){ Id = "", Model = true };
connectionExtension2.Append(connection4);
connectionExtensionList2.Append(connectionExtension2);
connection3.Append(databaseProperties1);
connection3.Append(connectionExtensionList2);
Connection connection5 = new Connection(){ Id = (UInt32Value)3U, KeepAlive = true, Name = "ThisWorkbookDataModel", Description = "Model", Type = (UInt32Value)5U, RefreshedVersion = 5, MinRefreshableVersion = 5, Background = true };
DatabaseProperties databaseProperties2 = new DatabaseProperties(){ Connection = "Data Model Connection", Command = "Model", CommandType = (UInt32Value)1U };
OlapProperties olapProperties1 = new OlapProperties(){ SendLocale = true, RowDrillCount = (UInt32Value)1000U };
ConnectionExtensionList connectionExtensionList3 = new ConnectionExtensionList();
ConnectionExtension connectionExtension3 = new ConnectionExtension(){ Uri = "{DE250136-89BD-433C-8126-D09CA5730AF9}" };
connectionExtension3.AddNamespaceDeclaration("x15", "http://schemas.microsoft.com/office/spreadsheetml/2010/11/main");
X15.Connection connection6 = new X15.Connection(){ Id = "", Model = true };
connectionExtension3.Append(connection6);
connectionExtensionList3.Append(connectionExtension3);
connection5.Append(databaseProperties2);
connection5.Append(olapProperties1);
connection5.Append(connectionExtensionList3);
connections1.Append(connection1);
connections1.Append(connection3);
connections1.Append(connection5);
connectionsPart1.Connections = connections1;
}
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();
}
