将数据从datagridview导出到多个工作表中的excel [英] Export data from datagridview to excel in multiple sheet

问题描述

我有一个数据表，其中有127000行，当我通过程序保存它时，错误是超过65536的行.所以我想要一个程序，通过该程序我的datatable = 65536中的数据应保存在MyExcel.xls中在第一张工作表中，其余行也应保存在MyExcel.xls中，但也要保存在sheet2中.

我制作了一个程序，通过该程序可以将数据表中的数据保存到1048576行，但是假设如果行大于1048576，则超出1048576的其余行应保存在同一excel文件中，但应保存在第二张表中.

将数据集导出到多个Excel工作表 [ http://stackoverflow.com/questions/1064695/export-datatables-into-multi- excel-worksheets [ ^ ]
http://stackoverflow. com/questions/8156616/how-to-to-create-excel-file-with-multiple-sheets-from-dataset-using-c-sharp [ Dim Row，Col，Rowno，ColExcel As 整数 昏暗 appxl Dim excel As 新建 Microsoft.Office. Interop.Excel.ApplicationClass Dim 本书 As Excel.Workbooks Dim Wsheet As Excel.Worksheet Dim 子名称 As String Dim 文件名，文件名，目录名 As 字符串 Dim obj As 新 CodesMarksManager DirName = My.Application.Info.DirectoryPath ' FileName = DirName& .CmbClass.Text& ".xls" appxl = CreateObject(" ) 书= appxl.Workbooks Wsheet = Book.Add.Worksheets( 1 ) ' 设置rngWSheet = Wsheet.Cells appxl.Visible = 真实 Wsheet.Range(Wsheet.Cells( 1 ， 1 )，Wsheet.Cells( 8 )).Merge() Wsheet.Cells( 1 ， 1 )= obj.GetDataFromTable(" ).表格( 0 ).行( 0 )(" ).ToString Wsheet.Cells( 1 ， 1 ).Font.Bold = 真 Wsheet.Cells( 1 ， 1 ).Font.Size = 18 ' 'Wsheet.Cells(1，1).Font.Underline = True Wsheet.Cells( 1 ， 1 ).Horizo​​ntalAlignment = 3 Wsheet.Range(Wsheet.Cells( 2 ， 1 )，Wsheet.Cells( 8 )).Merge() Wsheet.Cells( 2 ， 1 )= " 用于升级学生的综合表" Wsheet.Cells( 2 ， 1 ).Font.Bold = 真 Wsheet.Cells( 2 ， 1 ).Font.Size = 14 ' Wsheet.Cells(2，1).Font.Underline = True Wsheet.Cells( 2 ， 1 ).Horizo​​ntalAlignment = 3 Wsheet.Range(Wsheet.Cells( 3 ， 1 )，Wsheet.Cells( 4 )).Merge() ' Wsheet.Range(Wsheet.Cells(3，1)，Wsheet.Cells(3，8)).Merge( ) Wsheet.Cells( 3 ， 1 )= " 项目名称" Wsheet.Cells( 3 ， 1 ).Font.Bold = 真 Wsheet.Cells( 3 ， 1 ).Font.Size = 12 Wsheet.Range(Wsheet.Cells( 3 ， 5 )，Wsheet.Cells( 9 )).Merge() Wsheet.Cells( 3 ， 5 )= " 品牌:" Wsheet.Cells( 3 ， 5 ).Font.Bold = 真 Wsheet.Cells( 3 ， 5 ).Font.Size = 12 Rowno = 5 ColExcel = 1 对于行= 0 >到 DataGridView3. .Count- 1 ColExcel = 1 Wsheet.Cells(Rowno，ColExcel)= DataGridView3.Rows(Row).Cells( 1 ).Value ColExcel = 5 Wsheet.Cells(Rowno，ColExcel)= DataGridView3.Rows(Row).Cells( 2 ).Value Rowno = Rowno + 1 下一个

I have a data table where i have 127000 rows,when i save it by my program then the error is rows exceeded from 65536.So i want a program by which my data from datatable =65536 should be save in MyExcel.xls in 1st sheet and the remaining rows should also save in MyExcel.xls but in sheet2.

I made a program by which we can save our data from datatable till 1048576 rows but suppose if rows are greater than 1048576, the remaining rows which exceeded from 1048576 should be save in same excel file but in 2nd sheet.

解决方案

These links may help you
Export DataSet to Multiple Excel Sheets[^]
http://stackoverflow.com/questions/1064695/export-datatables-into-multiple-excel-worksheets[^]
http://stackoverflow.com/questions/8156616/how-to-create-excel-file-with-multiple-sheets-from-dataset-using-c-sharp[^]

Dim Row, Col, Rowno, ColExcel As Integer
     Dim appxl
     Dim excel As New Microsoft.Office.Interop.Excel.ApplicationClass
     Dim Book As Excel.Workbooks
     Dim Wsheet As Excel.Worksheet
     Dim SubName As String
     Dim FileName, ff, DirName As String
     Dim obj As New CodesMarksManager
     DirName = My.Application.Info.DirectoryPath
     'FileName = DirName & .CmbClass.Text & ".xls"
     appxl = CreateObject("Excel.Application")
     Book = appxl.Workbooks
     Wsheet = Book.Add.Worksheets(1)
     'Set rngWSheet = Wsheet.Cells
     appxl.Visible = True
     Wsheet.Range(Wsheet.Cells(1, 1), Wsheet.Cells(1, 8)).Merge()
     Wsheet.Cells(1, 1) = obj.GetDataFromTable("Select School_Name From SchoolDetails").Tables(0).Rows(0)("School_Name").ToString

     Wsheet.Cells(1, 1).Font.Bold = True
     Wsheet.Cells(1, 1).Font.Size = 18
     '' Wsheet.Cells(1, 1).Font.Underline = True
     Wsheet.Cells(1, 1).HorizontalAlignment = 3
     Wsheet.Range(Wsheet.Cells(2, 1), Wsheet.Cells(2, 8)).Merge()
     Wsheet.Cells(2, 1) = "Consolidated Sheet for Upgrading Students"
     Wsheet.Cells(2, 1).Font.Bold = True
     Wsheet.Cells(2, 1).Font.Size = 14
     'Wsheet.Cells(2, 1).Font.Underline = True
     Wsheet.Cells(2, 1).HorizontalAlignment = 3

     Wsheet.Range(Wsheet.Cells(3, 1), Wsheet.Cells(3, 4)).Merge()
     ' Wsheet.Range(Wsheet.Cells(3, 1), Wsheet.Cells(3, 8)).Merge()
     Wsheet.Cells(3, 1) = "Item Name "
     Wsheet.Cells(3, 1).Font.Bold = True
     Wsheet.Cells(3, 1).Font.Size = 12

     Wsheet.Range(Wsheet.Cells(3, 5), Wsheet.Cells(3, 9)).Merge()
     Wsheet.Cells(3, 5) = "Brand :"
     Wsheet.Cells(3, 5).Font.Bold = True
     Wsheet.Cells(3, 5).Font.Size = 12
     Rowno = 5
     ColExcel = 1
     For Row = 0 To DataGridView3.Rows.Count - 1
         ColExcel = 1
         Wsheet.Cells(Rowno, ColExcel) = DataGridView3.Rows(Row).Cells(1).Value
         ColExcel = 5
         Wsheet.Cells(Rowno, ColExcel) = DataGridView3.Rows(Row).Cells(2).Value
         Rowno = Rowno + 1
     Next

这篇关于将数据从datagridview导出到多个工作表中的excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！