CODEWITHSUNDEEP
c#asp.netexcel
Deepak
Deepak

Reputation: 195

How to create readonly Excel sheet using C# .net

I am creating a dynamic Excel sheet using ExcelWorksheet. I need to create a non-editable excel. ws.Cells["A1:Q12"].Style.Locked = true is not working.

Here is my code :

Default.aspx.cs

protected void Page_Load(object sender, EventArgs e)
    {
      string filePath = Server.MapPath("~/Download/Sample.xlsx");
      using (ExcelPackage pck = new ExcelPackage())
      {
       FileInfo summaryFilePath = new FileInfo(filePath);    
       ExcelWorksheet ws= pck.Workbook.Worksheets.Add("Sample Page");
       CreateForamters(ws);
      }
    }



 private void CreateForamters(ExcelWorksheet ws)
    {
        ws.Cells["B8:L8"].Value = "SamplePage";           
        ws.Cells["B10:L10"].Value = DateTime.Now.ToString("MMM-yy");
        ws.Cells["B11:L11"].Value = "test data........-";

        ws.Cells["B8:L11"].Style.Fill.PatternType = ExcelFillStyle.Solid;
        ws.Cells["B8:L11"].Style.Font.Bold = true;
        ws.Cells["B8:L11"].Style.Font.Name = "Arial";
        ws.Cells["B8:L11"].Style.Font.Size = 16;
        ws.Cells["B8:L11"].Style.Font.Color.SetColor(Color.Blue);
        ws.Cells["B8:L11"].Style.Fill.BackgroundColor.SetColor(Color.White);
        ws.Cells["B8:L11"].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
        ws.Cells["B8:L8"].Merge = true;
        ws.Cells["B9:L9"].Merge = true;
        ws.Cells["B10:L10"].Merge = true;
        ws.Cells["B11:L11"].Merge = true;
        ws.Cells["A1:Q12"].Style.Locked = true;
    }

Thank you all in advance for your response.

Upvotes: 3

Views: 7461

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

I am creating a dynamic Excel sheet using ExcelWorksheet. I need to create a non-editable excel. ws.Cells["A1:Q12"].Style.Locked = true is not working.

To create NON-Editable Cells, you have to use

ws.get_Range("A1", "Q12").Locked = true;

And then you need to protect the worksheet. Without protecting the worksheet, the .Locked command doesn't have any significance.

Here is a basic example (TRIED AND TESTED IN VS2010 + OFFICE 2010)

object misValue = System.Reflection.Missing.Value;

ws.get_Range("A1", "Q12").Locked = true;

string Password = "Sid";

ws.Protect(Password, misValue, misValue, misValue, misValue, misValue, 
misValue, misValue, misValue, misValue, misValue, misValue, misValue, 
misValue, misValue, misValue);

NOTE: By default all cells in Excel are locked. If you don't want to protect the rest of the cells in the sheet then remember to set their .Locked property to False. 

ws.Cells.Locked = false;

and then use the above code.

Upvotes: 3

Sudhakar Tillapudi
Sudhakar Tillapudi

Reputation: 26219

if you want to save Excel WorkBook as ReadOnly Save as Below:

object misValue = System.Reflection.Missing.Value;

ExcelWorkBook.ActiveWorkbook.SaveAs(save_path, Excel.XlFileFormat.xlWorkbookNormal, misValue , misValue , True, True,XlSaveAsAccessMode.xlShared, false, false, misValue, misValue , misValue );

Upvotes: 0

Related Questions