ExcelWriter, Knowledge Base

How to keep cell references absolute when using ExcelTemplate

Problem

When using ExcelTemplate to import data, a new row is inserted into the worksheet for each row of data in the data source. Any cell references to the cells where the new rows are being inserted will be updated to reflect that new rows have been inserted. This includes relative cell references (e.g. A5) and formulas (e.g. SUM(A5:A7)).

This is native Excel behavior: whenever a row (or column) is inserted or deleted, all cell references to that row/column will be updated.

In some cases, having the formulas or references updated may not be the desired behavior.

Solution

There are two ways to keep cell references absolute:

Use ‘$’ to denote absolute references

In Excel, absolute references are denoted with ‘$’. If a reference is absolute, then ExcelTemplate will not update the reference when rows are inserted. Here are some examples of absolute references:

  • $B5 – The column A is absolute and will not change, even if a new column is inserted between columns A and B. Rows will still update if new rows are inserted.
  • B$5 – The row 5 is absolute and won’t change if rows are inserted or deleted. If a column were to be inserted, then the column reference would update.
  • $B$5 – The cell reference is absolute and will refer to B5 even if rows/columns are inserted/deleted.

Use INDIRECT to preserve formula references

If a cell reference is pre-pended with INDIRECT, Excel treats the reference as a string and does not change it. For example, the formula =AVERAGE(INDIRECT(“Sheet1!E1:E10”)) will always refer to that particular range of cells.

Additional Reading

Email, RSS Follow
Email

Women In Tech

Stories from the WIT Trenches: Erin Stellato

[This is the fourth in a series of posts exploring the personal stories of real women in technology. Every woman in tech overcame at the very least statistical odds to be here; this blog series aims to find out why, and what they found along the way. Those of you engaged in the virtual and IRL SQL communities may already know Erin Stellato from her active and informative presences at conferences and user groups, on Twitter and on her blog. Here, she talks Commodore 64s, nature vs. nuture and her evolution from Kinesiology major to Senior DBA. If reading her story inspires you to share yours, please feel free to email me.]

My name is Erin Stellato and I’m a Senior Database Engineer for a software company outside Cleveland, Ohio.  I have been working in technology for almost 11 years, and with SQL Server for over 8.  I’ve been involved in the SQL Community since 2010, and spend my time on Twitter, blogging and presenting at SQLSaturdays.  I am active in our local user group and will be presenting at my first PASS Summit this fall.

1)      Can you take us back to your “eureka!” moment—a particular instance or event that got you interested in technology?

I think it starts with my dad…he always had the latest electronics.  My dad loves watching TV, especially movies.  In our house this meant that we had a big TV, a satellite and a VCR.  We also had an Intellivision, which was a bummer for me because all my friends had Ataris, but I still played it.  A lot.  We also had a Commodore 64.  I remember my mom sitting down and typing out a “Hello World!” program.  I tried it as well, and figured out how to make it type different words.  I thought that was cool.  My mom worked in the radiology department of a hospital, and when I would tag along when she got called in.  I was able to see her use the Ultrasound or CT machines, which were pretty new at that time.  It was a lot of lights and buttons, but you could see inside a person on the fly.  It didn’t require the waiting of a normal x-ray…point, shoot, develop, wait, and then see.  Technology was pervasive in my life growing up, but it wasn’t something we discussed.  It was just there. Continue reading Stories from the WIT Trenches: Erin Stellato →

Email, RSS Follow
Email

Knowledge Base, WordWriter

How to use SetCultureInfo with date functions and formatting

Problem

The SetCultureInfo method allows you to override the server’s default locale when generating a new Word file from a template with WordWriter. This is especially useful for the display of dates. It is important, however, to understand that any date formatting must be applied to the Word Template Merge Field instead of in the ASP.NET script.

Solution

To see a standard field code for a DateTimeObject merge field, right-click on the field, and choose Toggle Field codes:

 {MERGEFIELD DateTimeObject}

You can see that there are no formatting switches applied to this field. In this situation, the default format of “yyy-MM-dd HH:mm:ss” (4-digit year, month, day, military hour, minute, second) will be applied. To set the culture info to a particular language, such as German:

CultureInfo deDe = new CultureInfo("de-DE");
wt.CultureInfo = deDe;
For more information see our documentation on WordTemplate.CultureInfo.

Best coding practices

Unexpected date formatting behavior can be prevented by keeping the following in mind: The Word template is expecting to receive the date as a System.DateTime object. If it is passed a String instead of a System.DateTime object, it will not be able to apply the formatting codes and apply the correct Culture settings.

Email, RSS Follow
Email

ExcelWriter, Knowledge Base

How to check if a cell is empty

Solution

A cell can contain a value and/or a formula. To check if a cell is empty use the Cell.Value and Cell.Formula properties to look for the following conditions:

XLS Files XLSX and XLSM Files
Language Cell.Value Cell.Formula Cell.Value Cell.Formula
C# null (empty string) (empty string) OR null (empty string)
VB.NET Nothing Nothing (empty string) OR Nothing Nothing
ASP/COM * (empty string) (empty string) (empty string) (empty string)

 

Example

//--- myCell is a Cell object 
if (string.IsNullOrEmpty(myCell.Value) && string.IsNullOrEmpty(myCell.Formula))
{
//--- Cell is empty
}


Email, RSS Follow
Email

Knowledge Base, WordWriter

How to apply a banded style on a table with WordTemplate

Problem

In a template document created in Word 2003, table styles behave correctly when new rows are added by WordWriter. However, if a new template document is created in Word 2007 with the new table styles, these styles do not persist as expected when saved in the binary (.doc) format. This can easily be demonstrated with the banded coloring styles which are displayed as alternating row coloring.

After processing with WordTemplate, the additional rows in the table will have a solid background color instead of a banded styling. MS Word itself shows the same behavior when adding rows to a table styled with the new 2007 styles and saved to the 97-2003 binary file format (.doc). This appears to be a compatability issue at the file format level, with Word not fully supporting the new table styles in the binary file format.

Banded styles behave as expected with OOXML (.docx) files, which have been supported by WordTemplate since version 4.0.

Solution

Option 1: Save the file as .doc before applying table formatting

When Word 2007 opens a .doc file, it opens in compatibility mode which only makes available features that are supported for that format. However, when Word 2007 creates a new document, it doesn’t know which format the user will choose to save it in, so all options are shown, including those which aren’t compatible with the binary format.

The easiest solution would be to save the file as 97-2003 binary file format (.doc) before applying any table formatting. Once the file has been saved, only the styles compatible with the binary format will be available and you can use them freely. If you have already created a template file with 2007 formatting, simply reapply the styling to the tables after the file has been saved in binary format.

Option 2: Use the OOXML file format

Alternatively, with WordWriter 4.0 and above, the other good solution is to use the OOXML (.docx) file format. The resulting populated table will be as you expect when using that file format. The feature is supported in the file format level, so you can place your data marker in such formatted table and it will expand and maintain the selected style.

Email, RSS Follow
Email

Knowledge Base, WordWriter

How to use merge documents together with Document.Append

Problem

Prior to WordWriter 4.5.0, the only way to merge entire documents was to use InsertAfter. Starting in WordWriter 4.5.0, Document.Append() was introduced as an improved way to merge documents together.

This post covers the behavior of Document.Append().

Solution

The default behavior of Document.Append() is creating a section-page break between the original document and the inserted document:

 documentOne.Append(newDocument);

To merge the two documents so they appear continuous, simply change the section break type to be continuous:

int mySectionsCount = thisDocument.Sections.Length;
thisDocument.Append(otherDocument);
thisDocument.Sections[mySectionsCount].Break = Section.BreakType.Continuous;

Example:

For a more complicated example, here we want to insert into our current work document (thisDocument) a header from one template document and body content from another template document. Note that we want both the header and the body to be on the same page, so we change the type of the section break at the tail of the header to continuous.

// adding header document
Document headerDocument = Wapp.Open(_headerSource);
thisDocument.Append(headerDocument);

// adding body document
Document bodyDocument = Wapp.Open(_bodySource);
int sectionCount = thisDocument.Sections.Length;
thisDocument.Append(bodyDocument);
thisDocument.Sections[sectionCount].Break = Section.BreakType.Continuous;
Note that if the initial document (the one you append into) is empty, we get a blank page at the beginning of the document. To fix that we need to delete the first empty section:

 thisDocument.Sections[0].DeleteElement();

All other InsertAfter operations that don’t deal with sections should stay unchanged.

Email, RSS Follow
Email

Knowledge Base, WordWriter

Inserting a subreport into a WordWriter report

Problem

You need to insert a subreport into a larger report using WordWriter.

Solution

Option 1:

Generate each subreport separately and then use the WordApplication API to merge the subreports into the larger report. The subreports could be appended to the main report document with Document.Append, or if you need to insert the subreport into a particular place, you can use Element.InsertAfter or Element.InsertBefore.

Option 2

Flatten your data set and use WordWriter Grouping and Nesting. Grouping and Nesting would allow you repeat sections of a document for a particular subgroup in the data.

NOTE: Both these solutions require WordWriter Enterprise Edition. For more information about the features offered in Enterprise Edition, check out the OfficeWriter Change Log page.

Email, RSS Follow
Email

ExcelWriter, Knowledge Base

How to create a chart with different chart types

Problem

ExcelWriter supports creating custom charts, including charts that contain several different chart types. For example, a chart wtih two area series and one line series.

Solution

This code snippet creates a chart with two area series and one line series:

Chart chart = ws.Charts.CreateChart(ChartType.Area.StandardArea,ws.CreateAnchor(5, 0, 0, 0));
chart.SeriesCollection.CreateSeries("A1:A3");
chart.SeriesCollection.CreateSeries("B1:B3", ChartType.Area.StandardArea, AxisType.Primary);
chart.SeriesCollection.CreateSeries("C1:C3", ChartType.Line.StandardLine, AxisType.Primary);

Email, RSS Follow
Email

ExcelWriter, Knowledge Base

Optimizing ExcelApplication performance to reduce memory or time usage

Problem

Your program, which uses the ExcelApplication object, is using more memory than you would like or is taking too long to generate a report.

Solution

The Best Pratices with Large Reports article in our documentation discusses how to optimize for large ExcelApplication reports by reducing memory or run time. It includes, but is not limited to:

  • What to avoid when referencing cells, inserting rows or columns, and applying styles
  • Certain methods are known to be memory intensive, such as AutoFitWidth
  • Ways to improve performance by changing how and when data is imported

There are also code examples that compare inefficient code and code that was improved by the recommendations in the article. These code examples are split up into Memory Related Performance Issues and Time Related Performance Issues.

Email, RSS Follow
Email

Knowledge Base, WordWriter

Tools to convert Word documents to PDF

Problem

WordWriter does not support converting between file formats. This includes converting between DOC/DOCX and PDF. We generally recommend that you use a 3rd party component to convert Word documents to PDF files.

Solution

Note: Most Word to PDF converters require Office to be installed on the server in order to render the Word components before generating the PDF file. Microsoft does not recommend this:

Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment.

For more details, see Considerations for server-side Automation of Office.

Word Services for SharePoint

If you are using SharePoint Server 2010, then one possibility is to use Word Services to perform the format conversion. This entails writing code to convert a Word document that is in a list or a document library in SharePoint.

This is a blog post that describes Converting Word Documents to PDF using SharePoint Server 2010 and Word Services. In this case, Word Services automatically converts a document to PDF when the Word document is added to a list.

Rainbow PDF

Rainbow PDF by Atenna House has a server-based converter that does not require Office on the server. However, since Office is not installed, this component does not have the ability to render all Word document elements. Word documents rendered to PDF with Rainbow PDF experience loss of fidelity.

EasyPDF

EasyPDF by BCL Technologies has a a PDF Creator SDK as well as server-side solutions that can convert Word documents to PDF. EasyPDF does require Office to be installed on the server, however it carefully manages the instances of Word to avoid hitting the pitfalls that normally happen when automating Office on the server.

Email, RSS Follow
Email

Blogged