C# HSSFWorkbook.AddPicture方法代码示例

 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, PictureType.JPEG);
 }

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

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

 public void CopyTo(HSSFWorkbook dest, String name, Boolean copyStyle, Boolean keepFormulas)
 {
     int maxColumnNum = 0;
     HSSFSheet newSheet = (HSSFSheet)dest.CreateSheet(name);
     newSheet._sheet = Sheet.CloneSheet();
     Dictionary<short, short> paletteMap = new Dictionary<short, short>();
     if (dest.NumberOfSheets == 1)
     {
         //Replace the color palette with the palette from the source, since this is the first sheet
         dest.Workbook.CustomPalette.ClearColors();
         paletteMap = MergePalettes(Workbook as HSSFWorkbook, dest);
     }
     else if (dest != Workbook)
     {
         paletteMap = MergePalettes(Workbook as HSSFWorkbook, dest);
     }
     IDictionary<Int32, HSSFCellStyle> styleMap = (copyStyle) ? new Dictionary<Int32, HSSFCellStyle>() : null;
     for (int i = FirstRowNum; i <= LastRowNum; i++)
     {
         HSSFRow srcRow = (HSSFRow)GetRow(i);
         HSSFRow destRow = (HSSFRow)newSheet.CreateRow(i);
         if (srcRow != null)
         {
             CopyRow(this, newSheet, srcRow, destRow, styleMap, paletteMap, keepFormulas);
             if (srcRow.LastCellNum > maxColumnNum)
             {
                 maxColumnNum = srcRow.LastCellNum;
             }
         }
     }
     for (int i = 0; i < maxColumnNum; i++)
     {
         newSheet.SetColumnWidth(i, GetColumnWidth(i));
     }
     newSheet.ForceFormulaRecalculation = true;
     newSheet.PrintSetup.Landscape = PrintSetup.Landscape;
     newSheet.PrintSetup.HResolution = PrintSetup.HResolution;
     newSheet.PrintSetup.VResolution = PrintSetup.VResolution;
     newSheet.SetMargin(MarginType.LeftMargin, GetMargin(MarginType.LeftMargin));
     newSheet.SetMargin(MarginType.RightMargin, GetMargin(MarginType.RightMargin));
     newSheet.SetMargin(MarginType.TopMargin, GetMargin(MarginType.TopMargin));
     newSheet.SetMargin(MarginType.BottomMargin, GetMargin(MarginType.BottomMargin));
     newSheet.PrintSetup.HeaderMargin = PrintSetup.HeaderMargin;
     newSheet.PrintSetup.FooterMargin = PrintSetup.FooterMargin;
     newSheet.Header.Left = Header.Left;
     newSheet.Header.Center = Header.Center;
     newSheet.Header.Right = Header.Right;
     newSheet.Footer.Left = Footer.Left;
     newSheet.Footer.Center = Footer.Center;
     newSheet.Footer.Right = Footer.Right;
     newSheet.PrintSetup.Scale = PrintSetup.Scale;
     newSheet.PrintSetup.FitHeight = PrintSetup.FitHeight;
     newSheet.PrintSetup.FitWidth = PrintSetup.FitWidth;
     EscherAggregate escher = DrawingEscherAggregate;
     if (escher != null)
     {
         if (dest.Workbook.DrawingManager == null)
         {
             dest.Workbook.CreateDrawingGroup();
         }
         EscherAggregate destEscher = newSheet.DrawingEscherAggregate;
         //Note: This logic assumes that image id's go from 1 to N in the source document. It usually does
         //Note: This logic assumes that no images are shared between sheets of the source document. If they
         //are and you're copying multiple sheets, the file may be larger than expected due to duplicates.
         IEnumerable<int> usedImages = FindUsedPictures(escher.EscherRecords);
         Dictionary<int,int> remap = new Dictionary<int, int>();
         IList pics = Workbook.GetAllPictures();
         foreach (int imgId in usedImages)
         {
             if (imgId <= pics.Count)
             {
                 HSSFPictureData pic = (HSSFPictureData)pics[imgId - 1];
                 int dstIdx = dest.AddPicture(pic.Data, (PictureType)pic.Format);
                 remap.Add(imgId, dstIdx);
             }
         }
         //Apply the new image Id's the destination
         foreach (EscherRecord escherRecord in destEscher.EscherRecords)
         {
             ApplyEscherRemap(escherRecord, remap);
         }
     }
 }

        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 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 TestRemoveShapes()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;

            HSSFSimpleShape rectangle = patriarch.CreateSimpleShape(new HSSFClientAnchor());
            rectangle.ShapeType = HSSFSimpleShape.OBJECT_TYPE_RECTANGLE;

            int idx = wb.AddPicture(new byte[] { 1, 2, 3 }, PictureType.JPEG);
            patriarch.CreatePicture(new HSSFClientAnchor(), idx);

            patriarch.CreateCellComment(new HSSFClientAnchor());

            HSSFPolygon polygon = patriarch.CreatePolygon(new HSSFClientAnchor());
            polygon.SetPoints(new int[] { 1, 2 }, new int[] { 2, 3 });

            patriarch.CreateTextbox(new HSSFClientAnchor());

            HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
            group.CreateTextbox(new HSSFChildAnchor());
            group.CreatePicture(new HSSFChildAnchor(), idx);

            Assert.AreEqual(patriarch.Children.Count, 6);
            Assert.AreEqual(group.Children.Count, 2);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 12);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            Assert.AreEqual(patriarch.Children.Count, 6);

            group = (HSSFShapeGroup)patriarch.Children[5];
            group.RemoveShape(group.Children[0]);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 10);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            group = (HSSFShapeGroup)patriarch.Children[(5)];
            patriarch.RemoveShape(group);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 8);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 5);

            HSSFShape shape = patriarch.Children[0];
            patriarch.RemoveShape(shape);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 4);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 6);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 4);

            HSSFPicture picture = (HSSFPicture)patriarch.Children[0];
            patriarch.RemoveShape(picture);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 3);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sheet = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sheet.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 5);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 1);
            Assert.AreEqual(patriarch.Children.Count, 3);

            HSSFComment comment = (HSSFComment)patriarch.Children[0];
            patriarch.RemoveShape(comment);

            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).GetShapeToObjMapping().Count, 3);
            Assert.AreEqual(HSSFTestHelper.GetEscherAggregate(patriarch).TailRecords.Count, 0);
//.........这里部分代码省略.........

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

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

        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText = "统计数据", string strImageFileName = "")
        {
            IWorkbook workbook = new HSSFWorkbook();
            #region 填充图片
            if (strImageFileName != "")
            {
                ISheet sheet1 = workbook.CreateSheet("图标");
                byte[] bytes = System.IO.File.ReadAllBytes(strImageFileName);
                int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);

                // Create the drawing patriarch.  This is the top level container for all shapes.
                HSSFPatriarch patriarch = (HSSFPatriarch)sheet1.CreateDrawingPatriarch();

                //add a picture
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 0, 0, 0, 0, 20, 20);
                HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                pict.Resize();
                //rowIndex += 20;
            }
            #endregion
            if (strHeaderText == "")
            {
                strHeaderText = "统计数据";
            }
            ISheet sheet = workbook.CreateSheet(strHeaderText);


            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
                arrColWidth[item.Ordinal] = arrColWidth[item.Ordinal] > 50 ? 50 : arrColWidth[item.Ordinal];
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j] && intTemp < 50)
                    {
                        arrColWidth[j] = intTemp;
                    }
                }
            }
            int rowIndex = 0;
            foreach (DataRow row in dtSource.Rows)
            {
                #region 新建表，填充表头，填充列头，样式
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }
                    if (strHeaderText != "")
                    {
                        #region 表头及样式
                        {
                            IRow headerRow = sheet.CreateRow(0);
                            headerRow.HeightInPoints = 25;
                            headerRow.CreateCell(0).SetCellValue(strHeaderText);

                            ICellStyle headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = HorizontalAlignment.Center;
                            IFont font = workbook.CreateFont();
                            font.FontHeightInPoints = 20;
                            font.Boldweight = 700;
                            headStyle.SetFont(font);
                            headerRow.GetCell(0).CellStyle = headStyle;
                            sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                            //headerRow.Dispose();
                        }
                        #endregion
                        rowIndex = 1;
                    }
                    else
                    {
                        rowIndex = 0;
                    }


                    #region 列头及样式
                    {
                        IRow headerRow = sheet.CreateRow(rowIndex);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = HorizontalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight = 700;
                        headStyle.SetFont(font);
//.........这里部分代码省略.........

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

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.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);
            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.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");
        }

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

        public void ExportImg2Xls(System.Web.HttpResponse Response, byte[] imgBytes, string filename,int imgHeight,int imgWidth)
        {
            //byte[] bytes = System.IO.File.ReadAllBytes(@"E:\mineown\mine\TrainerEvaluate20140929\untitled.png");
            hssfworkbook=new HSSFWorkbook();
              //  File.WriteAllBytes(@"d:\11.png",imgBytes);
            int pictureIdx = hssfworkbook.AddPicture(imgBytes, PictureType.PNG);
            //create sheet
            var sheet = hssfworkbook.CreateSheet("课程满意度分布");
            // Create the drawing patriarch.  This is the top level container for all shapes.
            var patriarch = sheet.CreateDrawingPatriarch();
            //add a picture
            var anchor = new HSSFClientAnchor(0, 0,1023, 255, 0, 0,10, 18);
            var pict = patriarch.CreatePicture(anchor, pictureIdx);
            pict.Resize(1);

            Response.Clear();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}.xls",  HttpUtility.UrlPathEncode(filename)));
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            file.WriteTo(Response.OutputStream);
            Response.End();
        }

 /// <summary>
 /// 在指定位置插入图片，跨Column。ColumnIndex2需大于或等于ColumnIndex1
 /// </summary>
 public static void WirtePic(HSSFWorkbook workbook, HSSFSheet sheet, HSSFPatriarch patriarch, int columnIndex1, int columnIndex2, int rowIndex, string picPath)
 {
     if (!File.Exists(picPath))
     {
         return;
     }
     if (columnIndex2 < columnIndex1)
     {
         throw new Exception("ColumnIndex2需大于或等于ColumnIndex1");
     }
     int dx2 = 1023;
     int dy2 = 255;
     HSSFRow row = sheet.GetRow(rowIndex);
     if (row != null)
     {
         int cWidth = 0;
         for (int i = columnIndex1; i < columnIndex2; i++)
         {
             cWidth += sheet.GetColumnWidth(columnIndex1);
         }
         using (Image img = Image.FromFile(picPath))
         {
             double w = cWidth * 7.0 / 256.0;
             double h = (row.Height * 1.32 / 20.0);
             if (((double)img.Width / (double)img.Height) > (cWidth * 7.0 / 256 / (row.Height * 1.32 / 20)))
             {
                 double h1 = w * img.Height / img.Width;
                 dy2 = (int)(h1 * 255 / h);
                 if (dy2 < 0) dy2 = 0;
                 if (dy2 > 255) dy2 = 255;
             }
             else
             {
                 double w1 = h * img.Width / img.Height;
                 dx2 = (int)(w1 * 1023 / w);
                 if (dy2 < 0) dy2 = 0;
                 if (dy2 > 1023) dy2 = 1023;
             }
         }
     }
     HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, dx2, dy2, columnIndex1, rowIndex, columnIndex2, rowIndex);
     byte[] buff = File.ReadAllBytes(picPath);
     int pic = workbook.AddPicture(buff, HSSFWorkbook.PICTURE_TYPE_JPEG);
     anchor.AnchorType = 2;
     patriarch.CreatePicture(anchor, pic);
 }