I am trying to set the protection for a worksheet so that the cells are not editable, but then allow editing of certain cells. This is to help the user when working with the exported excel file.
I guess this means protection at the cell level? I can't seem to figure it out...
If the sheet is protected and you want to make some cells editable, you can set the cells' CellFormat.Locked properties to False.
I am not able to protect the worksheet. Can you please post the snippet.
Thanks,
Mani.
I am trying like following snippet Infragistics.Excel.
I am trying like following snippet
Infragistics.Excel.
Workbook wb = new Infragistics.Excel.Workbook();
Infragistics.Excel.Worksheet sheet = wb.Worksheets.Add("ExpenseAllocation"); sheet.Protected =
sheet.Protected =
true; sheet.DisplayOptions.PanesAreFrozen =
sheet.DisplayOptions.PanesAreFrozen =
false;
sheet.Rows[0].CellFormat.Font.Bold = Infragistics.Excel.ExcelDefaultableBoolean.True; sheet.Rows[0].CellFormat.FillPattern = Infragistics.Excel.
sheet.Rows[0].CellFormat.FillPattern = Infragistics.Excel.
FillPatternStyle.Solid; sheet.Rows[0].CellFormat.FillPatternForegroundColor = System.Drawing.
sheet.Rows[0].CellFormat.FillPatternForegroundColor = System.Drawing.
Color.FromArgb(238, 243,255); Infragistics.Excel.
WorksheetRow headersRow = sheet.Rows[0]; headersRow.Cells[0].Value =
headersRow.Cells[0].Value =
"Order ID"; headersRow.Cells[1].Value =
headersRow.Cells[1].Value =
"Product"; headersRow.Cells[2].Value =
headersRow.Cells[2].Value =
"Unit Price"; headersRow.Cells[3].Value =
headersRow.Cells[3].Value =
"Quantity"; headersRow.Cells[4].Value =
headersRow.Cells[4].Value =
"Discount"; headersRow.Cells[5].Value =
headersRow.Cells[5].Value =
"Order Total";
I ran the code you have posted and it works for me when I open the workbook in Excel 2007. You can also try making the workbook protected by setting wb.Protected to True.
Is it something to do with the version and I am using Infragistics2.Excel.v7.3?
I am not able to protect worksheet.
I don't believe the version should matter. The ability to protect worksheets has been in the Excel assembly since it was introduced.
In what version of Excel are you opening the workbook?
I am using Excel 2000 and also I am using memorystream to allow the user to save the excel file.
MemoryStream ms = new MemoryStream(); wb.Save(ms); Byte[] byteArr = (Byte[])Array.CreateInstance(typeof(Byte),ms.Length); ms.Position = 0; ms.Read(byteArr,0,(int)ms.Length); ms.Close(); Response.Clear(); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment;filename=DocTypeInfo.xls"); Response.BinaryWrite(byteArr); Response.End();
Following are the snippet I am using:
Infragistics.Excel.Workbook wb = new Infragistics.Excel.Workbook(); Infragistics.Excel.Worksheet sheet = wb.Worksheets.Add("ExpenseAllocation"); sheet.Protected = true; wb.Protected = true;
sheet.DisplayOptions.PanesAreFrozen = false; sheet.Rows[0].CellFormat.Font.Bold = Infragistics.Excel.ExcelDefaultableBoolean.True; sheet.Rows[0].CellFormat.FillPattern = Infragistics.Excel.FillPatternStyle.Solid; sheet.Rows[0].CellFormat.FillPatternForegroundColor = System.Drawing.Color.FromArgb(238, 243, 255);
Infragistics.Excel.WorksheetRow headersRow = sheet.Rows[0]; headersRow.Cells[0].Value = "Order ID"; headersRow.Cells[1].Value = "Product"; headersRow.Cells[2].Value = "Unit Price"; headersRow.Cells[3].Value = "Quantity"; headersRow.Cells[4].Value = "Discount"; headersRow.Cells[5].Value = "Order Total";
// The "Unit Price" column should display its values as dollars sheet.Columns[2].CellFormat.FormatString = "\"$\"#,##0.00_);(\"$\"#,##0.00)"; // The "Discount" column should display its values as percentages sheet.Columns[4].CellFormat.FormatString = "0.00%"; // The "Order Total" column should display its values as dollars sheet.Columns[5].CellFormat.FormatString = "\"$\"#,##0.00_);(\"$\"#,##0;00)"; // Allow enough room to display the totals sheet.Columns[5].Width = 3000;
Infragistics.Excel.WorksheetRow currentRow;
currentRow = sheet.Rows[1]; currentRow.Cells[0].Value = 10248; currentRow.Cells[1].Value = 42; currentRow.Cells[2].Value = 9.80; currentRow.Cells[3].Value = 10; currentRow.Cells[4].Value = 0.075;
currentRow = sheet.Rows[2]; currentRow.Cells[0].Value = 10248; currentRow.Cells[1].Value = 72; currentRow.Cells[2].Value = 34.80; currentRow.Cells[3].Value = 5; currentRow.Cells[4].Value = 0.075;
currentRow = sheet.Rows[3]; currentRow.Cells[0].Value = 10249; currentRow.Cells[1].Value = 14; currentRow.Cells[2].Value = 18.60; currentRow.Cells[3].Value = 9; currentRow.Cells[4].Value = 0.1;
currentRow = sheet.Rows[4]; currentRow.Cells[0].Value = 10249; currentRow.Cells[1].Value = 51; currentRow.Cells[2].Value = 42.40; currentRow.Cells[3].Value = 40; currentRow.Cells[4].Value = 0.1;
MemoryStream ms = new MemoryStream(); //wb.Save(@"c:\mani\test.xls"); wb.Save(ms); Byte[] byteArr = (Byte[])Array.CreateInstance(typeof(Byte), ms.Length); ms.Position = 0; ms.Read(byteArr, 0, (int)ms.Length); ms.Close(); Response.Clear(); Response.ContentType = "application/vnd.ms-excel"; Response.AddHeader("Content-Disposition", "attachment;filename=DocTypeInfo.xls"); Response.BinaryWrite(byteArr); Response.End();