Description |
---|
Excerpt |
---|
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 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 Fitting Data on to Multiple Slides for more information.
Code Block |
---|
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);
}
|
...