send datagridview to first 20 lines of excel and bal to pg2

goolam ahmod

9y958Windows Forms

How do i send my first 20 lines of datagridview to excel workbook and the balance to a continuation page using c#

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

goolam ahmod9y ago
string filename = @"C:\myfile\myquotes\quotes_" + datestr + ".xls"; For(int =0; i < datagridview1.Rows.Count-1; i++) xlworksheet.Celks[i+19,j+1] = datagridview1.Rows[i].Cells[j].value.ToString(); Xlworksheet.HPageBreaks.Add(xlworksheet.get_range("A50","J50")); xlWorkBook.SaveAs(filename, Excel.XLFormat.xlWorkBookNormal,misvalue,misvalue,misvalue, Excel.XLSaveAsAccessMode.xlExclusive, misvalue, misvalue, misvalue); It does not create the pagebreak.
0
alice yang9y ago
Then change this two lines of code:
sheet.InsertDataTable(datatable, true, 1, 1);
sheet.HPageBreaks.Add(sheet.Range["A21"]);
To:
sheet.InsertDataTable(datatable, true, 19, 1); //insert datatable into excel starts from the 19th row and 1st column.
sheet.HPageBreaks.Add(sheet.Range["A50"]); //Add horizontal page breaks to the 50th row (19th row to 49th row will be on page 1, and the balance to page 2)
After that save the workbook object to a new file use workbook.SaveToFile method.

If my answer helps, please accept it. Thanks
0
goolam ahmod9y ago
Hi. Tkd for the reply. The range stars from A19 to A49. On my datagridview1 there is 60 items. How to get 30 on one workbook saved as quote.cust.date on my excel template and balance to continue as page 2
0
alice yang9y ago
Hi,

You can use Spire.xls to easily export data from your data gridview to excel. First, add reference to spire.xls.dll, then read your gridview data to a data table and use the following code:
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.InsertDataTable(datatable, true, 1, 1);
For only export data to the first 20 lines of excel sheet and the balance to page 2, you can add page break in excel sheet like below:
sheet.HPageBreaks.Add(sheet.Range["A21"]);
Hope my answer helps.
0