Click here to Skip to main content
15,901,853 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,

I have thousand's of record in one datatable.
Now i have to split the data to 450 records and every 450 records to be stored in excel sheets that creates dynamically .(1 workbook multiple sheets).
Please help me.

Thanks in advance.

From answers

i have like this.. below is the c# code which i tried. but here, in one sheet everything is over written. and in sheet 1 last 450 records are getting displayed...
C#
int x = (table1.Rows.Count / 450);

                for (int i = 0; i < x; i++)
                {
                    System.Data.DataTable dtfind = table1.AsEnumerable().Take(450).CopyToDataTable();
                    int r = 1;
                    ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets[i + 1];
                    //Writing Columns Name in Excel Sheet
                    for (int col = 1; col <= table1.Columns.Count; col++)

                        ExcelWorkSheet.Cells[r, col] = table1.Columns[col - 1].ColumnName;
                    r++;
                    //Writing Rows into Excel Sheet
                    //r stands for ExcelRow and col for ExcelColumn      

                    for (int row = 0; row < dtfind.Rows.Count; row++)
                    {
                        // Excel row and column start positions for writing Row=1 and Col=1                     
                        for (int col = 1; col <= dtfind.Columns.Count; col++)
                        {
                            ExcelWorkSheet.Cells[r, col] = dtfind.Rows[row][col - 1].ToString();

                        }
                        dtfind.Rows[row].Delete();
                        dtfind.AcceptChanges();
                        r++;
                    }
                    ExcelWorkBook.Worksheets.Add();
                }
Posted
Updated 19-Oct-15 23:36pm
v5
Comments
Kornfeld Eliyahu Peter 20-Oct-15 5:17am    
Use SQL's range functions to split the table into pages of 450...
Member 11827873 20-Oct-15 5:18am    
can u please tell me how..
Kornfeld Eliyahu Peter 20-Oct-15 5:23am    
Not exactly, as it depends on the schema of your data (it involves some order by statements)...
You should read about ranking functions in SQL help...
Herman<T>.Instance 20-Oct-15 5:17am    
What have you tried? EPPLUS?
Member 11827873 20-Oct-15 5:22am    
i have like this.. below is the c# code which i tried. but here, in one sheet everything is over written. and in sheet 1 last 450 records are getting displayed...
int x = (table1.Rows.Count / 450);

for (int i = 0; i < x; i++)
{
System.Data.DataTable dtfind = table1.AsEnumerable().Take(450).CopyToDataTable();
int r = 1;
ExcelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelWorkBook.Worksheets[i + 1];
//Writing Columns Name in Excel Sheet
for (int col = 1; col <= table1.Columns.Count; col++)

ExcelWorkSheet.Cells[r, col] = table1.Columns[col - 1].ColumnName;
r++;
//Writing Rows into Excel Sheet
//r stands for ExcelRow and col for ExcelColumn

for (int row = 0; row < dtfind.Rows.Count; row++)
{
// Excel row and column start positions for writing Row=1 and Col=1
for (int col = 1; col <= dtfind.Columns.Count; col++)
{
ExcelWorkSheet.Cells[r, col] = dtfind.Rows[row][col - 1].ToString();

}
dtfind.Rows[row].Delete();
dtfind.AcceptChanges();
r++;
}
ExcelWorkBook.Worksheets.Add();
}

The reason of "overwriten" is here:
C#
System.Data.DataTable dtfind = table1.AsEnumerable().Take(450).CopyToDataTable();

Everytime you get first 450 records only. To be able to fetch next portion of data, you should Skip[^] a set of data which should be calculated as n*450.

C#
System.Data.DataTable dtfind = table1.AsEnumerable().Skip(i*450).Take(450).CopyToDataTable();


Example:
C#
List<int> numbers = Enumerable.Range(1,5000).ToList();
for(int i=0;i<numbers.Count;i++)
{
    var result = numbers.Skip(i*450).Take(450).ToList();
    //returns List<int>
    //if i == 0 then returns range 1-450
    //if i == 1 then returns range 451-900
    //and so on..
}


Tip:
Instead of copying cell by cell, use faster method. See: How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET [^] and Fast Exporting from DataSet to Excel[^]
 
Share this answer
 
v2
Looks like there is a flow in this code:
C#
int x = (table1.Rows.Count / 450);
    for (int i = 0; i < x; i++) {
    ...
    }

If I don't make a mistake, it will fill only full sheets and miss the last partial sheet.
if there are 850 records, the resulting workbook will have only 1 sheet of 450 records.
with this change, the last partial sheet will be included:
C#
int x = table1.Rows.Count;
    for (int i = 0; i < x; i+= 450) {
    ...
    }


I don't know why you split your base in multi sheets of 450 records, but it is certainly easier to put all records in 1 sheet.
Since Excel 2007, the limit is 1,048,576 rows per sheet.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900