Format of the npoi read Cell

Recently, a project needs to import some data. Some of the imported data uses the Excel percentage and some data uses the string format, (data comes from statistics by different people.) The format is slightly messy. When importing data to the system, convert all the percentage data into percent data for storage.

Because the component npoi used in the previous project to read excel, I directly used npoi to read it. The npoi I used is of Version 2.0, which is not too new.

During regular read operations, icell is read in the following way:

IWorkbook workbook = null;var workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fileStream);ISheet sheet = workbook .GetSheetAt(0);IRow firstRow = sheet.GetRow(1);ICell cell = firstRow.GetCell(0);

After obtaining the icell object, you can use icell. celltype determines the Data Type of cells, from icell. cellstyle. dataformat can go to the cell format, but the problem is: cellstyle. dataformat can only obtain the number encoding in one format (for example, 186), but cannot obtain the specific format (for example, 0.00% ).

So there is a need to complete the conversion from the format and number encoding to the specific format. I have been searching for it online for a long time and did not find a specific method. Later I thought that excel is in XML format, or start Excel first.

OK. First Change the excelsuffix to .rar, decompress it, and find \ XL \ styles. XML. This is the Excel Style File. Open it and check that the data format is at the beginning, it is a value of <numfmt numfmtid = "178" formatcode = "0.000%"/>. It is quite clear, but you cannot decode the style by yourself. This is too earthy, let's start with npoi.

Afterwards, I consulted my colleagues and my colleagues did not do this kind of thing. However, I gave a clue that he used a method to check whether the cell is in the time format. In the usermodel namespace of npoi, however, the method he used is used to check the built-in format of Excel, excluding the custom format. I think maybe the code for getting the style is in another namespace. Okay, let's start to flip the DLL (it's hard to find the API and use vs to check which classes are in the DLL namespace)

Fortunately, npoi is not very big, and it turns to the second DLL to find a clue, npoi. ooxml. DLL has a class npoi. xssf. usermodel. xssfdataformat. This class has a method npoi. xssf. usermodel. xssfdataformat. getformat (short) Looks like the get format, but the constructor of the xssfdataformat class is like this: xssfdataformat (npoi. xssf. model. stylestable). It is required to input a stylestable object and find the class definition of this stylestable. It is found that the constructor of this class has no parameters or the create method. It seems wrong.

At this time, my colleague came up with an idea: This npoi is open-source. Let's take down the source code and see if there are any stylestable instances. It's a good idea to think about it. several minutes later, the Code went down. The entire project searched for stylestable and immediately found a line of code.

Stylestable ST = (xssfworkbook) workbook). getstylessource ();

Haha, it turns out to be an implementation class in iwookbook. It's no wonder that the class cannot be found on the interface.

The above code is given as follows:

IWorkbook workbook = null;StylesTable st = ((XSSFWorkbook)workbook ).GetStylesSource();XSSFDataFormat df = new XSSFDataFormat(st);var workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fileStream);ISheet sheet = workbook .GetSheetAt(0);IRow firstRow = sheet.GetRow(1);ICell cell = firstRow.GetCell(0);

string formatCode = df.GetFormat(cell.CellStyle.DataFormat);if (formatCode.EndsWith("%")){}

　　

In this way, the obtained formatcode is in the "0.000%" format, and it's a breeze.

 

Finally, we found that the best way to open-source things is to find what you need in the source code, coupled with Google's clues.

 

 

Format of the npoi read Cell