We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

Export array property entity

Hello Syncfusion Team,
I want to export a list of entities to excel with their array.

This is my code

            List<MyUser> Users = new List<MyUser>();

            MyUser User1 = new MyUser()
            {
                Id = 1,
                Name = "Mario Rossi",
                Age = 30,
                Items = new List<KeyValuePair<string, string>>() {
                    new KeyValuePair<string, string>("key1", "val1"),
                    new KeyValuePair<string, string>("key2", "val2"),
                    new KeyValuePair<string, string>("key3", "val3")
                }
            };

            MyUser User2 = new MyUser()
            {
                Id = 2,
                Name = "Mario Rossi",
                Age = 35,
                Items = new List<KeyValuePair<string, string>>() {
                    new KeyValuePair<string, string>("key1", "val1"),
                    new KeyValuePair<string, string>("key2", "val2"),
                    new KeyValuePair<string, string>("key3", "val3")
                }
            };

            Users.Add(User1);
            Users.Add(User2);
            //=================================================

            ExcelEngine excelEngine = new ExcelEngine();
            IApplication application = excelEngine.Excel;

            IWorkbook workbook = application.Workbooks.Create(new string[] { "Foglio1" });
            IWorksheet sheet = workbook.Worksheets[0];

            sheet.ImportData(Users, 2, 1, false);

            sheet.UsedRange.AutofitRows();
            sheet.UsedRange.AutofitColumns();

            workbook.Version = ExcelVersion.Excel2007;
            return excelEngine.SaveAsActionResult(workbook, "export.xlsx", HttpContext.ApplicationInstance.Response, ExcelDownloadType.PromptDialog, ExcelHttpContentType.Excel2007);
}


The result is attached, export1.jpg.

The desidered result is attached also, export2.jpg (or export2-alternative.jpg).

How can achive this without have to write every cell "manually"?

Attachment: export_f43eab55.zip

5 Replies

DB Dilli Babu Nandha Gopal Syncfusion Team March 17, 2016 11:03 AM UTC

Hi Omar,

Thank you for contacting Syncfusion support.

Yes, your requirement can be achieved through a template marker in XlsIO. The input Excel file must contain marker with respective to their properties in MyUser class. Please find the screen shot of the input Excel file with markers.



And the generated Excel file looks like



We have shared the sample for your reference which can be downloaded from following link.

Sample link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/ListTemplateMarker-559716128

To know more about Template marker, kindly go through the following UG documentation link.

UG documentation link: https://help.syncfusion.com/file-formats/xlsio/working-with-template-markers

Please let us know if you have any concerns on this.

Regards,
Dilli babu.


OM Omar Muscatello replied to Dilli Babu Nandha Gopal March 18, 2016 11:04 AM UTC

Hi Omar,

Thank you for contacting Syncfusion support.

Yes, your requirement can be achieved through a template marker in XlsIO. The input Excel file must contain marker with respective to their properties in MyUser class. Please find the screen shot of the input Excel file with markers.



And the generated Excel file looks like



We have shared the sample for your reference which can be downloaded from following link.

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/ListTemplateMarker-559716128

To know more about Template marker, kindly go through the following UG documentation link.

UG documentation link: http://help.syncfusion.com/file-formats/xlsio/working-with-template-markers

Please let us know if you have any concerns on this.

Regards,
Dilli babu.

Tahnk you Dilli Babu Nandha Gopal, it is exactly what I was looking for.

However, if there is only one element in the list it returns this error message:

Can't insert row

Riga 61:             // Process the markers and detect the number format along with the data type in the template.
Riga 62: 
Riga 63:             marker.ApplyMarkers();
Riga 64:             InsertBlankRow(sheet.UsedRange.Columns[0]);
Riga 65:             workbook.Version = ExcelVersion.Excel2010;

[ArgumentException: Can't insert row]
   Syncfusion.XlsIO.Implementation.WorksheetImpl.InsertRow(Int32 iRowIndex, Int32 iRowCount, ExcelInsertOptions insertOptions) +1323
   Syncfusion.XlsIO.Implementation.TemplateMarkers.NewSpaceArgument.ApplyArgument(IWorksheet sheet, Point pOldPosition, Int32& iRow, Int32& iColumn, IList`1 arrMarkerCells, MarkerOptionsImpl options, Int32 count) +111
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.ApplyArguments(IWorksheet sheet, Int32& iRow, Int32& iColumn, IList`1 arrMarkerCells, IList lstArguments, MarkerOptionsImpl options) +199
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.PrepareNextCell(IWorksheet sheet, IMigrantRange migrantRange, IList`1 arrMarkerCells, IList lstArguments, MarkerOptionsImpl options) +72
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.SetArrayValue(IList value, String strText, IWorksheet sheet, IMigrantRange migrantRange, IList`1 arrMarkerCells, IList lstArguments, MarkerOptionsImpl options, RangeBuilder builder, UnknownVariableAction action, VariableTypeAction variableTypeAction) +444
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.SetUnknownVariable(Object value, String strText, IWorksheet sheet, IMigrantRange migrantRange, IList`1 arrMarkerCells, IList lstArguments, MarkerOptionsImpl options, RangeBuilder builder, UnknownVariableAction action, VariableTypeAction variableTypeAction) +236
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.SetVariable(String strVariable, String strText, IWorksheet sheet, IMigrantRange migrantRange, IList`1 arrMarkerCells, IList lstArguments, MarkerOptionsImpl options, RangeBuilder builder, UnknownVariableAction action) +171
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.ApplyMarker(IWorksheet sheet, IList`1 arrCells, Int32 i, IMigrantRange migrantRange, UnknownVariableAction action) +384
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.ApplyMarkers(IWorksheet sheet, List`1 arrLabels, UnknownVariableAction action) +678
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.ApplyMarkers(IWorkbook book, UnknownVariableAction action) +111
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.ApplyMarkers(UnknownVariableAction action) +121
   Syncfusion.XlsIO.Implementation.TemplateMarkers.TemplateMarkersImpl.ApplyMarkers() +7
   CreateSpreadsheet._Default.btnCreateExcel_Click(Object sender, EventArgs e) in Default.aspx.cs:63
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +9659822
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +108
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +12
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +15
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +35
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1639


I edited the example that you attached so you can reproduce the issue.

Thank you!

Attachment: ListTemplateMarker559716128edited_75674a37.zip


DB Dilli Babu Nandha Gopal Syncfusion Team March 21, 2016 08:59 AM UTC

Hi Omar,

Thank you for updating us.

Yes, we are able to reproduce the reported issue from our side. We have modified the given sample so that it works fine for the Items with single values. We have also increased the count of Users collection for testing purpose, which is working fine. Please find the below screen-shot of generated Excel file.



 
We have shared the sample for your reference which can be downloaded from following link.

Sample link: https://www.syncfusion.com/downloads/support/directtrac/general/ze/ListTemplateMarkerXlsIOEdited-666008130

Please let us know if you have any concerns.

Regards,
Dilli babu.


OM Omar Muscatello replied to Dilli Babu Nandha Gopal March 23, 2016 08:06 AM UTC

Hi Omar,

Thank you for updating us.

Yes, we are able to reproduce the reported issue from our side. We have modified the given sample so that it works fine for the Items with single values. We have also increased the count of Users collection for testing purpose, which is working fine. Please find the below screen-shot of generated Excel file.



 
We have shared the sample for your reference which can be downloaded from following link.

Sample link: http://www.syncfusion.com/downloads/support/directtrac/general/ze/ListTemplateMarkerXlsIOEdited-666008130

Please let us know if you have any concerns.

Regards,
Dilli babu.

Thank you Dilli Babu Nandha Gopal.

However, marker template is very slow with a lot of records, so I switched to API.

Thank you again.


DB Dilli Babu Nandha Gopal Syncfusion Team March 24, 2016 06:12 AM UTC

Hi Omar,

Thank you for updating us and we are glad to know that you have found an alternative solution for this.

Please let us know if you need any further assistance on this.

Regards,
Dilli babu.

Loader.
Live Chat Icon For mobile
Up arrow icon