@[TOC]NPOI操作已经有了参考
流程梳理
首先是对于自己模板excel的导入,因为有整体格式要求较高的excel,所以先制成模板,再向里面填充数据。
IWorkbook hssfworkbookDown = null;
//读取导入模板
var templatePath = FileVariable.SystemFilePath + "xxxx.xlsx";
using(FileStream file = new FileStream(templatePath, FileMode.Open, FileAccess.Read))
{
//将文件流中模板加载到工作簿对象中
hssfworkbookDown = new XSSFWorkbook(file);
file.Close();
}
//因为我模板就放在第一个sheet里所以就用第一张够了
XSSFSheet sheet1 = (XSSFSheet)hssfworkbookDown.GetSheetAt(0);
静态数据填入
sheet1.GetRow(1).GetCell(1).SetCellValue(info.accept.CreateDate.ToString());
sheet1.GetRow(1).GetCell(8).SetCellValue(“可以是模型内容”);
动态列表录入
//初始报修项目索引行
int Index = 5;
var sort = 1;
#region 添加维修项目列表
var itemList = info.itemList;
foreach(var item in itemList)
{
//往下添加一行
sheet1.ShiftRows(Index, sheet1.LastRowNum, 1, true, false);
//创建行
var rowInsert = sheet1.CreateRow(Index);
rowInsert.Height = 15 * 20;//设置行高度
for(int col = 0;col < 10; col++)
{
var cellInsert = rowInsert.CreateCell(col);
ICellStyle style = hssfworkbookDown.CreateCellStyle();//设置单元格格式
style.Alignment = HorizontalAlignment.Left;//【Center】靠左
if(col == 0)
{
style.Alignment = HorizontalAlignment.Right;//编号靠左
}
style.VerticalAlignment = VerticalAlignment.Center;//【Center】居中
style.BorderTop = BorderStyle.Thin;//上
style.BorderRight = BorderStyle.Thin;//右
if (col == 9)
{
style.BorderRight = BorderStyle.Medium;//尾巴上的为厚的
}
style.BorderBottom = BorderStyle.Thin;//设置单元格低边框为细线
style.BorderLeft = BorderStyle.Thin;//左
cellInsert.CellStyle = style;
//设置字体样式
IFont font = hssfworkbookDown.CreateFont();
font.FontHeight = 200;//设置字体高度
font.FontName = "宋体";//设置字体
font.IsBold = true;//是否加粗
style.SetFont(font);
}
//合并单元格。依据模板的样式要求合并单元格,填入数据(这一步依据自己的实际情况需要来修改)
sheet1.AddMergedRegion(new CellRangeAddress(Index, Index, 1, 3));
sheet1.AddMergedRegion(new CellRangeAddress(Index, Index, 6, 7));
sheet1.AddMergedRegion(new CellRangeAddress(Index, Index, 8, 9));
sheet1.GetRow(Index).GetCell(0).SetCellValue(sort.ToString()+".");
sheet1.GetRow(Index).GetCell(1).SetCellValue(item.ItemName);
Index++;
sort++;
}
#endregion
图片导入(base64 保存的图片为例要是用图片地址的则以上面参考链接为例子)
#region 签名部分数据填入
sheet1.GetRow(Index).GetCell(1).SetCellValue(info.accept.OutsourceUserName);
var outUser = _userService.GetInfoByUserId(info.accept.OutsourceUserId);
sheet1.GetRow(Index + 3).GetCell(1).SetCellValue(outUser.MobilePhone);
//流程信息获取
var flowInfo = await _flowTaskService.GetFlowBeforeInfo(id,null);
var nodeRecordList = flowInfo.flowTaskOperatorRecordList;
#region 承修方签名
var outsourceSign = nodeRecordList.Find(x => x.nodeCode == "gggl3K1" && x.handleStatus == 1).signImg;
outsourceSign = outsourceSign.Replace("data:image/png;base64,", "");
var outdealsign = Convert.FromBase64String(outsourceSign);
//添加图片到对象中,指定图片格式
int pictureIdx = hssfworkbookDown.AddPicture(outdealsign, PictureType.JPEG);
//在sheet中创建画部
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//设置锚点 在起始单元格的X坐标0-1023,Y的坐标0-255,在终止单元格的X坐标0-1023,Y的坐标0-255,起始单元格行数,列数,终止单元格行数,列数
IClientAnchor anchor = patriarch.CreateAnchor(0, 0, 0, 0, 1, Index + 1, 3, Index + 2);
//创建图片
IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
#endregion