Date format issue set format

Aspose.Cells Product Family
1

Hi,
I have one excel i want to set one date format everywhere / or for one sheet in excel where ever date is stored with datatype date

2

@Amrinder_Singh
If all data in your workbook is numeric value and you need to format them as datetime, you may set the number format as datetime for workbook’s DefaultStyle and then fill data into the workbook:

Workbook wb = new Workbook();
Style s = wb.DefaultStyle;
s.Number = 14;
//Or
//s.Custom = "yyyy-mm-dd hh:mm"; //or any pattern you need
wb.DefaultStyle = s;
Cells cells =...;
cells[0, 0].PutValue(54321);
...

If the data has been there in the workbook and you need to change the number format for all cells, you may ApplyStyle to all columns for every worksheet:

Workbook wb = new Workbook(templateFile);
Style s = wb.CreateStyle();
s.Number = 14;
//Or
s.Custom = "yyyy-mm-dd hh:mm"; //or any pattern you need

Worksheet sheet = ...;
int max = sheet.Cells.MaxDataColumn;
StyleFlag sf = new StyleFlag();
sf.NumberFormat = true;
for (int i = 0; i <= max; i++)
{
    sheet.Cells.Columns[i].ApplyStyle(s, sf);
}

And here are some other documents about formatting for your reference:
Format Ranges|Documentation (aspose.com)
Format cells|Documentation (aspose.com)

3

@Amrinder_Singh,

Moreover, to set Date format everywhere in the worksheet of your existing Excel file, you may also refer to the following sample code:
e.g.,
Sample code:

Workbook wb = new Workbook("sampleFile.xlsx");
Worksheet ws = wb.Worksheets[0];

Aspose.Cells.Range range = ws.Cells.MaxDisplayRange;
Style style = wb.CreateStyle();
//set your desired DateTime numbers format
style.Custom = "m/d/yy";
StyleFlag flag = new StyleFlag();
flag.NumberFormat = true;
range.ApplyStyle(style, flag);

wb.Save("out1.xlsx");

Hope, this helps a bit.

4

My query is where ever i have cell type as date that cell’s format if i can define that date should be in this format because i will have column and cells of different different types.
Like if i can give default date format for the dates in workbook.

5

@Amrinder_Singh,

You have to browse cells in the worksheet and specify DateTime format to date type cells for your requirements. See the sample code for your reference.
e.g
Sample code:

// Opening an existing workbook
Workbook workbook = new Workbook("Book1.xlsx");
            
// Accessing first worksheet
Worksheet worksheet = workbook.Worksheets[0];

foreach (Aspose.Cells.Cell cell in worksheet.Cells)
{
    // Passing the type of the data contained in the cell for evaluation
    switch (cell.Type)
    {
        // Evaluating the data type of the cell data for string value
        case CellValueType.IsString:
            //your code goes here.
            break;

        // Evaluating the data type of the cell data for double value
        case CellValueType.IsNumeric:
            //your code goes here.
            break;

        // Evaluating the data type of the cell data for boolean value
        case CellValueType.IsBool:
            //your code goes here
            break;

        // Evaluating the data type of the cell data for date/time value
        case CellValueType.IsDateTime:
            //Get the style of the cell
            Style style = cell.GetStyle();
            //Set custom format to yyyy-mm-dd
            style.Custom = "yyyy-mm-dd";
            //Set style to the the cell
            cell.SetStyle(style);
            break;

        // Evaluating the unknown data type of the cell data
        case CellValueType.IsUnknown:
            //your code goes here.
            break;

        // Terminating the type checking of type of the cell data is null
        case CellValueType.IsNull:
            break;
    }
}

There is no option available, not even in MS Excel, that could provide the desired custom date formatting for all the DateTime cells in the workbook. If you know such an option, let us know with details and sample file, we can check it further.

6

But i have number of sheets and so many column
That will not be best approach for me to go by each cell and check the DataType and assign

Is there any way i can set default setting at workbook level

7

@Amrinder_Singh,

You can set default style at workbook level but it will apply one style to all the cells in the workbook. This will work only if all data in your workbook is numeric value and you need to format them as datetime, you may set the number format as datetime for workbook’s DefaultStyle and then fill data into the workbook:

Workbook wb = new Workbook();
Style s = wb.DefaultStyle;
s.Number = 14;
//Or
//s.Custom = "yyyy-mm-dd hh:mm"; //or any pattern you need
wb.DefaultStyle = s;
Cells cells =...;
cells[0, 0].PutValue(54321);
...
8

@Amrinder_Singh
In addition, you can use iterators to provide traversal performance. Regarding the use of iterators, please refer to the following document.