Finance | Project Operations, Human Resources, ...

how to lock certain excel cells or make them read-only using X++?

(0) Share

Report

Posted on by Community Member

Hi All,

I want to lock some of excel cells (not all) using X in my Export to Excel utility class.
When someone opens the excel file, they will be able to edit any cell except those cells. And if someone wishes to delete all editable data then read-only cells will retain their value and other editable cells will lose their value. Because the same file I have to use for import and these fields will used to identify unique values from AX.

For reference a part of code is attached below

 using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
            {                 
                var currentRow = 10;
                var worksheets = package.get_Workbook().get_Worksheets();
                var worksheet  = worksheets.Add("@ApplicationFoundation:DMFExportOperation");
                var cells      = worksheet.get_Cells();
               

                //<---- Start---> Setup Header creation
                //Want to make lock below field 
                cell=null;
                value="XYZ";
                cell=cells.get_Item(4,2);
                cell.set_Value(value);
                this.setCellBold(cell);

                
            }

Thanks in Advance!!!

Regards,

Aimon Fatima

I have the same question (0)

All responses (7)

Answers (0)

André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi Aimon,

What is the real business requirement for blocking the cells? Are you afraid that users are not familiar with Excel and mess up the sheet before you can import it?

Probably, you can get an answer on an Excel/Office forum how to achieve locking fields using the Open Office XML interface.

techcommunity.microsoft.com/.../Excel_Cat

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Yes, it's a validation that user will not be able to change values in some cells.

So I need a code to write in my class

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

Hi Aimon,

Did you ask your question on the Excel forum? There might be volunteers more familiar with all coding options.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Not yet. Will I get reference for coding in X++ ??

Was this reply helpful? Yes No
André Arnaud de Cal... 300,911 Super User 2025 Season 2 on at

Like (0)

Report

You can expect answers how to use the OfficeOpenXML dll. Which methods and how to use them. Then it doesn't matter if the methods will be used in x++ or a C# project.

As you noticed, there is not much traction on this community for this topic. Your requirement is not common in the context of Dynamics 365. So, I think you will get quicker and better answers on an Excel forum.

Was this reply helpful? Yes No
Suggested answer

Pete Alberts 3,542 on at

Like (0)

Report

Hi Aimon

You wouldn't have this issue if you were using the MS Open in Excel feature. Writing custom export and import to excel features are old. Maybe there are still cases where it is needed. But I highly doubt it.

And then which ever method you choose I suggest you stay with validations. On import check that the values that shouldn't have been edited indeed have not been edited. If this is enforced on table level (which should be the case), a data entity will do that for you without any extra work. I am sure it is possible to accomplish non-editable excel fields, but it won't be straight forward and it will take a lot of your time.

All of the best.

Was this reply helpful? Yes No
Suggested answer

Rahul Mohta 21,032 on at

Like (0)

Report

check layout in budget planning and how it uses Excel to make fields editable and read only

Was this reply helpful? Yes No