Extract XLS and XLSX worksheet Protection password keys

[OpenDevSolutions]

Hello,

Is there a way to extract the protection details, specifically the worksheet protection including the password in XLSX and XLS files?

For example, a password-protected worksheet in XLSX contains a tag in the sheet#.xml files.

It would look something like : <sheetProtection password=“CBEB” sheet=“1” objects=“1” scenarios=“1”/

I would greatly appreciate any guidance on how to extract this in XLSX and XLS files and reapply it to new files.

The reasoning is that I am using a web editor that does not maintain the sheet protection between saving and I require the ability to re-apply the protection before actually saving the file to my database.

[ahsaniqbalsidiqui]

@OpenDevSolutions,
Could you please elaborate bit more about what protection details you want to extract? The password will be required to load a password protected excel file. Share your template file here for our analysis.

[Amjad_Sahi]

@OpenDevSolutions,

Moreover, to get to know if a worksheet is protected or not, you may try to use Worksheet.IsProtected Boolean attribute:
e.g
Sample code:

..........
            if (worksheet.IsProtected)
            {

                //.......
                //your code goes here.
               //........ 

            } 
.....

Hope this helps a bit.

[OpenDevSolutions]

The details I was attempting to extract was the element of an xlsx document and however an xls file stores its credentials.

Thanks for your quick responses. I managed to resolve my issue by caching the worksheets Protection object and using the Protection.Copy method to restore it on worksheets during a save.

I am using a web-based spreadsheet editor that removes the password when returning the spreadsheet content to my server, which would mean any subsequent edits would use a unprotected worksheet unintentionally.

I am considering my issue resolved.

[ahsaniqbalsidiqui]

@OpenDevSolutions,
Thank you for the feedback and we are glad to know that your issue is resolved. Please feel free to write us back if you have any other query related to Aspose.Cells.

[OpenDevSolutions]

Hello,

I appear to be experiencing an issue when trying to copy a worksheets protection details.

When applying worksheet.Protection.Copy(anotherWorkbook), then saving, I get an error in excel “We found a problem with some content in 'WorkbookName.xlsx” Do you want us to try and recover as much as we can? If you trust the source of this workbook, click Yes."

Is there a better way to reapply a worksheets protection without causing this error?

The C# code to apply the protection is below:

            Workbook prevWorkbook = new Workbook(prevVersion);
            Workbook currentWorkbook = new Workbook(currentVersion);
            bool changesMade = false;
            foreach (Worksheet currentWorksheet in currentWorkbook.Worksheets)
            {
                Worksheet prevWorksheet = prevWorkbook.Worksheets[currentWorksheet.Name];
                if (prevWorksheet != null)
                {
                    currentWorksheet.Protection.Copy(prevWorksheet.Protection);
                    changesMade = true;
                }
            }

            if (changesMade)
            {
                currentWorkbook.Save(currentVersion, saveFormat);
                currentVersion.Position = 0;
            }

[Amjad_Sahi]

@OpenDevSolutions,

To evaluate your issue precisely, we need your both Excel files (preWorkbook and currentWorkbook). Please zip the files and attach the zipped archive, we will check your issue soon. Also provide any other details regarding code segment and missing objects/variables initializations with data to reproduce the issue on our end. This will help us to look into your issue thoroughly to figure it out soon.

[OpenDevSolutions]

TestWorksheetProtection.zip (15.8 KB)

Attached is a zip of 3 files:

1. Excel document before changes (TestProtection-Before.xlsx)
2. Excel document after changes (TestProtection-After.xlsx)
3. JSON the browser sends to my server to be converted to an excel document using Syncfusion

The worksheet password is “testing”

After I load the “after” excel document to a filestream, I send it to the following method to grab the “before” workbook and copy the protection settings:

private async Task<Result<FileStreamResult>> RestoreWorksheetProtection(FileStreamResult currentVersion, Aspose.Cells.SaveFormat saveFormat)
{
    //Get the previous excel file version to compare worksheet protection and re-apply
    Result<TempFileDTO> previousVersionResult = await GetSpreadsheetAsync("C:\Temp\TestProtection-Before.xlsx");
    
    if (previousVersionResult.HasErrors)
        return new Result<FileStreamResult>(previousVersionResult.Errors.ToArray());

    try
    {
        Stream prevVersion;
        if (previousVersionResult.Value.InMemory)
            prevVersion = new MemoryStream(previousVersionResult.Value.Data);
        else
            prevVersion = new FileStream(previousVersionResult.Value.TempFullPath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite, 81920, FileOptions.DeleteOnClose);

        currentVersion.FileStream = AsposeHelper.RestoreWorksheetProtection(prevVersion, currentVersion.FileStream, saveFormat);
        prevVersion.Close();
        return new Result<FileStreamResult>(currentVersion);
    }
    catch (Exception ex)
    {
        throw new Exception(ex.Message);
    }
}

internal static Stream RestoreWorksheetProtection(Stream prevVersion, Stream currentVersion, Aspose.Cells.SaveFormat saveFormat)
        {
            Workbook prevWorkbook = new Workbook(prevVersion);
            Workbook currentWorkbook = new Workbook(currentVersion);
            bool changesMade = false;
            foreach (Worksheet currentWorksheet in currentWorkbook.Worksheets)
            {
                Worksheet prevWorksheet = prevWorkbook.Worksheets[currentWorksheet.Name];
                if (prevWorksheet != null)
                {
                    currentWorksheet.Protection.Copy(prevWorksheet.Protection);
                    changesMade = true;
                }
            }

            if (changesMade)
            {
                currentWorkbook.Save(currentVersion, saveFormat);
                currentVersion.Position = 0;
            }

            return currentVersion;
        }

[ahsaniqbalsidiqui]

@OpenDevSolutions,
I have tried the following simple code to reproduce the issue using the latest version Aspose.Cells for .NET 21.4.x but could not observe any error while opening the output file in MS Excel. Could you please try this sample code with the latest version and share your feedback by opening the output file in MS Excel?

private static void Cells_Protection()
{
    Workbook workbook = new Workbook("TestProtection-Before.xlsx");
    Workbook workbook1 = new Workbook();
    RestoreWorksheetProtection(workbook, workbook1);
}
private static void RestoreWorksheetProtection(Workbook prevWorkbook, Workbook currentWorkbook)
{
    bool changesMade = false;
    foreach (Worksheet currentWorksheet in currentWorkbook.Worksheets)
    {
        Worksheet prevWorksheet = prevWorkbook.Worksheets[currentWorksheet.Name];
        if (prevWorksheet != null)
        {
            currentWorksheet.Protection.Copy(prevWorksheet.Protection);
            changesMade = true;
        }
    }
            
    if (changesMade)
    {
        currentWorkbook.Save("TestProtection-After2.xlsx");
    }
            
}

[OpenDevSolutions]

Thank you for the very quick response. I was able to discover my issue. The problem was actually coming from saving the workbook to the current versions stream without performing a seek to the beginning of the file.

When Restoring the worksheet protection, it was essentially duplicating the workbook to the end of the file as an Append instead of overwrite.
adding currentVersion.Seek(0, SeekOrigin.Begin); resolves my issue.

public static Stream RestoreWorksheetProtection(Stream prevVersion, Stream currentVersion, Aspose.Cells.SaveFormat saveFormat)
        {
            Workbook prevWorkbook = new Workbook(prevVersion);
            Workbook currentWorkbook = new Workbook(currentVersion);
            bool changesMade = false;
            foreach (Worksheet currentWorksheet in currentWorkbook.Worksheets)
            {
                Worksheet prevWorksheet = prevWorkbook.Worksheets[currentWorksheet.Name];
                if (prevWorksheet != null)
                {
                    currentWorksheet.Protection.Copy(prevWorksheet.Protection);
                    changesMade = true;
                }
            }

            if (changesMade)
            {
                currentVersion.Seek(0, SeekOrigin.Begin);
                currentWorkbook.Save(currentVersion, saveFormat);
                currentVersion.Position = 0;
            }

            return currentVersion;
        }

[Amjad_Sahi]

@OpenDevSolutions,

@OpenDevSolutions

adding currentVersion.Seek(0, SeekOrigin.Begin); resolves my issue

It is nice to know that you have sorted out your issue now. Please feel free to ask any queries or share your comments; we will be happy to assist you soon.