http://coldfusion.com logo
Join Slack
Powered by
Hi everyone <https://github.com/Leftbower/cfsprea...
# lucee
c
Hi everyone https://github.com/Leftbower/cfspreadsheet-lucee-5/ Our organization is looking to install the following extension, but we will want to edit the bits that don't work properly yet. Is there a way we can edit code and see the result, without having to re-install the extension every time, in Lucee Admin, we make an update? If we use this extension and improve it, I will make a PR, so every one can benefit from the changes, once I have finished my current contract. Thanks Charlie
z
that's old n crufty try https://github.com/cfsimplicity/spreadsheet-cfml it's a live project
🙂 1
c
Thanks for this 🙏
z
always check when a project was last updated 😉
👍 1
c
The good thing about 
cfspreadsheet
is that we won't have to change too much syntax, because it emulates the tag based format of ACF's 
cfspreadsheet
cfsimplicty
doesn't really emulate any of the syntax
z
cfspreadsheet
will be installed under your lucee-server folder, you can edit the files there directly
c
But when I edit:
Copy code
<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\lar\org\cfpoi\spreadsheet\Spreadsheet.cfc
The change is not present, when I reload the page?
Sorry. My mistake, it does.
That's great. 🙏 We might persevere with 
cfspreadsheet
and I will publish any changes to the: https://github.com/Leftbower/cfspreadsheet-lucee-5/ Once, my contract has finished... Thanks for your help @zackster
OK. Now I am confused. In order to install the extension, I added the 
.lex
file to;
Copy code
.CommandBox\server\9AC33D0DF8B8FA3A47AE7BDADE2130CB-cfspreadsheetLucee539166\lucee-5.3.9.166\WEB-INF\lucee-web\deploy
How does this talk to the 
cfspreadsheet
Coldfusion files in my webroot?
Maybe Lucee looks for the 
cfspreadsheet
folder in my webroot? But, in this case, the extension's root folder is not called:
Copy code
cfspreadsheet
It is called:
Copy code
cfspreadsheet-lucee-5-master
When you drop the 
.lex
file into:
Copy code
.CommandBox\server\9AC33D0DF8B8FA3A47AE7BDADE2130CB-cfspreadsheetLucee539166\lucee-5.3.9.166\WEB-INF\lucee-web\deploy
What actually happens? I cannot see how the 
.lex
file knows where the 
cfspreadsheet
folder is?
This is what is in my manifest file:
Copy code
Manifest-Version: 1.0
mapping-type: cfc
Bundle-Description: this is a cfc mapping generated by Lucee.
mapping-id: 1mz0ez85r04pz
Bundle-SymbolicName: 1mz0ez85r04pz
mapping-top-level: true
Require-Bundle: lucee.core
Bundle-ManifestVersion: 2
mapping-virtual-path: /cfspreadsheet
mapping-readonly: false
mapping-hidden: false
mapping-physical-first: true
Bundle-Name: cfspreadsheet
Bundle-Version: 1.0.0.1q4cgun7t1cgi
Are you saying there is a 
cfspreadsheet
folder somewhere under the server webroot folder, that commandbox creates at:
Copy code
C:\Users\cdesi\.CommandBox\server\9AC33D0DF8B8FA3A47AE7BDADE2130CB-cfspreadsheetLucee539166\lucee-5.3.9.166
z
i just tried it, all the tags are under 
C:\lucee\tomcat\lucee-server\context\library
message has been deleted
c
Let me check the library in the commandbox implementation...
OK. I can see the functions but not the CFC
There is a CFC called:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\lar\org\cfpoi\spreadsheet\Spreadsheet.cfc
z
message has been deleted
c
You bloody genius! 😊
z
but it's only 15k
message has been deleted
c
👍
Hmmm. Not sure edits will work inside a zip
z
I hate .lars with a passion
you might want to repackage the extension without one
c
That's a bit annoying. Why hasn't Lucee uncompresseed this file
z
it's delivered as a class
it will be uncompressed in the temp\compress dir
i'd repackage the extension if i was going to spend much time hacking on it https://docs.lucee.org/categories/extensions.html
you can just drop the .lex file into /deploy https://docs.lucee.org/guides/deploying-lucee-server-apps.html
c
So, are you saying I can repackage the folder without the 
lar
folder Where do I put the stuff that was in the 
lar
folder?
z
install my log viewer and bump the deploy.log level up to trace
message has been deleted
👍 1
c
I might just uninstall the extension and just use this as a custom tag in development
j
i'm using the same plugin, fwiw. don't remember why, at this point.
c
I am trying to just use it as a custom tag: application.cfc
Copy code
<cfscript>
component {
    this.name = 'cfspreadsheetLucee539166';
    this.sessionManagement = true;
    
    this.dbLocation = replace(getDirectoryFromPath( getCurrentTemplatePath() ),'\','/','all') & 'db/cfdocexamples';
	this.customTagsLocation = replace(getDirectoryFromPath( getCurrentTemplatePath() ),'\','/','all') & 'cfspreadsheet-lucee-5-master/extension/tags';
    
    this.datasources['cfdocexamples'] = {
	  class: 'org.apache.derby.jdbc.EmbeddedDriver'
	, connectionString: 'jdbc:derby:#this.dbLocation#;create=true;'
    };
	
	this.customtagpaths = [this.customTagsLocation];
	

    function onApplicationStart() {
    }

 }
</cfscript>
Error:
index.cfm:
Copy code
<cfoutput>

#Now()#

<!--- Read data from two datasource tables. ---> 
<cfquery name="courses" datasource="cfdocexamples" cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
  SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME 
  FROM COURSELIST 
</cfquery> 
  
<cfquery name="centers" datasource="cfdocexamples" cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
  SELECT * 
  FROM CENTERS 
</cfquery> 
      
<cfscript> 
  //Use an absolute path for the files. ---> 
  theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
  theFile=theDir & "courses.xls"; 
  //Create two empty ColdFusion spreadsheet objects. ---> 
  theSheet = SpreadsheetNew("CourseData"); 
  theSecondSheet = SpreadsheetNew("CentersData"); 
  //Populate each object with a query. ---> 
  SpreadsheetAddRows(theSheet,courses); 
  SpreadsheetAddRows(theSecondSheet,centers); 
</cfscript> 
  
<!--- Write the two sheets to a single file ---> 
<cfspreadsheet action="write" filename="#theFile#" name="theSheet" 
    sheetname="courses" overwrite=true> 
<cfspreadsheet action="write" filename="#theFile#" name="theSecondSheet"
    sheetname="centers" overwrite=true> 
  
<!--- Read all or part of the file into a spreadsheet object, CSV string, 
      HTML string, and query. ---> 
<cfspreadsheet action="read" src="#theFile#" sheetname="courses" name="spreadsheetData"> 
<cfspreadsheet action="read" src="#theFile#" sheet=1 rows="3,4" format="csv" name="csvData"> 
<cfspreadsheet action="read" src="#theFile#" format="html" rows="5-10" name="htmlData"> 
<cfspreadsheet action="read" src="#theFile#" sheetname="centers" query="queryData"> 
  
<h3>First sheet row 3 read as a CSV variable</h3> 
<cfdump var="#csvData#"> 
  
<h3>Second sheet rows 5-10 read as an HTML variable</h3> 
<cfdump var="#htmlData#"> 
  
<h3>Second sheet read as a query variable</h3> 
<cfdump var="#queryData#"> 
  
<!--- Modify the courses sheet. ---> 
<cfscript> 
  SpreadsheetAddRow(spreadsheetData,"03,ENGL,230,Poetry 1",8,1); 
  SpreadsheetAddColumn(spreadsheetData, 
  "Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced", 
  3,2,true); 
</cfscript> 
  
<!--- Write the updated Courses sheet to a new XLS file ---> 
<cfspreadsheet action="write" filename="#theDir#updatedFile.xls" name="spreadsheetData" 
    sheetname="courses" overwrite=true> 
<!--- Write an XLS file containing the data in the CSV variable. --->     
<cfspreadsheet action="write" filename="#theDir#dataFromCSV.xls" name="CSVData" 
    format="csv" sheetname="courses" overwrite=true>





</cfoutput>
message has been deleted
Lucee 
application.cfc
this.customTagPaths doesn't seem to work? Unless I have to restart the server after adding this variable?
I would have thought it would find the tag, even if the logic inside the tag, requires some more connection updates. I am essentially trying to convert a completely unusable extension [from the perspective of being able to update it efficiently] to a custom tag. So far: 1. I have moved the 
org
folder out of the 
lar
folder, into the webroot 2. I have added the this.customTagPaths variable to the 
application.cfc
I was hoping it would just work now?
Then I can just edit the extension files inside my webroot, and see the updates working on the fly.
Trying to edit it as an extension is an utter nightmare.
Hi @zackster I have created two screenshots to show you, how I have altered the extension file structure. A before and after... I am hoping that my after version, will allow me to edit the 
Spreadsheet.cfc
inside the following folder:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\components\org\cfpoi\spreadsheet
Can you tell me how I repackage the extension? I have read: https://docs.lucee.org/guides/lucee-5/extensions.html But it says that I should just zip up the:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\META-INF\MANIFEST.MF
Is this correct? I am not sure how a 1KB file becomes a 15,743KB file? Or maybe, I am meant to zip up the contents of:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension
But not the actual 
extension
folder itself?
Do you know what this does?
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\components\org\cfpoi\spreadsheet\spreadsheet_cfc$cf.class
Why can't I just read the java methods from:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\jars\cfspreadsheet_3.0.1.jar
Some progress 🙂 I have managed to install the extension using the new directory/file structure and everything works, but when I try and edit any of the files in:
Copy code
C:\Users\cdesi\.CommandBox\server\9AC33D0DF8B8FA3A47AE7BDADE2130CB-cfspreadsheetLucee539166\lucee-5.3.9.166\WEB-INF\lucee-server\context\components\org\cfpoi\spreadsheet\Spreadsheet.cfc
Nothing changes? Do, I need to clear the cache, somehow?
z
the component mapping is set in the admin to never i think?
message has been deleted
👍 1
c
It won't let me edit:
Maybe, there is a way to do it programmatically in the 
Application.cfc
?
I am also a little concerned by;
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\components\org\cfpoi\spreadsheet\spreadsheet_cfc$cf.class
I don't have a clue what is does or why it is there?
I could try this and see if it overwrites:
message has been deleted
Copy code
C:\Users\cdesi\.CommandBox\server\9AC33D0DF8B8FA3A47AE7BDADE2130CB-cfspreadsheetLucee539166\lucee-5.3.9.166\WEB-INF\lucee-server\context\lucee-server.xml
I think I need to restart the server?
z
once means once per request, but i seem to remember somehow all the context stuff is never for some reason?
c
I cannot believe that the only way to edit the code within an extension, that you have to do the following: 1. Make changes 2. Repackage extension 3. Uninstall extension 4. Install extension There must be a way to develop extensions efficiently?
z
for pure cfml, like with my logviewer / performance viewer, i hack on the files under the admin
as i said before the .lar makes everything way to complicated
c
But you shouldn't need to hack anything? Maybe the person who built the Lucee extension architecture, forgot about the practical stuff?
I love the idea behind extensions, but there needs to be a rethink, on how to create a development mode. Like, in Angular, we develop on 
localhost:4200
and then for production we build stuff into obfuscated JS.
z
are you talking about the cfc in the .lar or the function definitions?
c
There could be an application setting, so that we can use extensions in development mode: Application.cfc:
Copy code
this.extensions = [
 {
   id: 'unique_id',
   name: 'name_of_extension',
   path: 'absolute_path_to_extension'
 },
];
Then we can edit the extension files directly.
z
no, the extension is just configured in a way which doesn't lend itself to hacking
c
I have tried editing functions and tags in the WEB-INF, and this does not work either?
I have tried changing the 
lucee-server.xml
, but it doesn't change the caching policy?
z
the correct way is to set this up so it can be run when not installed for hacking
extensions are designed to be deployed and high performance
c
But, if you can't update them easily, what's the point of high performance?
the correct way is to set this up so it can be run when not installed for hacking
Could you expand a little
z
go and set it up so you can just run it under a normal folder
c
I don't understand how to do this? It seems that when the extension is installed, it wires up references to specifically named folders? So, if it is not installed, it just doesn't work. I tried treating 
cfspreadsheet
, like a custom tag, which is what is at the heart of the extension, but it doesn't work, because none of the functions can be called, due to referencing issues. I tried looping through the functions folder to make them globally accessible: Application.cfc
Copy code
component {
    this.name = 'cfspreadsheetLucee539166';
    this.sessionManagement = true;
 
 this.mappings['/org'] = getDirectoryFromPath(getCurrentTemplatePath()) & 'cfspreadsheet-lucee-5-master\org';
    
    this.dbLocation = replace(getDirectoryFromPath( getCurrentTemplatePath() ),'\','/','all') & 'db/cfdocexamples';
 this.customTagsLocation = getDirectoryFromPath( getCurrentTemplatePath() ) & 'cfspreadsheet-lucee-5-master\extension\tags';
 this.functionLocation = getDirectoryFromPath( getCurrentTemplatePath() ) & 'cfspreadsheet-lucee-5-master\extension\functions';
    
    this.datasources['cfdocexamples'] = {
   class: 'org.apache.derby.jdbc.EmbeddedDriver',
   connectionString: 'jdbc:derby:#this.dbLocation#;create=true;'
    };
 
 this.customtagpaths = [this.customTagsLocation];
 
    function onApplicationStart() {
    }
 
 function onRequestStart() {
   var local = StructNew();
   local.qGetFiles = DirectoryList(this.functionLocation,false,'query','*.cfm','asc','file');
   
   WriteDump(var=local.qGetFiles);
   
   cfloop(query=local.qGetFiles){
  cfinclude(template='./cfspreadsheet-lucee-5-master/extension/functions/' & local.qGetFiles.Name);
   }
 }

 }
But it doesn't work?
z
it's 
this.functionPaths
not 
this.functionLocation
https://luceeserver.atlassian.net/browse/LDEV-1995
c
I just chose this variable name randomly? Is it a native setting?
z
c
Holy cow!
z
c
Application.cfc
Copy code
component {
    this.name = 'cfspreadsheetLucee539166';
    this.sessionManagement = true;
 
 this.mappings['/org'] = getDirectoryFromPath(getCurrentTemplatePath()) & 'cfspreadsheet-lucee-5-master\org';
    
    this.dbLocation = replace(getDirectoryFromPath( getCurrentTemplatePath() ),'\','/','all') & 'db/cfdocexamples';
 this.customTagsLocation = getDirectoryFromPath( getCurrentTemplatePath() ) & 'cfspreadsheet-lucee-5-master\extension\tags';
 this.functionLocation = getDirectoryFromPath( getCurrentTemplatePath() ) & 'cfspreadsheet-lucee-5-master\extension\functions';
 
 this.functionpaths = [];
    
    this.datasources['cfdocexamples'] = {
   class: 'org.apache.derby.jdbc.EmbeddedDriver',
   connectionString: 'jdbc:derby:#this.dbLocation#;create=true;'
    };
 
 this.customtagpaths = [this.customTagsLocation];


    function onApplicationStart() {
    }
 
 function onRequestStart() {
   var local = StructNew();
   local.qGetFiles = DirectoryList(this.functionLocation,false,'query','*.cfm','asc','file');
   
   cfloop(query=local.qGetFiles){
  local.functionPath = local.qGetFiles.Directory & '\' & local.qGetFiles.Name;
  ArrayAppend(this.functionpaths, local.functionPath);
   }
   
      WriteDump(var=this.functionpaths, label='this.functionpaths');
 }

 }
And, still none of the functions can be found?
index.cfm
Copy code
<cfoutput>

<!---<cfinclude template='cfspreadsheet-lucee-5-master/extension/functions/SpreadsheetNew.cfm' />--->

#Now()#

<!--- Read data from two datasource tables. ---> 
<cfquery name="courses" datasource="cfdocexamples" cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
  SELECT CORNUMBER,DEPT_ID,COURSE_ID,CORNAME 
  FROM COURSELIST 
</cfquery> 
  
<cfquery name="centers" datasource="cfdocexamples" cachedwithin="#CreateTimeSpan(0, 6, 0, 0)#"> 
  SELECT * 
  FROM CENTERS 
</cfquery> 
      
<cfscript> 
  //Use an absolute path for the files. ---> 
  theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
  theFile=theDir & "courses.xls"; 
  //Create two empty ColdFusion spreadsheet objects. ---> 
  theSheet = SpreadsheetNew("CourseData"); 
  theSecondSheet = SpreadsheetNew("CentersData"); 
  //Populate each object with a query. ---> 
  SpreadsheetAddRows(theSheet,courses); 
  SpreadsheetAddRows(theSecondSheet,centers); 
</cfscript> 
  
<!--- Write the two sheets to a single file ---> 
<cf_spreadsheet action="write" filename="#theFile#" name="theSheet" 
    sheetname="courses" overwrite=true> 
<cf_spreadsheet action="write" filename="#theFile#" name="theSecondSheet"
    sheetname="centers" overwrite=true> 
  
<!--- Read all or part of the file into a spreadsheet object, CSV string, 
      HTML string, and query. ---> 
<cf_spreadsheet action="read" src="#theFile#" sheetname="courses" name="spreadsheetData"> 
<cf_spreadsheet action="read" src="#theFile#" sheet=1 rows="3,4" format="csv" name="csvData"> 
<cf_spreadsheet action="read" src="#theFile#" format="html" rows="5-10" name="htmlData"> 
<cf_spreadsheet action="read" src="#theFile#" sheetname="centers" query="queryData"> 
  
<h3>First sheet row 3 read as a CSV variable</h3> 
<cfdump var="#csvData#"> 
  
<h3>Second sheet rows 5-10 read as an HTML variable</h3> 
<cfdump var="#htmlData#"> 
  
<h3>Second sheet read as a query variable</h3> 
<cfdump var="#queryData#"> 
  
<!--- Modify the courses sheet. ---> 
<cfscript> 
  SpreadsheetAddRow(spreadsheetData,"03,ENGL,230,Poetry 1",8,1); 
  SpreadsheetAddColumn(spreadsheetData, 
  "Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced", 
  3,2,true); 
</cfscript> 
  
<!--- Write the updated Courses sheet to a new XLS file ---> 
<cf_spreadsheet action="write" filename="#theDir#updatedFile.xls" name="spreadsheetData" 
    sheetname="courses" overwrite=true> 
<!--- Write an XLS file containing the data in the CSV variable. --->     
<cf_spreadsheet action="write" filename="#theDir#dataFromCSV.xls" name="CSVData" 
    format="csv" sheetname="courses" overwrite=true>





</cfoutput>
message has been deleted
I have managed to resolve the error by looping the:
Copy code
<cfinclude>
In the:
Copy code
index.cfm
Not sure why you can't execute 
cfinclude
inside the 
application.cfc
? Now I am getting this:
I reckon I may need to use 
javaloader
on:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\jars\cfspreadsheet_3.0.1.jar
This must be another job that the extension install, does automatically?
I resolved this one by adding the 
cfspreadsheet_3.0.1.jar
to:
Copy code
C:\Users\cdesi\.CommandBox\server\9AC33D0DF8B8FA3A47AE7BDADE2130CB-cfspreadsheetLucee539166\lucee-5.3.9.166\WEB-INF\lucee-server\bundles
Now we are back to the function not found errors:
This is why I tried to loop out the function paths in the 
application.cfc
, so that they are available globally, but Lucee doesn't like this strategy?
z
why this.functionpaths = []; ?
c
I like to set a default value at the top
When I take it out at the top, I get:
z
paths not files, did you try
Copy code
this.functionpaths = [getDirectoryFromPath( getCurrentTemplatePath() ) & 'cfspreadsheet-lucee-5-master\extension\functions'];
👍 1
c
Brilliant. You've cracked it.
It works now...
Now, I can work on fixing some datatype stuff in the POI 
spreadsheet.cfc
z
cool!
🙏 1
c
Well. That's good news, but, as you say, it is quite hacky! 😅
z
and now that you're working in a normal dir, you could add testbox tests if you like
👍 1
c
Actually, its not that hacky! Here is the final `application.cfc`:
Copy code
component {
    this.name = 'cfspreadsheetLucee539166';
    this.sessionManagement = true;
    
    this.dbLocation = replace(getDirectoryFromPath( getCurrentTemplatePath() ),'\','/','all') & 'db/cfdocexamples';
 this.customTagsLocation = getDirectoryFromPath( getCurrentTemplatePath() ) & 'cfspreadsheet-lucee-5-master\extension\tags';
 this.functionpaths = [getDirectoryFromPath( getCurrentTemplatePath() ) & 'cfspreadsheet-lucee-5-master\extension\functions'];    
    
    this.datasources['cfdocexamples'] = {
   class: 'org.apache.derby.jdbc.EmbeddedDriver',
   connectionString: 'jdbc:derby:#this.dbLocation#;create=true;'
    };
 
 this.customtagpaths = [this.customTagsLocation];

 }
And transfer any 
jars
to:
Copy code
C:\Users\cdesi\.CommandBox\server\9AC33D0DF8B8FA3A47AE7BDADE2130CB-cfspreadsheetLucee539166\lucee-5.3.9.166\WEB-INF\lucee-server\bundles
Oh and change references of:
Copy code
<cfspreadsheet>
To:
Copy code
<cf_spreadsheet>
z
c
Yes. That works!
So cleaning this up: application.cfc
Copy code
component {

    this.functionpaths = [path_to_cfspreadsheet_functions_directory];    
    this.customtagpaths = [path_to_cfspreadsheet_extension_tags_directory;
    this.javasettings.bundle = [path_to_cfspreadsheet_extension_jars_directory];

 }
I am going to try and find a solution for the:
Copy code
cf_
Issue? We should be able to access to do this:
Copy code
<cfspreadsheet>
Although, I can edit all the functions and tags, edits to:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\components\org\cfpoi\spreadsheet\Spreadsheet.cfc
Do not get processed?
I am wondering whether I can refresh:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\jars\cfspreadsheet_3.0.1.jar
I think I need to refresh this jar, each time I make an edit to the POI cfc?
z
there is a poi extension
move the poi cfc out of the jar
👍 1
c
Thanks
Is there any documentation on POI extension? I have installed it...
z
just makes the poi libs available
👍 1
c
cfsimplicity doesn't really emulate any of the syntax
Spreadsheet CFML certainly doesn't support the 
<cfspreadsheet>
tag itself, but it's not hard to rewrite using 
Spreadsheet.read()
and 
Spreadsheet.write()
. It supports all of the ACF spreadsheet functions with mostly the same arguments so you can just change 
SpreadsheetAddRows(workbook, data)
to 
Spreadsheet.addRows(workbook, data)
.
org\cfpoi\spreadsheet\Spreadsheet.cfc
was actually my starting point back in 2015 and I've spent the last 7 years fixing and expanding what it can do. It now runs on the very latest Apache POI v5 and with a wide range of additional functionality and options. I understand you'd prefer not to modify your code, but it's likely to take less time than you've already spent wrangling the extension.
Here's how you might re-write the code you posted above:
Copy code
<cfscript>
  Spreadsheet = New spreadsheet.SpreadSheet()
  //Use an absolute path for the files. ---> 
  theDir=GetDirectoryFromPath(GetCurrentTemplatePath()); 
  theFile=theDir & "courses.xls"; 
  //Write a single workbook with 2 populated sheets
  theSheet = Spreadsheet.new("courses");
  Spreadsheet.addRows(theSheet,courses);
  Spreadsheet.createSheet(theSheet,"centers");
  Spreadsheet.setActiveSheet(theSheet,"centers");
  Spreadsheet.addRows(theSheet,centers);
  Spreadsheet.write(theSheet,theFile,true);
  //Read all or part of the file into a spreadsheet object, CSV string, HTML string, and query
  spreadsheetData = Spreadsheet.read(src=theFile,sheetName="courses");
  csvData=Spreadsheet.read(src=theFile,sheetNumber=1,rows="3,4",format="csv");
  htmlData=Spreadsheet.read(src=theFile,rows="5-10",format="html");
  queryData=Spreadsheet.read(src=theFile,sheetName="centers",format="query");
</cfscript> 
  
<h3>First sheet row 3 read as a CSV variable</h3> 
<cfdump var="#csvData#"> 
  
<h3>Second sheet rows 5-10 read as an HTML variable</h3> 
<cfdump var="#htmlData#"> 
  
<h3>Second sheet read as a query variable</h3> 
<cfdump var="#queryData#"> 
  
<!--- Modify the courses sheet. ---> 
<cfscript> 
  Spreadsheet.setActiveSheet(theSheet,"courses")
  Spreadsheet.addRow(spreadsheetData,"03,ENGL,230,Poetry 1",8,1);
  Spreadsheet.addColumn(spreadsheetData, 
  "Basic,Intermediate,Advanced,Basic,Intermediate,Advanced,Basic,Intermediate,Advanced", 
  3,2,true);
  Spreadsheet.write(spreadsheetData,"#theDir#updatedFile.xls",true);
  Spreadsheet.writeToCsv(spreadsheetData,"#theDir#dataFromCSV.xls",true)
</cfscript>
👍 1
c
The thing is, we are doing a migration from ACF11 to the latest version of Lucee. If we were creating a new application, we would definitely use 
cfsimplicity
. To be honest, we haven't come to a final decision yet. We are just researching the best way to proceed. Anyway, thanks for all the information. I will pass this on to my manager. 🙏
👍 1
@zackster There is no 
poi.cfc
in the 
.jar
file:
Maybe you mean:
Copy code
poi-3.15.jar
z
split em out
c
OK. You mean, I need to split:
Copy code
cfspreadsheet-lucee-5-master\extension\jars\cfspreadsheet_3.0.1.jar ->  poi-3.15.jar
Into its constituent jars?
z
no cfcs in jars or lar files
c
Sorry, you've completely lost me? 😬
The other thing I don't understand, is what does the spreadsheet class do:
Copy code
C:\domains\<http://cfspreadsheet-lucee5-3-9_166.com|cfspreadsheet-lucee5-3-9_166.com>\wwwroot\cfspreadsheet-lucee-5-master\extension\components\org\cfpoi\spreadsheet\spreadsheet_cfc$cf.class
It is not referenced in the code base, anywhere?
z
that's just a precompiled cfc class
that's same as the spreadsheet.cfc (just pre compiled)
c
I have never seen a precompiled CFC class, before?
z
just rip the cfc out and never worry about that again
c
You mean:
Copy code
extension\components\org\cfpoi\spreadsheet\Spreadsheet.cfc
z
yup
c
Hmmm. But surely nothing will work?
This is like the main bit of the extension?
z
so, you just need to update the references to use the right path?
then you're back in good old normal cfml land, none of this packaging crap to worry about
c
I would really appreciate it, if you could just make the update and send the zip back to me? I am bit lost, to be honest? If it will take too long, don't worry. 🙏 Thanks
Just uploaded a much smaller version?
z
c
Thanks Zac. I am just setting it up now using commandBox
OK. I can now edit the 
spreadsheet.cfc
I see what you mean now. Because I placed the org folder in the component folder [which I shall need to do when I come to package the extension], the references were incorrect. The only error I had to fix was for:
Copy code
this.javasettings.bundle = [
    expandpath(".\jars")
];
I had to copy the:
Copy code
C:\domains\<http://cfspreadsheet-zac-lucee5-3-9_166.com|cfspreadsheet-zac-lucee5-3-9_166.com>\wwwroot\jars\cfspreadsheet_3.0.1.jar
To:
Copy code
C:\Users\cdesi\.CommandBox\server\FF98D9E0BAACD295A1A469616ACBE222-cfspreadsheetZacLucee539166\lucee-5.3.9.166\WEB-INF\lucee-server\bundles
Anyway, I now feel I have an extension that I can edit properly. 👍 Thank you so much for all your help. I have no idea how you know all this stuff! Thank god you do 🙂
z
Ah the jars should be explicit, not the folder. It worked coz I had the extension installed
I'm the community manager for lucee, it's my job to hopefully understand all things lucee
🙏 1
As you wanna hack on this and the old repo is dead, I'd suggest you create a new repo with a link back to the old one etc
I can setup GitHub actions to do CI
👍 1
c
Yes. I will create a new repo and continue the improvements. That is, if our organization decides to invest more time in 
cfspreafsheet
It will all come down to how many references we use in the codebase. I haven’t actually got access to the codebase yet [after 5 weeks: don’t ask 😵‍💫] If we decide to use 
cfsimplify
, I may update 
cfspreadsheet
in my own time.
https://github.com/charlesr1971/cfspreadsheet-lucee-5 New repo with a first commit that sorts out an error that gets thrown when:
Copy code
cfspreadsheet-lucee-5/lar/org/cfpoi/spreadsheet
Encounters an empty string. The fix is based on: https://stackoverflow.com/questions/43005125/using-ben-nadel-poi-to-read-excel I will talk to 
LeftBower
about whether he can add a link on his repo to my repo. It seems like he no longer maintains this repository? https://github.com/Leftbower/cfspreadsheet-lucee-5
No more errors:
2 Views
Open in Slack
PreviousNext
Powered by