Category / Section
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.
C#
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";
VB
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.