Intro
This demo shows how to open an existing Excel workbook and programmatically edit properties of existing charts with OfficeWriter. Select the major product categories you would like to see in the report. The SeriesCollection of the existing chart will be changed at runtime to reflect the number and types of categories you chose.
Code
Excerpt |
---|
An AdventureWorks Product Catalog with formatting, tables, lists, nested data, and images. |
Create a product catalog by writing data into sections of an existing Word document. This sample demonstrates writing data from a database, formatting, nested Word lists, Word tables, and image insertion
Code
Code Block |
---|
private WordApplication wwapp;
private Document doc;
private Font charFormat;
// The three major product categories
string[] categories = { "Component", "Clothing", "Accessory" };
private bool bCategoriesOnNewPage = true;
// This DataTable will store all the product details
private DataTable data;
/// <summary>
/// Build the report with WordTemplate
/// </summary>
public void GenerateDocument()
{
// Create an instance of WordApplication and open the template document
wwapp = new WordApplication();
string templatePath = @"..\..\WordTemplateFiles\ProductCatalogTemplate.doc";
doc = wwapp.Open(templatePath);
// This document has three existing sections.
// The title page, the welcome letter page, and the
// blank section where the catalog items should be started.
Section secTitlePg = doc.Sections[0];
Section secWelcomePg = doc.Sections[1];
Section secCatalogPg = doc.Sections[2];
// Get a reference to the base style and set up a CharacterFormat
// object for use throughout this class.
NamedStyle baseStyle = doc.Styles[NamedStyle.BuiltIn.Normal];
charFormat = baseStyle.Font;
charFormat.FontName = "Arial";
// Add the current date to the first section
AddGeneratedDate(secTitlePg);
// Add a bulleted list to the second section.
// Bulleted items will be numbered.
AddList(secWelcomePg, true);
// Add the catalog items to the third section
// Populate data DataTable
// GetCSVData() is a helper method for parsing .csv files
data = GetCSVData(@"..\..\WordData\ProductCatalog2.csv");
AddCatalog(secCatalogPg);
// Save the document by streaming it to the client's browser
wwapp.Save(doc, @"..\..\WordOutputFiles\ProductCatalog_out.doc");
}
/// <summary>
/// Add the date string to the provided Range.
/// Date will be written in the form: "Jan 1, 2009"
/// </summary>
/// <param name="rng">Element into which the date should be written</param>
private void AddGeneratedDate(Element rng)
{
DateTime dt = DateTime.Now;
string datestring = String.Format("{0}/{1}/{2}", dt.Month, dt.Day, dt.Year);
rng.InsertTextAfter(datestring, false);
}
/// <summary>
/// Add the bulleted list of requested categories
/// and subcategories to the provided Range.
/// </summary>
/// <param name="rng">Element into which the list should be written</param>
/// <param name="bNumbered">Numbered (ordered) list</param>
private void AddList(Element rng, bool bNumbered)
{
// Add line breaks
rng.InsertParagraphAfter(null);
rng.InsertParagraphAfter(null);
// Create the List
List list = rng.InsertListAfter(bNumbered);
// Loop for every selected category
for (int iCat = 0; iCat < categories.Length; iCat++)
{
// Add the category text to the first level of the outline.
// Make the font bold-faced.
CharacterRun categoryRun =
list.AddEntry(0).InsertTextAfter(categories[iCat], charFormat);
categoryRun.Font.Bold = true;
// Get a list of product subcategories from the major
// category name.
string[] subCats = GetProductSubCategories(categories[iCat]);
// Loop for every subcategory in the current category
for (int iSubCat = 0; iSubCat < subCats.Length; iSubCat++)
{
// Add the subcategory text to the second
// level of the outline.
ListEntry li = list.AddEntry(1);
//li.LineNumberingAllowed = true;
li.InsertTextAfter(subCats[iSubCat], charFormat);
} // for each subcategory
} // for each category
}
/// <summary>
/// Add the main product catalog to the provided Range.
/// </summary>
/// <param name="rng">Element into which the catalog will be written</param>
private void AddCatalog(Element rng)
{
// Loop for every major category chosen
for (int iCat = 0; iCat < categories.Length; iCat++)
{
// Get the category name from the array categories
string catName = categories[iCat];
// Insert a table 1row x 1col. This table will
// hold the header text for every major category.
Table catTbl = doc.InsertTableAfter(1, 1);
// Write the major category name into the table cell.
// Set cell background and font appearance.
CharacterRun catRun =
catTbl[0, 0].InsertTextAfter(catName, charFormat);
catTbl[0, 0].Shading.BackgroundColor = System.Drawing.Color.Gray;
catRun.Font.Bold = true;
catRun.Font.Italic = true;
catRun.Font.FontSize = 26.0f;
// Get subcategory names for the current category
string[] subCats = GetProductSubCategories(catName);
// Marker
// // Loop once for every subcategory in the current category
for (int iSubCat = 0; iSubCat < subCats.Length; iSubCat++)
{
doc.InsertParagraphAfter(null);
string subCat = subCats[iSubCat];
// Insert a smaller header for the subcategory
CharacterRun subCatRun =
doc.InsertParagraphAfter(null).InsertTextAfter(subCat, charFormat);
subCatRun.Font.Bold = true;
subCatRun.Font.FontSize = 22.0f;
// GetProductCatalogData
// Get a list of products in the current subcategory
DataTable dtProducts = GetProductCatalogData(subCat);
// Loop for every product in the subcategory
//REFINE THIS COMMENT
foreach (DataRow dr in dtProducts.Rows)
{
// Insert a table to hold product data
Table prodTbl = doc.InsertTableAfter(4, 2);
// Merge the top row of cells
prodTbl[0, 0].FirstMerged = true;
prodTbl[0, 1].Merged = true;
// Add the product name and set font appearance
CharacterRun titleRun =
prodTbl[0, 0].InsertTextAfter(dr["Name"].ToString(), charFormat);
titleRun.Font.FontSize = 18.0f;
titleRun.Font.Bold = true;
titleRun.Font.Italic = true;
titleRun.Font.TextColor = System.Drawing.Color.Blue;
// Add product description to the next row
prodTbl[1, 0].InsertTextAfter(dr["Description"].ToString(), charFormat);
// InsertImageAfter method
if (dr["ThumbNailPhoto"].ToString() != "")
{
InlineImage image = prodTbl[1, 1].InsertImageAfter(@"..\..\WordImages\ProductCatalogImages\" + dtProducts.Rows[dtProducts.Rows.IndexOf(dr)]["ThumbNailPhoto"]);
double width = image.Width;
double height = image.Height;
Console.WriteLine("W: " + image.Width + ", H: " + image.Height);
image.Height=(int)( height//(2000 / width));
image.Width=2000;
Console.WriteLine("W: "+image.Width+", H: "+image.Height);
}
// Write the rest of the product details into the table
prodTbl[2, 0].InsertTextAfter("Product Number", charFormat).Font.Bold = true;
prodTbl[3, 0].InsertTextAfter(dr["ProductNumber"].ToString(), charFormat);
prodTbl[2, 1].InsertTextAfter("Price", charFormat).Font.Bold = true;
prodTbl[3, 1].InsertTextAfter("$" + dr["Price"].ToString(), charFormat);
}
}
// If it was chosen to put categories on new pages,
// AND if the last category hasn't just been written,
// add a section/page break.
if (bCategoriesOnNewPage && (iCat < categories.Length - 1))
{
Section sec = doc.CreateSectionAfter();
sec.Break = Section.BreakType.Page;
}
}
}
/// <summary>
/// Set document property metadata for the catalog document
/// </summary>
private void AddDocProperties()
{
// Get the DocumentProperties interface from Document
DocumentProperties docprops = doc.DocumentProperties;
// Set built-in DocumentProperties values
docprops.Author = "John Doe";
docprops.Comments = "A basic demonstration of OfficeWriter for Word";
docprops.Company = "SoftArtisans, Inc.";
docprops.Title = "Basic Word Document";
// Set custom DocumentProperties key/value pairs
docprops.SetCustomProperty("GeneratedBy", "SoftArtisans OfficeWriter for Word");
}
/// <summary>
/// Get a list of products for a subcategory.
/// </summary>
/// <param name="subCat">Subcategory name to look up</param>
/// <returns>DataTable of product from the provided subcategory</returns>
private DataTable GetProductCatalogData(string subCat)
{
DataTable dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Description");
dt.Columns.Add("ProductNumber");
dt.Columns.Add("Price");
dt.Columns.Add("ThumbNailPhoto");
//find data
int iRow = 0;
while (iRow < data.Rows.Count)
{
if (data.Rows[iRow]["Subcategory"].ToString() == subCat)
{
dt.ImportRow(data.Rows[iRow]);
}
iRow++;
}
return dt;
}
/// <summary>
/// Get a list of subcategory names for the provided major category.
/// </summary>
/// <param name="cat">Category to look up</param>
/// <returns>Array of product subcategory strings</returns>
private string[] GetProductSubCategories(string cat)
{
ArrayList al = new ArrayList();
if (cat == "Clothing")
{
al.AddRange(new string[]{"Bib-Shorts","Caps","Gloves","Jerseys","Shorts","Socks","Tights","Vests"});
}
if (cat == "Component")
{
al.AddRange(new string[]{"Handlebars","Bottom Brackets","Brakes","Chains","Cranksets","Derailleurs","Forks","Headsets","Mountain Frames","Pedals","Road Frames","Saddles","Touring Frames","Wheels"});
}
if (cat == "Accessory")
{
al.AddRange(new string[]{ "Bike Racks","Bike Stands","Bottles and Cages","Cleaner","Fenders","Helmets","Hydration Packs","Lights","Locks","Panniers","Pumps","Tires and Tubes"});
}
return (string[])al.ToArray(typeof(string));
}
#region Utility Methods
//Uses CSV reader
System.Data.DataTable GetCSVData(string csvFileName)
{
DataTable dt;
using (GenericParserAdapter parser = new GenericParserAdapter(csvFileName))
{
parser.ColumnDelimiter = ',';
parser.FirstRowHasHeader = true;
dt = parser.GetDataTable();
}
return dt;
}
#endregion
|
Downloads
Template: ProductCatalogTemplate.doc
Output: ProductCatalog_out.doc