如何使用OpenXML代码取消保护工作表? [英] How to UnProtect Sheet using OpenXML code?

问题描述

我创建了一张工作表，可以使用OpenXml代码对其进行保护.

I have created one Sheet, which i could protect using OpenXml code.

但是现在需要读取此excel文件.

But now there is requirement to read this excel file.

我将所有值都设为NULL，因为它受到保护.

I am getting all the values as NULL because it is protected.

(我尚未在代码中放置任何密码来保护工作表，excel文件中只有一个工作表.)

( I haven't placed any password yet in the code to protect the sheet, there is only one sheet in the excel file.)

我从搜索中获得了以下代码以取消保护工作表.

I have got below code from my Search to unprotect the worksheet.

  workSheet.RemoveAllChildren<SheetProtection>();

但是，这不起作用.读取此受保护的工作表时，我仍然得到null值.

But, this is not working. I am still getting the null values while reading this protected sheet.

 using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(FilePath, false))
            {
                WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
                IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                //if ((sheets.Count() != 2) && (sheets.First().Name.Value != "StudentNomination") && (sheets.Last().Name.Value != "Sheet2"))
                //{
                //    throw new Exception("Please Upload the correct Nomination file, for example you can download the Nomination Template file first.!!");
                //}
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                workSheet.RemoveAllChildren<SheetProtection>();
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

有人可以帮我吗?

推荐答案

您用于删除保护的代码是正确的.但是在此之前，您必须在编辑模式"下打开Excel文件. SpreadSheetDocument.Open中的第二个参数应设置为true.

Your code for removing protection is correct. But before that you have to open the Excel file in Edit Mode. The second argument in SpreadSheetDocument.Open should be set to true.

此外，无论采取何种保护措施，您都应该能够读取单元格的值.请参见下面的代码.为了对此进行测试，您必须创建一个excel文件，并用数字填充单元格A1，B1和C1.

Also Regardless of protection, you should be able to read a cell's value. See the below code. In order to test this you would have to create a excel file and fill the cells A1,B1 and C1 with numbers.

using System.Linq;
using System.Collections.Generic;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

class Test
{

    static void Main()
    {
        string filePath = @"E:\test.xlsx";
        using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, true))
        {
            WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
            IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
            string relationshipId = sheets.First().Id.Value;
            WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
            Worksheet workSheet = worksheetPart.Worksheet;

            var dataBeforeProtection = workSheet.Descendants<Row>().First().Descendants<Cell>().First().CellValue.InnerText;
            workSheet.RemoveAllChildren<SheetProtection>();
            var dataAfterProtection = workSheet.Descendants<Row>().First().Descendants<Cell>().First().CellValue.InnerText;
            workSheet.Save();
        }
    }
}

这篇关于如何使用OpenXML代码取消保护工作表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！