However, this probably isn't something that will ever need to do. If you do use this feature then do so with care. However, in practice it will be used less frequently. In the above example the substitution is always made against the original token, A1 , which doesn't change. However, each substitution is made only once:. The qr operator is explained in the perlop man page.
Care should be taken with the values that are substituted. In particular you should avoid substituting a single 0, 1, 2 or 3. You should also be careful to avoid false matches. However it contains a bug. Another similar problem occurs due to the fact that substitutions are made in order. If you think that you have a problem related to a false match you can check the tokens that you are substituting against as follows. See also the repeat. A cell comment is indicated in Excel by a small red triangle in the upper right-hand corner of the cell.
Moving the cursor over the red triangle will reveal the comment. Most of these options are quite specific and in general the default comment behaviour will be all that you need. However, should you need greater control over the format of the cell comment the following options are available:.
If you wish to use Unicode characters in the comment string then the preferred method is to use perl 5. This option is used to indicate who the author of the comment is. Excel displays the author of the comment in the status bar at the bottom of the worksheet. This is usually of interest in corporate environments where several people might review and provide comments to a workbook. This option is used to make a cell comment visible when the worksheet is opened. The default behaviour in Excel is that comments are initially hidden.
However, it is also possible in Excel to make individual or all comments visible. In Spreadsheet::WriteExcel individual comments can be made visible as follows:. Alternatively, if all of the cell comments have been made visible you can hide individual comments:.
This option is used to set the background colour of cell comment box. You can use one of the named colours recognised by Spreadsheet::WriteExcel or a colour index. This option is used to set the cell in which the comment will appear.
By default Excel displays comments one cell to the right and one cell above the cell to which the comment relates. However, you can change this behaviour if you wish.
In the following example the comment which would appear by default in cell D2 is moved to E2. This option is used to set the row in which the comment will appear. The row is zero indexed. This option is used to set the column in which the comment will appear. The column is zero indexed. This method is used to extend the Spreadsheet::WriteExcel write method to handle user defined data. However, it doesn't always act in exactly the way that you would like it to. In the these examples the qr operator is used to quote the regular expression strings, see perlop for more details.
The method is used as follows. Then if you call write with an appropriate string it will be handled automatically:. The callback function will receive a reference to the calling worksheet and all of the other arguments that were passed to write.
So for example if you wished to apply the previous filter only to ID values that occur in the first column you could modify your callback function as follows:. You may add more than one handler in which case they will be called in the order that they were added. This method can be used to insert a image into a worksheet. The offset values are in pixels. The default width of a cell is 63 pixels.
The default height of a cell is 17 pixels. The pixels offsets can be calculated using the following relationships:. The offsets can be greater than the width or height of the underlying cell. This can be occasionally useful if you wish to align two or more images relative to the same cell. See also the images.
BMP images must be 24 bit, true colour, bitmaps. In general it is best to avoid BMP images since they aren't compressed. This method can be used to insert a Chart object into a worksheet.
The easiest way to calculate the required scaling is to create a test chart worksheet with Spreadsheet::WriteExcel. Then open the file, select the chart and drag the corner to get the required size.
While holding down the mouse the scale of the resized chart is shown to the left of the formula bar. This method can be used to insert a externally generated chart into a worksheet. The chart must first be extracted from an existing Excel file.
The activate method is used to specify which worksheet is initially visible in a multi-sheet workbook:. This is similar to the Excel VBA activate method. More than one worksheet can be selected via the select method, see below, however only one worksheet can be active. The select method is used to indicate that a worksheet is selected in a multi-sheet workbook:.
A selected worksheet has its tab highlighted. Selecting worksheets is a way of grouping them together so that, for example, several worksheets could be printed in one go. A worksheet that has been activated via the activate method will also appear as selected. You may wish to hide a worksheet in order to avoid confusing a user with intermediate data or calculations.
A hidden worksheet can not be activated or selected so this method is mutually exclusive with the activate and select methods. In addition, since the first worksheet will default to being the active worksheet, you cannot hide the first worksheet without activating another sheet:.
The activate method determines which worksheet is initially selected. However, if there are a large number of worksheets the selected worksheet may not appear on the screen.
The protect method also has the effect of enabling a cell's locked and hidden properties if they have been set. A "locked" cell cannot be edited. A "hidden" cell will display the results of a formula but not the formula itself. In Excel a cell's locked property is on by default.
Note, the worksheet level password in Excel provides very weak protection. It does not encrypt your data in any way and it is very easy to deactivate. Therefore, do not use the above method if you wish to protect sensitive data or calculations. For technical reasons this will never be supported by Spreadsheet::WriteExcel. This method can be used to specify which cell or cells are selected in a worksheet.
It is also possible to specify a cell or a range using A1 notation. This method can be used to change the default properties of a row. If you wish to set the format without changing the height you can pass undef as the height parameter:. For example. If you wish to define a row format in this way you should call the method before any calls to write.
Calling it afterwards will overwrite any format that was previously specified. This can be used, for example, to hide intermediary steps in a complicated calculation:. Adjacent rows with the same outline level are grouped together into a single outline. For a more complete example see the outline. Excel allows up to 7 outline levels. This method can be used to change the default properties of a single column or a range of columns.
It is also possible, and generally clearer, to specify a column range using the form of A1 notation used for columns. The width corresponds to the column width value that is specified in Excel.
It is approximately equal to the length of a string in the default font of Arial Unfortunately, there is no way to specify "AutoFit" for a column in the Excel file format. This feature is only available at runtime from within Excel.
If you wish to set the format without changing the width you can pass undef as the width parameter:. If you wish to define a column format in this way you should call the method before any calls to write. If you call it afterwards it won't have any effect.
Adjacent columns with the same outline level are grouped together into a single outline. Setting this parameter to 0 will cause all outlines on the worksheet to be hidden. They can be unhidden in Excel by means of the "Show Outline Symbols" command button. The default setting is 1 for visible outlines.
The default setting is 1 for symbols to appear below the outline level bar. The default setting is 1 for symbols to appear to the right of the outline level bar. This has no effect on a file generated by Spreadsheet::WriteExcel but it does have an effect on how the worksheet behaves after it is created.
The default setting is 0 for "Automatic Styles" to be turned off. This method can be used to divide a worksheet into horizontal or vertical regions known as panes and to also "freeze" these panes so that the splitter bars are not visible.
It should be noted that the split is specified at the top or left of a cell and that the method uses zero based indexing. Therefore to freeze the first row of a worksheet it is necessary to specify the split at row 2 which is 1 as the zero-based index. This might lead you to think that you are using a 1 based index but this is not the case. They are used to specify the top-most or left-most visible row or column in the scrolling region of the panes. For example to freeze the first row and to have the scrolling region begin at row twenty:.
See also the panes. This method can be used to divide a worksheet into horizontal or vertical regions known as panes. However, the vertical and horizontal units are different from each other. They are used to specify the top-most or left-most visible row or column in the bottom-right pane.
The older name is still available for backwards compatibility. However, this only allows simple Excel5 style horizontal merging which Excel refers to as "center across selection". It is a fatal error to use a merged format in a non-merged cell. Instead you should use separate formats for merged and non-merged cells.
This restriction will be removed in a future release. Therefore it will handle numbers, strings, formulas or urls as required. In fact using it will exclude the use of any other horizontal alignment option.
The full possibilities of this method are shown in the merge3. The default zoom factor is You cannot zoom to "Selection" because it is calculated by Excel at run-time. This is useful when creating Arabic, Hebrew or other near or far eastern worksheets that use right-to-left as the default direction.
This feature is only available in Excel and later. You can use one of the standard colour names provided by the Format object or a colour index. This method allows an autofilter to be added to a worksheet. An autofilter is a way of adding drop down lists to the headers of a 2D range of worksheet data. This in turn allow users to filter the data based on simple criteria so that some data is shown and some is hidden.
See the autofilter. NOTE: It isn't sufficient to just specify the filter condition. You must also hide any rows that don't match the filter condition. Spreadsheet::WriteExcel cannot do this automatically since it isn't part of the file format. The operator synonyms are just syntactic sugar to make you more comfortable using the expressions. It is important to remember that the expressions will be interpreted by Excel and not by perl. An expression can comprise a single statement or two statements separated by the and and or operators.
Filtering of blank or non-blank data can be achieved by using a value of Blanks or NonBlanks in the expression:. No other regular expression quantifier is supported by Excel's filters.
The placeholder variable x in the above examples can be replaced by any simple string. The actual placeholder name is ignored internally so the following are all equivalent:. Also, note that a filter condition can only be applied to a column in a range specified by the autofilter Worksheet method. Page set-up methods affect the way that a worksheet looks when it is printed. They control features such as page headers and footers and margins.
These methods are really just standard worksheet methods. They are documented here in a separate section for the sake of clarity. A common requirement when working with Spreadsheet::WriteExcel is to apply the same page set-up features to all of the worksheets in a workbook.
To do this you can use the sheets method of the workbook class to access the array of worksheets in a workbook:. This method is used to set the orientation of a worksheet's printed page to portrait. The default worksheet orientation is portrait, so you won't generally need to call this method.
This method is used to display the worksheet in "Page View" mode. This is currently only supported by Mac Excel, where it is the default. This method is used to set the paper format for the printed output of a worksheet.
The following paper styles are available:. Note, it is likely that not all of these paper types will be available to the end user since it will depend on the paper formats that the user's printer supports.
Therefore, it is best to stick to standard paper types. If you do not specify a paper type the worksheet will print using the printer's default paper. All of these methods take a distance in inches as a parameter. The default top and bottom margin is 1. For simple text, if you do not specify any justification the text will be centred. The information control characters act as variables that Excel will update as the workbook or worksheet changes.
Times and dates are in the users default format:. It is possible to combine all of these features together to create sophisticated headers and footers. As an aid to setting up complicated headers and footers you can record a page set-up as a macro in Excel and look at the format strings that VBA produces. Remember however that VBA uses two double quotes "" to indicate a single double quote. For the last example above the equivalent VBA code looks like this:.
As stated above the margin parameter is optional. As with the other margins the value should be in inches. The default header and footer margin is 0. The header and footer margin size can be set as follows:. Note, the header or footer string must be less than characters. Strings longer than this will not be written and a warning will be generated. See, also the headers. For large Excel documents it is often desirable to have the first row or rows of the worksheet print out at the top of each page.
For large Excel documents it is often desirable to have the first column or columns of the worksheet print out at the left hand side of each page. You can also specify the columns using A1 column notation, see the note about "Cell notation". This method is used to hide the gridlines on the screen and printed page. Gridlines are the lines that divide the cells on a worksheet. Screen and printed gridlines are turned on by default in an Excel worksheet. If you have defined your own cell borders you may wish to hide the default gridlines.
If you don't supply an argument or use undef the default option is 1, i. The headers are the letters and numbers at the top and the left of the worksheet. Since these headers serve mainly as a indication of position on the worksheet they generally do not appear on the printed page. This method is used to specify the area of the worksheet that will be printed.
All four parameters must be specified. You can also use A1 notation, see the note about "Cell notation". This is referred to by Excel as the sheet "page order".
The default page order is shown below for a worksheet that extends over 4 pages. The order is called "down then across":. If the printed area exceeds the specified number of pages it will be scaled down to fit. This guarantees that the printed area will always appear on the specified number of pages even if the page size or margins change. A common requirement is to fit the printed output to n pages wide but have the height be as long as necessary.
The last method call made will set the active option. The default value is 1. Set the scale factor of the printed page. The default scale factor is Add horizontal page breaks to a worksheet.
A page break causes all the data that follows it to be printed on the next page. Horizontal page breaks act between rows. To create a page break between rows 20 and 21 you must specify the break at row However in zero index notation this is actually row So you can pretend for a small while that you are using 1 index notation:.
There is a silent limitation of about horizontal page breaks per worksheet in line with an Excel internal limitation.
Add vertical page breaks to a worksheet. Vertical page breaks act between columns. To create a page break between columns 20 and 21 you must specify the break at column However in zero index notation this is actually column This section describes the methods and properties that are available for formatting cells in Excel. The properties of a cell that can be formatted include: fonts, colours, patterns, borders, alignment and number formatting.
Cell formatting is defined through a Format object. The format object holds all the formatting properties that can be applied to a cell, a row or a column. The process of setting these properties is discussed in the next section. Once a Format object has been constructed and its properties have been set it can be passed as an argument to the worksheet write methods as follows:.
The following table shows the Excel format categories, the formatting properties that can be applied and the equivalent object method:. There are two ways of setting Format properties: by using the object method interface or by setting the property directly.
For example, a typical use of the method interface would be as follows:. By comparison the properties can be set directly by passing a hash of properties to the Format constructor:. You can also store the properties in one or more named hashes and pass them to the required method:.
The provision of two ways of setting properties might lead you to wonder which is the best way. The method mechanism may be better is you prefer setting properties via method calls which the author did when the code was first written otherwise passing properties to the constructor has proved to be a little more flexible and self documenting in practice. An additional advantage of working with property hashes is that it allows you to share formatting between workbook objects as shown in the example above.
Each unique format in Spreadsheet::WriteExcel must have a corresponding Format object. It isn't possible to use a Format with a write method and then redefine the Format for use at a later stage. This is because a Format is applied to a cell not in its current state but in its final state. Consider the following example:. However, the colour is subsequently set to green.
When Excel displays Cell A1 it will display the final state of the Format which in this case will be the colour green. The Format object methods are described in more detail in the following sections. In addition, there is a Perl program called formats. This program creates an Excel workbook called formats. The above methods can also be applied directly as properties. However, this method is here mainly for legacy reasons. It is preferable to set the properties in the format constructor:.
Excel can only display fonts that are installed on the system that it is running on. See also the Fonts worksheet created by formats. Set the font size. Excel adjusts the height of a row to accommodate the largest font size in the row. For additional examples see the 'Named colors' and 'Standard colors' worksheets created by formats. It is probably best to set the value to 1 and use normal bold.
This method is used to define the numerical format of a number in Excel. It controls whether a number is displayed as an integer, a floating point number, a date, a currency value or some other user defined format. The numerical format of a cell can be specified by using a format string or an index to one of Excel's built-in formats:. Alternatively you can specify the colour based on a colour index as follows: [Color n] , where n is a standard Excel colour index - 7.
See the 'Standard colors' worksheet created by formats. For examples of these formatting codes see the 'Numerical formats' worksheet created by formats. Note 1. Numeric formats 23 to 36 are not documented by Microsoft and may differ in international versions. Note 2. Furthermore, I'd expect that the error message would read can't locate Spreadsheet::WriteExcel. Bharanikumar: what do you exactly mean? I told you how to install: either using cpan Spreadsheet::WriteExcel or by the interactive method Warning: prerequisite Parse::RecDescent 0 not found.
Show 4 more comments. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back The following time formats are valid:. The meridian, AM or PM, is optional and case insensitive. A 24 hour time is assumed if the meridian is omitted.
Time in Excel is expressed as a fraction of the day in seconds. Therefore you can calculate an Excel time as follows:. The parsing is performed using the ParseDate function of the Date::Manip module. Refer to the Date::Manip documentation for further information about the date and time formats that can be parsed. Note, if you parse a string that represents a time but not a date this function will add the current date. If you want the time without the date you can do something like the following:.
This function is used to initialise variables required by the Date::Manip module. It need only be called once. Refer to the Date::Manip documentation for further information. Refer to the Date::Calc for further information about the date formats that can be parsed. Also note the following from the Date::Calc documentation:. Note: the EU in the function name means that a European date format is assumed if it is not clear from the string.
See the first example below. Note: the US in the function name means that an American date format is assumed if it is not clear from the string. This function converts an Excel date based on the epoch into a date based on the epoch.
The date and time functions require functions from the Date::Manip and Date::Calc modules. The required functions are "autoused" from these modules so that you do not have to install them unless you wish to use the date and time routines.
Therefore it is possible to use the row and column functions without having Date::Manip and Date::Calc installed. For more information about "autousing" refer to the documentation on the autouse pragma. The current workaround for this is to put use warnings; near the beginning of your program. All Rights Reserved. This module is free software. For more information on module installation, please visit the detailed CPAN module installation guide.
0コメント