Excel Transformation

– Excel Control function for column width and line height by users –

It's a function to adjust the column width and the cell height from outside the program. By specifying the scaling ratio, it's possible to adjust the range of the cell to fit into 1 page.

By specifying the key information saved in the Excel file and the adjustment value to the key, it is made available to make adjustment to the file by users. It's also possible to write and read multiple settings in a file as a parameter set and read. It's also possible to adjust in block without using the key.

The key information means the default font and the printer name saved in the Excel file. The default font is a font set to the cell when creating a new Excel sheet, which also is a base when calculating the table header or column width. One default font is set to a Excel workbook (file). Printer name indicates the printer used for printing set to each sheet.

The target default font and printer name are the one saved in the Excel file respectively. Note that a printer name sometimes isn't preserved. Even when not preserved, the printer name is displayed when opening the printer-related menu in Excel. This is not the printer name saved in the file but the name set in the system.

The size of the column width fluctuates by the difference of the default fonts. By changing the printer, the size may differ, so additional keys are adopted. But there aren't so much situation to use a printer name. By specifying the scaling ratio to the key, the cell included in the page can be adjusted. In addition, we offer the feature to return the default font and printer name as the information when loading. The information can be obtained in the same format as the error message, so you can refer to it when specifying the key.

Prepare the file with the following description and pass it to the program. You can adjust the line height and column width by specifying the adjustment value when converting the file with the key specified.

  <xls-rowcol-controls>

    <xls-rowcol-control>

      <xls-key>

        <printer-name>Printer Name</printer-name>

        <font name="Font Name" size="Font Size"/>

      </xls-key>

      <xls-key> ... </xls-key>

        :

        <xls-parameter row="Line height adjustment value" col="Column width adjustment value"/>

    </xls-rowcol-control>

  </xls-rowcol-controls>

DTD

<!ELEMENT xls-rowcol-controls (xls-rowcol-control*)>

<!ELEMENT xls-rowcol-control (xls-key*,

                              xls-parameter? ) >

<!ELEMENT xls-key (printer-name?,

                   font? ) >

<!ELEMENT printer-name (#PCDATA) >

<!ELEMENT font EMPTY >

<!ATTLIST font name CDATA #IMPLIED

               size CDATA #IMPLIED >

<!ELEMENT xls-parameter EMPTY >

<!ATTLIST xls-parameter row CDATA #IMPLIED

                        col CDATA #IMPLIED >

Parameter description

• xls-rowcol-control : 1 set of the adjustment value. Multiple values can be written in a file. They are verified in description order.
• xls-key : Specifies the key to decide the target element (record) written in a file.
  • Multiple values can be written in xls-rowcol-control. They are verified in description order.
  • The adjustment value is adopted when it matches the target element (record) described in the file.
• printer-name : Printer name (possible to specify the wild card). It's compared with the printer name in the printer information stored in the file.
  • Example : "EPSON LP-S310" (Includes the registered product name), "EPSON *" (General EPSON Printers)
• font : Font information. It's compared with the default font information stored in the file.
  • name : Font name (possible to specify the wild card). It's compared with the default font name.
    Example : "IPA*" (Font beginning with IPA), "MS P Gothic"
  • size : Font size (enumerated values by the delimiter ',', range specification by '-' are available). It's compared with the default font size. (in units of point)
    Example : 8 (8pt), 10-15 (from 10pt to 15pt), 8,10 (8pt and 10pt), -10 (equal to or less than 10pt), 20- (equal to or greater than 20pt), 8, 14- (8pt and equal to or greater than 14pt)
• xls-parameter : A set of adjustment value. only one can be written in xls-rowcol-control.
  • row : line height adjustment value : The scaling ration with % is given. (90%)
  • col : column width adjustment value : The scaling ration with % is given. (90%)

Usage Note

• Multiple xsl-key can be specified to one xls-rowcol-control. Even if one matches, it will be adopted.
• xls-rowcol-control is detected from the head in sequence. The detection ends when the matched set is found.
  • If the matched set is not found, (including the case where there is no xls-row-control) the processing will be performed without using the user's adjustment.
  • When the xlsinfoview option is specified, a message saying there is no matched set will be displayed.
• If the setting by xls-key is omitted, the key is considered that its condition matches when detecting.
  • When there is no settings to the key at all, it is considered that their conditions match in all files and the following parameter settings will be effective. If such a key is written at the beginning, the latter settings are all ignored.
• When the setting is omitted in xls-parameter, the omitted items are considered that 100% is specified.
  • When there is no settings to the items at all, it is considered that 100% is specified as the parameter value, the adjustment by user won't be done.
  • Minimum/maximum values of row, col : 50% - 150%
  • Values with % can be specified. It's also possible to specify a decimal point, such as 82.5%, up to 2 digits.
• The item with no settings in font is considered the condition matches.
  • When there are no settings for both name and size, it's considered that they match in all fonts.
  • Maximum size of font name : Conforms to Office specification. 31 characters.
  • Maximum/minimum size of font size : Conforms to Office specification. 1-409.

Example of programing

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE xls-rowcol-controls SYSTEM "xls-rowcol-controls.dtd">

  <xls-rowcol-controls>

    <xls-rowcol-control>

      <xls-key> <font size="10"/> </xls-key>

      <xls-key> <font size="8-9" name="MS Gothic"/> </xls-key>

      <xls-parameter col="95%" row="82.5%"/>

    </xls-rowcol-control>

    <xls-rowcol-control>

      <xls-parameter col="90%"/>

    </xls-rowcol-control>

  </xls-rowcol-controls>

– Excel function for paper setting by users –

The function to specify the paper settings from outside the program. By specifying the paper size or page orientation for printing, you can get the output differently from the setting of the file.

They are both possible to specify the default value when the paper settings are not saved in the Excel file, and to use the specified value by ignoring the paper setting saved in the Excel file.

The available parameters are paper, paper orientation, top/bottom right/left margins and scaling ratio.

The value first found is adopted in the following order of priority. The setting is independent for every parameter.

Priority order: specified value by user (xlspaper) > specified value saved in the file > specified default value by user (xlspaperdefault) > default value embedded in the program

Parameters for elements can be specified by comma-delimited character strings. The necessary values belongs to the each element can be specified by putting the colon to separate them.

Parameters are interpreted in order from the beginning. When there is a duplicate element specified, the first specified one will be effective.

Parameter description

• Paper size : Specifies the paper size.
  • A1, A2, A3, A4, A5, B3, B4, B5, Letter, Legal, custom:SIZEWIDTH:SIZEHEIGHT can be specified.
  • Using custom, you can specify any size for the width and height (SIZEWIDTH,SIZEHEIGHT).
  • Add the unit of mm or inch after the numeric characters. If the unit is omitted, it is considered mm. It's necessary to specify both width and height. If not, an error will occur.
  • The default is A4.
• Paper orientation : Specify the paper orientation.
  • Specify landscape or portrait by landscape,portrait.
  • By setting graph:,sheet: in front, the graphsheet and the worksheet can be specified separately.
  • If graph:,sheet: is not added, the setting is effective for both sheets.
  • The default for worksheet is portrait, the default for graphsheet is landscape.
  • When custom is specified as the paper size, this setting is not effective.
• Scale : Specify the scaling ratio.
  • Specify % or number of pages in tall and wide direction.
  • If you use % specification, add % after the numerical character. e.g. 100%, 50%, 200%
  • If you use the number of pages for tall and wide direction, the entire sheet will be scaled to fit into the specified number of pages.
  • Number of pages can be specified by page:HORIZONTAL:VERTICAL. Specify number of pages in tall and wide direction. If 0 is specified for the one, the other one that is not 0 will be a base for determining the scaling ratio.
  • The default is 100%.
  • The minimum reduction ratio is 10%. The minimum value can be limited to prevent it from becoming too small. The default minimum value limit is 10%, and the value can be set from 10% to 100%. The value is specified by limit:SCALELIMIT, where SCALELIMIT is written in %. e.g. limit:10%, limit:60%, limit:100%
• Margin : Specify the top/bottom right/left margins of a paper.
  • Using margin, any size of the top/bottom right/left margins (MARGINLEFT,MARGINTOP,MARGINRIGHT,MARGINBOTTOM) can be specified.
  • Add the unit of mm or inch after the numeric characters. If the unit is omitted, it is considered mm. It's necessary to specify all 4 numeric values. If not, an error will occur.
  • The default is margin:20mm:25mm:20mm:25mm.

Example of programing

-xlspaper A4,graph:landscape,100%

The paper size is A4, graphsheet is in horizontal mode, the scaling ratio is 100%, use the margin specified to the file.

-xlspaperdefault custom:1000mm:1200mm,page:2:1,margin:10mm:10mm:15mm:15mm

The paper size is width 1000mm, height 1200mm, The margin for left/top is 10mm, right/bottom is 15mm, scale the sheets so that the number of pages in wide direction is within 2 and the number of pages in tall direction is within 1 page. This setting use the default value. If the xlspaper option is specified at the same time or the settings are saved in the file, the default will not be adopted.