Export DataTable to specific column and row using SPIRE.xls

[sinlao15]

Good day,
I was wondering on whether I can export all the data from a datatable and insert it on a
specified column and row of the excel. I already have a datatable from sql that is displaying
the data to the datagridview1. I would like to use the data from the datagridview1 in order
to insert to a specified column and row of the excel.

I am using FreeSpire.xls and FreeSpire.office for Windows Application C#

[Wenly.Zhang]

Hello,

Thanks for your inquiry.
You can add the data of datagridview1 to the specified rows and columns of the Excel file using the following code：

Code: Select all

            Workbook workbook1 = new Workbook();
            Worksheet worksheet1 = workbook.Worksheets[0];
            //Create an empty worksheet
            workbook1.CreateEmptySheets(1);
            DataTable newDt = (DataTable)dataGridView1.DataSource;

            int rowCount1 = newDt.Rows.Count;
            int colCount1 = newDt.Columns.Count;

            //Specify the starting row and column to insert data into
            int startRow1 = 3;
            int startCol1 = 2;
            //Traverse DataTable and insert data into Excel
            for (int i = 0; i < rowCount1; i++)
            {
                for (int j = 0; j < colCount1; j++)
                {
                    // Obtain data from DataTable
                    object data = newDt.Rows[i][j];
                    //Insert data into specified cells
                    worksheet.Range[startRow1 + i, startCol1 + j].Text = data.ToString();
                }
            }
            workbook.SaveToFile("F:\\result.xlsx");

If you have any other questions or concerns, please feel free to contact us anytime.

Sincerely,
Wenly
E-iceblue support team

[sinlao15]

Thanks for helping, I forgot to mention that some of the columns and rows are merged together, for example, A5 up to E5 is merged together.

An example output would be:

A5:E5 = Column 1, Row 1 of the datagridview.
F5 = Column 1, Row 2 of the datagridview.
and so on.

Is it possible to export or should the columns and rows not be merged?

Thanks for the reply

[Wenly.Zhang]

Hello,

Thank you for your response.
I'm sorry I didn't understand your requirements very clearly. Are you saying that you want to insert the data from the datagrid view into an Excel file with multiple merged rows and columns by column? To help us investigate further, please provide us with the rendering you want to achieve such as Screenshot with description and your Excel file. You could attach them here or send them to us via email (support@e-iceblue.com). Thanks in advance.

Sincerely,
Wenly
E-iceblue support team

[sinlao15]

Good day,

I have emailed the screenshot of the excel as well as the datagridview.

The excel that is being loaded has merged cells in different worksheets.

For example, in Worksheet 1, A5 up to C5 are merged cells while D5 is a single cells. In the datagridview Column 1, Row 1 has the data "Martin" and Column 2, Row 1 has the data "Gallows".

How can I input the data "Martin" to the merged cells A5 to C5 and "Gallows" to D5?

The expected output would be the merged cells A5 to C5 has the data "Martin" and D5 has the data "Gallows"

[Wenly.Zhang]

Hello,

Thanks for sharing the information via email.
Please use the following code to insert data from datagridview into an Excel file with merged cells：

Code: Select all

   Workbook workbook = new Workbook();
            workbook.LoadFromFile("F:\\test.xlsx");
            Worksheet worksheet1 = workbook.Worksheets[0];
            DataTable newDt = (DataTable)dataGridView1.DataSource;
            //The line number for data insertion, starting from line 5
            int startRow = 5;
             //insert data
            foreach (DataRow row in newDt.Rows)
            {
   //Insert data from dataTable into corresponding rows and columns
                worksheet1.Range["A" + startRow].Text = row[0].ToString();
                worksheet1.Range["F" + startRow].Text = row[1].ToString();
                worksheet1.Range["G" + startRow].Text = row[2].ToString();
                startRow++;
            }
            workbook.SaveToFile("F:\\result.xlsx");

If you have any other questions or concerns, please feel free to contact us anytime.

Sincerely,
Wenly
E-iceblue support team

[sinlao15]

Hello,

Thanks for sharing the information via email.
Please use the following code to insert data from datagridview into an Excel file with merged cells：

Code: Select all

   Workbook workbook = new Workbook();
            workbook.LoadFromFile("F:\\test.xlsx");
            Worksheet worksheet1 = workbook.Worksheets[0];
            DataTable newDt = (DataTable)dataGridView1.DataSource;
            //The line number for data insertion, starting from line 5
            int startRow = 5;
             //insert data
            foreach (DataRow row in newDt.Rows)
            {
   //Insert data from dataTable into corresponding rows and columns
                worksheet1.Range["A" + startRow].Text = row[0].ToString();
                worksheet1.Range["F" + startRow].Text = row[1].ToString();
                worksheet1.Range["G" + startRow].Text = row[2].ToString();
                startRow++;
            }
            workbook.SaveToFile("F:\\result.xlsx");

If you have any other questions or concerns, please feel free to contact us anytime.

Sincerely,
Wenly
E-iceblue support team

This solves it. Thank you for assisting me.

[Wenly.Zhang]

Hello,

Thank you for your feedback.
Glad to hear that the solution I provided resolved your issue. If you encounter other issues related to our products in the future, please feel free to contact us.
Have a nice day！

Sincerely,
Wenly
E-iceblue support team