NCF 如何導入Excel數據
簡介
學了上一節的WebApi之後，我們會發現一片新天地
本節跟大家聊一聊，如何把本地的Excel數據導入到NCF中
倉庫地址：//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("//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 }
有疑問隨時歡迎交流
