Add setHeaderImage() and setFooterImage(). Allows one per sheet only.

cfsimplicity · Apr 3, 2021 · c0d23b2 · c0d23b2
1 parent c234de2
commit c0d23b2
Show file tree

Hide file tree

Showing 17 changed files with 515 additions and 97 deletions.
diff --git a/README.md b/README.md
@@ -135,6 +135,8 @@ You will probably want to place the spreadsheet library files in a central locat
 * [removeSheetNumber](https://github.com/cfsimplicity/lucee-spreadsheet/wiki/removeSheetNumber)
 * [setCellRangeValue](https://github.com/cfsimplicity/lucee-spreadsheet/wiki/setCellRangeValue)
 * [setFitToPage](https://github.com/cfsimplicity/lucee-spreadsheet/wiki/setFitToPage)
+* [setFooterImage](https://github.com/cfsimplicity/lucee-spreadsheet/wiki/setFooterImage)
+* [setHeaderImage](https://github.com/cfsimplicity/lucee-spreadsheet/wiki/setHeaderImage)
 * [setReadOnly](https://github.com/cfsimplicity/lucee-spreadsheet/wiki/setReadOnly)
 * [setRepeatingColumns](https://github.com/cfsimplicity/lucee-spreadsheet/wiki/setRepeatingColumns)
 * [setRepeatingRows](https://github.com/cfsimplicity/lucee-spreadsheet/wiki/setRepeatingRows)
@@ -239,7 +241,7 @@ The automated tests require [TestBox 2.1](https://github.com/Ortus-Solutions/Tes
 
 ## Credits
 
-The code was originally adapted from the work of [TeamCfAdvance](https://github.com/teamcfadvance/). Ben Nadel's [POI Utility](https://github.com/bennadel/POIUtility.cfc) was also used as a basis for parts of the `read` functionality.
+The code was originally adapted from the work of [TeamCfAdvance](https://github.com/teamcfadvance/). Ben Nadel's [POI Utility](https://github.com/bennadel/POIUtility.cfc) was also used as a basis for parts of the `read` functionality. Header/Footer image functionality is based on code by [Axel Richter](https://stackoverflow.com/users/3915431/axel-richter).
 
 [JavaLoader](https://github.com/markmandel/JavaLoader) is by Mark Mandel.
 

diff --git a/Spreadsheet.cfc b/Spreadsheet.cfc
diff --git a/build/README.md b/build/README.md
@@ -0,0 +1,20 @@
+# Compiling java source and rebuilding the OSGi bundle
+
+Whenever either the java in `/src` or the jars in `/lib` change, the `lib-osgi.jar` file in the project root needs to be rebuilt.
+
+`task.cfc` is a CommandBox task runner which will compile the java source into a jar, add it to the `/lib` directory and then re-build the `lib-osgi.jar` bundle in the root.
+
+1. Edit `lib-osgi.mf` to increment the `Bundle-Version` and change the `Bundle-ClassPath` entries as required.
+2. Copy the new Bundle-Version to the `osgiLibBundleVersion` property at the top of `Spreadsheet.cfc`.
+3. Edit `task.cfc` if any of the `classpathDirectories` have changed
+4. Open CommandBox in the this `build` directory and execute:
+```
+task run
+```
+## Note on `poi-ooxml` jars
+
+The POI binary distribution includes two jar files:
+
+`poi-ooxml-lite-VERSION.jar` and `poi-ooxml-full-VERSION.jar`
+
+Only the *full* version is needed. Delete the lite version if copied to `/lib`
diff --git a/osgi-build/lib-osgi.mf → build/lib-osgi.mf b/osgi-build/lib-osgi.mf → build/lib-osgi.mf
@@ -2,25 +2,16 @@ Manifest-Version: 1.0
 Bundle-ManifestVersion: 2
 Bundle-Name: Lucee Spreadsheet Library Jars
 Bundle-SymbolicName: luceeSpreadsheet
-Bundle-Version: 5.0.0.1
+Bundle-Version: 5.0.0.2
 Bundle-ClassPath: commons-codec-1.15.jar,
  commons-collections4-4.4.jar,
  commons-compress-1.20.jar,
  commons-csv-1.8.jar,
  commons-io-2.8.0.jar,
  commons-math3-3.6.1.jar,
+ luceeSpreadsheet.jar,
  poi-5.0.0.jar,
  poi-ooxml-5.0.0.jar,
- poi-ooxml-lite-5.0.0.jar,
+ poi-ooxml-full-5.0.0.jar,
  SparseBitSet-1.2.jar,
  xmlbeans-4.0.0.jar
-Export-Package: org.apache.poi.*,
- org.openxmlformats.schemas.*,
- com.microsoft.schemas.*,
- org.w3.x2000.x09.xmldsig.*,
- org.etsi.uri.x01903.v13.*,
- org.apache.xmlbeans.*,
- org.apache.commons.compress.*,
- com.zaxxer.sparsebits.*,
- com.graphbuilder.*,
- org.apache.commons.csv.*
diff --git a/build/task.cfc b/build/task.cfc
@@ -0,0 +1,59 @@
+component{
+
+	/* Run me from CommandBox to compile source java, update the lib directory and build the lib-osgi.jar */
+
+	void function run(){
+		var rootPath = fileSystemUtil.resolvePath( "../" );
+		var srcPath = rootPath & "src/";
+		var libPath = rootPath & "lib/";
+		var classpathDirectories = [
+			srcPath
+			,libPath & "poi-ooxml-5.0.0.jar"
+			,libPath & "xmlbeans-4.0.0.jar"
+		];
+		var jarFileName = "luceeSpreadsheet.jar";
+		var classNames = [ "HeaderImageVML" ]; //allows for more source files in future
+		classNames.Each( function( className ){
+			var classFileName = className & ".class";
+			var javaSourceFilePath = srcPath & "luceeSpreadsheet/" & className & ".java";
+			compileSource( classpathDirectories, srcPath, javaSourceFilePath );
+		});
+		createNewJar( jarFileName );
+		replaceJarInLib( libPath, jarFileName );
+		recreateOsgiJar( rootPath, libPath );
+	}
+
+	private void function compileSource( required array classpathDirectories, required string srcPath, required string javaSourceFilePath ){
+		var destinationPath = getCWD() & "temp/";
+		var args = "-sourcepath #arguments.srcPath# -classpath #arguments.classpathDirectories.ToList( ';' )# #arguments.javaSourceFilePath# -d #destinationPath#";
+		execute name="javac" arguments=args timeout="5";
+	}
+
+	private void function createNewJar( required string jarFileName ){
+		var tempSourcePath = getCWD() & "temp/";
+		args = "--create --file #( getCWD() & arguments.jarFileName )# -C #tempSourcePath# .";//everything in temp
+		execute name="jar" arguments=args timeout="5";
+		DirectoryDelete( tempSourcePath, true );
+	}
+
+	private void function replaceJarInLib( required string libPath, required string jarFileName ){
+		var tempFilePath =  getCWD() & jarFileName;
+		var libJarPath = arguments.libPath & arguments.jarFileName;
+		deleteFileIfExists( libJarPath );
+		FileMove( tempFilePath, libJarPath );
+		deleteFileIfExists( tempFilePath );
+	}
+
+	private void function recreateOsgiJar( required string rootPath, required string libPath ){
+		var libOsgiPath = arguments.rootPath & "lib-osgi.jar";
+		deleteFileIfExists( libOsgiPath );
+		var manifestPath = getCWD() & "lib-osgi.mf";
+		args = "--create --file #libOsgiPath# --manifest #manifestPath# -C #arguments.libPath# .";//everything in libPath
+		execute name="jar" arguments=args timeout="5";
+	}
+
+	private void function deleteFileIfExists( required string path ){
+		if( FileExists( arguments.path ) ) FileDelete( arguments.path );
+	}
+
+}
diff --git a/lib-osgi.jar b/lib-osgi.jar
diff --git a/lib/luceeSpreadsheet.jar b/lib/luceeSpreadsheet.jar
diff --git a/lib/poi-ooxml-full-5.0.0.jar b/lib/poi-ooxml-full-5.0.0.jar
diff --git a/lib/poi-ooxml-lite-5.0.0.jar b/lib/poi-ooxml-lite-5.0.0.jar
diff --git a/osgi-build/README.md b/osgi-build/README.md
diff --git a/src/luceeSpreadsheet/HeaderImageVML.java b/src/luceeSpreadsheet/HeaderImageVML.java
@@ -0,0 +1,83 @@
+/**
+* Adapted from https://stackoverflow.com/questions/51077404/apache-poi-adding-watermark-in-excel-workbook/51103756#51103756
+**/
+package luceeSpreadsheet;
+
+import java.io.*;
+import org.apache.poi.openxml4j.opc.*;
+import org.apache.poi.ooxml.*;
+import org.apache.xmlbeans.*;
+
+import static org.apache.poi.ooxml.POIXMLTypeLoader.DEFAULT_XML_OPTIONS;
+
+public class HeaderImageVML extends POIXMLDocumentPart {
+
+  String rIdPic = "";
+  java.awt.Dimension imageDimension = null;
+  String position = "";
+
+  public HeaderImageVML(PackagePart part) {
+    super(part);
+  }
+
+  public HeaderImageVML setRIdPic(String rIdPic) {
+    this.rIdPic = rIdPic;
+    return this;
+  }
+
+  public HeaderImageVML setPosition(String position) {
+    this.position = position;
+    return this;
+  }
+
+  public HeaderImageVML setImageDimension(java.awt.Dimension imageDimension) {
+   this.imageDimension = imageDimension;
+   return this;
+  }
+
+  @Override
+  protected void commit() throws IOException {
+    PackagePart part = getPackagePart();
+    OutputStream out = part.getOutputStream();
+    try {
+      XmlObject doc = XmlObject.Factory.parse(
+        "<xml xmlns:v=\"urn:schemas-microsoft-com:vml\""
+        +" xmlns:o=\"urn:schemas-microsoft-com:office:office\""
+        +" xmlns:x=\"urn:schemas-microsoft-com:office:excel\">"
+        +" <o:shapelayout v:ext=\"edit\">"
+        +"  <o:idmap v:ext=\"edit\" data=\"1\"/>"
+        +" </o:shapelayout><v:shapetype id=\"_x0000_t75\" coordsize=\"21600,21600\" o:spt=\"75\""
+        +"  o:preferrelative=\"t\" path=\"m@4@5l@4@11@9@11@9@5xe\" filled=\"f\" stroked=\"f\">"
+        +"  <v:stroke joinstyle=\"miter\"/>"
+        +"  <v:formulas>"
+        +"   <v:f eqn=\"if lineDrawn pixelLineWidth 0\"/>"
+        +"   <v:f eqn=\"sum @0 1 0\"/>"
+        +"   <v:f eqn=\"sum 0 0 @1\"/>"
+        +"   <v:f eqn=\"prod @2 1 2\"/>"
+        +"   <v:f eqn=\"prod @3 21600 pixelWidth\"/>"
+        +"   <v:f eqn=\"prod @3 21600 pixelHeight\"/>"
+        +"   <v:f eqn=\"sum @0 0 1\"/>"
+        +"   <v:f eqn=\"prod @6 1 2\"/>"
+        +"   <v:f eqn=\"prod @7 21600 pixelWidth\"/>"
+        +"   <v:f eqn=\"sum @8 21600 0\"/>"
+        +"   <v:f eqn=\"prod @7 21600 pixelHeight\"/>"
+        +"   <v:f eqn=\"sum @10 21600 0\"/>"
+        +"  </v:formulas>"
+        +"  <v:path o:extrusionok=\"f\" gradientshapeok=\"t\" o:connecttype=\"rect\"/>"
+        +"  <o:lock v:ext=\"edit\" aspectratio=\"t\"/>"
+        +" </v:shapetype><v:shape id=\"" + position + "\" o:spid=\"_x0000_s1025\" type=\"#_x0000_t75\""
+        +"  style='position:absolute;margin-left:0;margin-top:0;"
+        +"width:" + (int)imageDimension.getWidth() + "px;height:" + (int)imageDimension.getHeight() + "px;"
+        +"z-index:1'>"
+        +"  <v:imagedata o:relid=\""+ rIdPic + "\"/>"
+        +"  <o:lock v:ext=\"edit\" rotation=\"t\"/>"
+        +" </v:shape></xml>"
+      );
+      doc.save(out, DEFAULT_XML_OPTIONS);
+      out.close();
+    } catch (Exception ex) {
+      ex.printStackTrace();
+    }
+  }
+
+ }
diff --git a/test/specs/addImage.cfm b/test/specs/addImage.cfm
@@ -1,17 +1,43 @@
 <cfscript>
 describe( "addImage", function(){
 
-	it( "Doesn't error when adding an image to a binary spreadsheet", function(){
+	it( "Doesn't error when adding an image to a spreadsheet", function(){
 		var imagePath = getTestFilePath( "test.png" );
-		var workbook = s.newXls();
-		s.addImage( workbook=workbook, filepath=imagePath, anchor="1,1,2,2" );
+		var workbooks = [ s.newXls(), s.newXlsx() ];
+		workbooks.Each( function( wb ) {
+			s.addImage( workbook=wb, filepath=imagePath, anchor="1,1,2,2" );
+			var imageData = ImageNew( "", 10, 10, "rgb", "blue" );
+			s.addImage( workbook=wb, imageData=imageData, imageType="png", anchor="1,2,2,3" );
+		});
 	});
 
-	it( "Doesn't error when adding an image to an XML spreadsheet", function(){
-		var imagePath = getTestFilePath( "test.png" );
-		var workbook = s.newXlsx();
-		s.addImage( workbook=workbook, filepath=imagePath, anchor="1,1,2,2" );
-	});
+	describe( "throws an exception if", function(){
+
+		beforeEach( function(){
+			variables.workbook = s.newXls();
+		});
+
+		it( "no image is provided", function(){
+			expect( function(){
+				s.addImage( workbook=workbook, anchor="1,1,2,2" );
+			}).toThrow( message="Invalid argument combination" );
+		});
+
+		it( "imageData is provided with no imageType", function(){
+			expect( function(){
+				var imageData = ImageRead( getTestFilePath( "test.png" ) );
+				s.addImage( workbook=workbook, imageData=imageData, anchor="1,1,2,2" );
+			}).toThrow( message="Invalid argument combination" );
+		});
+
+		it( "imageData is not a coldfusion image object", function(){
+			expect( function(){
+				var imageData = "I'm not an image";
+				s.addImage( workbook=workbook, imageData=imageData, imageType="png", anchor="1,1,2,2" );
+			}).toThrow( message="Invalid imageData" );
+		});
+
+	});	
 
 });	
 </cfscript>
diff --git a/test/specs/setFooter.cfm b/test/specs/setFooter.cfm
@@ -0,0 +1,20 @@
+<cfscript>
+describe( "setFooter", function(){
+
+	it( "adds text to the left, centre or right footer", function() {
+		makePublic( s, "getActiveSheetFooter" );
+		var workbooks = [ s.newXls(), s.newXlsx() ];
+		workbooks.Each( function( wb ) {
+			var leftText = "I'm on the left";
+			var centerText = "I'm in the middle";
+			var rightText = "I'm on the right";
+			s.setFooter( workbook=wb, leftFooter=leftText, centerFooter=centerText, rightFooter=rightText );
+			var footer = s.getActiveSheetFooter( wb );
+			expect( footer.getLeft() ).toBe( leftText );
+			expect( footer.getCenter() ).toBe( centerText );
+			expect( footer.getRight() ).toBe( rightText );
+		});
+	});
+
+});	
+</cfscript>
diff --git a/test/specs/setFooterImage.cfm b/test/specs/setFooterImage.cfm
@@ -0,0 +1,55 @@
+<cfscript>
+describe( "setFooterImage", function(){
+
+	it( "adds an image to the left, centre or right footer from a file path", function() {
+		makePublic( s, "getActiveSheetFooter" );
+		var imagePath = getTestFilePath( "test.png" );
+		var wb = s.newXlsx();
+		s.setFooterImage( wb, "left", imagePath );
+		var footer = s.getActiveSheetFooter( wb );
+		expect( footer.getLeft() ).toBe( "&G" );//Graphic
+		wb = s.newXlsx();
+		s.setFooterImage( wb, "center", imagePath );
+		footer = s.getActiveSheetFooter( wb );
+		expect( footer.getCenter() ).toBe( "&G" );
+		wb = s.newXlsx();
+		s.setFooterImage( wb, "right", imagePath );
+		footer = s.getActiveSheetFooter( wb );
+		expect( footer.getRight() ).toBe( "&G" );
+	});
+
+	it( "adds an image to the left, centre or right footer from a cfml image object", function() {
+		makePublic( s, "getActiveSheetFooter" );
+		var imageData = ImageNew( "", 10, 10, "rgb", "blue" );
+		var wb = s.newXlsx();
+		s.setFooterImage( wb, "left", imageData, "png" );
+		var footer = s.getActiveSheetFooter( wb );
+		expect( footer.getLeft() ).toBe( "&G" );//Graphic
+		wb = s.newXlsx();
+		s.setFooterImage( wb, "center", imageData, "png" );
+		footer = s.getActiveSheetFooter( wb );
+		expect( footer.getCenter() ).toBe( "&G" );
+		wb = s.newXlsx();
+		s.setFooterImage( wb, "right", imageData, "png" );
+		footer = s.getActiveSheetFooter( wb );
+		expect( footer.getRight() ).toBe( "&G" );
+	});
+
+	describe( "throws an exception if", function(){
+
+		it( "the workbook is not XLSX", function(){
+			expect( function(){
+				s.setFooterImage( s.newXls(), "left", getTestFilePath( "test.png" ) );
+			}).toThrow( message="Invalid spreadsheet type" );
+		});
+
+		it( "the position argument is invalid", function(){
+			expect( function(){
+				s.setFooterImage( s.newXls(), "wrong", getTestFilePath( "test.png" ) );
+			}).toThrow( message="Invalid footer position" );
+		});
+
+	});	
+
+});
+</cfscript>
diff --git a/test/specs/setHeader.cfm b/test/specs/setHeader.cfm
@@ -0,0 +1,20 @@
+<cfscript>
+describe( "setHeader", function(){
+
+	it( "adds text to the left, centre or right header", function() {
+		makePublic( s, "getActiveSheetHeader" );
+		var workbooks = [ s.newXls(), s.newXlsx() ];
+		workbooks.Each( function( wb ) {
+			var leftText = "I'm on the left";
+			var centerText = "I'm in the middle";
+			var rightText = "I'm on the right";
+			s.setHeader( workbook=wb, leftHeader=leftText, centerHeader=centerText, rightHeader=rightText );
+			var header = s.getActiveSheetHeader( wb );
+			expect( header.getLeft() ).toBe( leftText );
+			expect( header.getCenter() ).toBe( centerText );
+			expect( header.getRight() ).toBe( rightText );
+		});
+	});
+
+});
+</cfscript>