点击刷新
十年河东，十年河西，莫欺少年穷
学无止境，精益求精
好久没写博客了，太忙了.......................................................
代码类东西，不想多说，自己看吧，需要引用NPOI 包。
1、将文件转为IFormFile
/// <summary> /// c://path/to/test.jpg /// </summary> /// <param name="fileName">绝对路径</param> /// <returns></returns> public static IFormFile GetFormFileByFileName(string fileName) { var FileBytes = File.ReadAllBytes(fileName); using (var ms = new MemoryStream(FileBytes)) { IFormFile fromFile = new FormFile(ms, 0, ms.Length, Path.GetFileNameWithoutExtension(fileName), Path.GetFileName(fileName)); return fromFile; } }
2、读取Excel文件
先建一个实体类：
public class student { public string Name { get; set; } public string Age { get; set; } }
Api方法如下：
/// <summary> /// 获取Excel中的数据 /// </summary> /// <param name="fileName"></param> /// <param name="ColumnNumber"></param> /// <returns></returns> [HttpGet] [Route("GetExcelStudents")] public IActionResult GetExcelStudents(string fileName, int ColumnNumber) { List<string> err = new List<string>(); var result = _HardwareService.GetExcelStudents(fileName, ColumnNumber,ref err); return Ok(result); }
服务层方法如下：
/// <summary> /// 绝对路径+文件名称 D:\gitProject\test2.xls /// </summary> /// <param name="fileName"></param> /// <param name="ColumnNumber">读取的列数</param> /// <param name="RefErrors">报错处理</param> /// <returns></returns> public BaseResponse<List<student>> GetExcelStudents(string fileName, int ColumnNumber,ref List<string> RefErrors) { var Data = new List<student>(); var Error = new List<string>(); var FileBytes = File.ReadAllBytes(fileName); using (var ms = new MemoryStream(FileBytes)) { IFormFile file = new FormFile(ms, 0, ms.Length, Path.GetFileNameWithoutExtension(fileName), Path.GetFileName(fileName)); //定义一个bool类型的变量用来做验证 bool flag = true; try { string fileExt = Path.GetExtension(file.FileName).ToLower(); //定义一个集合一会儿将数据存储进来,全部一次丢到数据库中保存 MemoryStream mss = new MemoryStream(); file.CopyTo(mss); mss.Seek(0, SeekOrigin.Begin); IWorkbook book; if (fileExt == ".xlsx") { book = new XSSFWorkbook(mss); } else if (fileExt == ".xls") { book = new HSSFWorkbook(mss); } else { book = null; } ISheet sheet = book.GetSheetAt(0); int CountRow = sheet.LastRowNum + 1;//获取总行数 if (CountRow - 1 == 0) { return null; } #region 循环验证 for (int i = 1; i < CountRow; i++) { //获取第i行的数据 var row = sheet.GetRow(i); if (row != null) { //循环的验证单元格中的数据 for (int j = 0; j < ColumnNumber; j++) { if (row.GetCell(j) == null || row.GetCell(j).ToString().Trim().Length == 0) { flag = false; Error.Add($"第{i + 1}行,第{j + 1}列数据不能为空。"); } } } } #endregion if (flag) { for (int i = 1; i < CountRow; i++) { //实例化实体对象 student studentM = new student(); var row = sheet.GetRow(i); if (row.GetCell(0) != null && row.GetCell(0).ToString().Trim().Length > 0) { studentM.Name = row.GetCell(0).ToString(); } if (row.GetCell(1) != null && row.GetCell(1).ToString().Trim().Length > 0) { studentM.Age = row.GetCell(1).ToString(); } Data.Add(studentM); } } if (!flag) { RefErrors = Error; } } catch (Exception ex) { return CommonBaseResponse.SetResponse<List<student>>(null, false, ex.ToString()); } } return CommonBaseResponse.SetResponse<List<student>>(Data, true); }
最后，我们看看我的Excel文件长啥样
webAPI请求参数：
最最后，我们看看webAPI执行的结果，如下
从结果中我们得知，读取过程中自动跳过了标题。
如何下载文件
第一步，在构造函数中注入通用主机
private readonly IHostingEnvironment _hostingEnvironment; private readonly ISysGroupInfos _Service; private readonly IHardware _HardwareService; /// <summary> /// 构造函数注入 /// </summary> /// <param name="service"></param> /// <param name="hardwareService"></param> /// <param name="hostingEnvironment"></param> public HardwareController(ISysGroupInfos service,IHardware hardwareService,IHostingEnvironment hostingEnvironment) { _hostingEnvironment = hostingEnvironment; _Service = service; _HardwareService = hardwareService; }
第二步，读取到项目工作的目录
string contentRootPath = _hostingEnvironment.ContentRootPath
最后，就可以转化为文件流进行下载了，如下：
/// <summary> /// 下载模板 /// </summary> /// <returns></returns> [HttpGet] [Route("DowmLoadBatteryFile")] public IActionResult DowmLoadBatteryFile() { string contentRootPath = _hostingEnvironment.ContentRootPath+ "/Template/BatteryNos.xlsx"; var stream = System.IO.File.OpenRead(contentRootPath); return File(stream, "application/vnd.android.package-archive", Path.GetFileName(contentRootPath)); }
我的项目结构如下：
针对这种方式，VUE下载的方法我也贴出来，如下：
downloads() { let that = this; that .$axios({ url: "/Api/V2/Hardware/DowmLoadBatteryFile", method: "get", responseType:"blob" }) .then(function (response) { let blob = new Blob([response.data], { type: response.data.type }); // 针对于 IE 浏览器的处理, 因部分 IE 浏览器不支持 createObjectURL if (window.navigator && window.navigator.msSaveOrOpenBlob) { window.navigator.msSaveOrOpenBlob(blob, response.fileName); } else { var downloadElement = document.createElement("a"); var href = window.URL.createObjectURL(blob); // 创建下载的链接 downloadElement.href = href; downloadElement.download = response.fileName; // 下载后文件名 document.body.appendChild(downloadElement); downloadElement.click(); // 点击下载 document.body.removeChild(downloadElement); // 下载完成移除元素 window.URL.revokeObjectURL(href); // 释放掉 blob 对象 } }) .catch(function (error) { console.log(error); }); },
当然，还有几种下载方法，虚拟路径方式
// <summary> /// 虚拟文件地址输出下载 /// </summary> /// <returns></returns> public IActionResult OnPostDown() { var addrUrl = "/bak/love.xls"; return File(addrUrl, "application/vnd.android.package-archive", Path.GetFileName(addrUrl)); }
HttClient方式下载
/// <summary> /// 通过HttpClient获取另外站点的文件流，再输出 /// </summary> /// <returns></returns> public async Task<IActionResult> OnPostDown02() { var path = "https://files.cnblogs.com/files/wangrudong003/%E7%89%B9%E4%BB%B701.gif"; HttpClient client = new HttpClient(); client.BaseAddress = new Uri(path); var stream = await client.GetStreamAsync(path); return File(stream, "application/vnd.android.package-archive", Path.GetFileName(path)); }
多种方案请参考 https://www.cnblogs.com/wangrudong003/p/7592689.html
导出Excel:
公共类：
using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; using NPOI.XSSF.UserModel; using System; using System.Collections.Generic; using System.ComponentModel; using System.Configuration; using System.Data; using System.IO; using System.Reflection; using System.Text; namespace WuAnCommon { public static class ExcelHelper { public static byte[] DataTableToExcel(DataTable table, string title = null, string sheetName = "Sheet") { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); int cellsIndex = 0; // 填充表头 IRow cellsHeader = sheet.CreateRow(cellsIndex); for (int i = 0; i < table.Columns.Count; i++) { cellsHeader.CreateCell(i).SetCellValue(table.Columns[i].ColumnName); } // 填充数据 cellsIndex += 1; foreach (DataRow dr in table.Rows) { IRow row = sheet.CreateRow(cellsIndex); for (int i = 0; i < table.Columns.Count; i++) { row.CreateCell(i).SetCellValue(ToString(dr[i])); } cellsIndex++; } byte[] buffer = null; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); buffer = ms.GetBuffer(); ms.Close(); } return buffer; } public static byte[] ExortToExcel<T>(IList<T> data, string title = null, string sheetName = "Sheet") where T : class, new() { IWorkbook workbook = new XSSFWorkbook(); ISheet sheet = workbook.CreateSheet(sheetName); ICellStyle headerStyle = workbook.CreateCellStyle(); //ICellStyle dataStyle = workbook.CreateCellStyle(); IFont f = workbook.CreateFont(); f.Boldweight = (short)FontBoldWeight.Bold; headerStyle.SetFont(f); int cellsIndex = 0; var propertyInfos = TypeDescriptor.GetProperties(typeof(T)); // 标题 if (!string.IsNullOrEmpty(title)) { // 填充数据 IRow cellsTitle = sheet.CreateRow(0); var cell = cellsTitle.CreateCell(0); cell.CellStyle = headerStyle; cell.SetCellValue(title); // 合并单元格 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, propertyInfos.Count - 1)); cellsIndex = 2; } // 填充表头 IRow cellsHeader = sheet.CreateRow(cellsIndex); var colIndex = 0; for (int i = 0; i < propertyInfos.Count; i++) { var p = propertyInfos[i]; if (p.IsBrowsable && !string.IsNullOrEmpty(p.Description)) { var cell = cellsHeader.CreateCell(colIndex++); cell.CellStyle = headerStyle; cell.SetCellValue(p.Description); } } cellsIndex += 1; // 填充数据 foreach (var item in data) { IRow row = sheet.CreateRow(cellsIndex++); colIndex = 0; for (int i = 0; i < propertyInfos.Count; i++) { var p = propertyInfos[i]; if (p.IsBrowsable && !string.IsNullOrEmpty(p.Description)) { var value = p.GetValue(item); var cell = row.CreateCell(colIndex++); //cell.CellStyle = headerStyle; cell.SetCellValue(ToString(value)); } } } //重置自适应 for (int i = 0; i < cellsHeader.Cells.Count; i++) { sheet.AutoSizeColumn(i); } byte[] buffer = null; using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); buffer = ms.GetBuffer(); ms.Close(); } return buffer; } public static string ToString(Object data) { if (data == null) { return ""; } else if (data.ToString().StartsWith("0")) { return data.ToString(); } return data.ToString(); } public static T2 ConvertToModel<T1, T2>(T1 source) { T2 model = default(T2); PropertyInfo[] pi = typeof(T2).GetProperties(); PropertyInfo[] pi1 = typeof(T1).GetProperties(); model = Activator.CreateInstance<T2>(); foreach (var p in pi) { foreach (var p1 in pi1) { if (p.Name == p1.Name) { p.SetValue(model, p1.GetValue(source, null), null); break; } } } return model; } public static void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col) { try { WriteSqlLog("AddCellPicture", fileurl+"|"+row+"|"+col); //由于File类只能读取本地资源，所以在配置文件中配置了物理路径的前半部分 string FileName = fileurl; if (File.Exists(FileName) == true) { byte[] bytes = System.IO.File.ReadAllBytes(FileName); if (!string.IsNullOrEmpty(FileName)) { int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } } } catch (Exception ex) { WriteSqlLog("AddCellPictureException", ex.ToString()); throw ex; } } public static void WriteSqlLog(string action, string strMessage) { string path = AppDomain.CurrentDomain.BaseDirectory + @"\Log1\"; if (!System.IO.Directory.Exists(path)) System.IO.Directory.CreateDirectory(path); DateTime time = DateTime.Now; string fileFullPath = path + time.ToString("yyyy-MM-dd") + ".txt"; StringBuilder str = new StringBuilder(); str.Append("Time: " + time.ToString() + "\r\n"); str.Append("Action: " + action + "\r\n"); str.Append("Message: " + strMessage + "\r\n"); str.Append("-----------------------------------------------------------\r\n\r\n"); System.IO.StreamWriter sw; if (!File.Exists(fileFullPath)) { sw = File.CreateText(fileFullPath); } else { sw = File.AppendText(fileFullPath); } sw.WriteLine(str.ToString()); sw.Close(); } } }
实体类：
public class CabinetOrderModel { public int GroupId { get; set; } public string UID { get; set; } [Description("提现编号")] public string OrderNo { get; set; } [Description("客户手机号")] public string CustomerPhone { get; set; } [Description("客户姓名")] public string CustomerUserName { get; set; } [Description("订单金额")] public decimal OrderPrice { get; set; } [Description("支付金额")] public decimal PayMoney { get; set; } [Description("平台成本价")] public decimal centerfrmoney { get; set; } [Description("一级加价")] public decimal parentfrmoney { get; set; } [Description("购买充电时长")] public string BuyTime { get; set; } [Description("所属商家")] public string GroupName { get; set; } public int GroupLevel { get; set; } public string Province { get; set; } public string City { get; set; } public string RegionID { get; set; } public string PayGroup { get; set; } public int PayStatus { get; set; } public DateTime PayTime { get; set; } public int PayType { get; set; } public DateTime AddTime { get; set; } [Description("订单时间")] public DateTime OrderTime { get; set; } public string GroupPhone { get; set; } [Description("商家利润")] public decimal LRMoney { get; set; } public int Status { get; set; } [Description("订单状态")] public string StatusCn { get { return GetStatus(this.Status); } } private string GetStatus(int Status) { string s = string.Empty; switch (Status) { case 0: s = "待支付"; break; case 1: s = "待使用"; break; case 2: s = "使用中"; break; case 3: s = "已使用"; break; case 4: s = "已取消"; break; } return s; } public string OrderId { get; set; } public string GoodsName { get; set; } public string GoodsNo { get; set; } public string Count { get; set; } public string RemainderCount { get; set; } public DateTime? StartTime { get; set; } public DateTime? EndTime { get; set; } public int TimeType { get; set; } public string BusinessType { get; set; } public string AreaCode { get; set; } public bool IsDirect { get; set; } }
控制器方法：
/// <summary> /// 导出换电订单 /// </summary> /// <param name="Data"></param> /// <returns></returns> [HttpPost] [Route("ExcelCabinetOrder")] public IActionResult ExcelCabinetOrder([FromBody] SearchCabinetOrderModel Data) { BasePaginationModel pagination = new BasePaginationModel() { pageNumber = 1, pageSize = 10000000 }; var name = "换电订单"; var result = _Service.ExcelCabinetOrder(Data, CurrentUser, ref pagination); return File(result, "application/octet-stream", $"{name}_{DateTime.Now:yyyyMMddHHmmssfff}.xlsx"); }
服务方法：
/// <summary> /// 导出 /// </summary> /// <param name="search"></param> /// <param name="CurrentUser"></param> /// <returns></returns> public byte[] ExcelCabinetOrder(SearchCabinetOrderModel search, CurrentUserData CurrentUser, ref BasePaginationModel Pagination) { var result = SearchCabinetOrder(search, CurrentUser, ref Pagination); return ExcelHelper.ExortToExcel<CabinetOrderModel>(result.data.data, "换电订单", "换电订单"); }
Result.data.data 其实就是一个List<T> 集合
效果：
@天才卧龙的博客
© 著作权归作者所有
举报
发表评论
0/200
