excel - 使用 open xml sdk 只读或锁定特定单元格或行

Question

我正在使用 open xml sdk 导出 Excel。我获取 Excel 文件并将该文件复制到另一个位置，然后插入包含数据的新行。

我应该使用 open xml sdk 将这些行设置为只读(或者我必须锁定)..

如何做到这一点？

Answer 1

我引用了以下链接Excel File Password Protection with Open XML SDK .

在这里，我为您提供了从目录打开文件并插入单元格的完整代码。这里我只插入了一个单元格。您可以根据需要插入。在这里我插入了锁定的单元格。找到这个，它会对你很有帮助..

注意:这里我提到的代码仅用于读取现有文件并插入行和单元格并锁定已手动插入的单元格

谢谢。

// To read the xlsx file..
Package spreadsheetPackage = Package.Open(destinationFile, FileMode.Open, FileAccess.ReadWrite);

// create a document
using(var document = SpreadsheetDocument.Open(spreadsheetPackage)) {

    var workbookPart = document.WorkbookPart;
    //var workbook = workbookPart.Workbook;

    var sheet = workbookPart.Workbook.Descendants < Sheet > ().FirstOrDefault();
    Worksheet ws = ((WorksheetPart)(workbookPart.GetPartById(sheet.Id))).Worksheet;
    SheetData sheetData = ws.GetFirstChild < SheetData > ();

    if (sheet == null || sheetData == null) throw new Exception("No sheet found in the template file");

    int rowIndex;

    var worksheetPart = (WorksheetPart) workbookPart.GetPartById(sheet.Id);
    var rows = worksheetPart.Worksheet.Descendants < Row > ();
    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new Cell();

    // Getting row index from the web config..
    if (!int.TryParse(WebConfigurationManager.AppSettings["BasReportRowIndex"].ToString(), out rowIndex)) throw new Exception("Mention template row index in the configuration file");

    // Create Cell format .. It's necessary to lock the cell
    CellFormat lockFormat = new CellFormat() {
        ApplyProtection = true,
        Protection = new Protection() {
            Locked = true
        }
    };
    WorkbookStylesPart sp = workbookPart.GetPartsOfType < WorkbookStylesPart > ().FirstOrDefault();

    if (sp == null) sp = worksheetPart.AddNewPart < WorkbookStylesPart > ();

    sp.Stylesheet.CellFormats.AppendChild < CellFormat > (lockFormat);
    sp.Stylesheet.CellFormats.Count = UInt32Value.FromUInt32((uint) sp.Stylesheet.CellFormats.ChildElements.Count);
    sp.Stylesheet.Save();

    foreach(WeekSummary summary in report.Summary) {
        DocumentFormat.OpenXml.Spreadsheet.Row row = new DocumentFormat.OpenXml.Spreadsheet.Row();

        // Before insert Row we've to mention the index where the row must be inserted
        row.RowIndex = (UInt32) rowIndex++;

        row.AppendChild < Cell > (new Cell() {
            DataType = CellValues.String,
            CellValue = new CellValue(summary.name ? ?""),
            StyleIndex = 0
        });

        // Append the row to sheet data.. 
        sheetData.AppendChild < Row > (row);
    }
    // Till the line It's only to create and insert row..

    // Now we've to mention the sheet protection. It's necessary for the whole sheet. Then only cells will be locked
    SheetProtection sheetProtection = new SheetProtection();
    sheetProtection.Password = "CC";
    // these are the "default" Excel settings when you do a normal protect
    sheetProtection.Sheet = true;
    sheetProtection.Objects = true;
    sheetProtection.Scenarios = true;

    // After the following lines, the cell will be locked...
    bool bFound = false;
    OpenXmlElement oxe = worksheetPart.Worksheet.FirstChild;
    foreach(var child in worksheetPart.Worksheet.ChildElements) {
        // start with SheetData because it's a required child element
        if (child is SheetData || child is SheetCalculationProperties) {
            oxe = child;
            bFound = true;
        }
    }

    if (bFound) worksheetPart.Worksheet.InsertAfter(sheetProtection, oxe);
    else worksheetPart.Worksheet.PrependChild(sheetProtection);

    worksheetPart.Worksheet.Save();
}