EldoS | Feel safer!

Software components for data protection, secure storage and transfer

Questions about signing Excel 2003, 2010 and 2013 files

Also by EldoS: Rethync
The cross-platform framework that simplifies synchronizing data between mobile and desktop applications and servers and cloud storages
#26527
Posted: 09/19/2013 06:30:41
by Andreas Christoforou (Standard support level)
Joined: 09/01/2013
Posts: 12

Hello,

We've been experimenting with signing Excel files (2007, 2010 and 2013) using SecureBlackbox. So far we have the following two questions/issues:

1. Locked Workbooks
Excel files with locked workbooks are reported as not Signable on PCs with Office 2007. PCs with Office 2010 & 2013 can sign the files with locked workbooks without a problem. Is there a way to sign Excel files with locked workbooks on PCs with Office 2007?

2. Excel Auto Recalculation
When a file, signed on a PC with a certain version of Office (i.e 2007 or 2010), is opened on a PC with a different version of Office (i.e. 2013) the autorecalculation runs and invalidates the signatures.

Please note that asking the end user to set the autocalculation to manual via the menu option or setting it to manual with VBA is not an option.

The autocalculation can be set to manual by defining the calcMode="manual" parameter in the calcPr element in workbook.xml inside the excel file package.

Is there a way to set the excel calculation to manual before signing the document using SecureBlackbox?

Thanks in advance

Andreas
#26531
Posted: 09/19/2013 09:36:19
by Dmytro Bogatskyy (EldoS Corp.)

Quote

1. Locked Workbooks
Excel files with locked workbooks are reported as not Signable on PCs with Office 2007. PCs with Office 2010 & 2013 can sign the files with locked workbooks without a problem. Is there a way to sign Excel files with locked workbooks on PCs with Office 2007?

What do you mean by locked workbook: password protected or shared spreadsheet?
SecureBlackbox doesn't allow to sign shared binary spreadsheet.
I can't say anything about Office products.

Quote
. Excel Auto Recalculation
When a file, signed on a PC with a certain version of Office (i.e 2007 or 2010), is opened on a PC with a different version of Office (i.e. 2013) the autorecalculation runs and invalidates the signatures.

Please note that asking the end user to set the autocalculation to manual via the menu option or setting it to manual with VBA is not an option.

The autocalculation can be set to manual by defining the calcMode="manual" parameter in the calcPr element in workbook.xml inside the excel file package.

Is there a way to set the excel calculation to manual before signing the document using SecureBlackbox?

At the moment there is no option/method that could set automatic calculation mode for spreadsheets.
For OOXML spreadsheets this option could be easily changed with a following code:
Code
        void SetManualCalculationMode(TElOfficeOpenXMLDocument OpenXMLDocument)
        {
            if (OpenXMLDocument.DocumentType != TSBOfficeOpenXMLDocumentType.Spreadsheet)
                return;

            SBOfficePackage.TElOfficePackagePart Part = OpenXMLDocument.OfficeDocumentPart;
            if (Part == null)
                return;

            Stream Stream = Part.GetStream(false);
            try
            {
                Stream.Position = 0;
                using (SBXMLCore.TElXMLDOMDocument Doc = new SBXMLCore.TElXMLDOMDocument())
                {
                    Doc.LoadFromStream(Stream);
                    SBXMLCore.TElXMLDOMElement CalcPr = SBXMLUtils.Unit.FindElementByName(Doc.DocumentElement, "calcPr", "http://schemas.openxmlformats.org/spreadsheetml/2006/main");
                    if (CalcPr == null)
                    {
                        CalcPr = Doc.CreateElementNS("http://schemas.openxmlformats.org/spreadsheetml/2006/main", "calcPr");
                        Doc.DocumentElement.AppendChild(CalcPr);
                        CalcPr.set_AttribStrings("calcId", "0");
                    }

                    CalcPr.set_AttribStrings("calcMode", "manual");

                    Stream.Position = 0;
                    Doc.SaveToStream(Stream);
                    Stream.SetLength(Stream.Position);
                }
            }
            finally
            {
                Part.CloseStream();
            }
        }

  if (_OfficeDocument.OpenXMLDocument != null)
     SetManualCalculationMode(_OfficeDocument.OpenXMLDocument);

For binary spreadsheets this procedure is more complicated. We will check this and add support of this feature.

Thank you for pointing this out.
#26540
Posted: 09/20/2013 04:21:51
by Andreas Christoforou (Standard support level)
Joined: 09/01/2013
Posts: 12

Hello,

Quote

What do you mean by locked workbook: password protected or shared spreadsheet?
SecureBlackbox doesn't allow to sign shared binary spreadsheet.
I can't say anything about Office products.


The file is not password protected and we are not using shared spreadsheet.
Under the Review tab select Protect Workbook and select to protect only the Structure using a password. If we use a blank password everything works but when we set a password, PCs with Office 2007 only report the file as not Signable.

We are able to sign the same document on PCs with Office 2010 and 2013.

As for the second question/issue, the code you provided us solves the problem.
The issue is that Excel disrespects the Marked as Final flag set after a document is signed when a document is opened using a different version of Excel than the one used when last saved. I know this is not your issue and i would like to thank you for providing as with a solution.

Thanks in advance

Andreas
#26544
Posted: 09/20/2013 05:15:35
by Eugene Mayevski (EldoS Corp.)

Quote
Andreas Christoforou wrote:
Under the Review tab select Protect Workbook and select to protect only the Structure using a password. If we use a blank password everything works but when we set a password, PCs with Office 2007 only report the file as not Signable.


It's not clear how the above is related to SecureBlackbox. Are you trying to sign documents with Office (as it follows from your description)?


Sincerely yours
Eugene Mayevski
#26545
Posted: 09/20/2013 05:25:14
by Andreas Christoforou (Standard support level)
Joined: 09/01/2013
Posts: 12

Quote

It's not clear how the above is related to SecureBlackbox. Are you trying to sign documents with Office (as it follows from your description)?


No, files are signed using an application developed with Secureblackbox. I described the procedure used to Protect the Workbook in order to answer Dmytro Bogatskyy question "What do you mean by locked workbook: password protected or shared spreadsheet?"

The only difference between the test PCs is the version of Office installed on the PC.

The user uses the Office installed on the PC to fill the file and save it. After that the user signs the file using our application, not office.

Best Regards

Andreas
#26546
Posted: 09/20/2013 07:02:29
by Andreas Christoforou (Standard support level)
Joined: 09/01/2013
Posts: 12

Hello,

The whole procedure is as follows:

We create an excel file (OOXML spreadsheet) and protect the workbook's structure using the following procedure "Under the Review tab we select Protect Workbook and we select to protect only the Structure using a password".

The end user downloads the file and fills it using the Excel installed on his/her PC.

The end user signs the file using our application, which is developed with SecureBlackbox.

The folowing code in our application:

Code
    Using doc As New TElOfficeDocument()
        doc.Open(_destFile)
        If Not doc.Signable Then
            Return False
        End If
...


returns Signable as false on PCs that Office 2007 is installed.

On PCs with Office 2010 or 2013 the code above returns Signable as true.

I don't know if the Office version has anything to do with the above issue but it's the only difference between the test PCs.

Best Regards

Andreas
#26547
Posted: 09/20/2013 08:41:02
by Dmytro Bogatskyy (EldoS Corp.)

Hello,

Quote
returns Signable as false on PCs that Office 2007 is installed.

For OOXML documents the Signable property is true if the document is not encrypted and false otherwise.
Please check IsEncrypted and DocumentFormat properties.
Could you please attach a sample document for checking.
#26566
Posted: 09/23/2013 02:01:20
by Andreas Christoforou (Standard support level)
Joined: 09/01/2013
Posts: 12

Hello,

Apparently using Excel 2007 to save an excel file with its workbook protected with a password marks the file as encrypted. This is not happening with Excel 2010 or 2013.

Also not using a password when protecting the workbook, in Excel 2007, works.

*Update*
Excel 2007 actually encrypts the file using Open XML Standard
Encryption AES 128.

Andreas
Also by EldoS: MsgConnect
Cross-platform protocol-independent communication framework for building peer-to-peer and client-server applications and middleware components.

Reply

Statistics

Topic viewed 2588 times

Number of guests: 1, registered members: 0, in total hidden: 0




|

Back to top

As of July 15, 2016 EldoS Corporation will operate as a division of /n software inc. For more information, please read the announcement.

Got it!