How to hide rows in a Pivot Table

Problem

ExcelWriter fully preserves existing pivot tables however the .NET implementation of the ExcelApplication object does not offer any support for manipulating the pivot tables. Since there is no way to programmatically hide pivot table rows with the ExcelApplication object this must be done in VBA.

Solution

In order to hide rows in your pivot table, you can include the following VBA which will execute when the user opens the spreadsheet in Excel.

Due to the sequence in which events are fired it is necessary to explicitly refresh the data in a pivot table in the Workbook_Open event instead of relying on the “Refresh on Open” Option in the pivot table options.

Disabling the option in Excel and refreshing the data in Workbook_Open() guarantees that the data has been refreshed after which you can the hide rows:

Private Sub Workbook_Open()
Worksheets(1).PivotTables("Pivot1").PivotCache.Refresh
Worksheets(1).PivotTables("Pivot1").PivotFields("Field1").PivotItems("Row1").Visible = False Worksheets(1).PivotTables("Pivot1").PivotFields("Field1").PivotItems("Row2").Visible = False
End Sub

Working with Reporting Services in SharePoint integration mode

Problem

From version 3.9.2 and above support has been added to OfficeWriter Designer to work seamlessly with Reporting Services running in SharePoint integrated mode. All operations such as retrieve, publish and view reports are supported.

This feature is available in the client-side OfficeWriter Designer for Excel and Word. The server-side components of OfficeWriter require no special modification to work with Reporting Services running in SharePoint integrated mode with one exception, outlined below. We recommend always using matching versions of the designer and the server-side installation of OfficeWriter.

There are a few things to note when using the Designer in SharePoint integrated mode that differ from regular use, which are outlined in this post.

Solution

The Designer

When publishing or retrieving a report using the Designer, enter your SharePoint root site in the server URL textbox, like in the image below. Enter the address without the additional /ReportServer usually added for Reporting Services not running in integrated mode:

Please Note: Adding /ReportServer after the SharePoint site address will cause the operation to fail with the following error message:

The operation is not supported on a report server that is configured to run in SharePoint integrated mode.

For more information about using the OfficeWriter Designer, refer to our documentation on the OfficeWriter SSRS Integration.

Server-side

In SQL Server Reporting Services 2012, Microsoft changed the structure of SharePoint integrated mode for SharePoint 2010 and above. Essentially, SSRS is run through SharePoint service applications, rather than through the traditional Reporting Services configuration. All configuration settings are stored in a database, rather than config files.  This affects how OfficeWriter’s server-side renderer is installed in SSRS integrated with SharePoint.

Starting in 8.5.1, OfficeWriter can install against SSRS 2012 with SharePoint 2010 or 2013. During installation, the user will be prompted to select their SSRS instance as well as the SharePoint service application to install the renderer to.

There are no other changes to how OfficeWriter behaves in SSRS and SharePoint integrated mode.

Running VBA macros in Internet Explorer

Problem

Some VBA macros in a spreadsheet processed by ExcelWriter or WordWriter fail to execute when the file is opened in Internet Explorer. However, if the file is saved to the local filesystem and opened afterwards in Excel or Word, the macro works fine.

Solution

This issue is with MS Office and Internet Explorer, not with OfficeWriter. OfficeWriter preserves all VBA macros when processing a spreadsheet or document. However, not all VBA will execute in the Excel or Word browser plug-ins. In order to guarantee that all your macros will run in your OfficeWriter-generated files, it is recommended to use the Save option that will open the file in a separate Excel or Word window rather than in the browser.

For more information, see this article: Some Macro Commands Are Not Run in MS Internet Explorer.

Code samples

To make sure that your OfficeWriter-generated files are opened in Excel or Word instead of the browser, specify ‘false’ when using the Save method:
xlt.Save(Page.Response, "WorkbookWithMacros_out.xlsm", false); //ExcelTemplate
xla.Save(workbook, Page.Response, "WorkbookWithMacros_out.xlsm", false); //ExcelApplication
wt.Save(Page.Response, "DocWithMacros_out.docm", false); //WordTemplate
wapp.Save(document, Page.Response, "DocWithMacros_out.doc", false); //WordApplication

How to create a Hanging indentation for a paragraph

Problem

In MS Word you can create a paragraph that has a hanging indentation. A hanging indentation means that the entire paragraph is indented except for the first line:

In Word this is created through the Paragraph dialog:

To get to this dialog in Word 2010/2007, select the paragraph you want to format > right click > select Paragraph. To get to this dialog in Word 2003, select the paragraph you want to format > Format menu > select Paragraph.

This post covers how to do this with WordWriter.

Solution

Though there isn’t a property for hanging indentation in the current version of WordWriter, if a hanging indentation is set in an input file or template the hanging indentation will be preserved.

It is also possible to create a hanging indentation using WordApplication by setting the indent property of the ParagraphFormatting object twice with SetIndent:

Note: The indentation is set using the Twips measurement unit (Twip = 1/1440 Inch). To simplify the use of Twips WordWriter provides a TwipsConverter object that allows you to convert twips to or from inches, centimeters and points.

The code below creates a 1 inch hanging indentation. Notice the negative value given to the FirstLine indentation:

ParagraphFormatting pFormat = doc.CreateParagraphFormatting();
//using the TwipsConverter to convert the inches into Twips
int TwipIndentVal = TwipsConverter.FromInches(1);


//setting the paragraph to be indented 1 inch left
pFormat.set_Indent(TwipIndentVal, ParagraphFormatting.IndentLocation.Left);


//The negative value takes back the first line by 1 inch, to the left margin
pFormat.set_Indent(-TwipIndentVal, ParagraphFormatting.IndentLocation.FirstLine);
Paragraph par = doc.InsertParagraphAfter(null, pFormat);

How to insert HTML into Word documents

Problem

Many customers have rich text data that’s stored in databases as HTML and they need to display the HTML-formatted data in Word documents.

Solution

Using WordTemplate:

The ability to insert RTF and HTML documents into Word documents was introduced in WordWriter 8.0 for the WordTemplate object. For more information, read our tutorial for Inserting an Embedded Document in the documentation.

Using WordApplication:

SoftArtisans has an open source project called HTMLtoWord that allows users to insert well-formed HTML (XHTML) snippets into Word documents as formatted text. This uses the WordApplication project, so you must have WordWriter Enterprise Edition to use this project.

The project can be downloaded from SourceForge.net. For more information, see Using HTMLtoWord.

How to delete extra pages from a document with WordApplication

Problem

Extra pages in a template document can sometimes be useful in making a template more versatile. For instance, a company could use a generic employment contract with unique pages describing each position, but with common pages on company policy.

This post covers how to delete extra pages from a template document using WordApplication

Solution

In order to delete unneeded pages from a template document, you should use bookmarks to identify the pages that you wish to delete. Then, use the Bookmark.DeleteElement() method, which is inherited from our Element object. Calling DeleteElement() on a bookmark in WordWriter will delete not only the bookmark, but its contents.

1. Place each extra page within a bookmark in your template file. Remember to include the page break within the bookmark so that it is removed when you remove the bookmark.

2. Before filling the template with data, open the file using the WordApplication object and delete the extra pages using the Bookmark.DeleteElement() method.

Document doc = wa.Open(@"..\\..\\templates\templatefile.doc");
Bookmark bm = doc.get_Bookmark("Bookmark");
bm.DeleteElement();

3. Pass the Document back to the WordTemplate object and bind with data.

Basic tricks for creating ranges with formulas

Problem

A range is a group of cells in an Excel workbook that can span multiple worksheets, which is defined by an underlying formula that specifies the worksheets and cells included in the range. The formula references the worksheets and cells by name, for example: Sheet1!A5:A6 or Sheet2!C14:C27.

ExcelWriter provides the option of creating named and unnamed ranges programmatically. Named ranges will remain in the output file, but unnamed ranges are only defined in code. Both Worksheet.CreateRange() and one of the overloaded methods for Worksheet.CreateNamedRange() require a formula string.

Here are a few useful tips for pulling together the formulas for [defining ranges|http://wiki.softartisans.com/display/EW8/Areas+and+Ranges] with ExcelWriter.

Solution

The formulas that express a range in Excel use sheet names and absolute or relative cell names to reference groups of cells.

Cell Names

Cell names can either be relative (A5) or absolute ($A$5). When the cell name is relative, the cell name can update. For instance, a formula with the relative cell name A5 will update to A6 if a row is inserted above row 5. When the absolute cell name is used, the name does not update. Both can be used in the formulas for ranges.

For example, the two statements below create the same range using the ExcelApplication object:
Range cond_range = wb.CreateRange("=Sheet1!C7:O7");
Range vRange = wb.Worksheets[0].CreateRange("=Sheet1!$C$7:$O$7");

For additional ease, the Cell.Name object can be used to return the relative name of the cell:
Workbook.Worksheets[0].Cells[0, 0].Name; //Returns "A1"

Worksheet Names In Excel

Formulas that express ranges also need to specify the worksheet that the cells belong to in order to avoid ambiguity. The general format to fully specify cells with a worksheet is to preface the cells with SheetName!. For example: Sheet1!C4:C5.

If the worksheet name contains a space, the entire worksheet name needs to be surrounded by single quotation marks: ‘Sheet 2’!A5:A7.

Worksheet Names with ExcelWriter

Just like in Excel, if a worksheet name has a space in it, make sure to surround the sheet name with ‘ ‘. Not doing so will result in a failed parser error message: Unable to parse formula: irrecoverable syntax error.

Worksheet name with space:
Workbook.Worksheets[0].Name = "Sheet 1";
Range rng = WB.CreateRange("='Sheet 1'!$C$7:$O$7");

Worksheet name without space:
Workbook.Worksheets[1].Name = "Sheet2";
Range rng2 = WB.CreateRange("=Sheet2!$C$7:$O$7");

Worksheet.Name can be used to set or return the name of the worksheet:
Workbook.Worksheets[0].Name = "Sheet 1";

Including separate groups of cells in a single range:

Separate groups of cells can be included in the same range. Each continuous group of cells needs to be prefaced by SheetName! and separated by commas. For example: Sheet1!C5:C6,Sheet1!D5:D6,Sheet2!E7:E10 is made of three separate groups of continuous cells that span two worksheets.

Below is some sample code that adds cells in specified columns to a range:

int[] cols = { 2, 3, 6, 12, 14 }; //0-index column numbers to include in the range
string range_formula = "=";
foreach (int col in cols)
{
range_formula = range_formula + "Sheet1!" + wb.Worksheets[0].Cells[row - 1, col].Name + ",";
}

How to insert images from the internet into a Word document

Problem

The Element.InsertImageAfter() method in the WordApplication object can insert an image into a document from an image file location or a FileStream. It cannot insert an image from a URL as a string, however. In order to insert an image from a URL, you must first retrieve the image as a stream.

For more information about inserting images with WordTemplate please refer to our documentation.

Solution

Response programmatically and return the Response as a stream. The HttpWebRequest and WebResponse objects are described in Microsoft’s MSDN documentation.

In this example, the Word document is being filled with data using the WordTemplate object. Then, it is passed to the WordApplication object, where the the SearchMatch object is used to find all instances in the document of a string ending in an image file extension that is WordWriter-compatible (.jpg, .jpeg, .gif, .png, .bmp) using a Regular Expression. Then the .NET HttpWebResponse and WebResponse objects are used to retrieve the image as a stream. Finally, the images are inserted into the document.

Note: If you are using this code in a Web Application, you will need to pass the correct credentials to the proxy server or you will receive a 407 error. Microsoft has a support article addressing this issue located here.

Example


protected void InsertImagesFromURL()
{
//Create the Word Template object and open the template WordTemplate
wt = new WordTemplate(); wt.Open(Page.MapPath("templates/DatabaseSourceTest.doc"));


//Insert sample images into a template document
string[] data = new string[2] { "http://www.mbta.com/images/logo-mbta.gif", "http://www.purepage.com/sample/purepageimages/final_logo_bmp.Bmp" };
string[] names = new string[2] { "OrderID", "CustomerID" };


//Set the datasource for the template
wt.SetDataSource(data, names);
wt.Process();


//Open the template as a WordApplication Document for editing
WordApplication wa = new WordApplication();
Document doc = wa.Open(wt);


//Call the SearchAndReplaceWithImage method to search the document for
//image URLs and replace them with images
SearchAndReplaceWithImage(doc);


//Export the final document
wa.Save(doc, Page.Response, "output.doc", false);
}


public void SearchAndReplaceWithImage(Document doc)
{
//Search for all strings in the document ending in
//.jpg, .jpeg, .png, .bmp.
//These are all of the valid picture types that can be imported
//using WordWriter.
//Use regular expressions to perform the search efficiently.
SearchMatch[] searcherator = doc.Search(@"(?i).*jpe?g$|.*gif$|.*png$|.*bmp$");


//For every string that ends with an image extension, do this
for (int i = 0; i < searcherator.Length; i++)
{


//Select a match from the SearchMatch array
SearchMatch match = (SearchMatch)searcherator[i];


//Use the DownloadAndInsertImage method to retrieve the image as
//a stream stream and insert it into the document.
DownloadAndInsertImage(match);
}
}


public void DownloadAndInsertImage(SearchMatch match)
{


//Retrieve the URL as a string from the SearchMatch object
string strhttp = match.Element.Text;


//Send an HTTP request and get the image at the URL as an HTTP response
HttpWebRequest myReq = (HttpWebRequest)WebRequest.Create(strhttp);
WebResponse myResp = myReq.GetResponse();


//Get a stream from the webresponse
Stream stream = myResp.GetResponseStream();


//Insert the image into the document and delete the URL string
match.Element.InsertImageAfter(stream);
match.Element.DeleteElement(); }

Modifying an existing named range with ExcelWriter

Problem

If a workbook created with Excel or ExcelWriter contains a named range, can the named range be modified (i.e. modify the areas it references)?

A customer asked about this specific scenario:

For example, when the document was first created a named range called “DataRange” was created in the workbook referencing cells A1:B9 on sheet1. The end user has been instructed to create pivots and charts based on this named range.

Then at a later date when new data is available I want to refresh the data in that spreadsheet and refresh the named range. If the number of rows of data has changed, say going from 9 records to 12, how can I update the existing named range to include the new area and thus have all the pivots/charts that were created by the end user automatically linked to the new area?

Solution

Currently there is not a way to modify the area referenced by a named range using the ExcelApplication object. There is a method Range.JoinRange that can add a new area to a named range.

In the scenario listed above, this won’t work for the customer because Excel does not let you specify a formula containing multiple areas as the data source for a pivot table. Doing so would result in a “Reference is not valid” error.

In Excel, if you insert new rows or delete rows inside an existing range, the range would be automatically adjusted; charts and pivot tables which refer to this range are automatically updated. We can follow this approach to update named ranges referenced by pivot tables.

Inserting Data Rows with ExcelApplication

  1. Open the Excel file with the ExcelApplication object.
  2. Retrieve the existing named range. using Workbook.GetNamedRange or Worksheet.GetNamedRanged.
  3. Insert enough rows to accommodate new data within the named range using Worksheet.InsertRows or Worksheet.InsertRow.
  4. Insert data into the new rows by setting Cell.Value or using Worksheet.ImportData.

Inserting Data Rows with ExcelTemplate

  1. Create ExcelWriter data markers within the existing named range (e.g. use ExcelApplication to write Cell.Value = “%%=DataSource.ColumnName”).
  2. Use ExcelTemplate to populate the data markers.

Deleting Data Rows

To delete existing rows, you can use the Worksheet.DeleteRows or Worksheet.DeleteRow method.

Customer Example

In the customer’s scenario, the new rows need to be inserted inside the named range (not at the beginning or end). Also, the pivot table must be set to “Refresh on open”, which is an option under pivot table options > data.

How to insert a line break using WordTemplate

Problem

To insert a line break in Word, you can press SHIFT+ENTER. This post covers how to insert a Word line break by injecting a newline character into the string of data that will populate a merge field.

Note: You cannot insert a paragraph break by injecting a character into your data. To start a new paragraph, you should use a separate merge field or use the WordApplication class to programmatically modify the document.

Solution

In this example, the template has one merge field, called “MyParagraph.”

The following code populates the merge field with two lines of text:

 WordTemplate wt = new WordTemplate(); wt.Open(Server.MapPath("./template.docx")); string[] fields = {"MyParagraph"}; object[] data = {"This is the first line." + '\n' + "This is the second line."}; wt.SetDataSource(data, fields); wt.Process(); wt.Save(Page.Response, "Output.docx", true); 

The populated document will contain a line break wherever ‘\n’ was inserted:

Blogged