解决MySql布尔型新旧版本兼容问题,采用枚举来表示布尔型的数据表。由正向工程赋值
大石头 authored at 2018-05-15 21:21:05
5.44 KiB
X
# ExcelReader ʹÓÃÊÖ²á ±¾Îĵµ»ùÓÚÔ´Âë `NewLife.Core/IO/ExcelReader.cs`£¬ÓÃÓÚ˵Ã÷ `ExcelReader`£¨ÇáÁ¿¼¶ Excel xlsx ¶ÁÈ¡Æ÷£©µÄ¶¨Î»¡¢Ö§³Ö·¶Î§¡¢Êý¾Ý¶ÁÈ¡·½Ê½¡¢ÀàÐÍת»»¹æÔòÓëʹÓÃ×¢ÒâÊÂÏî¡£ > ¹Ø¼ü´Ê£ºxlsx¡¢ZipArchive¡¢sharedStrings¡¢styles¡¢sheetData¡¢ÁÐË÷Òý AA/AB¡¢È±Ê§Áв¹Æë¡¢ÊýÖµ¸ñʽ¡£ --- ## 1. ¸ÅÊö `ExcelReader` ÊÇÒ»¸ö½öÓÃÓÚ¡°µ¼ÈëÊý¾Ý¡±µÄÇáÁ¿¼¶ xlsx ¶ÁÈ¡Æ÷¡£ - ½öÖ§³Ö `xlsx`£¨OpenXML£©£¬±¾ÖÊÊÇ zip ѹËõ°ü£» - ²»ÒÀÀµµÚÈý·½ Office/Interop ×é¼þ£» - µ±Ç°ÊµÏÖÖ»×ö×îС»¯½âÎö£º - ¹²Ïí×Ö·û´®£¨`xl/sharedStrings.xml`£© - Ñùʽ£¨`xl/styles.xml`£¬Êý×Ö¸ñʽ£© - ¹¤×÷±íÊý¾Ý£¨`xl/worksheets/sheet*.xml` ÖÐµÄ `sheetData`£© ÊÊÓó¡¾°£º - ·þÎñÆ÷/×ÀÃæ¶ËÅúÁ¿µ¼Èë Excel£» - Ö»ÐèÒª°Ñ¹¤×÷±í°´ÐжÁÈ¡³É¶ÔÏóÊý×飻 - ²»¹Ø×¢¹«Ê½¼ÆËã¡¢ºÏ²¢µ¥Ôª¸ñ¡¢Í¼±í¡¢Åú×¢µÈ¸´ÔÓÌØÐÔ¡£ --- ## 2. ¹¹ÔìÓë×ÊÔ´¹ÜÀí ### 2.1 `ExcelReader(String fileName)` - ÒÔ¹²Ïí·½Ê½´ò¿ªÎļþ£º`FileShare.ReadWrite`£¬±ÜÃâÎļþ±»ÆäËü½ø³ÌÕ¼ÓÃʱ±¨´í£» - Óà `ZipArchive` ¶ÁÈ¡ zip ÄÚÈÝ£» - ¹¹Ô캯Êý»áÁ¢¼´µ÷Óà `Parse()` ½âÎö±ØÒªµÄË÷Òý¡£ ### 2.2 `ExcelReader(Stream stream, Encoding encoding)` - ´«Èë xlsx Êý¾ÝÁ÷£¨µ÷Ó÷½¸ºÔðÁ÷ÉúÃüÖÜÆÚ£¬Ðè±£³Ö¿É¶Á£©£» - `encoding` ÓÃÓÚ zip ÌõÄ¿Ãû³Æ/×¢Ê͵ȱàÂ루һ°ãΪ UTF-8£©¡£ ### 2.3 Dispose `ExcelReader` ¼Ì³Ð `DisposeBase`£º - `Dispose(Boolean)` »áÇåÀí `_entries` ²¢ÊÍ·Å `_zip`£» - Õâ»áͬʱÊÍ·ÅÆäµ×²ã `FileStream`£¨ÈôÓɹ¹Ô캯Êý´´½¨£©¡£ ½¨Ò飺 - ʼÖÕʹÓà `using var reader = new ExcelReader(...)`¡£ --- ## 3. »ù±¾ÊôÐÔ ### 3.1 `FileName` - ÀàÐÍ£º`String?` - ´ÓÎļþ¹¹Ô캯ÊýÖ±½Ó¸³Öµ£» - ´ÓÁ÷¹¹Ô캯ÊýÖУ¬µ± `stream is FileStream` ʱȡ `fs.Name`¡£ ### 3.2 `Sheets` - ÀàÐÍ£º`ICollection<String>?` - ÓïÒ壺¿ÉÓù¤×÷±íÃû³Æ¼¯ºÏ£¨¼üÀ´×Ô `_entries.Keys`£©¡£ ˵Ã÷£º - `Parse()` »á°Ñ¹¤×÷±íÃû³ÆÓ³Éäµ½¶ÔÓ¦ `ZipArchiveEntry`¡£ --- ## 4. ¶ÁÈ¡Êý¾Ý ### 4.1 `IEnumerable<Object?[]> ReadRows(String? sheet = null)` °´Ðзµ»ØÊý¾Ý£¨µÚÒ»ÐÐͨ³£ÊDZíÍ·£©£º - `sheet=null` ʱĬÈÏÈ¡ `Sheets.FirstOrDefault()`£» - ÕÒ²»µ½¹¤×÷±í»áÅ× `ArgumentOutOfRangeException`£» - ¶ÁÈ¡Á÷³Ì£º 1. ´ò¿ªÄ¿±ê sheet ÌõÄ¿Á÷£» 2. `XDocument.Load` ¶ÁÈ¡ XML£» 3. ÔÚ¸ù½ÚµãÏÂÕÒ `sheetData`£» 4. ±éÀúÿ¸ö `<row>`£¬¶ÔÏÂÃæ `<c>` µ¥Ôª¸ñ½øÐнâÎö¡£ ·µ»ØÖµ£º - ÿһÐÐÊÇÒ»¸ö `Object?[]`£» - Öµ¿ÉÄܱ»×ª»»Îª£º`DateTime` / `TimeSpan` / `Int32` / `Int64` / `Decimal` / `Double` / `Boolean` / `String`£» - ÎÞÖµ»òȱʧÁÐÒÔ `null` ±íʾ¡£ ### 4.2 ¹Ø¼üÐÐΪ£ºÁÐË÷ÒýÓëȱʧÁв¹Æë Excel µ¥Ôª¸ñÒýÓÃÈç `A1`¡¢`AB23`£»ÊµÏֻ᣺ - ½âÎöÁÐ×ÖĸΪ 0 »ùË÷Òý£¨`A=0`£¬`B=1`£¬`AA=26`£©£» - Èô±¾ÐгöÏÖÌøÁУ¨ÀýÈçÖ»ÓÐ A¡¢C£©£¬»á×Ô¶¯°Ñ B ²¹Îª `null`£» - »á¼Ç¼Ê×ÐÐÁÐÊý `headerColumnCount`£¬ºóÐøÐÐÈôβ²¿ÁÐȱʧҲ»á²¹Æëµ½ÓëÊ×ÐÐÒ»Ö¡£ ÕâʹµÃ£º - ¶ÁÈ¡½á¹û¸ü½Ó½ü¡°¶þά±í¸ñ¡±µÄÖ±¹Û½á¹¹£» - ±ãÓÚÖ±½Ó°´ÁÐË÷Òý·ÃÎÊ¡£ --- ## 5. µ¥Ôª¸ñÀàÐͽâÎöÓëת»»¹æÔò ### 5.1 ¹²Ïí×Ö·û´®£¨`t="s"`£© µ±µ¥Ôª¸ñÊôÐÔ `t="s"`£º - `<v>` ´æ´¢µÄÊǹ²Ïí×Ö·û´®Ë÷Òý£» - »áµ½ `_sharedStrings[sharedIndex]` È¡ÕæÊµÎı¾¡£ ¹²Ïí×Ö·û´®À´×Ô `xl/sharedStrings.xml`£¬¸ÃÌõÄ¿¿ÉÄÜȱʧ£¨ÔÊÐí£©¡£ ### 5.2 ²¼¶û£¨`t="b"`£© - `0/1` »ò `true/false`£» - תΪ `Boolean`¡£ ### 5.3 ¹«Ê½½á¹ûÎı¾£¨`t="str"`£© - ²»×öÌØÊâ´¦Àí£¬Ö±½ÓÈ¡Îı¾Öµ¡£ ### 5.4 Êý×Ö/ÈÕÆÚ/ʱ¼ä£ºÑùʽÇý¶¯×ª»» µ±µ¥Ôª¸ñֵΪ×Ö·û´®ÇÒ´æÔÚÑùʽ `_styles` ʱ£º - ¶ÁÈ¡µ¥Ôª¸ñÊôÐÔ `s`£¨StyleIndex£©£» - ¸ù¾Ý `styles[si]` µÄ `NumFmtId/Format` ¾ö¶¨×ª»»²ßÂÔ¡£ ת»»Âß¼­Î»ÓÚ `ChangeType(Object? val, ExcelNumberFormat st)`£º - **ÈÕÆÚ/ʱ¼ä**£º - Ìõ¼þ£º¸ñʽ°üº¬ `yy`/`mmm` »ò `NumFmtId` ÔÚ 14~17 »òΪ 22£» - Excel ÐòÁÐÖµÒÔ 1900-01-01 Ϊ»ù×¼£¬ÀúÊ·¼æÈÝʵÏÖ»á×ö `d-2` µ÷Õû£» - ʹÓà `AddSeconds(Math.Round((d - 2) * 24 * 3600))`£¬¾¡Á¿¹æ±Ü¸¡µãÎó²î¡£ - **ʱ¼ä¼ä¸ô**£¨TimeSpan£©£º - Ìõ¼þ£º`NumFmtId` ÔÚ 18~21 »ò 45~47£» - תΪ `TimeSpan.FromSeconds(Math.Round(d2 * 24 * 3600))`¡£ - **General / 0**£º - Ìõ¼þ£º`NumFmtId == 0`£» - ÒÀ´Î³¢ÊÔ `Int32`¡¢`Int64`¡¢`Decimal(InvariantCulture)`¡¢`Double`¡£ - **ÕûÊý¸ñʽ**£º - Ìõ¼þ£º`NumFmtId` Ϊ 1/3/37/38£» - ³¢ÊÔ `Int32/Int64`¡£ - **СÊý¸ñʽ**£º - Ìõ¼þ£º`NumFmtId` Ϊ 2/4/11/39/40£» - ³¢ÊÔ `Decimal(InvariantCulture)` »ò `Double`¡£ - **°Ù·Ö±È**£º - Ìõ¼þ£º`NumFmtId` Ϊ 9/10£» - ³¢ÊÔ `Double`£¨×¢Ò⣺µÃµ½µÄÊÇ 0.x£¬Èç 12% => 0.12£©¡£ - **Îı¾¸ñʽ**£º - Ìõ¼þ£º`NumFmtId == 49`£» - Èô¿É½âÎöΪÊýÖµÔòÔÙת»Ø×Ö·û´®£¨±ÜÃâµ¼Èëʱ½øÈëÊýÖµÀàÐÍ£©¡£ --- ## 6. ×îСʾÀý ### 6.1 ¶ÁÈ¡µÚÒ»¸ö¹¤×÷±í ```csharp using NewLife.IO; using var reader = new ExcelReader("./data.xlsx"); foreach (var row in reader.ReadRows()) { // µÚÒ»ÐÐͨ³£ÊDZíÍ· // row[i] ¿ÉÄÜÊÇ String/Int32/DateTime/Boolean/TimeSpan/null } ``` ### 6.2 Ö¸¶¨¹¤×÷±íÃû³Æ ```csharp using var reader = new ExcelReader("./data.xlsx"); var sheet = reader.Sheets?.FirstOrDefault(); if (!sheet.IsNullOrEmpty()) { foreach (var row in reader.ReadRows(sheet)) { } } ``` ### 6.3 Óë `CsvFile` ×éºÏ£ºExcel ת CSV ```csharp using NewLife.IO; using var reader = new ExcelReader("./data.xlsx"); using var csv = new CsvFile("./out.csv", write: true); foreach (var row in reader.ReadRows()) { csv.WriteLine(row); } ``` --- ## 7. ×¢ÒâÊÂÏîÓë³£¼ûÎÊÌâ ### 7.1 Ö»¶ÁÈ¡ `sheetData` ±¾ÊµÏÖÖ»¶ÁÈ¡ `sheetData`£¬²»»á½âÎö£º - ºÏ²¢µ¥Ôª¸ñ£¨mergedCells£© - ¹«Ê½¼ÆË㣨ֻ¶Á½á¹û£© - ͼƬ/ͼ±í/Åú×¢ ÈôÐèÒªÀ©Õ¹£¬¿É¸ù¾Ý OpenXML ½á¹¹»ùÓÚ `ZipArchive` ÌõÄ¿¼ÌÐø½âÎö¡£ ### 7.2 ÄÚ´æÕ¼Óà µ±Ç°ÊµÏÖ¶Ôÿ´Î `ReadRows()`£º - »á `XDocument.Load` °ÑÕû¸ö sheet XML ÔØÈëÄÚ´æ¡£ ¶Ô³¬´ó¹¤×÷±í¿ÉÄÜÕ¼Óý϶àÄڴ棻ÈôÒªÖ§³Ö¸ü´óÎļþ£¬ÐèÒª¸ÄΪ `XmlReader` Á÷ʽ½âÎö£¨ÊôÓÚ¹¦ÄÜÀ©Õ¹£¬²»ÔÚ±¾Îĵµ·¶Î§£©¡£ ### 7.3 ÈÕÆÚÆ«ÒÆ£¨¼õ 2£© Ô´ÂëÖÐ¶Ô Excel ÈÕÆÚÐòÁÐֵʹÓà `d - 2` µÄÀúÊ·¼æÈÝÐÐΪ£¬ÓÃÓÚÆ¥ÅäÏÖÓÐÓû§ÆÚÍû¡£ÈôÄã¶ÔÈÕÆÚ¾«¶ÈÓÐÑϸñÒªÇó£¬ÐèÒª½áºÏ¾ßÌåÑùÀýÑéÖ¤¡£ --- ## 8. Ïà¹ØÁ´½Ó - ÔÚÏßÎĵµ£º`https://newlifex.com/core/excel_reader` - Ô´Â룺`NewLife.Core/IO/ExcelReader.cs`