Export to excel [modified]
-
hi all, i need to export lakhs of records to excel, if i use the following code means, it doesn't throws any error, select top 50000 * from dbo.CustomerDetail but instead if i use select * from dbo.CustomerDetail (it contains more than 10lakhs of records), it throws me an error.... Significant loss of functionality - the workbook contains data in cells outside of the row and column limit. data beyond 256(IV) columns by 65,536 rows will not be saved..... my need is, if i have a table, it contains 70,000 records, i need to export it to excel, at that time it should automatically split the datas to two worksheets (which means 65000 in sheet1 and remaining 5000 in sheet2)... code snippet in vb.net: Imports Microsoft.Office.Interop.Excel Imports System.Data.SqlClient Imports System.Threading Public Class Form1 Dim dtstart As DateTime = DateTime.Now Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strUniqueParamId As String strUniqueParamId = System.Guid.NewGuid().ToString Dim xThread As New Thread(AddressOf excelcheck) xThread.Name = strUniqueParamId xThread.Start() MessageBox.Show("Generating") Timer1.Start() Thread.Sleep(1700) 'MessageBox.Show(Environment.TickCount.ToString()); End Sub Sub excelcheck() ' Create the Excel Application object Dim excelApp As New ApplicationClass() ' Create a new Excel Workbook Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing) Dim sheetIndex As Integer = 0 Dim col, row As Integer Dim excelSheet As Worksheet Dim sqlcon As New SqlConnection("server=SHRIGI104\SHRIUNO0301;database=Test;uid=sa;pwd=welcome3#;") sqlcon.Open() Dim ds As New DataSet Dim Da As New SqlDataAdapter() Dim sqlcmd As New SqlCommand sqlcmd.CommandType = CommandType.StoredProcedure sqlcmd.CommandText = "SampleExcel" sqlcmd.Connection = sqlcon Da.SelectCommand = sqlcmd Da.Fill(ds) sqlcon.Close() ' Copy each DataTable as a new Sheet For Each dt As System.Data.DataTable In ds.Tables sheetIndex += 1 ' Copy the DataTable to an object array Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object ' Copy the column names to the first row of the object array
-
hi all, i need to export lakhs of records to excel, if i use the following code means, it doesn't throws any error, select top 50000 * from dbo.CustomerDetail but instead if i use select * from dbo.CustomerDetail (it contains more than 10lakhs of records), it throws me an error.... Significant loss of functionality - the workbook contains data in cells outside of the row and column limit. data beyond 256(IV) columns by 65,536 rows will not be saved..... my need is, if i have a table, it contains 70,000 records, i need to export it to excel, at that time it should automatically split the datas to two worksheets (which means 65000 in sheet1 and remaining 5000 in sheet2)... code snippet in vb.net: Imports Microsoft.Office.Interop.Excel Imports System.Data.SqlClient Imports System.Threading Public Class Form1 Dim dtstart As DateTime = DateTime.Now Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strUniqueParamId As String strUniqueParamId = System.Guid.NewGuid().ToString Dim xThread As New Thread(AddressOf excelcheck) xThread.Name = strUniqueParamId xThread.Start() MessageBox.Show("Generating") Timer1.Start() Thread.Sleep(1700) 'MessageBox.Show(Environment.TickCount.ToString()); End Sub Sub excelcheck() ' Create the Excel Application object Dim excelApp As New ApplicationClass() ' Create a new Excel Workbook Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing) Dim sheetIndex As Integer = 0 Dim col, row As Integer Dim excelSheet As Worksheet Dim sqlcon As New SqlConnection("server=SHRIGI104\SHRIUNO0301;database=Test;uid=sa;pwd=welcome3#;") sqlcon.Open() Dim ds As New DataSet Dim Da As New SqlDataAdapter() Dim sqlcmd As New SqlCommand sqlcmd.CommandType = CommandType.StoredProcedure sqlcmd.CommandText = "SampleExcel" sqlcmd.Connection = sqlcon Da.SelectCommand = sqlcmd Da.Fill(ds) sqlcon.Close() ' Copy each DataTable as a new Sheet For Each dt As System.Data.DataTable In ds.Tables sheetIndex += 1 ' Copy the DataTable to an object array Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object ' Copy the column names to the first row of the object array
1. Having that much records in an excel file! Why? 2. Why are you using interop? You can connect to the excel file through OleDb Connection and export everything to it through one insert command.
50-50-90 rule: Anytime I have a 50-50 chance of getting something right, there's a 90% probability I'll get it wrong...!!
-
hi all, i need to export lakhs of records to excel, if i use the following code means, it doesn't throws any error, select top 50000 * from dbo.CustomerDetail but instead if i use select * from dbo.CustomerDetail (it contains more than 10lakhs of records), it throws me an error.... Significant loss of functionality - the workbook contains data in cells outside of the row and column limit. data beyond 256(IV) columns by 65,536 rows will not be saved..... my need is, if i have a table, it contains 70,000 records, i need to export it to excel, at that time it should automatically split the datas to two worksheets (which means 65000 in sheet1 and remaining 5000 in sheet2)... code snippet in vb.net: Imports Microsoft.Office.Interop.Excel Imports System.Data.SqlClient Imports System.Threading Public Class Form1 Dim dtstart As DateTime = DateTime.Now Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strUniqueParamId As String strUniqueParamId = System.Guid.NewGuid().ToString Dim xThread As New Thread(AddressOf excelcheck) xThread.Name = strUniqueParamId xThread.Start() MessageBox.Show("Generating") Timer1.Start() Thread.Sleep(1700) 'MessageBox.Show(Environment.TickCount.ToString()); End Sub Sub excelcheck() ' Create the Excel Application object Dim excelApp As New ApplicationClass() ' Create a new Excel Workbook Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing) Dim sheetIndex As Integer = 0 Dim col, row As Integer Dim excelSheet As Worksheet Dim sqlcon As New SqlConnection("server=SHRIGI104\SHRIUNO0301;database=Test;uid=sa;pwd=welcome3#;") sqlcon.Open() Dim ds As New DataSet Dim Da As New SqlDataAdapter() Dim sqlcmd As New SqlCommand sqlcmd.CommandType = CommandType.StoredProcedure sqlcmd.CommandText = "SampleExcel" sqlcmd.Connection = sqlcon Da.SelectCommand = sqlcmd Da.Fill(ds) sqlcon.Close() ' Copy each DataTable as a new Sheet For Each dt As System.Data.DataTable In ds.Tables sheetIndex += 1 ' Copy the DataTable to an object array Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object ' Copy the column names to the first row of the object array
Well, your choices are to change your proc to select all the data and put some loop method in your code to output 65,000 records per sheet and start a new sheet, OR use dynamic sql to generate the appropriate number of selects and then execute them within the stored proc. I assume your table has a unique key that you can use to control the selection of each 50,000 rows?
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
hi all, i need to export lakhs of records to excel, if i use the following code means, it doesn't throws any error, select top 50000 * from dbo.CustomerDetail but instead if i use select * from dbo.CustomerDetail (it contains more than 10lakhs of records), it throws me an error.... Significant loss of functionality - the workbook contains data in cells outside of the row and column limit. data beyond 256(IV) columns by 65,536 rows will not be saved..... my need is, if i have a table, it contains 70,000 records, i need to export it to excel, at that time it should automatically split the datas to two worksheets (which means 65000 in sheet1 and remaining 5000 in sheet2)... code snippet in vb.net: Imports Microsoft.Office.Interop.Excel Imports System.Data.SqlClient Imports System.Threading Public Class Form1 Dim dtstart As DateTime = DateTime.Now Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strUniqueParamId As String strUniqueParamId = System.Guid.NewGuid().ToString Dim xThread As New Thread(AddressOf excelcheck) xThread.Name = strUniqueParamId xThread.Start() MessageBox.Show("Generating") Timer1.Start() Thread.Sleep(1700) 'MessageBox.Show(Environment.TickCount.ToString()); End Sub Sub excelcheck() ' Create the Excel Application object Dim excelApp As New ApplicationClass() ' Create a new Excel Workbook Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing) Dim sheetIndex As Integer = 0 Dim col, row As Integer Dim excelSheet As Worksheet Dim sqlcon As New SqlConnection("server=SHRIGI104\SHRIUNO0301;database=Test;uid=sa;pwd=welcome3#;") sqlcon.Open() Dim ds As New DataSet Dim Da As New SqlDataAdapter() Dim sqlcmd As New SqlCommand sqlcmd.CommandType = CommandType.StoredProcedure sqlcmd.CommandText = "SampleExcel" sqlcmd.Connection = sqlcon Da.SelectCommand = sqlcmd Da.Fill(ds) sqlcon.Close() ' Copy each DataTable as a new Sheet For Each dt As System.Data.DataTable In ds.Tables sheetIndex += 1 ' Copy the DataTable to an object array Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object ' Copy the column names to the first row of the object array
You may want to do a little test first to see how much of your data you can actually fit in a WORKBOOK, not a worksheet. Yeah, you're going to exceed the number of rows available in a single sheet, and that's handled with a bit of work on your part. But, you're bigger problem is that you're probably going to run Excel out of memory before you get through all of your customers. The test is easy. Take a small sample of records from your database and export them to an Excel sheet. Then copy and paste those records over and over again filling up the sheet as much as possible. Then create a new sheet and paste the records into that, filling up the sheet as much as possible. I think you'll find that you run Excel out of memory fairly quickly and long before you get through all of your customers.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007, 2008
But no longer in 2009... -
hi all, i need to export lakhs of records to excel, if i use the following code means, it doesn't throws any error, select top 50000 * from dbo.CustomerDetail but instead if i use select * from dbo.CustomerDetail (it contains more than 10lakhs of records), it throws me an error.... Significant loss of functionality - the workbook contains data in cells outside of the row and column limit. data beyond 256(IV) columns by 65,536 rows will not be saved..... my need is, if i have a table, it contains 70,000 records, i need to export it to excel, at that time it should automatically split the datas to two worksheets (which means 65000 in sheet1 and remaining 5000 in sheet2)... code snippet in vb.net: Imports Microsoft.Office.Interop.Excel Imports System.Data.SqlClient Imports System.Threading Public Class Form1 Dim dtstart As DateTime = DateTime.Now Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim strUniqueParamId As String strUniqueParamId = System.Guid.NewGuid().ToString Dim xThread As New Thread(AddressOf excelcheck) xThread.Name = strUniqueParamId xThread.Start() MessageBox.Show("Generating") Timer1.Start() Thread.Sleep(1700) 'MessageBox.Show(Environment.TickCount.ToString()); End Sub Sub excelcheck() ' Create the Excel Application object Dim excelApp As New ApplicationClass() ' Create a new Excel Workbook Dim excelWorkbook As Workbook = excelApp.Workbooks.Add(Type.Missing) Dim sheetIndex As Integer = 0 Dim col, row As Integer Dim excelSheet As Worksheet Dim sqlcon As New SqlConnection("server=SHRIGI104\SHRIUNO0301;database=Test;uid=sa;pwd=welcome3#;") sqlcon.Open() Dim ds As New DataSet Dim Da As New SqlDataAdapter() Dim sqlcmd As New SqlCommand sqlcmd.CommandType = CommandType.StoredProcedure sqlcmd.CommandText = "SampleExcel" sqlcmd.Connection = sqlcon Da.SelectCommand = sqlcmd Da.Fill(ds) sqlcon.Close() ' Copy each DataTable as a new Sheet For Each dt As System.Data.DataTable In ds.Tables sheetIndex += 1 ' Copy the DataTable to an object array Dim rawData(dt.Rows.Count, dt.Columns.Count - 1) As Object ' Copy the column names to the first row of the object array
I'm not sure about the memory issues, as a powerful enough machine should solved that problem, however it sounds as if you are using Excel 2003. Excel 2003 is limited to 65,536 rows by 256 columns per worksheet. The number of worksheets is only limited by available memory. If you move to Excel 2007 you can go up the the limit of 1,048,576 rows by 16,384 column per worksheet, with the number of worksheets is limited by available memory. So either loop and create multiple sheets, or upgrade to 2007. Either way ensure you have a shed-load of memory available. Excel 2003 Limits[^] Excel 2007 Limits[^]
If you have knowledge, let others light their candles at it. Margaret Fuller (1810 - 1850) [My Articles] [My Website]