C#对模板Excel 表格的处理

He少年

于 2024-09-06 19:08:37 发布

阅读量330

点赞数 1

文章标签： c# excel .netcore

本文链接：https://blog.csdn.net/vsmybits/article/details/141967120

版权

说明：本教程主要说明C#对模板Excel 表格的处理，最后生成通过模板赋值生成数据表格下载数据表格功能的示例。

先安装组件 NPOI

1. 主方法：生产文件下载路径

 public async Task<string> GetGenerateInspectionReport(long id)
 {
     var result = await _DbQueryable.InSingleAsync(id);
     if (result.IQCResult == null)
     {
         throw new UserFriendlyException("检验单还未检验，无法生成检验报告!");
     }
     var arrivalEntity = await _repository._Db.AsTenant()
         .QueryableWithAttr<ArrivalEntity>()
         .Where(x => x.ArrivalBatchNo == result.BatchNo)
         .FirstAsync();

     var lists = await _inspectionDetailRepository.GetListAsync(x => x.ResultId == result.Id);
     var supplier = await _repository._Db.AsTenant()
         .QueryableWithAttr<NCC_SUPPLIER>()
         .Where(x => x.SUPPLIER_NAME == result.SupplierName && x.IS_DEL == 0)
         .FirstAsync();

     string basePath = "wwwroot/File/text检验报告.xlsx";
     string downLoadPath = string.Empty;
     bool succ = ExcelHelp.GetInterimOutPutFilePath(_hostingEnvironment, ref basePath, ref downLoadPath, "检验报告", "Generate");

     if (!succ)
     {
         throw new Exception("生成下载路径失败");
     }

     string filePath = Path.Combine(_hostingEnvironment.ContentRootPath, basePath);
     string downLoadFilePath = Path.Combine(_hostingEnvironment.ContentRootPath, $"wwwroot/File{downLoadPath}");

     //创建一个工作簿
     XSSFWorkbook book;
     try
     {
         using (var fileStream = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite))
         {
             book = new XSSFWorkbook(fileStream);
             ISheet sheet1 = book.GetSheetAt(0);

             if (sheet1 != null)
             {
             //更新第三行第1列数据为“111111”
                 IRow row2 = sheet1.GetRow(2);
                 ICell r1cell1 = row2.GetCell(0);
                 r1cell1.SetCellValue($"111111 ");
                 }

                 using (var downLoadFileStream = new FileStream(downLoadFilePath, FileMode.Create, FileAccess.Write))
                 {
                     book.Write(downLoadFileStream);
                 }
             }
         }

     }
     catch (Exception ex)
     {
         throw new Exception("生成检验报告失败", ex);
     }
     string oldFilePath = result.InspectionReportPath;
     result.InspectionReportPath =  downLoadPath;
     await _repository.UpdateAsync(result);

     if (!string.IsNullOrEmpty(oldFilePath))
     {
         string oldFileFullPath = Path.Combine(_hostingEnvironment.ContentRootPath, $"wwwroot/File{oldFilePath}");
         if (File.Exists(oldFileFullPath))
         {
             File.Delete(oldFileFullPath);
         }
     }
     return downLoadPath.TrimStart('/');
 }

2. 对于exlxhlep工具类

 public class ExcelHelp
 {
     /// <summary>
     /// 生成导入Excel模板
     /// </summary>
     /// <param name="cname">表头</param>
     /// <param name="tablename">表名</param>
     /// <returns></returns>
     public static async Task<string> Generateheader(string cname, string tablename)
     {
         var workbook = new XSSFWorkbook();
         //建立表
         var table = workbook.CreateSheet("sheet");
         List<IRow> rowlist = new List<IRow>();
         string[] cnames = cname.Split(',');
         var columnname = table.CreateRow(0);
         for (int i = 0; i < cnames.Length; i++)
         {
             columnname.CreateCell(i).SetCellValue(cnames[i]);
         }
         await using (var fs = File.OpenWrite($"wwwroot/File/{tablename}.xlsx"))
         {
             workbook.Write(fs);
         }
         string basePath = $"wwwroot\\File\\{tablename}.xlsx";
         string filename = $"{tablename}--" + DateTime.Now.ToString("yyyy_MM_dd_HH_mm_ss");
         string filePath = Path.Combine(Directory.GetCurrentDirectory(), basePath);
         string downLoadPath = filePath;
         if (File.Exists(filePath))
         {
             string fileExt = filePath.Substring(filePath.LastIndexOf("."));
             // 生成临时目录
             string fpath = "/wwwroot/File/" + DateTime.Now.ToString("yyyyMMdd") + "/";
             string path = string.Concat(Directory.GetCurrentDirectory(), fpath);
             if (!Directory.Exists(path))
             {
                 Directory.CreateDirectory(path);
             }
             string interimFile = fpath + filename + fileExt;
             string interimFilePath = string.Concat(Directory.GetCurrentDirectory(), interimFile);
             File.Copy(filePath, interimFilePath, true);
             File.SetAttributes(interimFilePath, FileAttributes.Normal);
             string returnPath = DateTime.Now.ToString("yyyyMMdd") + "/";
             downLoadPath = returnPath + filename + fileExt;
         }
         return downLoadPath;
     }
     /// <summary>
     /// 导入Excel数据
     /// </summary>
     /// <param name="file">文件</param>
     /// <returns>返回表格二维列表，非空</returns>
     public static async Task<List<List<string>>> GenerateDate(IFormFile file)
     {
         List<List<string>> xlsxdatelist = new List<List<string>>();
         List<IRow> rowlist = new List<IRow>();
         DateTime dateTime = DateTime.Now;
         if (file == null || file.Length <= 0)
         {
             return null;
         }
         else
         {
             var filePath = Path.GetTempFileName();//获取文件
             using (var stream = new FileStream(filePath, FileMode.Create))
             {
                 await file.CopyToAsync(stream);//创建一个局部FileStream变量
             }
             var filetest = new FileStream(filePath, FileMode.Open);//返回流
             await using (var fs = filetest)
             {
                 //把xlsx文件中的数据写入workbook1中
                 var workbook1 = new XSSFWorkbook(fs);
                 //嵌套excel
                 for (var i = 0; i < workbook1.NumberOfSheets; i++)
                 {
                     var sheet = workbook1.GetSheetAt(i);
                     for (var j = 0; j <= sheet.LastRowNum; j++)
                     {
                         var row = sheet.GetRow(j + 1);
                         if (row != null)
                         {
                             rowlist.Add(row);
                             List<string> xlsxdate = new List<string>();
                             for (var k = 0; k <= row.LastCellNum - 1; k++)
                             {
                                 var cell = row.GetCell(k);
                                 if (cell != null)
                                 {
                                     Console.Write(cell.ToString() + "  ");
                                 }
                                 if (cell.IsEmpty())
                                 {
                                     return null;
                                 }
                                 xlsxdate.Add(cell.ToString());
                             }
                             xlsxdatelist.Add(xlsxdate);
                             Console.WriteLine();//控制台浏览                              
                         }
                     }
                 }
             }
         }
         return xlsxdatelist;
     }
     /// <summary>
     /// 检验项目导入专用
     /// </summary>

     public static async Task<List<List<string>>> GenerateDate(IFormFile file,int startRow,int startColumn)
     {
         List<List<string>> xlsxdatelist = new List<List<string>>();
         List<IRow> rowlist = new List<IRow>();
         DateTime dateTime = DateTime.Now;
         if (file == null || file.Length <= 0)
         {
             return null;
         }
         else
         {
             var filePath = Path.GetTempFileName();//获取文件
             using (var stream = new FileStream(filePath, FileMode.Create))
             {
                 await file.CopyToAsync(stream);//创建一个局部FileStream变量
             }
             var filetest = new FileStream(filePath, FileMode.Open);//返回流
             await using (var fs = filetest)
             {
                 //把xlsx文件中的数据写入workbook1中
                 var workbook1 = new XSSFWorkbook(fs);
                 //嵌套excel
                 for (var i = 0; i < workbook1.NumberOfSheets; i++)
                 {
                     var sheet = workbook1.GetSheetAt(i);
                     for (var j = startRow; j <= sheet.LastRowNum - 6; j++)
                     {
                         var row = sheet.GetRow(j);
                         if (row != null)
                         {
                             rowlist.Add(row);
                             List<string> xlsxdate = new List<string>();
                             for (var k = startColumn; k <= 15; k++)
                             {
                                 if (k == 4) k = k + 8;
                                 var cell = row.GetCell(k);
                                 if (cell != null)
                                 {
                                     Console.Write(cell.ToString() + "  ");
                                 }
                                 xlsxdate.Add(cell.ToString());
                             }
                             xlsxdatelist.Add(xlsxdate);
                         }
                     }
                 }
             }
         }
         return xlsxdatelist;
     }
     /// <summary>
     /// 导入Excel数据
     /// </summary>
     /// <param name="file">文件</param>
     /// <returns>返回表格二维列表，可空</returns>
     public static async Task<List<List<ICell>>> GenerateCellDate(IFormFile file)
     {
         List<List<ICell>> xlsxdatelist = new List<List<ICell>>();
         DateTime dateTime = DateTime.Now;
         if (file == null || file.Length <= 0)
         {
             return null;
         }
         else
         {
             var filePath = Path.GetTempFileName();//获取文件
             using (var stream = new FileStream(filePath, FileMode.Create))
             {
                 await file.CopyToAsync(stream);//创建一个局部FileStream变量
             }
             var filetest = new FileStream(filePath, FileMode.Open);//返回流
             await using (var fs = filetest)
             {
                 //把xlsx文件中的数据写入workbook1中
                 var workbook1 = new XSSFWorkbook(fs);
                 //嵌套excel
                 for (var i = 0; i < workbook1.NumberOfSheets; i++)
                 {
                     var sheet = workbook1.GetSheetAt(i);
                     for (var j = 0; j <= sheet.LastRowNum; j++)
                     {
                         var row = sheet.GetRow(j + 1);
                         if (row != null)
                         {
                             List<ICell> xlsxdate = new List<ICell>();
                             for (var k = 0; k <= row.LastCellNum - 1; k++)
                             {
                                 var cell = row.GetCell(k);
                                 if (cell != null)
                                 {
                                     Console.Write(cell.ToString() + "  ");
                                 }
                                 xlsxdate.Add(cell);
                             }
                             xlsxdatelist.Add(xlsxdate);
                             Console.WriteLine();//控制台浏览                              
                         }
                     }
                 }
             }
         }
         return xlsxdatelist;
     }
     /// <summary>
     /// 传入模版文件相对路径，返回复制后的临时文件相对路径
     /// </summary>
     /// <param name="filePath"></param>
     /// <returns></returns>
     public static bool GetInterimOutPutFilePath(IWebHostEnvironment _hostingEnvironment,ref string filePath, ref string downLoadPath, string filename = "", string generatePath = "OutPut")
     {
         try
         {
             filename = string.IsNullOrEmpty(filename) ? Guid.NewGuid().ToString() : filename + DateTime.Now.ToString("HH_mm_ss");

             filePath = string.Concat(_hostingEnvironment.ContentRootPath, filePath);
             downLoadPath = filePath;
             if (File.Exists(filePath))
             {
                 string fileExt = filePath.Substring(filePath.LastIndexOf("."));
                 //临时文件目录
                 string fpath = $"wwwroot/File/Upload/{generatePath}/" + DateTime.Now.ToString("yyyyMMdd") + "/";
                 string path = string.Concat(_hostingEnvironment.ContentRootPath, fpath);
                 if (!Directory.Exists(path))
                 {
                     Directory.CreateDirectory(path);
                 }
                 string interimFile = fpath + filename + fileExt;
                 string interimFilePath = string.Concat(_hostingEnvironment.ContentRootPath, interimFile);
                 File.Copy(filePath, interimFilePath, true);
                 File.SetAttributes(interimFilePath, FileAttributes.Normal);
                 string returnPath = $"/Upload/{generatePath}/" + DateTime.Now.ToString("yyyyMMdd") + "/";
                 downLoadPath = returnPath + filename + fileExt;
                 filePath = interimFile;
                 return true;
             }
         }
         catch (Exception ex)
         {
             throw new Exception(ex.ToString());
         }
         return false;
     }
 }

3.前端vue3

function generateInspectionReport(id){
   GenerateReport(id).then(response => { 
   console.log(response.data); 
   window.location.href= upload.urlhead.concat(response.data);
    ElMessage({
    message: '下载成功',
    type: 'success',
  })
  }).catch(() => {});
 }