Home
PhpMyCodeGenerator is a code generator framework, which allows you to create code generators as PHP functions and provide input to the code generators through an Excel/Calc file. PhpMyCellScript allows you to write programs directly in Excel/Calc.Project News
2015-03-01 - PhpMyCodeGenerator 2.25.0 Released
2013-11-10 - PhpMyCodeGenerator 2.24.0 Released
2013-03-07 - PhpMyCodeGenerator 2.23.0 Released
More... Some Use Cases
- Generate UML sequence diagram. [Screencast]
- Sort SQL via programmable find and replace. [Example]
Input - example.xls (script - sort SQL)
COMMENT This script demonstrates how to use the FIND and REPLACE instructions to extract and sort SQL statements. STRING CREATE TABLE [dbo].[MY_TEST](\r
\t[my_test_id] [numeric](18, 0) NOT NULL,\r
\t[my_test_amount] [numeric](18, 2) NOT NULL\r
)\r
GO\r
CREATE TABLE [dbo].[MY_TEST2](\r
\t[my_test_id] [numeric](18, 0) NOT NULL,\r
\t[my_test_amount] [numeric](18, 2) NOT NULL\r
)\r
GO\r
ALTER TABLE [dbo].[MY_TEST] ADD CONSTRAINT [DF_MY_TEST_my_test_amount] DEFAULT (0) FOR [my_test_amount]\r
GO\r
ALTER TABLE [dbo].[MY_TEST2] ADD CONSTRAINT [DF_MY_TEST2_my_test_amount] DEFAULT (0) FOR [my_test_amount]\r
GO\r$sql COMMENT Extract the CREATE TABLE statements. FIND /CREATE TABLE .+?\nGO\r\n/is $sql $matches $selection_start $selection_length $sql_statements[] = $matches[0] // Remove the extracted SQL statement from $sql REPLACE COMMENT Extract the ALTER TABLE statements. The last three parameters are optional. FIND /ALTER TABLE .+?\nGO\r\n/is $sql $matches $sql_statements[] = $matches[0] // Remove the extracted SQL statement from $sql REPLACE COMMENT Sort and output the extracted SQL statements. sort($sql_statements) echo join('', $sql_statements) COMMENT Output the unextracted SQL statements. ECHO $sql Output - console
ALTER TABLE [dbo].[MY_TEST2] ADD CONSTRAINT [DF_MY_TEST2_my_test_amount] DEFAULT (0) FOR [my_test_amount] GO ALTER TABLE [dbo].[MY_TEST] ADD CONSTRAINT [DF_MY_TEST_my_test_amount] DEFAULT (0) FOR [my_test_amount] GO CREATE TABLE [dbo].[MY_TEST2]( [my_test_id] [numeric](18, 0) NOT NULL, [my_test_amount] [numeric](18, 2) NOT NULL ) GO CREATE TABLE [dbo].[MY_TEST]( [my_test_id] [numeric](18, 0) NOT NULL, [my_test_amount] [numeric](18, 2) NOT NULL ) GO
- Generate code based on DB schema.
- Generate HTML/XML based on spreadsheet data. [Example]
Input - example.xls (script - XML_FROM_CELL_ARRAY)
COMMENT This script tests to use XML_FROM_CELL_ARRAY to generate various XMLs. COMMENT Prepare cell array using ARRAY_MERGE_STRING. ASSIGN $msg Hello World! ARRAY_MERGE_STRING $html_data html head title text() $msg body div text() $msg table rows() tr td 2 Cell 1 Cell 2 Cell 3 Cell 4 XML_FROM_CELL_ARRAY $html $html_data ASSIGN_VALUE $html dom_import_simplexml($html)->ownerDocument->saveHTML() ECHO HTML:\n{$html}\n COMMENT XML_FROM_CELL_ARRAY gets the cell array from the following rows. XML_FROM_CELL_ARRAY $uibinder ui:UiBinder xmlns:ui urn:ui:com.google.gwt.uibinder xmlns:g urn:import:com.google.gwt.user.client.ui g:HTMLPanel text() Hello World! ECHO UiBinder:\n{$uibinder->asXML()}\n XML_FROM_CELL_ARRAY $zuml window title My First ZK Application border normal label value Hello World! ECHO ZUML:\n{$zuml->asXML()}\n Output - console
HTML: <html><head><title>Hello World!</title></head><body><div>Hello World!</div><table><tr><td>Cell 1</td><td>Cell 2</td></tr><tr><td>Cell 3</td><td>Cell 4</td></tr></table></body></html> UiBinder: <?xml version="1.0"?> <ui:UiBinder xmlns:ui="urn:ui:com.google.gwt.uibinder" xmlns:g="urn:import:com.google.gwt.user.client.ui"><g:HTMLPanel>Hello World!</g:HTMLPanel></ui:UiBinder> ZUML: <?xml version="1.0"?> <window title="My First ZK Application" border="normal"><label value="Hello World!"/></window>
- Generate simple static web pages, like some pages in this site.
- Update a Word document - fill placeholders. [Example]
Input - example-Word-template.doc
<START_TABLE_INFO>Table: <TABLE_NAME>
Description: <TABLE_DESCRIPTION>
<END_COLUMN_INFO>Column Description <START_COLUMN_INFO><COLUMN_NAME> <COLUMN_DESCRIPTION>
<END_TABLE_INFO>Input - example.xls (script - fill Word template)
COMMENT This script demonstrates how to fill a template in a Word document. ASSIGN $document_path http://phpmycodegen.sourceforge.net/examples/example-Word-template.doc COMMENT The placeholders. STRING_ARRAY <TABLE_NAME> <TABLE_DESCRIPTION> $table_info_fields STRING_ARRAY <COLUMN_NAME> <COLUMN_DESCRIPTION> $column_info_fields COMMENT The input data. ARRAY_MERGE $table_info table1 The first table. ARRAY_MERGE $table_column_info[count($table_info) - 1] t1_column1 The first column in the first table. t1_column2 The second column in the first table. ARRAY_MERGE $table_info table2 The second table. ARRAY_MERGE $table_column_info[count($table_info) - 1] t2_column1 The first column in the second table. t2_column2 The second column in the second table. MSWORD_OPEN $document_path $document COMMENT Generate table info. CALL script_sub - fill Word template $document $document->Content "<START_TABLE_INFO>" "<END_TABLE_INFO>" $table_info_fields $table_info TRUE $result COMMENT Generate column info for each table. FOREACH $table_info $table_index CALL script_sub - fill Word template $document $document->Content "<START_COLUMN_INFO>" "<END_COLUMN_INFO>" $column_info_fields $table_column_info[$table_index] TRUE $result ECHO You will be asked to save the document.\n ELSE ECHO Failed to open the document: {$document_path}\n Input - example.xls (script_sub - fill Word template)
COMMENT This subroutine can be used to generate content based on a template in a Word document. COMMENT A Word.Document object The range to find the template The start marker of the template The end marker of the template The placeholders The data Indicate whether to remove the template after generation ARGV $document $content_range $template_start_marker $template_end_marker $fields $records $remove_template_flag FOREACH $records $record COMMENT Find and copy the template. MSWORD_FIND $content_range $template_start_marker $start_marker_ranges 1 LIST $start_marker_range $start_marker_ranges ELSE ECHO Cannot find template start marker: {$template_start_marker}\n RETURN MSWORD_FIND $content_range $template_end_marker $end_marker_ranges 1 LIST $end_marker_range $end_marker_ranges ELSE ECHO Cannot find template end marker: {$template_end_marker}\n RETURN $document->Range($start_marker_range->Start, $end_marker_range->Start)->Copy() COMMENT Paste an instance of the template before the start marker. ASSIGN_VALUE $template_range $document->Range($start_marker_range->Start, $start_marker_range->Start) $template_range->Paste() COMMENT Remove the start marker from the template instance. (The start marker is copied, so that table row can be copied properly.) MSWORD_FIND $template_range $template_start_marker $start_marker_ranges FOREACH $start_marker_ranges $start_marker_range ASSIGN $start_marker_range->Text COMMENT Fill the placeholders in the template instance. FOREACH $fields $field $field_index IF $field != '' MSWORD_FIND $template_range $field $field_ranges FOREACH $field_ranges $field_range ASSIGN $field_range->Text {$record[$field_index]} IF $remove_template_flag COMMENT Remove the template. MSWORD_FIND $content_range $template_start_marker $start_marker_ranges 1 LIST $start_marker_range $start_marker_ranges MSWORD_FIND $content_range $template_end_marker $end_marker_ranges 1 LIST $end_marker_range $end_marker_ranges $document->Range($start_marker_range->Start, $end_marker_range->End)->Delete() Output - example-Word-template.doc
Table: table1
Description: The first table.Column Description t1_column1 The first column in the first table. t1_column2 The second column in the first table.
Table: table2
Description: The second table.Column Description t2_column1 The first column in the second table. t2_column2 The second column in the second table. - Extract news headlines. [Example]
Input - site.xls (script - Home) - modified snippet
COMMENT Get the project news from the project news page. ASSIGN $project_news_page http://sourceforge.net/p/phpmycodegen/news/ FILE_LOAD $project_news_page $project_news_html HTML_LOAD $project_news_html $project_news_xml XML_XPATH $project_news_xml //div[contains(div/@class, "markdown_content")] $news_nodes FOREACH $news_nodes $news_node $news_date = date('Y-m-d', strtotime($news_node->em->span['title'])) ECHO {$news_date} - {$news_node->h3->a}\n Output - console
2015-03-01 - PhpMyCodeGenerator 2.25.0 Released 2013-11-10 - PhpMyCodeGenerator 2.24.0 Released 2013-03-07 - PhpMyCodeGenerator 2.23.0 Released 2013-02-17 - PhpMyCodeGenerator 2.22.0 Released 2012-12-30 - PhpMyCodeGenerator 2.21.1 Released ...
- Define regex-based grammar to parse custom doc comments. [Example]
Input - site.xls (script - Instructions) - modified snippet
COMMENT Define the grammar for parsing the doc comments for instructions. ARRAY_MERGE $instruction_doc_grammar INSTRUCTION_DOC DESCRIPTION EOL Syntax: SYNTAX_LINE \s* PARAMETERS REMARK DESCRIPTION .+ EOL \r?\n|\r SYNTAX_LINE + + SYNTAX_ELEMENT SYNTAX_ELEMENT \S+ PARAMETERS * PARAMETER_LINE PARAMETER_LINE - + PARAMETER EOL PARAMETER [^\r\n]+ REMARK .* GRAMMAR_COMPILE $instruction_doc_grammar $compiled_instruction_doc_grammar COMMENT Parse a doc comment. ASSIGN_TEXT $doc_comment /**
* This instruction uses the parseAgainstRegexGrammar() function to parse an input string against a compiled regular-expression-based grammar, a parse tree will be returned as a SimpleXML object for you to query against.
*
* Syntax: GRAMMAR_PARSE compiled_grammar input_string parse_tree_variable [start_symbol] [encoding]
* - compiled_grammar: the compiled grammar, returned by GRAMMAR_COMPILE.
* - input_string: the input string to parse, where variables will be parsed.
* - parse_tree_variable: a variable for returning the parse tree, which is a SimpleXML object.
* - start_symbol: the start symbol of the grammar, where variables will be parsed. (optional)
* - encoding: the encoding of the input string, where variables will be parsed. (optional)
*
* If success, then the instructions in the next column will be processed.
*/COMMENT Remove /**, * and */ from doc comment. FIND /^[ \\t]*(\\/\\*\\*|\\*\\/?) ?/m $doc_comment REPLACE GRAMMAR_PARSE $compiled_instruction_doc_grammar $doc_comment $doc_comment_xml XML_XPATH $doc_comment_xml /*/DESCRIPTION $description ASSIGN_VALUE $description trim($description[0]) ECHO Description:\n{$description}\n XML_XPATH $doc_comment_xml /*//SYNTAX_ELEMENT $syntax_elements IF count($syntax_elements) > 0 ECHO \nSyntax elements:\n FOREACH $syntax_elements $syntax_element ECHO - {$syntax_element}\n XML_XPATH $doc_comment_xml /*//PARAMETER $parameters IF count($parameters) > 0 ECHO \nParameters:\n FOREACH $parameters $parameter ECHO - {$parameter}\n XML_XPATH $doc_comment_xml /*/REMARK $remark ASSIGN_VALUE $remark trim($remark[0]) ECHO \nRemark:\n{$remark}\n Output - console
Description: This instruction uses the parseAgainstRegexGrammar() function to parse an input string against a compiled regular-expression-based grammar, a parse tree will be returned as a SimpleXML object for you to query against. Syntax elements: - GRAMMAR_PARSE - compiled_grammar - input_string - parse_tree_variable - [start_symbol] - [encoding] Parameters: - compiled_grammar: the compiled grammar, returned by GRAMMAR_COMPILE. - input_string: the input string to parse, where variables will be parsed. - parse_tree_variable: a variable for returning the parse tree, which is a SimpleXML object. - start_symbol: the start symbol of the grammar, where variables will be parsed. (optional) - encoding: the encoding of the input string, where variables will be parsed. (optional) Remark: If success, then the instructions in the next column will be processed.
- Automate a browser to test a web site. [Screencast]
- Control and test a command line program. [Example]
Input - example.xls (script - execute command) - modified snippet
C Use PROC_XXX instructions to run the script for testing control flow. PROC_OPEN php example.php example.json $pass = 0 WHILE TRUE PROC_STDOUT $output TRUE IF $output == '' BREAK $phase = 0 C Select the script for testing control flow. IF $pass == $phase++ IF_M /(\\w+): script - test control flow/ $output $match PROC_STDIN {$match[1]}\n TRUE $pass++ CONTINUE C Select the PhpMyCellScript interpreter. IF $pass == $phase++ IF_M /(\\w+): gen_script/ $output $match PROC_STDIN {$match[1]}\n TRUE $pass++ CONTINUE C Confirm to test control flow. IF $pass == $phase++ IF_M /y\\/n/ $output PROC_STDIN y\n TRUE $pass++ CONTINUE C Provide input to test Fibonacci. IF $pass == $phase++ IF_M /n = \\?/ $output PROC_STDIN 7\n TRUE $pass++ CONTINUE PROC_CLOSE Output - console
Worksheets: * a: echo - test b: uml_seq - test c: script_sub - entity&DAO d: script - my_testX e: script_sub - Fibonacci f: script - test control flow g: script - extract UiBinder field h: script - sort SQL i: script - update Javadoc j: script - show all data in db k: script - download&extract l: script - gen dir template m: script - doc outline & image n: script_sub - fill Word template o: script - fill Word template p: script - query XML q: script - test XML_FROM_VALUE r: script - XML_FROM_CELL_ARRAY s: script - HTML to cell array t: script - gen C++ CLI class u: script - test grammar v: script - test selenium w: script - prepare SELENIUM_DO x: script - clipboard by AutoIt y: script - get password z: script - execute command aa: script - calendar event ab: script - calendar event xml ac: script - download google doc ad: script - update phpmycodegen ae: script - inst SeleniumClient af: script - inst SeleniumServer ag: script - install AutoIt3 ah: script - install Zend Gdata ai: script - spreadsheet to json aj: script - run local script ak: script - run remote script al: script - Control Panel Please choose a worksheet (default: a): f Selected worksheet: script - test control flow Generators: a: gen_echo b: gen_uml_seq c: gen_json_from_excel * d: gen_script Please choose a generator (default: d): d Selected generator: gen_script Test control flow (y/n)? y /** * The my test id. Mapped to "my_test_id". */ @Column(name="my_test_id") private Long myTestId; /** * The my test amount. Mapped to "my_test_amount". */ @Column(name="my_test_amount") private BigDecimal myTestAmount; /** * The my test text. Mapped to "my_test_text". */ @Column(name="my_test_text") private String myTestText; /** * The my test unicode text. Mapped to "my_test_unicode_text". */ @Column(name="my_test_unicode_text") private String myTestUnicodeText; /** * The my test date. Mapped to "my_test_date". */ @Column(name="my_test_date") private Date myTestDate; ================================================================================n = ? 7 Fn: 13 Test ELSE support of FOREACH: OK! Start instruction counting... 1 2 3 4 5 6 7 8 9 10 Number of instructions run: 21