Click here to Skip to main content
15,892,199 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I´m exporting Datatable to Excel workbook. Problem is that excel can limited rows in every sheet.

So..

How can i divide one Datatable to two Datatable.

Then i can have Datatable1 in sheet1 and Datatable2 in sheet2

any idea?.
Posted
Updated 3-Jan-12 23:52pm
v5
Comments
Supriya Srivastav 4-Jan-12 5:56am    
Is there any criteria for seperating datatables,I mean on which basis you want to divide your datatable
Prince Antony G 4-Jan-12 6:00am    
i need to upload 1000 records in one excel sheet, remaining into another sheet..
Is this Possible.
visnumca123 4-Jan-12 6:09am    
Please go through the link below!for copy datatable rows
http://forums.asp.net/t/352061.aspx/1
Prince Antony G 4-Jan-12 6:33am    
thanks for ur reply..
Prince Antony G 6-Jan-12 4:50am    
Thanks for all you gave idea and solution to me...

 
Share this answer
 
Comments
Prince Antony G 4-Jan-12 6:33am    
thanks for ur reply..
Prince Antony G 6-Jan-12 4:31am    
its working fine...Thanks..
only for loop can help you to split dataset
 
Share this answer
 
Comments
Prince Antony G 4-Jan-12 6:07am    
how?
[no name] 4-Jan-12 6:15am    
list <datarow> listRow = new list<datarow>();
foreach( datatable dt in ds.tables )
{
foreach( datarow dr in dt.datarows )
{
listrow.add(dr);
}
}
LIst <dataset> lsi = new list<dataset>();

for(int i = 0 ; i<listrow.count;i++)
{
if(i%600000 == 0)
{
lsi.add(new datasaet());
}
lsi[lsi.count - 1].rows.add(listrow[i]);
}

I suppose this logic will solve your problem.
Prince Antony G 4-Jan-12 6:33am    
thanks for ur reply..i will check it..
[no name] 4-Jan-12 6:36am    
Welcome
Try logic below,

DataTable dt = new DataTable();
        dt.Columns.Add("SNo", typeof(int));
        dt.Columns.Add("Name");
        dt.Columns.Add("Age");
        dt.Columns.Add("Address");
        for (int cnt = 1; cnt <= 110; cnt++)
        {
            DataRow dataRow;
            dataRow = dt.NewRow();
            dataRow["SNo"] = cnt;
            dataRow["Name"] = "Name" + cnt.ToString();
            dataRow["Age"] = "Age" + cnt.ToString();
            dataRow["Address"] = "Address" + cnt.ToString();
            dt.Rows.Add(dataRow);
            dt.AcceptChanges();
        }
        DataTable dtFirst = null;
        DataTable dtSecond = null;
        if (dt.Rows.Count > 100)
        {
            DataView dvFirst = new DataView(dt, "SNo<=100", "SNo", DataViewRowState.CurrentRows);
            dtFirst = dvFirst.ToTable();//This is the first datatable
            DataView dvSecond = new DataView(dt, "SNo>100", "SNo", DataViewRowState.CurrentRows);
            dtSecond = dvSecond.ToTable();//This is second datatable
        }
        dt.Dispose();
 
Share this answer
 
This is the Answer:
Which is taken from thatraja answer.


For LinkButton Click
C#
protected void LinkReport_Click(object sender, EventArgs e)
    {
        DataTable dt2 = (DataTable)ViewState["dtab"];
        List<datatable> dt1 = CloneTable(dt2, 5);
        DataSet ds = new DataSet("dst");
        for (int i = 0; i < dt1.Count; i++)
        {
            ds.Tables.Add(dt1[i]);
        }
        string filePath = Server.MapPath("Reports/").ToString() + "master.xls";
        FileInfo file = new FileInfo(filePath);
        if (file.Exists)
        {
            file.Delete();
        }

        Export(ds, filePath);
    }
</datatable>


For Split Datatable

C#
private List<datatable> CloneTable(DataTable tableToClone, int countLimit)
    {
        List<datatable> tables = new List<datatable>();
        int count = 0;
        DataTable copyTable = null;
        foreach (DataRow dr in tableToClone.Rows)
        {
            if ((count++ % countLimit) == 0)
            {
                copyTable = new DataTable();
                copyTable = tableToClone.Clone();
                copyTable.TableName = "TableCount" + count;
                tables.Add(copyTable);
            }
            copyTable.ImportRow(dr);
        }
        return tables;
    }

</datatable></datatable></datatable>


Namespace is needed

C#
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;

For Export into Excel in Multiple Sheets

C#
public void Export(DataSet ds, string filePath)
   {
       string data = null;
       string columnName = null;
       int i = 0;
       int j = 0;
       Excel.Application xlApp;
       Excel.Workbook xlWorkBook;
       //Excel.Worksheet xlWorkSheet;
       Excel.Worksheet xlWorkSheet = null;
       object misValue = System.Reflection.Missing.Value;
       Excel.Range range;

       xlApp = new Excel.ApplicationClass();
       xlWorkBook = xlApp.Workbooks.Add(misValue);
       //xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


       for (int l = 0; l < ds.Tables.Count; l++)
       {
           xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(l + 1);
           xlWorkSheet.Cells[1, 1] = "Report";
           xlWorkSheet.get_Range("A1:D1", Type.Missing).Merge(Type.Missing);
           xlWorkSheet.get_Range("A1", "D1").Font.Bold = true;
           xlWorkSheet.Cells.Font.Name = "Courier New";

           if (l == 0)
           {
               xlWorkSheet.Name = "Sheet1";
           }
           else if (l == 1)
           {
               xlWorkSheet.Name = "Sheet2";
           }
           else if (l == 2)
           {
               xlWorkSheet.Name = "Sheet3";
           }
           else if (l == 3)
           {
               xlWorkSheet.Name = "Sheet4";
           }
           else if (l == 4)
           {
               xlWorkSheet.Name = "Sheet5";
           }

           for (i = 0; i <= ds.Tables[l].Rows.Count - 1; i++)
           {


               for (j = 0; j <= ds.Tables[l].Columns.Count - 1; j++)
               {
                   columnName = ds.Tables[l].Columns[j].ColumnName.ToString();
                   xlWorkSheet.Cells[3, j + 1] = columnName;
                   data = ds.Tables[l].Rows[i].ItemArray[j].ToString();
                   xlWorkSheet.Cells[i + 5, j + 1] = data;
               }
           }
       }


       //for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
       //{
       //    for (j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
       //    {
       //        data = ds.Tables[0].Rows[i].ItemArray[j].ToString();
       //        xlWorkSheet1.Cells[i + 1, j + 1] = data;
       //    }
       //}


       xlWorkBook.SaveAs(filePath, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
       xlWorkBook.Close(true, misValue, misValue);
       xlApp.Quit();

       // kill all excel processes
       Process[] pros = Process.GetProcesses();
       for (int p = 0; p < pros.Length; p++)
       {
           if (pros[p].ProcessName.ToLower().Contains("excel"))
           {
               pros[p].Kill();
               break;
           }
       }

       releaseObject(xlWorkSheet);
       releaseObject(xlWorkBook);
       releaseObject(xlApp);
   }
private void releaseObject(object obj)
   {
       try
       {
           System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
           obj = null;
       }
       catch (Exception ex)
       {
           obj = null;
       }
       finally
       {
           GC.Collect();
       }
   }
 
Share this answer
 
pl check the following article which uses SSIS and splits data of one million each and stores in separate excel:

http://consultingblogs.emc.com/jamiethomson/archive/2005/12/04/SSIS-Nugget_3A00_-Splitting-a-file-into-multiple-files.aspx[^]
 
Share this answer
 
Comments
Prince Antony G 4-Jan-12 5:16am    
thanks for ur reply..But i need to split dataset ...
Sorry to say...your answer is not helpful for me..
Hi,

1.Seperate Dataset with datatable as per your requirement and bind that data table with seperate dataset in your question manner!
 
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