I am trying to dump two datatables into an Excel spreadsheet so they are side-by-side. However, when dumping the second datatable new rows are inserted causing the original datatable to be cut in half (overwitten with blank cells). How do I get two (or more) datatable side-by-side?
If anyone could offer any assistance on this it would be much appreciated.
Cheers.
Hi,
Thanks for considering Aspose.
May the following code fulfil your need. It which works fine with the dummy data and the two tables are placed side by side. Attached is the output file:
DataSet ds = new DataSet();
System.Data.DataTable dt = new DataTable("Table1");
dt.Columns.Add("CustID", typeof(int));
dt.Columns.Add("CustName", typeof(string));
dt.Columns.Add("Description", typeof(string));
for (int i = 1; i<=20;i++)
{
System.Data.DataRow dr = dt.NewRow();
dr["CustID"]=i;
dr["CustName"]="Name " + i;
dr["Description"]= "Description " + i;
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
dt = new DataTable("Table2");
dt.Columns.Add("Field1", typeof(string));
dt.Columns.Add("Field2", typeof(string));
dt.Columns.Add("Field3", typeof(string));
dt.Columns.Add("Field4", typeof(string));
for (int i = 1; i<=40;i++)
{
System.Data.DataRow dr = dt.NewRow();
dr["Field1"]="Field1 " + i.ToString();
dr["Field2"]= "Field2 " + i.ToString();
dr["Field3"]= "Field3 " + i.ToString();
dr["Field4"]="Field4 " + i.ToString();
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Cells.ImportDataTable(ds.Tables[0], true, 0, 0,false);
sheet.Cells.ImportDataTable(ds.Tables[1], true, 0, 6,false);
workbook.Save("d:\\test\\sidebysidetables.xls");
System.Data.DataTable dt = new DataTable("Table1");
dt.Columns.Add("CustID", typeof(int));
dt.Columns.Add("CustName", typeof(string));
dt.Columns.Add("Description", typeof(string));
for (int i = 1; i<=20;i++)
{
System.Data.DataRow dr = dt.NewRow();
dr["CustID"]=i;
dr["CustName"]="Name " + i;
dr["Description"]= "Description " + i;
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
dt = new DataTable("Table2");
dt.Columns.Add("Field1", typeof(string));
dt.Columns.Add("Field2", typeof(string));
dt.Columns.Add("Field3", typeof(string));
dt.Columns.Add("Field4", typeof(string));
for (int i = 1; i<=40;i++)
{
System.Data.DataRow dr = dt.NewRow();
dr["Field1"]="Field1 " + i.ToString();
dr["Field2"]= "Field2 " + i.ToString();
dr["Field3"]= "Field3 " + i.ToString();
dr["Field4"]="Field4 " + i.ToString();
dt.Rows.Add(dr);
}
ds.Tables.Add(dt);
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
sheet.Cells.ImportDataTable(ds.Tables[0], true, 0, 0,false);
sheet.Cells.ImportDataTable(ds.Tables[1], true, 0, 6,false);
workbook.Save("d:\\test\\sidebysidetables.xls");
Thank you.
Hi Amjad,
I tried running the code above and I got a ArgumentOutOfRangeExceptionexception after the second importDataTable fumction with ": Index was out of range. Must be non-negative and less than the size of the collection".
I'm using aspose.cells.dll version 4.4.1.3.
Do you know what the problem could be?
Thanks,
Ofir
Hi,
I am not sure if the older version has some problem with ImportDataTable() method. Any how I utilized the attached version (4.4.3.21), run my code and it works fine without any problem.
Kindly try it.
Thank you.