Implementation process of an Excel export class (4): formatting and Some pitfall completed figures and code zip

This is the fourth and last part of this article. It is suitable for beginners to learn generics and reflection. It provides limited business scenario analysis and demonstration of several NPOI interfaces. The links to the first three parts are as follows:

• Implementation of an Excel export class (I): generics and reflection
• Implementation of an Excel export class (2): Display Customization
• Implementation of an Excel export class (III): NPOI component

In the third part, because the cell setting statement only contains one simple sentence row. CreateCell (I). SetCellValue (value. ToString (), the generated Excel file is still crude.

Excel export usually encounters several problems:

• Integer to string problem: the integer value, such as the ID column, has a green triangle symbol in the upper left corner of the cell, and the cell has changed to the string type, which is not friendly enough;
• Date display problem: Sometimes the date needs to be displayed, for example, "" instead of "16:50:13". Sometimes, you only want to display "" instead of telling the user to set the format of the generated workbook;
• Scientific Notation: Mobile Phone Number 13912345678 is just a short-term array that looks like a scientific notation, but an integer of more than 15 digits, 1234567890123456, is actually 1.23457E + 15 and the last 6 is changed to zero.

The ICell implementer HSSFCell has a large number of attributes and methods to help us complete cell format settings. Considering the Application Scenario, creating a specified type of cells and formatting options can meet the requirements, the following describes the functions of force transformation and control statements.

First, expand the Header class and add a Format parameter. When the user passes in the null Format parameter, we infer the cell type and value. When the user passes in the non-null Format parameter, we call the ToString (String format) method for formatting.

  String Name { ;  String PrintName { ;  String Format { ; (name, printName, ===

When we hand over the power to the caller, we think that the passing of his parameters is judged and justified. For example, the display of a pure numeric barcode may need to call Decimal. toString ("F0") method, but the user cannot call ToString ("P2") to display the percentage number. The other part is the problem at the beginning of the design, such as Double precision, later.

Because ICell. the cell type created by CreateCell (int column, CellType type) is ultimately created by ICell. the parameter types (Boolean, DateTime, Double, String, and IRichTextString) in the SetCellValue () overload version are determined. Therefore, only ICell is used. createCell (int column), it must be noted that only CellType is used to store the cell type enumeration values. NUMERIC, which will be discussed later. The new Export change point is described in the previous section. For the old Person class and Export overload, see the previous article. The implementation is as follows:

 IWorkbook Export<T>(IList<T> records, IList<Header> (records ==   ArgumentNullException( (headers ==   ArgumentNullException(); =  (Int32 i = ; i < headers.Count; i++= (T).GetProperty(headers[i].Name); = = =  (Int32 r = ; r < records.Count; r++ ((r % RowPerSheet) == = (Int32)((Double)r / RowPerSheet) + = workbook.CreateSheet( + sheetIndex);             row = sheet.CreateRow();              (Int32 i = ; i < headers.Count; i++= sheet.CreateRow((r % RowPerSheet) + );          (Int32 i = ; i < props.Length; i++ (props[i] != ) = props[i].GetValue(records[r],  (value != ) = (props[i].PropertyType ==   (props[i].PropertyType ==   (props[i].PropertyType ==   (props[i].PropertyType ==      (Int32 i = ; i < workbook.NumberOfSheets; i++= (Int32 h = ; h < headers.Count; h++

The birthday data is displayed on the year, month, and day. We use "yyyy-MM-dd" as the formatting parameter. The client call code is as follows:

  Main(= <Person> persons =  List<Person> (Int32 i = ; i < records; i++= = i + =  + (i + =  Math.Abs(Guid.NewGuid().GetHashCode() % ) + =<ExcelHelper.Header> headers =  List<ExcelHelper.Header> ExcelHelper.Header( ExcelHelper.Header(,  ExcelHelper.Header(, ,  ExcelHelper.Header(, = = = = excelHelper.Export<Person> (FileStream stream =

I have mentioned the problem of dual precision before. If I set the salary of an employee to 1234567890123456 and output the Excel file, I will first perform a test:

Person person = new Person();person.Salary = 1234567890123456;Console.WriteLine(person.Salary);

The output result is 1.23456789012316e + 15. That is to say, Excel not only displays scientific notation, but also the numerical value has become 1234567890123460 of the tragedy. Roughly speaking, this is because of Double-precision Double-type valid digits. It is not because of the ToString method or Excel writing.

To solve similar problems, only the Person. it is not enough to declare Salary as the Decimal type, because only CellType is used to store numbers in cell type CellType enumeration. NUMERIC, we can't use ToString ("F0") (or F2, depending on the specific requirements) to write the value as a string to the cell. The code of the modified part is as follows:

 Decimal Salary { ; = ExcelHelper.Header(, , 

As a result, we can see the 1234567890123456 shown in Excel. Although there is a small green triangle, it is not a scientific counting method or rounding. For more complete requirements, see NPOI APIs. The code file is provided.