Controlling Excel Conversion in Document Conversion Service

PEERNET Document Conversion Service is an off the shelf high volume solution for converting documents to high quality TIFF, JPEG, Adobe PDF, and other image formats. The Peernet Document Conversion Service Product is a very flexible, easy to navigate and robust conversion solution.

This article continues a series of articles highlighting the individual conversion options of each of the converters included with Document Conversion Service. A list of the previously posted articles is available at the end of this article.

Controlling the Excel Converter

The Excel converter has options to control the printing order of worksheets and charts, what data is shown, and what printing area, if any, is used.

You can control how the workbook is printed, including grid lines, comments and notes, margins, and headers and footers. You can also provide passwords to handle protected documents. A useful feature for e-Discovery applications is the ability to control what is printed in any auto date, time and file name fields in the document. A small sample of some of the available settings is listed below. A complete list of available settings can be found here.

Printing Options

Setting NameValues
Microsoft.Excel.PrintOutPrintOutWorkbookOnly(default) PrintOutChartsOnly PrintOutChartsThenWorkbook PrintOutWorkbookThenCharts
Microsoft.Excel.Worksheet.ShowAllDataFalse(default) True
Microsoft.Excel.PageSetup.OrderDownThenOver OverThenDown
Microsoft.Excel.PageSetup.PrintCommentsPrintNoComments(default) PrintSheetEnd PrintInPlace
Microsoft.Excel.PageSetup.PrintGridlinesTrue False

Document Protection

Setting NameValues
Microsoft.Excel.UnprotectPasswordA string value containing the password to use to unprotect the Excel document.
Microsof.Excel.OpenPasswordA string value containing the password to use to open a password-protected Excel document.
Microsoft.Excel.WritePasswordA string value containing the password to use to allow saving changes to the Excel document.

Field Replacement

Setting NameValues
Microsoft.Excel.ReplaceFieldDateWithThe string value to place in the field.
Microsoft.Excel.ReplaceFieldTimeWithThe string value to place in the field.
Microsoft.Excel.ReplaceFieldFileNameWithA string value to replace the auto file name field.

Telling Document Conversion Service about the Setting

How this setting is passed to Document Conversion Service depends on how you are using the service and its included conversion tools. The samples shown below will configure Excel spreadsheets to be printed with:

  • the charts first, then the spreadsheet with any comments visible,
  • gridlines displayed on the spreadsheet,
  • and all date fields replaced with the string “***DATE***”.

For the command line tools and the PEERNET.ConvertUtility .NET library, the conversion settings are normally passed into the commands by supplying the name of a conversion profile file. This file is a simple XML file stored on your computer.

Sample Profile – Excel Charts then Sheets, Show Gridlines, Comments, and Date Replacement.

<?xml version="1.0" encoding="utf-8"?>
<Profile Type="0"
         DisplayName="TIFF 200dpi Optimized Color"
         Description ="Excel Charts First with Gridlines, Comments, Date Replacement.">

  <Settings>

    <!--Excel Specific options -->
    <add Name="Microsoft.Excel.PrintOut" Value="PrintOutChartsThenWorkbook"/>
    <add Name="Microsoft.Excel.PageSetup.PrintGridlines" Value="True"/>
    <add Name="Microsoft.Excel.PageSetup.PrintComments" Value="PrintInPlace"/>
    <add Name="Microsoft.Excel.ReplaceFieldDateWith" Value="***DATE***"/>

    <!-- Output file options -->
    <add Name="Devmode settings;Resolution" Value="200"/>
    <add Name="Save;Output File Format" Value="TIFF Multipaged"/>
    <add Name="Save;Append" Value="0"/>
    <add Name="Save;Color reduction" Value="Optimal"/>
    <add Name="Save;Dithering method" Value="Halftone"/>

    <!-- TIFF Compression Options -->
    <add Name="TIFF File Format;BW compression" Value="Group4"/>
    <add Name="TIFF File Format;Color compression" Value="LZW RGB"/>
    <add Name="TIFF File Format;Indexed compression" Value="LZW"/>
    <add Name="TIFF File Format;Greyscale compression" Value="LZW"/>
    <add Name="JPEG File Format;Color compression" Value="Medium Quality"/>
    <add Name="JPEG File Format;Greyscale compression" Value="High Quality"/>
    <add Name="Image Options;Fax" Value="0"/>

  </Settings>
</Profile>

If you are using the Watch Folder service included with Document Conversion Service, the collection of settings is passed as part of the definition for each input folder and is used for every file dropped into that folder. By adding the following highlighted lines into the settings for the desired input folder, any Word documents dropped into that folder will use these settings when they are converted.

Sample Watch Folder Configuration – Excel Charts then Sheets, Gridlines, Comments, with Date Replacement

<WatchFolder Name="Excel Charts First with Gridlines, Comments, Date Replacement">
  <Settings>
    <!-- Folder options -->
    <add Name="InputFolder" Value="C:PEERNETWatchFolderInput"/>
    <add Name="SearchFilter" Value="*.*"/>
    <add Name="IncludeSubFolders" Value="True"/>
    <add Name="DeleteInputSubFolders" Value="True"/>
    <add Name="StagingFolder" Value="C:PEERNETWatchFolderStaging"/>
    <add Name="WorkingFolder" Value="C:PEERNET WatchFolderWorking"/>
    <add Name="FailedFolder" Value="C:PEERNETWatchFolderFailed"/>
    <add Name="CompletedFolder" Value="C:PEERNETWatchFolderCompleted"/>
    <add Name="OutputFolder" Value="C:PEERNETWatchFolderOutput"/>
    <add Name="PollingInterval" Value="15000"/>
    <add Name="DCOMComputerName" Value="localhost"/>
    <add Name="TestMode" Value="false"/>
    <add Name="NormalizeFilenames" Value="false"/>

    <!--Excel Specific options -->
    <add Name="Microsoft.Excel.PrintOut" Value="PrintOutChartsThenWorkbook"/>
    <add Name="Microsoft.Excel.PageSetup.PrintGridlines" Value="True"/>
    <add Name="Microsoft.Excel.PageSetup.PrintComments" Value="PrintInPlace"/>
    <add Name="Microsoft.Excel.ReplaceFieldDateWith" Value="***DATE***"/>

    <!-- Output file options -->
    <add Name="Devmode settings;Resolution" Value="300"/>
    <add Name="Save;Output File Format" Value="TIFF Multipaged"/>
    <add Name="Save;Append" Value="0"/>
    <add Name="Save;Color reduction" Value="Optimal"/>
    <add Name="Save;Dithering method" Value="Halftone"/>
    <add Name="Save;Remove filename extension" Value="0" />
    <add Name="TIFF File Format;BW compression" Value="Group4"/>
    <add Name="TIFF File Format;Color compression" Value="LZW RGB"/>
    <add Name="TIFF File Format;Indexed compression" Value="LZW"/>
    <add Name="TIFF File Format;Greyscale compression" Value="LZW"/>
    <add Name="JPEG File Format;Color compression" Value="Medium Quality"/>
    <add Name="JPEG File Format;Greyscale compression" Value="High Quality"/>
    <add Name="Image Options;Fill order" Value="MSB2LSB"/>
    <add Name="Image Options;Fax" Value="0"/>
    <add Name="Image Options;Fax Profile" Value="0"/>
    <add Name="Image Options;Fax Resolution" Value="4"/>
    <add Name="Processing;Rotate landscape" Value="0"/>
  </Settings>
</WatchFolder>  

Previous article in this series:

Read more about conversion settings in the previous blog entry Setting Conversion Settings in Document Conversion Service for an overview on conversion settings.