解决MySql布尔型新旧版本兼容问题,采用枚举来表示布尔型的数据表。由正向工程赋值
|
# 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`
|