Click here to Skip to main content
14,332,086 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi all
I am struggling to get some cell validation working. I have been passed over code which needs some improvements. Currently I need cells to only accept a int value, but users currently can enter apostrophes into the cell which then later causes problems.

The Excel sheets are all generated programmatically and comments are sparse. I believe the leafcells are where the user will enter the data, sorry can't provide an example

what I have is
public void WriteRowsBody(xl::Worksheet sheet, IEnumerable<ServiceAgreement> agreements, ref int lastRow)
           // Initialise the row & cell counters for the data.
           xl::Range range = null;
           Cell[] leafcells = Cells.SelectMany(c => c.Leaves).ToArray();
           int col = 1;
           int row = lastRow + 1;

           foreach (ServiceAgreement sa in agreements)
               // Write the first batch of data.
               string[] values = new string[] { sa.Id, (sa.DateWeekStart.HasValue ? sa.DateWeekStart.Value.ToString("yyyy-MM-dd") : ""), sa.ClientName, sa.ClientAddress, sa.ServiceElement };
               for (int i = 0; i < values.Length; i++, col++)
                   sheet.Cells[row, col].Value2 = values[i];
                   sheet.Range[sheet.Cells[row, col], sheet.Cells[row + 1, col]].Merge();

               sheet.Cells[row, col].Value2 = "Planned";
               sheet.Cells[row + 1, col].Value2 = "Actual";
               // Write the data cells.
               WriteDataCells(sheet, sa, row);

               // Set the default formatting for the data rows.
               range = sheet.Range[sheet.Cells[row, 1], sheet.Cells[row + 1, leafcells.Length]];

               XlPaintBackground(range, TitleColour);

               XlSetAlignment(range, xl::XlHAlign.xlHAlignCenter, xl::XlVAlign.xlVAlignCenter);

               // Set the specific formatting for the data rows.
               for (int i = 0; i < leafcells.Length; i++)
                   XlDrawBorder(sheet.Cells[row, i + 1], Color.Black);
                   xl::Range rb = sheet.Cells[row + 1, i + 1];
                   XlDrawBorder(rb, Color.Black);
                   //Makes the leaf cells editable
                   if (leafcells[i].IsEditable)
                       //sets the color of the editable leaf cells to white
                       XlPaintBackground(rb, alternate);
                       XlProtect(rb, false);



               col = 1;
               row += 2;

           lastRow = row;


How do I go about validating the cells?

Any help please
Ramza360 11-Nov-15 10:25am
Have you tried doing an int.TryParse on the Value2 object?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100