/// <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); }