C# HSSFWorkbook.GetAllPictures方法代码示例

        public DataTable CreateFromXsl(Stream xslFileStream, bool onlyCreateSchedule, out IList allPictures)
        {
            HSSFWorkbook book = new HSSFWorkbook(xslFileStream);
            allPictures = book.GetAllPictures();

            var sheet = book.GetSheetAt(0);
            DataTable dt = new DataTable();
            var row = sheet.GetRow(1);

            foreach (var cell in row.Cells)
            {
                DataColumn col = new DataColumn(cell.ToString(), typeof(String));
              //  dt.Columns.Add(col);
            }

            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 < 2) continue;
                    //防止其遍历到没有数据的row
                    if (currentRow.LastCellNum < row.Cells.Count)
                    {
                        //    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;
        }

        public void Test53432()
        {
            IWorkbook wb = new HSSFWorkbook(); //or new HSSFWorkbook();
            wb.AddPicture(new byte[] { 123, 22 }, PictureType.JPEG);
            Assert.AreEqual(wb.GetAllPictures().Count, 1);

            wb = new HSSFWorkbook();
            wb = WriteOutAndReadBack((HSSFWorkbook)wb);
            Assert.AreEqual(wb.GetAllPictures().Count, 0);
            wb.AddPicture(new byte[] { 123, 22 }, PictureType.JPEG);
            Assert.AreEqual(wb.GetAllPictures().Count, 1);

            wb = WriteOutAndReadBack((HSSFWorkbook)wb);
            Assert.AreEqual(wb.GetAllPictures().Count, 1);
        }

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

        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 DataTable Read(out string errMsg)
        {
            StringBuilder sbErrMsg = new StringBuilder();
            HSSFWorkbook book = new HSSFWorkbook(InputStream);

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

        public void Bug49658()
        {
            // 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));
        }

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