C# HSSFWorkbook.RemoveSheetAt方法代码示例

        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 FileResult Generate(SearchEntity searchEntity)
 {
     int num;
     var arrayData = this.GetArrayData(searchEntity, out num);
     var str = "根基对账单";
     using (var stream = System.IO.File.OpenRead(base.Server.MapPath(string.Format("~/Excel/Market/{0}.xls", str))))
     {
         var xlBook = new HSSFWorkbook(stream);
         this.GenerateM1(arrayData, xlBook);
         this.GenerateM2(arrayData, xlBook);
         this.GenerateM3(arrayData, xlBook);
         if (xlBook.NumberOfSheets > 3)
         {
             xlBook.RemoveSheetAt(0);
             xlBook.RemoveSheetAt(0);
             xlBook.RemoveSheetAt(0);
             xlBook.FirstVisibleTab = 0;
         }
         var str3 = string.Format("{0}{1:yyMMddHHmmss}.xls", str, DateTime.Now);
         var path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3);
         if (System.IO.File.Exists(path))
         {
             str3 = str3 + Guid.NewGuid().ToString() + ".xls";
             path = string.Format(@"{0}Temp\{1}", AppDomain.CurrentDomain.BaseDirectory, str3);
         }
         using (var stream2 = System.IO.File.OpenWrite(path))
         {
             xlBook.Write(stream2);
         }
         return this.File(path, "application/vnd.ms-excel", str3);
     }
 }

        public void TestGetSheetIndex()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = (HSSFSheet)wb.CreateSheet("Sheet1");
            HSSFSheet sheet2 = (HSSFSheet)wb.CreateSheet("Sheet2");
            HSSFSheet sheet3 = (HSSFSheet)wb.CreateSheet("Sheet3");
            HSSFSheet sheet4 = (HSSFSheet)wb.CreateSheet("Sheet4");

            Assert.AreEqual(0, wb.GetSheetIndex(sheet1));
            Assert.AreEqual(1, wb.GetSheetIndex(sheet2));
            Assert.AreEqual(2, wb.GetSheetIndex(sheet3));
            Assert.AreEqual(3, wb.GetSheetIndex(sheet4));

            // remove sheets
            wb.RemoveSheetAt(0);
            wb.RemoveSheetAt(2);

            // ensure that sheets are Moved up and Removed sheets are not found any more
            Assert.AreEqual(-1, wb.GetSheetIndex(sheet1));
            Assert.AreEqual(0, wb.GetSheetIndex(sheet2));
            Assert.AreEqual(1, wb.GetSheetIndex(sheet3));
            Assert.AreEqual(-1, wb.GetSheetIndex(sheet4));
        }

        public void TestActiveSheetAfterDelete_bug40414()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            NPOI.SS.UserModel.ISheet sheet0 = wb.CreateSheet("Sheet0");
            NPOI.SS.UserModel.ISheet sheet1 = wb.CreateSheet("Sheet1");
            NPOI.SS.UserModel.ISheet sheet2 = wb.CreateSheet("Sheet2");
            NPOI.SS.UserModel.ISheet sheet3 = wb.CreateSheet("Sheet3");
            NPOI.SS.UserModel.ISheet sheet4 = wb.CreateSheet("Sheet4");

            // Confirm default activation/selection
            ConfirmActiveSelected(sheet0, true);
            ConfirmActiveSelected(sheet1, false);
            ConfirmActiveSelected(sheet2, false);
            ConfirmActiveSelected(sheet3, false);
            ConfirmActiveSelected(sheet4, false);

            wb.SetActiveSheet(3);
            wb.SetSelectedTab(3);

            ConfirmActiveSelected(sheet0, false);
            ConfirmActiveSelected(sheet1, false);
            ConfirmActiveSelected(sheet2, false);
            ConfirmActiveSelected(sheet3, true);
            ConfirmActiveSelected(sheet4, false);

            wb.RemoveSheetAt(3);
            // after removing the only active/selected sheet, another should be active/selected in its place
            if (!sheet4.IsSelected)
            {
                throw new AssertionException("identified bug 40414 a");
            }
            if (!sheet4.IsActive)
            {
                throw new AssertionException("identified bug 40414 b");
            }

            ConfirmActiveSelected(sheet0, false);
            ConfirmActiveSelected(sheet1, false);
            ConfirmActiveSelected(sheet2, false);
            ConfirmActiveSelected(sheet4, true);

            sheet3 = sheet4; // re-align local vars in this Test case

            // Some more cases of removing sheets

            // Starting with a multiple selection, and different active sheet
            wb.SetSelectedTabs(new int[] { 1, 3, });
            wb.SetActiveSheet(2);
            ConfirmActiveSelected(sheet0, false, false);
            ConfirmActiveSelected(sheet1, false, true);
            ConfirmActiveSelected(sheet2, true, false);
            ConfirmActiveSelected(sheet3, false, true);

            // removing a sheet that is not active, and not the only selected sheet
            wb.RemoveSheetAt(3);
            ConfirmActiveSelected(sheet0, false, false);
            ConfirmActiveSelected(sheet1, false, true);
            ConfirmActiveSelected(sheet2, true, false);

            // removing the only selected sheet
            wb.RemoveSheetAt(1);
            ConfirmActiveSelected(sheet0, false, false);
            ConfirmActiveSelected(sheet2, true, true);

            // The last remaining sheet should always be active+selected
            wb.RemoveSheetAt(1);
            ConfirmActiveSelected(sheet0, true, true);
        }

        public void Bug56325()
        {
            HSSFWorkbook wb;

            FileInfo file = HSSFTestDataSamples.GetSampleFile("56325.xls");
            Stream stream = new FileStream(file.FullName, FileMode.Open, FileAccess.ReadWrite);
            try
            {
                POIFSFileSystem fs = new POIFSFileSystem(stream);
                wb = new HSSFWorkbook(fs);
            }
            finally
            {
                stream.Close();
            }

            Assert.AreEqual(3, wb.NumberOfSheets);
            wb.RemoveSheetAt(0);
            Assert.AreEqual(2, wb.NumberOfSheets);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            Assert.AreEqual(2, wb.NumberOfSheets);
            wb.RemoveSheetAt(0);
            Assert.AreEqual(1, wb.NumberOfSheets);
            wb.RemoveSheetAt(0);
            Assert.AreEqual(0, wb.NumberOfSheets);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            Assert.AreEqual(0, wb.NumberOfSheets);
        }

        public ActionResult DownloadResult(string ontologyCode, string fileName)
        {
            OntologyDescriptor ontology;
            if (!AcDomain.NodeHost.Ontologies.TryGetOntology(ontologyCode, out ontology))
            {
                throw new ValidationException("非法的本体码" + ontologyCode);
            }
            string dirPath = Server.MapPath("~/Content/Import/Excel/" + ontology.Ontology.Code + "/" + AcSession.Account.Id);
            string fullName = Path.Combine(dirPath, fileName);
            if (!System.IO.File.Exists(fullName))
            {
                throw new ValidationException("下载的文件不存在" + fullName);
            }
            // 操作Excel
            FileStream fs = System.IO.File.OpenRead(fullName);
            IWorkbook workbook = new HSSFWorkbook(fs);//从流内容创建Workbook对象
            fs.Close();

            ISheet sheet = workbook.GetSheet(ResultSheetName);
            var sheetIndex = workbook.GetSheetIndex(sheet);
            for (int i = 0; i < workbook.NumberOfSheets; i++)
            {
                if (i != sheetIndex)
                {
                    workbook.RemoveSheetAt(i);
                }
            }
            sheetIndex = workbook.GetSheetIndex("Failed");
            if (sheetIndex >= 0)
            {
                workbook.RemoveSheetAt(sheetIndex);
            }
            ISheet failedSheet = workbook.CreateSheet("Failed");
            if (sheet.LastRowNum == 2)
            {
                throw new ValidationException("没有待导入数据");
            }
            int rowIndex = 0;
            IRow headRow0 = sheet.GetRow(rowIndex);
            var columnWidthDic = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
            for (int i = 0; i < headRow0.Cells.Count; i++)
            {
                var cell = headRow0.Cells[i];
                columnWidthDic.Add(cell.SafeToStringTrim(), sheet.GetColumnWidth(i));
            }
            IRow failedRow0 = failedSheet.CreateRow(rowIndex);
            var cells = headRow0.Cells;
            for (int i = 0; i < cells.Count; i++)
            {
                var cell = failedRow0.CreateCell(i);
                cell.CellStyle = cells[i].CellStyle;
                cell.SetCellValue(cells[i].SafeToStringTrim());
            }
            rowIndex++;
            IRow headRow1 = sheet.GetRow(rowIndex);
            IRow failedRow1 = failedSheet.CreateRow(rowIndex);
            cells = headRow1.Cells;
            for (int i = 0; i < cells.Count; i++)
            {
                var cell = failedRow1.CreateCell(i);
                cell.CellStyle = cells[i].CellStyle;
                cell.SetCellValue(cells[i].SafeToStringTrim());
            }
            rowIndex++;
            IRow headRow2 = sheet.GetRow(rowIndex);
            IRow failedRow2 = failedSheet.CreateRow(rowIndex);
            cells = headRow2.Cells;
            for (int i = 0; i < cells.Count; i++)
            {
                var cell = failedRow2.CreateCell(i);
                cell.CellStyle = cells[i].CellStyle;
                cell.SetCellValue(cells[i].SafeToStringTrim());
            }
            failedSheet.CreateFreezePane(0, 3, 0, 3);
            rowIndex++;
            int resultFailedRowIndex = rowIndex;
            int stateCodeIndex = -1;
            int localEntityIdIndex = -1;
            int infoValueKeysIndex = -1;
            int infoIdKeysIndex = -1;
            for (int i = 0; i < headRow0.Cells.Count; i++)
            {
                var value = headRow0.GetCell(i).SafeToStringTrim();
                if (CommandColHeader.StateCode.Equals(value, StringComparison.OrdinalIgnoreCase))
                {
                    stateCodeIndex = i;
                    break;
                }
            }
            if (stateCodeIndex < 0)
            {
                throw new ValidationException("目标Excel中没有头为$StateCode的列");
            }
            for (int i = 0; i < headRow0.Cells.Count; i++)
            {
                var value = headRow0.GetCell(i).SafeToStringTrim();
                if (CommandColHeader.LocalEntityId.Equals(value, StringComparison.OrdinalIgnoreCase))
                {
                    localEntityIdIndex = i;
                    break;
//.........这里部分代码省略.........

//.........这里部分代码省略.........
                        successStyle.BorderRight = BorderStyle.Thin;
                        successStyle.BorderTop = BorderStyle.Thin;
                        successStyle.FillForegroundColor = HSSFColor.LightGreen.Index;
                        successStyle.FillPattern = FillPattern.SolidForeground;

                        ISheet sheet = null;
                        // 工作表sheet的命名规则是：本体码 或 本体名 或 ‘工作表’
                        var sheetNames = new HashSet<string>(StringComparer.OrdinalIgnoreCase) {
                            ontology.Ontology.Code, ontology.Ontology.Name, "工作表","Failed","失败的","Sheet1"
                        };
                        foreach (var sheetName in sheetNames)
                        {
                            if (sheet != null)
                            {
                                break;
                            }
                            int dataSheetIndex = workbook.GetSheetIndex(sheetName);
                            if (dataSheetIndex >= 0)
                            {
                                sheet = workbook.GetSheetAt(dataSheetIndex);
                            }
                        }
                        if (sheet == null)
                        {
                            System.IO.File.Delete(fullName);
                            throw new ValidationException("没有名称为'" + ontology.Ontology.Code + "'或'" + ontology.Ontology.Name + "'或'工作表'的sheet");
                        }
                        int sheetIndex = workbook.GetSheetIndex(sheet);
                        workbook.SetSheetName(sheetIndex, ResultSheetName);
                        for (int i = 0; i < workbook.NumberOfSheets; i++)
                        {
                            if (i != sheetIndex)
                            {
                                workbook.RemoveSheetAt(i);
                            }
                        }
                        if (sheet.LastRowNum == 2)
                        {
                            System.IO.File.Delete(fullName);
                            throw new ValidationException("没有待导入数据");
                        }
                        int rowIndex = 0;
                        IRow headRow1 = sheet.GetRow(rowIndex);
                        rowIndex++;
                        IRow headRow2 = sheet.GetRow(rowIndex);
                        rowIndex++;
                        IRow headRow3 = sheet.GetRow(rowIndex);
                        rowIndex++;
                        #region 提取列索引 这些字段在Excel模板上对应前缀为“$”的列。
                        int actionCodeIndex = -1,
                            localEntityIdIndex = -1,
                            descriptionIndex = -1,
                            eventReasonPhraseIndex = -1,
                            eventSourceTypeIndex = -1,
                            eventStateCodeIndex = -1,
                            eventSubjectCodeIndex = -1,
                            infoIdKeysIndex = -1,
                            infoValueKeysIndex = -1,
                            isDumbIndex = -1,
                            timeStampIndex = -1,
                            ontologyCodeIndex = -1,
                            reasonPhraseIndex = -1,
                            requestTypeIndex = -1,
                            serverTicksIndex = -1,
                            stateCodeIndex = -1,
                            versionIndex = -1;