Creating Excel and PowerPoint Documents Using PerformancePoint

Eric Friedrichsen  (ericfr@microsoft.com)

Microsoft Corporation

August 2007

Applies to:
Microsoft Office PerformancePoint Server 2007
Microsoft Office Excel 2007
Microsoft Office PowerPoint 2007


Summary: Microsoft Office PerformancePoint Server 2007 has the ability to generate Excel and PowerPoint documents without the presence of Microsoft Office. 

Introduction

Using Microsoft Office PerformancePoint Server 2007 APIs to generate Excel and PowerPoint documents is interesting if:

·         You need to generate Excel and PowerPoint documents without the presence of Microsoft Office.  This would be important if you're building a server that needs to generate Excel and PowerPoint documents, and you want to avoid using Office Automation on the server.   Note: Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when run in this environment.  See details at http://support.microsoft.com/kb/257757.

·         You need to generate Excel and PowerPoint documents with a simplified API that does not require a comprehensive understanding of the Office (2007) Open XML File Formats.  See details at http://msdn2.microsoft.com/en-us/library/aa338205.aspx for more information on the Open XML File Formats.  Though the Office (2007) Open XML File Formats are not difficult to learn, certain limited scenarios may be reached more quickly by using the APIs found in Microsoft Office PerformancePoint Server 2007.

Technologies

Microsoft Office PerformancePoint Server 2007 APIs generate Excel and PowerPoint documents by natively generating xml, and using .NET Framework 3.0 to insert that xml into XPS based packages that then can be opened by Excel 2007 and PowerPoint 2007 clients as xlsx / pptx files.  See details on XPS at http://msdn2.microsoft.com/en-us/windowsvista/aa904982.aspx.

References

In order to use the Microsoft Office PerformancePoint Server 2007 APIs to generate Excel and PowerPoint documents, you need to reference the following PPS assemblies:

Microsoft.PerformancePoint.Scorecards.Client

Microsoft.PerformancePoint.Scorecards.Common

 

Creating a Simple Excel/PowerPoint Document

The following code creates a very simple Excel spreadsheet, and a similar looking PowerPoint presentation.  The complete code for this is at the end of this article.

 

using System;

using System.Collections.Generic;

using System.Drawing;

using System.IO;

using System.Text;

 

using Microsoft.PerformancePoint.Scorecards;

using Microsoft.PerformancePoint.Scorecards.Common.Export;

 

namespace CreateExcelAndPowerPoint

{

    public class Program

    {

        static void Main(string[] args)

        {

            CreateDocument1();

        }

 

        private static void CreateDocument1()

        {

            CellRange[] ranges = new CellRange[2];

 

            ranges[0] = new CellRange();

            ranges[0].Coordinates = "3,2,3,2";

            ranges[0].Text = "ABC Company";

 

            ranges[1] = new CellRange();

            ranges[1].Coordinates = "4,2,4,2";

            ranges[1].Text = "International Sales";

 

            List<GridDisplayElementRendering> elements = new List<GridDisplayElementRendering>();

 

            TextRendering text1 = new TextRendering();

            text1.Range = new CellRange();

            text1.Range.Coordinates = "5,2,5,2";

            elements.Add(text1);

 

            TextRendering text3 = new TextRendering();

            text3.Range = new CellRange();

            text3.Range.Coordinates = "6,2,6,2";

            text3.Range.Text = "All Customers";

            elements.Add(text3);

 

            TextRendering column3Text = new TextRendering();

            column3Text.Range = new CellRange();

            column3Text.Range.Coordinates = "5,3,5,3";

            column3Text.Range.Text = "2007";

            elements.Add(column3Text);

 

            NumericRendering column3Number = new NumericRendering();

            column3Number.Range = new CellRange();

            column3Number.Range.Coordinates = "6,3,6,3";

            column3Number.Range.Text = "$40,000,000.00";

            column3Number.NumericValue = 40000000M;

            elements.Add(column3Number);

 

            TextRendering column4Text = new TextRendering();

            column4Text.Range = new CellRange();

            column4Text.Range.Coordinates = "5,4,5,4";

            column4Text.Range.Text = "2008";

            elements.Add(column4Text);

 

            NumericRendering column4Number = new NumericRendering();

            column4Number.Range = new CellRange();

            column4Number.Range.Coordinates = "6,4,6,4";

            column4Number.Range.Text = "$40,450,597.00";

            column4Number.NumericValue = 40450596.9823M;

            elements.Add(column4Number);

 

            TextRendering column5Text = new TextRendering();

            column5Text.Range = new CellRange();

            column5Text.Range.Coordinates = "5,5,5,5";

            column5Text.Range.Text = "All Periods";

            elements.Add(column5Text);

 

            NumericRendering column5Number = new NumericRendering();

            column5Number.Range = new CellRange();

            column5Number.Range.Coordinates = "6,5,6,5";

            column5Number.Range.Text = "$80,450,597.00";

            column5Number.NumericValue = 80450596.9823M;

            elements.Add(column5Number);

 

            List<GridCellRendering> cells = new List<GridCellRendering>();

            foreach (GridDisplayElementRendering element in elements)

            {

                GridCellRendering cell = new GridCellRendering();

                cell.Elements = new GridDisplayElementRendering[] { element };

                cells.Add(cell);

            }

 

            PrimitiveGrid grid = new PrimitiveGrid();

            grid.Cells = cells.ToArray();

            grid.GridHeaders = ranges;

            grid.SheetName = "Document1";

            grid.TitleOfParts = "Document1";

 

            byte[] xlsx = NativeExport.ExportToOfficeDocument(ExportFormat.Excel, grid);

            OutputBytesToTempDir("Document1.xlsx", xlsx);

 

            byte[] pptx = NativeExport.ExportToOfficeDocument(ExportFormat.PowerPoint, grid);

            OutputBytesToTempDir("Document1.pptx", pptx);

        }

 

        private static void OutputBytesToTempDir(string outputFileName, byte[] bytes)

        {

            string outputFileFullPath = Path.Combine(Path.GetTempPath(), outputFileName);

            FileStream outputFileStream = new FileStream(outputFileFullPath, FileMode.Create);

            outputFileStream.Write(bytes, 0, bytes.GetLength(0));

            outputFileStream.Flush();

            outputFileStream.Close();

        }

    }

}

 

 

The above code creates an Excel spreadsheet and similar PowerPoint presentation that is very simple, devoid of formatting.  The currency values were specified as NumericRendering in the code (see above), so they resolve in Excel as numbers with numeric formatting to achieve the currency symbol, number group delimiters, and decimal characters.  The end result looks like this:

exppt1

Adding Formatting to Excel/PowerPoint Documents

The following code applies fill color, font color, font sizing, and text alignment.  The complete code for this is at the end of this article.

 

            GridFormatInfo shaded = new GridFormatInfo();

            shaded.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Center;

            shaded.FontInfo = new GridFontInfo();

            shaded.FontInfo.FamilyName = "Tahoma";

            shaded.FontInfo.SizeInPoints = 10.0f;

            shaded.ForeColor = new GridColor(Color.Black);

            shaded.BackColor = new GridColor(Color.FromArgb(0, 235, 243, 255));

 

            TextRendering column3Text = new TextRendering();

            column3Text.Range = new CellRange();

            column3Text.Range.Format = shaded;

            column3Text.Range.Coordinates = "5,3,5,3";

            column3Text.Range.Text = "2007";

            elements.Add(column3Text);

 

 

The following code applies cell merging and borders.  The complete code for this is at the end of this article.

 

            CellRange[] ranges = new CellRange[3];

 

            ranges[0] = new CellRange();

            ranges[0].Format = new GridFormatInfo();

            ranges[0].Format.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Center;

            ranges[0].Format.FontInfo = new GridFontInfo();

            ranges[0].Format.FontInfo.FamilyName = "Arial";

            ranges[0].Format.FontInfo.SizeInPoints = 14.0f;

            ranges[0].Format.FontInfo.Bold = Trinary.True;

            ranges[0].Format.FontInfo.Italic = Trinary.True;

            ranges[0].Format.ForeColor = new GridColor(Color.Blue);

            ranges[0].Format.BackColor = new GridColor(Color.White);

            ranges[0].Coordinates = "3,2,3,5";

            ranges[0].IsMerged = true;

            ranges[0].Text = "ABC Company";

 

            ranges[1] = new CellRange();

            ranges[1].Format = new GridFormatInfo();

            ranges[1].Format.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Center;

            ranges[1].Format.FontInfo = new GridFontInfo();

            ranges[1].Format.FontInfo.FamilyName = "Arial";

            ranges[1].Format.FontInfo.SizeInPoints = 12.0f;

            ranges[1].Format.FontInfo.Bold = Trinary.True;

            ranges[1].Format.FontInfo.Italic = Trinary.True;

            ranges[1].Format.ForeColor = new GridColor(Color.Blue);

            ranges[1].Format.BackColor = new GridColor(Color.White);

            ranges[1].Coordinates = "4,2,4,5";

            ranges[1].IsMerged = true;

            ranges[1].Text = "International Sales";

           

            ranges[2] = new CellRange();

            ranges[2].BorderColor = "FF9999FF";

            ranges[2].BorderLineStyle = "thin"; // Currently, only "thin" is supported

            ranges[2].Coordinates = "3,2,6,5";

 

 

After applying fill color, font color, font sizing, text alignment, cell merging, and borders, the Excel spreadsheet and PowerPoint presentation look like this:

exppt2

Inserting Images into Excel/PowerPoint Documents

 

The following code demonstrates how to insert an image into an Excel spreadsheet or PowerPoint presentation.  The following code reads image bytes in from an external file, but you can provide the bytes for the image any way you see fit.  The complete code for this is at the end of this article.

 

                FileInfo imageFile = new FileInfo(@"c:\chart.bmp");

                FileStream imageFileStream = new FileStream(@"c:\chart.bmp", FileMode.Open);

                byte[] imageBytes = new byte[imageFile.Length];

                imageFileStream.Read(imageBytes, 0, Convert.ToInt32(imageFile.Length));

                imageFileStream.Position = 0;

                Image imageAnalysis = Image.FromStream(imageFileStream);

                imageFileStream.Close();

 

                ImageRendering imageRendering = new ImageRendering();

                imageRendering.Column = 1;

                imageRendering.Row = 3;

                imageRendering.ImageBytes = imageBytes;

                imageRendering.ImageHeight = imageAnalysis.Size.Height;

                imageRendering.ImageWidth = imageAnalysis.Size.Width;

                imageRendering.PaddedRowHeight = imageAnalysis.Size.Height;

                imageRendering.Range = new CellRange();

                imageRendering.Range.Coordinates = "3,1,3,1";

                imageRendering.Range.IsSpillOver = true;

                elements.Add(imageRendering);

 

 

This can be used to generate Excel spreadsheets and PowerPoint presentations that contain a mixture of text, numbers, and images:

exppt3

Conclusion

You can use the above approach to generate Excel spreadsheets and PowerPoint presentations using Microsoft Office PerformancePoint Server 2007 APIs. Certain limited scenarios may be reached more quickly by using these APIs, but for complete access to all Excel and PowerPoint document structure, please see the Open XML File Formats at http://msdn2.microsoft.com/en-us/library/aa338205.aspx. 

Complete Code List

Instructions for use:

1.     Install the .NET 3.0 Framework if it isn't already installed.

2.     Open Visual Studio and create a new Console Project.

3.     Replace the contents of the Program.cs file with the following code listing.  You can copy and paste directly from this page.

4.     Add a references to your project for the following assemblies:

System.Drawing

Microsoft.PerformancePoint.Scorecards.Client

Microsoft.PerformancePoint.Scorecards.Common

 

5.     Execute the code.

6.     It will generate six documents to your Temp directory that demonstrate the feature.

 

 

using System;

using System.Collections.Generic;

using System.Drawing;

using System.IO;

using System.Text;

 

using Microsoft.PerformancePoint.Scorecards;

using Microsoft.PerformancePoint.Scorecards.Common.Export;

 

namespace CreateExcelAndPowerPoint

{

    public class Program

    {

        static void Main(string[] args)

        {

            CreateDocument1();

            CreateDocument2();

            CreateDocument3();

        }

 

        private static void CreateDocument1()

        {

            CellRange[] ranges = new CellRange[2];

 

            ranges[0] = new CellRange();

            ranges[0].Coordinates = "3,2,3,2";

            ranges[0].Text = "ABC Company";

 

            ranges[1] = new CellRange();

            ranges[1].Coordinates = "4,2,4,2";

            ranges[1].Text = "International Sales";

 

            List<GridDisplayElementRendering> elements = new List<GridDisplayElementRendering>();

 

            TextRendering text1 = new TextRendering();

            text1.Range = new CellRange();

            text1.Range.Coordinates = "5,2,5,2";

            elements.Add(text1);

 

            TextRendering text3 = new TextRendering();

            text3.Range = new CellRange();

            text3.Range.Coordinates = "6,2,6,2";

            text3.Range.Text = "All Customers";

            elements.Add(text3);

 

            TextRendering column3Text = new TextRendering();

            column3Text.Range = new CellRange();

            column3Text.Range.Coordinates = "5,3,5,3";

            column3Text.Range.Text = "2007";

            elements.Add(column3Text);

 

            NumericRendering column3Number = new NumericRendering();

            column3Number.Range = new CellRange();

            column3Number.Range.Coordinates = "6,3,6,3";

            column3Number.Range.Text = "$40,000,000.00";

            column3Number.NumericValue = 40000000M;

            elements.Add(column3Number);

 

            TextRendering column4Text = new TextRendering();

            column4Text.Range = new CellRange();

            column4Text.Range.Coordinates = "5,4,5,4";

            column4Text.Range.Text = "2008";

            elements.Add(column4Text);

 

            NumericRendering column4Number = new NumericRendering();

            column4Number.Range = new CellRange();

            column4Number.Range.Coordinates = "6,4,6,4";

            column4Number.Range.Text = "$40,450,597.00";

            column4Number.NumericValue = 40450596.9823M;

            elements.Add(column4Number);

 

            TextRendering column5Text = new TextRendering();

            column5Text.Range = new CellRange();

            column5Text.Range.Coordinates = "5,5,5,5";

            column5Text.Range.Text = "All Periods";

            elements.Add(column5Text);

 

            NumericRendering column5Number = new NumericRendering();

            column5Number.Range = new CellRange();

            column5Number.Range.Coordinates = "6,5,6,5";

            column5Number.Range.Text = "$80,450,597.00";

            column5Number.NumericValue = 80450596.9823M;

            elements.Add(column5Number);

 

            List<GridCellRendering> cells = new List<GridCellRendering>();

            foreach (GridDisplayElementRendering element in elements)

            {

                GridCellRendering cell = new GridCellRendering();

                cell.Elements = new GridDisplayElementRendering[] { element };

                cells.Add(cell);

            }

 

            PrimitiveGrid grid = new PrimitiveGrid();

            grid.Cells = cells.ToArray();

            grid.GridHeaders = ranges;

            grid.SheetName = "Document1";

            grid.TitleOfParts = "Document1";

 

            byte[] xlsx = NativeExport.ExportToOfficeDocument(ExportFormat.Excel, grid);

            OutputBytesToTempDir("Document1.xlsx", xlsx);

 

            byte[] pptx = NativeExport.ExportToOfficeDocument(ExportFormat.PowerPoint, grid);

            OutputBytesToTempDir("Document1.pptx", pptx);

        }

 

        private static void OutputBytesToTempDir(string outputFileName, byte[] bytes)

        {

            string outputFileFullPath = Path.Combine(Path.GetTempPath(), outputFileName);

            FileStream outputFileStream = new FileStream(outputFileFullPath, FileMode.Create);

            outputFileStream.Write(bytes, 0, bytes.GetLength(0));

            outputFileStream.Flush();

            outputFileStream.Close();

        }

 

        private static void CreateDocument2()

        {

            // Ranges

 

            CellRange[] ranges = new CellRange[3];

 

            ranges[0] = new CellRange();

            ranges[0].Format = new GridFormatInfo();

            ranges[0].Format.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Center;

            ranges[0].Format.FontInfo = new GridFontInfo();

            ranges[0].Format.FontInfo.FamilyName = "Arial";

            ranges[0].Format.FontInfo.SizeInPoints = 14.0f;

            ranges[0].Format.FontInfo.Bold = Trinary.True;

            ranges[0].Format.FontInfo.Italic = Trinary.True;

            ranges[0].Format.ForeColor = new GridColor(Color.Blue);

            ranges[0].Format.BackColor = new GridColor(Color.White);

            ranges[0].Coordinates = "3,2,3,5";

            ranges[0].IsMerged = true;

            ranges[0].Text = "ABC Company";

 

            ranges[1] = new CellRange();

            ranges[1].Format = new GridFormatInfo();

            ranges[1].Format.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Center;

            ranges[1].Format.FontInfo = new GridFontInfo();

            ranges[1].Format.FontInfo.FamilyName = "Arial";

            ranges[1].Format.FontInfo.SizeInPoints = 12.0f;

            ranges[1].Format.FontInfo.Bold = Trinary.True;

            ranges[1].Format.FontInfo.Italic = Trinary.True;

            ranges[1].Format.ForeColor = new GridColor(Color.Blue);

            ranges[1].Format.BackColor = new GridColor(Color.White);

            ranges[1].Coordinates = "4,2,4,5";

            ranges[1].IsMerged = true;

            ranges[1].Text = "International Sales";

           

            ranges[2] = new CellRange();

            ranges[2].BorderColor = "FF9999FF";

            ranges[2].BorderLineStyle = "thin"; // Currently, only "thin" is supported

            ranges[2].Coordinates = "3,2,6,5";

 

            // Elements

 

            List<GridDisplayElementRendering> elements = new List<GridDisplayElementRendering>();

 

            TextRendering text1 = new TextRendering();

            text1.Range = new CellRange();

            text1.Range.Format = new GridFormatInfo();

            text1.Range.Format.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Left;

            text1.Range.Format.FontInfo = new GridFontInfo();

            text1.Range.Format.FontInfo.FamilyName = "Tahoma";

            text1.Range.Format.FontInfo.SizeInPoints = 10.0f;

            text1.Range.Format.ForeColor = new GridColor(Color.Black);

            text1.Range.Format.BackColor = new GridColor(Color.FromArgb(0,235,243,255));

            text1.Range.Coordinates = "5,2,5,2";

            elements.Add(text1);

 

            TextRendering text3 = new TextRendering();

            text3.Range = new CellRange();

            text3.Range.Format = new GridFormatInfo();

            text3.Range.Format.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Left;

            text3.Range.Format.FontInfo = new GridFontInfo();

            text3.Range.Format.FontInfo.FamilyName = "Tahoma";

            text3.Range.Format.FontInfo.SizeInPoints = 10.0f;

            text3.Range.Format.ForeColor = new GridColor(Color.Black);

            text3.Range.Coordinates = "6,2,6,2";

            text3.Range.Text = "All Customers";

            elements.Add(text3);

 

            // Formatting

 

            GridFormatInfo shaded = new GridFormatInfo();

            shaded.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Center;

            shaded.FontInfo = new GridFontInfo();

            shaded.FontInfo.FamilyName = "Tahoma";

            shaded.FontInfo.SizeInPoints = 10.0f;

            shaded.ForeColor = new GridColor(Color.Black);

            shaded.BackColor = new GridColor(Color.FromArgb(0, 235, 243, 255));

 

            GridFormatInfo unshaded = new GridFormatInfo();

            unshaded.VerticalAlignment = GridFormatInfo.VerticalAlignments.Middle;

            unshaded.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Center;

            unshaded.FontInfo = new GridFontInfo();

            unshaded.FontInfo.FamilyName = "Tahoma";

            unshaded.FontInfo.SizeInPoints = 10.0f;

            unshaded.ForeColor = new GridColor(Color.Black);

 

            // column3

 

            TextRendering column3Text = new TextRendering();

            column3Text.Range = new CellRange();

            column3Text.Range.Format = shaded;

            column3Text.Range.Coordinates = "5,3,5,3";

            column3Text.Range.Text = "2007";

            elements.Add(column3Text);

 

            NumericRendering column3Number = new NumericRendering();

            column3Number.Range = new CellRange();

            column3Number.Range.Format = unshaded;

            column3Number.Range.Coordinates = "6,3,6,3";

            column3Number.Range.Text = "$40,000,000.00";

            column3Number.NumericValue = 40000000M;

            elements.Add(column3Number);

 

            // column4

 

            TextRendering column4Text = new TextRendering();

            column4Text.Range = new CellRange();

            column4Text.Range.Format = shaded;

            column4Text.Range.Coordinates = "5,4,5,4";

            column4Text.Range.Text = "2008";

            elements.Add(column4Text);

 

            NumericRendering column4Number = new NumericRendering();

            column4Number.Range = new CellRange();

            column4Number.Range.Format = unshaded;

            column4Number.Range.Coordinates = "6,4,6,4";

            column4Number.Range.Text = "$40,450,597.00";

            column4Number.NumericValue = 40450596.9823M;

            elements.Add(column4Number);

 

            // column5

 

            TextRendering column5Text = new TextRendering();

            column5Text.Range = new CellRange();

            column5Text.Range.Format = (GridFormatInfo)shaded.Clone();

            column5Text.Range.Format.FontInfo.Bold = Trinary.True;

            column5Text.Range.Coordinates = "5,5,5,5";

            column5Text.Range.Text = "All Periods";

            elements.Add(column5Text);

 

            NumericRendering column5Number = new NumericRendering();

            column5Number.Range = new CellRange();

            column5Number.Range.Format = (GridFormatInfo)unshaded.Clone();

            column5Number.Range.Format.FontInfo.Bold = Trinary.True;

            column5Number.Range.Coordinates = "6,5,6,5";

            column5Number.Range.Text = "$80,450,597.00";

            column5Number.NumericValue = 80450596.9823M;

            elements.Add(column5Number);

 

            List<GridCellRendering> cells = new List<GridCellRendering>();

            foreach (GridDisplayElementRendering element in elements)

            {

                GridCellRendering cell = new GridCellRendering();

                cell.Elements = new GridDisplayElementRendering[] { element };

                cells.Add(cell);

            }

 

            PrimitiveGrid grid = new PrimitiveGrid();

            grid.Cells = cells.ToArray();

            grid.GridHeaders = ranges;

            grid.SheetName = "Document2";

            grid.TitleOfParts = "Document2";

 

            byte[] xlsx = NativeExport.ExportToOfficeDocument(ExportFormat.Excel, grid);

            OutputBytesToTempDir("Document2.xlsx", xlsx);

 

            byte[] pptx = NativeExport.ExportToOfficeDocument(ExportFormat.PowerPoint, grid);

            OutputBytesToTempDir("Document2.pptx", pptx);

        }

 

        private static void CreateDocument3()

        {

            List<GridDisplayElementRendering> elements = new List<GridDisplayElementRendering>();

 

            TextRendering textElement = new TextRendering();

            textElement.Range = new CellRange();

            textElement.Range.Format = new GridFormatInfo();

            textElement.Range.Format.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Left;

            textElement.Range.Format.FontInfo = new GridFontInfo();

            textElement.Range.Format.FontInfo.Bold = Trinary.True;

            textElement.Range.Format.FontInfo.FamilyName = "Tahoma";

            textElement.Range.Format.FontInfo.SizeInPoints = 10.0f;

            textElement.Range.Format.ForeColor = new GridColor(Color.Green);

            textElement.Range.Coordinates = "1,1,1,1";

            textElement.Range.Text = "Mixture of text, numbers and images";

            elements.Add(textElement);

 

            NumericRendering numericElement = new NumericRendering();

            numericElement.Range = new CellRange();

            numericElement.Range.Format = new GridFormatInfo();

            numericElement.Range.Format.FontInfo = new GridFontInfo();

            numericElement.Range.Format.HorizontalAlignment = GridFormatInfo.HorizontalAlignments.Left;

            numericElement.Range.Coordinates = "2,1,2,1";

            numericElement.Range.Text = "$80,450,597.00";

            numericElement.NumericValue = 80450596.9823M;

            elements.Add(numericElement);

 

            try

            {

                FileInfo imageFile = new FileInfo(@"c:\chart.bmp");

                FileStream imageFileStream = new FileStream(@"c:\chart.bmp", FileMode.Open);

                byte[] imageBytes = new byte[imageFile.Length];

                imageFileStream.Read(imageBytes, 0, Convert.ToInt32(imageFile.Length));

                imageFileStream.Position = 0;

                Image imageAnalysis = Image.FromStream(imageFileStream);

                imageFileStream.Close();

 

                ImageRendering imageRendering = new ImageRendering();

                imageRendering.Column = 1;

                imageRendering.Row = 3;

                imageRendering.ImageBytes = imageBytes;

                imageRendering.ImageHeight = imageAnalysis.Size.Height;

                imageRendering.ImageWidth = imageAnalysis.Size.Width;

                imageRendering.PaddedRowHeight = imageAnalysis.Size.Height;

                imageRendering.Range = new CellRange();

                imageRendering.Range.Coordinates = "3,1,3,1";

                imageRendering.Range.IsSpillOver = true;

                elements.Add(imageRendering);

            }

            catch

            {

                TextRendering errorText = new TextRendering();

                errorText.Range = new CellRange();

                errorText.Range.Coordinates = "3,1,3,1";

                errorText.Range.Text = @"If you place an image file at c:\chart.bmp, then an image will be displayed here.";

                elements.Add(errorText);

            }

 

            List<GridCellRendering> cells = new List<GridCellRendering>();

            foreach (GridDisplayElementRendering element in elements)

            {

                GridCellRendering cell = new GridCellRendering();

                cell.Elements = new GridDisplayElementRendering[] { element };

                cells.Add(cell);

            }

 

            // Tie it all together in a grid.

 

            PrimitiveGrid grid = new PrimitiveGrid();

            grid.Cells = cells.ToArray();

            grid.SheetName = "Document3";

            grid.TitleOfParts = "Document3";

 

            byte[] xlsx = NativeExport.ExportToOfficeDocument(ExportFormat.Excel, grid);

            OutputBytesToTempDir("Document3.xlsx", xlsx);

 

            byte[] pptx = NativeExport.ExportToOfficeDocument(ExportFormat.PowerPoint, grid);

            OutputBytesToTempDir("Document3.pptx", pptx);

        }

    }

}

 

 

Published 22 August 07 05:55 by alysonp
Filed under:

Comments

# Erika Ehrli said on September 28, 2007 7:30 PM:

The release of PerformancePoint Server 2007 should be of special interest for those of you who are passionate

# engy79 said on November 7, 2007 4:30 PM:

Is there an SDK method that saves/exports existing objects in Excel or PowerPoint formats?

Having to write custom code to export an existing report/kpi/scorecard is going to be a nightmare.

# ericfr said on November 7, 2007 8:47 PM:

engy79,

Yes, there is an SDK method that exports existing objects in Excel and PowerPoint formats!  But you wouldn't want to use the above article to help you with that.

FYI, I wrote the above article to handle the case where you DIDN'T have a PPS 2007 report/kpi/scorecard to start with.  This would help if you just wanted to create original Excel / PowerPoint docs more quickly and easily than generating XPS Packages natively.

The ability exports existing objects in Excel and PowerPoint formats has always been there in PPS 2007.  You need to submit a request to the OfficeExportPage.aspx web page.  That page will return an Excel spreadsheet (*.xlsx) or (*.pptx) document to you.  The problem is, how to parameterize OfficeExportPage.aspx isn't an obvious thing.  =(

Fortunately, at long last a blog entry is in the works that will help clarify how to use OfficeExportPage.aspx from a remote or automated process.  Stay tuned to this blog...

# ericfr said on November 8, 2007 1:58 PM:

engy79,

The article you want is hot off the presses (just posted today):

http://blogs.msdn.com/performancepoint/archive/2007/11/08/using-pps-m-a-reports-to-create-an-excel-powerpoint-report-deck.aspx

That explains how to post to OfficeExportPage.aspx, which will convert a PPS 2007 scorecard or report object to an xlsx or pptx file and return it to you.

# Debasishp said on September 15, 2008 12:30 PM:

1.How to retain formulas after exporting to Excel.

For Ex: I have Actuals, Forecast, Variance and Variance% in an scorecard. If Variance is an MDX calculated field at scorecard level. How this can be retained after exporting to excel.

2. How to have "Fit To Page" property by default in the exported Excel Sheet.

Please treat this as an urgent basis.

Many Thanks

Anonymous comments are disabled

Search

This Blog

Microsoft BI Sites

Syndication

Page view tracker