C# HSSFWorkbook.GetCreationHelper方法代码示例

        public void TestEvaluate()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US"); 
            
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sh = wb.CreateSheet();
            ICell cell1 = sh.CreateRow(0).CreateCell(0);
            cell1.CellFormula = ("MROUND(10, 3)");
            ICell cell2 = sh.CreateRow(0).CreateCell(0);
            cell2.CellFormula = ("MROUND(-10, -3)");
            ICell cell3 = sh.CreateRow(0).CreateCell(0);
            cell3.CellFormula = ("MROUND(1.3, 0.2)");
            ICell cell4 = sh.CreateRow(0).CreateCell(0);
            cell4.CellFormula = ("MROUND(5, -2)");
            ICell cell5 = sh.CreateRow(0).CreateCell(0);
            cell5.CellFormula = ("MROUND(5, 0)");

            double accuracy = 1E-9;

            IFormulaEvaluator Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            Assert.AreEqual(9.0, Evaluator.Evaluate(cell1).NumberValue, accuracy, "Rounds 10 to a nearest multiple of 3 (9)");

            Assert.AreEqual(-9.0, Evaluator.Evaluate(cell2).NumberValue, accuracy, "Rounds -10 to a nearest multiple of -3 (-9)");

            Assert.AreEqual(1.4, Evaluator.Evaluate(cell3).NumberValue, accuracy, "Rounds 1.3 to a nearest multiple of 0.2 (1.4)");

            Assert.AreEqual(ErrorEval.NUM_ERROR.ErrorCode, Evaluator.Evaluate(cell4).ErrorValue, "Returns an error, because -2 and 5 have different signs (#NUM!)");

            Assert.AreEqual(0.0, Evaluator.Evaluate(cell5).NumberValue, "Returns 0 because the multiple is 0");
        }

        public void TestAvg()
        {
            IWorkbook wb = new HSSFWorkbook();

            IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();

            ISheet sh = wb.CreateSheet();
            ICell a1 = sh.CreateRow(1).CreateCell(1);
            a1.SetCellValue(1);
            ICell a2 = sh.CreateRow(2).CreateCell(1);
            a2.SetCellValue(3);
            ICell a3 = sh.CreateRow(3).CreateCell(1);
            a3.CellFormula = ("SUBTOTAL(1,B2:B3)");
            ICell a4 = sh.CreateRow(4).CreateCell(1);
            a4.SetCellValue(1);
            ICell a5 = sh.CreateRow(5).CreateCell(1);
            a5.SetCellValue(7);
            ICell a6 = sh.CreateRow(6).CreateCell(1);
            a6.CellFormula = ("SUBTOTAL(1,B2:B6)*2 + 2");
            ICell a7 = sh.CreateRow(7).CreateCell(1);
            a7.CellFormula = ("SUBTOTAL(1,B2:B7)");

            fe.EvaluateAll();

            Assert.AreEqual(2.0, a3.NumericCellValue);
            Assert.AreEqual(8.0, a6.NumericCellValue);
            Assert.AreEqual(3.0, a7.NumericCellValue);
        }

 public void TestCallFunction_invalidArgs()
 {
     HSSFWorkbook workbook = new HSSFWorkbook();
     ISheet sheet = workbook.CreateSheet("test");
     IRow row1 = sheet.CreateRow(0);
     ICell cellA1 = row1.CreateCell(0, CellType.Formula);
     cellA1.CellFormula = (/*setter*/"COUNTIFS()");
     IFormulaEvaluator Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
     CellValue Evaluate = Evaluator.Evaluate(cellA1);
     Assert.AreEqual(15, Evaluate.ErrorValue);
     cellA1.CellFormula = (/*setter*/"COUNTIFS(A1:C1)");
     Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
     Evaluate = Evaluator.Evaluate(cellA1);
     Assert.AreEqual(15, Evaluate.ErrorValue);
     cellA1.SetCellFormula("COUNTIFS(A1:C1,2,2)");
     Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
     Evaluate = Evaluator.Evaluate(cellA1);
     Assert.AreEqual(15, Evaluate.ErrorValue);
 }

        public void SetUp()
        {
            wb = HSSFTestDataSamples.OpenSampleWorkbook("TestRandBetween.xls");
            Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            ISheet sheet = wb.CreateSheet("RandBetweenSheet");
            IRow row = sheet.CreateRow(0);
            bottomValueCell = row.CreateCell(0);
            topValueCell = row.CreateCell(1);
            formulaCell = row.CreateCell(2, CellType.FORMULA);
        }

        static void Main(string[] args)
        {
            var path = @"d.xls";
            using (var fs = File.OpenRead(path))
            {
                //FunctionEval.RegisterFunction("ROUND", new ICDBRound()); не позволяет переопределить формулу
                //WorkbookEvaluator.RegisterFunction("ROUND", new ICDBRound()); не позволяет переопределить формулу

                var wb = new HSSFWorkbook(fs);

                #region 1 way
                //new CustomFunctionEval();
                #endregion

                #region 2 way
                /* можно добавлять только новые функции. round подменить не выйдет

                String[] functionNames = { "ROUND2" };
                FreeRefFunction[] functionImpls = { new ICDBUDFRound() };
                UDFFinder udfs = new DefaultUDFFinder(functionNames, functionImpls);
                UDFFinder udfToolpack = new AggregatingUDFFinder(udfs);
                wb.AddToolPack(udfToolpack);
                */
                #endregion

                #region 3 way
                //FunctionEvlEx.RegisterFunction("round", new ICDBRound());
                #endregion

                var eval = wb.GetCreationHelper().CreateFormulaEvaluator();
                var sheet = wb.GetSheetAt(0);

                var en = sheet.GetRowEnumerator();
                while (en.MoveNext())
                {
                    var row = (IRow)en.Current;
                    var orig = row.GetCell(0);
                    var charp = Math.Round(decimal.Parse(orig.ToString()), 2, MidpointRounding.AwayFromZero);
                    var formula = row.GetCell(2);
                    var npoiRound = eval.Evaluate(formula).NumberValue;
                    var excel = row.GetCell(1);

                    formula.CellFormula = formula.CellFormula.Replace("ROUND(", "ROUNDUP(");
                    eval.ClearAllCachedResultValues();
                    var npoiRoundUp = eval.Evaluate(formula).NumberValue;

                    Console.WriteLine("orig = {0}; excel = {1}; charp (MidpointRounding.AwayFromZero) = {2}; npoi(ROUND) = {3}; npoi(ROUNDUP) = {4}", orig, excel, charp, npoiRound, npoiRoundUp);
                }
            }
        }

        public void TestEvaluate()
        {
            IWorkbook wb = new HSSFWorkbook();
            ISheet sh = wb.CreateSheet();
            IRow row1 = sh.CreateRow(0);
            IRow row2 = sh.CreateRow(1);

            // Create cells
            row1.CreateCell(0, CellType.Numeric);
            row1.CreateCell(1, CellType.Numeric);
            row1.CreateCell(2, CellType.Numeric);
            row2.CreateCell(0, CellType.Numeric);
            row2.CreateCell(1, CellType.Numeric);

            // Create references
            CellReference a1 = new CellReference("A1");
            CellReference a2 = new CellReference("A2");
            CellReference b1 = new CellReference("B1");
            CellReference b2 = new CellReference("B2");
            CellReference c1 = new CellReference("C1");

            // Set values
            sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(210);
            sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(55);
            sh.GetRow(b1.Row).GetCell(b1.Col).SetCellValue(35);
            sh.GetRow(b2.Row).GetCell(b2.Col).SetCellValue(0);
            sh.GetRow(c1.Row).GetCell(c1.Col).SetCellFormula("A1/B2");

            ICell cell1 = sh.CreateRow(3).CreateCell(0);
            cell1.SetCellFormula("IFERROR(A1/B1,\"Error in calculation\")");
            ICell cell2 = sh.CreateRow(3).CreateCell(0);
            cell2.SetCellFormula("IFERROR(A2/B2,\"Error in calculation\")");
            ICell cell3 = sh.CreateRow(3).CreateCell(0);
            cell3.SetCellFormula("IFERROR(C1,\"error\")");

            double accuracy = 1E-9;

            IFormulaEvaluator evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();

            Assert.AreEqual(CellType.Numeric, evaluator.Evaluate(cell1).CellType, "Checks that the cell is numeric");
            Assert.AreEqual(6.0, evaluator.Evaluate(cell1).NumberValue, accuracy, "Divides 210 by 35 and returns 6.0");


            Assert.AreEqual(CellType.String, evaluator.Evaluate(cell2).CellType, "Checks that the cell is numeric");
            Assert.AreEqual("Error in calculation", evaluator.Evaluate(cell2).StringValue, "Rounds -10 to a nearest multiple of -3 (-9)");

            Assert.AreEqual(CellType.String, evaluator.Evaluate(cell3).CellType, "Check that C1 returns string");
            Assert.AreEqual("error", evaluator.Evaluate(cell3).StringValue, "Check that C1 returns string \"error\"");
        }

        public void Test50209()
        {
            IWorkbook wb = new HSSFWorkbook();
            ISheet sh = wb.CreateSheet();
            ICell a1 = sh.CreateRow(1).CreateCell(1);
            a1.SetCellValue(1);
            ICell a2 = sh.CreateRow(2).CreateCell(1);
            a2.CellFormula = ("SUBTOTAL(9,B2)");
            ICell a3 = sh.CreateRow(3).CreateCell(1);
            a3.CellFormula = ("SUBTOTAL(9,B2:B3)");

            IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();
            fe.EvaluateAll();
            Assert.AreEqual(1.0, a2.NumericCellValue);
            Assert.AreEqual(1.0, a3.NumericCellValue);
        }

        public void TestCallFunction()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet sheet = workbook.CreateSheet("test");
            IRow row1 = sheet.CreateRow(0);
            ICell cellA1 = row1.CreateCell(0, CellType.Formula);
            ICell cellB1 = row1.CreateCell(1, CellType.Numeric);
            ICell cellC1 = row1.CreateCell(2, CellType.Numeric);
            ICell cellD1 = row1.CreateCell(3, CellType.Numeric);
            ICell cellE1 = row1.CreateCell(4, CellType.Numeric);
            cellB1.SetCellValue(1);
            cellC1.SetCellValue(1);
            cellD1.SetCellValue(2);
            cellE1.SetCellValue(4);

            cellA1.SetCellFormula("COUNTIFS(B1:C1,1, D1:E1,2)");
            IFormulaEvaluator Evaluator = workbook.GetCreationHelper().CreateFormulaEvaluator();
            CellValue Evaluate = Evaluator.Evaluate(cellA1);
            Assert.AreEqual(1.0d, Evaluate.NumberValue);
        }

        public void TestMissingWorkbookMissingOverride()
        {
            mainWorkbook = HSSFTestDataSamples.OpenSampleWorkbook(MAIN_WORKBOOK_FILENAME);
            HSSFSheet lSheet = (HSSFSheet)mainWorkbook.GetSheetAt(0);
            HSSFCell lA1Cell = (HSSFCell)lSheet.GetRow(0).GetCell(0);
            HSSFCell lB1Cell = (HSSFCell)lSheet.GetRow(1).GetCell(0);
            HSSFCell lC1Cell = (HSSFCell)lSheet.GetRow(2).GetCell(0);

            Assert.AreEqual(CellType.FORMULA, lA1Cell.CellType);
            Assert.AreEqual(CellType.FORMULA, lB1Cell.CellType);
            Assert.AreEqual(CellType.FORMULA, lC1Cell.CellType);

            HSSFFormulaEvaluator evaluator = (HSSFFormulaEvaluator)mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
            evaluator.IgnoreMissingWorkbooks = (true);

            Assert.AreEqual(CellType.NUMERIC, evaluator.EvaluateFormulaCell(lA1Cell));
            Assert.AreEqual(CellType.STRING, evaluator.EvaluateFormulaCell(lB1Cell));
            Assert.AreEqual(CellType.BOOLEAN, evaluator.EvaluateFormulaCell(lC1Cell));

            Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d);
            Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue);
            Assert.AreEqual(true, lC1Cell.BooleanCellValue);
        }

        public void TestCount()
        {

            IWorkbook wb = new HSSFWorkbook();

            IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();

            ISheet sh = wb.CreateSheet();
            ICell a1 = sh.CreateRow(1).CreateCell(1);
            a1.SetCellValue(1);
            ICell a2 = sh.CreateRow(2).CreateCell(1);
            a2.SetCellValue(3);
            ICell a3 = sh.CreateRow(3).CreateCell(1);
            a3.CellFormula = ("SUBTOTAL(2,B2:B3)");
            ICell a4 = sh.CreateRow(4).CreateCell(1);
            a4.SetCellValue("POI");                  // A4 is string and not counted
            ICell a5 = sh.CreateRow(5).CreateCell(1); // A5 is blank and not counted

            ICell a6 = sh.CreateRow(6).CreateCell(1);
            a6.CellFormula = ("SUBTOTAL(2,B2:B6)*2 + 2");
            ICell a7 = sh.CreateRow(7).CreateCell(1);
            a7.CellFormula = ("SUBTOTAL(2,B2:B7)");

            fe.EvaluateAll();

            Assert.AreEqual(2.0, a3.NumericCellValue);
            Assert.AreEqual(6.0, a6.NumericCellValue);
            Assert.AreEqual(2.0, a7.NumericCellValue);
        }

        public void TestStdev()
        {

            IWorkbook wb = new HSSFWorkbook();

            IFormulaEvaluator fe = wb.GetCreationHelper().CreateFormulaEvaluator();

            ISheet sh = wb.CreateSheet();
            ICell a1 = sh.CreateRow(0).CreateCell(0);
            a1.SetCellValue(1);
            ICell a2 = sh.CreateRow(1).CreateCell(0);
            a2.SetCellValue(3);
            ICell a3 = sh.CreateRow(2).CreateCell(0);
            a3.CellFormula = ("SUBTOTAL(7,A1:A2)");
            ICell a4 = sh.CreateRow(3).CreateCell(0);
            a4.SetCellValue(1);
            ICell a5 = sh.CreateRow(4).CreateCell(0);
            a5.SetCellValue(7);
            ICell a6 = sh.CreateRow(5).CreateCell(0);
            a6.CellFormula = ("SUBTOTAL(7,A1:A5)*2 + 2");
            ICell a7 = sh.CreateRow(6).CreateCell(0);
            a7.CellFormula = ("SUBTOTAL(7,A1:A6)");

            fe.EvaluateAll();

            Assert.AreEqual(1.41421, a3.NumericCellValue, 0.0001);
            Assert.AreEqual(7.65685, a6.NumericCellValue, 0.0001);
            Assert.AreEqual(2.82842, a7.NumericCellValue, 0.0001);
        }

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

            byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png");
            byte[] picturePPT = POIDataSamples.GetSlideShowInstance().ReadFile("clock.jpg");
            int imgIdx = wb.AddPicture(pictureData, PictureType.PNG);
            POIFSFileSystem pptPoifs = GetSamplePPT();
            int pptIdx = wb.AddOlePackage(pptPoifs, "Sample-PPT", "sample.ppt", "sample.ppt");
            POIFSFileSystem xlsPoifs = GetSampleXLS();
            int imgPPT = wb.AddPicture(picturePPT, PictureType.JPEG);
            int xlsIdx = wb.AddOlePackage(xlsPoifs, "Sample-XLS", "sample.xls", "sample.xls");
            int txtIdx = wb.AddOlePackage(GetSampleTXT(), "Sample-TXT", "sample.txt", "sample.txt");

            int rowoffset = 5;
            int coloffset = 5;

            ICreationHelper ch = wb.GetCreationHelper();
            HSSFClientAnchor anchor = (HSSFClientAnchor)ch.CreateClientAnchor();
            anchor.SetAnchor((short)(2 + coloffset), 1 + rowoffset, 0, 0, (short)(3 + coloffset), 5 + rowoffset, 0, 0);
            anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize);

            patriarch.CreateObjectData(anchor, pptIdx, imgPPT);

            anchor = (HSSFClientAnchor)ch.CreateClientAnchor();
            anchor.SetAnchor((short)(5 + coloffset), 1 + rowoffset, 0, 0, (short)(6 + coloffset), 5 + rowoffset, 0, 0);
            anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize);

            patriarch.CreateObjectData(anchor, xlsIdx, imgIdx);

            anchor = (HSSFClientAnchor)ch.CreateClientAnchor();
            anchor.SetAnchor((short)(3 + coloffset), 10 + rowoffset, 0, 0, (short)(5 + coloffset), 11 + rowoffset, 0, 0);
            anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize);

            patriarch.CreateObjectData(anchor, txtIdx, imgIdx);

            anchor = (HSSFClientAnchor)ch.CreateClientAnchor();
            anchor.SetAnchor((short)(1 + coloffset), -2 + rowoffset, 0, 0, (short)(7 + coloffset), 14 + rowoffset, 0, 0);
            anchor.AnchorType = (/*setter*/AnchorType.DontMoveAndResize);

            HSSFSimpleShape circle = patriarch.CreateSimpleShape(anchor);
            circle.ShapeType = (/*setter*/HSSFSimpleShape.OBJECT_TYPE_OVAL);
            circle.IsNoFill = (/*setter*/true);

            if (false)
            {
                FileStream fos = new FileStream("embed.xls", FileMode.Create);
                wb.Write(fos);
                fos.Close();
            }

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb as HSSFWorkbook);

            MemoryStream bos = new MemoryStream();
            HSSFObjectData od = wb.GetAllEmbeddedObjects()[0];
            Ole10Native ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory());
            bos = new MemoryStream();
            pptPoifs.WriteFileSystem(bos);
            Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, bos.ToArray()));

            od = wb.GetAllEmbeddedObjects()[1];
            ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory());
            bos = new MemoryStream();
            xlsPoifs.WriteFileSystem(bos);
            Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, bos.ToArray()));

            od = wb.GetAllEmbeddedObjects()[2];
            ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory());
            Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, GetSampleTXT()));

        }

        public void stackoverflow23114397()
        {
            IWorkbook wb = new HSSFWorkbook();
            IDataFormat format = wb.GetCreationHelper().CreateDataFormat();

            // How close the sizing should be, given that not all
            //  systems will have quite the same fonts on them
            float fontAccuracy = 0.15f;

            // x%
            ICellStyle iPercent = wb.CreateCellStyle();
            iPercent.DataFormat = (/*setter*/format.GetFormat("0%"));
            // x.x%
            ICellStyle d1Percent = wb.CreateCellStyle();
            d1Percent.DataFormat = (/*setter*/format.GetFormat("0.0%"));
            // x.xx%
            ICellStyle d2Percent = wb.CreateCellStyle();
            d2Percent.DataFormat = (/*setter*/format.GetFormat("0.00%"));

            ISheet s = wb.CreateSheet();
            IRow r1 = s.CreateRow(0);

            for (int i = 0; i < 3; i++)
            {
                r1.CreateCell(i, CellType.Numeric).SetCellValue(0);
            }
            for (int i = 3; i < 6; i++)
            {
                r1.CreateCell(i, CellType.Numeric).SetCellValue(1);
            }
            for (int i = 6; i < 9; i++)
            {
                r1.CreateCell(i, CellType.Numeric).SetCellValue(0.12345);
            }
            for (int i = 9; i < 12; i++)
            {
                r1.CreateCell(i, CellType.Numeric).SetCellValue(1.2345);
            }
            for (int i = 0; i < 12; i += 3)
            {
                r1.GetCell(i + 0).CellStyle = (/*setter*/iPercent);
                r1.GetCell(i + 1).CellStyle = (/*setter*/d1Percent);
                r1.GetCell(i + 2).CellStyle = (/*setter*/d2Percent);
            }
            for (int i = 0; i < 12; i++)
            {
                s.AutoSizeColumn(i);
                //System.out.Println(i + " => " + s.GetColumnWidth(i));
            }

            // Check the 0(.00)% ones
            assertAlmostEquals(980, s.GetColumnWidth(0), fontAccuracy);
            assertAlmostEquals(1400, s.GetColumnWidth(1), fontAccuracy);
            assertAlmostEquals(1700, s.GetColumnWidth(2), fontAccuracy);

            // Check the 100(.00)% ones
            assertAlmostEquals(1500, s.GetColumnWidth(3), fontAccuracy);
            assertAlmostEquals(1950, s.GetColumnWidth(4), fontAccuracy);
            assertAlmostEquals(2225, s.GetColumnWidth(5), fontAccuracy);

            // Check the 12(.34)% ones
            assertAlmostEquals(1225, s.GetColumnWidth(6), fontAccuracy);
            assertAlmostEquals(1650, s.GetColumnWidth(7), fontAccuracy);
            assertAlmostEquals(1950, s.GetColumnWidth(8), fontAccuracy);

            // Check the 123(.45)% ones
            assertAlmostEquals(1500, s.GetColumnWidth(9), fontAccuracy);
            assertAlmostEquals(1950, s.GetColumnWidth(10), fontAccuracy);
            assertAlmostEquals(2225, s.GetColumnWidth(11), fontAccuracy);
        }

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