Category Archives: OfficeWriter

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:

How to make the pie chart display larger

Problem

There is a known issue with pie charts that are created with ExcelApplication in the binary XLS file format. Pie chart display was improved in ExcelWriter 8 for the XLSX file format as part of the ExcelApplication OOXML implementation.

This post addresses how to improve the size of a pie chart in an XLS file.

Solution

There are two approaches for increasing the size of a pie chart:

Approach Before After
Increase the size of the chart while maintaining the proportions between the pie graphic and the chart frame. This requires making the chart (as a whole) larger.
Increase the size of the chart in relation to the chart frame. This requires making adjustments to the chart’s plot area.

Option 1: Increase the pie chart and maintain current proportions

If the pie chart graphic displays too small, increase the chart’s area by providing more room for it in Add method of the Charts object. The size of the pie graphic in the chart is directly related to the amount of space provided for the chart.

To provide more area for the chart, after adding the chart with the AddChart method:

  1. Make the chart wider by increasing the Chart.Width property
  2. Make the chart taller by increasing the Chart.Height property

Option 2: Change the proportion of the chart size in relation to the chart frame

Use the PlotArea‘s Height and Width properties. The values you can provide range from 1 to 4000. 4000 makes the chart take up the maximum area possible. In many cases, this value will be too large. For the area provided in the above code, 2500 is a good value for the PlotArea Width and height.

OfficeWriter and Excel Services: When to Use Which

Current and potential customers often ask our sales team: “what is the difference between OfficeWriter and Excel Services?” The high level answer is that that they are very different products designed to address very distinct scenarios. In brief, OfficeWriter is a tool that developers can use to generate, read, and manipulate Excel and Word documents in .NET code. Excel Services is a SharePoint-based server product for sharing, managing, and securing Excel workbooks.

Note that the title of this article is “OfficeWriter and Excel Services”, not “OfficeWriter vs. Excel Services”! There are scenarios where they are complementary to one another, and can be used together to solve some pretty interesting problems.

Excel Services Core Capabilities

Excel Services is a server-based workbook sharing, viewing, and calculation service. It’s a server product that is part of the SharePoint platform. It requires an enterprise-level SharePoint license to use.

The features discussed here pertain to Excel Services in SharePoint 2010, which is the latest version. Excel Services focuses on several key scenarios:

  1. Sharing and viewing workbooks through a browser– Excel Services allows for thin, web-based viewing of Excel 2007 and 2010 workbooks. This feature works only with modern XLSX files, not the legacy XLS files. The key benefits of this are:
    1. Excel is not required to be installed in order to view the file since it’s rendered in a web-based view
    2. Web-based views are interactive to a certain extent. Certain features including filtering, sorting, and page-level slicers are supported.
    3. Since an XLSX file viewable by Excel Services is just another item in document library, it can be secured and managed with SharePoint permissions just as any other document.
    4. The web-based view can be hosted in SharePoint webparts to become part of a larger dashboard.

    Continue reading OfficeWriter and Excel Services: When to Use Which

Exporting SQL Server Database Data to Excel

Introduction

The need to get business data from a SQL Server database (or any other kind of RDBMS for that matter) into Excel for further analysis is universal.  I would venture to say that anyone in any business role from marketer to analyst to salesperson to executive to program manager has had the occasion to say: “I need this data in Excel so that I can crunch it!”

Since getting data into Excel is a common requirement, it stands to reason that there are several different ways to accomplish the task.  For simple, non-stylized, or purely tabular data imports (situations where you don’t really care about styles or fonts or corporate branding) Excel’s out-of-the-box data import solution can be an option.  For more sophisticated scenarios that require a specific look and feel, or something more complex than just straight tabular data dumps there is OfficeWriter.

This article will discuss both methods for getting your data from SQL Server into Excel.

Methods for Exporting Database Data to Excel

As I mentioned there is more than one way to skin the cat here.  Which method you choose depends on what you need to accomplish.

Method 1: Excel-based data access

Continue reading Exporting SQL Server Database Data to Excel