Page tree

Versions Compared

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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