How to set date format for entire sheet using XlsIO?

1 min read

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.

           
            IWorkbook workbook = application.Workbooks.Open(@"../../Data/syncfusion.xlsx",   ExcelOpenType.Automatic);
 
            IWorkbook workbook1 = application.Workbooks.Open(@"../../Data/sample.xlsx", ExcelOpenType.Automatic);
            IWorksheet sheet = workbook.Worksheets[0];
            IWorksheet sheet1 = workbook1.Worksheets[0];
 
            DataTable dataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
 
            sheet1.ImportDataTable(dataTable, false, 1, 1);
            sheet.UsedRange.NumberFormat = "mm/dd/yyyy";

 
            Dim workbook As IWorkbook = application.Workbooks.Open("../../Data/syncfusion.xlsx", ExcelOpenType.Automatic)
            Dim workbook1 As IWorkbook = application.Workbooks.Open("../../Data/sample.xlsx", ExcelOpenType.Automatic)
            'IWorkbook workbook = application.Workbooks.Create(1);
            Dim sheet As IWorksheet = workbook.Worksheets(0)
            Dim sheet1 As IWorksheet = workbook1.Worksheets(0)
 
            Dim dataTable As DataTable = sheet.ExportDataTable(sheet.UsedRange, ExcelExportDataTableOptions.ColumnNames)
 
            sheet1.ImportDataTable(dataTable, False, 1, 1)
            sheet.UsedRange.NumberFormat = "mm/dd/yyyy"

The sample illustrating this behavior is available here.

Did you find this information helpful?

Yes

Comments

How to set date format for entire sheet using XlsIO?

Access denied