C# HSSFWorkbook.CreateSheet方法代码示例

        private static HSSFWorkbook CreateWBA()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet("Sheet1");
            ISheet sheet2 = wb.CreateSheet("Sheet2");
            ISheet sheet3 = wb.CreateSheet("John's sales");

            CreateDataRow(sheet1, 0, 11, 12, 13, 14);
            CreateDataRow(sheet1, 1, 21, 22, 23, 24);
            CreateDataRow(sheet1, 2, 31, 32, 33, 34);

            CreateDataRow(sheet2, 0, 50, 55, 60, 65);
            CreateDataRow(sheet2, 1, 51, 56, 61, 66);
            CreateDataRow(sheet2, 2, 52, 57, 62, 67);

            CreateDataRow(sheet3, 0, 30, 31, 32);
            CreateDataRow(sheet3, 1, 33, 34, 35);

            IName name1 = wb.CreateName();
            name1.NameName = ("sales1");
            name1.RefersToFormula = ("Sheet1!A1:D1");

            IName name2 = wb.CreateName();
            name2.NameName = ("sales2");
            name2.RefersToFormula = ("Sheet2!B1:C3");

            IRow row = sheet1.CreateRow(3);
            row.CreateCell(0).SetCellValue("sales1");  //A4
            row.CreateCell(1).SetCellValue("sales2");  //B4

            return wb;
        }

        static void Main(string[] args)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;

            //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
            //So we insert three sheet just like what Excel does
            hssfworkbook.CreateSheet("Sheet1");
            hssfworkbook.CreateSheet("Sheet2");
            hssfworkbook.CreateSheet("Sheet3");
            hssfworkbook.CreateSheet("Sheet4");

            ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
            ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;

            //Write the stream data of workbook to the root directory
            FileStream file = new FileStream(@"test.xls", FileMode.Create);
            hssfworkbook.Write(file);
            file.Close();
        }

        static void Main(string[] args)
        {
            IWorkbook workbook = new HSSFWorkbook();

            ISheet s1=workbook.CreateSheet("Sheet1");
            //set A2
            s1.CreateRow(1).CreateCell(0).SetCellValue(-5);
            //set B2
            s1.GetRow(1).CreateCell(1).SetCellValue(1111);
            //set C2
            s1.GetRow(1).CreateCell(2).SetCellValue(7.623);
            //set A3
            s1.CreateRow(2).CreateCell(0).SetCellValue(2.2);

            //set A4=A2+A3
            s1.CreateRow(3).CreateCell(0).CellFormula = "A2+A3";
            //set D2=SUM(A2:C2);
            s1.GetRow(1).CreateCell(3).CellFormula = "SUM(A2:C2)";
            //set A5=cos(5)+sin(10)
            s1.CreateRow(4).CreateCell(0).CellFormula="cos(5)+sin(10)";


            //create another sheet
            ISheet s2 = workbook.CreateSheet("Sheet2");
            //set cross-sheet reference
            s2.CreateRow(0).CreateCell(0).CellFormula = "Sheet1!A2+Sheet1!A3";
            IFormulaEvaluator e = WorkbookFactory.CreateFormulaEvaluator(workbook);
            var cell = e.Evaluate(cell);
            WriteToFile();
        }

        public void ProcessRequest(HttpContext context)
        {
            string projectName = context.Request["pname"].Trim();

            HSSFWorkbook workbook = new HSSFWorkbook();

            HSSFSheet projectSheet = workbook.CreateSheet("项目各报销项汇总");
            HSSFSheet projectReimSummarySheet = workbook.CreateSheet("项目报销项金额汇总");
            HSSFSheet projectSummarySheet = workbook.CreateSheet("项目报销总金额汇总");

            List<ProjectCategory> queryCategoryList = new List<ProjectCategory>();

            DalOperationAboutReimItem dalReimItem = new DalOperationAboutReimItem();

            List<ReimItem> reimItemList;
            if (!string.IsNullOrWhiteSpace(projectName))
            {
                reimItemList = dalReimItem.GetReimItems(projectName.Trim(), 0, 0);
            }
            else
            {
                reimItemList = dalReimItem.GetAllDistinctReimItems();
            }

            List<ReimItem> reimItemSummaryList = dalReimItem.GetReimItemSummaryValues(projectName, 0, 0, null);

            List<ReimItem> projectReimSummaryList = dalReimItem.GetProjectReimItems(projectName, 0);

            SetProjectReimSheet(projectSheet, reimItemList);

            SetProjectReimSummarySheet(projectReimSummarySheet, reimItemSummaryList);
            SetProjectSummarySheet(projectSummarySheet, projectReimSummaryList);

            string fileName = "项目报销项汇总";

            System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(fileName + ".xls"), System.IO.FileMode.Create);
            workbook.Write(file);
            file.Dispose();

            ////插入值
            FileInfo DownloadFile = new FileInfo(context.Server.MapPath(fileName + ".xls"));

            context.Response.Clear();
            context.Response.ClearHeaders();
            context.Response.Buffer = false;
            Encoding code = Encoding.GetEncoding("gb2312");
            context.Response.ContentEncoding = Encoding.UTF8;
            context.Response.HeaderEncoding = code;//这句很重要
            context.Response.ContentType = "application/octet-stream";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            context.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
            context.Response.WriteFile(DownloadFile.FullName);

            if (File.Exists(context.Server.MapPath(fileName + ".xls")))
            {
                File.Delete(context.Server.MapPath(fileName + ".xls"));
            }
            context.Response.Flush();
        }

 public void TestSetRepeatingRowsAndColumnsBug29747()
 {
     HSSFWorkbook wb = new HSSFWorkbook();
     wb.CreateSheet();
     wb.CreateSheet();
     HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet();
     sheet2.RepeatingRows = (CellRangeAddress.ValueOf("1:2"));
     NameRecord nameRecord = wb.Workbook.GetNameRecord(0);
     Assert.AreEqual(3, nameRecord.SheetNumber);
 }

 public void TestSetRepeatingRowsAndColumns()
 {
     // Test bug 29747
     HSSFWorkbook b = new HSSFWorkbook();
     b.CreateSheet();
     b.CreateSheet();
     b.CreateSheet();
     b.SetRepeatingRowsAndColumns(2, 0, 1, -1, -1);
     NameRecord nameRecord = b.Workbook.GetNameRecord(0);
     Assert.AreEqual(3, nameRecord.SheetNumber);
 }

 static void Main(string[] args)
 {
     hssfworkbook = new HSSFWorkbook();
     ISheet sheet1 = hssfworkbook.CreateSheet("new sheet");
     ISheet sheet2 = hssfworkbook.CreateSheet("second sheet");
     //Create a split with the lower left side being the active quadrant
     sheet2.CreateSplitPane(2000, 2000, 0, 0, PanePosition.LowerLeft);
     //Write the stream data of workbook to the root directory
     FileStream file = new FileStream(@"output-Split.xls", FileMode.Create);
     hssfworkbook.Write(file);
     file.Close();
 }

        public void TestAddToExistingFile()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFShapeGroup group1 = patriarch.CreateGroup(new HSSFClientAnchor());
            HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor());

            group1.SetCoordinates(1, 2, 3, 4);
            group2.SetCoordinates(5, 6, 7, 8);

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

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

            HSSFShapeGroup group3 = patriarch.CreateGroup(new HSSFClientAnchor());
            group3.SetCoordinates(9, 10, 11, 12);

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

            Assert.AreEqual(patriarch.Children.Count, 3);
        }

        public void TestCreateCFRuleRecord()
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
            CFRuleRecord record = CFRuleRecord.Create(sheet, "7");
            TestCFRuleRecord1(record);

            // Serialize
            byte[] SerializedRecord = record.Serialize();

            // Strip header
            byte[] recordData = new byte[SerializedRecord.Length - 4];
            Array.Copy(SerializedRecord, 4, recordData, 0, recordData.Length);

            // DeSerialize
            record = new CFRuleRecord(TestcaseRecordInputStream.Create(CFRuleRecord.sid, recordData));

            // Serialize again
            byte[] output = record.Serialize();

            // Compare
            Assert.AreEqual(recordData.Length + 4, output.Length, "Output size"); //includes sid+recordlength

            for (int i = 0; i < recordData.Length; i++)
            {
                Assert.AreEqual(recordData[i], output[i + 4], "CFRuleRecord doesn't match");
            }
        }

        /**
         * Set up a calculation workbook with input cells nicely segregated on a
         * sheet called "Inputs"
         */
        private static HSSFWorkbook CreateWorkbook()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet("Inputs");
            ISheet sheet2 = wb.CreateSheet("Calculations");
            IRow row;
            row = sheet2.CreateRow(0);
            row.CreateCell(0).CellFormula = ("B1*Inputs!A1-Inputs!B1");
            row.CreateCell(1).SetCellValue(5.0); // Calculations!B1

            // some default input values
            row = sheet1.CreateRow(0);
            row.CreateCell(0).SetCellValue(2.0); // Inputs!A1
            row.CreateCell(1).SetCellValue(3.0); // Inputs!B1
            return wb;
        }

        public void TestSetGetCoordinates()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFShapeGroup group = patriarch.CreateGroup(new HSSFClientAnchor());
            Assert.AreEqual(group.X1, 0);
            Assert.AreEqual(group.Y1, 0);
            Assert.AreEqual(group.X2, 1023);
            Assert.AreEqual(group.Y2, 255);

            group.SetCoordinates(1, 2, 3, 4);

            Assert.AreEqual(group.X1, 1);
            Assert.AreEqual(group.Y1, 2);
            Assert.AreEqual(group.X2, 3);
            Assert.AreEqual(group.Y2, 4);

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

            group = (HSSFShapeGroup)patriarch.Children[(0)];
            Assert.AreEqual(group.X1, 1);
            Assert.AreEqual(group.Y1, 2);
            Assert.AreEqual(group.X2, 3);
            Assert.AreEqual(group.Y2, 4);
        }

        public void TestEvaluateFormulaWithRowBeyond32768_Bug44539()
        {

            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
            wb.SetSheetName(0, "Sheet1");

            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell((short)0);
            cell.CellFormula = ("SUM(A32769:A32770)");

            // put some values in the cells to make the evaluation more interesting
            sheet.CreateRow(32768).CreateCell((short)0).SetCellValue(31);
            sheet.CreateRow(32769).CreateCell((short)0).SetCellValue(11);

            //HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(sheet, wb);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            NPOI.SS.UserModel.CellValue result;
            try
            {
                result = fe.Evaluate(cell);
            }
            catch (Exception e)
            {
                if (e.Message.Equals("Found reference to named range \"A\", but that named range wasn't defined!"))
                {
                    Assert.Fail("Identifed bug 44539");
                }
                throw;
            }
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, result.CellType);
            Assert.AreEqual(42.0, result.NumberValue, 0.0);
        }

        public void TestEvaluateInSheetExample2()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet("Sheet1");
            IRow row = sheet.CreateRow(0);

            sheet.CreateRow(1).CreateCell(0).SetCellValue(0.08d);
            sheet.CreateRow(2).CreateCell(0).SetCellValue(-40000d);
            sheet.CreateRow(3).CreateCell(0).SetCellValue(8000d);
            sheet.CreateRow(4).CreateCell(0).SetCellValue(9200d);
            sheet.CreateRow(5).CreateCell(0).SetCellValue(10000d);
            sheet.CreateRow(6).CreateCell(0).SetCellValue(12000d);
            sheet.CreateRow(7).CreateCell(0).SetCellValue(14500d);

            ICell cell = row.CreateCell(8);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            // Enumeration
            cell.CellFormula = ("NPV(A2, A4,A5,A6,A7,A8)+A3");
            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            double res = cell.NumericCellValue;
            Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);

            // Range
            cell.CellFormula = ("NPV(A2, A4:A8)+A3");

            fe.ClearAllCachedResultValues();
            fe.EvaluateFormulaCell(cell);
            res = cell.NumericCellValue;
            Assert.AreEqual(1922.06d, Math.Round(res * 100d) / 100d);
        }

        public void TestWriteSheetSimple()  {
        string            filepath = TempFile.GetTempFilePath("TestWriteSheetSimple",
                                                    ".xls");
        FileStream out1  = new FileStream(filepath,FileMode.OpenOrCreate);
        HSSFWorkbook     wb   = new HSSFWorkbook();
        NPOI.SS.UserModel.ISheet        s    = wb.CreateSheet();
        IRow          r    = null;
        ICell         c    = null;

        for (int rownum = 0; rownum < 100; rownum++) {
            r = s.CreateRow(rownum);

            for (int cellnum = 0; cellnum < 50; cellnum += 2) {
                c = r.CreateCell(cellnum);
                c.SetCellValue(rownum * 10000 + cellnum
                               + ((( double ) rownum / 1000)
                                  + (( double ) cellnum / 10000)));
                c = r.CreateCell(cellnum + 1);
                c.SetCellValue(new HSSFRichTextString("TEST"));
            }
        }
        wb.Write(out1);
        out1.Close();
        sanityChecker.CheckHSSFWorkbook(wb);
        Assert.AreEqual(99, s.LastRowNum, "LAST ROW == 99");
        Assert.AreEqual(0, s.FirstRowNum, "FIRST ROW == 0");
    }

        public void TestClean1()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ICell cell = wb.CreateSheet().CreateRow(0).CreateCell(0);
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);

            String[] asserts = {
            "aniket\u0007\u0017\u0019", "aniket",
            "\u0011aniket\u0007\u0017\u0010", "aniket",
            "\u0011aniket\u0007\u0017\u007F", "aniket\u007F",
            "\u2116aniket\u2211\uFB5E\u2039", "\u2116aniket\u2211\uFB5E\u2039",
        };

            for (int i = 0; i < asserts.Length; i += 2)
            {
                String formulaText = "CLEAN(\"" + asserts[i] + "\")";
                ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
            }

            asserts = new String[] {
            "CHAR(7)&\"text\"&CHAR(7)", "text",
            "CHAR(7)&\"text\"&CHAR(17)", "text",
            "CHAR(181)&\"text\"&CHAR(190)", "\u00B5text\u00BE",
            "\"text\"&CHAR(160)&\"'\"", "text\u00A0'",
        };
            for (int i = 0; i < asserts.Length; i += 2)
            {
                String formulaText = "CLEAN(" + asserts[i] + ")";
                ConfirmResult(fe, cell, formulaText, asserts[i + 1]);
            }
        }