Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Wed Mar 11, 2020 3:13 pm

Is it possible to change the attribute CellRange.FormatType to be public? I know there is the NumberFormat attribute, but this doesn't provide the information in the right way.

Many thanks in advance,

Darren.

wraydc
 
Posts: 130
Joined: Wed Apr 11, 2018 5:14 am

Thu Mar 12, 2020 5:46 am

Hello,

Thanks for your inquiry.
Do you want to get the format type of the cell data? If so, our Spire.XLS provides the GetCellType method which is public and could return the cell format type. Please refer to this tutorial: Get cell type in Excel.
If this does not meet your needs well, to help us better look into your issue, please provide more information for our reference, such as your input file and your desired result.

Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Fri Mar 13, 2020 7:44 am

Thanks for your quick reply.

Unfortunately, the GetCellType method doesn't work in my use case. I need to know when a cell is formatted as a DateTime, GetCellType always classifies these cells as numeric, which is of course true. I can do a number of further checks HasDateTime for example, but it would be nice to be a far nicer solution to be able to switch on the result of FormatType.

I think there may be other similar cases that I haven't encountered yet, such as numerics formatted as currency.

I hope that helps,

Darren

wraydc
 
Posts: 130
Joined: Wed Apr 11, 2018 5:14 am

Fri Mar 13, 2020 9:56 am

Hello,

Thanks for your feedback.
Yes, the method "GetCellType" can't further recognize the DateTime type. For your case, you can refer to the following code to further identify the DateTime and Currency types. If there is still any question, please provide more information for our reference. So that we could provide you with a corresponding solution.
Code: Select all
    Workbook wb = new Workbook();
    wb.LoadFromFile("test.xlsx");
    Worksheet sheet = wb.Worksheets[0];
    CellRange cell = sheet.Range["A1"];
    string formatType = sheet.GetCellType(cell.Row, cell.Column, false).ToString();
    string numberFormat = cell.NumberFormat;
    if ("Number".Equals(formatType))
    {
        if (cell.HasDateTime)
        {
            formatType = "DateTime";
        }
        if (!string.IsNullOrEmpty(numberFormat) && (numberFormat.Contains("$") || numberFormat.Contains("¥")))
        {
            formatType = "Currency";
        }
    }


Sincerely,
Rachel
E-iceblue support team
User avatar

rachel.lei
 
Posts: 1571
Joined: Tue Jul 09, 2019 2:22 am

Return to Spire.XLS