分类:
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);
}
}
}模板样式：
运行效果：
评价
