NCF 如何匯入Excel資料

MartyZane發表於2021-06-14
Excel

簡介

學了上一節的WebApi之後，我們會發現一片新天地

本節跟大家聊一聊，如何把本地的Excel資料匯入到NCF中

倉庫地址：https://github.com/NeuCharFramework/NCF

歡迎收藏，歡迎Star哦

步驟

1.首先在頁面上需要增加可以操作匯入的按鈕

2.在頁面上增加點選匯入按鈕後需要彈出的視窗

3.在視窗中增加下載匯入資料的模板

4.在視窗中增加點選上傳的資料的檔案進行上傳

5.在js中增加對應的視窗中需要處理的方法

6.引入NPOI

7.建立ExcelHelper類用來讀取Excel檔案內容

8.在api中增加上傳資料檔案的接收方法

9.在Service中處理上傳的檔案的資料

 

實施

1.首先在頁面上需要增加可以操作匯入的按鈕

1 <el-button class="filter-item" size="mini" type="primary" @@click="handleEdit('','','import')">匯入</el-button>

2.在頁面上增加點選匯入按鈕後需要彈出的視窗

 1 @*匯入*@
 2 <el-dialog :title="dialogImport.title"
 3             :visible.sync="dialogImport.visible"
 4             :close-on-click-modal="false"
 5             width="700px">
 6     
 7     <el-form ref="dataFormImport"
 8                 :rules="dialogImport.rules"
 9                 :model="dialogImport.data"
10                 :disabled="dialogImport.disabled"
11                 label-position="left"
12                 label-width="100px"
13                 style="max-width: 200px; margin-left:50px;">
14         <el-button @@click="downloadTemplate">下載匯入模板</el-button>
15         <el-form-item label="匯入的檔案">
16             <el-upload class="upload-demo"
17                         action="@Model.UpFileUrl"
18                         accept=".xls,.xlsx"
19                         :on-success="uploadSuccess"
20                         :on-preview="handlePreview"
21                         :on-remove="handleRemove"
22                         :before-remove="beforeRemove"
23                         multiple
24                         :limit="3"
25                         :on-exceed="handleExceed"
26                         :file-list="fileList"
27                         ref="my-upload">
28                 <el-button size="small" type="primary">點選上傳</el-button>
29                 <div slot="tip" class="el-upload__tip">只能上傳excel檔案</div>
30             </el-upload>
31             <el-input class="hidden" v-model="dialogImport.data.file" clearable placeholder="檔案" />
32         </el-form-item>
33     </el-form>
34     <div slot="footer" class="dialog-footer">
35         <el-button @@click="dialogImport.visible=false">取消</el-button>
36         <el-button :loading="dialogImport.updateLoading" :disabled="dialogImport.disabled" type="primary" @@click="dialogImport.visible=false">確認</el-button>
37     </div>
38 </el-dialog>

js中的程式碼

 1 dialogImport:
 2 {
 3     title: '匯入資料',
 4     visible: false,
 5     data:
 6     {
 7         file: ''
 8     },
 9     updateLoading: false,
10     disabled: false,
11     checkStrictly: true // 是否嚴格的遵守父子節點不互相關聯
12 }

3.在視窗中增加下載匯入資料的模板

這裡可以本地編輯一個Excel的檔案，欄位可以根據自己實際的隨便寫

4.在視窗中增加點選上傳的資料的檔案進行上傳

el-upload控制元件則是用來上傳檔案的

5.在js中增加對應的視窗中需要處理的方法

1 downloadTemplate() {
2     window.open("https://aaa.oss-cn-beijing.aliyuncs.com/ImportData.xls", '_blank')
3 }

6.引入NPOI

1 <PackageReference Include="NPOI" Version="2.5.3" />

7.建立ExcelHelper類用來讀取Excel檔案內容

  1 using NPOI.SS.UserModel;
  2 using System;
  3 using System.Collections.Generic;
  4 using System.Data;
  5 using System.IO;
  6 using System.Text;
  7 
  8 namespace Senparc.Xncf.Admin.Utils
  9 {
 10     public class ExcelHeler
 11     {
 12         /// <summary>
 13         /// 讀取Excel多Sheet資料
 14         /// </summary>
 15         /// <param name="filePath">檔案路徑</param>
 16         /// <param name="sheetName">Sheet名</param>
 17         /// <returns></returns>
 18         public static DataSet ReadExcelToDataSet(string filePath, string sheetName = null)
 19         {
 20             if (!File.Exists(filePath))
 21             {
 22                 //logger.LogError($"未找到檔案{filePath}");
 23                 return null;
 24             }
 25             //獲取檔案資訊
 26             FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
 27             IWorkbook workbook = WorkbookFactory.Create(fs);
 28             //獲取sheet資訊
 29             ISheet sheet = null;
 30             DataSet ds = new DataSet();
 31             if (!string.IsNullOrEmpty(sheetName))
 32             {
 33                 sheet = workbook.GetSheet(sheetName);
 34                 if (sheet == null)
 35                 {
 36                     //logger.LogError($"{filePath}未找到sheet:{sheetName}");
 37                     return null;
 38                 }
 39                 DataTable dt = ReadExcelFunc(workbook, sheet);
 40                 ds.Tables.Add(dt);
 41             }
 42             else
 43             {
 44                 //遍歷獲取所有資料
 45                 int sheetCount = workbook.NumberOfSheets;
 46                 for (int i = 0; i < sheetCount; i++)
 47                 {
 48                     sheet = workbook.GetSheetAt(i);
 49                     if (sheet != null)
 50                     {
 51                         DataTable dt = ReadExcelFunc(workbook, sheet);
 52                         if (dt != null) ds.Tables.Add(dt);
 53                     }
 54                 }
 55             }
 56             return ds;
 57         }
 58 
 59         /// <summary>
 60         /// 讀取Excel多Sheet資料
 61         /// </summary>
 62         /// <param name="filePath">檔案路徑</param>
 63         /// <param name="sheetName">Sheet名</param>
 64         /// <returns></returns>
 65         public static DataSet ReadExcelToDataSet(FileStream fs, string sheetName = null)
 66         {
 67             IWorkbook workbook = WorkbookFactory.Create(fs);
 68             //獲取sheet資訊
 69             ISheet sheet = null;
 70             DataSet ds = new DataSet();
 71             if (!string.IsNullOrEmpty(sheetName))
 72             {
 73                 sheet = workbook.GetSheet(sheetName);
 74                 if (sheet == null)
 75                 {
 76                     //logger.LogError($"{filePath}未找到sheet:{sheetName}");
 77                     return null;
 78                 }
 79                 DataTable dt = ReadExcelFunc(workbook, sheet);
 80                 ds.Tables.Add(dt);
 81             }
 82             else
 83             {
 84                 //遍歷獲取所有資料
 85                 int sheetCount = workbook.NumberOfSheets;
 86                 for (int i = 0; i < sheetCount; i++)
 87                 {
 88                     sheet = workbook.GetSheetAt(i);
 89                     if (sheet != null)
 90                     {
 91                         DataTable dt = ReadExcelFunc(workbook, sheet);
 92                         if (dt != null)
 93                             ds.Tables.Add(dt);
 94                     }
 95                 }
 96             }
 97             return ds;
 98         }
 99 
100         /// <summary>
101         /// 讀取Excel資訊
102         /// </summary>
103         /// <param name="workbook">工作區</param>
104         /// <param name="sheet">sheet</param>
105         /// <returns></returns>
106         private static DataTable ReadExcelFunc(IWorkbook workbook, ISheet sheet)
107         {
108             DataTable dt = new DataTable();
109             //獲取列資訊
110             IRow cells = sheet.GetRow(sheet.FirstRowNum);
111             //空資料化返回
112             if (cells == null) return null;
113             int cellsCount = cells.PhysicalNumberOfCells;
114             //空列返回
115             if (cellsCount == 0) return null;
116             int emptyCount = 0;
117             int cellIndex = sheet.FirstRowNum;
118             List<string> listColumns = new List<string>();
119             bool isFindColumn = false;
120             while (!isFindColumn)
121             {
122                 emptyCount = 0;
123                 listColumns.Clear();
124                 for (int i = 0; i < cellsCount; i++)
125                 {
126                     if (string.IsNullOrEmpty(cells.GetCell(i).StringCellValue))
127                     {
128                         emptyCount++;
129                     }
130                     listColumns.Add(cells.GetCell(i).StringCellValue);
131                 }
132                 //這裡根據邏輯需要，空列超過多少判斷
133                 if (emptyCount == 0)
134                 {
135                     isFindColumn = true;
136                 }
137                 cellIndex++;
138                 cells = sheet.GetRow(cellIndex);
139             }
140 
141             foreach (string columnName in listColumns)
142             {
143                 if (dt.Columns.Contains(columnName))
144                 {
145                     //如果允許有重複列名，自己做處理
146                     continue;
147                 }
148                 dt.Columns.Add(columnName, typeof(string));
149             }
150             //開始獲取資料
151             int rowsCount = sheet.PhysicalNumberOfRows;
152             var rowIndex = 1;
153             DataRow dr = null;
154             //空資料化返回
155             if (rowsCount <= 1) { return null; }
156             for (int i = rowIndex; i < rowsCount; i++)
157             {
158                 cells = sheet.GetRow(i);
159                 dr = dt.NewRow();
160                 for (int j = 0; j < dt.Columns.Count; j++)
161                 {
162                     //這裡可以判斷資料型別
163                     switch (cells.GetCell(j).CellType)
164                     {
165                         case CellType.String:
166                             dr[j] = cells.GetCell(j).StringCellValue;
167                             break;
168                         case CellType.Numeric:
169                             dr[j] = cells.GetCell(j).NumericCellValue.ToString();
170                             break;
171                         case CellType.Unknown:
172                             dr[j] = cells.GetCell(j).StringCellValue;
173                             break;
174                     }
175                 }
176                 dt.Rows.Add(dr);
177             }
178             return dt;
179         }
180     }
181 }

8.在api中增加上傳資料檔案的接收方法

 1 /// <summary>
 2 /// 上傳資料
 3 /// </summary>
 4 /// <param name="file">檔案資訊</param>
 5 /// <returns></returns>
 6 [HttpPost]
 7 public IActionResult ImportExcelData([FromForm] IFormFile file)
 8 {
 9     string prefixPath = string.Empty;
10     try
11     {
12         var file_data = this.Request.Form.Files[0];
13         if (file_data == null)
14         {
15             return Fail("檔案引數無效，請提供name值為file_data的檔案");
16         }
17         //驗證副檔名
18         var extension = Path.GetExtension(file_data.FileName);
19         if (!AllowFileExtension.FileExtension.Contains(extension))
20         {
21             return Fail("不支援此副檔名檔案的上傳！");
22         }
23         //基礎儲存路徑
24         var basePath = "default"; // sysKeyModel.Name;
25         //驗證檔案字首路徑有效性
26         if (!string.IsNullOrWhiteSpace(prefixPath))
27         {
28             if (prefixPath.IndexOfAny(Path.GetInvalidPathChars()) > -1)//驗證路徑有效性
29             {
30                 return Fail("無效路徑！");
31             }
32             //進一步規範路徑
33             var invalidPattern = new Regex(@"[\\\/\:\*\?\042\<\>\|]");
34             prefixPath = invalidPattern.Replace(prefixPath, "");
35 
36             prefixPath = prefixPath.Replace("_", "\\");//使用下劃線“_”代替斜槓“\”
37             basePath = Path.Combine(basePath, prefixPath);
38         }
39         //物理檔案路徑
40         var pathMp = Path.Combine(_webHostEnvironment.ContentRootPath, staticResourceSetting.CurrentValue.RootDir, basePath);
41         if (!Directory.Exists(pathMp)) Directory.CreateDirectory(pathMp);
42 
43         string strFileName = file_data.FileName.Split('\\').LastOrDefault();
44 
45         var filename = $"{DateTime.Now:yyyyMMddHHmmss}-{UniqueHelper.LongId()}{extension}";
46         string strFileHash = string.Empty;
47         string strFilePath = string.Empty;
48         using (var fs = new FileStream(Path.Combine(pathMp, filename), FileMode.CreateNew))
49         {
50             file_data.CopyTo(fs);
51             strFileHash = HashHelper.SHA1File(fs);//賦值檔案Hash值
52             fs.Flush();
53         }
54         //開始解析Excel
55         //呼叫ExcelHelper方法
56         DataSet ds = ExcelHeler.ReadExcelToDataSet(Path.Combine(pathMp, filename));
57         SenparcTrace.Log($"{pathMp}----{filename}");
58         dataService.ImportDataAsync(ds);
59         return Success("匯入成功");
60     }
61     catch (Exception ex)
62     {
63         return Fail(ex.Message);
64     }
65 }

9.在Service中處理上傳的檔案的資料

 1 public void ImportDataAsync(DataSet ds)
 2 {
 3     if (ds != null)
 4     {
 5         DataTable dt = ds.Tables[0];
 6         if (dt.Rows.Count > 0)
 7         {
 8             List<DataEntity> lstData = new List<DataEntity>();
 9             for (int i = 0; i < dt.Rows.Count; i++)
10             {
11                 int iRows = GetCount(_ => _.Name.Equals(dt.Rows[i][0].ToString()));
12                 if (iRows > 0)
13                 {
14                     continue;
15                 }
16                 DataEntity dataItem = new DataEntity()
17                 {
18                     Name = dt.Rows[i][0].ToString()
19                 };
20                 lstData.Add(dataItem);
21             }
22             SaveObjectListAsync(lstData);
23         }
24     }
25 }

 

有疑問隨時歡迎交流

相關文章