public void TestAddShapesToGroup() { HSSFWorkbook wb = new HSSFWorkbook(); // create a sheet with a text box HSSFSheet sheet = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor()); int index = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG); group.CreatePicture(new HSSFChildAnchor(), index); HSSFPolygon polygon = group.CreatePolygon(new HSSFChildAnchor()); polygon.SetPoints(new int[] { 1, 100, 1 }, new int[] { 1, 50, 100 }); group.CreateTextbox(new HSSFChildAnchor()); group.CreateShape(new HSSFChildAnchor()); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(1, patriarch.Children.Count); Assert.IsTrue(patriarch.Children[0] is HSSFShapeGroup); group = (HSSFShapeGroup)patriarch.Children[0]; Assert.AreEqual(group.Children.Count, 4); Assert.IsTrue(group.Children[0] is HSSFPicture); Assert.IsTrue(group.Children[1] is HSSFPolygon); Assert.IsTrue(group.Children[2] is HSSFTextbox); Assert.IsTrue(group.Children[3] is HSSFSimpleShape); HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor()); index = wb.AddPicture(new byte[] { 2, 2, 2 }, PictureType.JPEG); group2.CreatePicture(new HSSFChildAnchor(), index); polygon = group2.CreatePolygon(new HSSFChildAnchor()); polygon.SetPoints(new int[] { 1, 100, 1 }, new int[] { 1, 50, 100 }); group2.CreateTextbox(new HSSFChildAnchor()); group2.CreateShape(new HSSFChildAnchor()); group2.CreateShape(new HSSFChildAnchor()); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sheet = wb.GetSheetAt(0) as HSSFSheet; patriarch = sheet.DrawingPatriarch as HSSFPatriarch; Assert.AreEqual(2, patriarch.Children.Count); group = (HSSFShapeGroup)patriarch.Children[1]; Assert.AreEqual(group.Children.Count, 5); Assert.IsTrue(group.Children[0] is HSSFPicture); Assert.IsTrue(group.Children[1] is HSSFPolygon); Assert.IsTrue(group.Children[2] is HSSFTextbox); Assert.IsTrue(group.Children[3] is HSSFSimpleShape); Assert.IsTrue(group.Children[4] is HSSFSimpleShape); int shapeid = group.ShapeId; }
public static void setPicture(HSSFWorkbook workbook, HSSFSheet worksheet, int row, int col, string fileName) { try { byte[] bytes = File.ReadAllBytes(fileName); if (!string.IsNullOrEmpty(fileName)) { int pictureIdx = 0; if (fileName.EndsWith(".jpg")) { pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); } else if (fileName.EndsWith(".png")) { pictureIdx = workbook.AddPicture(bytes, PictureType.PNG); } HSSFPatriarch patriarch = (HSSFPatriarch)worksheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1); //##处理照片位置,【图片左上角为(col, row)第row+1行col+1列,右下角为( col +1, row +1)第 col +1+1行row +1+1列,宽为50,高为50 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } } catch (Exception ex) { throw ex; } }
public void Test49658() { // test if inserted EscherMetafileBlip will be read again IWorkbook wb = new HSSFWorkbook(); byte[] pictureDataEmf = POIDataSamples.GetDocumentInstance().ReadFile("vector_image.emf"); int indexEmf = wb.AddPicture(pictureDataEmf, PictureType.EMF); byte[] pictureDataPng = POIDataSamples.GetSpreadSheetInstance().ReadFile("logoKarmokar4.png"); int indexPng = wb.AddPicture(pictureDataPng, PictureType.PNG); byte[] pictureDataWmf = POIDataSamples.GetSlideShowInstance().ReadFile("santa.wmf"); int indexWmf = wb.AddPicture(pictureDataWmf, PictureType.WMF); ISheet sheet = wb.CreateSheet(); HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch; ICreationHelper ch = wb.GetCreationHelper(); IClientAnchor anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 2); anchor.Col2 = (/*setter*/ 5); anchor.Row1 = (/*setter*/ 1); anchor.Row2 = (/*setter*/ 6); patriarch.CreatePicture(anchor, indexEmf); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 2); anchor.Col2 = (/*setter*/ 5); anchor.Row1 = (/*setter*/ 10); anchor.Row2 = (/*setter*/ 16); patriarch.CreatePicture(anchor, indexPng); anchor = ch.CreateClientAnchor(); anchor.Col1 = (/*setter*/ 6); anchor.Col2 = (/*setter*/ 9); anchor.Row1 = (/*setter*/ 1); anchor.Row2 = (/*setter*/ 6); patriarch.CreatePicture(anchor, indexWmf); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook); byte[] pictureDataOut = (wb.GetAllPictures()[0] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(pictureDataEmf, pictureDataOut)); byte[] wmfNoHeader = new byte[pictureDataWmf.Length - 22]; Array.Copy(pictureDataWmf, 22, wmfNoHeader, 0, pictureDataWmf.Length - 22); pictureDataOut = (wb.GetAllPictures()[2] as HSSFPictureData).Data; Assert.IsTrue(Arrays.Equals(wmfNoHeader, pictureDataOut)); }
public void Test44916() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet(); // 1. Create drawing patriarch IDrawing patr = sheet.CreateDrawingPatriarch(); // 2. Try to re-get the patriarch IDrawing existingPatr; try { existingPatr = sheet.DrawingPatriarch; } catch (NullReferenceException) { throw new AssertionException("Identified bug 44916"); } // 3. Use patriarch HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 600, 245, (short)1, 1, (short)1, 2); anchor.AnchorType = (3); byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); patr.CreatePicture(anchor, idx1); // 4. Try to re-use patriarch later existingPatr = sheet.DrawingPatriarch; Assert.IsNotNull(existingPatr); }
/// <summary> /// .xls后缀的Excel文件添加图片 /// </summary> /// <param name="excelPath"></param> /// <param name="imgPath"></param> public static void InsertImageToXLSExcel(string excelPath, string imgPath) { byte[] bytes = System.IO.File.ReadAllBytes(imgPath); try { using (FileStream fs = new FileStream(excelPath, FileMode.Open)) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs); HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheet(hssfworkbook.GetSheetName(0)); int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 255, 5, 2, 10, 3);//(255, 125, 1023, 150, 5, 2, 10, 3);//(0, 0, 0, 0, 5, 2, 10, 3); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); FileStream file = new FileStream(excelPath, FileMode.Create); hssfworkbook.Write(file); hssfworkbook.Close(); } } catch (Exception ex) { throw new Exception(ex.Message); } }
/// 向sheet插入图片 /// /// /// private void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col, string mappath) { try { if (string.IsNullOrEmpty(fileurl)) { return; } string path = mappath + fileurl.Replace("/", @"\"); string FileName = path; if (!File.Exists(FileName)) { return; } byte[] bytes = File.ReadAllBytes(FileName); if (!string.IsNullOrEmpty(FileName)) { int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, col, row, col + 1, row + 1); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); // pict.Resize();这句话一定不要,这是用图片原始大小来显示 } } catch (Exception ex) { throw ex; } }
private static ISheet IncluirLogoVLI(HSSFWorkbook workbook, ISheet sheet) { var merge = new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 2); sheet.AddMergedRegion(merge); var diretorioAtual = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location); var caminho = $"{diretorioAtual}/Recursos/logoVLI.png"; byte[] data = ArquivosUtil.RetornarArquivo(caminho); int pictureIndex = workbook.AddPicture(data, PictureType.JPEG); ICreationHelper helper = workbook.GetCreationHelper(); IDrawing drawing = sheet.CreateDrawingPatriarch(); IClientAnchor anchor = helper.CreateClientAnchor(); anchor.Col1 = 1; anchor.Row1 = 1; IPicture picture = drawing.CreatePicture(anchor, pictureIndex); picture.Resize(1.8, 1.8); /*Não mudar o tamanho da imagem física. Aparecerá sobrepondo as outras células ou fixa apenas na célula alocada(mesmo sendo mesclada)*/ return(sheet); }
//向excel中提交图片 private void AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col) { try { //由于File类只能读取本地资源,所以在配置文件中配置了物理路径的前半部分 string DiscPath = ConfigurationManager.AppSettings["PictureDiscPath"]; // string FileName = DiscPath.Replace("\\", "/") + fileurl.Replace("http://www.bolioptics.com/", ""); string FileName = fileurl; FileInfo file = new FileInfo(FileName); if (file.Exists == true) { byte[] bytes = System.IO.File.ReadAllBytes(FileName); if (!string.IsNullOrEmpty(FileName)) { int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } } } catch (Exception ex) { throw ex; } }
/// <summary> /// 将图片导出到Excel /// </summary> /// <param name="sheet"></param> /// <param name="xssfworkbook"></param> /// <param name="imageName"></param> private void ExportImgToExcel(ISheet sheet, HSSFWorkbook xssfworkbook, string imageName) { try { string picName = string.IsNullOrEmpty(imageName) ? @"Image\sjlogo.png" : imageName; var picType = string.IsNullOrEmpty(imageName) ? PictureType.PNG : GetPicType(imageName); Image imgOutput = Bitmap.FromFile(Path.Combine(Directory.GetCurrentDirectory(), picName)); //Image imgOutput = System.Drawing.Bitmap.FromStream() Image img = imgOutput.GetThumbnailImage(160, 115, null, IntPtr.Zero); //图片转换为文件流 MemoryStream ms = new MemoryStream(); img.Save(ms, ImageFormat.Bmp); BinaryReader br = new BinaryReader(ms); var picBytes = ms.ToArray(); ms.Close(); //插入图片 if (picBytes != null && picBytes.Length > 0) { int pictureIdx = xssfworkbook.AddPicture(picBytes, picType); //添加图片 HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(200, 90, 240, 200, 0, 0, 1, 0);// 90 200 图片高度110 // new HSSFClientAnchor(X1, Y1, X2, Y2, 列索引1,行索引1 , 列索引2, 行索引2); 行列索引从0开始 ,行列索引指的是 图片左上角所在单元格的行列和 图片右下角所在单元格的行列 //X: 0-1024 Y:0-256 ; X1\X2相对本单元格,距离Y轴的偏移量,最大值1023;Y1\Y2相对本单元格,距离X轴的偏移量,最大值255; HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //picture.Resize(); //使图像恢复到原始大小 picBytes = null; } } catch (Exception ex) { throw new Exception(ex.Message); } }
public void TestBSEPictureRef() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet("Pictures") as HSSFSheet; HSSFPatriarch dr = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFClientAnchor anchor = new HSSFClientAnchor(); InternalSheet ish = HSSFTestHelper.GetSheetForTest(sh); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); Assert.AreEqual(1, idx1); HSSFPicture p1 = dr.CreatePicture(anchor, idx1) as HSSFPicture; EscherBSERecord bse = wb.Workbook.GetBSERecord(idx1); Assert.AreEqual(bse.Ref, 1); dr.CreatePicture(new HSSFClientAnchor(), idx1); Assert.AreEqual(bse.Ref, 2); HSSFShapeGroup gr = dr.CreateGroup(new HSSFClientAnchor()); gr.CreatePicture(new HSSFChildAnchor(), idx1); Assert.AreEqual(bse.Ref, 3); }
//导出excel public ActionResult GetExcel(int jiedian, int data, DateTime today) { string title = today.ToString("yyyy-MM-dd ") + jiedian + "号节点" + "数据统计图"; title = title + Guid.NewGuid().ToString(); IWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("智能水产数据"); string path = Server.MapPath("1.jpg"); path = path.Replace("Home\\", ""); byte[] bytes = System.IO.File.ReadAllBytes(path); int pictureIdx = hssfworkbook.AddPicture(bytes, PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 10, 20); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); string writepath = Server.MapPath("upload/" + title + ".xls"); writepath = writepath.Replace("Home\\", ""); using (FileStream write = System.IO.File.OpenWrite(writepath)) { hssfworkbook.Write(write); } return(Content("/upload/" + title + ".xls")); }
/// <summary> /// create xls /// using NPOI /// </summary> /// <param name="svgDocs"></param> /// <param name="stream"></param> public static void CreateExcelStreamBySvgs(List <SvgDocument> svgDocs, Stream stream) { using (stream) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); IDrawing patriarch = sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor; IPicture pic; IRow row = null; for (int i = 0; i < svgDocs.Count; i++) { using (MemoryStream ms = new MemoryStream()) { using (System.Drawing.Bitmap image = svgDocs[i].Draw()) { image.Save(ms, ImageFormat.Bmp); ms.Seek(0, SeekOrigin.Begin); int index = workbook.AddPicture(ms.ToArray(), PictureType.JPEG); row = sheet.CreateRow(i); row.HeightInPoints = convertPixelToPoints(image.Height) + 10; anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, i, 0, i); pic = patriarch.CreatePicture(anchor, index); pic.Resize(); } } } workbook.Write(stream); } }
/// <summary> /// 更新Excel表格 /// </summary> /// <param name="outputFile">需更新的excel表格路径</param> /// <param name="sheetname">sheet名</param> /// <param name="updateData">需更新的img数据</param> /// <param name="coluid">需更新的列号</param> /// <param name="rowid">需更新的开始行号</param> /// <param name="IsTrue">true:以横向输出; false:以横向输出</param> public static void UpdateImgToExcel(string outputFile, string sheetname, byte[] updateData, int beginCol, int beginRow, int endCol, int endRow) { try { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); int pictureIdx = hssfworkbook.AddPicture(updateData, PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch(); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释 HSSFClientAnchor anchor = new HSSFClientAnchor(30, 30, 30, 30, beginCol, beginRow, endCol, endRow); //把图片插到相应的位置 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); } }
/// <summary> /// 导出按钮监听 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void btn_ExTWO_Click(object sender, EventArgs e) { SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = @"Excel|*.xls"; sfd.ShowDialog(); string path = sfd.FileName; PlotModel pm = plotView1.Model; var pngExporter = new PngExporter { Width = 1000, Height = 400, Background = OxyColors.White }; Bitmap bitmap = pngExporter.ExportToBitmap(pm); Image image = bitmap; MemoryStream ms = new MemoryStream(); image.Save(ms, System.Drawing.Imaging.ImageFormat.Gif); byte[] bytes = ms.ToArray(); IWorkbook workbook = new HSSFWorkbook(); workbook.CreateSheet("sheet1"); if (path != "") { using (FileStream fs = File.Create(path)) { ISheet sheet = workbook.GetSheetAt(0); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 5, 6, 10); int pic = workbook.AddPicture(bytes, PictureType.PNG); IDrawing patriarch = sheet.CreateDrawingPatriarch(); IPicture ipic = patriarch.CreatePicture(anchor, pic); ipic.Resize(); workbook.Write(fs); } MessageBox.Show("保存成功!"); } }
public static int LoadImage(string path, HSSFWorkbook wb) { FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read); byte[] buffer = new byte[file.Length]; file.Read(buffer, 0, (int)file.Length); return(wb.AddPicture(buffer, NPOI.SS.UserModel.PictureType.JPEG)); }
private void ExportDeliveryInfo(IDictionary <string, List <Order> > receiversByAccounts, Stream stm) { HSSFWorkbook book = new HSSFWorkbook(); var topBorderStyle = book.CreateCellStyle(); topBorderStyle.BorderTop = BorderStyle.DashDot; var noteBorderStyle = book.CreateCellStyle(); noteBorderStyle.BorderTop = BorderStyle.DashDot; noteBorderStyle.TopBorderColor = HSSFColor.Grey25Percent.Index; noteBorderStyle.BorderBottom = BorderStyle.DashDot; foreach (var ra in receiversByAccounts) { var sheet = book.CreateSheet(ra.Key); sheet.SetColumnWidth(0, 10 * 256); sheet.SetColumnWidth(1, 100 * 256); var row = 0; foreach (var o in ra.Value) { var r = o.Receiver; { var bytes = this.GetBarcodeBytes(r.OrderNO); int pictureIdx = book.AddPicture(bytes, NPOI.SS.UserModel.PictureType.PNG); var patriarch = sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(250, 0, 500, 100, 1, row + 3, 1, row + 5); var pict = patriarch.CreatePicture(anchor, pictureIdx); //pict.Resize(); } var tmpNo = string.Format("{0} {1} {2} {3}", o.AccountOf.QuickCode, Regex.Replace(r.OrderNO, @"(.{4})", "$1 "), r.Country.ZhName, r.CountryCode); this.WriteField(sheet, ref row, "NO.", tmpNo, topBorderStyle); this.WriteField(sheet, ref row, "Name", r.Name); //var addr = string.Join(", ", (new string[] { r.Address, r.City, r.Province, r.Country }).Where(s => !string.IsNullOrEmpty(s))); //var addr = string.Join(" ", r.FullAddress, r.Country.ZhName); this.WriteField(sheet, ref row, "Address", r.FullAddress); this.WriteField(sheet, ref row, "Postcode", r.ZipCode); this.WriteField(sheet, ref row, "Phone", r.Mobi); this.WriteField(sheet, ref row, "Tel", r.Phone); this.WriteField(sheet, ref row, "Note", string.Format("[{0}] {1}", r.OrderNO, o.Note != null ? o.Note.Note : ""), noteBorderStyle); row += 2; } } this.ExportOrderList( receiversByAccounts.Values .SelectMany(o => o) .Select(o => o.OrderNO).ToList(), book.CreateSheet("本次导出的订单号码列表")); book.Write(stm); }
public static int LoadImage(string path, HSSFWorkbook wb) { //FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read); byte[] buffer = GetBytesFromUrl(path); return(wb.AddPicture(buffer, PictureType.JPEG)); }
/// <summary> /// create xls with table /// using NPOI /// </summary> /// <param name="svgDoc"></param> /// <param name="stream"></param> /// <param name="table"></param> public static void CreateExcelWithTableStreamBySvg(SvgDocument svgDoc, Stream stream, FileTable table) { using (stream) { IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); IDrawing patriarch = sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor; IPicture pic; ICellStyle head_style = createHeadCellStyle(workbook); ICellStyle cell_style = createCellBorderStyle(workbook); IRow head_row = null; head_row = sheet.CreateRow(0); ICell blank = head_row.CreateCell(0); blank.CellStyle = head_style; for (int i = 0; i < table.series.Count; i++) { ICell head_cell = head_row.CreateCell(i + 1); head_cell.SetCellValue(table.series[i]); head_cell.CellStyle = head_style; } // create vertical yAxis int _ycount = 1; foreach (string key in table.xAxis.Keys) { IRow y_row = sheet.CreateRow(_ycount); ICell cell = y_row.CreateCell(0); cell.SetCellValue(table.xAxis[key]); cell.CellStyle = head_style; for (int i = 0; i < table.series.Count; i++) { ICell value_cell = y_row.CreateCell(i + 1); value_cell.SetCellValue(table.rows[i][key]); value_cell.CellStyle = cell_style; } _ycount++; } using (MemoryStream ms = new MemoryStream()) { using (System.Drawing.Bitmap image = svgDoc.Draw()) { image.Save(ms, ImageFormat.Bmp); ms.Seek(0, SeekOrigin.Begin); int index = workbook.AddPicture(ms.ToArray(), PictureType.JPEG); anchor = new HSSFClientAnchor(0, 0, 0, 0, table.series.Count + 2, 0, 100, 100); pic = patriarch.CreatePicture(anchor, index); pic.Resize(); } } workbook.Write(stream); } }
public void Test45829() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet sh1 = wb.CreateSheet(); IDrawing p1 = sh1.CreateDrawingPatriarch(); byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("45829.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); IPicture pic = p1.CreatePicture(new HSSFClientAnchor(), idx1); pic.Resize(); }
private void SetPic(HSSFWorkbook workbook, HSSFPatriarch patriarch, string path, ISheet sheet, IMGOffet mGOffet) { 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) 后面再作解释 HSSFClientAnchor anchor = new HSSFClientAnchor(mGOffet.StartOffsetX, mGOffet.StartOffsetY, mGOffet.EndOffsetX, mGOffet.EndOffsetY, mGOffet.StartCellCol, mGOffet.StartCellRow, mGOffet.EndCellCol, mGOffet.EndCellRow); //把图片插到相应的位置 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); }
public void TestAddToExistingFile() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet() as HSSFSheet; HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch; int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.PNG); HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; comment.Column = (5); comment.String = new HSSFRichTextString("comment1"); comment = patriarch.CreateCellComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)10, 10)) as HSSFComment; comment.Row = (5); comment.String = new HSSFRichTextString("comment2"); comment.SetBackgroundImage(idx); Assert.AreEqual(comment.GetBackgroundImageId(), idx); Assert.AreEqual(patriarch.Children.Count, 2); HSSFWorkbook wbBack = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = wbBack.GetSheetAt(0) as HSSFSheet; patriarch = sh.DrawingPatriarch as HSSFPatriarch; comment = (HSSFComment)patriarch.Children[(1)]; Assert.AreEqual(comment.GetBackgroundImageId(), idx); comment.ResetBackgroundImage(); Assert.AreEqual(comment.GetBackgroundImageId(), 0); Assert.AreEqual(patriarch.Children.Count, 2); comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment; comment.String = new HSSFRichTextString("comment3"); Assert.AreEqual(patriarch.Children.Count, 3); HSSFWorkbook wbBack2 = HSSFTestDataSamples.WriteOutAndReadBack(wbBack); sh = wbBack2.GetSheetAt(0) as HSSFSheet; patriarch = sh.DrawingPatriarch as HSSFPatriarch; comment = (HSSFComment)patriarch.Children[1]; Assert.AreEqual(comment.GetBackgroundImageId(), 0); Assert.AreEqual(patriarch.Children.Count, 3); Assert.AreEqual(((HSSFComment)patriarch.Children[0]).String.String, "comment1"); Assert.AreEqual(((HSSFComment)patriarch.Children[1]).String.String, "comment2"); Assert.AreEqual(((HSSFComment)patriarch.Children[2]).String.String, "comment3"); wb.Close(); wbBack.Close(); wbBack2.Close(); }
/// <summary> /// 将DataTable转换成流 /// </summary> public static Stream RenderDataTableToExcelAndImg(DataTable sourceTable, string path) { HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); ISheet sheet = workbook.CreateSheet("Sheet1"); HSSFRow hssfRow = (HSSFRow)sheet.CreateRow(1); HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle(); cellStyle.Alignment = HorizontalAlignment.Center; hssfRow.HeightInPoints = 120; HSSFFont cellfont = (HSSFFont)workbook.CreateFont(); cellfont.FontHeightInPoints = 10; cellStyle.SetFont(cellfont); byte[] picByte = File.ReadAllBytes(path); workbook.AddPicture(picByte, PictureType.PNG); IRow headerRow = sheet.CreateRow(0); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); //picture.Resize(); int rowIndex = 1; foreach (DataRow row in sourceTable.Rows) { IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { if (column.Ordinal == 0) { HSSFClientAnchor anchor = new HSSFClientAnchor(0, 10, 1023, 0, 2 + (column.Ordinal), 0, 2, 1 + (column.Ordinal)); HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, 1); dataRow.CreateCell(column.Ordinal).CellStyle = cellStyle; } else { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } } rowIndex++; } workbook.Write(ms); ms.Flush(); ms.Position = 0; return(ms); }
public static int LoadImageExcel(string path, HSSFWorkbook workbook) { try { FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read); byte[] buffer = new byte[file.Length]; file.Read(buffer, 0, Convert.ToInt32(file.Length)); return(workbook.AddPicture(buffer, PictureType.PNG)); } catch (Exception ex) { throw ex; } }
/// <summary> /// 下载图片加载到execl /// </summary> /// <param name="sheet"></param> /// <param name="workbook"></param> /// <param name="fileurl"></param> /// <param name="row"></param> /// <param name="col"></param> private static void AddPieChart(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col) { try { var uploadFilePath = AppDomain.CurrentDomain.BaseDirectory + "ExportImage"; //判断文件夹是否存在 if (!Directory.Exists(uploadFilePath)) { Directory.CreateDirectory(uploadFilePath); } string imgFileName = uploadFilePath + Path.DirectorySeparatorChar + Guid.NewGuid().ToString() + "jpg"; HttpWebRequest request = (HttpWebRequest)WebRequest.Create(fileurl); HttpWebResponse response = request.GetResponse() as HttpWebResponse; Stream responseStream = response.GetResponseStream(); Stream stream = new FileStream(imgFileName, FileMode.Create); byte[] bArr = new byte[1024]; int size = responseStream.Read(bArr, 0, bArr.Length); while (size > 0) { stream.Write(bArr, 0, size); size = responseStream.Read(bArr, 0, bArr.Length); } stream.Close(); responseStream.Close(); byte[] bytes = System.IO.File.ReadAllBytes(imgFileName); if (!string.IsNullOrEmpty(fileurl)) { int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1); //##处理照片位置,【图片左上角为(col, row)第row+1行col+1列,右下角为( col +1, row +1)第 col +1+1行row +1+1列,宽为100,高为50 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } File.Delete(imgFileName);//直接删除文件 } catch (Exception ex) { LogHelper.logError("AddPieChart异常:" + ex.Message + "____" + ex.ToString()); } }
private void genExcel(Bitmap bitmap, int index) { int column_index = (index / 4) * 3; int rowline = index % 4; IRow row = sheet.CreateRow(column_index); //设置行高 ,excel行高度每个像素点是1/20 row.Height = 60 * 20; byte[] bytes = Bitmap2Byte(bitmap); int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); // 插图片的位置 HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释 HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, rowline, column_index, rowline + 1, column_index + 1); //把图片插到相应的位置 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); }
/// <summary> /// 向sheet插入图片 /// </summary> /// <param name="sheet"></param> /// <param name="workbook"></param> /// <param name="fileurl"></param> /// <param name="row"></param> /// <param name="col"></param> private static void AddPieChart(HSSFSheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col) { try { byte[] bytes = System.IO.File.ReadAllBytes(fileurl); if (!string.IsNullOrEmpty(fileurl)) { int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 50, 25, col, row, col + 1, row + 1); //##处理照片位置,【图片左上角为(col, row)第row+1行col+1列,右下角为( col +1, row +1)第 col +1+1行row +1+1列,宽为100,高为50 HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); // pict.Resize();这句话一定不要,这是用图片原始大小来显示 } } catch (Exception ex) { throw ex; } }
/// <summary> /// 向sheet插入图片 /// </summary> /// <param name="sheet"></param> /// <param name="workbook"></param> /// <param name="fileurl"></param> /// <param name="row"></param> /// <param name="col"></param> private async Task AddCellPicture(ISheet sheet, HSSFWorkbook workbook, string fileurl, int row, int col) { try { var imageStream = await _fileUploader.GetFileStreamAsync(fileurl); if (imageStream != null && imageStream.Length > 0) { byte[] bytes = new byte[imageStream.Length]; await imageStream.ReadAsync(bytes); int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1); HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); } } catch (Exception ex) { throw ex; } }
public void TestResize() { HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sh1 = wb.CreateSheet(); Drawing p1 = sh1.CreateDrawingPatriarch(); byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png"); int idx1 = wb.AddPicture(pictureData, PictureType.PNG); Picture picture1 = p1.CreatePicture(new HSSFClientAnchor(), idx1); ClientAnchor anchor1 = picture1.GetPreferredSize(); //assert against what would BiffViewer print if we insert the image in xls and dump the file Assert.AreEqual(0, anchor1.Col1); Assert.AreEqual(0, anchor1.Row1); Assert.AreEqual(1, anchor1.Col2); Assert.AreEqual(9, anchor1.Row2); Assert.AreEqual(0, anchor1.Dx1); Assert.AreEqual(0, anchor1.Dy1); Assert.AreEqual(848, anchor1.Dx2); Assert.AreEqual(240, anchor1.Dy2); }
private void ExportImgToExcel(ISheet sheet, HSSFWorkbook xssfworkbook) { try { //Assembly _assembly = Assembly.GetExecutingAssembly(); //string[] resNames = _assembly.GetManifestResourceNames(); Image imgOutput = Bitmap.FromFile(Path.Combine(Directory.GetCurrentDirectory(), @"Image\excelLogo.jpg")); //Image imgOutput = System.Drawing.Bitmap.FromStream() Image img = imgOutput.GetThumbnailImage(160, 115, null, IntPtr.Zero); //图片转换为文件流 MemoryStream ms = new MemoryStream(); img.Save(ms, ImageFormat.Bmp); BinaryReader br = new BinaryReader(ms); var picBytes = ms.ToArray(); ms.Close(); //插入图片 if (picBytes != null && picBytes.Length > 0) { int pictureIdx = xssfworkbook.AddPicture(picBytes, PictureType.JPEG); //添加图片 //XSSFDrawing drawing = (XSSFDrawing)sheet.CreateDrawingPatriarch(); //XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, col, row, col + 1, row + 1);// 0 行0列 //XSSFPicture picture = (XSSFPicture)drawing.CreatePicture(anchor, pictureIdx); HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 1, 1);// 0 行0列 HSSFPicture picture = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx); //picture.Resize(); picBytes = null; } } catch (Exception ex) { throw new Exception(ex.Message); } }
public void TestSetGetProperties() { HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sh = wb.CreateSheet("Pictures") as HSSFSheet; HSSFPatriarch dr = sh.CreateDrawingPatriarch() as HSSFPatriarch; HSSFClientAnchor anchor = new HSSFClientAnchor(); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); HSSFPicture p1 = dr.CreatePicture(anchor, idx1) as HSSFPicture; Assert.AreEqual(p1.FileName, ""); p1.FileName = ("aaa"); Assert.AreEqual(p1.FileName, "aaa"); wb = HSSFTestDataSamples.WriteOutAndReadBack(wb); sh = wb.GetSheet("Pictures") as HSSFSheet; dr = sh.DrawingPatriarch as HSSFPatriarch; p1 = (HSSFPicture)dr.Children[0]; Assert.AreEqual(p1.FileName, "aaa"); }