Free Support Forum - aspose.com

Import DataTable into 1 cell?

Aspose.Cells Product Family
#1

I am trying to get the data from several rows of a data table into a single cell. How can I do that? The table below in my code contains a single column and multiple records. I would like to get all the records into one cell and have the cell size vertically to accomodate the records. When I try the code below the data starts in the specified cell but there is a record in each cell below it. For example, if there are 3 records in the table, they populate cells E5 thru G5. I want all 3 records to end up in Cell E5 stacked vertically. How can that be done?

MyAdapter.Fill(SelTable)

worksheet.Cells.ImportDataTable(SelTable, True, "E5")

worksheet.AutoFitColumns()

Thanks...

#2
Hi,

Well, Cells.ImportDataTable will extract data into tabular format and not in a single cell. If you want to do it, you have to manually extract the data and puts it into a single cell.

May the following example help you for your need, kindly consult it:

Workbook excel = new Workbook();
Cells cells = excel.Worksheets[0].Cells;
DataTable dt = new DataTable("Products");
dt.Columns.Add("Product_ID",typeof(Int32));
dt.Columns.Add("Product_Name",typeof(string));
dt.Columns.Add("Units_In_Stock",typeof(Int32));
DataRow dr = dt.NewRow();
dr[0] = 1;
dr[1] = "Aniseed Syrup";
dr[2] = 15;
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 2;
dr[1] = "Boston Crab Meat";
dr[2] = 123;
dt.Rows.Add(dr);

dr = dt.NewRow();
dr[0] = 3;
dr[1] = "Carbon Anode";
dr[2] = 567;
dt.Rows.Add(dr);

string data = null;
foreach (DataRow r in dt.Rows)
{
for(int i = 0;i<dt.Columns.Count -1;i++)
{
data +=r[i].ToString() + " ";

}
data += "\n";
}
cells.SetColumnWidth(4,7);
cells.SetRowHeight(4,40);
cells["E5"].PutValue(data);
cells["E5"].Style.IsTextWrapped = true;
cells["E5"].Style.Rotation = 90;
excel.Worksheets[0].AutoFitRow(4);

excel.Save("d:\\test\\recordsincell.xls");

Thank you.

#3

Thank you Amjad,

With your help I may finally be able to output the Excel file I am trying to create in ASP.NET with VB.