C# HSSFWorkbook.Close方法代码示例

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

            b.Close();
        }

 public void SetUp()
 {
     IWorkbook wb = new HSSFWorkbook();
     try
     {
         buildWorkbook(wb);
     }
     finally
     {
         wb.Close();
     }
 }

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

            b.Close();
        }

        public void TestDefaultStyles()
        {

            XSSFWorkbook wb1 = new XSSFWorkbook();

            XSSFCellStyle style1 = (XSSFCellStyle)wb1.CreateCellStyle();
            Assert.AreEqual(IndexedColors.Automatic.Index, style1.FillBackgroundColor);
            Assert.IsNull(style1.FillBackgroundColorColor);

            Assert.IsNotNull(XSSFTestDataSamples.WriteOutAndReadBack(wb1));

            //compatibility with HSSF
            HSSFWorkbook wb2 = new HSSFWorkbook();
            HSSFCellStyle style2 = (HSSFCellStyle)wb2.CreateCellStyle();
            Assert.AreEqual(style2.FillBackgroundColor, style1.FillBackgroundColor);
            Assert.AreEqual(style2.FillForegroundColor, style1.FillForegroundColor);
            Assert.AreEqual(style2.FillPattern, style1.FillPattern);

            Assert.AreEqual(style2.LeftBorderColor, style1.LeftBorderColor);
            Assert.AreEqual(style2.TopBorderColor, style1.TopBorderColor);
            Assert.AreEqual(style2.RightBorderColor, style1.RightBorderColor);
            Assert.AreEqual(style2.BottomBorderColor, style1.BottomBorderColor);

            Assert.AreEqual(style2.BorderBottom, style1.BorderBottom);
            Assert.AreEqual(style2.BorderLeft, style1.BorderLeft);
            Assert.AreEqual(style2.BorderRight, style1.BorderRight);
            Assert.AreEqual(style2.BorderTop, style1.BorderTop);

            wb2.Close();
        }

        public void TestRangeOperator()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet();
            ICell cell = sheet.CreateRow(0).CreateCell(0);

            wb.SetSheetName(0, "Sheet1");
            cell.CellFormula = ("Sheet1!B$4:Sheet1!$C1"); // explicit range ':' operator
            Assert.AreEqual("Sheet1!B$4:Sheet1!$C1", cell.CellFormula);

            cell.CellFormula = ("Sheet1!B$4:$C1"); // plain area ref
            Assert.AreEqual("Sheet1!B1:$C$4", cell.CellFormula); // note - area ref is normalised

            cell.CellFormula = ("Sheet1!$C1...B$4"); // different syntax for plain area ref
            Assert.AreEqual("Sheet1!B1:$C$4", cell.CellFormula);

            // with funny sheet name
            wb.SetSheetName(0, "A1...A2");
            cell.CellFormula = ("A1...A2!B1");
            Assert.AreEqual("A1...A2!B1", cell.CellFormula);

            wb.Close();
        }

        public void TestMultiSheetReference()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            wb.CreateSheet("Cash_Flow");
            wb.CreateSheet("Test Sheet");

            HSSFSheet sheet = wb.CreateSheet("Test") as HSSFSheet;
            HSSFRow row = sheet.CreateRow(0) as HSSFRow;
            HSSFCell cell = row.CreateCell(0) as HSSFCell;
            String formula = null;

            // References to a single cell:

            // One sheet
            cell.CellFormula = (/*setter*/"Cash_Flow!A1");
            formula = cell.CellFormula;
            Assert.AreEqual("Cash_Flow!A1", formula);

            // Then the other
            cell.CellFormula = (/*setter*/"\'Test Sheet\'!A1");
            formula = cell.CellFormula;
            Assert.AreEqual("\'Test Sheet\'!A1", formula);

            // Now both
            cell.CellFormula = (/*setter*/"Cash_Flow:\'Test Sheet\'!A1");
            formula = cell.CellFormula;
            Assert.AreEqual("Cash_Flow:\'Test Sheet\'!A1", formula);

            // References to a range (area) of cells:

            // One sheet
            cell.CellFormula = ("Cash_Flow!A1:B2");
            formula = cell.CellFormula;
            Assert.AreEqual("Cash_Flow!A1:B2", formula);

            // Then the other
            cell.CellFormula = ("\'Test Sheet\'!A1:B2");
            formula = cell.CellFormula;
            Assert.AreEqual("\'Test Sheet\'!A1:B2", formula);

            // Now both
            cell.CellFormula = ("Cash_Flow:\'Test Sheet\'!A1:B2");
            formula = cell.CellFormula;
            Assert.AreEqual("Cash_Flow:\'Test Sheet\'!A1:B2", formula);

            wb.Close();
        }

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

            HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
            HSSFRow row = sh.CreateRow(0) as HSSFRow;
            HSSFCell cell = row.CreateCell(0) as HSSFCell;
            cell.CellComment = (comment);

            CommentShape commentShape = HSSFTestModelHelper.CreateCommentShape(1025, comment);

            Assert.AreEqual(comment.GetEscherContainer().ChildRecords.Count, 5);
            Assert.AreEqual(commentShape.SpContainer.ChildRecords.Count, 5);

            //sp record
            byte[] expected = commentShape.SpContainer.GetChild(0).Serialize();
            byte[] actual = comment.GetEscherContainer().GetChild(0).Serialize();

            Assert.AreEqual(expected.Length, actual.Length);
            Assert.IsTrue(Arrays.Equals(expected, actual));

            expected = commentShape.SpContainer.GetChild(2).Serialize();
            actual = comment.GetEscherContainer().GetChild(2).Serialize();

            Assert.AreEqual(expected.Length, actual.Length);
            Assert.IsTrue(Arrays.Equals(expected, actual));

            expected = commentShape.SpContainer.GetChild(3).Serialize();
            actual = comment.GetEscherContainer().GetChild(3).Serialize();

            Assert.AreEqual(expected.Length, actual.Length);
            Assert.IsTrue(Arrays.Equals(expected, actual));

            expected = commentShape.SpContainer.GetChild(4).Serialize();
            actual = comment.GetEscherContainer().GetChild(4).Serialize();

            Assert.AreEqual(expected.Length, actual.Length);
            Assert.IsTrue(Arrays.Equals(expected, actual));

            ObjRecord obj = comment.GetObjRecord();
            ObjRecord objShape = commentShape.ObjRecord;

            expected = obj.Serialize();
            actual = objShape.Serialize();

            Assert.AreEqual(expected.Length, actual.Length);
            //assertArrayEquals(expected, actual);


            TextObjectRecord tor = comment.GetTextObjectRecord();
            TextObjectRecord torShape = commentShape.TextObjectRecord;

            expected = tor.Serialize();
            actual = torShape.Serialize();

            Assert.AreEqual(expected.Length, actual.Length);
            Assert.IsTrue(Arrays.Equals(expected, actual));

            NoteRecord note = comment.NoteRecord;
            NoteRecord noteShape = commentShape.NoteRecord;

            expected = note.Serialize();
            actual = noteShape.Serialize();

            Assert.AreEqual(expected.Length, actual.Length);
            Assert.IsTrue(Arrays.Equals(expected, actual));

            wb.Close();
        }

        public void TestNamesWithUnderscore()
        {
            HSSFWorkbook wb = new HSSFWorkbook(); //or new XSSFWorkbook();
            ISheet sheet = wb.CreateSheet("NamesWithUnderscore");

            IName nm;

            nm = wb.CreateName();
            nm.NameName = ("DA6_LEO_WBS_Number");
            nm.RefersToFormula = ("33");

            nm = wb.CreateName();
            nm.NameName = ("DA6_LEO_WBS_Name");
            nm.RefersToFormula = ("33");

            nm = wb.CreateName();
            nm.NameName = ("A1_");
            nm.RefersToFormula = ("22");

            nm = wb.CreateName();
            nm.NameName = ("_A1");
            nm.RefersToFormula = ("11");

            nm = wb.CreateName();
            nm.NameName = ("A_1");
            nm.RefersToFormula = ("44");

            nm = wb.CreateName();
            nm.NameName = ("A_1_");
            nm.RefersToFormula = ("44");

            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);

            cell.CellFormula = ("DA6_LEO_WBS_Number*2");
            Assert.AreEqual("DA6_LEO_WBS_Number*2", cell.CellFormula);

            cell.CellFormula = ("(A1_*_A1+A_1)/A_1_");
            Assert.AreEqual("(A1_*_A1+A_1)/A_1_", cell.CellFormula);

            cell.CellFormula = ("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))");
            Assert.AreEqual("INDEX(DA6_LEO_WBS_Name,MATCH($A3,DA6_LEO_WBS_Number,0))", cell.CellFormula);

            wb.Close();
        }

        public void TestNumbers()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");

            HSSFWorkbook wb = new HSSFWorkbook();

            wb.CreateSheet("Cash_Flow");

            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Test");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell((short)0);
            String formula = null;

            // starts from decimal point

            cell.CellFormula = (".1");
            formula = cell.CellFormula;
            Assert.AreEqual("0.1", formula);

            cell.CellFormula = ("+.1");
            formula = cell.CellFormula;
            Assert.AreEqual("0.1", formula);

            cell.CellFormula = ("-.1");
            formula = cell.CellFormula;
            Assert.AreEqual("-0.1", formula);

            // has exponent

            cell.CellFormula = ("10E1");
            formula = cell.CellFormula;
            Assert.AreEqual("100", formula);

            cell.CellFormula = ("10E+1");
            formula = cell.CellFormula;
            Assert.AreEqual("100", formula);

            cell.CellFormula = ("10E-1");
            formula = cell.CellFormula;
            Assert.AreEqual("1", formula);

            wb.Close();
        }

        public void TestCachedTypeChange()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Sheet1");
            HSSFCell cell = (HSSFCell)sheet.CreateRow(0).CreateCell(0);
            cell.CellFormula = ("A1");
            cell.SetCellValue("abc");
            ConfirmStringRecord(sheet, true);
            cell.SetCellValue(123);
            NPOI.HSSF.Record.Record[] recs = RecordInspector.GetRecords(sheet, 0);
            if (recs.Length == 28 && recs[23] is StringRecord)
            {
                wb.Close();
                throw new AssertionException("Identified bug - leftover StringRecord");
            }
            ConfirmStringRecord(sheet, false);

            // string to error code
            cell.SetCellValue("abc");
            ConfirmStringRecord(sheet, true);
            cell.SetCellErrorValue(FormulaError.REF.Code);
            ConfirmStringRecord(sheet, false);

            // string to boolean
            cell.SetCellValue("abc");
            ConfirmStringRecord(sheet, true);
            cell.SetCellValue(false);
            ConfirmStringRecord(sheet, false);
            wb.Close();
        }

        public void TestWorksheetReferences()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            wb.CreateSheet("NoQuotesNeeded");
            wb.CreateSheet("Quotes Needed Here &#[email protected]");

            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet("Test");
            IRow row = sheet.CreateRow(0);
            ICell cell;

            cell = row.CreateCell((short)0);
            cell.CellFormula = ("NoQuotesNeeded!A1");

            cell = row.CreateCell((short)1);
            cell.CellFormula = ("'Quotes Needed Here &#[email protected]'!A1");

            wb.Close();
        }

        /// <summary>
        /// 依据参数，选择生成SQL语言的方法。
        /// </summary>
        /// <param name="filesPath">文件完整的路径名,如D:\\test.xls</param>
        /// <param name="filesTypes">文件类型，如*.xls将会引用NPOI组件</param>
        /// <param name="sqlLangTypes">SQL语言类别，如Insert、Update、Delete、Up-Only</param>
        /// <returns></returns>
        public static void npoiPrintSQLLangTypesAndMethods(string filesPath, int filesTypes, int sqlLangTypes)
        {
            if(filesTypes == 2003)
            {
                #region    //xls文件的处理
                try
                {
                    FileStream fs = new FileStream(filesPath, FileMode.Open);

                    HSSFWorkbook HBook = new HSSFWorkbook(fs);
                    ISheet isheet = HBook.GetSheetAt(FormMain.defaultTables);

                    #region //回传当前读取的Sheet表名！
                    FormMain.selectTableName = isheet.SheetName;
                    #endregion

                    switch (sqlLangTypes)
                    {
                        case 1:
                            npoiPrintSQLLangInsertMulti(isheet);
                            break;
                        case 2:
                            npoiPrintSQLLangDelete(isheet);
                            break;
                        case 3:
                            npoiPrintSQLLangUpdate(isheet);
                            break;
                        case 4:
                            npoiPrintSQLLangUpdateOnly(isheet);
                            break;
                        case 5:
                            npoiPrintSQLLangInsertEachLineASentence(isheet);
                            break;
                        default:
                            break;

                    }

                    //释放过程中使用的资源！
                    HBook.Close();
                    fs.Close();
                    FormMain.isSqlLangCreatedSuccessful = true;
                }
                catch (Exception ex)
                {
                    FormMain.isSqlLangCreatedSuccessful = false;
                    MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
                                MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return;
                }
                #endregion
            }
            else if (filesTypes == 2007)    //*.XLSX
            {
                try
                {
                    ExcelPackage excelPackage;

                    FileInfo newFile = new FileInfo(filesPath);
                    excelPackage = new ExcelPackage(newFile);

                    ExcelWorkbook myWorkbook = excelPackage.Workbook;
                    ExcelWorksheet myWorksheet = myWorkbook.Worksheets[FormMain.defaultTables + 1];

                    #region //回传当前读取的Sheet表名！
                    FormMain.selectTableName = myWorksheet.Name;
                    #endregion

                    switch (sqlLangTypes)
                    {
                        case 1:
                            excelPackagePrintSQLLangInsertMulti(myWorksheet);
                            break;
                        case 2:
                            excelPackagePrintSQLLangDelete(myWorksheet);
                            break;
                        case 3:
                            excelPackagePrintSQLLangUpdate(myWorksheet);
                            break;
                        case 4:
                            excelPackagePrintSQLLangUpdateOnly(myWorksheet);
                            break;
                        case 5:
                            excelPackagePrintSQLLangInsertEachLineASentence(myWorksheet);
                            break;
                        default:
                            break;

                    }

                    //貌似很有必要释放内存，不然没法连续执行，不关掉程序文档打不开。
                    excelPackage.Dispose();
                    FormMain.isSqlLangCreatedSuccessful = true;
//.........这里部分代码省略.........

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

            EscherAggregate agg = HSSFTestHelper.GetEscherAggregate(patriarch);
            Assert.AreEqual(agg.TailRecords.Count, 0);

            HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
            Assert.AreEqual(agg.TailRecords.Count, 1);

            HSSFSimpleShape shape = patriarch.CreateSimpleShape(new HSSFClientAnchor());
            Assert.IsNotNull(shape);

            Assert.AreEqual(comment.GetOptRecord().EscherProperties.Count, 10);
            wb.Close();
        }

 public void TestMissingArgPtg()
 {
     HSSFWorkbook wb = new HSSFWorkbook();
     try
     {
         ICell cell = wb.CreateSheet("Sheet1").CreateRow(4).CreateCell(0);
         cell.CellFormula = ("IF(A1=\"A\",1,)");
     }
     finally
     {
         wb.Close();
     }
 }

 public void TestComplexSheetRefs()
 {
     HSSFWorkbook sb = new HSSFWorkbook();
     try
     {
         NPOI.SS.UserModel.ISheet s1 = sb.CreateSheet("Sheet a.1");
         NPOI.SS.UserModel.ISheet s2 = sb.CreateSheet("Sheet.A");
         s2.CreateRow(1).CreateCell(2).CellFormula = ("'Sheet a.1'!A1");
         s1.CreateRow(1).CreateCell(2).CellFormula = ("'Sheet.A'!A1");
         string tmpfile = TempFile.GetTempFilePath("TestComplexSheetRefs", ".xls");
         FileStream fs = new FileStream(tmpfile, FileMode.Create);
         try
         {
             sb.Write(fs);
         }
         finally
         {
             fs.Close();
         }
     }
     finally
     {
         sb.Close();
     }
 }