C# HSSFWorkbook.SetSheetName方法代码示例

        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 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 Test3DArea()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet1 = wb.CreateSheet();
            wb.SetSheetName(0, "Sheet1");
            wb.CreateSheet();
            wb.SetSheetName(1, "Sheet2");
            IRow row = sheet1.CreateRow(0);
            ICell cell = row.CreateCell(0);

            cell.CellFormula=("isblank(Sheet2!A1:A1)");

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            CellValue result = fe.Evaluate(cell);
            Assert.AreEqual(CellType.BOOLEAN, result.CellType);
            Assert.AreEqual(true, result.BooleanValue);

            cell.CellFormula=("isblank(D7:D7)");

            result = fe.Evaluate(cell);
            Assert.AreEqual(CellType.BOOLEAN, result.CellType);
            Assert.AreEqual(true, result.BooleanValue);
        }

        public void TestSetFormulaWithRowBeyond32768_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)");
            if ("SUM(A-32767:A-32766)".Equals(cell.CellFormula))
            {
                Assert.Fail("Identified bug 44539");
            }
            Assert.AreEqual("SUM(A32769:A32770)", cell.CellFormula);

            wb.Close();
        }

        public void TestParseStringLiterals_bug28754()
        {

            StringPtg sp;
            try
            {
                sp = (StringPtg)ParseSingleToken("\"test\"\"ing\"", typeof(StringPtg));
            }
            catch (Exception e)
            {
                if (e.Message.StartsWith("Cannot Parse"))
                {
                    throw new AssertionException("Identified bug 28754a");
                }
                throw e;
            }
            Assert.AreEqual("test\"ing", sp.Value);

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

                IRow row = sheet.CreateRow(0);
                ICell cell = row.CreateCell((short)0);
                cell.CellFormula = ("right(\"test\"\"ing\", 3)");
                String actualCellFormula = cell.CellFormula;
                if ("RIGHT(\"test\"ing\",3)".Equals(actualCellFormula))
                {
                    throw new AssertionException("Identified bug 28754b");
                }
                Assert.AreEqual("RIGHT(\"test\"\"ing\",3)", actualCellFormula);
            }
            finally
            {
                wb.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();
        }

        [Ignore]// not found in poi
        public void Test28031()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            ISheet sheet = wb.CreateSheet();
            wb.SetSheetName(0, "Sheet1");

            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);
            String formulaText =
                "IF(ROUND(A2*B2*C2,2)>ROUND(B2*D2,2),ROUND(A2*B2*C2,2),ROUND(B2*D2,2))";
            cell.CellFormula = (formulaText);

            Assert.AreEqual(formulaText, cell.CellFormula);
            WriteTestOutputFileForViewing(wb, "output28031.xls");
        }

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

//.........这里部分代码省略.........
                        int stateCode;
                        if (!int.TryParse(stateCodeStr, out stateCode))
                        {
                            throw new AnycmdException("文件" + fullName + "行中有意外的状态码");
                        }
                        if (stateCode >= 200 && stateCode < 300)
                        {
                            var cell = row.GetCell(localEntityIdIndex);
                            entityIDs.Add(cell.SafeToStringTrim());
                        }
                        if (stateCode < 200 || stateCode >= 300)
                        {
                            IRow resultRow = failedSheet.CreateRow(resultFailedRowIndex);
                            for (int j = 0; j < headRow0.Cells.Count; j++)
                            {
                                var cell = resultRow.CreateCell(j);
                                var oldCell = row.GetCell(j);
                                if (oldCell != null)
                                {
                                    cell.CellStyle = oldCell.CellStyle;
                                    cell.SetCellValue(oldCell.SafeToStringTrim());
                                }
                            }
                            resultFailedRowIndex++;
                        }
                    }
                    sheet.RemoveRow(row);
                }
                rowIndex++;
            }
            sheet.RemoveRow(headRow0);
            sheet.RemoveRow(headRow1);
            sheet.RemoveRow(headRow2);
            workbook.SetSheetName(workbook.GetSheetIndex(sheet), "Success");
            #region Success 数据
            rowIndex = 0;
            var headRow = sheet.CreateRow(rowIndex);
            sheet.CreateFreezePane(0, 1, 0, 1);
            rowIndex++;
            ICellStyle helderStyle = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 14;
            helderStyle.SetFont(font);
            helderStyle.BorderBottom = BorderStyle.Thin;
            helderStyle.BorderLeft = BorderStyle.Thin;
            helderStyle.BorderRight = BorderStyle.Thin;
            helderStyle.BorderTop = BorderStyle.Thin;
            helderStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
            helderStyle.FillPattern = FillPattern.SolidForeground;
            int cellIndex = 0;
            foreach (var element in selectElements)
            {
                ICell cell = headRow.CreateCell(cellIndex, CellType.String);
                sheet.SetColumnHidden(cellIndex, hidden: false);
                if (element.IsCodeValue)
                {
                    cell.SetCellValue(element.Element.Name + "码");
                }
                else
                {
                    cell.SetCellValue(element.Element.Name);
                }
                if (!string.IsNullOrEmpty(element.Element.Description))
                {
                    //添加批注
                    IDrawing draw = sheet.CreateDrawingPatriarch();

            /// <summary>
            /// 导出Excel
            /// </summary>
            /// <param name="ds">要导出的数据源</param>
            /// <param name="excelFilePath">文件的物理路径</param>
            /// <param name="errorFun"></param>
            /// <returns></returns>
            public static bool Output(DataSet ds, string excelFilePath, Action<Exception> errorFun = null)
            {

                if (!File.Exists(excelFilePath))
                {
                    throw new Exception("Excel 文件不存在");
                }
                if (null == ds || ds.Tables.Count == 0)
                {
                    return false;
                }
                try
                {
                    //1.0 创建工作薄 和 工作表对象
                    NPOI.HSSF.UserModel.HSSFWorkbook book;
                    using (FileStream Readfile = new FileStream(excelFilePath, FileMode.Open, FileAccess.ReadWrite))
                    {
                        book = new NPOI.HSSF.UserModel.HSSFWorkbook(Readfile);
                    }
                    int sheetIndex = 0;
                    foreach (DataTable dt in ds.Tables)
                    {
                        string sheetName = dt.TableName ?? "sheet" + sheetIndex;
                        NPOI.SS.UserModel.ISheet sheet1 = book.GetSheetAt(sheetIndex); //book.CreateSheet(string.IsNullOrEmpty(sheetName) ? dt.TableName : sheetName); //添加一个sheet表
                        if (null == sheet1)
                        {
                            sheet1 = book.CreateSheet(sheetName);
                        }
                        else if (sheet1.SheetName != sheetName)
                        {
                            book.SetSheetName(sheetIndex, sheetName);
                        }
                        //2.0给sheet1添加第一行的头部标题
                        NPOI.SS.UserModel.IRow rowHead = sheet1.CreateRow(0);//创建标题行
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            rowHead.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                        }

                        //3.0 填充表格数据
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            NPOI.SS.UserModel.IRow rowTemp = sheet1.CreateRow(i + 1);//创建数据行
                            for (int j = 0; j < dt.Columns.Count; j++)//填充行数据
                            {
                                rowTemp.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                            }
                        }
                        sheetIndex++;
                    }
                    //4.0 写入文件
                    using (FileStream wfile = new FileStream(excelFilePath, FileMode.Create))
                    {
                        book.Write(wfile);
                    }
                    return true;
                }
                catch (Exception ex)
                {
                    if (errorFun != null)
                    {
                        errorFun(ex);
                    }
                    return false;
                }
            }

    public void CreateReportExt()
    {

        LocalReport report = new LocalReport();
        string mimeType, encoding, fileExtension;
        string[] streams;
        Warning[] warnings;
        int year = Int32.Parse(Session["year"].ToString());
        report.SubreportProcessing += new SubreportProcessingEventHandler(ItemsSubreportProcessingEventHandler);

        
        string username = User.Identity.Name;
        UserInfos userinfos = UserInfos.getUserInfos(username);
        int fermaid = GetFermaId(userinfos.UserCod);
        if (fermaid != 0)
        {
          
                string[] parIds = { "g", "p", "c", "l", "s", "h", "u", "n", "z" };
                string[] chartLabelsName = { "Grasime (g/100g)", "Proteine (g/100g)", "Cazeina (g/100g)", "Lactoza (g/100g)", "SUN (g/100g)", "pH", "Uree (mg/dl)", "NCSx1000", "Cantitate lapte (l)" };
                string[] chartTitles = { "Grasime", "Proteine", "Cazeina", 
                                           "Lactoza", "SUN", "Ph",
                                           "Uree", "NCS","Cantitate lapte"};
                string[] um = { "g/100g", "g/100g", "g/100g", "g/100g", "g/100g", "pH", "mg/dl", "NCSx1000", "l" };

          
                                 //controale
                ReportDataSource rdsm = new ReportDataSource();
                 rdsm.Name = "SampleAnalizeExt";
                 List<SampleAnalizeExt> samplesext = SampleAnalize.GetSampleAnalizeExt(fermaid,year);
                  rdsm.Value = samplesext;
                  report.DataSources.Clear();  
                  report.DataSources.Add(rdsm);

                  report.ReportPath = "ControlsSummaryExt.rdlc";
                  string datatestare = "Data: " + DateTime.Now.ToString("dd/MM/yyyy");
                        // set reports parameters
                 string title = userinfos.NumeFerma + " - " + "Cod exploatatie " + userinfos.UserCod;

                        ReportParameter pCod = new ReportParameter("CodExpl", userinfos.UserCod);
                        ReportParameter pData = new ReportParameter("DataExec", datatestare);
                    //    ReportParameter pChartTitle = new ReportParameter("ChartTitle", chartTitles[i]);
                     //   ReportParameter pChartLabelsName = new ReportParameter("ChartLabelsName", chartLabelsName[i]);
                       // ReportParameter pUm = new ReportParameter("Um", um[i]);
                        ReportParameter pTitle = new ReportParameter("Title", title);
                        ReportParameter pUsername = new ReportParameter("UserName", username);
                        string baseurl = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + ConfigurationManager.AppSettings["baseurl"];
                        ReportParameter pBaseurl = new ReportParameter("BaseUrl", baseurl);
                        ReportParameter[] p = { pCod, pData,pTitle,pBaseurl,pUsername };
                        report.SetParameters(p);
                        report.Refresh();
                        // write xls
                        string httppath = ConfigurationManager.AppSettings["httppath"]+userinfos.AsocId+"/" + year +"/";
                        //"/Downloads/";
                        string filepath = ConfigurationManager.AppSettings["filepath"]+userinfos.AsocId+"\\" + year +"\\";
                        if (!Directory.Exists(filepath))
                        {
                            Directory.CreateDirectory(filepath);
                        } 

                        //"D:\\portal\\Downloads\\";
                        string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
                        string fileid = chartTitles[0];
                        string fermaname = replace_special_car_null(userinfos.NumeFerma); 
                        string raport_excel = userinfos.UserCod + "_" + fermaname +".xls";
                        string excel_file = filepath + raport_excel;
                        string excel_link = path_raport_http + raport_excel;
                        byte[] xls_content = report.Render("EXCEL", deviceInfoXls, out mimeType, out encoding, out fileExtension, out streams, out warnings);
                        File.WriteAllBytes(excel_file, xls_content);
                        //rename sheets
                        List<SampleAnalize> samples = SampleAnalize.GetSampleAnalize(fermaid, "",year);
                        using (StreamReader input = new StreamReader(excel_file))
                        {
                            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(input.BaseStream));
                            if (workbook != null && samples.Count >0)
                            {
                                for (int count=0; count < samples.Count; count++)
                                workbook.SetSheetName(count, samples[count].Nrcontrol);

                                for (int count = 0; count < chartTitles.Length; count++)
                                {
                                    string sheetname = chartTitles[count].Replace(" ", "");
                                    workbook.SetSheetName(count + samples.Count, "Grafic" + sheetname);
                                }
                                    //  workbook.SetSheetName(1, "Detaliu date");
                                WriteExcel(excel_file, workbook);
                            }

                        }

                        //
                        ContentPlaceHolder holder = (ContentPlaceHolder)Master.FindControl("MainContent");
                        HyperLink hyper = (HyperLink)holder.FindControl("xlslink" + parIds[0]);
                        hyper.Visible = true;
                        hyper.NavigateUrl = excel_link;
                        hyper.Text = raport_excel;
                        ErrorMessage.Text = "";
        }//fermaid >0
        else
        {
            // ErrorMessage.Text = "Nu exista date pentru codul "+ userinfos.UserCod;
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                                    sample.Val = sample.Solide; break;
                                case 5:
                                    sample.Val = sample.Ph; break;
                                case 6:
                                    sample.Val = sample.Urea; break;
                                case 7:
                                    sample.Val = sample.Ncs; break;
                                case 8:
                                    sample.Val = sample.Cantitate; break;
                                default: break;
                            }
                            sample.Val = Math.Round(sample.Val, 2);


                        }
                        // create samplescontrol from samples 

                        // set reports datasource
                        rds.Value = samples;
                        report.DataSources.Clear();
                        report.DataSources.Add(rds);

                        //  
                        ReportDataSource rdsc = new ReportDataSource();
                        rdsc.Name = "SampleAnalizeControl";
                        List<SampleAnalizeControl> samplescontrol = SampleAnalize.getSampleAnalizeControl(samples,year);
                        rdsc.Value = samplescontrol;
                        report.DataSources.Add(rdsc);
                        // detaliu 
                        ReportDataSource rdscrot = new ReportDataSource();
                        rdscrot.Name = "SampleAnalizeCrotalia";
                        List<SampleAnalizeCrotalia> samplescrot = SampleAnalize.getSampleAnalizeCrotalia(fermaid, i,year);
                        rdscrot.Value = samplescrot;
                        report.DataSources.Add(rdscrot);
                        //controale
                        ReportDataSource rdsm = new ReportDataSource();
                        rdsm.Name = "SampleAnalizeExt";
                        List<SampleAnalizeExt> samplesext = SampleAnalize.GetSampleAnalizeExt(fermaid,year);
                        rdsm.Value = samplesext;
                        report.DataSources.Add(rdsm);

                        report.ReportPath = "ControlsSummaryAll.rdlc";
                        string datatestare = "Data: " + DateTime.Now.ToString("dd/MM/yyyy");
                        // set reports parameters
                        string title = userinfos.NumeFerma + " - " + "Cod exploatatie " + userinfos.UserCod;
                      
                        ReportParameter pCod = new ReportParameter("CodExpl", userinfos.UserCod);
                        ReportParameter pData = new ReportParameter("DataExec", datatestare);
                        ReportParameter pChartTitle = new ReportParameter("ChartTitle", chartTitles[i]);
                        ReportParameter pChartLabelsName = new ReportParameter("ChartLabelsName", chartLabelsName[i]);
                        ReportParameter pUm = new ReportParameter("Um", um[i]);
                        ReportParameter pTitle = new ReportParameter("Title", title);
                        ReportParameter pUsername = new ReportParameter("UserName", username);
                        string baseurl = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + ConfigurationManager.AppSettings["baseurl"];
                        ReportParameter pBaseurl = new ReportParameter("BaseUrl", baseurl);
                        ReportParameter[] p = { pCod, pData, pChartTitle, pChartLabelsName, pUm,pTitle,pUsername,pBaseurl };
                        report.SetParameters(p);
                        report.Refresh();
                        // write xls
                        string httppath = ConfigurationManager.AppSettings["httppath"];
                        //"/Downloads/";
                        string filepath = ConfigurationManager.AppSettings["filepath"];
                        //"D:\\portal\\Downloads\\";
                        string path_raport_http = "http://" + Request.ServerVariables.Get("HTTP_HOST") + "/" + httppath;
                        string fileid = chartTitles[i];
                        if (i == 8) fileid = fileid.Substring(0, 4);
                        string raport_excel = "Grafic" + fileid + "_" + userinfos.UserCod + "_" + (DateTime.Now).ToString("ddMMyyyy") + ".xls";
                        string excel_file = filepath + raport_excel;
                        string excel_link = path_raport_http + raport_excel;
                        byte[] xls_content = report.Render("EXCEL", deviceInfoXls, out mimeType, out encoding, out fileExtension, out streams, out warnings);
                        File.WriteAllBytes(excel_file, xls_content);
                        //rename sheets
                        using (StreamReader input = new StreamReader(excel_file))
                        {
                            HSSFWorkbook workbook = new HSSFWorkbook(new POIFSFileSystem(input.BaseStream));
                            if (workbook != null)
                            {
                                workbook.SetSheetName(0, chartTitles[i]);
                              //  workbook.SetSheetName(1, "Detaliu date");
                                WriteExcel(excel_file, workbook);
                            }

                        }

                        //

                        HyperLink hyper = (HyperLink)holder.FindControl("xlslink" + parIds[i]);
                        hyper.Visible = true;
                        hyper.NavigateUrl = excel_link;
                        hyper.Text = raport_excel;
                        ErrorMessage.Text = "";
                    } //checked
                }//for
            }//samplecount >0
        }//fermaid >0
        else
        {
            // ErrorMessage.Text = "Nu exista date pentru codul "+ userinfos.UserCod;
        }
    }

        public void TestEvaluateAll()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet s1 = wb.CreateSheet();
            NPOI.SS.UserModel.ISheet s2 = wb.CreateSheet();
            wb.SetSheetName(0, "S1");
            wb.SetSheetName(1, "S2");

            IRow s1r1 = s1.CreateRow(0);
            IRow s1r2 = s1.CreateRow(1);
            IRow s2r1 = s2.CreateRow(0);

            ICell s1r1c1 = s1r1.CreateCell(0);
            ICell s1r1c2 = s1r1.CreateCell(1);
            ICell s1r1c3 = s1r1.CreateCell(2);
            s1r1c1.SetCellValue(22.3);
            s1r1c2.SetCellValue(33.4);
            s1r1c3.CellFormula = ("SUM(A1:B1)");

            ICell s1r2c1 = s1r2.CreateCell(0);
            ICell s1r2c2 = s1r2.CreateCell(1);
            ICell s1r2c3 = s1r2.CreateCell(2);
            s1r2c1.SetCellValue(-1.2);
            s1r2c2.SetCellValue(-3.4);
            s1r2c3.CellFormula = ("SUM(A2:B2)");

            ICell s2r1c1 = s2r1.CreateCell(0);
            s2r1c1.CellFormula = ("S1!A1");

            // Not Evaluated yet
            Assert.AreEqual(0.0, s1r1c3.NumericCellValue, 0);
            Assert.AreEqual(0.0, s1r2c3.NumericCellValue, 0);
            Assert.AreEqual(0.0, s2r1c1.NumericCellValue, 0);

            // Do a full Evaluate, as per our docs
            // uses EvaluateFormulaCell()
            for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
            {
                NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
                HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
                {
                    IRow r = (IRow)rit.Current;

                    for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext(); )
                    {
                        ICell c = (ICell)cit.Current;
                        if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
                        {
                            evaluator.EvaluateFormulaCell(c);

                            // For Testing - all should be numeric
                            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, evaluator.EvaluateFormulaCell(c));
                        }
                    }
                }
            }

            // Check now as expected
            Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual("SUM(A1:B1)", wb.GetSheetAt(0).GetRow(0).GetCell(2).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);

            Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual("SUM(A2:B2)", wb.GetSheetAt(0).GetRow(1).GetCell(2).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);

            Assert.AreEqual(22.3, wb.GetSheetAt(1).GetRow(0).GetCell(0).NumericCellValue, 0);
            Assert.AreEqual("'S1'!A1", wb.GetSheetAt(1).GetRow(0).GetCell(0).CellFormula);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.FORMULA, wb.GetSheetAt(1).GetRow(0).GetCell(0).CellType);


            // Now do the alternate call, which zaps the formulas
            // uses EvaluateInCell()
            for (int sheetNum = 0; sheetNum < wb.NumberOfSheets; sheetNum++)
            {
                NPOI.SS.UserModel.ISheet sheet = wb.GetSheetAt(sheetNum);
                HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(wb);

                for (IEnumerator rit = sheet.GetRowEnumerator(); rit.MoveNext(); )
                {
                    IRow r = (IRow)rit.Current;

                    for (IEnumerator cit = r.GetEnumerator(); cit.MoveNext(); )
                    {
                        ICell c = (ICell)cit.Current;
                        if (c.CellType == NPOI.SS.UserModel.CellType.FORMULA)
                        {
                            evaluator.EvaluateInCell(c);
                        }
                    }
                }
            }

            Assert.AreEqual(55.7, wb.GetSheetAt(0).GetRow(0).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(0).GetCell(2).CellType);

            Assert.AreEqual(-4.6, wb.GetSheetAt(0).GetRow(1).GetCell(2).NumericCellValue, 0);
            Assert.AreEqual(NPOI.SS.UserModel.CellType.NUMERIC, wb.GetSheetAt(0).GetRow(1).GetCell(2).CellType);
//.........这里部分代码省略.........

        public void TestEvaluateBooleanInCell_bug44508()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet = wb.CreateSheet();
            wb.SetSheetName(0, "Sheet1");
            IRow row = sheet.CreateRow(0);
            ICell cell = row.CreateCell(0);

            cell.CellFormula = ("1=1");

            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            try
            {
                fe.EvaluateInCell(cell);
            }
            catch (FormatException)
            {
                Assert.Fail("Identified bug 44508");
            }
            Assert.AreEqual(true, cell.BooleanCellValue);
        }

        private void btn_export_Click(object sender, EventArgs e)
        {
            FileStream xls = new FileStream(@"Template.xls", FileMode.Open);
            HSSFWorkbook wb = new HSSFWorkbook(xls);

            #region sheet
            ISheet sheet1 = wb.GetSheet("Sheet1");
            string start = this.dateTimePicker1.Value.Date.ToShortDateString();
            string end = this.dateTimePicker2.Value.Date.ToShortDateString();
            wb.SetSheetName(0, start + "~" + end + @" ͼֽ������(��רҵ)");

            string Obj = this.comboBox1.Text;//�������
            if (Obj == string.Empty)
            {
                MessageBox.Show("��ѡ����������");
                return;
            }

            string proj = this.comboBox2.Text;//��Ŀ
            if (proj == string.Empty)
            {
                MessageBox.Show("��ѡ����Ŀ");
                return;
            }
            DataSet mDSTotalDwg = GetDataSet(GetSQLs(proj, Obj, "ͼֽ����", start, end, true));
            DataSet mDSPlanApp = GetDataSet(GetSQLs(proj, Obj, "�ƻ�����", start, end, true));
            DataSet mDSActualApp = GetDataSet(GetSQLs(proj, Obj, "ʵ������", start, end, true));
            DataSet mDSDelayApp = GetDataSet(GetSQLs(proj, Obj, "�ۼ���������", start, end, true));
            DataSet mDSDelayNotApp = GetDataSet(GetSQLs(proj, Obj, "�ۼ�����δ����", start, end, true));
            DataSet mDSRejectedDwg = GetDataSet(GetSQLs(proj, Obj, "���ܷ���", start, end, true));
            DataSet mDSRejectedCDwg = GetDataSet(GetSQLs(proj, Obj, "������Ͽ�", start, end, true));
            DataSet mDSPlannedAppDwg = GetDataSet(GetSQLs(proj, Obj, "�ƻ���ȫ�Ͽ�", start, end, true));
            DataSet mDSApprovedDwg = GetDataSet(GetSQLs(proj, Obj, "��ȫ�Ͽ�", start, end, true));

            IRow HeadRow = sheet1.GetRow(0);
            IRow TotalDwg = sheet1.GetRow(1);
            IRow PlanApp = sheet1.GetRow(2);
            IRow ActualApp = sheet1.GetRow(3);
            IRow DelayApp = sheet1.GetRow(4);
            IRow DelayNotApp = sheet1.GetRow(5);
            IRow RejectedDwg = sheet1.GetRow(6);
            IRow RejectedCDwg = sheet1.GetRow(7);
            IRow PlannedDwg = sheet1.GetRow(8);
            IRow ApprovedDwg = sheet1.GetRow(9);

            for (int i = 1; i < HeadRow.Cells.Count - 3; i++)
            {
                string discipline = HeadRow.Cells[i].ToString();
                //ͼֽ����
                for (int j = 0; j < mDSTotalDwg.Tables[0].Rows.Count; j++)
                {
                    if (mDSTotalDwg.Tables[0].Rows[j][0].ToString() == discipline)
                        TotalDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSTotalDwg.Tables[0].Rows[j][1].ToString()));
                }
                //�ƻ�����
                for (int j = 0; j < mDSPlanApp.Tables[0].Rows.Count; j++)
                {
                    if (mDSPlanApp.Tables[0].Rows[j][0].ToString() == discipline)
                        PlanApp.GetCell(i).SetCellValue(Convert.ToInt32(mDSPlanApp.Tables[0].Rows[j][1].ToString()));
                }
                //ʵ������
                for (int j = 0; j < mDSActualApp.Tables[0].Rows.Count; j++)
                {
                    if (mDSActualApp.Tables[0].Rows[j][0].ToString() == discipline)
                        ActualApp.GetCell(i).SetCellValue(Convert.ToInt32(mDSActualApp.Tables[0].Rows[j][1].ToString()));
                }
                //�ۼ���������
                for (int j = 0; j < mDSDelayApp.Tables[0].Rows.Count; j++)
                {
                    if (mDSDelayApp.Tables[0].Rows[j][0].ToString() == discipline)
                        DelayApp.GetCell(i).SetCellValue(Convert.ToInt32(mDSDelayApp.Tables[0].Rows[j][1].ToString()));
                }
                //�ۼ�����δ����
                for (int j = 0; j < mDSDelayNotApp.Tables[0].Rows.Count; j++)
                {
                    if (mDSDelayNotApp.Tables[0].Rows[j][0].ToString() == discipline)
                        DelayNotApp.GetCell(i).SetCellValue(Convert.ToInt32(mDSDelayNotApp.Tables[0].Rows[j][1].ToString()));
                }
                //���ܷ���
                for (int j = 0; j < mDSRejectedDwg.Tables[0].Rows.Count; j++)
                {
                    if (mDSRejectedDwg.Tables[0].Rows[j][0].ToString() == discipline)
                        RejectedDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSRejectedDwg.Tables[0].Rows[j][1].ToString()));
                }
                //������Ͽ�
                for (int j = 0; j < mDSRejectedCDwg.Tables[0].Rows.Count; j++)
                {
                    if (mDSRejectedCDwg.Tables[0].Rows[j][0].ToString() == discipline)
                        RejectedCDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSRejectedCDwg.Tables[0].Rows[j][1].ToString()));
                }
                //�ƻ���ȫ�Ͽ�
                for (int j = 0; j < mDSPlannedAppDwg.Tables[0].Rows.Count; j++)
                {
                    if (mDSPlannedAppDwg.Tables[0].Rows[j][0].ToString() == discipline)
                        PlannedDwg.GetCell(i).SetCellValue(Convert.ToInt32(mDSPlannedAppDwg.Tables[0].Rows[j][1].ToString()));
                }
                //��ȫ�Ͽ�
                for (int j = 0; j < mDSApprovedDwg.Tables[0].Rows.Count; j++)
                {
                    if (mDSApprovedDwg.Tables[0].Rows[j][0].ToString() == discipline)
//.........这里部分代码省略.........