How to insert a hard carriage return into a string of data

Problem

In Excel, to have text displayed on multiple lines, two conditions must be met:

  1. The cell must be formatted with Wrap Text
  2. The text must contain the new line character (hard carriage return)

When you press ALT+ENTER in Excel, Excel automatically formats the cell to have Wrap Text and inserts a new line character into the cell.

This post covers how to insert a hard carriage return into data that is imported into an Excel file with ExcelWriter.

Solution

Text Wrap

If you are importing data using ExcelTemplate, make sure to format the cell alignment with “Text Wrap” in the cell that contains the data marker. This also applies if you are importing data using ExcelApplication with a partial template.

To set “Wrap Text” in Excel:

  1. Right-click on the cell
  2. Go to the Alignment tab
  3. Under Text Control, check off “Wrap Text”

If you aren’t working with a template, you can also set this property on a cell using ExcelApplication:

 //The WrapText property must be set to true in the cell containing the new line character worksheet.Cells["A1"].Style.WrapText = true; 

New Line Character

To import the new line character with your data, just add the new line character to your string of data.

Here is an example of using ExcelApplication to write a string into a cell. The string contains the new line character:

 worksheet.Cells["A1"].Value = "Fred Smith," + '\n' + "President"; 

Here is an example of some data that might be bound to a ExcelTemplate file:

 object[] dataArray = new object {"Fred Smith \n President", "2003", "Boston, MA"};

How to retrieve a formatted date from an Excel workbook

Problem

When I retrieve the value of a cell which has a Date format in Excel, I do not get the formatted Date; I get a number. How do I retrieve the formatted Date instead?

Solution

Excel Dates are stored in a “serial date format” behind-the-scenes. So, for example, 2/4/2004 is stored as 38021 in Excel. This number represents the number of days elapsed since January 1, 1900.

When you display a Date in Excel, a built-in conversion is performed which converts this serial format into a string for display in the cell depending on the format you apply to that cell.

More information on Excel Serial Dates can be found at http://www.cpearson.com/excel/datetime.htm.

If you want to use a Date from a cell in your code, all you will be able to retrieve is the serial value of this Date. To use it as a formatted string you will have to convert it.

A set of conversion methods can be found at Code Project.

You will need to use one of them in your application in order to convert the serial value of the date to a formatted string. Once you have a formatted string, you can also create a Date object in .NET (using Convert.ToDateTime() for example) and use the built in .NET Date functions if desired.

A simple example in VB.NET

This example retrieves a serial date from an Excel cell and uses a VB.NET version of the ExcelSerialDateToDMY() method from The Code Project to convert it to its day, month, and year components. To run this example, you would call ReadDate() from the PageLoad event or a ButtonClick event, for example. ReadDate() reads the Date (in serial date format) from cell “E1” of sample2.xls. It then calls ExcelSerialDateToDMY() to retrieve the converted day, month, and year values of the date. It then creates a new .NET Date object using those values. Finally, it writes the .NET Date’s short string value to the Response, so you can see that the correct values were retrieved.

Private Sub ReadDate()
'Create Application object and load Excel file from disk
Dim xlw As New SAExcelApplicationDotNet xlw.Open(Server.MapPath("./sample2.xls"), False)
Dim sheet As SAWorksheet sheet = xlw.Worksheets(1)


'Read cell E1, it's a date
Dim sd As Integer = sheet.Cells("E1").Value

' The following 3 variables are used as "output" parameters to the ExcelSerialDateToDMY()
' method, since the method uses the parameters By Reference, not By Value.
Dim d As Integer ' Will contain the day as an Integer
Dim m As Integer ' Will contain the month as an Integer
Dim y As Integer ' Will contain the year as an Integer


' Retrieve the day, month, and year values of the serial date.
ExcelSerialDateToDMY(sd, d, m, y)


' Create a new .NET Date object with the retrieved values, and display it on the page.
Dim dt As Date = New Date(y, m, d)
Response.Write(dt.ToShortDateString)
End Sub


Private Sub ExcelSerialDateToDMY(ByRef nSerialDate As Integer, ByRef nDay As Integer, _
ByRef nMonth As Integer, ByRef nYear As Integer)
' This function is courtesy of The Code Project.
' http://www.codeproject.com/datetime/exceldmy.asp?df=100&forumid=4548&exp=0&select=590906


' nSerialDate is the serial date from Excel
' nDay, nMonth, and nYear are the "output parameters" where the method will store
' the Day, Month, and Year values as Integers that it extracts from nDate


' Excel/Lotus 123 have a bug with 29-02-1900. 1900 is not a
' leap year, but Excel/Lotus 123 think it is...
If nSerialDate = 60 Then
nDay = 29
nMonth = 2
nYear = 1900
Return
ElseIf nSerialDate < 60 Then
' Because of the 29-02-1900 bug, any serial date
' under 60 is one off... Compensate.
nSerialDate = nSerialDate + 1
End If


' Modified Julian to DMY calculation with an addition of 2415019
Dim l As Integer = nSerialDate + 68569 + 2415019
Dim n As Integer = CInt(Fix((4 * l) / 146097))
l = l - CInt(Fix((146097 * n + 3) / 4))
Dim i As Integer = CInt(Fix((4000 * (l + 1)) / 1461001))
l = l - CInt(Fix((1461 * i) / 4)) + 31
Dim j As Integer = CInt(Fix((80 * l) / 2447))
nDay = l - CInt(Fix((2447 * j) / 80))
l = CInt(Fix(j / 11))
nMonth = j + 2 - (12 * l)
nYear = 100 * (n - 49) + i + l
End Sub

How to run different versions of OfficeWriter side-by-side

Problem

You have existing applications using an older version of ExcelWriter or WordWriter. You want to use the latest version of the product for new application development without affecting existing applications. Is this possible?

Solution

Yes, if you are using the .NET version of OfficeWriter, it is easy to run different versions side-by-side (in different applications). You just need to be aware of considerations regarding the automatic installer and the product license keys.

Note: If you are using OfficeWriter’s SSRS integration, a single instance of Reporting Services can only have one version of OfficeWriter installed. However, you may use the newer version in other standalone applications.

Installation

Only one version of OfficeWriter can be installed with the automatic installer. The usual upgrade process is to first remove the previous version through Add/Remove programs and then run the new installer. Therefore, in order to use two versions side-by-side, at least one of the installations will need to be manual.

License Keys

When your company purchased OfficeWriter, the main contact for your order received license keys for the product which must be installed on each server. These keys can be entered either through the automatic installer or by using our LicenseManager.exe utility (which is unpacked to the OfficeWriter Program Folder).

Every major version of OfficeWriter requires a new set of license keys. Minor upgrades can continue using the same license keys. If the new version you wish to install is a major upgrade from the previous version, you will need to enter a new license key.

Instructions

First, download the new installer package from the product updates page.

Assuming the previous version of the product was installed with the automatic installer, you now have two options:

  1. Do a normal automated upgrade and then reinstall the old license key manually.
  2. Leave the old installation untouched and do a manual upgrade.

Option 1:

  1. (optional) Back up the OfficeWriter Program folder from the previous version.
  2. Uninstall OfficeWriter from Add/Remove Programs. This will not affect the DLLs in your applications.
  3. Run the new installer. Make sure to use the correct license key for the new version.
  4. If this was a major upgrade, the new license key will not work for the older version so you need to reinstall the older license key

To reinstall the older license key:

  1. Double-click on the LicenseManager.exe utility in the program folder.
  2. Enter the older key in the first textbox and click “Add/Upgrade.” Exit the application.

Option 2:

  1. Do NOT uninstall the previous version.
  2. Unpack the files from the new installer by running it on a different machine. (Note: If the other machine does not have a license for OfficeWriter, please uninstall the product when you are through with this process)
  3. Copy the OfficeWriter DLLs to the destination server from the bin directory in the OfficeWriter Program folder.

If this is a major upgrade:

  1. Copy the new LicenseManager.exe from the OfficeWriter program folder. The older version of this utility that you may have on your server will not be able to accept the license keys for the new major version of OfficeWriter.
  2. Double-click on the .exe. It will show you which keys are already installed.
  3. Enter the new key in the first textbox and click “Add/Upgrade.” Exit the application.

You are now ready to use the new version of OfficeWriter in any application.

What are the system requirements for OfficeWriter?

The system requirements for OfficeWriter are outlined in our documentation for the two major versions that are currently supported:

OfficeWriter 8

OfficeWriter 8 Documentation Home

OfficeWriter 4

OfficeWriter 4 Documentation Home

Grouping and Nesting vs. PivotTables in ExcelTemplate

Although PivotTables and ExcelWriter’s Grouping and Nesting can produce similar looking results when used with ExcelTemplate, these options provide different functionality. This post outlines the pros and cons of using both options.

Pivot Tables

Pivot tables are a native Excel feature that are designed to sort and filter aggregate data. You can use them to transpose and combine data to present grouped views.

Useful Features

  • Group data – This can be done horizontally in row groups or vertically in column groups.
  • Sort data – Pivot tables automatically sort and group data.
  • Produce grouped calculations – i.e. the sum over all of the sales for a region, or the larger expense for a particular quarter
  • Transpose data

Limitations (when working with ExcelWriter)

  • You cannot pre-select filters, create groupings by hand etc. – Any feature that requires data to be in the pivot table cannot be selected in the template because the data won’t be imported until after the report is generated
  • Pivot tables have limited style options. Pivot tables can have table styles, but creating custom headers/footers for groups is challenging
  • You cannot put anything to the right or below the pivot table. This is because when the pivot table is populated, it will expand to the right and down. It will overwrite anything that was in those cells. For this reason it is recommended that you do not have multiple pivot tables on a worksheet.
  • Pivot tables do not allow for text in the data values area of the table. Text is only allowed in the report filters, row labels, or column labels.

PivotTables in OfficeWriter Docs:

Grouping and Nesting

ExcelWriter Grouping and Nesting allows you to format grouped data in OOXML (XLSX, XLSM) files using ExcelTemplate.

Features

  • Custom formatting – You have the ability to create multi-row headers and footers that contain whatever formulas, formatting, and content you’d like.
  • ‘Push down’ behavior – ExcelTemplate automatically pushes down rows when inserting data. This means that anything below a grouping block will be retained in the final report.
  • Accepts any data type – There are no limitations on the type of data that be represented (unlike pivot tables which only allow non-numerical data in the row/column labels).

Limitations

  • Data must be sorted ahead of time, otherwise Grouping and Nesting will not work properly (see information below on how to create Grouped and Nested reports)
  • Unable to group over columns

Grouping and Nesting in OfficeWriter Docs:

How to change strings to formatted numerical values

Problem

In the creation of an Excel workbook, sometimes a column contains numerical values stored as strings. For example, a percent column can display percents as strings. This poses a problem if you want to use these values for equations, or if you want to clean up the display of the file, as Excel will mark these strings with a green corner indicating that they can be displayed as numerals, along with a warning message: “The number in this cell is stored as text or preceded by an apostrophe.”

Solution

ExcelApplication allows you to change the NumberFormat of a column so that it will automatically format numerals. This alone is not enough to change the strings into formatted numerals, though, as you must trim any characters off the strings and convert them to doubles to be handled correctly by Excel.

Hence, converting the values for an entire column requires three steps:

  1. Gather the values from the column into a collection and convert them to numeric values.
  2. Apply the desired Number Format to the styles of column.
  3. Cycle through the collection and add the values back to each cell of the column.

For a code example of the three steps outlined above, please see the sample in our documentation for how to Convert strings to formatted numerical values.

Can a web application send additional information after streaming a file to the browser?

Problem

Your web application uses OfficeWriter to generate and stream a document or spreadsheet to the browser. Subsequently, you want to send additional instructions to the browser to update the web page, display a pop-up confirmation message, or re-direct to another web page.

Solution

It is not possible for a web application to stream a file to the browser and subsequently send additional information in the same response. This limitation is a consequence of the request-response nature of web programming and is not specific to OfficeWriter. The same limitation applies to any web application which performs media streaming or file downloading.

The HTTP protocol mandates that there can be only one response to each request. A response consists of HTTP headers and content. The HTTP headers provides information about the file and optionally specifies how it should be handled. In the case of file downloading, the response’s content must contain only file data. If there are non-file data in the response, such as HTML code to update the web page or perform additional actions, the foreign data would be taken to be part of the file, causing the file to be corrupted.

After a web application streams a file to the browser using OfficeWriter’s Save method, attempting to redirect to another web page may generate the following error: “Cannot redirect after HTTP headers have been sent.” This is because OfficeWriter’s Save method sets the appropriate HTTP headers before streaming the file and the instruction to redirect requires another set of HTTP headers.

How to import data into a Word document with WordWriter

Solution

WordWriter has two approaches for importing data:

Approach 1: WordTemplate

Use WordTemplate to import data into existing templates with merge fields.

Additional reading:

Approach 2: WordApplication

Call ImportData to generate Table in the document with the data.

Additional reading:

How to change a column of URLs imported with ExcelTemplate into hyperlinks

Problem

Sometimes your application requires creating a column of hyperlinks from URLs stored as strings. When ExcelTemplate populates a data marker with these strings, they remain static.

Solution

There are two ways to turn a column of strings into a column of hyperlinks:

  1. Fill a hidden column in the template with the URLs and use Excel’s HYPERLINK function to build the hyperlinks.
  2. Open the populated spreadsheet with the ExcelApplication object and use Cell.CreateHyperlink to turn the cell values into links.

Using the HYPERLINK Function

In this approach you can create a formula in a column in your worksheet that will reference another column that holds the URLs. To do this, place the datamarker for your URLs in an unused column on the same row in the same worksheet as the column that will contain the hyperlinks. For example, if you want the hyperlinks to begin in cell C5, place the data marker in row five of some other column, for instance AA5.

Now use Excel’s HYPERLINK() formula in C5 to reference the datamarker. Set C5’s formula to “=HYPERLINK(AA5)”, and as the datamarker in AA5 gets filled with URLs and stretches downward, the formula in C5 that references it will also stretch. If you don’t want the readers of the spreadsheet to be able to see the column of raw URLs, you can set it to hidden. In Excel, just right-click on a column header (the letter at the top) and choose ‘Hide’ from the drop-down menu. Now the column will not be visible, though it can be unhidden.

This method can be faster than using the ExcelApplication method (below), since it doesn’t require opening the file with ExcelApplication, but it has the disadvantage of leaving a column full of raw data in the worksheet.

Using ExcelApplication

After filling a column with URLs, you can open the workbook with the ExcelApplication object and convert the strings to hyperlinks. To iterate over a column of URLs and turn them all to hyperlinks, do something like this:


ExcelTemplate xlt = new ExcelTemplate();
xlt.Open("C:\\path_to_template\\template.xls");


// ... Data binding code ...


// Populate the spreadsheet and open it with ExcelApplication
xlt.Process();
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open(xlt);
Worksheet ws = wb["Sheet With URLS"];


// Define the area to convert to hyperlinks
Area URLArea = ws.CreateArea("C3:C90");
for (int i = 0; i < URLArea.RowCount; i++)
{
// Convert each cell to a hyperlink pointing to its URL
Cell c = URLArea[0, i];
c.CreateHyperlink(c.Value);
}

This will create hyperlinks in every cell in that Area using the value of the cell as the URL. Each cell will display the URL as the link text. If you want the cells to show some other text, change c.Value after the call to CreateHyperlink.

Keep in mind when using this technique that opening large files with the ExcelApplication object can be memory-intensive as it needs to create objects for each filled cell.

How to preserve leading zeros with ExcelWriter

Problem

Importing numbers with leadings zeros, such as zip codes and social security numbers, can pose a challenge. While these numbers are most often stored in a database as strings, they can be imported into Excel as either numbers or strings. Importing as numbers is the preferred option, as importing as strings will cause Excel to display a warning message that a number is being displayed as text.

Solution

When importing numbers with leading zeros, you have two options:

  1. Import or insert the values as numbers and not as strings. Set the appropriate special format as needed to display the desired number of leading zeros. This is the recommended practice.
  2. Import or insert the values as strings. Format the cells as General or Text. Excel will display a warning on each cell that a number is displayed as text. However, the cell values will contain the leading zeros.

Option 1: Import numbers with leading zeros as numbers (Recommended)

As mentioned above, the preferred option for importing numbers with leading zeros as numbers. Leading zeros will be stripped off, as numeric data types cannot contain leading zeros. To display the desired number of leading zeros within Excel on these numeric values, the appropriate formatting string should be set on the cells which contain the data.

It should be noted that a limitation to this approach is that when a value is read out of a cell there will be no leading zeros. If you would prefer to have leading zeros stored in the cell values (and have Excel display the warning message instead), you should use Option 2 as described below.

ExcelTemplate

When using ExcelTemplate, you have three options for converting your data from strings to an appropriate numeric data type. You can:

  • Let ExcelTemplate attempt to globally convert all strings to numeric values where appropriate. (This is the default behavior.)
  • Convert the applicable zip code values (and any other numbers) to numeric data types through native features of the language. Then set the PreserveStrings property to true to force ExcelTemplate to preserve all strings as strings. This approach will result in the most predictable results, since setting PreserveStrings to true will prevent ExcelWriter from performing string to numeric data type conversions.
  • Use a combination of Preserve and/or Convert data marker modifiers on specific columns. Which modifiers you use should depends on how PreserveStrings is set. If you choose this option, the recommended practice is to set PreserveStrings to true and use Convert data marker modifiers on zip code columns. For more info about data marker modifiers, please refer to the Creating Data Markers documentation.

Then set the appropriate cell formats on the template for the columns which will contain these values. Use the Zip Code or Social Security formats (listed under the Special category), or use your own custom string.

ExcelApplication

With ExcelApplication, no automatic string to number conversion exists. Therefore, you must convert the applicable zip code data using native features of the language. Set the appropriate cell formats on the existing file, if there is one, or use the ExcelApplication API to set it on an AreaRange, or individual Cell.

When setting the formatting through the ExcelApplication API, you should use the Style.NumberFormat property. For example, to set a zip code format on a cell, you would do:

 myCell.Style.NumberFormat = NumberFormat.Special_EN_US.ZipCode; 

In the above example, the NumberFormat.SpecialENUS.ZipCode constant is equivalent to “00000”. This forces Excel to always display five digits, prepending zeros to the number as necessary.

Option 2: Import numbers with leading zeros as strings

As mentioned above, you can import numbers with leading zeros as strings instead of converting them to numbers. However, Excel will display a warning that a number is displayed as text. This will allow you store the leading zeros in the cell values, preventing the need to set a special cell format, and also allowing the value to be read from the cell so that it includes the leading zero.

ExcelTemplate

To preserve strings using ExcelTemplate, use a combination of the following techniques:

  • Set the global PreserveStrings property to true.
  • Use a combination of Preserve and/or Convert data marker modifiers on specific columns. Which modifiers you use depends on how PreserveStrings is set.

Set the applicable cell formats to General or Text in the template.

For more information about using ExcelTemplate to convert strings to numbers where appropriate, please see this post: How to preserve strings with ExcelTemplate.

ExcelApplication

To preserve string values with ExcelApplication, no action is needed. ExcelApplication will always preserve strings, because it provides no automatic conversion functionality.

Set the appropriate cell formats to General or Text, either on the template or using the ExcelApplication API. The following example sets a cell format to Text:

 myCell.Style.NumberFormat = "@";

Blogged