C# IWorkbook类代码示例

 public double? Calculate(IWorkbook book)
 {
     var func = _calc.Build(this.StringExpressionPart);
     var paramDict = this.Variables.ToDictionary(k => k.Key, v => GetCellValue(v.Value, book));
     this.Result = func.Invoke(paramDict);
     return this.Result;
 }

 private ICellStyle GetBaseStyle(IWorkbook workbook)
 {
     var style = workbook.CreateCellStyle();
     style.BorderBottom = style.BorderLeft = style.BorderRight = style.BorderTop = BorderStyle.Thin;
     style.FillPattern = FillPattern.SolidForeground;
     return style;
 }

 static void SetValueAndFormat(IWorkbook workbook, ICell cell, double value, short formatId)
 {
     cell.SetCellValue(value);
     ICellStyle cellStyle = workbook.CreateCellStyle();
     cellStyle.DataFormat = formatId;
     cell.CellStyle = cellStyle;
 }

        internal static ICell CreateCell(IWorkbook workbook, IRow row, int column, decimal? content, bool isCentered)
        {
            ICellStyle style = workbook.CreateCellStyle();
            ICell cell = row.CreateCell(column);
            if (content == null)
            {
                cell.SetCellValue("");
            }
            else
            {
                cell.SetCellValue(Convert.ToDouble(content.Value));
            }
            if (isCentered)
            {
                style.Alignment = HorizontalAlignment.Center;

            }
            style.BorderBottom = BorderStyle.Thin;
            style.BorderTop = BorderStyle.Thin;
            style.BorderLeft = BorderStyle.Thin;
            style.BorderRight = BorderStyle.Thin;

            cell.CellStyle = style;
            return cell;
        }

        private void FillData(IWorkbook p_wb)
        {
            ISheet sheet = p_wb.CreateSheet("sheet123");
            sheet.RowSumsBelow = (/*setter*/false);
            int i;
            for (i = 0; i < ROWS_NUMBER; i++)
            {
                IRow row = sheet.CreateRow(i);
                ICell cell = row.CreateCell(0);
                cell.SetCellValue(i + 1);
            }

            i = 1;
            while (i < ROWS_NUMBER)
            {
                int end = i + (GROUP_SIZE - 2);
                int start = i;                    // natural order
                //            int start = end - 1;                // reverse order
                while (start < end)
                {             // natural order
                    //                while (start >= i) {            // reverse order
                    sheet.GroupRow(start, end);
                    //o_groupsNumber++;
                    bool collapsed = IsCollapsed();
                    //System.out.Println("Set group " + start + "->" + end + " to " + collapsed);
                    sheet.SetRowGroupCollapsed(start, collapsed);
                    start++;                      // natural order
                    //                start--;                        // reverse order
                }
                i += GROUP_SIZE;
            }
        }

 public CheckExpression(string expression, IWorkbook workbook)
 {
     this.StringExpression = expression;
     this.Workbook = workbook;
     this.Cells = new List<Cell>();
     this.ErrorMessages = new List<string>();
 }

 public static IWorkbook WriteOutAndReadBack(IWorkbook wb)
 {
     IWorkbook result;
     try
     {
         using (MemoryStream baos = new MemoryStream(8192))
         {
             wb.Write(baos);
             using (Stream is1 = new MemoryStream(baos.ToArray()))
             {
                 if (wb is HSSFWorkbook)
                 {
                     result = new HSSFWorkbook(is1);
                 }
                 else if (wb is XSSFWorkbook)
                 {
                     result = new XSSFWorkbook(is1);
                 }
                 else
                 {
                     throw new RuntimeException("Unexpected workbook type ("
                             + wb.GetType().Name + ")");
                 }
             }
         }
     }
     catch (IOException e)
     {
         throw new RuntimeException(e);
     }
     return result;
 }

 public CellStyleCache(IWorkbook xlWorkbook)
 {
     this.xlWorkbook = xlWorkbook;
       this.cellStyles = new Dictionary<string, CellStyleWrapper>();
       this.cellStyle = xlWorkbook.CreateCellStyle();
       this.defaultCellStyle = xlWorkbook.CreateCellStyle();
 }

        public void AssertDataValidation(IWorkbook wb)
        {

            MemoryStream baos = new MemoryStream(22000);
            try
            {
                wb.Write(baos);
                baos.Close();
            }
            catch (IOException e)
            {
                throw new RuntimeException(e);
            }
            byte[] generatedContent = baos.ToArray();
#if !HIDE_UNREACHABLE_CODE
            bool isSame;
            if (false)
            {
                // TODO - add proof spreadsheet and compare
                Stream proofStream = HSSFTestDataSamples.OpenSampleFileStream("TestDataValidation.xls");
                isSame = CompareStreams(proofStream, generatedContent);
            }
            isSame = true;

            if (isSame)
            {
                return;
            }

            //File tempDir = new File(System.GetProperty("java.io.tmpdir"));
            string tempDir = Path.GetTempFileName();
            //File generatedFile = new File(tempDir, "GeneratedTestDataValidation.xls");
            try
            {
                FileStream fileOut = new FileStream(tempDir, FileMode.Create);
                fileOut.Write(generatedContent, 0, generatedContent.Length);
                fileOut.Close();
            }
            catch (IOException e)
            {
                throw new RuntimeException(e);
            }


            Console.WriteLine("This Test case has failed because the generated file differs from proof copy '"
                    ); // TODO+ proofFile.AbsolutePath + "'.");
            Console.WriteLine("The cause is usually a change to this Test, or some common spreadsheet generation code.  "
                    + "The developer has to decide whether the Changes were wanted or unwanted.");
            Console.WriteLine("If the Changes to the generated version were unwanted, "
                    + "make the fix elsewhere (do not modify this Test or the proof spreadsheet to Get the Test working).");
            Console.WriteLine("If the Changes were wanted, make sure to open the newly generated file in Excel "
                    + "and verify it manually.  The new proof file should be submitted After it is verified to be correct.");
            Console.WriteLine("");
            Console.WriteLine("One other possible (but less likely) cause of a failed Test is a problem in the "
                    + "comparison logic used here. Perhaps some extra file regions need to be ignored.");
            Console.WriteLine("The generated file has been saved to '" + tempDir + "' for manual inspection.");

            Assert.Fail("Generated file differs from proof copy.  See sysout comments for details on how to fix.");
#endif
        }

        private void Confirm(IWorkbook wb)
        {
            ISheet sheet = wb.CreateSheet("new sheet");
            cell11 = sheet.CreateRow(0).CreateCell(0);
            cell11.SetCellType(CellType.Formula);

            Confirm("PROPER(\"hi there\")", "Hi There");
            Confirm("PROPER(\"what's up\")", "What'S Up");
            Confirm("PROPER(\"I DON'T TH!NK SO!\")", "I Don'T Th!Nk So!");
            Confirm("PROPER(\"dr\u00dcb\u00f6'\u00e4 \u00e9lo\u015f|\u00eb\u00e8 \")", "Dr\u00fcb\u00f6'\u00c4 \u00c9lo\u015f|\u00cb\u00e8 ");
            Confirm("PROPER(\"hi123 the123re\")", "Hi123 The123Re");
            Confirm("PROPER(\"-\")", "-");
            Confirm("PROPER(\"!\u00a7$\")", "!\u00a7$");
            Confirm("PROPER(\"/&%\")", "/&%");

            // also test longer string
            StringBuilder builder = new StringBuilder("A");
            StringBuilder expected = new StringBuilder("A");
            for (int i = 1; i < 254; i++)
            {
                builder.Append((char)(65 + (i % 26)));
                expected.Append((char)(97 + (i % 26)));
            }
            Confirm("PROPER(\"" + builder.ToString() + "\")", expected.ToString());
        }

        /// <summary>
        /// To insert comment in active sheet
        /// </summary>
        /// <param name="workbook">Instance of IWorkbook</param>
        /// <param name="cellAddress">Address of cell</param>
        /// <param name="comment">Comment string</param>
        /// <param name="clearOldComments">True/False. True to clear old comments</param>
        internal static void AddComment(IWorkbook workbook, string cellAddress, string comment, bool clearOldComments)
        {
            SpreadsheetGear.IRange Cell;
            SpreadsheetGear.IComment CellComment;
            try
            {
                // Get a reference to a cell.
                Cell = workbook.ActiveWorksheet.Cells[cellAddress];
                // If the cell has no comment, add one.
                if (Cell.Comment != null & clearOldComments)
                {
                    Cell.ClearComments();
                }

                CellComment = Cell.AddComment(comment);

                // Turn off the default bold font.
                CellComment.Shape.TextFrame.Characters.Font.Bold = false;
                //CellComment.Visible = true;
            }
            catch (Exception ex)
            {
                //                new ApplicationException(ex.ToString());
            }
        }

 static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
 {
     sheet.SetColumnWidth(0, 6000);
     sheet.SetColumnWidth(1, 6000);
     sheet.SetColumnWidth(2, 3600);
     sheet.SetColumnWidth(3, 3600);
     sheet.SetColumnWidth(4, 2400);
     sheet.SetColumnWidth(5, 2400);
     sheet.SetColumnWidth(6, 2400);
     sheet.SetColumnWidth(7, 2400);
     sheet.SetColumnWidth(8, 2400);
     IRow row = sheet.CreateRow(0);
     ICellStyle style = wb.CreateCellStyle();
     IFont font = wb.CreateFont();
     font.Boldweight = (short)FontBoldWeight.BOLD;
     style.SetFont(font);
     WriteHeaderCell(row, 0, "Raw Long Bits A", style);
     WriteHeaderCell(row, 1, "Raw Long Bits B", style);
     WriteHeaderCell(row, 2, "Value A", style);
     WriteHeaderCell(row, 3, "Value B", style);
     WriteHeaderCell(row, 4, "Exp Cmp", style);
     WriteHeaderCell(row, 5, "LT", style);
     WriteHeaderCell(row, 6, "EQ", style);
     WriteHeaderCell(row, 7, "GT", style);
     WriteHeaderCell(row, 8, "Check", style);
 }

        private void buildWorkbook(IWorkbook wb)
        {
            ISheet sh = wb.CreateSheet();
            IRow row1 = sh.CreateRow(0);
            IRow row2 = sh.CreateRow(1);
            row3 = sh.CreateRow(2);

            row1.CreateCell(0, CellType.Numeric);
            row1.CreateCell(1, CellType.Numeric);

            row2.CreateCell(0, CellType.Numeric);
            row2.CreateCell(1, CellType.Numeric);

            row3.CreateCell(0);
            row3.CreateCell(1);

            CellReference a1 = new CellReference("A1");
            CellReference a2 = new CellReference("A2");
            CellReference b1 = new CellReference("B1");
            CellReference b2 = new CellReference("B2");

            sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(35);
            sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(0);
            sh.GetRow(b1.Row).GetCell(b1.Col).CellFormula = (/*setter*/"A1/A2");
            sh.GetRow(b2.Row).GetCell(b2.Col).CellFormula = (/*setter*/"NA()");

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

 private void Init()
 {
     #region//初始化信息
     try
     {
         using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
         {
             if (filePath.Trim().ToLower().EndsWith(".xls"))
             {
                 hssfworkbook = new HSSFWorkbook(file);
             }
             else if (filePath.Trim().ToLower().EndsWith(".xlsx"))
             {
                 hssfworkbook = new XSSFWorkbook(file);
             }
             else if (filePath.Trim().ToLower().EndsWith(".csv"))
             {
                 hssfworkbook = new XSSFWorkbook(file);
             }
            
            
         }
     }
     catch (Exception e)
     {
         throw e;
     }
     #endregion
 }

 public byte[] WriteStream()
 {
     var ms = new MemoryStream();
     _workbook.Write(ms);
     _workbook = null;
     return ms.ToArray();
 }