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);
                }
            }
        }

little confused by the filestream chunk at the bottom of your for loop, you open a new filestream to book1, and to another item you write that filestream.. — BugFinder
How can I answer how to solve it, if you dont explain a bit that isnt making sense? what is wb? why are you writing a copy of book1 to it every itteration of column count for every row.. — BugFinder

Brian Rogers Brian Rogers · Accepted Answer · 2016-12-07T18:10:28

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);
}

C# Working with excel file

1 Answers