# Digital Signatures DsExcel allows you to work with digital signatures in a worksheet. Learn more in DsExcel docs. ## Content Digital signatures are the proof of a document's authenticity. A digitally signed document assures that it has been created by the signer and has not been changed in any way. DsExcel allows users to add digital signatures to Excel spreadsheets to make them authentic and easier to validate. ## Signature Lines Signature lines act as a signature placeholder for digital signatures. They can be added to worksheet as signature line shapes which can be signed further. ### Add Signature Line The **addSignatureLine** method of **ISignatureSet** interface adds signature lines in a worksheet. You can also add information about the intended signer and instructions for the signer by using various methods of **ISignatureSetup** interface. When the workbook is opened again or sent to the intended signer as an Excel file, the signature line can be seen along with a notification that their signature is requested. Refer to the following example code to add signature line in a worksheet. ```Java Workbook workbook = new Workbook(); ISignature signature = workbook.getSignatures().addSignatureLine(workbook.getActiveSheet(), 100.0, 50.0); // Add Signature lines ISignatureSetup setup = signature.getSetup(); setup.setShowSignDate(false); setup.setAllowComments(false); setup.setSigningInstructions("Please check the content before signing."); setup.setSuggestedSigner("Shinzo Nagama"); setup.setSuggestedSignerEmail("shinzo.nagama@ea.com"); setup.setSuggestedSignerLine2("Commander (Balanced)"); // Save to an excel file workbook.save("AddSignatureLines.xlsx"); ``` The below image shows the signature lines in Excel: ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/signaturelines.png) ### Copy Signature Lines You can copy a signature line to another range of worksheet or to another worksheet by using any of the below: * Duplicate signature line - By using **duplicate** method of **IShape** interface * Copy signature line's cell range - By using **copy** method of **IRange** interface * Copy worksheet containing signature line - By using **copy** method of **IWorksheet** interface Refer to the following example code to copy a signature line to another range and another worksheet. ```Java // Copy signature line with Range.Copy IRange srcRange = activeSheet.getRange("A1:I15"); IRange destRange = activeSheet.getRange("A16:I30"); srcRange.copy(destRange); // Copy signature line with Shape.Duplicate signature.getSignatureLineShape().duplicate(); // Copy signature line with Worksheet.Copy activeSheet.copy(); ``` ### Delete Signature Lines You can delete a signature line by using any of the below: * Delete signature line - By using **delete** method of **ISignature** interface * Delete shape associated with signature line - By using **delete** method of **IShape** interface Refer to the following example code to delete signature line in a worksheet. ```Java // Create a new signature line and delete with Signature.Delete ISignature signatureForTest = newSignatureLine.call(); signatureForTest.delete(); // Create a new signature line and delete with Shape.Delete signatureForTest = newSignatureLine.call(); IShape signatureLineShape = signatureForTest.getSignatureLineShape(); signatureLineShape.delete(); ``` ### Move Signature Lines Refer to the following example code to move signature lines to another range or a worksheet. ```Java // Move signature line IShape signatureLineShape = signatureShinzo.getSignatureLineShape(); signatureLineShape.setTop(signatureLineShape.getTop() + 100); signatureLineShape.setLeft(signatureLineShape.getLeft() + 50); ``` ### List Signature Lines Refer to the following example code to list signature lines in a worksheet. ```Java // Add first signature line ISignature signatureShinzo = signatures.addSignatureLine(activeSheet, 100.0, 50.0); ISignatureSetup setup1 = signatureShinzo.getSetup(); setup1.setShowSignDate(false); setup1.setAllowComments(false); setup1.setSigningInstructions("Please check the content before signing."); setup1.setSuggestedSigner("Shinzo Nagama"); setup1.setSuggestedSignerEmail("shinzo.nagama@ea.com"); setup1.setSuggestedSignerLine2("Commander (Balanced)"); ISignature signatureKenji = signatures.addSignatureLine(activeSheet, 100.0, 350.0); ISignatureSetup setup2 = signatureKenji.getSetup(); setup2.setShowSignDate(true); setup2.setAllowComments(true); setup2.setSigningInstructions("Please check the content before signing!"); setup2.setSuggestedSigner("Kenji Tenzai"); setup2.setSuggestedSignerEmail("kenji.tenzai@ea.com"); setup2.setSuggestedSignerLine2("Commander (Mecha)"); // List signatures with indexes for (int i = 0; i < signatures.getCount(); i++) { ISignature signature = signatures.get(i); // change SuggestedSigner if (i == 0) signature.getSetup().setSuggestedSigner("Shinzo Nagama 123"); // change SuggestedSignerLine2 if (i == 1) signature.getSetup().setSuggestedSignerLine2("Commander (Mecha 1234)"); } ``` The **getSignatureLineShape** method in **ISignature** interface can be used while using signature line as a shape. Its members and their behavior is elaborated in the below table: | **SignatureLineShape members** | **Get or Call** **Behavior** | **Set Behavior** | | -------------------------- | -------------------- | ------------ | | Adjustments | Supported | #N/A | | Adjustments.Count | Supported | #N/A | | Adjustments.Item | Not Supported | Not Supported | | Adjustments.GetEnumerator | Not Supported | #N/A | | AutoShapeType | Supported | Not Supported | | BottomRightCell | Supported | #N/A | | Chart | Not Supported | #N/A | | Connector | Supported | #N/A | | ConnectorFormat | Not Supported | #N/A | | Fill | Not Supported | #N/A | | GroupItems | Not Supported | #N/A | | HasChart | Supported | #N/A | | Hyperlink | Not Supported | #N/A | | IsPrintable | Supported | Supported | | Line | Not Supported | #N/A | | Locked | Supported | Supported | | Name | Supported | Supported | | Parent | Supported | #N/A | | ParentGroup | Not Supported | #N/A | | PictureFormat | Supported | #N/A | | PictureFormat.ColorType | Supported | Supported | | PictureFormat.Brightness | Supported | Supported | | PictureFormat.Contrast | Supported | Supported | | PictureFormat.Crop | Not Supported | #N/A | | PictureFormat.CropLeft, CropTop,
CropRight and CropBottom | Not Supported | Not Supported | | Placement | Supported | Supported | | Rotation | Supported | Not Supported | | TextFrame | Not Supported | #N/A | | ThreeD | Not Supported | #N/A | | Title | Not Supported | Not Supported | | TopLeftCell | Supported | #N/A | | Left, Top, Right and Bottom | Supported | Supported | | Type | Supported | Supported | | Transparency | Not Supported | Not Supported | | Ungroup | Not Supported | #N/A | | Visible | Supported | Supported | | ZOrderPosition | Supported | Supported | The signature lines can also be exported to PDF documents. Refer [Export Signature Lines](/document-solutions/java-excel-api/docs/online/ManageFileOperations/ExporttoaPDFFile/export-signature-lines). ## Add Digital Signatures Digital signatures can be added to Excel spreadsheet by signing the signature lines using a signing certificate which proves signer's identity. Please follow the steps mentioned in [Generate Certificate document](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/templates/generate_certificate.docx) to generate the certificate file (.pfx). The **sign** method of **ISignature** interface can be used to add digital signatures. In order to commit signatures, the workbook should be saved with xlsx or xlsm extension. A workbook containing digital signatures is 'marked as final' to discourage editing. Refer to the following example code to add digital signatures in a worksheet. ```Java // Create a new workbook Workbook workbook = new Workbook(); ISignature signature = workbook.getSignatures().addSignatureLine(workbook.getActiveSheet(), 100.0, 50.0); ISignatureSetup setup = signature.getSetup(); setup.setShowSignDate(true); setup.setAllowComments(true); setup.setSigningInstructions(""); setup.setSuggestedSigner(""); setup.setSuggestedSignerEmail("example@microsoft.com"); setup.setSuggestedSignerLine2(""); SignatureDetails details = new SignatureDetails(); details.setAddress1(""); details.setAddress2("
"); details.setSignatureComments("Final"); details.setCity(""); details.setStateOrProvince(""); details.setPostalCode(""); details.setCountryName(""); details.setClaimedRole(""); details.setCommitmentTypeDescription("Approved"); details.setCommitmentTypeQualifier("Final"); KeyStore ks = KeyStore.getInstance("pkcs12"); String password = "test@123"; char[] passwordChars = password.toCharArray(); String pfxFileKey = "DsExcelTest.pfx"; InputStream pfxStrm = new FileInputStream(pfxFileKey); ks.load(pfxStrm, passwordChars); System.out.println(Collections.list(ks.aliases()).size()); signature.sign(ks, password, "John Williams", details); workbook.save("SignSignatureLines.xlsx"); ``` The below image shows digital signature in Excel: ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/digitalsignatures.png) ## Add Non Visible Signatures You can also add invisible digital signatures to a workbook by using **addNonVisibleSignature** method of **ISignatureSet** interface. The non visible digital signatures do not appear in any worksheet. However, they can be viewed by clicking ｀View Signatures¨ dialog in Excel. Refer to the following example code to add non visible signatures in a workbook. ```Java // Create a new workbook Workbook workbook = new Workbook(); ISignature signature = workbook.getSignatures().addNonVisibleSignature(); SignatureDetails details = new SignatureDetails(); details.setAddress1(""); details.setAddress2("
"); details.setSignatureComments("Final"); details.setCity(""); details.setStateOrProvince(""); details.setPostalCode(""); details.setCountryName(""); details.setClaimedRole(""); details.setCommitmentTypeDescription("Approved"); details.setCommitmentTypeQualifier("Final"); KeyStore ks = KeyStore.getInstance("pkcs12"); String password = "test@123"; char[] passwordChars = password.toCharArray(); String pfxFileKey = "DsExcelTest.pfx"; InputStream pfxStrm = new FileInputStream(pfxFileKey); ks.load(pfxStrm, passwordChars); signature.sign(ks, password, details); // Save to an excel file workbook.save("AddInvisibleSignatures.xlsx"); ``` ## Countersign Signatures A digitally signed workbook becomes read-only. When it is opened again in DsExcel, its digital signatures must be preserved before closing it. To achieve this: ### Countersign the Workbook A digitally signed workbook should be countersigned if it is opened and any modification is done to it. Otherwise, the existing signatures are removed after saving the workbook as xlsx or xlsm. The **countersign** method of **ISignature** interface can be used to countersign a signature using the same certificate. Refer to the following example code to open a digitally signed workbook and countersign it after modifying the worksheet. ```Java // Open a digitally signed workbook workbook.open("signsignaturelines.xlsx"); // Modify workbook.getWorksheets().get(0).getRange("A1").setValue("Modified"); // Countersign workbook.getSignatures().get(0).countersign(ks, password); // Save to an excel file workbook.save("CounterSignSignatureLines.xlsx"); ``` ### Open the Workbook in Digital Signature Only Mode A digitally signed workbook can be opened in digital signature only mode by using **setDigitalSignatureOnly** method in **XlsxOpenOptions** class. In this mode, you can perform the following operations while preserving existing signatures: * Sign existing signature lines * Remove signatures from signed signature lines * Add and Remove non visible signatures Refer to the following example code to open a digitally signed workbook in digital signature only mode and add non visible signatures to it. ```Java workbook.Open("signsignaturelines.xlsx"); // Use DigitalSignatureOnly mode, because the workbook was already signed. // If you don't open it with digital signature only mode, // all existing signatures will be removed after saving the workbook. XlsxOpenOptions openOption = new XlsxOpenOptions(); openOption.setDigitalSignatureOnly(true); // Add signature to this workbook ISignature signature = workbook.getSignatures().addNonVisibleSignature(); signature.sign(ks, password, details); // Commit signatures workbook.save("AddNonVisibleSignatureToSignedWorkbook.xlsx"); ``` ## Verify Digital Signatures DsExcel allows you to verify digital signatures by using **getIsValid** method of **ISignature** interface. Refer to the following example code to verify digital signatures in a signed workbook. ```Java // Create a new workbook Workbook workbook = new Workbook(); workbook.open("signsignaturelines.xlsx"); ISignatureSet signatures = workbook.getSignatures(); boolean signed = false; boolean valid = false; X509Certificate certificate = null; // Verify the first signature for (ISignature signature : signatures) { if (signature.getIsSigned()) { // Save the result in locals. You can print them later. signed = true; certificate = signature.getDetails().getSignatureCertificate(); valid = signature.getIsValid(); break; } } // Verify the first certificate boolean certificateIsValid = true; // Check expiration date and start date try { certificate.checkValidity(); } catch (CertificateExpiredException e) { certificateIsValid = false; return; } catch (CertificateNotYetValidException e) { certificateIsValid = false; return; } ``` ## Remove Digital Signatures DsExcel allows you to remove digital signatures from a signed signature line by using **delete** method of **ISignature** interface. The signature line is retained but can be deleted separately (as explained above). Refer to the following example code to delete digital signatures from signed signature line in a workbook. ```Java // Create a new workbook Workbook workbook = new Workbook(); // This file contains 1 signed signature line and // a not signed signature line. workbook.open("signsignaturelines.xlsx"); // Use DigitalSignatureOnly mode, because the workbook was already signed. // If you don't open it with digital signature only mode, // all existing signatures will be removed after saving the workbook. XlsxOpenOptions openOption = new XlsxOpenOptions(); openOption.setDigitalSignatureOnly(true); // Remove signature of signed signature line. for (ISignature signature : workbook.getSignatures()) { if (signature.getIsSignatureLine() && signature.getIsSigned()) { // Remove digital signature. // The signature line will not be removed from the SignatureSet // in digital signature only mode. // Because signature lines are shapes. signature.delete(); break; } //commit signatures workbook.save("DeleteDigitalSignature.xlsx"); ``` ## Important Information ### Version Information The signature formats observed in this feature have been tested with following versions: **Target Office version** The office version used to observe file structures when developing this feature is Office 365, build 16.0.12228. This version can be observed by using SignatureDetails.getApplicationVersion method. **Minimum Office version** The minimum Office version required to open the signed workbook is Office 2013. ### Certificate Compatibility The certificate compatibility is tested with OpenJDK 14 and Oracle JDK 8. It requires BouncyCastleProvider (in bcprov-jdk18on). The pfx certificate export has been tested on Windows 10, version 1909. The jks,jce,bks and ubr certificate generation has been tested on JDK 14 keytool on Ubuntu 18.04 LTS. | **File extension** | **Signature algorithm** | **Private key protection algorithm** | **Type name** | **Provider** | **Is JDK 8 compatible** | **Is JDK 13+ compatible** | | -------------- | ------------------- | -------------------------------- | --------- | -------- | ------------------- | --------------------- | | @rows=3:\*.pfx, \*.p12 | RSA | AES-256 | PKCS12 | Not specified | Error 1 | Warning 1 | | RSA | Triple-DES | PKCS12 | Not specified | Warning 1 | Warning 1 | | DSA, ECDsa | AES-256, Triple-DES | PKCS12 | Not specified | Error 3 | Error 3 | | @rows=2:\*.jks | RSA | [Custom](https://github.com/openjdk/jdk/blob/4cf63160ad575d49dbe70f128cd36aba22b8f2ff/src/java.base/share/classes/sun/security/provider/KeyProtector.java) | JKS or PKCS12 | Not specified | Warning 1 | Warning 1 | | DSA, ECDsa | [Custom](https://github.com/openjdk/jdk/blob/4cf63160ad575d49dbe70f128cd36aba22b8f2ff/src/java.base/share/classes/sun/security/provider/KeyProtector.java) | JKS | Not specified | Error 3 | Error 3 | | @rows=2:\*.jce | RSA | Triple-DES | JCEKS | SunJCE | TRUE | TRUE | | DSA, ECDsa | Triple-DES | JCEKS | SunJCE | Error 3 | Error 3 | | @rows=2:\*.bks | RSA | Triple-DES | BKS | BC | TRUE | TRUE | | DSA, ECDsa | Triple-DES | BKS | BC | Error 3 | Error 3 | | @rows=2:\*.ubr | RSA | PBE/SHA1/Twofish | UBER | BC | TRUE | TRUE | | DSA, ECDsa | PBE/SHA1/Twofish | UBER | BC | Error 3 | Error 3 | | \*.pem | RSA, DSA, ECDsa | Not supported | I've tried almost all possible type names | Not specified | Error 2 | Error 2 | **\*Error 1 -** Unable to load certificate if private key is encrypted with AES256-SHA256 mode. **\*Error 2 -** java.io.IOException: Invalid keystore format. **\*Error 3 -** If provider was not specified, then throws java.security.UnrecoverableKeyException: Get Key failed: Given final block not properly padded. Such issues can arise if a bad key is used during decryption. **\*Warning 1 -** Make sure the code cleanup provides newly registered providers. Otherwise, there might be an error when using this certificate format. For example, assume that in order to use the bouncy castle provider to open BKS certificates, you have registered BouncyCastleProvider. Then you need to unregister it before opening JKS or pfx certificates. If you are writing unit tests, consider configuring them run synchronously. ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/errorimage1.png) ![](https://cdn.mescius.io/document-site-files/images/dd59ea42-cd61-4fa6-a018-6231c2a9c598/images/errorimage2.png) **Possible solutions** * Upgrade to the latest JDK (Recommended). * Convert certificate format to supported formats. * Use weaker encryption algorithms that the platform supports. For example, downgrade AES256-SHA256 to TripleDES-SHA1 with OpenSSL. * Use 3rd-party certificate providers (if you trust them). * Develop a new certificate provider by yourself (advanced). ## Limitations * Only Microsoft Office signature lines are supported. * Emf image files are not supported. Hence, when exporting signature lines, the signature image is skipped if it is in emf format. The preview images are also emf images. Hence, placeholder preview images are exported instead. * The date format of signature line does not follow system configurations but Excel follows it. * The X.509 certificate being used must have a password. * Java 8 or higher is required to use digital signatures. Otherwise, an exception will be thrown at runtime while opening a signed workbook or signing a workbook. * If you are using PKCS#12 files (\*.pfx) to store private key with AES-256 encryption, your app or service must run on OpenJDK or Oracle JDK 11.0.3, 12.0.2 or 13+ . Refer these bugs (https://bugs.openjdk.org/browse/JDK-8214513 and https://bugs.openjdk.org/browse/JDK-8220734) for details. Caution: Triple-DES is not safe enough for protecting your private key. Refer [this](https://datatracker.ietf.org/doc/html/rfc8429). * When running on JDK 9 or higher, a warning occurs "An illegal reflective access operation has occurred". * Certificate validation returns incorrect result if the certificate chain contains 2 or more items. This is because KeyStore.getCertificateChain(String) method doesn't get certificate chain from Windows certificate storage or OpenSSL certificate storage. * SLF4J prints warning "SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder". This warning cannot be removed, because users might use SLF4J to write logs. * While signing or verifying a workbook, you can only use RSA. This is caused by limitations of default Java key store and org.bouncycastle.jcajce.provider.asymmetric.rsa.DigestSignatureSpi. | **Key Algorithm** | **Action** | **Supported** | | ------------- | ------ | --------- | | RSA | Sign/Verify | Yes | | DSA | Sign/Verify | No | | ECDsa | Sign/Verify | No |