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. (Last updated on: November 16, 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

How to set date format for entire sheet using XlsIO?

Platform: WinForms |
Control: XlsIO |
Published Date: March 3, 2015 |
Last Revised Date: March 3, 2015

Setting a number format for an entire sheet at once is not possible with XlsIO. However, this approach also affects the performance and will impact the memory usage.

 

To apply a number format to all the cells, we recommend to utilize UsedRange property of IRange. This will apply a same format to all the used range cells in a worksheet. The following code snippets illustrates the same for your reference.

C#

VB


The sample illustrating this behavior is available here.

 

2X faster development

The ultimate WinForms UI toolkit to boost your development speed.
ADD COMMENT
You must log in to leave a comment
Comments
Urja Patel
Feb 26, 2019

By setting Number format for the entire sheet, it also changes formats of those cells which are just numeric numbers into specified date format.
How do I change date format of only to those cells who has DateTime value assigned.

For Example, I have Exported Datatable to excel, which has DateTime Column as well as Column consisting numbers of integer type. By setting number format, it converted that numeric column in datetime format.

How to change just the DateTime Column date format?

Reply
Mohan Chandran [Syncfusion]
Mar 04, 2019

Hi Urja,

 

Thank you for contacting Syncfusion support.

 

When you apply date format for entire worksheet, the numeric type cells also will change into date format. This is a Microsoft Excel behaviour and XlsIO behaves similarly. To avoid this, we suggest you to check the cell has date time type before changing the number format of the cell in the worksheet. Please refer the code snippet given below.

 

using (ExcelEngine excelEngine = new ExcelEngine())

{

    IApplication application = excelEngine.Excel;

    application.DefaultVersion = ExcelVersion.Excel2013;

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

    IWorksheet sheet = workbook.Worksheets[0];

 

    DataTable reports = new DataTable();

    reports.Columns.Add("ID");

    reports.Columns.Add("SalesPerson");

    reports.Columns.Add("FromDate", typeof(DateTime));

    reports.Columns.Add("ToDate", typeof(DateTime));

    reports.Rows.Add(1021,"Andy Bernard", new DateTime(2014, 09, 08), new DateTime(2014, 09, 11));

    reports.Rows.Add(1022,"Jim Halpert", new DateTime(2014, 09, 11), new DateTime(2014, 09, 15));

    reports.Rows.Add(1023,"Karen Fillippelli", new DateTime(2014, 09, 15), new DateTime(2014, 09, 20));

    reports.Rows.Add(1024,"Phyllis Lapin", new DateTime(2014, 09, 21), new DateTime(2014, 09, 25));

    reports.Rows.Add(1025,"Stanley Hudson", new DateTime(2014, 09, 26), new DateTime(2014, 09, 30));

 

    sheet.ImportDataTable(reports, true, 1, 1);

 

    IMigrantRange range = sheet.MigrantRange;

    for (int row = sheet.UsedRange.Row; row <= sheet.UsedRange.LastRow; row++)

    {

        for (int col = sheet.UsedRange.Column; col <= sheet.UsedRange.LastColumn; col++)

        {

            range.ResetRowColumn(row, col);

            if(range.HasDateTime)

                range.NumberFormat = "dd-mm-yyyy";

        }

    }

 

    sheet.UsedRange.AutofitColumns();

    workbook.SaveAs("NumberFormat.xlsx");             

}

 

 

 

 

 

 

                                             

Please let us know if you have any queries.

 

Regards,

Mohan.

Please sign in to access our KB

This page will automatically be redirected to the sign-in page in 10 seconds.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon