OfficeWriter Documentation
.
PowerPointWriter v8
Space Shortcuts
Page tree
    Loading...
Skip to end of metadata
Go to start of metadata

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.

The Template

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

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.

?
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.

Downloads

data:Data.xlsx
template:template.pptx
output: output.pptx

  • No labels