C# Working with excel file

Question

After save changes from dataGridView,when open xlsx file by excel this error occurs:

We found a problem with some content in 'Book1.xlsx' Do you want us to recover as much as we can? If you trust the source of this workbook, click Yes.

for (int i = 0; i < dataGridView1.RowCount - 1; i++)
        {
            if (sh.GetRow(i) == null)
                sh.CreateRow(i);

            for (int j = 0; j < dataGridView1.ColumnCount; j++)
            {
                if (sh.GetRow(i).GetCell(j) == null)
                    sh.GetRow(i).CreateCell(j);

                if (dataGridView1[j, i].Value != null)
                {
                    sh.GetRow(i).GetCell(j).SetCellValue(dataGridView1[j, i].Value.ToString());
                }
                using (var fs = new FileStream("Book1.xlsx", FileMode.Open, FileAccess.ReadWrite))
                {
                    wb.Write(fs);
                }
            }
        }

Answer 1

For one thing, you're writing the workbook multiple times, once for each cell. You really should only write the workbook once, at the end, when you are finished making changes.

Also, it is not clear from your question how you are creating the workbook or the sheet. I added some code below to make it clear how that should look as well.

Try it like this:

XSSFWorkbook wb = new XSSFWorkbook();
ISheet sheet = wb.GetSheet("Sheet1") ?? wb.CreateSheet("Sheet1");

for (int i = 0; i < dataGridView1.RowCount; i++)
{
    IRow row = sheet.GetRow(i) ?? sh.CreateRow(i);

    for (int j = 0; j < dataGridView1.ColumnCount; j++)
    {
        ICell cell = row.GetCell(j) ?? row.CreateCell(j);

        if (dataGridView1[j, i].Value != null)
        {
            cell.SetCellValue(dataGridView1[j, i].Value.ToString());
        }
    }
}

using (var fs = new FileStream("Book1.xlsx", FileMode.Create, FileAccess.Write))
{
    wb.Write(fs);
}