Importing data from DataGridView to existing Excel file using GemBox

Question

I am fairly new to programming and GemBox. I found this code that inserts a data from a DataGridView to an existing Excel sheet with headers and footers. What happens with the code is it replaces the exisiting excel file totally and removes all the headers and footers. What I want to do is just insert the data starting from cell A:9 without removing the pre-existing data from other excel cells. Is there anyway to do this using GemBox?

private void replace_Click(object sender, EventArgs e)
        {
            var saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "XLSX files (*.xlsx)|*.xlsx";
            saveFileDialog.FilterIndex = 3;

            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                var workbook = new ExcelFile();
                var worksheet = workbook.Worksheets.Add("Sheet1");

                var options = new ImportFromDataGridViewOptions();
                options.ColumnHeaders = false;
                options.StartRow = 8;       
                options.StartColumn = 0;

                DataGridViewConverter.ImportFromDataGridView(worksheet, this.dataGridView1, options);

                workbook.Save(saveFileDialog.FileName);
            }
        }

Any help would greatly be appreciated.

Your question is confusing. You state that… _”I have a code that should insert a data from a DataGridView to an existing Excel sheet with headers and footers.”_ … but the code appears to be “adding” a new worksheet with… `var worksheet = workbook.Worksheets.Add("Sheet1");` ? I am guessing that you may want something like… `var worksheet = workbook.Worksheets[0]` … it is unclear what index the worksheet is. — JohnG, May 06 '21 at 01:53
I'm sorry for the confusion. I would want to open an excel file and work with the active worksheet. The file only contains a single worksheet but the worksheet name varies. — cc_newbie, May 06 '21 at 01:58
Edited my question. I hope this makes it a little less confusing. — cc_newbie, May 06 '21 at 02:16
As I said, the line of code… `var worksheet = workbook.Worksheets.Add("Sheet1");` … is going to “add” a worksheet named “Sheet1.” I will assume from your question that it is “creating” a new worksheet. I am guessing that if the worksheet, “Sheet1”, already exist, then it may be overwriting it or creating a new worksheet named `Sheet1(1).` Have you checked to see if the final workbook as two (2) worksheets? In either case, changing the code to… `var worksheet = workbook.Worksheets[0];` should fix this if there is only one worksheet. — JohnG, May 06 '21 at 02:25

score 0 · Accepted Answer · answered May 06 '21 at 07:31

Use this to import the data from DataGridView to an existing Excel sheet:

private void replace_Click(object sender, EventArgs e)
{
    var saveFileDialog = new SaveFileDialog();
    saveFileDialog.Filter = "XLSX files (*.xlsx)|*.xlsx";

    if (saveFileDialog.ShowDialog() == DialogResult.OK)
    {
        var workbook = ExcelFile.Load("Path to your existing Excel file.");
        var worksheet = workbook.Worksheets.ActiveWorksheet;
        var options = new ImportFromDataGridViewOptions("A9");

        DataGridViewConverter.ImportFromDataGridView(worksheet, this.dataGridView1, options);
        workbook.Save(saveFileDialog.FileName);
    }
}

Note, this will import the data starting from cell A9 and replace any existing data that is inside the range where the import was done.

In other words, if you have an Excel file that already has some data in cells A9, B9, etc. they will end up being overridden.

If you don't want that, then insert empty rows to make room for DataGridView data, like this:

worksheet.Rows.InsertEmpty(8, this.dataGridView1.Rows.Count);

var options = new ImportFromDataGridViewOptions("A9");
DataGridViewConverter.ImportFromDataGridView(worksheet, this.dataGridView1, options);

Exactly what I was looking for. Thanks! – cc_newbie May 06 '21 at 23:59 — cc_newbie, May 06 '21 at 23:59

Importing data from DataGridView to existing Excel file using GemBox

1 Answers1