数据导Execl

需要导入NuGet的NPOI包

数据源是一个list

可以通过此方法获取其中的属性名（表头）

//获取表头名称
List<string> title = GetDisplayName((tickets[0]));
public List<string> GetDisplayName<T>(T model) 
{
    //获取所有属性
    PropertyInfo[] properties = model.GetType().GetProperties();
    var list = new List<string>();
    foreach (var item in properties) 
    {
        var attrs = item.GetCustomAttributes(typeof(DisplayNameAttribute), true);
        if (attrs != null) 
        {
            var displayName = ((DisplayNameAttribute)attrs[0]).DisplayName;
            if (displayName != "ID") 
            {
                list.Add(displayName);
            }
        }
    }
    return list;
}

 

传入一个list返回文件对象.Net Core

/// <summary>
/// 导出excel数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entitys">实体集合</param>
/// <param name="title">表头集合</param>
/// <param name="picindex">图片所在的索引数组</param>
/// <returns></returns>
public IActionResult OutputExcel<T>(List<T> entitys, List<string> title, int[] picindex) 
{
    //获取图片列(如果有)最大张数
    int[] maxPic = new int[picindex.Length];
    for (int i = 0; i < picindex.Length; i++) 
    {
         maxPic[i] = GetPicCount(entitys, picindex[i]);
    }
    //创建一个工作簿
    IWorkbook workbook = new XSSFWorkbook();
    ISheet sheet = workbook.CreateSheet("sheet");
    //创建一个IDrawing，用于存图片
    IDrawing drawing = sheet.CreateDrawingPatriarch();
    //反射获取Ticket类
    Type entityType = entitys[0].GetType();
    //获取类成员的类型
    PropertyInfo[] entityProperties = entityType.GetProperties();
    IRow Title = sheet.CreateRow(0);
    for (int i = 0; i <= entitys.Count; i++) 
    {
        sheet.AutoSizeColumn(i);
        //第一行加载表头
        if (i == 0) 
        {
            //单元格高度
            Title.Height = 50 * 20;
            Title.CreateCell(0).SetCellValue("序号");
            for (int k = 1; k < title.Count + 1; k++) 
            {
                Title.CreateCell(k).SetCellValue(title[k - 1]);
            }
            continue;
        }
        //其余的加载内容 else 
        {
            IRow rows = sheet.CreateRow(i);
            //单元格高度
            rows.Height = 150 * 20;
            object entity = entitys[i - 1];
            //第一列序号
            rows.CreateCell(0).SetCellValue(i);
            //循环给其余列
            for (int j = 2; j <= entityProperties.Length; j++) 
            {
                object[] entityValues = new object[entityProperties.Length];
                entityValues[j - 1] = entityProperties[j - 1].GetValue(entity);
                string value = (entityValues[j - 1] ?? "").ToString();
                rows.CreateCell(j - 1).SetCellValue(value);
            }
            //最后一列图片列
            //索引5和15是图片
            int PicIndex1 = entityProperties.Length;
            for (int pic = 0; pic < maxPic.Length; pic++) 
            {
                object[] PicentityValues = new object[entityProperties.Length];
                PicentityValues[picindex[pic]] = entityProperties[picindex[pic]].GetValue(entity);
                string Picvalue = (PicentityValues[picindex[pic]] ?? "").ToString();
                //图片地址的网络路径
                List<string> PicList = Picvalue.Split(",").ToList();
                for (int p = 0; p < PicList.Count; p++) 
                {
                    try 
                    {
                        WebRequest myrequest = WebRequest.Create(PicList[p]);
                        WebResponse myresponse = myrequest.GetResponse();
                        Stream imgstream = myresponse.GetResponseStream();
                        System.Drawing.Image img = System.Drawing.Image.FromStream(imgstream);
                        int height = img.Height;
                        int width = img.Width;
                        double Percentage = width / (height * 1.0);
                        //宽度
                        sheet.SetColumnWidth(PicIndex1 + p, Convert.ToInt32((150) * Percentage) / 4 * 256);
                        MemoryStream ms = new MemoryStream();
                        img.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
                        byte[] bytes1 = ms.ToArray();
                        //byte[] bytes1 = System.IO.File.ReadAllBytes(item);
                        int pictureIdx1 = workbook.AddPicture(bytes1, NPOI.SS.UserModel.PictureType.JPEG);
                        NPOI.SS.UserModel.IClientAnchor anchor = new NPOI.XSSF.UserModel.XSSFClientAnchor(0, 0, 0, 0, PicIndex1 + p, i, PicIndex1 + 1 + p, i + 1);
                        anchor.AnchorType = AnchorType.DontMoveAndResize;
                        drawing.CreatePicture(anchor, pictureIdx1);
                    }
                    catch (Exception) 
                    {
                        //这里由于该网络路径对应的图片不存在
                    }
                }
                PicIndex1 += maxPic[pic];
            }
        }
    }
    int PicIndex = entityProperties.Length;
    for (int i = 0; i < maxPic.Length; i++) 
    {
        ICellStyle cellStyle = workbook.CreateCellStyle();
        //声明样式
        cellStyle.Alignment = HorizontalAlignment.Center;
        //水平居中
        cellStyle.VerticalAlignment = VerticalAlignment.Center;
        //垂直居中
        //图片列表头
        Title.CreateCell(PicIndex).SetCellValue("图片" + (i + 1));
        ICell cellpic = Title.Cells[entityProperties.Length + i];
        cellpic.CellStyle = cellStyle;
        sheet.AddMergedRegion(new CellRangeAddress(0, 0, PicIndex, PicIndex + maxPic[i] - 1));
        PicIndex += maxPic[i];
    }
    byte[] bytes = null;
    using (MemoryStream ms = new MemoryStream()) 
    {
        workbook.Write(ms);
        bytes = ms.ToArray();
        ms.Close();
    }
    return File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "ExcelFileName");
}

 

辅助方法，List里面有图片（网络图片路径，以,隔开）

/// <summary>
/// 获得一个List<T>里面图片列中图片的最大数量
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="entitys">集合</param>
/// <param name="picindex">图片列的索引</param>
/// <returns></returns>
public int GetPicCount<T>(List<T> entitys, int picindex) 
{
    Type entityType = entitys[0].GetType();
    PropertyInfo[] entityProperties = entityType.GetProperties();
    int max = 0;
    for (int i = 0; i < entitys.Count; i++) 
    {
        object entity = entitys[i];
        object[] PicentityValues = new object[entityProperties.Length];
        PicentityValues[picindex] = entityProperties[picindex].GetValue(entity);
        string Picvalue = (PicentityValues[picindex] ?? "").ToString();
        //图片地址的网络路径
        List<string> PicList = Picvalue.Split(",").ToList();
        if (PicList.Count > max) 
        {
            max = PicList.Count;
        }
    }
    return max;
}

 

导入文件，传入一个IFormFile ，返回一个list

/// <summary>
/// 导入Excel
/// </summary>
/// <param name="file">导入文件</param>
/// <returns>List<T></returns>
public ExcelInfo InputExcel(IFormFile file) 
{
    List<object> list = new List<object> { };
    MemoryStream ms = new MemoryStream();
    file.CopyTo(ms);
    ms.Seek(0, SeekOrigin.Begin);
    IWorkbook workbook = new XSSFWorkbook(ms);
    ISheet sheet = workbook.GetSheetAt(0);
    IRow cellNum = sheet.GetRow(0);
    var propertys = typeof(ORM.Location).GetProperties();
    string value = string.Empty;
    int num = cellNum.LastCellNum;
    for (int i = 1; i <= sheet.LastRowNum; i++) 
    {
        IRow row = sheet.GetRow(i);
        var obj = Activator.CreateInstance(typeof(ORM.Location));
        for (int j = 1; j < num; j++) 
        {
            try 
            {
                int k = j;
                if (row.GetCell(j) == null) 
                {
                    value = "";
                } else 
                {
                    if (row.GetCell(j).CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(row.GetCell(j))) 
                    {
                        value = row.GetCell(j).DateCellValue.ToString("yyyy/MM/dd HH:mm:ss");
                    } else 
                    {
                        value = row.GetCell(j).ToString();
                    }
                }
                if (row.GetCell(j).GetType() == typeof(string)) 
                {
                    propertys[k].SetValue(obj, value, null);
                } else if (row.GetCell(j).GetType() == typeof(DateTime)) 
                {
                    DateTime pdt = Convert.ToDateTime(value, CultureInfo.InvariantCulture);
                    propertys[k].SetValue(obj, pdt, null);
                } else if (row.GetCell(j).GetType() == typeof(bool)|| row.GetCell(j).GetType() == typeof(boolean)) 
                {
                    bool pb = Convert.Toboolean(value);
                    propertys[k].SetValue(obj, pb, null);
                } else if (row.GetCell(j).GetType() == typeof(Int16)) 
                {
                    short pi16 = Convert.ToInt16(value);
                    propertys[k].SetValue(obj, pi16, null);
                } else if (row.GetCell(j).GetType() == typeof(Int32)) 
                {
                    int pi32 = Convert.ToInt32(value);
                    propertys[k].SetValue(obj, pi32, null);
                } else if (row.GetCell(j).GetType() == typeof(Int64)) 
                {
                    long pi64 = Convert.ToInt64(value);
                    propertys[k].SetValue(obj, pi64, null);
                } else if (row.GetCell(j).GetType() == typeof(byte)) 
                {
                    byte pb = Convert.Tobyte(value);
                    propertys[k].SetValue(obj, pb, null);
                } else 
                {
                    propertys[k].SetValue(obj, null, null);
                }
            }
            catch 
            {
                int i_1 = i + 1;
                int j_1 = j + 1;
                string msg = "Excel表格第" + i_1 + "行,第" + j_1 + "列，发生错误,类型填入不正确或该单元格不能为空";
                List<object> list1 = new List<object>();
                return new ExcelInfo() 
                {
                    list = list1, msg = msg };
                }
            }
            list.Add(obj);
        }
        return new ExcelInfo() 
        {
            list = list, msg = "" 
            };
}

 

 

以上笔记属自己项目中自用，仅供参考