Skip to content

Instantly share code, notes, and snippets.

@hertsch

hertsch/kitcommand_excelread_en.md

Last active December 22, 2015 10:39
Show Gist options
  • Save hertsch/6460548 to your computer and use it in GitHub Desktop.
Save hertsch/6460548 to your computer and use it in GitHub Desktop.
Download ZIP
kitcommand to read Excel Files and show them at your website
Raw
kitcommand_excelread_en.md

##~~ ExcelRead ~~

With the kitCommand ~~ ExcelRead ~~ you can embed a Excel file in any WYSIWYG section of your Content Management System.

Please read the Introduction for your first steps with ExcelRead, this helpfile explains the different parameters for the command.

Introduction

Parameters

Please be aware that these are only the additional parameters for ~~ ExcelRead ~~, there exists also parameters for the general usage with kitCommands.

base[]

Specify the base for the parameter file[]. By default the specified Excel file will be searched in the/media directory of your Content Management System, the following base values are available:

  • base[cms_media] - default, the Excel file will be expected in the /media directory of your CMS
  • base[media] - the Excel file will be expected in the /media/public directory of the kitFramework
  • base[media_protected] - the Excel file will be expected in the /media/protected directory of the kitFramework
  • base[path] - the Excel file is located in the specified path of your Webserver.
  • base[url] - the Excel file will be loaded from the specified URL (your Webserver must enable allow_url_fopen)

column[]

By default ExcelRead will create a table with all columns of the loaded Excel file. With the parameter column you can specify the columns to show or to hide.

~~ ExcelRead ... column[1,3,5] ~~

will use only the columns 1, 3 and 5 of the Excel file to create the table.

~~ ExcelRead ... column[-2,-8,-9] ~~

will exclude the columns 2, 8 and 9 of the Excel file.

create[]

If the Excel file contains URI's like http://yourdomain.tld or www.yourdomain.tld you can use the parameter

~~ ExcelRead ... create[link] ~~

to force ExcelRead to create a HTML links like

<a href="http://www.yourdomain.tld" target="_top">www.yourdomain.tld</a>

By default the target of these links will be _top. If you need another target you can use the additional parameter target[].

To create mailto links you can use the parameter

~~ ExcelRead ... create[mail] ~~

this will result in links like

<a href="mailto:webmaster@yourdomain.tld">webmaster@yourdomain.tld</a>

You can also combine the both possible values for create[]

~~ ExcelRead ... create[link,mail] ~~

just separate the values by comma.

file[]

This is the main parameter for ExcelRead: specify the Excel file to load. By default the specified file

~~ ExcelRead file[excel.xls] ~~

will be searched in the /media folder of your Content Management System. Use the parameter base[] to specify another base.

format[]

You can tell ExcelRead to format columns in a specific way:

~~ ExcelRead ... format[3|currency] ~~

will change the value i.e. 1203,4504 of column 3 to

1,203,45 $

if the language of the page is English or to

1.203,45 €

if the language of the page is German.

Use the schema <column>|<format> to specify the column number and the format which should be assigned. Separate the columns by comma.

The date format is a bit more tricky:

~~ ExcelRead ... format[3|date] ~~

will format the value of column 3, i.e. 2013-10-07 to

07.10.2013

if the language of the page is German (using the format defined in the language file).

~~ ExcelRead ... format[3|date(m/d/Y)] ~~

will format the value of column 3, i.e. 2013-10-07 to

10/07/2013

Using brackets date() let you define a format string for the date. You can use all formats which are supported by the PHP function date().

Attention: If you need a comma within the format string you can not type it between the brackets, because the comma is the separator for multiple format commands! So if want to use the format string:

F j, Y

you must replace the comma with a typed comma:

~~ ExcelRead ... format[3|date(F jcomma Y)] ~~

this will format the value of column 3, i.e. 2013-10-07 to

October 7, 2013

The following format commands are supported by the default template:

  • currency - will format the value as currency string, including the currency symbol and a thousand separator
  • date - will format the value as date string
  • date() - will format the value as date string, using the PHP function date()
  • decimal - will format the value as decimal string with 2 decimal places and a thousand separator
  • integer - will format the value as integer value without decimal places and with a thousand separator
  • translate - will try to translate the value to the actual language

All commands, except date(), are depending on the settings for the language for the page, where ExcelRead is placed. The settings are defined in the language files of the kitFramework BASIC extension in

/kit2/extension/phpmanufaktur/phpManufaktur/Basic/Data/Locale

and uses

CURRENCY_SYMBOL
DATE_FORMAT
DECIMAL_SEPARATOR
THOUSAND_SEPARATOR

you can define additional languages or custom language files (which will not changed at updates of the BASIC extension) in the directory

/kit2/extension/phpmanufaktur/phpManufaktur/Basic/Data/Locale/Custom

header[]

By default ExcelRead use the first row of the Excel file to create the header for the table. With

~~ ExcelRead ... head[false] ~~

you can suppress the table header.

row[]

By default ExcelRead show all rows of the Excel file. With the parameter row[] you can specify rows to show or to hide. With

~~ ExcelRead ... row[1,2,5] ~~

only the rows 1, 2 and 5 will be shown.

~~ ExcelRead ... row[-1,2,-5] ~~

All rows except the rows 1, 2 and 5 will be shown.

sheet[]

By default ExcelRead will show only the first sheet of the ExcelTable. Use the parameter sheet[] to show one or more specified sheets:

~~ ExcelRead ... sheet[2,3] ~~

will show the sheets 2 and 3 (table below table).

style[]

You can specify CSS classes for each column to change the style:

~~ ExcelRead ... style[2|right,4|center bold,5|italic] ~~

This will

  • assign the class right to column 2
  • assign the classes center and bold to column 4
  • assign the class italic to column 5

use the schema <column>|<style> to specify the column number and one or more classes (separated by a space) which should be assigned. Separate the columns by comma.

The default Template for ExcelRead support the following classes:

  • left - default, assign the text of the column to the left
  • right - assign the text of the column to the right
  • center - center the text of the column
  • bold - show the column text bold
  • italic - show the column text italic

You can extend the stylesheet or create your own.

tablesorter[]

Using the default template you can use the parameter tablesorter[] to activate the jQuery Tablesorter.

This parameter does not support any values.

target[]

If your are using the parameter create[link] the target of these links is by default _top. Use target[] to change this:

  • target[_blank] - target is a new browser window
  • target[_parent] - target is parent of the kitCommand iFrame
  • target[_self] - target is the kitCommand iFrame self
  • parent[_top] - target is the most top parent within the window

title[]

Use the parameter title[] without any value to insert a <h2> title tag with the Excel sheet name above the table.

Raw
welcome.md

This is the welcome file for ExcelRead!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment