what is the way to check whether excel cell is locked or not?
How to Check if a particluar cell in that range is locked,
I am posting the small snippet of my code, Please suggestme , the better way of writing to cell if the cell is not locked.
if (reader.HasRows) { minRow = 0; minCol = 0;
// Process each result in the result set
while (reader.Read())
{
// Create an array big enough to hold the column values
object[] values = new object[reader.FieldCount];
// Add the array to the ArrayList
rowList.Add(values);
// Get the column values into the array
reader.GetValues(values);
int iValueIndex = 0;
int jValueIndex = 1;
// If the Reading Format is by ColumnByColumn
if (CurTaskNode.ReadFormat == "ColumnbyColumn")
{
minCol = 0;
int lengthHeader = 0;
if (CurTaskNode.ReadHeader == true)
{
lengthHeader = CurTaskNode.HEADER_MAX_ROW - CurTaskNode.HEADER_MIN_ROW;
}
else
{
lengthHeader = CurTaskNode.HeaderData.Length;
}
for (int iCol = 0; iCol < lengthHeader; iCol++)
{
// Checking whether the Header data exists or not
if (CurTaskNode.HeaderData[minCol] != "")
{
//if (!(excelworksheet.Cells[CurTaskNode.DATA_MIN_ROW + minRow, CurTaskNode.DATA_MIN_COL + minCol]).Locked)
//{
// Assigning the Value from reader to the particular cell in excel sheet
excelworksheet.Cells[CurTaskNode.DATA_MIN_ROW + minRow, CurTaskNode.DATA_MIN_COL + minCol] = values[iValueIndex];
iValueIndex++;
//}
}
minCol++;
}
minRow++;
}
In the code, writing to excel cell is
excelworksheet.Cells[CurTaskNode.DATA_MIN_ROW + minRow, CurTaskNode.DATA_MIN_COL + minCol] = values[iValueIndex];
here I have to check the condition whether the cell is locked or not, I tried it, but this is not correct.
CurTaskNode.DATA_MIN_ROW is the minrow value for that data to write in excel sheet, this value comes from XML file and if I have to write from (10, 2) to (20, 10) , cells, in which if the first cell is say locked, I cant write it to that cell and I have to proceed writing with other cells.
Please help me in doing this. Thanks, Ramm
Answer
((Range)excelworksheet.Cells[CurTaskNode.DATA_MIN_ROW + minRow, CurTaskNode.DATA_MIN_COL + minCol]).Locked is the property to find a cells locked attribute.
excelworksheet.Protection.AllowBlahBlahBlah is the property to get/set a kind of protection for a worksheet.
excelworksheet.ProtectionMode is a read only boolean to find out if the protection defined by the AllowBlahBlahBlah's is enabled.
So I might use
Range r = (Range)excelworksheet.Cells[CurTaskNode.DATA_MIN_ROW + minRow, CurTaskNode.DATA_MIN_COL + minCol]?? new Range(); // cover null
if(
r.Locked && // is cell locked
excelworksheet.ProtectionMode && // is sheet protected
!excelworksheet.Protection.AllowFormattingCells) // are locked cells not allowed to be messed with
DoSomethingBecauseItsProtected();