How To:: Create Excel from List of Objects

Aspose.Cells Product Family
1

I have a list of objects and trying to create Excel and download it.

The code is like this but getting error Crawler is Undefined.

public ExportSheetDetails createReport(string TableName, List<GEPSmartEventEntity> Data, int counter)

{

DataTable td = new DataTable();

ExportSheetDetails objSheetOne = new ExportSheetDetails();

if (Data.Count == 0)

{

td.Columns.Add(

"UserType");

td.Columns.Add(

"EventId");

td.Columns.Add(

"MessageBody");

td.Columns.Add(

"UserId");

td.Columns.Add(

"Platform");

td.Columns.Add(

"Browser");

td.Columns.Add(

"Cookies");

td.Columns.Add(

"IPAddress");

DataRow d = td.NewRow();

d[

"PartitionKey"] = "No Records For this Table";

td.Rows.Add(d);

}

else

{

td.Columns.Add(

"UserType");

td.Columns.Add(

"DateTime");

td.Columns.Add(

"EventId");

td.Columns.Add(

"UserId");

td.Columns.Add(

"Platform");

td.Columns.Add(

"Browser");

td.Columns.Add(

"Cookies");

td.Columns.Add(

"IPAddress");

foreach (var item in Data)

{

DataRow d = td.NewRow();

d[

"UserType"] = item.Crawler;

d[

"DateTime"] = string.Concat(item.Timestamp.ToShortDateString(), " ", item.Timestamp.ToLongTimeString());

d[

"EventId"] = item.EventId;

d[

"UserId"] = item.UserId;

d[

"Platform"] = item.Platform;

d[

"Browser"] = item.BrowserType;

d[

"Cookies"] = item.Cookies.ToString();

d[

"IPAddress"] = item.IPAddress;

td.Rows.Add(d);

}

}

//FileStream fileStream = null;

//ImpExManager objIP;

try

{

FieldManager manager = new FieldManager();

List<GEP.Cumulus.Impex.Entities.Field> fields = new List<GEP.Cumulus.Impex.Entities.Field>();

GEP.Cumulus.Impex.Entities.

Field UserType = manager.GetField<TextField>();

UserType.FieldName =

"UserType";

fields.Add(UserType);

GEP.Cumulus.Impex.Entities.

Field DateTime = manager.GetField<TextField>();

DateTime.FieldName =

"DateTime";

fields.Add(DateTime);

GEP.Cumulus.Impex.Entities.

Field EventId = manager.GetField<TextField>();

EventId.FieldName =

"EventId";

fields.Add(EventId);

GEP.Cumulus.Impex.Entities.

Field UserId = manager.GetField<TextField>();

UserId.FieldName =

"UserId";

fields.Add(UserId);

GEP.Cumulus.Impex.Entities.

Field Platform = manager.GetField<TextField>();

Platform.FieldName =

"Platform";

fields.Add(Platform);

GEP.Cumulus.Impex.Entities.

Field BrowserType = manager.GetField<TextField>();

BrowserType.FieldName =

"BrowserType";

fields.Add(BrowserType);

GEP.Cumulus.Impex.Entities.

Field Cookies = manager.GetField<TextField>();

Cookies.FieldName =

"Cookies";

fields.Add(Cookies);

GEP.Cumulus.Impex.Entities.

Field IPAddress = manager.GetField<TextField>();

IPAddress.FieldName =

"IPAddress";

fields.Add(IPAddress);

objSheetOne.DataTable = td;

objSheetOne.Field = fields.ToArray();

objSheetOne.FirstColumn = 0;

objSheetOne.FirstRow = 1;

objSheetOne.Format =

FileFormat.Excel;

objSheetOne.IsShowFieldName =

false;

objSheetOne.ProtectWorksheet =

false;

objSheetOne.WorksheetId = counter;

objSheetOne.WorksheetName = TableName;

}

catch (Exception)

{

throw;

}

finally

{

}

return objSheetOne;

}

//=====================================================

public void DownloadReportFile()

{

// FileManagerProxy fileManager = new FileManagerProxy(usercontext);

usercontext.ClientName = usercontext.CompanyName;

byte[] bytLicenceData = StrToByteArray(impexlicenceStrings); // fileManager.GetLicenceBytes();

//FileStream licenceStream = new FileStream("D:\\Aspose.Total.lic", FileMode.Open);

//licenceStream.Seek(0, SeekOrigin.Begin);

//byte[] bytLicenceData = new byte[licenceStream.Length];

//licenceStream.Close();

ImpExManager mgr = new ImpExManager(bytLicenceData);

byte[] fileData = null;

MemoryStream memoryStream = new MemoryStream();

mgr.WriteData(memoryStream, objExportSheetDetailsCollection);

memoryStream.Seek(0,

SeekOrigin.Begin);

fileData =

new byte[memoryStream.Length];

memoryStream.Read(fileData, 0, fileData.Length);

string fileName = "Reports.xlsx";

System.Web.

HttpContext.Current.Response.ContentType = "application/text-plain";

System.Web.

HttpContext.Current.Response.AddHeader("Content-disposition", "attachment; filename=" + fileName);

System.Web.

HttpContext.Current.Response.BinaryWrite(fileData);

System.Web.

HttpContext.Current.Response.End();

objExportSheetDetailsCollection =

null;

}

public void DownloadReportFile()

{

// FileManagerProxy fileManager = new FileManagerProxy(usercontext);

usercontext.ClientName = usercontext.CompanyName;

byte[] bytLicenceData = StrToByteArray(impexlicenceStrings); // fileManager.GetLicenceBytes();

//FileStream licenceStream = new FileStream("D:\\Aspose.Total.lic", FileMode.Open);

//licenceStream.Seek(0, SeekOrigin.Begin);

//byte[] bytLicenceData = new byte[licenceStream.Length];

//licenceStream.Close();

ImpExManager mgr = new ImpExManager(bytLicenceData);

byte[] fileData = null;

MemoryStream memoryStream = new MemoryStream();

mgr.WriteData(memoryStream, objExportSheetDetailsCollection);

memoryStream.Seek(0,

SeekOrigin.Begin);

fileData =

new byte[memoryStream.Length];

memoryStream.Read(fileData, 0, fileData.Length);

string fileName = "Reports.xlsx";

System.Web.

HttpContext.Current.Response.ContentType = "application/text-plain";

System.Web.

HttpContext.Current.Response.AddHeader("Content-disposition", "attachment; filename=" + fileName);

System.Web.

HttpContext.Current.Response.BinaryWrite(fileData);

System.Web.

HttpContext.Current.Response.End();

objExportSheetDetailsCollection =

null;

}

v
2

Hi there,

Thank you for contacting Aspose support.

I am not sure about your code as it does not seem to refer Aspose.Cells for .NET API anywhere. Could you please confirm if you are using Aspose.Cells API in your project, and what version of it?

In order to import data to worksheets while using Aspose.Cells for .NET API, please check the below linked detailed technical article for your kind reference.
http://www.aspose.com/docs/display/cellsnet/Importing+Data+to+Worksheets

3

Thank you for your prompt reply.

We are using ASPOSE.TOTAL.

I am not sure how I will get the version.

Sorry for the long listing of the code.

The scenario is:

I have List of our custom objects e.g. List

I will create excel workbook and download it to user machine from the MVC4 web page.

Only few properties of CustomObject is used now. If creating Excel for all properties is easier I can use it for all. If you have sample code snippet for two properties for this scenario will help a lot.

Thanks a lot.

4

Hi again,

Thank you for writing back.

You can get the version of Aspose.Cells for .NET API using the CellsHelper.GetVersion method. Alternatively, you may browse to the Aspose.Cells.dll file and check the properties for assembly. Under the Details tab, you will find the File/Product version in a format as X.X.X.X.

Regarding your original inquiry, you can use the Cells.ImportCustomObjects method to import the custom objects to the worksheet cells. Please check the below provided code snippet that uses the said method for demonstration. Please note, I have used the latest version of Aspose.Cells for .NET 7.7.2.2 for testing. In case you do not find the said method in your current version of the API then I would suggest you to download the latest build and give it a try on your end.

C#


public class Author
{
private string name;
private short age;
private string title;
private bool mvp;
private DateTime pubdate;

public Author(string name, short age, string title, bool mvp, DateTime pubdate)
{
this.name = name;
this.age = age;
this.title = title;
this.mvp = mvp;
this.pubdate = pubdate;
}

public string Name
{
get { return name; }
set { name = value; }
}

public short Age
{
get { return age; }
set { age = value; }
}

public string BookTitle
{
get { return title; }
set { title = value; }
}

public bool IsMVP
{
get { return mvp; }
set { mvp = value; }
}

public DateTime PublishedDate
{
get { return pubdate; }
set { pubdate = value; }
}
}


List AuthorList = new List();
AuthorList.Add(new Author(“Mahesh Chand”, 35, “A Prorammer’s Guide to ADO.NET”, true, new DateTime(2003,7,10)));
AuthorList.Add(new Author(“Neel Beniwal”, 18, “Graphics Development with C#”, false, new DateTime(2010, 2, 22)));
AuthorList.Add(new Author(“Praveen Kumar”, 28, “Mastering WCF”, true, new DateTime(2012, 01, 01)));
AuthorList.Add(new Author(“Mahesh Chand”, 35, “Graphics Programming with GDI+”, true, new DateTime(2008, 01, 20)));
AuthorList.Add(new Author(“Raj Kumar”, 30, “Building Creative Systems”, false, new DateTime(2011, 6, 3)));

var book = new Workbook();
var sheet = book.Worksheets[0];
sheet.Cells.ImportCustomObjects(AuthorList, 0, 0, new ImportTableOptions() { IsFieldNameShown = true });
book.Save(response, “output.xlsx”, ContentDisposition.Attachment, SaveFormat.Xlsx);

5

The Version number is ASPOSE.CELL 7.4.3.0v

Thanks for your help. It is much simpler solution. Then how this is different from Aspose.Cell Field building and then creating the worksheet (I was using it before).

6

Hi there,

Thank you for the confirmation on the provided solution.

Please note, Aspose.Cells APIs provide easy to use yet flexible routines while hiding the long and ugly details from the developers. You must agree that the provided solution is much simpler than you current approach therefore I would recommend you to use the Cells.ImportCustomObjects method for this particular requirement.