Formatting the Cells in the Table

Aspose.Cells Product Family
1

In Excel I have created the Table which contains three columns and six rows. for each column I have formated the cells into different types like Date(14-Mar-2022), Time(12:00 AM), Fraction(As halves 1/2) respectively.
In C# code(Aspose.Cells.Tables), first I am retrieving the table details and adding value to the each cells in the column with below values
Date - 2024-09-18
Time - 03:00:00 pm
Fraction - 6/3
but values are not getting added in the expected format after I save the Excel file through code.
below are the excepted values and value which I get
Date - 9/18/2024 12:00:00 AM (excepted 18-Sep-2024)
Fraction - 6/3 (excepted 2)

2

@usha22

To format the cells in your Excel table correctly using Aspose.Cells in C#, you need to ensure that you are applying the appropriate styles to each cell after setting their values. Here’s how you can achieve the expected formats for Date, Time, and Fraction:

  1. Date Formatting: To display the date in the format “18-Sep-2024”, you can set the cell’s style to a custom date format.
  2. Time Formatting: For the time to appear as “12:00 AM”, you should set the cell’s style to a time format.
  3. Fraction Formatting: To display the fraction as “2” instead of “6/3”, you can set the cell’s style to a fraction format.

Here’s a sample code snippet demonstrating how to format the cells accordingly:

using Aspose.Cells;

class Program
{
    static void Main()
    {
        // Create a new workbook
        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.Worksheets[0];

        // Adding values to the cells
        worksheet.Cells["A1"].PutValue("2024-09-18"); // Date
        worksheet.Cells["B1"].PutValue("03:00:00 PM"); // Time
        worksheet.Cells["C1"].PutValue("6/3"); // Fraction

        // Formatting the Date cell
        Style dateStyle = worksheet.Cells["A1"].GetStyle();
        dateStyle.Number = 14; // Custom format for "dd-MMM-yyyy"
        worksheet.Cells["A1"].SetStyle(dateStyle);

        // Formatting the Time cell
        Style timeStyle = worksheet.Cells["B1"].GetStyle();
        timeStyle.Number = 1; // Custom format for "hh:mm AM/PM"
        worksheet.Cells["B1"].SetStyle(timeStyle);

        // Formatting the Fraction cell
        Style fractionStyle = worksheet.Cells["C1"].GetStyle();
        fractionStyle.Number = 6; // Custom format for fractions
        worksheet.Cells["C1"].SetStyle(fractionStyle);

        // Save the workbook
        workbook.Save("FormattedTable.xlsx");
    }
}

In this code:

  • The PutValue method is used to set the initial values in the cells.
  • The GetStyle method retrieves the current style of the cell, which you can modify.
  • The SetStyle method applies the modified style back to the cell.

Make sure to adjust the number format codes according to your specific requirements. The codes used in the example correspond to common formats for dates, times, and fractions in Excel.

By applying these styles, you should see the values displayed in the expected formats when you open the saved Excel file.

3

This code is not working as Expected

4

@usha22,

Please note that when you specify default numbers formatting (via Number property) to the cells and save the file to disk, the formatting displayed in the output Excel file when opened manually in MS Excel will be based on your locale/regional settings of the system. This may result in different outputs for the cells in different regions or locales. To ensure consistent (same) formatting, you should apply custom numbers formatting to the cells instead. The following updated sample code has been tested and ensures the data in the output Excel file is fine-tuned, correct and will not change based on the underlying locale/regional settings.
e.g.,
Sample code:

        // Create a new workbook
        Workbook workbook = new Workbook();
        Worksheet worksheet = workbook.Worksheets[0];

        // Adding values to the cells
        worksheet.Cells["A1"].PutValue("2024-09-18", true); // Date
        worksheet.Cells["B1"].PutValue("03:00:00 PM", true); // Time
        worksheet.Cells["C1"].PutValue(6.0 / 3.0);// Fraction

        // Formatting the Date cell
        Style dateStyle = worksheet.Cells["A1"].GetStyle();
        dateStyle.Custom = "dd-MMM-yyyy";
        worksheet.Cells["A1"].SetStyle(dateStyle);

        // Formatting the Time cell
        Style timeStyle = worksheet.Cells["B1"].GetStyle();
        timeStyle.Custom = "hh:mm AM/PM"; 
        worksheet.Cells["B1"].SetStyle(timeStyle);

        // Formatting the Fraction cell
        Style fractionStyle = worksheet.Cells["C1"].GetStyle();
        fractionStyle.Custom = "# ?/?";
        worksheet.Cells["C1"].SetStyle(fractionStyle);

        worksheet.AutoFitColumns(0,1);

        // Save the workbook
        workbook.Save("e:\\test2\\FormattedTable1.xlsx");

Please find attached the output Excel file for your reference.
FormattedTable1.zip (6.1 KB)

Let us know if you still find any issue or confusion.

5

image.png (46.7 KB)

like Above Image, I used existing template by formatted F column with Time(h:mm AM/PM). If I used your code that formatted cell style is Changing to Custom.

6

@usha22
Thank you for your feedback. This is the expected result. If you want to directly set the built-in number formats, please refer to the following document.

7

@usha22,

If you use the Custom property to set the number format, any previous format set using the Number property will is overridden.