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 TestMacPicture() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("53446.xls"); List <HSSFPictureData> lst = (List <HSSFPictureData>)wb.GetAllPictures(); Assert.AreEqual(1, lst.Count); HSSFPictureData pict = lst[(0)]; String ext = pict.SuggestFileExtension(); if (!ext.Equals("png")) { Assert.Fail("Expected a PNG."); } //try to read image data using javax.imageio.* (JDK 1.4+) byte[] data = pict.Data; using (MemoryStream ms = new MemoryStream(data)) { Image png = Image.Load(ms); Assert.IsNotNull(png); Assert.AreEqual(78, png.Width); Assert.AreEqual(76, png.Height); Assert.AreEqual((int)PictureType.PNG, pict.Format); Assert.AreEqual("image/png", pict.MimeType); } }
public void TestPictures() { HSSFWorkbook wb = HSSFTestDataSamples.OpenSampleWorkbook("SimpleWithImages.xls"); IList lst = wb.GetAllPictures(); //Assert.AreEqual(2, lst.Count); for (IEnumerator it = lst.GetEnumerator(); it.MoveNext();) { HSSFPictureData pict = (HSSFPictureData)it.Current; String ext = pict.SuggestFileExtension(); byte[] data = pict.Data; if (ext.Equals("jpeg")) { //try to read image data using javax.imageio.* (JDK 1.4+) Image jpg = Image.Load(new MemoryStream(data)); Assert.IsNotNull(jpg); Assert.AreEqual(192, jpg.Width); Assert.AreEqual(176, jpg.Height); } else if (ext.Equals("png")) { //try to read image data using javax.imageio.* (JDK 1.4+) Image png = Image.Load(new MemoryStream(data)); Assert.IsNotNull(png); Assert.AreEqual(300, png.Width); Assert.AreEqual(300, png.Height); } else { //TODO: Test code for PICT, WMF and EMF } } }
static void Main(string[] args) { try { string itemFile = @"D:\ADMIN_LUUKHO.xls"; // Opening the Excel template... FileStream fs = new FileStream(itemFile, FileMode.Open, FileAccess.Read); // Getting the complete workbook... HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true); var sheet = templateWorkbook.GetSheet("例"); var lst = templateWorkbook.GetAllPictures(); int i = 0; foreach (var item in lst) { var pic = (HSSFPictureData)item; byte[] data = pic.Data; BinaryWriter writer = new BinaryWriter(File.OpenWrite(String.Format("{0}.jpeg", i))); writer.Write(data); writer.Flush(); writer.Close(); i++; } } catch { } }
public void TestImageCopy() { HSSFWorkbook srcBook = HSSFTestDataSamples.OpenSampleWorkbook("Images.xls"); HSSFWorkbook destBook = new HSSFWorkbook(); HSSFSheet sheet1 = srcBook.GetSheetAt(0) as HSSFSheet; sheet1.CopyTo(destBook, "First Sheet", true, true); using (MemoryStream ms = new MemoryStream()) { destBook.Write(ms); ms.Position = 0; HSSFWorkbook sanityCheck = new HSSFWorkbook(ms); //Assert that only one image got copied, because only one image was used on the first page Assert.IsTrue(sanityCheck.GetAllPictures().Count == 1); } HSSFSheet sheet2 = srcBook.GetSheetAt(1) as HSSFSheet; sheet2.CopyTo(destBook, "Second Sheet", true, true); using (MemoryStream ms = new MemoryStream()) { destBook.Write(ms); ms.Position = 0; HSSFWorkbook sanityCheck = new HSSFWorkbook(ms); //2nd sheet copied, make sure we have two images now, because sheet 2 had one image Assert.IsTrue(sanityCheck.GetAllPictures().Count == 2); } }
public void TestEmbeding() { // This used to break, until bug #43116 was fixed HSSFWorkbook workbook = HSSFTestDataSamples.OpenSampleWorkbook("ole2-embedding.xls"); // Check we can get at the Escher layer still workbook.GetAllPictures(); }
public void deletePicture(int k, int i) { if (wb == null) { return; } i = i - 1; ISheet sheet = wb.GetSheetAt(k); //sheet.GetAllPictures(); System.Collections.IList pictures = wb.GetAllPictures(); int w = pictures.Count; Console.WriteLine("Count=" + w); pictures.Remove(i); //foreach (HSSFPictureData pic in pictures) //{ // pic. //} }
static void Main(string[] args) { InitializeWorkbook(); IList pictures = hssfworkbook.GetAllPictures(); int i = 0; foreach (HSSFPictureData pic in pictures) { string ext = pic.SuggestFileExtension(); if (ext.Equals("jpeg")) { Image jpg = Image.FromStream(new MemoryStream(pic.Data)); jpg.Save(string.Format("pic{0}.jpg", i++)); } else if (ext.Equals("png")) { Image png = Image.FromStream(new MemoryStream(pic.Data)); png.Save(string.Format("pic{0}.png", i++)); } } }
public void TestAddPictures() { IWorkbook wb = new HSSFWorkbook(); ISheet sh = wb.CreateSheet("Pictures"); IDrawing dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(0, ((HSSFPatriarch)dr).Children.Count); IClientAnchor anchor = wb.GetCreationHelper().CreateClientAnchor(); //register a picture byte[] data1 = new byte[] { 1, 2, 3 }; int idx1 = wb.AddPicture(data1, PictureType.JPEG); Assert.AreEqual(1, idx1); IPicture p1 = dr.CreatePicture(anchor, idx1); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)p1).PictureData.Data)); // register another one byte[] data2 = new byte[] { 4, 5, 6 }; int idx2 = wb.AddPicture(data2, PictureType.JPEG); Assert.AreEqual(2, idx2); IPicture p2 = dr.CreatePicture(anchor, idx2); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)p2).PictureData.Data)); // confirm that HSSFPatriarch.Children returns two picture shapes Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // Write, read back and verify that our pictures are there wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst2 = wb.GetAllPictures(); Assert.AreEqual(2, lst2.Count); Assert.IsTrue(Arrays.Equals(data1, (lst2[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst2[(1)] as HSSFPictureData).Data)); // confirm that the pictures are in the Sheet's Drawing sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(2, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); // add a third picture byte[] data3 = new byte[] { 7, 8, 9 }; // picture index must increment across Write-read int idx3 = wb.AddPicture(data3, PictureType.JPEG); Assert.AreEqual(3, idx3); IPicture p3 = dr.CreatePicture(anchor, idx3); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)p3).PictureData.Data)); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); // write and read again wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst3 = wb.GetAllPictures(); // all three should be there Assert.AreEqual(3, lst3.Count); Assert.IsTrue(Arrays.Equals(data1, (lst3[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst3[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst3[(2)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(3, ((HSSFPatriarch)dr).Children.Count); // forth picture byte[] data4 = new byte[] { 10, 11, 12 }; int idx4 = wb.AddPicture(data4, PictureType.JPEG); Assert.AreEqual(4, idx4); dr.CreatePicture(anchor, idx4); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); wb = HSSFTestDataSamples.WriteOutAndReadBack((HSSFWorkbook)wb); IList lst4 = wb.GetAllPictures(); Assert.AreEqual(4, lst4.Count); Assert.IsTrue(Arrays.Equals(data1, (lst4[(0)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data2, (lst4[(1)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data3, (lst4[(2)] as HSSFPictureData).Data)); Assert.IsTrue(Arrays.Equals(data4, (lst4[(3)] as HSSFPictureData).Data)); sh = wb.GetSheet("Pictures"); dr = sh.CreateDrawingPatriarch(); Assert.AreEqual(4, ((HSSFPatriarch)dr).Children.Count); Assert.IsTrue(Arrays.Equals(data1, ((HSSFPicture)((HSSFPatriarch)dr).Children[(0)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data2, ((HSSFPicture)((HSSFPatriarch)dr).Children[(1)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data3, ((HSSFPicture)((HSSFPatriarch)dr).Children[(2)]).PictureData.Data)); Assert.IsTrue(Arrays.Equals(data4, ((HSSFPicture)((HSSFPatriarch)dr).Children[(3)]).PictureData.Data)); }
//static void LoadDocumentPart(HWPFDocument hwpf,TreeNode parent,string partName,FieldsDocumentPart part) //{ // TreeNode mainNode = new TreeNode(partName); // mainNode.ImageKey = "Folder"; // mainNode.SelectedImageKey = "Folder"; // parent.Nodes.Add(mainNode); // Fields fields = hwpf.GetFields(); // foreach (Field field in fields.GetFields(part)) // { // TreeNode tn = new TreeNode("Field"); // mainNode.Nodes.Add(tn); // } //} static void HandleWorkbook(TreeNode treeNode, HSSFWorkbook hssfworkbook) { if (hssfworkbook.NumberOfSheets > 0) { treeNode.ImageKey = "Folder"; treeNode.SelectedImageKey = "Folder"; for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) { string sheettext = string.Format("Sheet {0}", i + 1); TreeNode sheetnode = treeNode.Nodes.Add(sheettext, sheettext, "Folder", "Folder"); HSSFSheet hssfsheet = ((HSSFSheet)hssfworkbook.GetSheetAt(i)); EscherAggregate ea = hssfsheet.DrawingEscherAggregate; IEnumerator iterator1 = hssfsheet.Sheet.Records.GetEnumerator(); while (iterator1.MoveNext()) { if (iterator1.Current is Record) { Record record = (Record)iterator1.Current; sheetnode.Nodes.Add(new RecordTreeNode(record)); } else if (iterator1.Current is RecordAggregate) { RecordAggregate record = (RecordAggregate)iterator1.Current; sheetnode.Nodes.Add(new RecordAggregateTreeNode(record)); } } //RecordTreeNode rtn = new DirectoryTreeNode(); if (ea != null) { foreach (EscherRecord er in ea.EscherRecords) { sheetnode.Nodes.Add(new EscherRecordTreeNode(er)); } } } } else { treeNode.ImageKey = "Binary"; } IEnumerator iterator2 = hssfworkbook.Workbook.Records.GetEnumerator(); while (iterator2.MoveNext()) { if (iterator2.Current is Record) //&& !(iterator2.Current is UnknownRecord)) { Record record = (Record)iterator2.Current; if (record is DrawingGroupRecord) { hssfworkbook.GetAllPictures(); } treeNode.Nodes.Add(new RecordTreeNode(record)); } else if (iterator2.Current is RecordBase) { RecordBase record = (RecordBase)iterator2.Current; treeNode.Nodes.Add(record.GetType().Name); } } }
/// <summary> /// 读取excel内容,填入DataTable /// </summary> /// <param name="startRowIndex">起始行,从0开始.上面的row忽略.</param> /// <param name="xslFileStream">Excel文件流</param> /// <param name="onlyCreateSchedule">只创建结构,不填充数据.</param> /// <param name="allPictures">excel文件内的所图片</param> /// <returns></returns> public DataTable CreateFromXsl(Stream xslFileStream, int startRowIndex, bool onlyCreateSchedule, out IList allPictures) { HSSFWorkbook book = new HSSFWorkbook(xslFileStream); allPictures = book.GetAllPictures(); var sheet = book.GetSheetAt(0); DataTable dt = new DataTable(); //起始行单元格内的值作为datatable的列名. var row = sheet.GetRow(startRowIndex); for (int i = 0; i < row.LastCellNum; i++) { var columnName = row.GetCell(i); //空白列导致出错 string strColName = string.Empty; if (columnName == null) { strColName = Guid.NewGuid().ToString(); } else { strColName = columnName.ToString(); } DataColumn col = new DataColumn(strColName, typeof(String)); dt.Columns.Add(col); } if (!onlyCreateSchedule) { IEnumerator rowEnumer = sheet.GetRowEnumerator(); while (rowEnumer.MoveNext()) { var currentRow = (HSSFRow)rowEnumer.Current; if (currentRow.RowNum < startRowIndex + 1) { continue; } //防止其遍历到没有数据的row if (currentRow.LastCellNum < row.Cells.Count) { // break; } //空白行判断 bool isEnd = true; foreach (var cell in currentRow.Cells) { if (!string.IsNullOrEmpty(NLibrary.StringHelper.ReplaceSpace(cell.StringCellValue))) { isEnd = false; break; } } if (isEnd) { break; } DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { var cell = currentRow.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } } return(dt); }
private List <EmCostumePhoto> ExcelToImage(string sourcePath, string savepath) { List <EmCostumePhoto> listPhotos = new List <EmCostumePhoto>(); try { using (FileStream fsReader = File.OpenRead(path)) { HSSFWorkbook wk = new HSSFWorkbook(fsReader); IList pictures = wk.GetAllPictures(); int i = 0; foreach (HSSFPictureData pic in pictures) { EmCostumePhoto cutPhoto = new EmCostumePhoto(); //if (pic.Data.Length == 19504) //跳过不需要保存的图片,其中pic.data有图片长度 // continue; string ext = pic.SuggestFileExtension();//获取扩展名 string path = string.Empty; byte[] photo = null; Image img = null; if (ext.Equals("icon") || ext.Equals("jpeg") || ext.Equals("bmp") || ext.Equals("gif") || ext.Equals("png")) { img = Image.FromStream(new MemoryStream(pic.Data)); //从pic.Data数据流创建图片 path = Path.Combine(savepath, string.Format("pic{0}." + ext, i++)); img.Save(path);//保存 photo = pic.Data; // ShowMessage(pic.Data.Length.ToString()); } img = JGNet.Core.ImageHelper.GetNewSizeImage(img, 800); MemoryStream stream = new MemoryStream(); img.Save(stream, System.Drawing.Imaging.ImageFormat.Png); photo = stream.ToArray(); Image thumbnailImage = img.GetThumbnailImage(64, 64, null, new IntPtr()); EmCostumePhoto emCostumePhoto = null; String newFileName = JGNet.Core.ImageHelper.GetNewFileName(Path.GetFileName(path)); String savePath = GlobalUtil.EmallDir + newFileName; Core.ImageHelper.Compress(img, savePath, 50); path = savePath; emCostumePhoto = new EmCostumePhoto() { Image = img, SavePath = path, Bytes = photo, // CostumeID = this.skinTextBox_ID.Text, PhotoName = newFileName }; emCostumePhoto.Image = img; // cutPhoto.Photo = photo; listPhotos.Add(emCostumePhoto);; } } } catch (Exception ex) { return(null); } return(listPhotos); }
public DataTable Read(out string errMsg) { StringBuilder sbErrMsg = new StringBuilder(); HSSFWorkbook book = new HSSFWorkbook(InputStream); if (NeedGetAllPictures) { AllPictures = book.GetAllPictures(); } var sheet = book.GetSheetAt(SheetIndex); DataTable dt = new DataTable(); //起始行单元格内的值作为datatable的列名. var row = sheet.GetRow(RowNumberBegin); for (int i = 0; i < row.LastCellNum; i++) { var columnName = row.GetCell(i); //空白列导致出错 string strColName = string.Empty; if (columnName == null) { strColName = Guid.NewGuid().ToString(); } else { strColName = columnName.ToString(); } DataColumn col = new DataColumn(strColName, typeof(String)); dt.Columns.Add(col); } if (!OnlyCreateSchemar) { IEnumerator rowEnumer = sheet.GetRowEnumerator(); while (rowEnumer.MoveNext()) { var currentRow = (HSSFRow)rowEnumer.Current; if (currentRow.RowNum < RowNumberBegin + 1) { continue; } //防止其遍历到没有数据的row if (currentRow.LastCellNum < row.Cells.Count) { // break; } //空白行判断:如果所有的cell都没有可见数据,则为空白行. bool isEnd = true; foreach (var cell in currentRow.Cells) { if (!string.IsNullOrEmpty(NLibrary.StringHelper.ReplaceSpace(cell.ToString()))) { isEnd = false; break; } } if (isEnd) { break; } DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { var cell = currentRow.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } } errMsg = sbErrMsg.ToString(); return(dt); }