You have multiple questions here. For starters, if you want to add multiple selections to the same workbook, then you will need to do some things differently…
check to see if the file already exists, if not, then create a new one, if it does exist then you will need to “open” it.
If the file already exists, then, after opening the file, you will need to check if the worksheet "Exported from gridview" exists… if it does exist, then, you will need to find the last used row and start adding the additional rows after that last row.
Finally save the file.
Currently, the code is simply “creating” a new workbook, then the code is overwriting the existing workbook if it already exists. So, if you want to “add” additional selections to the “same” workbook, you will need to add the code that checks if the file exists, and if so, find the next available row on the worksheet to add the additional items to as explained in the previous 3 steps above.
Below is a simple example that works however it was not tested well and I am confident you may need to adjust the code to fit your requirements.
First, in the GetDgvToTable method, it appears the code is grabbing the wrong rows when creating the table….
for (int count = 0; count < dgv.SelectedRows.Count; count++)
{
DataRow dr = dt.NewRow();
for (int countsub = 0; countsub < dgv.Columns.Count; countsub++)
//for (int countsub = 0; countsub < dgv.SelectedRows.Count; countsub++)
{
dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
}
dt.Rows.Add(dr);
}
Above, the code is looping through the “number of selected” rows. The problem is on the line…
dr[countsub] = Convert.ToString(dgv.Rows[count].Cells[countsub].Value);
Specifically, at …
dgv.Rows[count].
Here the code is ignoring the “SELECTED” rows. In other words, if the “selected” rows were contiguous “selected” rows starting from the “first” row (0), then it will work. Unfortunately, the first “selected” row may not necessarily be the “first” row in the grid. The code is incorrectly making this assumption.
To fix this, I suggest you loop through the “selected” rows using a foreach loop through grids SelectedRows collection. This will ensure the code uses the “SELECTED” rows. The code change would look something like…
foreach (DataGridViewRow row in dgv.SelectedRows) {
DataRow dr = dt.NewRow();
for (int i = 0; i < row.Cells.Count; i++) {
dr[i] = row.Cells[i].Value.ToString();
}
dt.Rows.Add(dr);
}
Next, you state you want to additional selections to be added to the same worksheet with an empty row between the selections. This was previously discussed above. In the code below, it checks to see if the Excel file already exist and if it does, then opens that file. Next a check is made to see if the worksheet already exists. If it does not exist, then a new one is created.
Next a check is made to see if any “previous” selections have already been added to the worksheet. I am not that familiar with Spire, however, I noted that if you call the method…
sheet.LastRow…
It will return the next available row… EXCEPT if the worksheet is empty. When the worksheet is empty, this will return a value like 65,570. I will leave this to you to possibly figure out a more elegant way to get the next empty row. In this case I simply checked if the sheet.LastRow was over 60000. If the value is over 60000, then I assume the sheet is empty and simply set the next available row to 1. I am confident there is a better way to do this.
Given all this, the changes below to both methods appears to do as you want by adding the additional selection to the same worksheet “below” the previous selections.
public DataTable GetDgvToTable(DataGridView dgv) {
DataTable dt = new DataTable();
//Column
for (int count = 0; count < dgv.Columns.Count; count++) {
DataColumn dc = new DataColumn(dgv.Columns[count].Name.ToString());
dt.Columns.Add(dc);
}
//Row
foreach (DataGridViewRow row in dgv.SelectedRows) {
DataRow dr = dt.NewRow();
for (int i = 0; i < row.Cells.Count; i++) {
dr[i] = row.Cells[i].Value.ToString();
}
dt.Rows.Add(dr);
}
decimal total = dataGridView1.SelectedRows.OfType<DataGridViewRow>()
.Sum(t => Convert.ToDecimal(t.Cells[2].Value));
dt.Rows.Add("","Total",total);
return dt;
}
Then the changes to the Export method.
private void btnExportToExcel_Click(object sender, EventArgs e) {
Workbook book = new Workbook();
if (File.Exists(@"pathToFile\Report.xlsx")) {
book.LoadFromFile(@"pathToFile\Report.xlsx");
}
Worksheet sheet = book.Worksheets["Exported from gridview"];
if (sheet == null) {
sheet = book.CreateEmptySheet("Exported from gridview");
}
//Convert data from datagridview to datatable
DataTable dt = GetDgvToTable(dataGridView1);
//Export datatable to excel
int startRow = sheet.LastRow + 2;
if (startRow > 60000) {
startRow = 1;
}
sheet.InsertDataTable(dt, true, startRow, 1, -1, -1);
sheet.Range[1, 1, sheet.LastRow, sheet.LastColumn].AutoFitColumns();
sheet.AllocatedRange.BorderAround(LineStyleType.Thin, borderColor: ExcelColors.Black);
sheet.AllocatedRange.BorderInside(LineStyleType.Thin, borderColor: ExcelColors.Black);
book.SaveToFile(@"pathToFile\Report.xlsx", ExcelVersion.Version2013);
book.Dispose();
MessageBox.Show("Export complete");
}
I hope this makes sense and helps.
