split every 450 records of one datatable to multiple excel sheets using c# .net

Question

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 19-Oct-15 23:08pm

Member 11827873

Updated 19-Oct-15 23:36pm

Herman<T>.Instance

v5

Add a Solution

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();
}

Herman<T>.Instance 20-Oct-15 5:27am

you only take 450, you do not set the starting point x by using Skip(x*450)

Member 11827873 20-Oct-15 5:29am

should i do like this?
var Dtfind450= dt.Rows.OfType<datarow>().Skip(i * countneeded).Take(countneeded).ToArray

Herman<T>.Instance 20-Oct-15 5:33am

yup

Member 11827873 20-Oct-15 6:01am

can u please guide me further. because it is in array, how will the for loops work here.. because it is copying cells to cells and now i need to do for array

Herman<T>.Instance 20-Oct-15 5:35am

for 1 million records you should get 2223 pages.....

Member 11827873 20-Oct-15 5:37am

no no, the table would be in range 5000 -50000 records

Herman<T>.Instance 20-Oct-15 6:34am

have you got it working already?

Member 11827873 20-Oct-15 7:29am

No,
The above code is copying all the records in sheet1.
and add the number of sheets. but the control is not moving to the next shhet. means the sheet2 is not actvated

Herman<T>.Instance 20-Oct-15 8:30am

isn't there a sheet collection to which you add sheets?

Member 11827873 20-Oct-15 8:58am

No.. I dont have any sheets collections nor datatable collections nor dataset collections. I am just using one datatable.. So records start copying to excel sheets and new sheet will be added(created) when it finishes copying 25 records... If you have any other idea also thats fine.I will really be happy to learn.. but the sheets should be dynamically created.

Herman<T>.Instance 20-Oct-15 5:28am

you devide by 450, but if the remainder is not 0, the last records are never shown.
int x = (table1.Rows.Count / 450) +1;

Patrice T 20-Oct-15 16:28pm

I fear your code fail when the number of rows is exactly a multiple of 450.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Maciej Los · Answer 1 · 2015-10-20T08:58:00

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[^]

Patrice T · Answer 2 · 2015-10-20T10:27:00

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.

split every 450 records of one datatable to multiple excel sheets using c# .net

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0