Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migrated to Confluence 4.0
Description

This sample demonstrates how to extract data from an Excel file using ExcelWriter and import it to a PowerPoint presentation using PowerPointWriter.

The Data

Our Excel file contains employee data from the AdventureWorks database.
Image Removed

The Template

Our template presentation has a table with columns corresponding to the data in our Excel file.
Image Removed

The Code

The code below uses ExcelApplication to extract the data from the Excel spreadsheet into a DataTable. This code is also used in the ExcelWriter sample, Excel to DataTable Sample. It then passes the presentation to PowerPointTemplate and binds the data. The code uses MaxRowsPerSlide to ensure the data fits neatly in the presentation. See fitting Data on to Multiple Slides for more information.

Code Block
Wiki Markup
{description}
This sample demonstrates how to extract data from an Excel file using ExcelWriter and import it to a PowerPoint presentation using PowerPointWriter.

h3. The Data
Our Excel file contains employee data from the AdventureWorks database.
!data.png!

h3. The Template
Our template presentation has a table with columns corresponding to the data in our Excel file.  
!template.png!
h3. The Code
The code below uses ExcelApplication to extract the data from the Excel spreadsheet into a DataTable. This code is also used in the ExcelWriter sample, [EW8:Excel to DataTable Sample].  It then passes the presentation to PowerPointTemplate and binds the data.  The code uses [MaxRowsPerSlide|DatabindingProperties.MaxRowsPerSlide] to ensure the data fits neatly in the presentation.  See [fitting Data on to Multiple Slides] for more information.
{newcode}

        private static void RunExcelToPresentation()
        {
            //Use ExcelApplication to extract the data from the Excel spreadsheet
            //See http://wiki.softartisans.com/display/EW8/Excel+to+DataTable+Sample for more information
            ExcelApplication xla = new ExcelApplication();
            Workbook wb = xla.Open(@"..\..\inputs\Data.xlsx");

            Range dataRange = wb.GetNamedRange("Employees");
            Area dataArea = dataRange.Areas[0];

            int[] dateColumnIndxs = new int[] { 4 };

            DataTable dataTable = AreaToDataTable(dataArea, true, dateColumnIndxs, "Employees");

            //Populate the presentation with the data
            PowerPointTemplate ppt = new PowerPointTemplate();
            ppt.Open(@"..\..\inputs\ExcelToPresentationTable.pptx");

            SoftArtisans.OfficeWriter.PowerPointWriter.DataBindingProperties dataProps = ppt.CreateDataBindingProperties();
            dataProps.MaxRowsPerSlide = 10;
            ppt.BindData(dataTable, "Employee", dataProps);

            ppt.Process();
            ppt.Save(@"..\..\outputs\ExcelToPresentationTableOut.pptx");
        }

        private static DataTable AreaToDataTable(Area a, bool hasHeaderRow, int[] dateColumnIndexes, string tableName)
        {
            // Set count and index variables 
            int dataRowCount;
            int firstDataRowIdx;
            int columnCount = a.ColumnCount;

            if (hasHeaderRow == true)
            {
                firstDataRowIdx = 1;
                dataRowCount = a.RowCount - 1;
            }
            else
            {
                firstDataRowIdx = 0;
                dataRowCount = a.RowCount;
            }
            // Create DataTable 
            DataTable table = new DataTable(tableName);

            // Add columns to DataTable 
            for (int colIdx = 0; colIdx < columnCount; colIdx++)
            {
                string columnName;

                if (hasHeaderRow == true)
                {
                    // Get column name from cell value 
                    columnName = (string)a[0, colIdx].Value;
                }
                else
                {
                    // Use a generic column name 
                    columnName = "Column" + colIdx;
                }

                // Insert column into DataTable 
                table.Columns.Add(new DataColumn(columnName));
            }

            //Populate rows of DataTable 
            // For each row in Area 
            for (int rowIdx = 0; rowIdx < dataRowCount; rowIdx++)
            {
                // Add row to DataTable 
                DataRow row = table.NewRow();
                table.Rows.Add(row);

                // For each column 
                for (int colIdx = 0; colIdx < columnCount; colIdx++)
                {
                    // Get value from cell 
                    object val = a[firstDataRowIdx + rowIdx, colIdx].Value;

                    // If current column is in the array of date columns, convert to a .NET date.
                    //(Excel dates are stored in a serial format which we must convert.)

                    if (Array.IndexOf(dateColumnIndexes, colIdx) != -1)
                    {
                        // Convert to a .NET date using conversion method below 
                        DateTime dt = ExcelSerialDateToDateTime(Convert.ToInt32(val));

                        // Add date to DataTable 
                        row[colIdx] = dt.ToShortDateString();
                    }
                    else
                    {
                        // Otherwise, just add cell value to DataTable 
                        row[colIdx] = val;
                    }
                }
            }

            // Return DataTable 
            return table;
        }

        private static DateTime ExcelSerialDateToDateTime(int serialDate)
        {
            if (serialDate == 60)
            {
                return new DateTime(1900, 2, 29);
            }
            else if (serialDate < 60)
            {
                serialDate++;
            }

            return new DateTime(1900, 1, 1).AddDays(serialDate - 2);
        }

Result

The resulting output shows all the data from the Excel spreadsheet in a PowerPoint presentation table.

Image Removed

Downloads

...

{newcode}
h3. Result
The resulting output shows all the data from the Excel spreadsheet in a PowerPoint presentation table. 

!output.png!

h1. Downloads
data:[Extracting Data from Excel^Data.xlsx]
template:[Extracting Data from Excel^template.pptx]
output: [Extracting Data from Excel^output.pptx]