15,917,971 members
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
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
http://forums.asp.net/t/352061.aspx/1
Prince Antony G 4-Jan-12 6:33am
Prince Antony G 6-Jan-12 4:50am
Thanks for all you gave idea and solution to me...

## Solution 4

Prince Antony G 4-Jan-12 6:33am
Prince Antony G 6-Jan-12 4:31am
its working fine...Thanks..

## Solution 3

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 )
{
}
}
LIst <dataset> lsi = new list<dataset>();

for(int i = 0 ; i<listrow.count;i++)
{
if(i%600000 == 0)
{
}
}

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

## Solution 5

Try logic below,

```DataTable dt = new DataTable();
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();
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();```

## Solution 6

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++)
{
}
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;
}
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();
//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();
}
}
```

## Solution 1

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

Prince Antony G 4-Jan-12 5:16am
thanks for ur reply..But i need to split dataset ...

## Solution 2

Hi,

1.Seperate Dataset with datatable as per your requirement and bind that data table with seperate dataset in your question manner!