C# HSSFWorkbook.CloneSheet方法代码示例

        public static void SavePoExcel(List<po> poList, List<List<PoItemContentAndState>> poitemsListList)
        {
            if (poList.Count() != poitemsListList.Count())
               {
               MessageBox.Show("Internal Error. Please send the log file to the Author");
               Logger.Error(poList.Count() + "," + poitemsListList.Count());
               return;
               }

               FileStream file;
               try
               {
               file = new FileStream(@"PoTemplate.dll", FileMode.Open, FileAccess.Read);
               }
               catch (Exception)
               {
               MessageBox.Show("Please check the PoTemplate.dll.");
               return;
               }
               hssfworkbook = new HSSFWorkbook(file);
               WriteDsiInfo();

               for (int i = 0; i < poList.Count(); i++)
               {
               ISheet sheet = hssfworkbook.CloneSheet(0);
               FillThePoSheet(sheet, poList[i], poitemsListList[i]);
               hssfworkbook.SetSheetName(3 + i, "PO" + i.ToString());
               }
               hssfworkbook.RemoveSheetAt(0);
               hssfworkbook.RemoveSheetAt(0);
               hssfworkbook.RemoveSheetAt(0);
               WriteToFile();
        }

 public void TestCloneSheetBasic()
 {
     HSSFWorkbook b = new HSSFWorkbook();
     ISheet s = b.CreateSheet("Test");
     s.AddMergedRegion(new CellRangeAddress(0, 1, 0, 1));
     ISheet clonedSheet = b.CloneSheet(0);
     Assert.AreEqual(1, clonedSheet.NumMergedRegions, "One merged area");
 }

        public void TestPageBreakClones()
        {
            HSSFWorkbook b = new HSSFWorkbook();
            ISheet s = b.CreateSheet("Test");
            s.SetRowBreak(3);
            s.SetColumnBreak((short)6);

            ISheet clone = b.CloneSheet(0);
            Assert.IsTrue(clone.IsRowBroken(3), "Row 3 not broken");
            Assert.IsTrue(clone.IsColumnBroken((short)6), "Column 6 not broken");

            s.RemoveRowBreak(3);

            Assert.IsTrue(clone.IsRowBroken(3), "Row 3 still should be broken");
        }

        public void TestSheetClone()
        {
            // First up, try a simple file
            HSSFWorkbook b = new HSSFWorkbook();
            Assert.AreEqual(0, b.NumberOfSheets);
            b.CreateSheet("Sheet One");
            b.CreateSheet("Sheet Two");

            Assert.AreEqual(2, b.NumberOfSheets);
            b.CloneSheet(0);
            Assert.AreEqual(3, b.NumberOfSheets);

            // Now try a problem one with drawing records in it
            b = OpenSample("SheetWithDrawing.xls");
            Assert.AreEqual(1, b.NumberOfSheets);
            b.CloneSheet(0);
            Assert.AreEqual(2, b.NumberOfSheets);
        }

        [Ignore] //this test was not found in poi 3.8beta4
        public void Test22720()
        {
            HSSFWorkbook workBook = new HSSFWorkbook();
            workBook.CreateSheet("TEST");
            ISheet template = workBook.GetSheetAt(0);

            template.AddMergedRegion(new CellRangeAddress(0, 1, 0, 2));
            template.AddMergedRegion(new CellRangeAddress(1, 2, 0, 2));

            ISheet clone = workBook.CloneSheet(0);
            int originalMerged = template.NumMergedRegions;
            Assert.AreEqual(2, originalMerged, "2 merged regions");

            //        Remove merged regions from clone
            for (int i = template.NumMergedRegions - 1; i >= 0; i--)
            {
                clone.RemoveMergedRegion(i);
            }

            Assert.AreEqual(originalMerged, template.NumMergedRegions, "Original Sheet's Merged Regions were Removed");
            //        Check if template's merged regions are OK
            if (template.NumMergedRegions > 0)
            {
                // fetch the first merged region...EXCEPTION OCCURS HERE
                template.GetMergedRegion(0);
            }
            //make sure we dont exception

        }

        [Ignore] ///not found in poi 3.8beat4
        public void Test18800()
        {
            HSSFWorkbook book = new HSSFWorkbook();
            book.CreateSheet("TEST");
            ISheet sheet = book.CloneSheet(0);
            book.SetSheetName(1, "CLONE");
            sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString("Test"));

            book = WriteOutAndReadBack(book);
            sheet = book.GetSheet("CLONE");
            IRow row = sheet.GetRow(0);
            ICell cell = row.GetCell(0);
            Assert.AreEqual("Test", cell.RichStringCellValue.String);
        }

        public void TestClone()
        {

            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet();
            String formula = "7";

            HSSFSheetConditionalFormatting sheetCF = (HSSFSheetConditionalFormatting)sheet.SheetConditionalFormatting;

            HSSFConditionalFormattingRule rule1 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(formula);
            HSSFFontFormatting fontFmt = (HSSFFontFormatting)rule1.CreateFontFormatting();
            fontFmt.SetFontStyle(true, false);

            HSSFPatternFormatting patternFmt = (HSSFPatternFormatting)rule1.CreatePatternFormatting();
            patternFmt.FillBackgroundColor = (HSSFColor.Yellow.Index);


            HSSFConditionalFormattingRule rule2 = (HSSFConditionalFormattingRule)sheetCF.CreateConditionalFormattingRule(ComparisonOperator.Between, "1", "2");
            HSSFConditionalFormattingRule[] cfRules =
            {
                rule1, rule2
            };

            short col = 1;
            CellRangeAddress[] regions = {
                new CellRangeAddress(0, 65535, col, col)
            };

            sheetCF.AddConditionalFormatting(regions, cfRules);

            try
            {
                wb.CloneSheet(0);
            }
            catch (Exception e)
            {
                if (e.Message.IndexOf("needs to define a clone method") > 0)
                {
                    throw new AssertionException("Indentified bug 45682");
                }
                throw e;
            }
            Assert.AreEqual(2, wb.NumberOfSheets);
        }

        public void Test52272()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch;

            HSSFSimpleShape s = p.CreateSimpleShape(new HSSFClientAnchor());
            s.ShapeType = (HSSFSimpleShape.OBJECT_TYPE_LINE);

            HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet;
            Assert.IsNotNull(sh2.DrawingPatriarch);
        }

        public void TestCloneComment()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch p = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFComment c = p.CreateComment(new HSSFClientAnchor(0, 0, 100, 100, (short)0, 0, (short)5, 5));
            c.Column=(1);
            c.Row=(2);
            c.String=(new HSSFRichTextString("qwertyuio"));

            HSSFSheet sh2 = wb.CloneSheet(0) as HSSFSheet;
            HSSFPatriarch p2 = sh2.DrawingPatriarch as HSSFPatriarch;
            HSSFComment c2 = (HSSFComment)p2.Children[0];

            Assert.AreEqual(c.String, c2.String);
            Assert.AreEqual(c.Row, c2.Row);
            Assert.AreEqual(c.Column, c2.Column);

            // The ShapeId is not equal? 
            // assertEquals(c.getNoteRecord().getShapeId(), c2.getNoteRecord().getShapeId());

            Assert.IsTrue(Arrays.Equals(c2.GetTextObjectRecord().Serialize(), c.GetTextObjectRecord().Serialize()));

            // ShapeId is different
            CommonObjectDataSubRecord subRecord = (CommonObjectDataSubRecord)c2.GetObjRecord().SubRecords[0];
            subRecord.ObjectId = (1025);

            Assert.IsTrue(Arrays.Equals(c2.GetObjRecord().Serialize(), c.GetObjRecord().Serialize()));

            // ShapeId is different
            c2.NoteRecord.ShapeId = (1025);

            Assert.IsTrue(Arrays.Equals(c2.NoteRecord.Serialize(), c.NoteRecord.Serialize()));


            //everything except spRecord.shapeId must be the same
            Assert.IsFalse(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));
            EscherSpRecord sp = (EscherSpRecord)c2.GetEscherContainer().GetChild(0);
            sp.ShapeId=(1025);
            Assert.IsTrue(Arrays.Equals(c2.GetEscherContainer().Serialize(), c.GetEscherContainer().Serialize()));

            wb.Close();
        }

        public new void Test35084()
        {

            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet s = wb.CreateSheet("Sheet1");
            IRow r = s.CreateRow(0);
            r.CreateCell(0).SetCellValue(1);
            r.CreateCell(1).CellFormula = ("A1*2");
            NPOI.SS.UserModel.ISheet s1 = wb.CloneSheet(0);
            r = s1.GetRow(0);
            Assert.AreEqual(r.GetCell(0).NumericCellValue, 1, 1); // sanity Check
            Assert.IsNotNull(r.GetCell(1));
            Assert.AreEqual(r.GetCell(1).CellFormula, "A1*2");
        }

        public void TestCloneWithProtect()
        {
            String passwordA = "secrect";
            int expectedHashA = -6810;
            String passwordB = "admin";
            int expectedHashB = -14556;
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet hssfSheet = (HSSFSheet)workbook.CreateSheet();
            Assert.IsFalse(hssfSheet.ObjectProtect);
            hssfSheet.ProtectSheet(passwordA);
            Assert.IsTrue(hssfSheet.ObjectProtect);
            Assert.AreEqual(expectedHashA, hssfSheet.Password);
            Assert.AreEqual(expectedHashA, hssfSheet.Sheet.ProtectionBlock.PasswordHash);
            

            // Clone the sheet, and make sure the password hash is preserved
            HSSFSheet sheet2 = (HSSFSheet)workbook.CloneSheet(0);
            Assert.IsTrue(hssfSheet.ObjectProtect);
            Assert.AreEqual(expectedHashA, sheet2.Sheet.ProtectionBlock.PasswordHash);

            // change the password on the first sheet
            hssfSheet.ProtectSheet(passwordB);
            Assert.IsTrue(hssfSheet.ObjectProtect);
            Assert.AreEqual(expectedHashB, hssfSheet.Sheet.ProtectionBlock.PasswordHash);
            Assert.AreEqual(expectedHashB, hssfSheet.Password);
            // but the cloned sheet's password should remain unchanged
            Assert.AreEqual(expectedHashA, sheet2.Password);
        }