1C:Enterprise features a spreadsheet editor for creating print forms.
This section describes how to use this editor.
3.1. Understanding spreadsheet documents
1C:Enterprise spreadsheets look like typical spreadsheets, with rectangular cells that form rows and columns.
Each column and each row have a unique number. Columns and rows are numbered independently of each other. Numbering starts at 1 at the top left corner of a spreadsheet. Therefore, any cell may be identified by a row number and a column number.
You can name cells and cell ranges (rectangular groups of cells). Use the Names window or the property palette to assign cell names and manage named cells.
The topmost cell of a column is referred to as the column header. It contains the column number and you can use it to select a column or change column width. The leftmost cell of a row is referred to as the row header, you can use it to select a row or change row height.
A spreadsheet document can be generated by an application (as a result of a running report or data processor) or by a user. It can be saved to a file. Spreadsheet documents are usually ready-to-use print forms.
3.2. Managing spreadsheet documents
You can use 1C:Enterprise spreadsheet document editor to create, view, and edit spreadsheet documents.
Reports are usually spreadsheet documents that display data processing results.
You can also create a spreadsheet or view reports previously saved to files in spreadsheet format.
Regardless of the spreadsheet document origin, you can use the spreadsheet document editor to edit it.
3.3. Creating and opening spreadsheet documents
To create a spreadsheet document, on the main menu, click File – New. Then select Spreadsheet document and click OK. This opens a blank document in the spreadsheet document editor.
To open a spreadsheet document, on the main menu, click File – Open. This opens the standard file selection dialog box.
Then select a file. You can select a file type in the File type field.
3.3.1. Importing Microsoft Excel and OpenDocument spreadsheets
To open a Microsoft Excel 97–2010 spreadsheet Excel workbook (*.xls, *xlsx) or an OpenDocument spreadsheet OpenDocument Spreadsheet ODF(*.ods), on the main menu, click File – Open, and select a document of that type. This creates a spreadsheet document and imports the original file to it.
The import has the following limitations:
- Import of password-protected documents is not supported.
- Import of charts is not supported.
- Import of pivot tables is not supported.
- Import of colors does not include opacity.
- Import of gradient patterns is partially supported: they are replaced with solid patterns that have the color of the first gradient point.
- Import of OLE objects is not supported.
- Import of text formatting has some limitations.
- Filling AutoShapes with patterns and pictures is not supported.
- Import of visual effects applied to pictures and shapes is not supported. This includes filters, blur, glow, antialiasing, and other effects.
- Rotation of AutoShapes and text aligned with AutoShapes is not supported.
- Import of hyperlinks is not supported. Imported hyperlinks are converted to plain text.
- AutoShapes: only import of ellipses, rectangles, and straight lines is supported. If AutoShape text is empty or consists only of spaces no figure of Text type is created.
- AutoShapes: for lines, import of arrow parameters is not supported. If AutoShape text is empty or consists only of spaces no figure of Text type is created
- Import of different border colors for a single cell is not supported.
- Only formula results are imported.
- When Microsoft Excel documents are imported from the clipboard, the default Excel color scheme is applied.
- Conditional formatting is based on constants only. Formula values and references to other cells are not supported.
- Conditional formatting of Microsoft Excel documents (histograms, icon sets, and rules of "formula equal to" type) is not supported.
- When a book with multiple non-blank sheets is imported, a page break is added after each sheet.
- Named areas are used to mark up areas of a spreadsheet document that represent sheets of the original document. In addition:
- If a document sheet name contains only letters, digits, and underscores (_) and starts with a letter or underscore, the area name is identical to the original sheet name.
- If a document sheet name also contains other characters or does not start with a letter or underscore, the area name is based on the original sheet name, with all invalid characters replaced by underscores and an underscore appended to the beginning of the name.
- If the original document sheet has no name, the area name is generated according to template _<Sheet number>, where <Sheet number> is the sequence number of the sheet in the document, starting with 1.
To paste data in Microsoft Excel or OpenDocument format from the clipboard, click Edit – Paste special, select source data format, and click OK.
3.4. Saving spreadsheet documents
You can save a spreadsheet document to a file for further viewing, editing, and printing. To save a spreadsheet document to a file, on the main menu, click File– Save. If you are saving a new document, specify the file name and path.
To save a spreadsheet document with a different name or in a different format, on the main menu, click File – Save As.
By default, spreadsheet documents are saved in a proprietary 1C:Enterprise format (*.mxl). You can save spreadsheet documents in other formats: Excel sheet (*.xls), HTML document, HTML3 document, HTML4 document, or HTML5 document (*.htm), PDF, PDF/A-1, PDF/A-2 or PDF/A-3 document (*.pdf), ANSI text file and UTF-8 text file (*.txt), Spreadsheet document v7.x (legacy 1C:Enterprise format), Excel95 sheet (*.xls), Excel97 sheet (*.xls), Word 2007-... document (*.docx), Excel2007-... sheet (*.xlsx), and ODF spreadsheet (*.ods). Select the file format in the Save As field.
For more details on saving documents in specific formats, see below.
3.4.1. Saving to spreadsheet document (*.mxl)
When multiline cell text is output to a spreadsheet document, its content is enclosed in quotation marks ("), while each quotation mark within the multiline string is replaced with two quotation marks.
3.4.2. Saving to Word2007–… document (*.docx)
When you save a spreadsheet document to a DOCX file, the following spreadsheet document print properties are preserved: page size, page orientation, scale, breakdown by pages, field settings, header and footer settings (except for line break and figures in footers), header presentation on each page, and the option to disable the display of headers and footers on the first page.
The following limitations apply:
- If text orientation is set, the text is displayed horizontally or vertically, whatever is closer to the original orientation.
- Pictures of Text type are always displayed with horizontal orientation.
- The only available text placement is Wrap. Other placement types are converted to Wrap.
- If at least one rectangle border is displayed, all its borders are displayed.
- Pictures in SVG format are rasterized and saved in PNG format. The resulting picture size depends on the size of an original picture in a spreadsheet document.
- If a cell contains a picture, the cell text will be positioned horizontally when saving to DOCX format.
- If the number of the first page used in the header and footer is less than 0, the numbering starts from 1 in the output document.
3.4.3. Saving Excel sheets (*.xls-*.xlsx) to files
When you save a spreadsheet document in Excel sheet format, it is saved in the default Microsoft Office Excel format (equivalent to Excel97 spreadsheet).
When saving a spreadsheet document in Excel sheet format, please keep these points in mind:
- Cell text is only converted to Excel number if the cell has horizontal right alignment; otherwise, the cell content is saved as text.
- If a cell contains centered multiline text that does not fit in the cell (and free space to the left and right of the cell is insufficient to display this text fully), the cell appearance may slightly differ from the original spreadsheet document.
- Pictures in SVG format are rasterized and saved in PNG format. The resulting picture size depends on the size of an original picture in a spreadsheet document.
- When you open an XLSX document in Microsoft Excel version earlier than 2007, some spreadsheet document colors might be displayed incorrectly.
- Footer text and background color are ignored when saving in Excel format; the limit of the total length of all footers is 255 characters.
- Figures in the footers are not exported when saving in XLS format.
- Only the first image is exported in the footers when saving in XLS format, the remaining are replaced with space.
- You cannot save documents in Microsoft Excel 95 format on Linux and macOS.
3.4.4. Saving HTML documents (*.htm) to files
Spreadsheets saved as HTML documents may be viewed in any HTML document viewer, for example, in Microsoft Internet Explorer. HTML documents are saved in HTML4 format, which preserves most formatting parameters. Pictures and charts are preserved as well.
The following limitations apply to exporting spreadsheets to HTML3, HTML4, and HTML5 formats:
- If a nonblank cell has Text placement property set to Auto, cells from this one to the next nonblank cell or to the end of row are merged.
- If a picture occupies multiple spreadsheet document cells, these cells are merged. The resulting cell contains the text that originally was in the top left cell.
- Text-and-picture overlapping is not supported. A picture is displayed first, then text.
- Charts and graphical objects are converted to embedded SVG pictures.
- When you save a document containing a chart to HTML5 format, or export a chart to SVG format, all custom chart fonts are replaced by standard Windows fonts.
- When you save a document to HTML3 or HTML4 format, pictures in SVG format are rasterized and saved in PNG format. The resulting picture size depends on the size of an original picture in a spreadsheet document.
- When you save a document to HTML5 format, pictures in SVG format are embedded.
3.4.5. Saving to PDF documents (*.pdf)
- Pictures in SVG format are saved as bitmap images if filters are used, or as vector images otherwise.
- If the spreadsheet document does not specify a page size, the page size set for the current printer is used.
3.4.6. Saving to PDF/A-1 documents (*.pdf)
- Document is written as PDF 1.4.
- Duplex printing is not supported.
- The source of paper in accordance with PDF-document size is not chosen during printing.
- The document does not support transparency
3.4.7. Saving to ODF spreadsheets (*.ods)
- Pictures in SVG format are saved as PNG images if filters are used, or in the original format otherwise.
- If the number of the first page used in the header is less than 1, the numbering starts from 1 in the output document.
- The images and background color in the footers are ignored.
3.5. Closing spreadsheet documents
To close a spreadsheet document, close the spreadsheet document editor (you can simply press Esc). If the document is modified, you are prompted to save the document.
3.6. Viewing spreadsheet documents
Navigating spreadsheets. As in the standard Microsoft Windows interface, the spreadsheet document window always contains only a part of the spreadsheet document. To select a cell in a visible area, click it. To view other parts of the spreadsheet document, use the scroll bars.
To scroll a large document, hold down the scroll wheel and select the scrolling direction with the pointer. The scrolling speed depends on the distance between the current pointer location and the pointer location at the time when the scroll wheel was pressed (it is marked with a special icon).
Managing spreadsheet view. Use the Table – View section of the main menu to configure the view of spreadsheet document parts: column and row headers, grid, and more.
The following table briefly describes the View menu items. On the main menu, click Table – View and then click a menu command. This shows or hides specific areas.
Dock the top rows and left columns of a spreadsheet document so that they are always visible when the document is scrolled.
Show or hide spreadsheet grid (thin lines between cells).
Show or hide row and column headers.
Show or hide groups (the areas to the left of the row header or above the column header).
Allow or prohibit spreadsheet document editing.
Show or hide comments.
Black and White View
Enable or disable colors specified in the spreadsheet document. If you check this item, spreadsheet colors are defined by the operating system settings.
Page view mode
Show or hide the minimum number of rows (depending on the amount of data in the spreadsheet), print area, and page breaks.
Scale the document
Scaling spreadsheets. To scale a spreadsheet document, on the main menu, click Table – View – Scale and select the scale.
You can use the scroll wheel to change the scale. You can also scale a spreadsheet by rotating the scroll wheel while holding down Ctrl. To zoom in, rotate the scroll wheel forward, and to zoom out, rotate it backward.
Docking rows or columns. You can dock the top rows and left columns so that they are always visible when you scroll a spreadsheet document. This is convenient if you are viewing a large table.
To dock spreadsheet rows, select a row and then, on the main menu, click Table – View – Dock Table. This docks the rows above the selected one.
To dock spreadsheet columns, select a column. This docks the columns to the left of the selected one.
To dock spreadsheet rows and columns simultaneously, click a cell. This docks the rows above the cell and the columns to the left of the cell.
To undock rows and columns, click Dock Table again.
Making spreadsheets read-only. To protect a spreadsheet document from editing, on the main menu, click Table – View – Edit (it this item is selected, the document is already protected).
If a cell contains a text that does not fit the visible area, move the pointer over that cell to display the full text as a tooltip. If the text is too large to fit in a tooltip, only a part of the text is displayed. While you are editing this text, a tooltip is not displayed, as long as you do not move the pointer.
Scrolling spreadsheets. Use the standard Microsoft Windows scrolling methods. If your mouse has a scroll wheel, to scroll vertically, rotate the wheel. To scroll horizontally, hold down Shift and rotate the wheel.
You can assign names to spreadsheet cells, cell ranges, and graphical objects.
To manage names, use the Names dialog box.
To name an area, select it and then, on the main menu, click Table – Names – Names. In the Names dialog box, enter a name and click Assign.
To delete a name, click the name and click Delete. To select a named area, click the name and click Select area. To close the dialog box, click Close.
If a named area is a row, or multiple rows, or a column, or multiple columns, in the Show Named Rows/Columns mode, area names are displayed at the left (for rows) or at the top (for columns). In the Show Named Cells mode, internal named areas are cross-hatched. An area name is displayed in the center of an area.
Spreadsheet document areas that do not have user-assigned names have default names. These names are not displayed in the Names dialog box. You can view them in the property palette. You cannot assign a name that matches a default name. Default names look as follows:
- R5C7. A cell in row 5, column 7.
- R5C7:R8C15. A rectangular cell range in rows 5–8 and columns 7–15.
- R6. Row 6.
- R3:R9. Rows 3–9.
- C2. Column 2.
- C11:C20. Columns 11–20.
- D12. A figure where 12 is the sequential number of a graphical object.
- T. The entire spreadsheet document.
Area names must be unique for each document. Area names are not case-sensitive.
3.6.2. Entering cell text
We will consider any combination of characters containing letters, numbers, punctuation marks, spaces, and special characters as text. Numbers typed in cells are also considered to be text.
You can enter text into any spreadsheet document cell. When a document or a report is generated, the cell text is processed based on the cell properties and then moved to the resulting report.
You can enter text in an active cell. To activate a cell, click it.
To enter text into a blank cell, simply start typing. This switches the cell to the editor mode: a cursor (a blinking vertical line) appears in the cell and you see the text being entered. When you finish typing, press Enter. You can also complete the editing by clicking another cell. This is equivalent to pressing Enter.
If a cell contains any text when you start editing it, this text is replaced with the new text.
To add more text to a cell, press Enter or F2 or double-click the cell before typing the text. This switches the cell to the editor mode and you can move the cursor using the mouse or the arrow keys.
Attention! Press Shift+Enter to start a new text line in a cell. You can also use this key combination to start a new line when entering text in the Text field of a cell property palette.
To cancel the changes that you made in the editor mode, press Esc.
If text does not fit a cell, it occupies adjacent cells of the same row (left, right, or both, depending on the text alignment).
If you need to enter the same text in multiple cells, select the cells and enter the text in the Text property. To save the text to the cells, click Save on the property palette toolbar. You can also copy text to the clipboard, select any group of cells (they do not have to be adjacent), and paste the text from the clipboard.
You can use any text orientation in cells. To set an angle, select cells and specify the angle from -360○ to 360○ in the Orientation property.
A comment can contain any text that describes the cell. A cell with a comment is marked by a red triangle in the upper-right corner. If the Show Comments mode the comment text is always displayed. If this mode is turned off, to view a comment, move the pointer over a cell.
You can define a cell text template in the Mask property.
Keyboard shortcuts. You can use all the keyboard shortcuts available in other Microsoft Windows applications for editing text in spreadsheet document cells.
If you make a mistake when editing a spreadsheet document, you can undo your actions. To undo the last action, on the main menu, click Edit – Undo. You can undo all the actions that you have performed after opening or creating a spreadsheet document.
To redo an action, on the main menu, click Edit – Redo.
3.7. Selecting cells, rows, and columns
To edit cell data, you have to select it first. Some operations can apply to a custom cell group or a cell range (a rectangular group of cells).
Before performing these operations, you have to select a cell group or a cell range. The column and row numbers of a selected group are also selected. This helps to find the group easily.
Use the standard Microsoft Windows methods to select a cell or a cell group.
To clear the selection, click any cell.
You can search for any character sequence and replace it with any other character sequence.
Search. To start a search, on the main menu, click Edit – Find. This opens the search dialog box.
In the Find field, enter the search string. Its length is not limited.
To make the search case-sensitive, select the Match case check box.
To search only for cells with text that exactly matches the source text, select the Match whole cell only check box.
There are eight possible search orders. Select the search order in the Direction group and Search list. All the search methods are cyclical.
Use the Direction group to specify the general search direction: Forward. Search in rows to the right and in columns down from the current position. Back. Search in rows to the left and in columns up from the current position.
Use the Search list to specify the range and order of the search:
- By columns. Search the current column. Upon reaching the end (or beginning) of the column, move to the next cell in the general direction. Upon reaching the end (or beginning) of the document, move to the beginning (end).
- By rows. Search the current row. Upon reaching the end (or beginning) of the row, move to the next cell in the general direction. Upon reaching the end (or beginning) of the document, move to the beginning (end).
- By current column. Search the current column. Upon reaching the end (or beginning) of the column, move back to the beginning (end) of the column.
- By current row. Search the current row. Upon reaching the end (or beginning) of the row, move back to the beginning (end) of the row.
Once you specify the search parameters, click Find. This moves the cursor to the next cell that matches the search criteria.
To continue the search in the Forward direction (regardless of the direction that was specified earlier), on the main menu, click Edit – Find Next, or press F3.
To continue the search in the Backward direction (regardless of the direction that was specified earlier), on the main menu, click Edit – Find Previous, or press Shift + F3.
Replacement. To replace a character sequence, on the main menu, click Edit – Replace. The opens the replacement dialog box.
It is similar to the search dialog box. In the Replace with field, enter the replacement text.
You cannot choose the replacement direction, it always goes Forward.
Once you specify the search and replacement parameters, click Find.
This moves the cursor to the next cell that matches the search criteria.
To replace the text in that cell, click Replace. Then the search continues.
To replace all occurrences of the selected text, click Replace all.
3.9. Changing row height and column width in spreadsheet documents
You can change row height and column width automatically or manually.
Resizing rows and columns manually. To change row height, move the pointer over the bottom border of the row header until the pointer becomes a vertical double-headed arrow. Then drag the border.
To change column width, move the pointer over the right border of column header until the pointer becomes a horizontal double-headed arrow. Then drag the border. To resize a column to fit the content, double-click the column header. The column width is automatically adjusted to fit the text.
You can also resize rows and columns by rotating the scroll wheel while holding down Ctrl. Press the Ctrl key and move the mouse pointer to the border of any cell located in the desired column or row. When the pointer changes its shape, drag the cell border. This method is convenient when column and row headers are not displayed (usually for spreadsheet documents located in forms).
Resizing rows and columns automatically. To change column width, select a column. Then, on the main menu, click Table – Cells – Column width. In the prompt that is displayed on the screen, specify the required width of the column measured in the number of characters of the spreadsheet document font. To set default width for one or several columns, select the columns and click Default width. If the Default width parameter is also specified for the columns of the entire spreadsheet document, the column width is set to 9 characters.
In the Weight factor field, you can specify the column width weight factor used for automatic column width calculation when stretching horizontally.
Click OK. This changes width of all columns to match the new value.
Changing the height of a row is similar to changing the width of a column.
To adjust height for one or several rows, select the rows and then, on the main menu, click Table – Cells – Row height.
For automatic calculation of row height based on the text height, select the Auto fit row height check box. If you select the Maximum row height check box, specify the maximum row height. If the actual row height is less than the maximum height, the row is displayed with the actual height. If the actual height is greater than the maximum height, the row height is reduced to the maximum height. In this case a part of the text will not fit. If you clear the Maximum row height check box, the row height is always equal to the actual height.
If you clear the Auto fit row height check box, specify the height in the Row height field.
Automatic column width is set differently from automatic row height. When you create a spreadsheet document, all the columns in this document have default width that equals the width of 9 characters of the default spreadsheet font.
If cells contain data and you need to set minimum column width or row height, double-click the column or row border. The application determines the maximum height or width of the text and resizes the cell to fit the text.
3.10. Moving and copying cells
You can move any cell or a cell range to a different location in a spreadsheet document. This actually moves the cell text and formatting, not the cell itself. You can also copy cells, which includes copying the cell formatting.
Moving cells. To move a cell or a range of cells:
- Select a cell or a cell range.
- Move the pointer over a cell border until it becomes an arrow .
- Drag the cells to a new location.
To move cells to a spreadsheet area that does not fit the screen, move the pointer to the border of the visible area and it will scroll automatically.
Copying cells. Using the mouse, you can easily copy a cell or range of cells. To do this, you need to hold down Ctrl and perform the dragging action.
Using clipboard. You can use Microsoft Windows clipboard to move and copy cells.
You can copy blocks of adjacent cells.
You can use the M toolbar button (Copy to clipboard as a number) to copy to the clipboard. This copies cell data as a number (if the copied cell contains a number). If you select a cell range, clicking the M button copies the sum of all the numbers in the selected cells to the clipboard.
The cell copy remains in the clipboard until the clipboard is cleared or some other data replaces it. You can use this feature to create multiple copies of a cell. For example, you can copy a cell, select a block of cells, and use the Paste command to paste the clipboard content to each cell in the block. You can use this method to copy several adjacent cells. However, remember that the target block size (the number of cells) must be divisible by the size of the block being copied. Otherwise the following warning is displayed: Selection does not correspond to insertable block.
If you use the Paste special command instead of Paste, the following dialog box is displayed:
Fig. . "Paste special" settings for a spreadsheet document
Use these options to specify what is inserted into a spreadsheet document: text only, cell values, formatting, borders, comments, or names.
Use the Column formatting parameter to preserve the original column formatting (for details, see section "Clearing cells" on page ) of the source document.
The Groups parameter allows pasting the cells area with original grouping of columns and rows of the spreadsheet document. It is only available when special pasting the entire document. Columns groups are pasted only when the Column formatting parameter is set.
The Headers and footers parameter allows pasting original headers and footers. It is only available when special pasting the entire document.
Use the Select all button to paste all content of all copied cells.
Enhanced dragging. You can use the right mouse button to move and copy cells. This operation is referred to as enhanced dragging. The procedure is the same as for the left mouse button, the only difference being that when the button is released, a context menu is displayed.
Use this menu to specify the operation: moving, copying, or copying with the "paste special" option. You can also cancel the operation from this menu.
Navigation to a cell. To navigate to a cell, on the main menu, click Table – Cells – Go to Cell. In the dialog box that is opened, enter the row number and column numbers.
Click OK to navigate to the specified cell.
3.11. Adding and deleting cells
You might need to insert a spreadsheet document cell, row, or column, as well as delete rows or columns.
Inserting a row (column). Perform the following steps (rows are inserted above the selected row, and columns are inserted to the left of the selected column).
- Select the number of rows or columns that you want to insert at the location where you want to insert them.
- On the main menu, click Table – Cells – Insert.
To insert rows, you can do the following: on the main menu, click Table – Cells – Insert rows above/Insert rows below.
To insert columns, you can do the following: on the main menu, click Table – Cells – Insert columns right / Insert columns left.
Deleting a row (column). To delete a row or a column:
- Select rows or columns.
- On the main menu, click Edit – Delete.
To delete selected rows (or columns), you can do the following: on the main menu, click Table – Cells – Remove rows (Remove columns).
Rows or columns are deleted with all their contents. They are replaced with rows below or columns to the right.
Inserting a cell. You can insert a cell anywhere in a spreadsheet document.
To insert a cell:
- Select a cell at the position where you want to insert a new cell.
- On the main menu, click Table – Cells – Insert.
- Select how to move cells to make room for a new cell: horizontally (move cells to the right) or vertically (move cells down).
- Click OK.
Inserting a cell range. You can insert a cell range to a spreadsheet document.
To insert a cell range:
- Select the cell range of the same size at the position where you want to insert a cell range.
- On the main menu, click Table – Cells – Insert.
- Select how to move cells to make room for a new range: horizontally (move cells to the right) or vertically (move cells down).
- Click OK.
Deleting a cell range. To delete a cell range:
- Select a cell range.
- On the main menu, click Edit – Delete.
- Select how to move cells to replace the deleted range: horizontally (move cells to the left) or vertically (move cells up).
- Click OK.
3.12. Splitting cells
You can split cells vertically or horizontally.
To split a cell, click it and then, on the main menu, click Table – Cells – Split Cell. This opens a dialog box where you can select the splitting type.
The following figure shows a result of horizontal splitting.
Vertical splitting is performed in the same way.
To clear cell data, on the main menu, click Table – Cells – Clear. Then do one of the following. To clear formatting, click Format. To clear borders, click Borders. To clear text, click Values or press Del. To clear the entire cell, click All.
3.14. Setting custom cell width for each row
You can specify custom cell width for each row. Select one or several rows as shown in the figure below.
Drag a column separator to the right or to the left. Release the mouse button. The following message is displayed: New row format will be created for the selected rows. Click OK. Now the spreadsheet looks as follows:
3.15. Hiding and showing rows and columns
You can hide or show spreadsheet rows and columns for viewing convenience.
Let us use the currency rate report as an example.
According to the spreadsheet document data, there were no changes in the currency rate during the first 9 days. You may want to hide this superfluous data. Select rows 5 to 13, right-click them, and click Hide. The resulting report looks as follows:
Note that the row numbering indicates that there are hidden rows.
To show the hidden rows, select any area that includes the hidden rows (in this case you can select rows 4 to 14), right-click them, and click Show.
To show all the hidden areas, select the entire spreadsheet.
3.16. Merging cells
Document print forms often require spreading text across multiple cells (both horizontally and vertically). You can use the merge feature for this. When multiple cells are merged into a single one, its text is aligned with the borders of the merged cell.
To merge cells, on the main menu, click Table – Cells – Merge.
NOTE. To merge cells, you can also use Ctrl + M or a context menu command.
All the text in the merged cells is lost except for the text in the top left cell, which becomes the text of the merged cell.
To split a merged cell, select it and then, on the main menu, click Table – Cells – Merge.
If a merged cell contains text, the text goes to top left cell upon merging, regardless of the alignment (for example, if it is has bottom right alignment, this does not affect the result).
3.17. Managing named spreadsheet document areas
You can assign a name to a cell group. Then you can access this area from 1C:Enterprise script by this name. Named areas are important for report templates. They are less important for spreadsheet documents and therefore they are not displayed in spreadsheet documents by default.
A template consists of building blocks that comprise a report. These building blocks are named areas.
3.17.1. Creating named areas
Named areas can be horizontal (consisting of selected rows), vertical (consisting of selected columns), and rectangular (consisting of adjacent cells).
To create a named area, select the area and then, on the main menu, click Table – Names – Set Name. In the dialog box that is opened, specify the area name.
3.17.2. Viewing named areas
To view named rows and columns, on the main menu, click Table – Names – Show Named Rows/Columns.
Area names are displayed next to row and column headers.
To view rectangular named areas, on the main menu, click Table – Names – Show Named Cells.
Named areas are cross-hatched. Names are displayed in the middle of areas.
To remove a named area, select an area and then, on the main menu, click Table – Names – Remove Name.
3.17.4. Resizing named areas
If a named area consists of multiple rows or columns, to resize it, select rows or columns inside the area and then, on the main menu, click Table – Cells – Insert.
If an area consists of a single row or column, it cannot be expanded. You can remove the name and assign it to a bigger area instead.
To reduce the size of a named area, select rows or columns to be deleted, right-click them, and click Delete.
You can do the same for rectangular areas. If you select individual cells instead of rows or columns, the area cannot be resized.
3.18. Managing spreadsheet document groups
You can join selected rows or columns to groups for viewing convenience.
You can create or delete groups, as well as expand them by including adjacent rows or columns. You can create nested and parent groups.
3.18.1. Creating groups
Groups can be horizontal (a group of rows) and vertical (a group of columns).
To create a group, select the rows or columns. Then, on the main menu, click Table – Groups – Combine to Group.
Attention! You cannot create a group for several adjacent cells.
3.18.2. Viewing groups
To view groups, on the main menu, click Table – View – View Groups.
Groups are shown as markers with thin lines that outline grouped areas.
You can manage display mode for the row and column groups by using special markers. Two marker types are available: level markers and group markers.
Level markers are numbered and located in the upper-left corner of the spreadsheet. Markers are displayed if the header view is turned on (on the main menu, click Table – View –View Headers/Footers). Use the marker with the highest number to expand or collapse all groups. Clicking a marker expands or collapses all the groups of the current level but does not affect higher-level groups.
Group markers are located to the left of the rows or above the columns. They indicate whether groups are expanded or collapsed (see ). These markers control the display of a particular group. Use the mouse pointer to click the group marker. This will expand a collapsed group, or collapse an expanded group. The marker appearance will change accordingly.
NOTE. To expand or collapse groups, you can also use the Group Levels section of the document context menu and the following keyboard shortcuts:
To collapse a group, press Ctrl + Num–.
To collapse a group and its nested groups, press Shift + Alt + Num–.
To collapse all groups, press Ctrl + Shift + Num–.
To expand a group, press Ctrl + Num+.
To expand all groups, press Ctrl + Shift + Num+.
To expand a group and its nested groups, press Shift + Alt + Num+.
The row or column that is located near the marker is referred to as the totals row. You can change the position of the totals row. For horizontal groups, the totals row may be located at the top or at the bottom of a group. For vertical groups, the totals row may be located to the left or right of the group. Use the Total Horizontally and Total Vertically properties of the spreadsheet document to set the position of the totals row. Once the position of the totals row is changed, the group marker position also changes. The totals row positioning properties are applied to all the groups of a spreadsheet.
3.18.3. Removing groups
To delete a group, select it (all the rows or columns that belong to the group) and then, on the main menu, click Table – Groups – Remove from Group.
3.18.4. Resizing groups. Parent and nested groups
You can resize groups by adding or removing rows or columns. You can create parent and nested groups.
Adding rows or columns to a group. To add rows or columns:
- Select rows or columns.
- On the main menu, click Table – Groups – Combine to Group.
The result depends on the selected range and whether the range belongs to a group:
- If the selected row or column range does not overlap any of the existing groups, create a group.
- If the selected row or column range partially overlaps an existing group, add the selected rows or columns to the group.
- If the selected row or column range is located inside an existing group, create a nested group.
- If the selected row or column range contains an existing group, create a parent group.
Removing rows or columns from a group. To remove rows or columns:
- Select rows or columns.
- On the main menu, click Table – Groups – Remove from Group.
The result depends on the selected range and whether the range belongs to a group:
- If the selected row or column range does not overlap any of the existing groups, no action is performed.
- If the selected row or column range is located inside an existing group, no action is performed.
- If the selected row or column range partially overlaps an existing group, remove the selected rows or columns from the group.
- If the selected row or column range contains an existing group, remove the group.
The picture below is an example of parent and nested groups.
A first-level group Division 07 (a parent group) contains two nested groups for Department 1 and Department 2. These groups are parent to employee groups.
You can add graphical objects to spreadsheets. They improve the document appearance and make minor additions to form descriptions without changing the template structure.
Graphical objects include lines, rectangles, ellipses, texts, pictures, embedded objects (OLE objects), and charts.
A text object is a frame with text inside.
This section describes how to work with all the graphical objects except OLE objects.
Graphical object properties are described in detail in respective sections of this Manual.
This section describes the options for adding graphical objects to spreadsheets.
3.19.1. Adding graphical objects
To insert a graphical object to a spreadsheet document, on the main menu, click Table – Pictures, and click the graphical object type. To customize graphical objects, use the property palette.
To insert a picture, select a cell for the upper-left corner of the picture. Then, on the main menu, click Table – Pictures – Picture. This opens the picture selection dialog box.
You can select a picture from a library (the Library list) or insert it from a file. To select from a file, click Select from file. A standard file open dialog box is displayed on the screen. In this dialog box, you can specify the name of the file with the image. If you do not select a file and click OK, a place for a picture is reserved in the spreadsheet. $$$ remove it$$$
When you insert a picture from a file, you can specify transparent color for the picture. We recommend that you use pictures with transparent background color for clear view of data overlapped by the picture.
The picture is always inserted in its original size. The size of the area for placing the picture can be changed. To resize a picture, click the Picture object and drag the object border or object corner accordingly. To maintain aspect ratio, hold down Shift while dragging.
Use the Picture Size property to specify picture placement options:
- Actual Size. Display the picture in its original size. If the picture is smaller than the area, leave the rest of the area blank. If the picture is larger than the area, display only a part of the picture.
- Stretch. Stretch the picture to fill the area.
- Proportional. Display the picture with its original proportions. Resize the biggest picture dimension to fit the area and calculate another dimension proportionally.
- Tile. Display the picture in its original size. If the area is larger than the original picture, display the picture as tiles.
- Auto Size. If the picture fits the area, display it in its original size. If the picture is larger than the area, resize it proportionally to fit in the area.
Standard graphical objects (rectangle, ellipse, and text) can be transparent or filled. By default, graphical objects are filled with solid color. You can select the fill pattern in the Pattern property of the Appearance category. You can also select the background color and the pattern color.
In the Line Style property, select the border style. In the Border Color property, select the border color.
Embedded graphical objects are automatically docked to spreadsheet cells. Objects are docked by the top left and bottom right corners. When column width or row height between these corners is changed, the object size also changes.
3.19.3. Grouping graphical objects
To combine several graphical objects into a group, select the objects.
On the main menu, click Table – Pictures – Group.
You can group multiple graphical objects to create a composite graphical object. Composite objects behave identically to regular graphical objects.
If required, a composite object can be ungrouped. To do this, select a composite graphic object and click Table – Pictures – Ungroup.
3.19.4. Naming graphical objects
Graphical objects, like spreadsheet cells, can be named. Use the Names dialog box to assign names (on the main menu, click Table – Names).
In 1C:Enterprise mode, default names are assigned to all new graphical objects. Default names consist of the word "Area" and the sequential number of the graphical object.
In Designer, default names are assigned to all new graphical objects. Default names consist of the letter D and the sequential number of the graphical object.
3.19.5. Selecting and resizing graphical objects
To manipulate a graphical object, you have to select it.
You can use the following selection methods: direct selection or graphical object selection mode.
For direct selection, click an object. If it is a rectangle, ellipse, or text, eight small square markers appear around it: four in the middle of each border side and four in the corners.
To resize a graphical object, drag the markers accordingly.
To resize an object proportionally, hold down Shift and drag any of its markers.
You can also resize graphical objects using arrow keys while holding down Shift.
When you select a line, markers appear only at the beginning and end of the line. When you drag a line while holding down Shift, this only changes the line length, without changing its angle.
To select multiple graphical objects, click them while holding down Shift.
To clear object selection, click any blank area of the spreadsheet document.
Another method is drawing a rectangle with the pointer. This selects all graphical objects in the rectangle. On the main menu, click Table – Pictures – Select Drawing Objects.
To turn graphical object selection mode off, on the main menu, click Table – Pictures – Select Drawing Objects.
3.19.6. Aligning graphical object groups
You can align graphical objects using the Table – Alignment menu. The toolbar commands become available when you select multiple objects.
Align the left borders of the selected items. The left borders of the selected items are aligned to a single vertical line.
Align items vertically by the center of the active ("master") item.
Align the right borders.
Align the top borders.
Align items vertically by the middle of the active ("master") item.
Align the bottom borders.
3.19.7. Specifying graphical object group size
To set the same size for all the objects in a group, click Table – Size.
An object with white markers is selected as a source (see ). This object is referred to as the master object. Other objects have black markers.
To select another object as a master object, click it. To specify the group size, select the Table – Size menu command.
Set identical width for all items in a group.
Set identical height for all items in a group.
Set identical size for all items in a group.
3.19.8. Moving and copying graphical objects
You can move and copy graphical objects.
Moving graphical objects. To move a graphical object, drag it to a new location. A dotted border (or line, if you move a line) marks the target location.
You can also move graphical objects using the arrow keys.
Copying graphical objects. To copy a graphical object, drag it to a new location while holding down Ctrl.
If you release Ctrl while dragging an object, the object is moved instead of copying.
Using clipboard. You can use the clipboard to move or copy graphical objects.
The object (or object group) position after pasting is determined as follows: the upper-left corner of an imaginary rectangular frame surrounding the objects is placed to the upper-left corner of the active cell.
3.19.9. Distributing graphical objects
To provide an even distribution of grouped graphical objects in a spreadsheet document, it is convenient to use the commands of the field menu of the spreadsheet document or the commands of the main menu Table – Distribute.
Evenly distribute the selected objects between the top and bottom objects.
Evenly distribute the selected objects between the leftmost and rightmost objects.
These commands are available only when multiple objects are selected.
When you execute the command, the outermost objects remain in place and all other objects are repositioned so that the distance between all selected objects is the same. The objects in the middle are distributed without aligning to the grid, even if auto grid alignment is turned on.
3.19.10. Changing graphical object order
You can specify the display order or overlapping graphical objects.
A new graphical object is always placed in the foreground and covers all the objects behind it. Graphical objects have a layered structure, where each object belongs to a layer. The top layer contains the object that is always in the foreground. You can use menu commands to move an object to the background (bottom layer), to the foreground (top layer), or to move it one layer up or down.
To change object order, right-click it and click Order, or in the main menu, click Table – Order, then click one of the following commands:
Move the selected object one layer forward.
Move the selected object one layer back.
Bring to front
Move the selected object to the front layer.
Send to back
Move the selected object to the back layer.
3.19.11. Deleting graphical objects
To delete a graphical object, select it and then, on the main menu, click Edit – Delete. You can delete an object only if the entire cell range that contains the object is selected. If some of the cells containing the object are not selected, the object is not deleted; however, its size may change.
You can add charts to spreadsheet documents. A chart is a graphical object that contains a chart or a diagram. You can create a chart using 1C:Enterprise script tools (in reports) or by inserting a chart to a spreadsheet for data analysis. This section describes how to add a chart to a spreadsheet.
3.19.13. Creating charts
To create a chart:
- On the main menu, click Table – Pictures – Chart.
- Select a rectangular chart area using the mouse.
- Enter the cell range containing the source data for the chart (you can change the range at any time).
If you select a cell range before creating a chart, you do not have to enter the range later. Once you insert a chart, it automatically displays the selected data.
After creating a chart, double-click it to activate it, then open its property palette to customize it.
3.19.14. Chart data area
You can configure a chart to display spreadsheet document data.
Suppose that a spreadsheet document contains data.
To create a histogram based on this data:
- Select a cell range that contains data (marked with a border in this example).
- On the main menu, click Table – Pictures – Chart.
- Select an area for drawing a chart.
This adds a chart that displays the specified data in graphical form. You can customize the chart appearance, title, area sizes, colors, and many other parameters using the chart property palette.
To customize a chart, on the main menu, click Edit – Properties.
Microsoft Windows applications can use embedded objects, which are also referred to as OLE objects. An OLE object is a document part that is created by another Windows application. An OLE object, like any other graphical object, is an integral part of a document. The only difference is that those objects are edited in another application, the one where they were initially created.
To create an OLE object:
- On the main menu, click Table – Pictures – Insert Object.
- Use the mouse to draw a rectangle where you want to insert an OLE object.
- Select the object type.
Then 1C:Enterprise opens a Windows application where you can edit objects of this type. At that, the Designer window layout changes: commands specific to the new application are added to the main menu, context menus, and the toolbar.
Attention! Applications that create and edit OLE objects are not included in 1C:Enterprise distribution kit. These are separately licensed Windows applications. To obtain help for these applications, refer to their documentation.
- Edit the OLE object if required.
- Finish editing the OLE object by clicking anywhere in the spreadsheet work area outside of the object.
An OLE object shares all the properties of graphical objects. You can move or resize it, copy it using the mouse or the clipboard, combine it with other objects, or delete it. For more information, see section "" on page .
To edit an OLE object, double-click it. This opens the Windows application where the object has been created.
3.20. Using spreadsheet documents as templates
Print form templates (for reports, documents, and so on) are usually designed by application developers. However, you can use a spreadsheet document stored in a file as a print form template.
3.21. Designing templates
Designing a template consists of "drawing" blocks that will be used to "assemble" the resulting document or report. Since nearly all the business documents have a rectangular structure, it is best to create templates for these documents in an editor that supports rectangular elements.
1C:Enterprise spreadsheet editor fits this requirement. You can enter text into spreadsheet cells, specify text and cell formatting, adjust spreadsheet row height and column width, and insert drawings (lines, rectangles, pictures, OLE objects, charts, and other graphical objects). You can also customize the appearance for the entire spreadsheet or for individual cells or cell groups.
In its final form a template is a combination of rectangular areas, each area representing some document or report part: a title area (used to display name, dates, and other data), a header area, a table area, and other areas.
You can assign a unique name to each rectangular area of a template so that you can refer to it later on.
Building a report based on a template goes as follows.
First, a document or a report is created as a blank spreadsheet. Note that a template is not directly linked to the final report. A template is a set of building blocks turned into a report using 1C:Enterprise script tools.
When a report is being generated out of a template, area names are taken in the required order, translated (variable names are replaced with their values), and copied to the report. The report generation script supports expansion of reports both horizontally and vertically.
Another way of report generation is entering data in spreadsheet document cells. Then the application processes this data using a formula given in the spreadsheet document. The results appear in other spreadsheet document cells and the report is ready to be viewed and printed.
Before you specify the print settings, we recommend that you turn the page view mode on to view the results. On the main menu, click Table – View – Page view mode.
3.23. Specifying headers and footers
Headers and footers are areas at the top and at the bottom of pages that contain text repeated on each document page. They usually contain a page number, a document name, a document creation date and time, or other data.
To create a header or a footer, on the main menu, click Table – Print Options – Headers and Footers. This opens a dialog box where you can specify whether you want to create a header, running title or a footer, enter its text, and apply style.
For each header or footer, specify the start page, alignment, and font.
In the First page number field, specify the number of the first page that has a header and a footer. Default value is 1.
3.24. Specifying pagination
1C:Enterprise applications split printed documents into pages based on the page settings, manual page breaks, and object positions. In addition, the forced page breaks and page position settings are considered for pagination. Note that a content of a single cell cannot be printed across multiple pages.
Object positions are not carried over from spreadsheet documents to final documents.
Manual page breaks.
To add a horizontal page break, select a cell or a row and then, on the main menu, click Table – Print Options – Insert Page Break. A page break is added above the selected cell or row. It is displayed as a dotted line.
To remove a horizontal page break, select a cell or a row under an existing page break and then, on the main menu, click Table – Print Options – Remove Page Break.
To insert a vertical page break, select a column and then, on the main menu, click Table – Print Options – Insert Page Break. A page break is added to the left of the selected column. It is displayed as a dotted line.
To remove a vertical page break, select a column to the left of the page break and then, on the main menu, click Table – Print Options – Remove Page Break.
Object positions. You can specify a position on the page for any row, column, or a range of rows or columns. To do so, select a range, open its property palette (for Cells) and then, under the Paragraph category, select or clear the Beginning of Page and Keep with Next check boxes.
3.25. Repeating rows and columns on each page
You can repeat a row or a row range (a column or a column range) on each page when printing a spreadsheet document. To do so, select a row or a row range (a column or a column range) and then, on the main menu, click Table – Print Options – Running Area.
The selected range is placed on the first page in the same way it is placed in the spreadsheet, that is, in accordance with its position on the sheet related to the column and row headers. On the subsequent pages, repeated rows are located at the top of each page, while columns are located at the left of each page.
To cancel repeating all rows and columns, on the main menu, click Table – Print Options – Remove Repetition.
Repeated rows and columns are not carried over from spreadsheets to final documents. You can also view and change repeated rows and columns in the spreadsheet property palette.
Instead of printing an entire spreadsheet, you can use the editor to specify a certain area for printing.
To specify a print area, select a cell range and then, on the main menu, click Table – Print Options – Set Print Area.
To cancel the print area selection, select a cell range and then, on the main menu, click Table – Print Options – Remove Print Area.
You can also specify the print area in the property palette of a spreadsheet document.
3.27. Specifying page settings
In the page setup dialog box, you can select a printer and specify paper size, orientation, and other options. To open the dialog box, on the main menu, click File – Page Setup. This opens the Page Setup dialog box.
The options available in this dialog box are typical for Microsoft Windows applications. We will only describe 1C:Enterprise-specific options.
Pages per sheet. In the Pages per sheet box, specify the number of pages printed on a single paper sheet (pages are printed in a column). If this option is set to Auto, the application determines the number of copies automatically using the following rule: if two pages fit in a single sheet with the specified size and margins, print two pages, otherwise print one page.
Headers and footers. The Headers and footers group is intended to specify the size of the sheet area that will be used to print headers and footers. Sizes are entered in millimeters and calculated from the top or bottom margin to the respective sheet edge.
Scale. In the Scale field, specify the scale for printing a spreadsheet. It does not affect the viewing scale in the editor.
Fit page width. Use this option to scale the spreadsheet automatically to fit page width.
Page placement. In the Page placement field, specify the margin adjustment options for even and odd pages.
- Do not use. Do not adjust margins.
- Mirror left. For odd pages, do not adjust the margins. For even pages, swap the left and right margins.
- Mirror top. For odd pages, do not adjust the margins. For even pages, swap the top and bottom margins.
- Auto. If the value in the Print on both sides field is No, do not adjust margins. If the value is Rotate left, use the Mirror left option. If the value is Rotate up, use the Mirror top option.
Print on both sides. Specify the options for printing on both sides.
- No. Print on a single side.
- Rotate left. Print on both sides for flipping pages from left to right.
- Rotate up. Print on both sides for flipping pages from bottom to top.
- Use printer settings. Two-sided printing depends on the settings of the current printer.
Black and white. Ignore the spreadsheet colors, print the document in black and white.
3.28. Printing spreadsheet documents
To switch between pages, click Move to next element and Move to previous element. To toggle between one-page and two-page views, click Displayed Pages.
To scale the preview, use the mouse or the Zoom In and Zoom Out buttons.
If the pointer is inside the preview area, click it to increase the scale. There are three fixed viewing scales. When you click a document while viewing it at the largest scale, it is displayed in the smallest scale again.
To increase the scale, click Zoom In. To decrease the scale, click Zoom Out.
To close a preview, click Close.
The preview uses the print options specified in the page setup dialog box. These options include paper size, print area borders, scale, and more. The preview also uses the row and column placement parameters specified in the spreadsheet editor. These parameters include manual page breaks, row and column repetition, row and column positions on a page, and more.
Printing documents. On the main menu, click File – Print.
In this dialog box, select the print parameters (the printer, the page range, and the number of copies) and click OK.
NOTE. The spreadsheet printing scale may or may not affect the page margins, depending on the application.
3.29. Editing spreadsheet document and cell properties
Use the property palette to configure spreadsheet, cell, or cell group behavior.
Spreadsheet document properties define which cell becomes active after pressing Enter, the print area, repeating document parts, and other parameters.
Cell or cell group properties define the data type, view, and formatting, cell dimensions, and other parameters.
When you open or create a spreadsheet document, its property palette displays cell properties. To switch to viewing and editing spreadsheet document properties, select Spreadsheet document at the top of the property palette. To view and edit properties of a cell or a cell group, select Cells at the top of the property palette.
Use the spreadsheet document property palette to configure its properties. To view spreadsheet document properties, at the top of the property palette, select Spreadsheet document.
Template. Shows whether this document is a template.
Total Horizontally. The position of the totals column for horizontal groups. See section "".
Total Vertically. The position of the totals column for vertical groups. See section "".
Picture. The background picture. See section "" on page .
Fixed Background. Shows whether the background picture is scrolled with the document.
Move after Input. Shows which cell becomes active after pressing Enter. Available options include: No – do not move; Down – move to the cell located below the current cell; Right – move to the cell located to the right of the current cell.
Print range. The print area. See section "".
Repeating Rows. Rows that are printed on each page. See section "".
Repeating Columns. Columns that are printed on each page. See section "".
You can customize the appearance of groups and headers by selecting the text and background colors.
To save the display settings (header view, grid, groups, names, and fixed rows and columns), select the Save settings check box.
The Total Horizontally and Total Vertically options define the rows and columns that are a logical part of a group but remain visible when the group is collapsed.
Let us consider an example with the Total Vertically option set to Above. The spreadsheet is shown in the figure below.
If you click the first-level marker, the table looks as follows.
If you click the second-level marker, the table looks as follows.
These are department totals.
If you click the third-level marker, the table looks as follows.
These are employee data.
You can use a picture from the picture library or from an external file as a background for a spreadsheet document.
To select a background picture, click the selection button in the Picture field of the property palette.
This opens the image selection dialog box.
Select a picture from the library or from a file. In the selected picture, you can set any of the colors in the picture to transparent (essentially, clear the color). To do this, click Set transparent color. The mouse pointer will change its appearance. Point to the picture color that you need to make transparent and click it. To cancel transparency, click Clear transparency.
Click OK to set the background picture.
To remove a background picture, in the picture selection dialog box, click Clear.
If you do not want the background picture to be scrolled with the spreadsheet document, in the property palette, select the Fixed Background check box.
You can print a part of a spreadsheet document (a sequence of rows or columns or a cell group) instead of the entire document. To specify the print area for a spreadsheet, use the Print Range field in the property palette.
You can specify the print area explicitly by entering the range. For example, R5C7:R8C15 describes a rectangular cell range, R3:R9 stands for rows 3-9, and D12 describes a picture. Once you enter an area name, it is replaced with the range describing the area.
You can specify the print area in the Range dialog box that is opened by clicking the selection button in the Print Range field.
In this dialog box, specify a range (see section "" on page ) and then, in the Range dialog box, click the selection button. The specified range is displayed in the Print Range field.
You can also specify the print area using menu commands (see section "" on page ).
When developing a report, remember that spreadsheet documents sometimes do not fit a single page. If this is the case, you can have column or row headers displayed at the top or left side of each spreadsheet page.
To specify repeating rows and columns, use the Repeating Rows and Repeating Columns fields.
For more information, see section "".
You can customize the behavior of individual cells or cell groups in a spreadsheet document.
You can use cell property palettes to customize them. To view and edit properties of a cell or a cell group, select Cells at the top of the property palette.
The set of properties that are open for viewing and editing applies to all selected cells of a spreadsheet document. If you set the value of a property, this value will be set for the entire selected area.
3.30.1. Specifying general properties
Text. The cell text. This property is available if the Contains value property in the Values category is not set and the Fill property of the Template category has the Text or Template value.
Text placement. Specifies how text is displayed if it does not fit into a cell.
Display parts that do not fit the adjacent cells if these cells are blank.
Limit the visible text part with the left and right cell borders.
Limit the visible text part with the left and right cell borders and replace all symbols of the visible text part with #. (Use it if the clipped text can be interpreted in a wrong way. Example: the number "123456" in a 5-character wide column will look like "#####")
Wrap the cell text if it does not fit the cell width.
Name. The area name.
Protection. Shows whether the selected cells and pictures are protected from modification (whether the Read Only property is set for the spreadsheet document).
Hyperlink. Shows whether a single click is enough to open the cell details. For spreadsheet documents located in forms, a click calls the Selection event handler and, if the standard processing flag is set, this calls the DetailProcessing event handler. For spreadsheet documents that are used independently (for example, reports), this calls the standard processing (for example, if a cell contains a reference to a Products list item, this opens the item form). For more information, see the description of the Details parameter property.
Column size change mode. Normal or Quick change.
3.30.2. Specifying value properties
Format. The format of cell content. Clicking the selection button opens the Format string wizard where you can specify the format.
Mask. A mask that defines the format of cell content.
3.30.3. Specifying alignment properties
Horizontal. Horizontal text alignment.
Align text with the left cell border.
Align text with the right cell border.
Align text with a vertical line running through the cell center.
Align text with the left and right cell borders. Resize the cells to display the entire text.
Align text depending on the cell content type. For example, use Left alignment for text and Right alignment for numbers.
By selected columns. Apply the selected text alignment to the leftmost cell of the selected area. This mode is mostly used to center text in several cells of a single row.
Vertical. Vertical text alignment.
Align text with the bottom cell border.
Align text with the top cell border.
Align text with a horizontal line running through the cell center.
Text orientation. The text angle, varies from -360o to 360o.
Indent. The indent from the cell border that is used for text alignment.
AutoIndent. If this value is greater than 0, calculate the indent as a product of the group level and the value of this property.
The Indent and AutoIndent properties are not mutually exclusive.
3.30.4. Specifying appearance properties
Use this category to specify the font type, size, style, and color, whether blank or negative values are highlighted, the background and pattern color, and the style of individual cell borders.
To customize a cell border, click the selection button. This opens the selection dialog box.
You can select the line type and width.
The dialog box displays a line preview.
If you want the line not to be drawn across the entire side of the cell area, select the Indent check box. In this case, the line will be indented from the edges of the cells. See an example in the figure below.
3.30.5. Specifying picture properties
Use this category to specify properties for a picture embedded in a cell.
Picture. To select a picture, click the selection button. This opens the picture selection dialog box.
In this dialog box, select a picture from the picture library or from a file.
Picture size. If this property is set to Tile or Stretch, the cell text is displayed on top of the picture. The default value is Proportional.
Text alignment relative to the picture. The text alignment relative to the picture. If this property is set to Auto, the following rules apply:
- If the picture is aligned left, display the text to the right of the picture.
- If the picture is aligned right, display the text to the left of the picture.
- If the picture is aligned top, display the text below the picture.
- If the picture is aligned bottom, display the text above the picture.
- Otherwise display the text on top of the picture.