/// <summary>
/// 向Excel中插入图片
/// </summary>
/// <param name="pictureName">图片的绝对路径加文件名</param>
/// <param name="left">左边距</param>
/// <param name="top">右边距</param>
/// <param name="width">宽</param>
/// <param name="heigth">高</param>
public void InsertPictures(string pictureName, int row, int column, int rowEnd, int columnEnd)
{
try
{
if (_myExcel is HSSFWorkbook)
{
HSSFPatriarch patriarch = (HSSFPatriarch)_activeSheet.CreateDrawingPatriarch();
//create the anchor
HSSFClientAnchor anchor;
anchor = new HSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
else if (_myExcel is XSSFWorkbook)
{
XSSFDrawing drawing = (XSSFDrawing)_activeSheet.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor;
anchor = new XSSFClientAnchor(10, 10, 0, 0, column, row, columnEnd, rowEnd);
anchor.AnchorType = 2;
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, LoadImage(pictureName, _myExcel));
picture.LineStyle = LineStyle.DashDotGel;
}
}
catch (Exception exception)
{
//Log.Write.Error("InsertPictures to file fail", exception);
}
}
/*泛类型行不通
* public ISheet pictureDataToSheet<T>(ISheet sheet, T pictureNPOI,int startRow,int startCol, int endRow,int endCol)
* // where T: XSSFPicture, HSSFPicture,类型应该只有一种的原因吧，无法执行类型约束为两个类，因为类的约束必须放在第一个
* {
*
* XSSFPicture pictureNPOI_XSSFPicture = pictureNPOI as XSSFPicture;
* HSSFPalette pictureNPOI_HSSFPalette = pictureNPOI as HSSFPalette;
* //XSSFPicture，HSSFPalette是类，只能有一种类型，正好是泛类型要解决的
* //方法和使用一样，但是T的类型取决类申城的Isheet的类型
* //应该使用重载
* if (true)
* {
*
* }
* else
* {
* return null;
* }
* workbook.AddPicture(pictureNPOI.)
* }
*/
//重载
public ISheet pictureDataToSheet(ISheet sheet, byte[] pictureNPOI, int startRow, int startCol, int endRow, int endCol)
{
/*将实际图片转换为pictureData时使用，但是pictureNPOI本身就是picture
* byte[] pictureByte=
* workbook.AddPicture(, PictureType.PNG);
*/
//判断是否有sheet
//无，则创建
if (sheet == null)
{
sheet = this.workbook.CreateSheet();
}
//执行向sheet写图片
//创建DrawingPatriarch，存放的容器
IDrawing patriarch = sheet.CreateDrawingPatriarch();
///System.InvalidCastException:“无法将类型为“NPOI.XSSF.UserModel.XSSFDrawing”的对象强制转换为类型“NPOI.HSSF.UserModel.HSSFPatriarch”。”
/// HSSFPatriarch patriarch = (HSSFPatriarch)sheetA.CreateDrawingPatriarch();
/// 根据报错改为如下
// IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, startCol, startRow, endCol, endRow);
//将图片文件读入workbook，用索引指向该文件
int pictureIdx = workbook.AddPicture(pictureNPOI, PictureType.PNG);
//根据读入图片和anchor把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
return(sheet);
}
public void Create()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
Assert.AreEqual(AnchorType.MoveAndResize, (AnchorType)anchor.AnchorType);
anchor.AnchorType = AnchorType.DontMoveAndResize;
Assert.AreEqual(AnchorType.DontMoveAndResize, (AnchorType)anchor.AnchorType);
XSSFPicture shape = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.IsTrue(anchor.Equals(shape.GetAnchor()));
Assert.IsNotNull(shape.PictureData);
Assert.IsTrue(Arrays.Equals(jpegData, shape.PictureData.Data));
CT_TwoCellAnchor ctShapeHolder = (CT_TwoCellAnchor)drawing.GetCTDrawing().CellAnchors[0];
// STEditAs.ABSOLUTE corresponds to ClientAnchor.DONT_MOVE_AND_RESIZE
Assert.AreEqual(ST_EditAs.absolute, ctShapeHolder.editAs);
}
public void Test53568()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("53568.xlsx");
List <XSSFPictureData> pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.IsNotNull(pictures);
Assert.AreEqual(4, pictures.Count);
XSSFSheet sheet1 = wb.GetSheetAt(0) as XSSFSheet;
List <XSSFShape> shapes1 = (sheet1.CreateDrawingPatriarch() as XSSFDrawing).GetShapes();
Assert.IsNotNull(shapes1);
Assert.AreEqual(5, shapes1.Count);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
XSSFSheet sheet = wb.GetSheetAt(i) as XSSFSheet;
XSSFDrawing Drawing = sheet.CreateDrawingPatriarch() as XSSFDrawing;
foreach (XSSFShape shape in Drawing.GetShapes())
{
if (shape is XSSFPicture)
{
XSSFPicture pic = (XSSFPicture)shape;
XSSFPictureData picData = pic.PictureData as XSSFPictureData;
Assert.IsNotNull(picData);
}
}
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = AnchorType.MoveDontResize;
//load the picture and get the picture index in the workbook
//first picture
int imageId = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, imageId);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
picture.Resize();
//second picture
int imageId2 = LoadImage("../../image/HumpbackWhale.jpg", workbook);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(500, 200, 0, 0, 5, 10, 7, 15);
XSSFPicture picture2 = (XSSFPicture)patriarch.CreatePicture(anchor2, imageId2);
picture.LineStyle = LineStyle.DashDotGel;
using (FileStream sw = File.Create("test.xlsx"))
{
workbook.Write(sw);
}
}
private void exportXLSX_Click(object sender, RoutedEventArgs e)
{
var newFile = @"newbook.core.xlsx";
using (var fs = new FileStream(newFile, FileMode.Create, FileAccess.Write))
{
// XSSFWorkbook : *.xlsx >= Excel2007
// HSSFWorkbook : *.xls < Excel2007
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet Name");
// 所有索引都从0开始
// 合并单元格
sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 10));
var rowIndex = 0;
IRow row = sheet1.CreateRow(rowIndex); //创建行
row.Height = 30 * 80;
row.CreateCell(0).SetCellValue("this is content");
sheet1.AutoSizeColumn(0); //按照值的长短 自动调节列的大小
rowIndex++;
// 插入图片
byte[] data = File.ReadAllBytes(@"image.jpg");
int picInd = workbook.AddPicture(data, NPOI.SS.UserModel.PictureType.JPEG);
XSSFCreationHelper helper = workbook.GetCreationHelper() as XSSFCreationHelper;
XSSFDrawing drawing = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFClientAnchor anchor = helper.CreateClientAnchor() as XSSFClientAnchor;
anchor.Col1 = 10;
anchor.Row1 = 0;
XSSFPicture pict = drawing.CreatePicture(anchor, picInd) as XSSFPicture;
pict.Resize();
// 新建sheet
var sheet2 = workbook.CreateSheet("Sheet2");
// 更改样式
var style1 = workbook.CreateCellStyle();
style1.FillForegroundColor = HSSFColor.Blue.Index2;
style1.FillPattern = FillPattern.SolidForeground;
var style2 = workbook.CreateCellStyle();
style2.FillForegroundColor = HSSFColor.Yellow.Index2;
style2.FillPattern = FillPattern.SolidForeground;
var cell2 = sheet2.CreateRow(0).CreateCell(0);
cell2.CellStyle = style1;
cell2.SetCellValue(0);
cell2 = sheet2.CreateRow(1).CreateCell(0);
cell2.CellStyle = style2;
cell2.SetCellValue(1);
//保存
workbook.Write(fs);
}
txtStatus.Text = "writing xlsx successful!";
}
public void TestNew()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing Drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
byte[] jpegData = Encoding.UTF8.GetBytes("test jpeg data");
byte[] wmfData = Encoding.UTF8.GetBytes("test wmf data");
byte[] pngData = Encoding.UTF8.GetBytes("test png data");
IList pictures = wb.GetAllPictures();
Assert.AreEqual(0, pictures.Count);
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
Assert.AreEqual(1, pictures.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures[jpegIdx]).Data));
int wmfIdx = wb.AddPicture(wmfData, PictureType.WMF);
Assert.AreEqual(2, pictures.Count);
Assert.AreEqual("wmf", ((XSSFPictureData)pictures[wmfIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures[wmfIdx]).Data));
int pngIdx = wb.AddPicture(pngData, PictureType.PNG);
Assert.AreEqual(3, pictures.Count);
Assert.AreEqual("png", ((XSSFPictureData)pictures[pngIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures[pngIdx]).Data));
//TODO finish usermodel API for XSSFPicture
XSSFPicture p1 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), jpegIdx);
Assert.IsNotNull(p1);
XSSFPicture p2 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), wmfIdx);
Assert.IsNotNull(p1);
XSSFPicture p3 = (XSSFPicture)Drawing.CreatePicture(new XSSFClientAnchor(), pngIdx);
Assert.IsNotNull(p1);
//check that the Added pictures are accessible After write
wb = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(wb);
IList pictures2 = wb.GetAllPictures();
Assert.AreEqual(3, pictures2.Count);
Assert.AreEqual("jpeg", ((XSSFPictureData)pictures2[jpegIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(jpegData, ((XSSFPictureData)pictures2[jpegIdx]).Data));
Assert.AreEqual("wmf", ((XSSFPictureData)pictures2[wmfIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(wmfData, ((XSSFPictureData)pictures2[wmfIdx]).Data));
Assert.AreEqual("png", ((XSSFPictureData)pictures2[pngIdx]).SuggestFileExtension());
Assert.IsTrue(Arrays.Equals(pngData, ((XSSFPictureData)pictures2[pngIdx]).Data));
}
public void Resize()
{
XSSFWorkbook wb = XSSFITestDataProvider.instance.OpenSampleWorkbook("resize_Compare.xlsx") as XSSFWorkbook;
XSSFDrawing dp = wb.GetSheetAt(0).CreateDrawingPatriarch() as XSSFDrawing;
List <XSSFShape> pics = dp.GetShapes();
XSSFPicture inpPic = (XSSFPicture)pics[(0)];
XSSFPicture cmpPic = (XSSFPicture)pics[(0)];
BaseTestResize(inpPic, cmpPic, 2.0, 2.0);
wb.Close();
}
/// <summary>
/// XLSX类型Excle文件添加图片
/// </summary>
/// <param name="intRowIndex">插入图片的行数</param>
/// <param name="pictureIndex">图片的顺序</param>
private void XSSFCreatePicture(int intRowIndex, int pictureIndex)
{
XSSFDrawing xssfDrawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor();
anchor.Row1 = intRowIndex;
//anchor.AnchorType = AnchorType2;
XSSFPicture picture = (XSSFPicture)xssfDrawing.CreatePicture(anchor, pictureIndex);
picture.LineStyle = LineStyle.Solid;
picture.Resize();//显示图片的原始尺寸
}
private void setPic(XSSFWorkbook workbook, XSSFDrawing patriarch, string path, ISheet sheet, int rowline, int col, int rowMergerPlus = 0, int colMergerPlus = 0)
{
if (string.IsNullOrEmpty(path))
{
return;
}
byte[] bytes = System.IO.File.ReadAllBytes(path);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, rowline, col + colMergerPlus, rowline + rowMergerPlus);
//把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
/// <summary>
/// 获取指定sheet的图片
/// </summary>
/// <param name="sheetName"></param>
/// <returns></returns>
public IList <PictureInfo> GetPictures(string sheetName)
{
ISheet sheet = this._workbook.GetSheet(sheetName);
IDrawing a = sheet.DrawingPatriarch;
IList <PictureInfo> result = new List <PictureInfo>();
if (sheet == null)
{
throw new Exception($"名为{sheetName}的sheet不存在");
}
if (sheet is XSSFSheet)
{
List <POIXMLDocumentPart> documentParts = (sheet as XSSFSheet).GetRelations();
foreach (var part in documentParts)
{
if (part is XSSFDrawing)
{
List <XSSFShape> shapes = (part as XSSFDrawing).GetShapes();
foreach (var shap in shapes)
{
if (shap is XSSFPicture)
{
XSSFPicture picture = shap as XSSFPicture;
IClientAnchor anchor = picture.ClientAnchor;
result.Add(new PictureInfo(anchor.Row1, anchor.Row1, anchor.Col1, anchor.Col1, picture.PictureData.Data));
}
}
}
}
}
else if (sheet is HSSFSheet)
{
HSSFShapeContainer shanpContainer = sheet.DrawingPatriarch as HSSFShapeContainer;
if (shanpContainer != null)
{
foreach (HSSFShape shape in shanpContainer.Children ?? new List <HSSFShape>())
{
if (shape is HSSFPicture && shape.Anchor is HSSFClientAnchor)
{
HSSFPicture picture = shape as HSSFPicture;
HSSFClientAnchor anchor = shape.Anchor as HSSFClientAnchor;
result.Add(new PictureInfo(anchor.Row1, anchor.Row1, anchor.Col1, anchor.Col1, picture.PictureData.Data));
}
}
}
}
return(result);
}
public void multiRelationShips()
{
XSSFWorkbook wb = new XSSFWorkbook();
byte[] pic1Data = Encoding.UTF8.GetBytes("test jpeg data");
byte[] pic2Data = Encoding.UTF8.GetBytes("test png data");
List <XSSFPictureData> pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.AreEqual(0, pictures.Count);
int pic1 = wb.AddPicture(pic1Data, XSSFWorkbook.PICTURE_TYPE_JPEG);
int pic2 = wb.AddPicture(pic2Data, XSSFWorkbook.PICTURE_TYPE_PNG);
XSSFSheet sheet1 = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape1 = drawing1.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture;
XSSFPicture shape2 = drawing1.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture;
XSSFSheet sheet2 = wb.CreateSheet() as XSSFSheet;
XSSFDrawing drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape3 = drawing2.CreatePicture(new XSSFClientAnchor(), pic2) as XSSFPicture;
XSSFPicture shape4 = drawing2.CreatePicture(new XSSFClientAnchor(), pic1) as XSSFPicture;
Assert.AreEqual(2, pictures.Count);
wb = XSSFTestDataSamples.WriteOutAndReadBack(wb) as XSSFWorkbook;
pictures = wb.GetAllPictures() as List <XSSFPictureData>;
Assert.AreEqual(2, pictures.Count);
sheet1 = wb.GetSheetAt(0) as XSSFSheet;
drawing1 = sheet1.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape11 = (XSSFPicture)drawing1.GetShapes()[0];
Assert.IsTrue(Arrays.Equals(shape1.PictureData.Data, shape11.PictureData.Data));
XSSFPicture shape22 = (XSSFPicture)drawing1.GetShapes()[1];
Assert.IsTrue(Arrays.Equals(shape2.PictureData.Data, shape22.PictureData.Data));
sheet2 = wb.GetSheetAt(1) as XSSFSheet;
drawing2 = sheet2.CreateDrawingPatriarch() as XSSFDrawing;
XSSFPicture shape33 = (XSSFPicture)drawing2.GetShapes()[0];
Assert.IsTrue(Arrays.Equals(shape3.PictureData.Data, shape33.PictureData.Data));
XSSFPicture shape44 = (XSSFPicture)drawing2.GetShapes()[1];
Assert.IsTrue(Arrays.Equals(shape4.PictureData.Data, shape44.PictureData.Data));
}
/// <summary>
/// .xlsx后缀的Excel文件添加图片
/// </summary>
/// <param name="excelPath"></param>
/// <param name="imgPath"></param>
public static void InsertImageToXLSXExcel(string excelPath, string imgPath)
{
try
{
using (FileStream fs = new FileStream(excelPath, FileMode.Open))//获取指定Excel文件流
{
//创建工作簿
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
//获取第一个工作表（下标从0起）
XSSFSheet sheet = (XSSFSheet)xssfworkbook.GetSheet(xssfworkbook.GetSheetName(0));
//获取指定图片的字节流
byte[] bytes = System.IO.File.ReadAllBytes(imgPath);
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.JPEG);
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//设置图片坐标与大小
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第五列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 5, 2, 10, 3);
//正式在指定位置插入图片
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
//创建一个新的Excel文件流，可以和原文件名不一样，
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
FileStream file = new FileStream(excelPath, FileMode.Create);
//将已插入图片的Excel流写入新创建的Excel中
xssfworkbook.Write(file);
//关闭工作簿
xssfworkbook.Close();
}
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
public static ICell AddPicture(this ICell cell, byte[] bytes)
{
int pictureIdx = cell.Sheet.Workbook.AddPicture(bytes, PictureType.JPEG);
IDrawing patriarch = cell.Sheet.CreateDrawingPatriarch();
// 插图片的位置 HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
if (cell is HSSFCell)
{
HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
//把图片插到相应的位置
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
else
{
XSSFClientAnchor anchor = new XSSFClientAnchor(70, 10, 0, 0, cell.ColumnIndex, cell.RowIndex, cell.ColumnIndex + 1, cell.RowIndex + 1);
//把图片插到相应的位置
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
return(cell);
}
public void IncrementShapeId()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 1, 1, 10, 30);
byte[] jpegData = Encoding.UTF8.GetBytes("picture1");
int jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape1 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)1, shape1.GetCTPicture().nvPicPr.cNvPr.id);
jpegData = Encoding.UTF8.GetBytes("picture2");
jpegIdx = wb.AddPicture(jpegData, PictureType.JPEG);
XSSFPicture shape2 = (XSSFPicture)drawing.CreatePicture(anchor, jpegIdx);
Assert.AreEqual((uint)2, shape2.GetCTPicture().nvPicPr.cNvPr.id);
}
private static void InsertImage(XSSFWorkbook workbook, ISheet sheet, byte[] data, int row, int column)
{
try
{
int picInd = workbook.AddPicture(data, XSSFWorkbook.PICTURE_TYPE_PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, column, row, column + 1, row + 1)
{
AnchorType = AnchorType.MoveAndResize
};
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, picInd);
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
}
catch
{
//ignore
}
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PictureSheet");
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor = new XSSFClientAnchor(500, 200, 0, 0, 2, 2, 4, 7);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, LoadImage("../../image/HumpbackWhale.jpg", workbook));
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.DashDotGel;
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <summary>
/// 简单实现图片替换
/// </summary>
/// <param name="fileFullPath"></param>
/// <param name="replacements"></param>
public void ReplacePlaceholders(string fileFullPath, List <EasyNPOI.Models.Excel.Replacement> replacements)
{
if (string.IsNullOrEmpty(fileFullPath))
{
throw new ArgumentNullException();
}
var fs = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read);
var extName = Path.GetExtension(fileFullPath);
IWorkbook wb;
if (extName == ".xls")
{
wb = new HSSFWorkbook(fs);
}
else if (extName == ".xlsx")
{
wb = new XSSFWorkbook(fs);
}
else
{
wb = null;
}
if (wb == null)
{
throw new ArgumentNullException();
}
var snum = wb.NumberOfSheets;
var reSave = false;
for (int i = 0; i < snum; i++)
{
//if (wb.IsSheetHidden(i))
//{
// continue;
//}
var sheet = wb.GetSheetAt(i);
IRow row = sheet.GetRow(sheet.LastRowNum);
if (row == null)
{
continue;
}
var cells = row.Cells.Where(p => p.CellType == CellType.String).ToList();
foreach (var x in cells)
{
var colNum = x.ColumnIndex;
var rowNum = x.RowIndex;
var cellText = x.StringCellValue;
if (string.IsNullOrEmpty(cellText))
{
continue;
}
foreach (var replace in replacements)
{
if (cellText.Contains(replace.Placeholder))
{
//获取指定图片的字节流
//byte[] bytes = new byte[pictureData.Length];
//pictureData.Read(bytes, 0, bytes.Length);
byte[] bytes = System.IO.File.ReadAllBytes(replace.PictureUrl);
//将图片添加到工作簿中，返回值为该图片在工作表中的索引（从0开始）
//图片所在工作簿索引理解：如果原Excel中没有图片，那执行下面的语句后，该图片为Excel中的第1张图片，其索引为0；
//同理，如果原Excel中已经有1张图片，执行下面的语句后，该图片为Excel中的第2张图片，其索引为1；
int pictureIdx = wb.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
if (extName == ".xls")
{
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, colNum, rowNum, colNum + 1, rowNum + 1);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
reSave = true;
}
else if (extName == ".xlsx")
{
//创建画布
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
//设置图片坐标与大小
//函数原型：XSSFClientAnchor(int dx1, int dy1, int dx2, int dy2, int col1, int row1, int col2, int row2)；
//坐标(col1,row1)表示图片左上角所在单元格的位置，均从0开始，比如(5,2)表示(第五列，第三行),即F3；注意：图片左上角坐标与(col1,row1)单元格左上角坐标重合
//坐标(col2,row2)表示图片右下角所在单元格的位置，均从0开始，比如(10,3)表示(第十一列，第四行),即K4；注意：图片右下角坐标与(col2,row2)单元格左上角坐标重合
//坐标(dx1,dy1)表示图片左上角在单元格(col1,row1)基础上的偏移量(往右下方偏移)；(dx1，dy1)的最大值为(1023, 255),为一个单元格的大小
//坐标(dx2,dy2)表示图片右下角在单元格(col2,row2)基础上的偏移量(往右下方偏移)；(dx2,dy2)的最大值为(1023, 255),为一个单元格的大小
//注意：目前测试发现，对于.xlsx后缀的Excel文件，偏移量设置(dx1,dy1)(dx2,dy2)无效；只会对.xls生效
XSSFClientAnchor anchor = new XSSFClientAnchor(100, 100, 0, 0, colNum, rowNum, colNum + 1, rowNum + 1);
//正式在指定位置插入图片
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
reSave = true;
}
cellText = cellText.Replace(replace.Placeholder, "");
x.SetCellValue(cellText);
}
}
}
}
if (reSave)
{
//创建一个新的Excel文件流，可以和原文件名不一样，
//如果不一样，则会创建一个新的Excel文件；如果一样，则会覆盖原文件
FileStream file = new FileStream(fileFullPath, FileMode.Create);
//将已插入图片的Excel流写入新创建的Excel中
wb.Write(file);
//关闭工作簿
wb.Close();
}
}
/// <summary>
/// Genera el archivo excel
/// </summary>
/// <param name="datos"></param>
/// <param name="tituloReporte"></param>
/// <param name="nombreOrganizacion"></param>
/// <param name="fecha"></param>
/// <returns></returns>
private bool GenerarReporteExcel(List <ReporteTabularDisponibilidadSemanaInfo> datos, string tituloReporte, string nombreOrganizacion, DateTime fecha)
{
bool retValue = false;
XSSFWorkbook hssfworkbook = new XSSFWorkbook();
//Manejo del formato
IDataFormat format = hssfworkbook.CreateDataFormat();
//Estilo para renglon en general encabezado claro
var esRowGeneral = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esRowGeneral.SetFillForegroundColor(GrisClaro);
esRowGeneral.FillPattern = FillPattern.SolidForeground;
esRowGeneral.FillBackgroundColor = IndexedColors.White.Index;
//Creamos la hora del archivo
ISheet sheet1 = hssfworkbook.CreateSheet(Properties.Resources.RecepcionReporteTabular_RptNomHoja);
//indices
int indexColumn = 0;
int indexRow = 6;
int indexNumeroSemana = 0;
int maxCorrales = 0;
foreach (var semana in datos)
{
if (maxCorrales < semana.TotalCorrales)
{
maxCorrales = semana.TotalCorrales;
}
}
//Renglones de encabezado
ICellStyle styleEncabezado = hssfworkbook.CreateCellStyle();
var i = 0;
//Creamos los renglones para alojar el numero de corrales
for (; i < maxCorrales + indexRow + 5; i++)
{
IRow rengloi = sheet1.CreateRow(i);
}
//fuente para todos los encabezados y totales
IFont fontEncabezado = hssfworkbook.CreateFont();
fontEncabezado.FontHeightInPoints = 10;
fontEncabezado.Boldweight = (short)FontBoldWeight.Bold;
IRow renglonEncabezado1 = sheet1.GetRow(indexRow + 1);
IRow renglonNsemana = sheet1.CreateRow(indexRow + 2);
IRow renglonEncabezadoDetalle = sheet1.CreateRow(indexRow + 3);
IRow renglonTotal = sheet1.CreateRow(i);
//Creamos los registros de semanas
foreach (var semana in datos)
{
var indexAux = indexColumn;
//Total Cabezas
XSSFRichTextString lblTotal = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncTotal);
lblTotal.ApplyFont(0, lblTotal.Length, fontEncabezado);
ICell cellLblTotal = renglonTotal.CreateCell(indexAux);
cellLblTotal.SetCellValue(lblTotal);
//Total Cabezas
ICellStyle esTotal = hssfworkbook.CreateCellStyle();
esTotal.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
XSSFRichTextString txtTotal = new XSSFRichTextString(semana.TotalCabezas.ToString());
txtTotal.ApplyFont(0, txtTotal.Length, fontEncabezado);
ICell cellTxtTotal = renglonTotal.CreateCell(indexAux + 3);
cellTxtTotal.CellStyle = esTotal;
cellTxtTotal.SetCellValue(txtTotal);
//LblSem
var esSem = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esSem.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
esSem.SetFillForegroundColor(GrisClaro);
esSem.FillPattern = FillPattern.SolidForeground;
esSem.FillBackgroundColor = IndexedColors.White.Index;
XSSFRichTextString lblSemana = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncSemN);
lblSemana.ApplyFont(0, lblSemana.Length, fontEncabezado);
ICell cellLblSemana = renglonEncabezado1.CreateCell(indexAux);
cellLblSemana.CellStyle = esSem;
cellLblSemana.SetCellValue(lblSemana);
//////merged cells on mutiple rows
//CellRangeAddress regionSemana = new CellRangeAddress(indexRow, indexRow, indexColumn, indexColumn+=2);
//sheet1.AddMergedRegion(regionSemana);
//LblAc
var esAc = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esAc.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
esAc.SetFillForegroundColor(GrisClaro);
esAc.FillPattern = FillPattern.SolidForeground;
esAc.FillBackgroundColor = IndexedColors.White.Index;
XSSFRichTextString lblAc = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncAC);
lblAc.ApplyFont(0, lblAc.Length, fontEncabezado);
ICell cellLblAc = renglonEncabezado1.CreateCell(indexAux + 3);
cellLblAc.CellStyle = esAc;
cellLblAc.SetCellValue(lblAc);
//CellRangeAddress regionAc = new CellRangeAddress(indexRow, indexRow, indexColumn, indexColumn + 1);
//sheet1.AddMergedRegion(regionAc);
//Renglon 2 valor de semana y primer lunes de la semana
indexAux = indexColumn;
//Valor de numero de semana
var estxtSemana = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
estxtSemana.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
estxtSemana.SetFillForegroundColor(GrisClaro);
estxtSemana.FillPattern = FillPattern.SolidForeground;
estxtSemana.FillBackgroundColor = IndexedColors.White.Index;
XSSFRichTextString txtSemana = new XSSFRichTextString(indexNumeroSemana.ToString());
txtSemana.ApplyFont(0, txtSemana.Length, fontEncabezado);
ICell cellTxtSemana = renglonNsemana.CreateCell(indexAux);
cellTxtSemana.CellStyle = estxtSemana;
cellTxtSemana.SetCellValue(txtSemana);
var cellx1 = renglonNsemana.CreateCell(indexAux + 1);
cellx1.CellStyle = estxtSemana;
var cellx2 = renglonNsemana.CreateCell(indexAux + 2);
cellx2.CellStyle = estxtSemana;
//Valor de numero de semana
var esTxtLunes = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esTxtLunes.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
esTxtLunes.SetFillForegroundColor(GrisClaro);
esTxtLunes.FillPattern = FillPattern.SolidForeground;
esTxtLunes.FillBackgroundColor = IndexedColors.White.Index;
XSSFRichTextString txtPrimerLunes =
new XSSFRichTextString(semana.FechaInicioSemana.ToString("MMM dd").ToUpper());
txtPrimerLunes.ApplyFont(0, txtPrimerLunes.Length, fontEncabezado);
ICell cellTxtLunes = renglonNsemana.CreateCell(indexAux + 3);
cellTxtLunes.CellStyle = esTxtLunes;
cellTxtLunes.SetCellValue(txtPrimerLunes);
var cellx3 = renglonNsemana.CreateCell(indexAux + 4);
cellx3.CellStyle = estxtSemana;
indexAux = indexColumn;
//Valor de enc corral
var esEncCorr = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esEncCorr.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
esEncCorr.SetFillForegroundColor(GrisClaro);
esEncCorr.FillPattern = FillPattern.SolidForeground;
esEncCorr.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 5 * 256);
XSSFRichTextString encCorral = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncCorral);
encCorral.ApplyFont(0, encCorral.Length, fontEncabezado);
ICell cellEncCorral = renglonEncabezadoDetalle.CreateCell(indexAux++);
cellEncCorral.CellStyle = esEncCorr;
cellEncCorral.SetCellValue(encCorral);
//Valor disp
var esDis = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esDis.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esDis.SetFillForegroundColor(GrisClaro);
esDis.FillPattern = FillPattern.SolidForeground;
esDis.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 3 * 256);
XSSFRichTextString encDispoManual = new XSSFRichTextString("");
encDispoManual.ApplyFont(0, encDispoManual.Length, fontEncabezado);
ICell cellDispManual = renglonEncabezadoDetalle.CreateCell(indexAux++);
cellDispManual.CellStyle = esDis;
cellDispManual.SetCellValue(encDispoManual);
//Valor de enc tipo
var esTipo = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esTipo.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
esTipo.SetFillForegroundColor(GrisClaro);
esTipo.FillPattern = FillPattern.SolidForeground;
esTipo.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 20 * 256);
XSSFRichTextString encTipo = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncTipo);
encTipo.ApplyFont(0, encTipo.Length, fontEncabezado);
ICell cellEncTipo = renglonEncabezadoDetalle.CreateCell(indexAux++);
cellEncTipo.CellStyle = esTipo;
cellEncTipo.SetCellValue(encTipo);
//Valor de enc cabezas
var esCab = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esCab.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esCab.SetFillForegroundColor(GrisClaro);
esCab.FillPattern = FillPattern.SolidForeground;
esCab.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 5 * 256);
XSSFRichTextString encCabezas = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncCab);
encCabezas.ApplyFont(0, encCabezas.Length, fontEncabezado);
ICell cellEncCabezas = renglonEncabezadoDetalle.CreateCell(indexAux++);
cellEncCabezas.CellStyle = esCab;
cellEncCabezas.SetCellValue(encCabezas);
//encabezado formula
var esForm = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
esForm.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esForm.SetFillForegroundColor(GrisClaro);
esForm.FillPattern = FillPattern.SolidForeground;
esForm.FillBackgroundColor = IndexedColors.White.Index;
sheet1.SetColumnWidth(indexAux, 5 * 256);
XSSFRichTextString encForm = new XSSFRichTextString(Properties.Resources.RecepcionReporteTabular_RptEncFor);
encForm.ApplyFont(0, encForm.Length, fontEncabezado);
ICell cellEncFormula = renglonEncabezadoDetalle.CreateCell(indexAux);
cellEncFormula.CellStyle = esForm;
cellEncFormula.SetCellValue(encForm);
//colocamos los corrales
var indexCorral = indexRow + 4;
foreach (var corral in semana.Corrales)
{
indexAux = indexColumn;
IRow rowCorral = sheet1.GetRow(indexCorral);
XSSFRichTextString rwCorral = new XSSFRichTextString(corral.Codigo.Trim());
ICell cellCorral = rowCorral.CreateCell(indexAux++);
cellCorral.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
cellCorral.SetCellValue(rwCorral);
XSSFRichTextString rwDispo =
new XSSFRichTextString(corral.DisponibilidadManual == 1 ? "*" : "");
ICell cellDispo = rowCorral.CreateCell(indexAux++);
cellDispo.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
cellDispo.SetCellValue(rwDispo);
XSSFRichTextString rwTipo = new XSSFRichTextString(corral.Descripcion);
ICell cellTipo = rowCorral.CreateCell(indexAux++);
cellTipo.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellTipo.SetCellValue(rwTipo);
XSSFRichTextString rwCabezas = new XSSFRichTextString(corral.Cabezas.ToString());
ICell cellCabezas = rowCorral.CreateCell(indexAux++);
ICellStyle esRwCabezas = hssfworkbook.CreateCellStyle();
esRwCabezas.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esRwCabezas.DataFormat = format.GetFormat("0.00");
cellCabezas.CellStyle = esRwCabezas;
cellCabezas.SetCellValue(rwCabezas);
XSSFRichTextString rwFormula = new XSSFRichTextString(corral.FormulaIDServida.ToString());
ICell cellFormula = rowCorral.CreateCell(indexAux);
ICellStyle esRwFormula = hssfworkbook.CreateCellStyle();
esRwFormula.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
esRwFormula.DataFormat = format.GetFormat("0.00");
cellFormula.CellStyle = esRwFormula;
cellFormula.SetCellValue(rwFormula);
indexCorral++;
}
indexColumn += 6;
sheet1.SetColumnWidth(indexColumn - 1, 2 * 256);
for (int renglonR = indexRow; renglonR < maxCorrales + indexRow + 5; renglonR++)
{
var renglon = sheet1.GetRow(renglonR);
ICell celda = renglon.GetCell(indexColumn - 1);
if (celda == null)
{
celda = renglon.CreateCell(indexColumn - 1);
}
celda.CellStyle = esRowGeneral;
}
indexNumeroSemana++;
if (indexColumn > 10000)
{
indexColumn = 0;
indexRow = maxCorrales + 10;
}
}
//Agregamos el logo
IDrawing patriarch = sheet1.CreateDrawingPatriarch();
//create the anchor
XSSFClientAnchor anchor;
anchor = new XSSFClientAnchor(10, 10, 0, 0, 0, 0, 7, 5);
anchor.AnchorType = 2;
//load the picture and get the picture index in the workbook
XSSFPicture picture = (XSSFPicture)patriarch.CreatePicture(anchor, LoadImage("Imagenes/skLogo.png", hssfworkbook));
//Reset the image to the original size.
//picture.Resize(); //Note: Resize will reset client anchor you set.
picture.LineStyle = LineStyle.None;
for (int indexRowTitulos = 0; indexRowTitulos <= 7; indexRowTitulos++)
{
IRow renglon = sheet1.GetRow(indexRowTitulos);
if (renglon != null)
{
for (int ix = 0; ix <= indexColumn - 1; ix++)
{
ICell celda = renglon.GetCell(ix);
if (celda == null)
{
celda = renglon.CreateCell(ix);
}
celda.CellStyle = esRowGeneral;
}
}
}
for (int ix = 0; ix <= indexColumn - 1; ix++)
{
//ICell celda = renglonEncabezado1.GetCell(ix);
//if (celda == null)
// celda = renglonEncabezado1.CreateCell(ix);
ICell celdaTotal = renglonTotal.GetCell(ix);
if (celdaTotal == null)
{
celdaTotal = renglonTotal.CreateCell(ix);
}
//celda.CellStyle = esRowGeneral;
celdaTotal.CellStyle = esRowGeneral;
}
//Encabezado de empresa
var estiloOrganizacion = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
estiloOrganizacion.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
estiloOrganizacion.SetFillForegroundColor(GrisClaro);
estiloOrganizacion.FillPattern = FillPattern.SolidForeground;
XSSFRichTextString txtOrganizacion = new XSSFRichTextString(nombreOrganizacion);
IFont fontOrg = hssfworkbook.CreateFont();
fontOrg.FontHeightInPoints = 20;
fontOrg.Boldweight = (short)FontBoldWeight.Bold;
txtOrganizacion.ApplyFont(0, txtOrganizacion.Length, fontOrg);
ICell cell = sheet1.GetRow(1).CreateCell(14);
cell.CellStyle = estiloOrganizacion;
cell.SetCellValue(txtOrganizacion);
//Titulo reporte
var estiloTituloReporte = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
estiloTituloReporte.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
estiloTituloReporte.SetFillForegroundColor(GrisClaro);
estiloTituloReporte.FillPattern = FillPattern.SolidForeground;
XSSFRichTextString txtTitulo = new XSSFRichTextString(tituloReporte);
IFont fontTitulo = hssfworkbook.CreateFont();
fontTitulo.FontHeightInPoints = 14;
fontTitulo.Boldweight = (short)FontBoldWeight.Bold;
txtTitulo.ApplyFont(0, txtTitulo.Length, fontTitulo);
ICell cellTitulo = sheet1.GetRow(3).CreateCell(14);
cellTitulo.CellStyle = estiloTituloReporte;
cellTitulo.SetCellValue(txtTitulo);
//fecha
var estiloFecha = (XSSFCellStyle)hssfworkbook.CreateCellStyle();
estiloFecha.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
estiloFecha.SetFillForegroundColor(GrisClaro);
estiloFecha.FillPattern = FillPattern.SolidForeground;
XSSFRichTextString txtFecha = new XSSFRichTextString(fecha.ToString("dd/MM/yyyy"));
IFont fontFecha = hssfworkbook.CreateFont();
fontFecha.FontHeightInPoints = 10;
fontFecha.Boldweight = (short)FontBoldWeight.Bold;
txtFecha.ApplyFont(0, txtFecha.Length, fontFecha);
ICell cellFecha = sheet1.GetRow(4).CreateCell(14);
cellFecha.CellStyle = estiloFecha;
cellFecha.SetCellValue(txtFecha);
retValue = EscribirArchivo(hssfworkbook, Properties.Resources.RecepcionReporteTabular_RptFileName);
return(retValue);
}
///// <summary>
///// 清除时间
///// </summary>
///// <param name="sender"></param>
///// <param name="e"></param>
//private void ClearDatePickerTime(object sender, MouseButtonEventArgs e)
//{
// DatePicker dp = sender as DatePicker;
// dp.Text = "";
//}
/// <summary>
/// 导出excel
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private async void ExportExcelFile_Click(object sender, RoutedEventArgs e)
{
try
{
string saveExcelPath = ConfigurationManager.AppSettings["Excel保存地址"];
if (string.IsNullOrEmpty(saveExcelPath))
{
saveExcelPath = System.Windows.Forms.Application.StartupPath + @"\比对记录导出";
}
else
{
saveExcelPath += @"\比对记录导出";
}
await Task.Run(
() =>
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFCellStyle style = (XSSFCellStyle)workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
XSSFCellStyle style2 = (XSSFCellStyle)workbook.CreateCellStyle();
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style2.BorderBottom = BorderStyle.Thin;
style2.BorderLeft = BorderStyle.Thin;
style2.BorderRight = BorderStyle.Thin;
style2.BorderTop = BorderStyle.Thin;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
style2.SetFont(font);
XSSFCellStyle style0 = (XSSFCellStyle)workbook.CreateCellStyle();
style0.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style0.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
style0.BorderBottom = BorderStyle.Thin;
style0.BorderLeft = BorderStyle.Thin;
style0.BorderRight = BorderStyle.Thin;
style0.BorderTop = BorderStyle.Thin;
IDataFormat dataformat = workbook.CreateDataFormat();
style0.DataFormat = dataformat.GetFormat("yyyy年MM月dd日 h:mm:ss 上午/下午");
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("比对记录");
int row = 0;
List <MyCmpFaceLogWidthImgModel> allCmpInfo = new List <MyCmpFaceLogWidthImgModel>();
allCmpInfo = GetCmpFaceLogByPage(1);
foreach (var item in allCmpInfo)
{
if (row == 0)
{
sheet.CreateRow(row).CreateCell(0).SetCellValue("序号");
sheet.GetRow(row).CreateCell(1).SetCellValue("抓拍通道");
sheet.GetRow(row).CreateCell(2).SetCellValue("抓拍时间");
sheet.GetRow(row).CreateCell(3).SetCellValue("模版姓名");
sheet.GetRow(row).CreateCell(4).SetCellValue("注册类型");
sheet.GetRow(row).CreateCell(5).SetCellValue("相似度");
sheet.GetRow(row).CreateCell(6).SetCellValue("抓拍照片");
sheet.GetRow(row).CreateCell(7).SetCellValue("模版照片");
sheet.GetRow(row).GetCell(0).CellStyle = style2;
sheet.GetRow(row).GetCell(1).CellStyle = style2;
sheet.GetRow(row).GetCell(2).CellStyle = style2;
sheet.GetRow(row).GetCell(3).CellStyle = style2;
sheet.GetRow(row).GetCell(4).CellStyle = style2;
sheet.GetRow(row).GetCell(5).CellStyle = style2;
sheet.GetRow(row).GetCell(6).CellStyle = style2;
sheet.GetRow(row).GetCell(7).CellStyle = style2;
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 30 * 256);
row++;
}
sheet.CreateRow(row).CreateCell(0).SetCellValue(row);
sheet.GetRow(row).Height = 100 * 40;
sheet.GetRow(row).CreateCell(1).SetCellValue(item.channelName);
sheet.GetRow(row).CreateCell(2).SetCellValue(item.time);
sheet.GetRow(row).CreateCell(3).SetCellValue(item.name);
sheet.GetRow(row).CreateCell(4).SetCellValue(item.type);
sheet.GetRow(row).CreateCell(5).SetCellValue(item.score);
sheet.GetRow(row).CreateCell(6).SetCellValue("");
sheet.GetRow(row).CreateCell(7).SetCellValue("");
sheet.GetRow(row).GetCell(0).CellStyle = style;
sheet.GetRow(row).GetCell(1).CellStyle = style;
sheet.GetRow(row).GetCell(2).CellStyle = style0;
sheet.GetRow(row).GetCell(3).CellStyle = style;
sheet.GetRow(row).GetCell(4).CellStyle = style;
sheet.GetRow(row).GetCell(5).CellStyle = style;
sheet.GetRow(row).GetCell(6).CellStyle = style;
sheet.GetRow(row).GetCell(7).CellStyle = style;
if (true)
{
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
List <byte[]> capImage = new List <byte[]>();
capImage = thirft.QueryCmpLogImageH(item.ID, exportCurrDay);
if (capImage.Count > 0)
{
byte[] bytes = capImage[0];
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, 6, row, 7, row + 1);
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
var faceObject = thirft.QueryFaceObj(item.tcUuid);
if (faceObject.Count > 0 && faceObject[0].Tmplate.Count > 0)
{
byte[] bytes2 = faceObject[0].Tmplate[0].Img;
int pictureIdx2 = workbook.AddPicture(bytes2, PictureType.JPEG);
XSSFClientAnchor anchor2 = new XSSFClientAnchor(0, 0, 0, 0, 7, row, 8, row + 1);
XSSFPicture pict2 = (XSSFPicture)patriarch.CreatePicture(anchor2, pictureIdx2);
}
}
row++;
Thread.Sleep(1);
}
FileStream fs = new FileStream(saveExcelPath + ".xlsx", FileMode.Create, FileAccess.Write);
workbook.Write(fs);
workbook = null;
});
MB_MODULES.Views.MyMessage.showYes("导出完成!");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static MemoryStream Export(DataSet dataSet, IList <Image> jpgImagesList, Dictionary <bool, string> boolKeyValue, string fileExt)
{
DataSet ds = dataSet.Copy();
IWorkbook workbook;
//XSSFWorkbook 适用XLSX格式，HSSFWorkbook 适用XLS格式
switch (fileExt)
{
case ".xlsx":
workbook = new XSSFWorkbook();
break;
case ".xls":
workbook = new HSSFWorkbook();
break;
default:
workbook = null;
break;
}
if (workbook == null)
{
return(null);
}
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int tableIndex = -1;
for (int i = 0; i < 32; i++)
{
sheet.SetColumnWidth(i, 20 * 256);
}
switch (fileExt)
{
case ".xlsx":
{
if (jpgImagesList != null)
{
XSSFDrawing patriarch = sheet.CreateDrawingPatriarch() as XSSFDrawing;
foreach (Image image in jpgImagesList)
{
byte[] bytes = ConvertImage(image);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFClientAnchor anchor = new XSSFClientAnchor {
Row1 = rowIndex
};
XSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx) as XSSFPicture;
if (pict != null)
{
pict.Resize();
}
rowIndex = anchor.Row2 + 2;
}
}
}
break;
case ".xls":
{
if (jpgImagesList != null)
{
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
foreach (Image image in jpgImagesList)
{
byte[] bytes = ConvertImage(image);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
HSSFClientAnchor anchor = new HSSFClientAnchor {
Row1 = rowIndex
};
HSSFPicture pict = patriarch.CreatePicture(anchor, pictureIdx) as HSSFPicture;
if (pict != null)
{
pict.Resize();
}
rowIndex = anchor.Row2 + 2;
}
}
}
break;
}
foreach (DataTable dtSource in ds.Tables)
{
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex >= 60000)
{
sheet = workbook.CreateSheet();
rowIndex = 0;
}
if (rowIndex == 0 || tableIndex != ds.Tables.IndexOf(dtSource))
{
tableIndex = ds.Tables.IndexOf(dtSource);
//#region 表头及样式
//{
// if (dtSource.TableName.IndexOf("Table", StringComparison.Ordinal) != 0)
// {
// IRow headerRow = sheet.CreateRow(rowIndex);
// headerRow.HeightInPoints = dtSource.TableName.Length != new Regex(" ").Replace(dtSource.TableName, "\n", 1).Length ? 25 : 50;
// headerRow.CreateCell(0).SetCellValue(new Regex(" ").Replace(dtSource.TableName, "\n", 1));
// ICellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
// IFont font = workbook.CreateFont();
// font.FontHeightInPoints = 20;
// font.Boldweight = 700;
// //font.Color = HSSFColor.WHITE.index;
// headStyle.SetFont(font);
// headStyle.WrapText = true;
// //headStyle.FillForegroundColor = GetXLColour(workbook, AppConfig.ZhongTaiLightRed);
// //headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
// headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, 0, dtSource.Columns.Count - 1 < 0 ? 0 : dtSource.Columns.Count - 1));
// }
//}
//#endregion
rowIndex = -1;
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(rowIndex + 1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
//font.Color = HSSFColor.WHITE.index;
headStyle.SetFont(font);
headStyle.WrapText = true;
//headStyle.FillForegroundColor = GetXLColour(workbook, AppConfig.ZhongTaiLightRed);
//headStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(new Regex(" ").Replace(column.ColumnName, "\n", 1));
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
}
}
#endregion
rowIndex += 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
if (boolKeyValue != null)
{
string value;
boolKeyValue.TryGetValue(boolV, out value);
newCell.SetCellValue(value);
}
else
{
newCell.SetCellValue(boolV ? "√" : "×");
}
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
rowIndex += 2;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
//sheet.Dispose();
return(ms);
}
}
/// <summary>
/// 集合导出Excel
/// </summary>
/// <param name="list">集合</param>
/// <param name="columnNames">列名转换</param>
/// <param name="dicOnly">部分转换</param>
/// <returns></returns>
public static byte[] NpoiListToExcel(List <T> list, string sheetName = "Sheet1")
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet = workbook.CreateSheet(sheetName);
var headRow = sheet.CreateRow(0);
PropertyInfo[] props = typeof(T).GetProperties();
for (var i = 0; i < props.Length; ++i)
{
Object obj = props[i].GetCustomAttribute(typeof(ExcelAttribute));
if (obj != null)
{
ExcelAttribute head = (ExcelAttribute)obj;
headRow.CreateCell(i).SetCellValue(head.Header);
}
else
{
headRow.CreateCell(i).SetCellValue(props[i].Name);
}
}
for (var i = 0; i < list.Count; ++i)
{
var row = sheet.CreateRow(i + 1);
for (var j = 0; j < props.Length; ++j)
{
Object obj = props[j].GetCustomAttribute(typeof(ExcelAttribute));
var key = props[j];
if (obj != null)
{
ExcelAttribute head = (ExcelAttribute)obj;
if (head.Picture)
{
string url = props[j].GetValue(list[i]).ToString().Split(",")[0];
if (!string.IsNullOrWhiteSpace(url))
{
row.Height = 80 * 20;
WebClient temp = new WebClient();
byte[] bytes = temp.DownloadData(url);
int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, j, i + 1, j + 1, i + 2);
XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx);
bytes = null;
}
continue;
}
else
{
var drValue = props[j].GetValue(list[i]) == null ? "" : props[j].GetValue(list[i]).ToString();
var type = props[j].PropertyType;
if (head.Obj != null)
{
type = head.Obj.GetType();
}
if (type == typeof(Int32) || type == typeof(Int32?))
{
int intV = 0;
int.TryParse(drValue, out intV);
row.CreateCell(j).SetCellValue(intV);
}
else if (type == typeof(System.Decimal) || type == typeof(System.Decimal?) ||
type == typeof(System.Double) || type == typeof(System.Double?))
{
double doubV = 0;
double.TryParse(drValue, out doubV);
if (string.IsNullOrWhiteSpace(drValue))
{
row.CreateCell(j).SetCellValue("");
}
else
{
row.CreateCell(j).SetCellValue(doubV);
}
}
else if (type == typeof(System.Byte) || type == typeof(System.Byte?))
{
Byte doubV = 0;
Byte.TryParse(drValue, out doubV);
row.CreateCell(j).SetCellValue(doubV);
}
else if (type == typeof(System.Boolean) || type == typeof(System.Boolean?))
{
Boolean doubV = false;
Boolean.TryParse(drValue, out doubV);
row.CreateCell(j).SetCellValue(doubV);
}
else
{
row.CreateCell(j).SetCellValue(drValue);
}
}
}
}
}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= props.Length; columnNum++)
{
int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (sheet.GetRow(rowNum) == null)
{
currentRow = sheet.CreateRow(rowNum);
}
else
{
currentRow = sheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length + 1;
}
}
if (columnWidth > 40)
{
columnWidth = 40;
}
}
sheet.SetColumnWidth(columnNum, columnWidth * 256);
}
//转为字节数组
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
workbook.Close();
stream.Close();
stream.Dispose();
return(buf);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">数据</param>
/// <param name="headDict">头部信息</param>
/// <param name="imgColumnList">图片列</param>
/// <param name="sheetName">Sheet名</param>
/// <param name="showSrNo">是否要加上序号</param>
/// <returns></returns>
public static byte[] ExportExcel <T>(List <T> data, Dictionary <string, string> headDict, List <string> imgColumnList, string sheetName = "", bool showSrNo = false)
{
if (imgColumnList == null)
{
imgColumnList = new List <string>();
}
DataTable dt = ListToDataTable <T>(data);
byte[] result = null;
List <string> keyList = new List <string>();
if (showSrNo)
{
keyList.Add("RowNum");
dt.Columns.Add("RowNum");
for (int i = 0; i < dt.Rows.Count; i++)
{
dt.Rows[i]["RowNum"] = i + 1;
}
}
//通过键的集合取
foreach (string key in headDict.Keys)
{
keyList.Add(key);
}
IWorkbook workbook = new XSSFWorkbook();
//设置宽度
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
style.Alignment = HorizontalAlignment.Center; //水平对齐;
if (showSrNo)
{
headDict.Add("RowNum", "序号");
}
ISheet sheet = sheetName.IsNullOrEmpty() ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(sheetName);
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < keyList.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(headDict[keyList[i]]);
cell.CellStyle = style;
}
//数据
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < keyList.Count; j++)
{
if (imgColumnList.Contains(keyList[j]))
{
//插入图片
byte[] bytes = HttpMethods.GetImage(dt.Rows[i][keyList[j]].ToString());
if (bytes != null)
{
ICell cell = row1.CreateCell(j);
cell.CellStyle = style;
try
{
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG);
IDrawing patriarch = sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 100, 100, j, i + 1, j + 1, i + 2);
//##处理照片位置，【图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50
XSSFPicture pict = (XSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
}
catch
{
cell.SetCellValue(dt.Rows[i][keyList[j]].ToString());
}
}
}
else
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][keyList[j]].ToString());
cell.CellStyle = style;
}
}
}
//自适应列宽
for (int i = 0; i < keyList.Count; i++)
{
sheet.AutoSizeColumn(i, true);
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
result = ms.GetBuffer();
ms.Close();
};
return(result);
}
