使用ClosedXML实现Excel导入导出

使用ClosedXML实现Excel导入导出

写在开头

游览Dotnet 基金会中的项目时，发现了这个库,Github 链接，它的性能非常好，详细见下图

话不多说，直接上代码吧！

导出

后端：

public static byte[] Output<T>(List<T> data,  string sheetName) 
    {
        using var workbook = new XLWorkbook();
            var worksheet = workbook.Worksheets.Add(sheetName);

            var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

            //表头
            for (int i = 0; i < properties.Length; i++)
            {
                worksheet.Cell(1, i + 1).Value = properties[i].Name;
            }

            //值
            for (int row = 0; row < data.Count; row++)
            {
                for (int col = 0; col < properties.Length; col++)
                {
                    var value = properties[col].GetValue(data[row], null);
                    worksheet.Cell(row + 2, col + 1).Value = value?.ToString()??"" ;
                }
            }

            //保存至内存流
            using var ms = new MemoryStream();
            workbook.SaveAs(ms);
            ms.Position = 0;
            return ms.ToArray();
    }
[HttpGet("output")]
    public async Task<IActionResult> Output()
    {
        var data =
            new{
               字段1=1，
                 字段2=“2”
            }；
        return File(ExcelHelper.Output(data,“标题”), "application/octet-stream", "output.xls");
    }

前端：

export function fileDownAction(url, parameter) {
  return request({//axios
    url: url,
    method: 'get',
    params: parameter,
    responseType: 'blob'
  })
}
let timeStamp = Date.parse(new Date()).toString().substring(0, 10)
fileDownAction(url + "/output", params).then((res) => {
          // 处理返回的文件流
          let url = window.URL.createObjectURL(new Blob([res]))
          let link = document.createElement('a')
          link.style.display = 'none'
          link.href = url
          link.setAttribute('download', fileName + timeStamp + ".xls")
          document.body.appendChild(link)
          link.click()
        })

导入

后端：

public static async Task<IXLWorksheet> CreateTableAsync(IFormFile file)
    {
        var ms = new MemoryStream();
        await file.CopyToAsync(ms);
        ms.Position = 0;
         var workbook = new XLWorkbook(ms);
        // 获取第一个工作表
        return workbook.Worksheet(1);
    }
//根据位置获取单元格的值
public static string GetCellValue(IXLWorksheet worksheet,string address)
    {
        return worksheet.Cell(address).Value.ToString();//“B12”
  //return worksheet.Cell(row,column).Value.ToString(); 
    }
//根据值获取单元格地址
public static IXLAddress GetCellAddress(IXLWorksheet worksheet,string cellName)
    {
        return  worksheet.CellsUsed(a=>a.Value.ToString()==cellName).First().Address;
  // 以下返回“B12”此类字符串
  //worksheet.CellsUsed(a=>a.Value.ToString()==cellName).First().ToString();
    }

前端代码我就不贴了，传入一个file对象即可

写在最后

如果觉得该库好用，可以去github点个star哦！