原

使用NPOI导出excel（包括图片）

12167人阅读 2019/4/25 9:04 总访问：136597 评论：5 收藏：0 手机

分类: C#

Excl模板导出相信我们都会，那么模板上要导出图片呢？

嗯~还是来个例子：

准备工作：

首先要引用NPOI包：

然后获取数据集（我这里以导出用户信息为例子）：

using BaseUtility;
using DataAccess.Repository;
using IService.UserManage;
using Model.EntityModel;
using Model.ResultModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Service.UserManage
{
    /// <summary>
    /// 用户信息操作
    /// </summary>
    public class UserService : RepositoryFactory<GetDataResult>, IUserService
    {
        /// <summary>
        /// 根据用户code获取用户信息
        /// </summary>
        /// <param name="usercode"></param>
        /// <returns></returns>
        public GetDataResult GetUserInfoByCode(string usercode)
        {
            GetDataResult result = new GetDataResult();

            try
            {
                var watch = CommonHelper.TimerStart();

                StringBuilder sql = new StringBuilder();

                sql.Append("select UserName Name,UserCode,CreateTime,HeadImg,Remark Memo from UserInfo");
                sql.Append(" where UserCode = @UserCode");
                SqlParameter[] parameters = {
                            new SqlParameter("@UserCode", usercode),
                        };
                DataSet ds = Repository().FindDataSetBySql(sql.ToString(), parameters);
                if (ds.Tables.Count > 0)
                {
                    // 将dt转换为model
                    result.Return_Data = BaseUtility.TableToList.GetModel(ds.Tables[0],new UserInfo());
                    result.Return_ID = 0;
                    result.Return_Mess = "请求成功！";
                    result.CostTime = CommonHelper.TimerEnd(watch);
                }
            }
            catch (Exception ex)
            {
                result.Return_ID = 999;
                result.Return_Mess = ex.Message;
            }

            return result;
        }
    }
}

导出工具类核心代码：

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace BaseUtility.Files
{
    /// <summary>
    /// 功能描述：
    ///     Excel操作类
    /// </summary>
    public class ExcelHelper
    {

        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }

            switch (cell.CellType)
            {
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:

                    if (DateUtil.IsCellDateFormatted(cell))
                    {
                        return cell.DateCellValue.ToString("yyyy/MM/dd HH:mm:ss");
                    }

                    return cell.NumericCellValue.ToString(CultureInfo.InvariantCulture);
                case CellType.Formula:
                    HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                    return eva.Evaluate(cell).StringValue;
                case CellType.String:
                case CellType.Unknown:
                    return cell.StringCellValue ?? cell.RichStringCellValue.String;
                case CellType.Blank:
                default:
                    return string.Empty;

            }
        }

        /// <summary>
        /// 根据excel模板导出数据和图片
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="templatefile">模板文件</param>
        /// <param name="Model">实体</param>
        /// <param name="bytes">图片字节流</param>
        /// <param name="sheetName">输出文件名</param>
        /// <param name="cells">单元格对象</param>
        /// <returns></returns>
        public static byte[] ExportList<TEntity>(string templatefile, TEntity model,List<Byte[]> bytes, string sheetName, IList<EntityCell> cells)
        {
            IWorkbook workbook;
            using (var file = new FileStream(templatefile, FileMode.Open, FileAccess.Read))
            {
                workbook = WorkbookFactory.Create(file);
            }
            ISheet sheet = workbook.GetSheetAt(0);

            //插入图片
            WriteInImg(workbook, sheet, bytes);

            workbook.SetSheetName(0, sheetName);

            foreach (var cell in cells)
            {
                ICell item = sheet.GetRow(cell.RowIndex).GetCell(cell.ColumnIndex);
                item.SetCellValue(cell.CellValue);
            }

            ICellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.WrapText = true;
            Type entityType = typeof(TEntity);
            Type descAttType = typeof(DescriptionAttribute);

            List<PropertyInfo> columnProperty = entityType
                    .GetProperties()
                    .Where(o => o.IsDefined(descAttType, false))
                    .ToList();

            for (int n = 0; n < sheet.LastRowNum + 1; n++)
            {
                List<string> columnBindList = sheet.GetRow(n).Cells.Select(GetCellValue).ToList();
                IRow row = sheet.GetRow(n);
                if (columnBindList.Count > 0)
                {
                    for (int i = 0; i < row.LastCellNum; i++)
                    {

                        //得到当前单元格
                        ICell cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        string cellvalues = cell.StringCellValue;
                        if (string.IsNullOrEmpty(cellvalues))
                        {
                            continue;
                        }

                        foreach (PropertyInfo header in typeof(TEntity).GetProperties())
                        {
                            PropertyInfo property = null;
                            //这里匹配模板中值，然后填充
                            if (cellvalues.Contains("{" + header.Name + "}"))
                            {
                                property = header;
                            }
                            //property = columnProperty.FirstOrDefault(o => cellvalues.Contains("{"));

                            if (property == null)
                            {
                                continue;
                            }
                            string a = property.Name;
                            //判断当前属性的数据类型
                            object value = property.GetValue(model, null);
                            //得到当前单元格
                            //ICell cell = row.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK);
                            cell.SetCellValue(value + "");

                        }
                    }
                }
            }
            using (var ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms.ToArray();
            }
        }

        /// <summary>
        /// 填充图片
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        /// <param name="bytes"></param>
        private static void WriteInImg(IWorkbook workbook, ISheet sheet, List<byte[]> bytes)
        {
            int count = 0;
            int i = 1;
            for (int j = 0; j < bytes.Count; j++)
            {
                byte[] _byte = bytes[j];

                int pictureIdx = workbook.AddPicture(_byte, PictureType.PNG);

                HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

                //cout 15 代表起始坐标（X,Y） i 19代表结束坐标(X,Y) 我这里是以我的模板调的,模板图片位置不一样就改变这个值
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, count, 7, i, 11);

                i = 2 + i;

                count = count + 2;

                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

            }
        }
    }

    /// <summary>
    /// 单元格对象
    /// </summary>
    public class EntityCell
    {
        /// <summary>
        /// 行下标
        /// </summary>
        public int RowIndex;
        /// <summary>
        /// 列下标
        /// </summary>
        public int ColumnIndex;
        /// <summary>
        /// 单元格内容
        /// </summary>
        public string CellValue;
    }
}

写一个控制器，调用测试：

using BaseUtility.Files;
using BLL.UserManage;
using Model.EntityModel;
using Model.ResultModel;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace Admin.Controllers
{
    public class ExportExcelController : Controller
    {
        // GET: ExportExcel
        public ActionResult ExportExcelIndex()
        {
            GetDataResult result = UserBLL.GetUserInfoByCode("aojiancc");

            UserInfo user = new UserInfo();
            if (result.Return_ID == 0)
            {
                user = result.Return_Data;
            }

            //获取图片,并获取转化为字节流
            List<byte[]> bytes = new List<byte[]>();
            if (!string.IsNullOrEmpty(user.HeadImg))
            {
                string flePath = user.HeadImg;//头像路径
                string[] pathleng = flePath.Split('~');
                flePath = pathleng[1];
                string dataDir = AppDomain.CurrentDomain.BaseDirectory;
                flePath = flePath.Substring(1, flePath.Length - 1);
                flePath = dataDir + flePath;
                byte[] vs = System.IO.File.ReadAllBytes(flePath);
                if (vs.Length > 0)
                {
                    bytes.Add(vs);
                }
            }
            //输出excel名称
            string cellValue = string.Format(user.Name+"用户信息报告（{0:yyyy年MM月dd日}）", DateTime.Now.ToString("yyyy-MM-dd"));
            string fileName = string.Format(user.Name + "用户信息报告（{0:yyyy年MM月dd日}）.xls", DateTime.Now.ToString("yyyy-MM-dd"));
            //模板路径
            string templatefile = Server.MapPath("~/ExcelTemplet/用户信息报告输出模板.xls");
            //单元格对象设置
            List<EntityCell> cells = new List<EntityCell>
            {
                /*new EntityCell
                {
                    CellValue = cellValue,
                    ColumnIndex = 0,
                    RowIndex = 0
                }*/
            };
            //调用输出方法
            byte[] buffer = ExcelHelper.ExportList<UserInfo>(templatefile, user, bytes, fileName, cells);
            //byte[] buffer = ms.ToArray();
            string contentType = "application/vnd.ms-excel";
            return File(buffer, contentType, fileName);
        }
    }
}

模板样式：