Category Archives: Uncategorized

Uncategorized

How to Use PageSetup Options When Saving to a PDF Document

When saving Excel worksheets to a PDF document using the PDF rendering extension methods introduced in OfficeWriter 10.0, it is often useful to be able to specify details about the resulting PDF document, or about how the rendering should behave.  This is achieved by setting properties on the worksheet’s PageSetup property before calling the SavePdf method.  This tutorial will walk through that process.

Setting up your worksheet

For this example, we will open an existing workbook, in order to save the first worksheet of the workbook to a PDF document:

ExcelApplication xla = new ExcelApplication();

Workbook WB = xla.Open(“input.xlsx”);

Worksheet worksheetToSave = WB[0];

 Specifying page properties

 Using the worksheet’s PageSetup property, we can specify the page size, orientation, and margins.  These properties will be reflected in the final PDF document:

worksheetToSave.PageSetup.PaperSize = PageSetup.PagePaperSize.Legal;

worksheetToSave.PageSetup.Orientation = PageSetup.PageOrientation.Landscape;

worksheetToSave.PageSetup.TopMargin = 1.5; // Specified in inches

Setting a header and footer

The PageSetup property can also be used to set a header or footer.  The header and footer will be printed on each page of the PDF:

HeaderFooterSection.Section hfSection = HeaderFooterSection.Section.Center;

worksheetToSave.PageSetup.GetHeader(hfSection).SetContent(“Header text”);

Specifying rendering options

Other PageSetup properties are useful for specifying how the content should be rendered to the PDF document.  For example, we can make the content smaller than normal by using the Zoom property, specify the order in which pages should appear in the PDF document, and print all of the cell comments at the end of the document by setting the relevant properties on the worksheet:

worksheetToSave.PageSetup.Zoom = 50; // Specified as a percentage

worksheetToSave.PageSetup.UseZoom = true;

worksheetToSave.PageSetup.PrintOrder = PageSetup.PagePrintOrder.DownThenOver;

worksheetToSave.PageSetup.PrintComments = true;

worksheetToSave.PageSetup.PrintCommentsAtEnd = true;

There are additional PageSetup properties that may prove useful; for a complete list, see the PageSetup documentation.

Saving the PDF document

Once you have set all desired PageSetup options, you can then save the worksheet to a PDF document:

worksheetToSave.SavePdf(“output.pdf”);

If you want to save multiple PDF documents from the same workbook, but with different options set, you can repeat this process.  For example, after saving one PDF document, you could then change the PaperSize property to a different size of paper, and then save a second document.  The updated properties will be reflected in any subsequent calls to SavePdf.

When saving an entire workbook to a PDF, each worksheet is rendered using its own PageSetup properties.

ExcelWriter, Knowledge Base, OfficeWriter, Programming, Uncategorized

How to Calculate Unsupported or Custom Formulas on the Server with ExcelWriter

Beginning in OfficeWriter 9.1, ExcelApplication’s calculation engine will offer the ability to implement custom formulas. This feature should prove helpful to users whom would like to calculate their own custom formulas or calculate formulas not currently supported by ExcelWriter on the server using ExcelWriter. This tutorial will show you how to implement these formulas.

Creating Your Own Formula

  1. Create a class that implements an interface provided by ExcelWriter called IFunction. The IFunction interface implements a method called Calculate.
  2. Inside of your class, create the Calculate method with this signature: 
    FunctionValue Calculate(IList<FunctionValue> args, Cell currentCell)
  3. Inside of the Calculate Function, code the logic of the formula you would like to implement.
    1. The first argument of Calculate is a list of FunctionValue objects. FunctionValue objects are how we pass values in and out of formulas. FunctionValues have several properties available to them, so please see the documentation for more information about how you can use the properties in your formula logic.
    2. The second argument of Calculate is the cell that contains the formula. Please see the documentation for more information about Cell objects and their available properties and methods.

Registering the Formula with ExcelWriter

  1. Once the formula is written, register the function using Workbook.RegisterCustomFunction. 
    WB.RegisterCustomFunction("MyFormula", new MyClass());

Calculating the Formula and Removing it From a Workbook

Once your formula is registered, you can use WB.CalculateFormulas to have ExcelWriter calculate the value of any cells that use the formula in your Workbook. If you are generating Excel output using ExcelApplication.Save, please note that if you have implemented a custom formula that it is not recognized by Microsoft Excel, the end user will not see the calculated values in the output workbook when opened in Excel. To get around this issue, you can use Workbook.RemoveFormulas or Worksheet.RemoveFormulas to remove the formulas from the worksheet or workbook, while leaving the last calculated values in the cells.

Here is an example of what your finished code should look like:

public class sample
{
   MyMainMethod()
   {
      ExcelApplication xla = new    ExcelApplication(ExcelApplication.FileFormat.Xlsx);
      Workbook WB = xla.Create(ExcelApplication.FileFormat.Xlsx);
      WB[0]["A1"].Formula = "=COUNTARGUMENTS(4, 5, 6)";
      WB.RegisterCustomFunction("COUNTARGUMENTS", new Formula());
      WB.CalculateFormulas();
      //Optionally remove all formulas from the workbook, so only values remain. This is good in case you are using a custom formula that Excel will not be able to calculate.
      WB.RemoveFormulas();
      xla.Save("output.xlsx");
   }
}

class Formula : IFunction
{
   public FunctionValue Calculate(IList<FunctionValue> args, Cell cell)
   {
      //Returns the number of arguments
      return new FunctionValue(args.Count);
   }
}

Knowledge Base, OfficeWriter, Uncategorized

Saving Your Report’s Data in CSV Format

If you have ever filed a support incident with SoftArtisans Technical Services concerning your OfficeWriter reports, you know that one of the most important steps in resolving an issue is having a Technical Services Engineer reproduce it. While there are many components to reproducing a customer issue, one of the most critical aspects is having sample data to run the affected report with. Unfortunately, this can be a special challenge because a Technical Services Engineer does not have access to your data sources. The inability to run the report can delay or even halt Technical Service’s ability to troubleshoot an issue. However, do not fret, as CSV files can save the day!

Steps to Save Your Report Data as a CSV File

  1. Download the attached template Collecting_Data_For_SA. The template will allow you to gather data for up to 10 datasources with up to 35 columns each.
  2. Run this template through your application:
    1. If you have a custom web application that uses ExcelTemplate: run this template through your application.
    2. If you have a custom web application that uses  ExcelApplication or Word Application: run this template through your application using ExcelTemplate with code that looks something like this: 
      ExcelTemplate xlt= new ExcelTemplate();

//Open the collecting_data_for_sa template
xlt.Open("collecting_data_for_SA.xlsx"));

//Bind each of your datasources to the template
xlt.BindData([YOUR DATA SOURCE], "Something", xlt.CreateDataBindingProperties());
xlt.BindData([YOUR DATA SOURCE2], "Something2", xlt.CreateDataBindingProperties());
xlt.BindData([YOUR DATA SOURCE3], "Something3", xlt.CreateDataBindingProperties());

xlt.Process();
xlt.Save("data_collected.xlsx");
    3. If you are using SSRS integration:
      1. Create a copy of your affected RDL.
      2. Open the copy RDL in the OfficeWriter Designer or OfficeWriter Designer .NET for Microsoft Excel.
      3. Click “Import Template” in the Designer tab and overwrite your existing template with the collecting_data_for_sa file.
      4. The copy rdl should now contain the collecting_data_for_sa template.
      5. Upload the rdl to SSRS and run the report.
  3. Send the results to SoftArtisans Technical Services

Knowledge Base, OfficeWriter, Uncategorized, WordWriter

How to Avoid Extra Page at Document End When Using WordTemplate

When you want part of your WordTemplate document to repeat on every page, you must set a PageBreak on that page. However, if you set a default PageBreak, Word will automatically insert what is called a Page Break After. The Page Break After  will result in an extra page at the end of your document. In order to fix this, you will need to set a Page Break Before  at the beginning of the page you want to repeat.

Instructions on Setting a Page Break Before

  1. Click on the top left corner of the page where you want your page break to occur.
  2. In the “Home” tab, there should be a “Paragraph” section. Click the arrow on the bottom-right of the Paragraph Section.
    Screenshot 2014-06-20 11.30.19
  3. Go to the “Line and Page Breaks” tab and check the “Page Break Before” option.
    Screenshot 2014-06-20 11.35.51
  4. Click “Ok”.

Word will have now inserted a PageBreak before at the spot where you cursor lay in the document. Your document should now generate the correct number of pages.

ExcelWriter, Knowledge Base, OfficeWriter, PowerPointWriter, Programming, Reporting Services, Uncategorized, WordWriter

How to Send OfficeWriter Output in an E-mail

Sending OfficeWriter output in an e-mail is possible while either using OfficeWriter in .NET or with SSRS integration. When using the OfficeWriter .NET API, you can use the .NET MailMessage class to send your OfficeWriter output as an attachment in an e-mail. In SSRS, you can send the output as a subscription-based e-mail.

Sending OfficeWriter Output Using the .NET MailMessage Class

In order to send OfficeWriter in an e-mail output using .NET, you need to use the MailMessage object. If you save the workbook,  document, or presentation to a stream, you can create an attachment out of the file in the stream. This attachment can be added to the MailMessage object and sent to the workbook’s or document’s end users.

//Open Word Template
WordTemplate wt= new WordTemplate();

//Do some databinding and processing here
//YOUR CODE HERE

//Create a file stream that will save the output file on the server
FileStream myStream = new FileStream(@"C:\temp\outfile.xlsx", FileMode.Create);

//Save the output to the stream
wt.Save(myStream);

//Create an Attachment of the file in the stream
Attachment myAttachment= new Attachment(myStream, "output.xlsx");

//Create a mailmessage obj
MailMessage mailObj = new MailMessage("From Address", "To Address", "Subject", "Body");

//Add the attachment
mailObj.Attachments.Add(myAttachment);

//Connect to the server that will send the email
SmtpClient SMTPServer = new SmtpClient("thenameofyourhost");

//Set credentials
SMTPServer.UseDefaultCredentials = true;

//Try sending
try
{
    SMTPServer.Send(mailObj);
}
//Basic error handling
catch (Exception ex)
{
    Response.Write(ex.ToString());
}

Sending OfficeWriter Output Using a SSRS Subscription

If you are integrating OfficeWriter with SSRS, it also possible to send OfficeWriter output by using subscriptions. Be aware that the credentials to run the report must be stored inside of the report.

  1. After uploading your report to the SSRS server, open Report Manager
  2. Find your report and from the Drop Down, select “Manage”
  3. In the left-hand menu, select “Subscriptions” and then “New Subscription”
  4. Fill out the form, including:
    1. Who you want to send the report to
    2. When you want to send it
    3. What parameters the report should run with
    4. Be sure to select either “Excel designed by OfficeWriter” or “Word designed by OfficeWriter” as the rendering format.
      RenderFormat
  5. Click “Ok”
  6. Your new subscription should now appear in the Subscriptions list for your report and will run at the time you specified.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, Uncategorized

OutOfMemory Exceptions with Large Excel Reports

At times customers have experienced OutOfMemory exceptions when generating very large Excel reports with OfficeWriter. Generating reports with millions of cells will necessarily use a significant amount of memory, especially when using the ExcelApplication object. Note that ExcelWriter is not just keeping a file in memory, it is populating an entire object model in order to be able to manipulate the file. For example, each cell has associated objects for the value, formula, formatting, and more. The ExcelTemplate object (which is also used behind the scenes in our Reporting Services integration) has a smaller object model, so it uses less memory than ExcelApplication. However, a very large report can still require a significant amount of memory with ExcelTemplate.

This article provides tips about how to avoid memory issues when generating large reports.

Try to use the latest version of OfficeWriter
Make sure your application is compiled as 64-bit when possible
Use the ExcelApplication API in the most efficient manner
Cache reports if possible
Queue reports if necessary

Try to use the latest version of OfficeWriter

Many performance improvements have been implemented in ExcelWriter over time. To take advantage of these optimizations, upgrade to the latest version when possible. See the OfficeWriter Change Log for details about changes in every release since OfficeWriter version 4.0 (ExcelWriter version 7.0).

Make sure your application is compiled as 64-bit if possible

A 32-bit application will have never have more than 2 GB of memory available to it, even you are running the application on a 64-bit OS with a huge amount of RAM. The pure .NET ExcelWriter objects (in the namespace SoftArtisans.OfficeWriter.ExcelWriter) are fully 64-bit compatible (see Using OfficeWriter .NET on a 64-bit machine). If you are using the pure .NET objects and running on a 64-bit machine, make sure your project is compiled as a 64-bit application. If you have an ASP.NET application that was written against a very old version of ExcelWriter, it may have dependencies on ExcelWriter COM which is 32-bit, in which case compiling for 64-bit will not be an option (see this KB article for more information)

Use the ExcelApplication API in the most efficient manner

There are a number of steps that will improve performance when working with large reports with the ExcelApplication API (For more details, see Best Practices with Large Reports) :

  • Populate data with ExcelTemplate, and use ExcelApplication beforehand for any necessary runtime file manipulations. ExcelTemplate is the most efficient way to import data but it cannot make fine-grained changes to the workbook. If you need to modify the workbook at runtime, modify the template programmatically before passing it to ExcelTemplate to avoid having to open a fully populated report with ExcelApplication. See Preprocessing vs. Postprocesssing.
  • Avoid referencing Empty Cells. Any time you touch an empty cell with ExcelApplication, a Cell object (and all its associated objects) is created even if it didn’t already exist. If you need to loop through cells to look for something, used Worksheet.PopulatedCells to get an Area containing only populated cells. Note that PopulatedCells will return cells that have only formatting and no data. For this reason is it important to apply formatting in the most efficient manner. More information about this below.
  • Apply Styles to Columns and Rows, not Cells. Setting styles on a cell-by-cell basis or applying a style to an area, causes a separate Style object to be created for every Cell. On the other hand, if you Set a Style on a column or row, using ColumnProperties.Style or RowProperties.Style, there will only be one formatting record for the entire column or row. Currently there is no option to set conditional formatting at the column or row level with the ExcelWriter API. A workaround is to set the conditional formatting on the columns or rows in your input file, and ExcelWriter will preserve it.

Cache reports if possible

If you have a report that requires a lot of memory and is requested by many concurrent users, investigate whether it might be possible to keep a cached copy of the report and serve it to multiple users. Does the data change constantly or only at certain intervals? Are there parameters that tend to be different for every user or do multiple users run the report with the same parameters? If you have multiple users requesting a report with the same data and the same parameters, this report could be a good candidate for caching. You could run the report at a certain time with a background process, or you could cache a copy the first time any user generates the report within a certain timeframe.

If you are using OfficeWriter in a custom .NET application, you would implement caching in your own code. If you are using OfficeWriter in SSRS-integration mode, you can use SSRS’s built-in caching functionality.

Queue reports if necessary

If you have a report being accessed by multiple users that requires a great deal of memory in a memory-constrained environment (i.e. your server is 32-bit or you have to compile your application as 32-bit for some reason), and caching isn’t an option (i.e. every time a user runs the report it is different), then queuing reports may be something to consider. Instead of delivering every report on-demand, you can restructure your application to store the users’ requests, process them sequentially in a background process, and notify the users by email or some other means when their reports are ready. This approach should not cause your users to have to wait much longer for a report than if it was being generated on-demand, but of course the user experience will be a little different. One option is to implement logic in your application to only queue very large reports but deliver smaller reports on-demand.

If you are using OfficeWriter in SSRS-integration mode, you can accomplish queuing by using SSRS subscriptions.

ExcelWriter, Knowledge Base, OfficeWriter, Reporting Services, SSRS, Uncategorized, WordWriter

Enabling ExcelTemplate and WordTemplate Properties for SSRS

OfficeWriter SSRS Integration allows users to generate OfficeWriter output from RDLs in Report Manager. To enhance the level of customization that SSRS integration provides, users are able to set ExcelTemplate and WordTemplate properties by adding keys to specific configuration files. This article will describe how to enable supported ExcelTemplate and WordTemplate properties in SSRS. Please note that these properties are global: they will apply to OfficeWriter output generated by every report in Report Manager. To see what ExcelTemplate properties are supported in SSRS, visit this link.

Generating Reports On-Demand

One possibility for generating reports from RDLs is to click on the RDL in report manager and export the RDL to ExcelWriter or WordWriter formats.

If you would like to use this functionality, you will need to add your property keys to:  C:\Program Files\Microsoft SQL Server\[SQL INSTANCE]\Reporting Services\ReportServer\web.config

SSRS Subscriptions

SSRS subscriptions allow reports to be generated from RDLs to a fileshare or delivered to users via e-mail on a schedule. However, it is important to note that SSRS subscriptions run in a different context and process from the rest of SSRS. The result is that you will need to add your property keys to a different configuration file for subscriptions than you will for generating reports on the fly.

If you would like to use this functionality, you will need to add your property keys to:  C:\Program Files\Microsoft SQL Server\[SQL INSTANCE]\Reporting Services\ReportServer\bin\ReportingServicesService.exe.config

Instructions for Adding Keys to A Configuration File

Here are the steps to enabling supported ExcelTemplate and WordTemplate properties to the above configuration files:

  1. Open the configuration file you would like to modify. See above for which configuration file will enable the functionality you desire. You can add properties for reports generated on-demand, by subscription, or both.
  2. Check to see if an appSettings section exists in the file: ie <appSettings></appSettings>
    1. If yes
      1. Add your keys to the existing appSettings section. The format is <add key=”[KEY NAME]” value=”[VALUE]” />
    2. If not
      1.  add an appSettings section before the closing </configuration> tag at the bottom of your file. It should look like: <appSettings></appSettings>
      2. Inside of the appSettings tags, add your keys in the <add key=”[KEY NAME]” value=”[VALUE]” /> format.

webconfig

This is an example appSettings section in a configuration file

Uncategorized

[Press Release] SoftArtisans Expands its Product Offerings with the Addition of PowerPointWriter

FOR IMMEDIATE RELEASE

SoftArtisans Expands its Product Offerings with the Addition of PowerPointWriter

SoftArtisans releases OfficeWriter version 9 and introduces a new tool named PowerPointWriter to their .NET API. PowerPointWriter is a template-based approach to generate dynamic PowerPoint presentations.

WATERTOWN, MA – February 26, 2014 – SoftArtisans, a leading developer of Office-based reporting software, announces the release of their newest product version, OfficeWriter version 9. Alongside their OfficeWriter version 9 release, SoftArtisans introduces a new tool called PowerPointWriter, thereby greatly expanding the product portfolio of their enterprise software.

PowerPointWriter is a template-based approach to generating reports. Unlike other tools on the market, PowerPointWriter uses a template model, making it easy for developers to design their reports within PowerPoint and merge them with data using as little as five lines of code. VP of Development, Sam Haddad, comments, “This has been a request from our customers for some time now. One of the things our customers always tell us is that they love our template-based approach to generating Office documents, and wanted the same ability with PowerPoint. So we did just that.” PowerPointWriter takes advantage of the technology behind Excel and Word’s mail merge feature, to make combining customized templates with company data easier.

Along with PowerPointWriter, SoftArtisans adds major developments to their ExcelWriter tool. The most notable of which, is a calculation engine, providing users the ability to calculate the results of formulas (such as SUM, PRODUCT, AVERAGE, VLOOKUP, etc.) in an Excel workbook through the OfficeWriter API. This allows users to view formula-laden Excel spreadsheets across many devices and viewers.

OfficeWriter has long been the trusted software for building Microsoft Excel and Word reports in .NET, without requiring Microsoft Office to be installed on the server. In addition to legacy binary file formats XLS and DOC, OfficeWriter supports OOXML files. With the addition of PowerPointWriter, SoftArtisans has expanded into PowerPoint file formats, including PPTX. As a trusted part of enterprise applications, OfficeWriter is easier to use than the OpenXML SDK, and, unlike automating Office on the server, is scalable and optimized for use in ASP.NET. The new product version is now available for download through their site at www.officewriter.com.

###

About SoftArtisans
SoftArtisans is a leading developer of Microsoft Office format reporting software. A key Microsoft partner, SoftArtisans delivers award-winning products to over 20,000 clients in more than 70 countries. SoftArtisans also offers a wide range of technical services that enable clients in industries ranging from finance to healthcare to government, to maximize their reporting abilities with OfficeWriter. Headquartered in Watertown, MA, SoftArtisans has been honored as one of North America’s 500 fastest-growing technology companies.

Contact:
Elise Kovi
elisek@softartisans.com

Uncategorized

[Press Release] SoftArtisans Receives 2013 Best of Watertown Award

Best of Watertown 2013

We are so honored (not to mention pumped) to be recognized in the Watertown Award Program this year. Thank you!

“The Watertown Award Program recognizes outstanding local businesses that serve their local market. Each year, we identify companies that we believe have achieved exceptional marketing success in their local community and business category. These are local companies that enhance the positive image of small business through service to their customers and community.“

Press Release

FOR IMMEDIATE RELEASE

Software Artisans Receives 2013 Best of Watertown Award

Watertown Award Program Honors the Achievement

WATERTOWN June 6, 2013 — Software Artisans has been selected for the 2013 Best of Watertown Award in the Retail Computer Software category by the Watertown Award Program.

Each year, the Watertown Award Program identifies companies that we believe have achieved exceptional marketing success in their local community and business category. These are local companies that enhance the positive image of small business through service to their customers and our community. These exceptional companies help make the Watertown area a great place to live, work and play.

Various sources of information were gathered and analyzed to choose the winners in each category. The 2013 Watertown Award Program focuses on quality, not quantity. Winners are determined based on the information gathered both internally by the Watertown Award Program and data provided by third parties.

About Watertown Award Program Continue reading [Press Release] SoftArtisans Receives 2013 Best of Watertown Award →

Truth in Tech, Uncategorized

This Week in Tech News

This week in tech news: Harvard medical school rakes in a cool $1 million to study NFL injuries and Foodspotting app is acquired for $10 million. But nothing beats the priceless price of putting your head on a PEZ dispenser.

Boston: Harvard Medical School Awarded 100mil by NFL Players Association to Study football injuries. The program’s goal is to “improve the health and well-being of NFL players, while further elucidating the risks of participation in American football.” – the research to improve players’ health will include studying concussions, treatment for ACL injuries, and heart function.

Continue reading This Week in Tech News →