How to update Excel Cell based on validation using ClosedXML?

Question

I have an input excel file which contains 6 columns and n number of rows. Out of which 3 columns are mandatory columns. If any of mandatory columns are empty then I need to update some custom text in the Remarks column. In the input excel column names are fixed but position is not fixed.

For example, in the below table I have updated Remarks column values of row no. 2 & 3 as Fail since CCode and ID column values are blank.

Name	ID	ComapanyName	CCode	Address	Remarks
Anto	12	ABC Corp Cmp	ABCCo	AvenueSt
Anuj	13	XYZ Corp Cmp		AvenueSt	Fail
Kathy		CTF Corp Cmp	CTFCo	AvenueSt	Fail

Close XML Logic:

var workbook = new XLWorkbook(IPPath);
    var rows     = workbook.Worksheet(1).RangeUsed().RowsUsed().Skip(1);

    foreach (var row in rows)
    {
        -- update logic
    }

score 1 · Answer 1 · answered Mar 18 '22 at 13:28

First you need to read the excel data in DataTable, then you should be able to update the excel data by iterating each record.

private static System.Data.DataTable ReadExcelData(string filePath)
        {
            try
            {
                System.Data.DataTable dataTable = new System.Data.DataTable();
                using (Stream stream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
                {
                    using (XLWorkbook workBook = new XLWorkbook(stream))
                    {

                        var workSheet = workBook.Worksheet(1);
                        dataTable.TableName = workSheet.Name;

                        int lastRowIndex = workSheet.LastRowUsed().RowNumber();
                        int lastColumnIndex = workSheet.LastColumnUsed().ColumnNumber();
                        bool header = false;

                        foreach (IXLRow row in workSheet.Rows(1, lastRowIndex))
                        {
                            if (!header)
                            {
                                foreach (IXLCell cell in row.Cells(1, lastColumnIndex))
                                {
                                    dataTable.Columns.Add(cell.GetFormattedString());
                                }
                                header = true;
                            }
                            dataTable.Rows.Add();
                            int i = 0;
                            foreach (IXLCell cell in row.Cells(1, lastColumnIndex))
                            {
                                dataTable.Rows[dataTable.Rows.Count - 1][i] = cell.GetFormattedString();
                                i++;
                            }
                        }
                        dataTable.Rows.RemoveAt(0);
                    }
                }

                return dataTable;
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static void UpdateData(string filePath, DataTable dataTable)
        {
            using (Stream stream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite))
            {
                using (XLWorkbook workBook = new XLWorkbook(stream))
                {
                    var workSheet = workBook.Worksheet(1);
                    if (workSheet != null)
                    {
                        int ColumnIndex = 0;
                        int rowNumber = 1;
                        foreach (DataColumn column in dataTable.Columns)
                        {
                            if (column.ColumnName.Contains("Column Name"))
                            {
                                ColumnIndex = column.Ordinal + 1;
                            }
                        }
                        string colAddress = ColumnAddress(ColumnIndex);

                        foreach (DataRow row in dataTable.Rows)
                        {
                            rowNumber++;
                            workSheet.Cell(rowNumber, ColumnIndex).Value = "Update Value";
                        }
                    }
                    workBook.Save();
                }
            }

        }

        private static string ColumnAddress(int col)
        {
            if (col <= 26)
            {
                return Convert.ToString(Convert.ToChar(col + 64));
            }
            int div = col / 26;
            int mod = col % 26;
            if (mod == 0) { mod = 26; div--; }
            return ColumnAddress(div) + ColumnAddress(mod);
        }

How to update Excel Cell based on validation using ClosedXML?

1 Answers1