Page History

{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);
        }

{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]