C# UserModel.HSSFWorkbook類代碼示例

        public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet)
        {
            //Create a Title row
            var titleFont = workbook.CreateFont();
            titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
            titleFont.FontHeightInPoints = 11;
            titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;

            var titleStyle = workbook.CreateCellStyle();
            titleStyle.SetFont(titleFont);

            var row = sheet.CreateRow(rowCount++);
            var cell = row.CreateCell(0);
            cell.CellStyle = titleStyle;
            cell.SetCellValue("Date Range");

            row = sheet.CreateRow(rowCount++);
            cell = row.CreateCell(0);
            var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy"));
            cell.SetCellValue(value);
            row = sheet.CreateRow(rowCount++);
            cell = row.CreateCell(0);
            value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy"));
            cell.SetCellValue(value);
        }

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

            IFont f = wb.CreateFont();
            f.FontName = ("Testing");
            NPOI.SS.UserModel.ICellStyle s = wb.CreateCellStyle();
            s.SetFont(f);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            // Optimise fonts
            HSSFOptimiser.OptimiseFonts(wb);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            Assert.AreEqual(f, s.GetFont(wb));

            // Optimise styles
            HSSFOptimiser.OptimiseCellStyles(wb);

            Assert.AreEqual(5, wb.NumberOfFonts);
            Assert.AreEqual(22, wb.NumCellStyles);

            Assert.AreEqual(f, s.GetFont(wb));
        }

 public CMixExcel(Stream s)
 {
     if (IsUpperVer2003)
         _excelPackage = new ExcelPackage(s);
     else
         _HSSFWorkbook = new HSSFWorkbook(s);
 }

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

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

        	/**
	 * Makes sure that a formula referring to the named range parses properly
	 */
        private static void ConfirmParseFormula(HSSFWorkbook workbook)
        {
            Ptg[] ptgs = HSSFFormulaParser.Parse("SUM(testName)", workbook);
            Assert.IsTrue(ptgs.Length == 2, "two tokens expected, got " + ptgs.Length);
            Assert.AreEqual(typeof(NamePtg), ptgs[0].GetType());
            Assert.AreEqual(typeof(AttrPtg), ptgs[1].GetType());
        }

        public void TestGetAnchorHeightInPoints()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet = (HSSFSheet)wb.CreateSheet("Test");
            HSSFClientAnchor a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
            float p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(12.7, p, 0.001);

            sheet.CreateRow(0).HeightInPoints = (14);
            a = new HSSFClientAnchor(0, 0, 1023, 255, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(13.945, p, 0.001);

            a = new HSSFClientAnchor(0, 0, 1023, 127, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(6.945, p, 0.001);

            a = new HSSFClientAnchor(0, 126, 1023, 127, (short)0, 0, (short)0, 0);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(0.054, p, 0.001);

            a = new HSSFClientAnchor(0, 0, 1023, 0, (short)0, 0, (short)0, 1);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(14.0, p, 0.001);

            sheet.CreateRow(0).HeightInPoints = (12);
            a = new HSSFClientAnchor(0, 127, 1023, 127, (short)0, 0, (short)0, 1);
            p = a.GetAnchorHeightInPoints(sheet);
            Assert.AreEqual(12.372, p, 0.001);

        }

        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 ServiceLineExplorerExplorerExcelExporter(string serviceGroupName, MemoryStream output)
 {
     this.serviceGroupName = serviceGroupName;
     this.output = output;
     workbook = new HSSFWorkbook();
     filterLists = new Dictionary<string, Dictionary<string, bool>>();
 }

 public NpoiLib(string FileTemplate)
 {
     using (FileStream file = new FileStream(FileTemplate, FileMode.Open, FileAccess.Read))
     {
         _workbook = new HSSFWorkbook(file);
     }
 }

        /// <summary>
        /// Formats the COBie data into an Excel XLS file
        /// </summary>
        /// <param name="cobie"></param>
        public void Serialise(COBieWorkbook workbook, ICOBieValidationTemplate ValidationTemplate = null)
        {
            if (workbook == null) { throw new ArgumentNullException("COBie", "COBieXLSSerialiser.Serialise does not accept null as the COBie data parameter."); }

            if (!File.Exists(TemplateFileName))
                throw new Exception("COBie creation error. Could not locate template file " + TemplateFileName);
            // Load template file
            FileStream excelFile = File.Open(TemplateFileName, FileMode.Open, FileAccess.Read);

            XlsWorkbook = new HSSFWorkbook(excelFile, true);

            CreateFormats();

            foreach (var sheet in workbook)
            {
                WriteSheet(sheet);
            }

            UpdateInstructions();

            ReportErrors(workbook, ValidationTemplate);

            ReportRules();

            using (FileStream exportFile = File.Open(FileName, FileMode.OpenOrCreate, FileAccess.Write, FileShare.None))
            {
                XlsWorkbook.Write(exportFile);
            }
        }

    /// <summary>
    /// 加邊框
    /// </summary>
    /// <param Name="rowindex">1開始</param>
    /// <param Name="cellIndex">1開始</param>
    public void AddBorder( ISheet sheet, HSSFWorkbook workbook)
    {
        ICellStyle styel = workbook.CreateCellStyle();
        styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
        IFont font1 = workbook.CreateFont();
        font1.FontHeightInPoints = 11;

        font1.Boldweight = 600;
        font1.FontName = "宋體";
        styel.SetFont(font1);
        for (int rowindex=1;rowindex<sheet.LastRowNum+1;rowindex++)
        {
            for (int cellIndex =0; cellIndex < dcs.Count;cellIndex++ )
            {
                sheet.GetRow(rowindex).RowStyle = styel;
                ICell cell = sheet.GetRow(rowindex ).GetCell(cellIndex );

                HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle;

                Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                Style.VerticalAlignment = VerticalAlignment.Center;
                Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
                Style.DataFormat = 0;
                Style.SetFont(font1);
                cell.CellStyle = Style;
            }
         }
    }

        public HSSFCreationHelper(HSSFWorkbook wb)
        {
            workbook = wb;

            // Create the things we only ever need one of
            dataFormat = new HSSFDataFormat(workbook.Workbook);
        }

        public void TestRecord()
        {
            POIFSFileSystem fs = new POIFSFileSystem(
                    HSSFTestDataSamples.OpenSampleFileStream("WithFormattedGraphTitle.xls"));

            // Check we can Open the file via usermodel
            HSSFWorkbook hssf = new HSSFWorkbook(fs);

            // Now process it through eventusermodel, and
            //  look out for the title records
            ChartTitleFormatRecordGrabber grabber = new ChartTitleFormatRecordGrabber();
            Stream din = fs.CreateDocumentInputStream("Workbook");
            HSSFRequest req = new HSSFRequest();
            req.AddListenerForAllRecords(grabber);
            HSSFEventFactory factory = new HSSFEventFactory();
            factory.ProcessEvents(req, din);
            din.Close();

            // Should've found one
            Assert.AreEqual(1, grabber.chartTitleFormatRecords.Count);
            // And it should be of something interesting
            AlRunsRecord r =
                (AlRunsRecord)grabber.chartTitleFormatRecords[0];
            Assert.AreEqual(3, r.GetFormatCount());
        }

        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            HSSFSheet sheet = workbook.GetSheet(SheetName);

            DataTable table = new DataTable();

            HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }