XlsIO Best Path to import data to Excel

[Sunil]

Hi,

I have been trying to import data into Excel by defining HTML templates, but I am running into issues over and over. Is there a way I can define some formatting and bring those along with data to excel? For example, I would like to define certain columns in currency format, some in Date format etc. Is that a possibility without defining HTML templates?

Thanks!

Sunil

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Sunil, 

Greetings from Syncfusion. 

Please find the response for your queries below. 

Query	Response
Is there a way I can define some formatting and bring those along with data to excel?	It is not possible to set the number format while importing the Data to Excel. But you can set the format in Excel file, after importing.   Please go through the below link to know about applying number formats.   https://help.syncfusion.com/file-formats/xlsio/working-with-cell-or-range-formatting#apply-number-formats
Is that a possibility without defining HTML templates?	You can directly import data from Data table to Excel instead of importing into HTML.  Please look into following link to Import data to Excel.  https://help.syncfusion.com/file-formats/xlsio/working-with-data#import-data-from-datatable

Please find the code snippet describing both above concepts. 

Code Snippet: 

 

using (ExcelEngine excelEngine = new ExcelEngine()) 

{ 

        IApplication application = excelEngine.Excel; 

        application.DefaultVersion = ExcelVersion.Excel2013; 

        IWorkbook workbook = application.Workbooks.Create(1); 

        IWorksheet worksheet = workbook.Worksheets[0]; 

        //Initialize the DataTable 

        DataTable table = SampleDataTable(); 

        //Import DataTable to the worksheet. 

        worksheet.ImportDataTable(table, true, 1, 1); 

         //Applying different number formats 

         worksheet.Range["B2:B4"].NumberFormat = "$#,##0.00"; 

         worksheet.Range["C2:C4"].NumberFormat = "yyyy/mm/dd"; 

         workbook.SaveAs("ImportFromDataTable.xlsx"); 

} 

Kindly try and let us know if this helps. 

Regards, 

Ramya. 

[Sunil]

Thanks Ramya! Yes, I looked at the ImportFromDataTable before, my question was very specific to brining in data along with formatting. I think i will mostly rely on the formatting after importing the data. Thank you for your help. Please let me know what you find about that special char causing failure in parsing the HTML file. It might be an encoding issue, but it might be an overkill to encoding the entire document.

Thanks,

Sunil

[Sunil]

Hi,

I am using asp.net project and initiating the license in Global.aspx.vb file, as provided your guideline. The Excel file generated, still has a sheet indicating it was created using trial version (I have a community edition). PLease advise how to fix this.

Thanks!

Sunil

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Sunil, 

 

Please find the response for your queries below. 

Query	Response
My question was very specific to brining in data along with formatting.	As mentioned earlier, we don’t have support for importing data along with the formatting.
Please let me know what you find about that special char causing failure in parsing the HTML file.	We will share the validation details for this query in the forum https://www.syncfusion.com/forums/170734/xlsio-error on January 11th, 2021, as promised.
I am using asp.net project and initiating the license in Global.aspx.vb file, as provided your guideline. The Excel file generated, still has a sheet indicating it was created using trial version (I have a community edition). PLease advise how to fix this.	Our license keys are version and platform specific. So, please generate and register the corresponding platform license key for the required version in your application by referring the below KB article.    Generate License Key: https://www.syncfusion.com/kb/8976/how-to-generate-license-key-for-licensed-products   Registering License Key: https://help.syncfusion.com/common/essential-studio/licensing/registering-license-keys

Regards, 

Ramya. 

[Sunil]

Thank you Ramya! I was using above instructions to generate/apply license, not sure why it did not work earlier. I did regenerate and also updated my local Synfusion library version to match the license generated version and it looks good now. Appreciate the quick response!

THanks!

Sunil

[Konduru Keerthi Konduru Ravichandra Raju]

Hi Sunil, 

Thanks for the update. 

We are glad that the provided information helped you. Kindly let us know if you need any further assistance. 

Regards, 

Keerthi.