How do you check whether a cell is readonly in EXCEL using C#

Question

Hi,

I am importing data to Excel sheets from a database. For this, I am using datareader. The excel sheet template has some macros and few formulae calculated and its not the normal excel worksheet. so I have to write the data into the excel sheet only if the particular cell is allowed to write. If not, the data shouldn't be imported.

So for this, I have a XML file which says from which column I should start writing and in which row it should stop, I have done this for many sheets. But in one sheet, the first cell of the row is "readonly" (locked) and the rest are write access permitted.

Since I get the entire row from DB using Datareader, I am stuck with needing to write to the other cells, without writing to the locked cell.

I am attaching the code snippet for reference.

Please help me in doing this.

Sample ::

 if (reader.HasRows)
  {
   minRow = 0;
    minCol = 0;
   Excel.Workbook SelWorkBook = excelAppln.Workbooks.Open(curfile, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, false, false, false);
    Excel.Sheets excelSheets = SelWorkBook.Worksheets;

 Excel.Worksheet excelworksheet = (Excel.Worksheet)excelSheets.get_Item(CurSheetName);

                                        // 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;

                                            // If the Reading Format is by ColumnByColumn 
                                            if (CurTaskNode.ReadFormat == "ColumnbyColumn")
                                            {
                                                minCol = 0;
                                                //   minRow = 0;
                                                for (int iCol = 0; iCol < CurTaskNode.HeaderData.Length; iCol++)
                                                {

                                                    // Checking whether the Header data exists or not
                                                    if (CurTaskNode.HeaderData[minCol] != "")
                                                    {
                                                        // 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++;
                                            }SelWorkBook.Close(true, curfile, null);

Please help me in resolving this.

Thank You,

Ramm

Answer 1

Ok, first you need to check the locked property of the first cell, then if it's locked slice the array (so that you have the whole row minus the first column), then write to the sheet. Here's some code, not necessarily exact, the SLICE function is just pseudo-code, there are a number of different ways of slicing arrays in C#, use the method of your choice:

if (!excelworksheet.Cells[CurTaskNode.DATA_MIN_ROW + minRow, CurTaskNode.DATA_MIN_COL + 1].Locked )
{
  excelworksheet.Cells[CurTaskNode.DATA_MIN_ROW + minRow, CurTaskNode.DATA_MIN_COL + minCol] = values[iValueIndex];
  iValueIndex++;
}
else
{
  excelworksheet.Cells[CurTaskNode.DATA_MIN_ROW + minRow, CurTaskNode.DATA_MIN_COL + minCol] = values.SLICE(iValueIndex);
  iValueIndex++;
}