C# XSSFWorkbook.GetSheetAt方法代碼示例

        public void TestBug48936()
        {
            IWorkbook w = new XSSFWorkbook();
            ISheet s = w.CreateSheet();
            int i = 0;
            List<String> lst = ReadStrings("48936-strings.txt");
            foreach (String str in lst)
            {
                s.CreateRow(i++).CreateCell(0).SetCellValue(str);
            }

            try
            {
                w = XSSFTestDataSamples.WriteOutAndReadBack(w);
            }
            catch (POIXMLException)
            {
                Assert.Fail("Detected Bug #48936");
            }
            s = w.GetSheetAt(0);
            i = 0;
            foreach (String str in lst)
            {
                String val = s.GetRow(i++).GetCell(0).StringCellValue;
                Assert.AreEqual(str, val);
            }
        }

 private LoadPatternList()
 {
     using (FileStream fs = new FileStream(path,FileMode.Open,FileAccess.Read))
     {
         IWorkbook workbook = new XSSFWorkbook(fs);
         int sheetnumber = workbook.NumberOfSheets;
         int [][][] sheetelements = new int[sheetnumber][][];
         for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){
             ISheet _isheet = workbook.GetSheetAt(sheetindex);
             int lastrownum = _isheet.LastRowNum;
             int [][] rowelements = new int[lastrownum+1][];
             for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){
                 IRow row = _isheet.GetRow(rowindex);
                 if(row == null)continue;
                 int lastcellnum = row.LastCellNum;
                 int[] cellelements = new int[lastcellnum+1];
                 for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){
                     ICell cell = row.GetCell(cellindex);
                     if(cell != null){
                         cellelements[cellindex] = Convert.ToInt32(cell.ToString());
                     }
                 }
                 rowelements[rowindex] = cellelements;
             }
             sheetelements[sheetindex] = rowelements;
         }
         data = sheetelements;
     }
     if(data.Length > 0){
         checksign = "No";
     }
 }

        public string Read(string inFile)
        {
            var stream = File.OpenRead(inFile);
            var book = new XSSFWorkbook(stream);
            stream.Close();

            var sb = new StringBuilder();
            var sheet = book.GetSheetAt(0);
            int lastRowNum = sheet.LastRowNum;
            for (int r = 0; r <= lastRowNum; r++)
            {
                var datarow = sheet.GetRow(r);
                {
                    foreach (var cell in datarow.Cells)
                    {
                        switch (cell.CellType)
                        {
                            case CellType.Numeric:
                                sb.Append(cell.NumericCellValue.ToString() + "\t");
                                break;
                            case CellType.String:
                                sb.Append(cell.StringCellValue.Replace("\n", "") + "\t");
                                break;
                            default:
                                throw new Exception("?");
                        }
                    }
                    sb.Append("\r\n");
                }
            }
            return sb.ToString();
        }

 public ActionResult ExportExcel()
 {
     var data = this.rptRepository.All();
     string path = Server.MapPath("~/Templates/客戶資訊.xlsx");
     FileStream stream = new FileStream(path, FileMode.Open, FileAccess.Read);
     IWorkbook wb = new XSSFWorkbook(stream);
     stream.Close();
     ISheet sheet = wb.GetSheetAt(0);
     ICellStyle cs = sheet.GetRow(1).Cells[0].CellStyle;
     int Index = 1;
     foreach (var item in data)
     {
         IRow row = sheet.CreateRow(Index);
         CreateCell(item.客戶名稱, row, 0, cs);
         CreateCell(item.聯絡人數量.ToString(), row, 1, cs);
         CreateCell(item.客戶帳戶數量.ToString(), row, 2, cs);
         Index++;
     }
     string serverPath = @"D:/repot.xlsx";
     using (FileStream file = new FileStream(serverPath, FileMode.Create))
     {
         wb.Write(file);
         file.Close();
     }
     return File(serverPath, "application/excel","Report.xlsx");
 }

        public static bool UpdateExcelData(string excelFilePath, string excelName)
        {
            XSSFWorkbook excelBook = new XSSFWorkbook(File.Open(excelFilePath, FileMode.Open));
            var sheet = excelBook.GetSheetAt(0);
            var headerRow = sheet.GetRow(0);
            //total columns
            int cellCount = headerRow.LastCellNum;
            //total rows
            int rowCount = sheet.LastRowNum;

            switch (excelName)
            {
                case Constant.Water:
                    UpdateWaterData(sheet, cellCount, rowCount);
                    break;
                case Constant.Air:
                    UpdateAirData(sheet, cellCount, rowCount);
                    break;
            }

            try
            {
                FileStream writefile = new FileStream("d:\\" + excelName + ".xlsx", FileMode.Create, FileAccess.Write);
                excelBook.Write(writefile);
                writefile.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Gets the field names from a workbook and stores them in a list.
        /// </summary>
        /// <param name="fieldNames">list where field names will be stored</param>
        /// <param name="workbook">workbook from which field names will be extracted</param>
        public static void GetFieldNames(ImportData data, XSSFWorkbook workbook)
        {
            ISheet sheet = workbook.GetSheetAt(0);
            bool reading = true;
            int rindex = sheet.FirstRowNum;

            while (reading) {
                IRow row = sheet.GetRow(rindex);

                if (row != null) {
                    ICell cell = row.GetCell(0);

                    if (cell != null) {
                        string s = CellValueAsString(cell);

                        if (s != "" && s[0] == '[') {
                            for (int i = 0; i < row.LastCellNum; i++) {
                                s = CellValueAsString(row.GetCell(i)).TrimEnd(']').TrimStart('['); ;
                                data.fieldNames.Add(s);
                            }

                            // don't read more than one row of field names
                            reading = false;
                        }
                    }
                }

                rindex++;

                if (rindex > sheet.LastRowNum) {
                    reading = false;
                }
            }
        }

        public Dictionary<DateTime, List<SaleParsed>> ImportFromSheet(string path, DateTime dateTime, IEnumerable<int> sheetNumbers, IEnumerable<GoodType> goodTypes, string producer)
        {
            this.goodTypes = goodTypes;
            this.producer = producer;
            var result = new Dictionary<DateTime, List<SaleParsed>>();

            using (var fs = File.OpenRead(path))
            {
                var workbook = new XSSFWorkbook(fs);

                foreach (var sNum in sheetNumbers)
                {
                    ISheet sheet = workbook.GetSheetAt(sNum);

                    var sales = ParseSales(sheet, dateTime);
                    foreach (var s in sales)
                    {
                        if (result.ContainsKey(s.Key))
                        {
                            result[s.Key].AddRange(s.Value);
                        }
                        else
                        {
                            result.Add(s.Key, s.Value);
                        }
                    }
                }
            }

            return result;
        }

 public static int[][][] ReadExcelPattern(string filepath)
 {
     using (FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read))
     {
         IWorkbook workbook = new XSSFWorkbook(fs);
         int sheetnumber = workbook.NumberOfSheets;
         int [][][] sheetelements = new int[sheetnumber][][];
         for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){
             ISheet _isheet = workbook.GetSheetAt(sheetindex);
             int lastrownum = _isheet.LastRowNum;
             int [][] rowelements = new int[lastrownum+1][];
             for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){
                 IRow row = _isheet.GetRow(rowindex);
                 if(row == null)continue;
                 int lastcellnum = row.LastCellNum;
                 int[] cellelements = new int[lastcellnum+1];
                 for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){
                     ICell cell = row.GetCell(cellindex);
                     if(cell != null){
                         cellelements[cellindex] = Convert.ToInt32(cell.ToString());
                         Debug.Log(cellelements[cellindex]);
                     }
                 }
                 rowelements[rowindex] = cellelements;
             }
             sheetelements[sheetindex] = rowelements;
         }
         return sheetelements;
     }
 }

 public void TestLoadStyles()
 {
     XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.OpenResourceAsStream("styles.xlsx"));
     ISheet sheet = workbook.GetSheetAt(0);
     IRow row = sheet.GetRow(0);
     ICell cell = row.GetCell((short)0);
     ICellStyle style = cell.CellStyle;
     // assertNotNull(style);
 }

    /// <summary>
    /// Get the parsable rows from a workbook
    /// </summary>
    /// <param name="rows">a reference to an object that will contain parsable rows</param>
    /// <param name="workbook">the workbook to extract rows from</param>
    public static void GetParsableRows(ref List<ParsableRow> rows, XSSFWorkbook workbook) {

        ISheet sheet = workbook.GetSheetAt(0);

        bool reading = true;
        int rindex = sheet.FirstRowNum;

        while (reading) {
            IRow row = sheet.GetRow(rindex);

            // if there is a row, and it's not empty
            if (row != null && row.Cells.Count > 0) {

                // check to see if the row is parsable by looking at the first value
                ICell firstCell = row.GetCell(0);

                if (firstCell != null) {

                    string firstCellVal = CellValueAsString(firstCell);
                                        
                    // treat the row as parsable if it is:
                    // - not a title
                    // - not metadata
                    // - not a variable
                    if (firstCellVal[0] != '[' && firstCellVal[0] != '#' && firstCellVal[0] != '$') {

                        // cells are stored in parsable row objects
                        ParsableRow pr = new ParsableRow();

                        // copy the line number to the parsable row object
                        pr.linenumber = rindex;

						// make an array which is as long as the last cell number - note that NPOI will show cells **up to** the last cell that
						// contains data, so if a row in the sheet only has a single value in the fourth column, that row will return null for all
						// indices up to 2, then a value for index 3.
                        pr.cells = new string[row.LastCellNum];

                        // for each cell in the row, convert it to a string and copy it to the parsable row object
                        for (int i = 0; i < row.LastCellNum; i++) {
                            pr.cells[i] = CellValueAsString(row.GetCell(i));                   
                        }
                        
                        // add the parsable row object we just set up to the list of rows that was passed to the method
                        rows.Add(pr);
                    }
                }
            }

            rindex++;

            // check to make sure we're not out of bounds
            if (rindex > sheet.LastRowNum) {

                reading = false;
            }
        }
    }

        private void button1_Click(object sender, EventArgs e)
        {

           // LoadTemplate();


            //FileStream file = new FileStream(@"template/Astro2_Sys WHCK Status - 0712-SA2.xlsx", FileMode.Open, FileAccess.ReadWrite);
            FileStream file = new FileStream(@"template/Astro2_Sys WHCK Status - 0712-SA2.xlsx", FileMode.Open, FileAccess.ReadWrite);
            IWorkbook wb = new XSSFWorkbook(file);


            MessageBox.Show(wb.NumberOfSheets+"", "警告", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            if (wb.NumberOfSheets >= 2)
            {
                ISheet sheet1 = wb.GetSheetAt(1);

                sheet1.GetRow(7).GetCell(6).SetCellValue("Failed");

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

            }
            //create cell on rows, since rows do already exist,it's not necessary to create rows again.
         //   sheet1.GetRow(8).GetCell(7).SetCellValue("Failed");
            /*
            sheet1.GetRow(2).GetCell(1).SetCellValue(300);
            sheet1.GetRow(3).GetCell(1).SetCellValue(500050);
            sheet1.GetRow(4).GetCell(1).SetCellValue(8000);
            sheet1.GetRow(5).GetCell(1).SetCellValue(110);
            sheet1.GetRow(6).GetCell(1).SetCellValue(100);
            sheet1.GetRow(7).GetCell(1).SetCellValue(200);
            sheet1.GetRow(8).GetCell(1).SetCellValue(210);
            sheet1.GetRow(9).GetCell(1).SetCellValue(2300);
            sheet1.GetRow(10).GetCell(1).SetCellValue(240);
            sheet1.GetRow(11).GetCell(1).SetCellValue(180123);
            sheet1.GetRow(12).GetCell(1).SetCellValue(150);
             * */

            //Force excel to recalculate all the formula while open
            //sheet1.ForceFormulaRecalculation = true;

            
            //Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
            //Response.BinaryWrite(ms.ToArray());
           // WriteToFile();
          


          //  wb = null;
            //ms.Close();
            //ms.Dispose();
        }

    public static void CreateLoadScriptableObject()
    {
        string filepath = AssetDatabase.GetAssetPath(Selection.activeObject);
        string createpath = AssetDatabase.GenerateUniqueAssetPath("Assets/PartA/Resources/Pattern/" + Selection.activeObject.name +".asset");
        ExcelData exceldata = ScriptableObject.CreateInstance<ExcelData>();
        //List<_Row> _sheet_list = new List<_Row>();
        using (FileStream fs = new FileStream(filepath,FileMode.Open,FileAccess.Read))
        {
            IWorkbook workbook = new XSSFWorkbook(fs);
            int sheetnumber = workbook.NumberOfSheets;
            int [][][] sheetelements = new int[sheetnumber][][];
            exceldata.data = new int[sheetnumber][][];
            for(int sheetindex = 0;sheetindex < sheetnumber;++sheetindex){
                ISheet _isheet = workbook.GetSheetAt(sheetindex);
                int lastrownum = _isheet.LastRowNum;
                int [][] rowelements = new int[lastrownum + 1][];
                _Row localrow = new _Row();
                for(int rowindex = _isheet.FirstRowNum;rowindex <= lastrownum;++rowindex){
                    IRow row = _isheet.GetRow(rowindex);
                    if(row == null)continue;
                    int lastcellnum = row.LastCellNum;
                    int[] cellelements = new int[lastcellnum + 1];
                    _Cell localcell = new _Cell();
                    for(int cellindex = row.FirstCellNum;cellindex < lastcellnum;++cellindex){
                        ICell cell = row.GetCell(cellindex);
                        if(cell != null){
                            //cellelements[cellindex] = Convert.ToInt32(cell.ToString());
                            localcell.cell[cellindex] = Convert.ToInt32(cell.ToString());
                        }
                    }
                    localrow.row.Add(localcell);
                    rowelements[rowindex] = cellelements;
                }
                exceldata.sheet.Add(localrow);
                sheetelements[sheetindex] = rowelements;
            }

            //exceldata.data.CopyTo(sheetelements,0);
            //exceldata.data = (int[][][])sheetelements.Clone();
            foreach(_Row cells in  exceldata.sheet){
                foreach(_Cell test in cells.row){
                    for(int i = 0;i < 5;i++)
                    Debug.Log(test.cell[i]);
                }
            }
            //exceldata.data = sheetelements;
            exceldata.check = "Exist";
            exceldata.hideFlags = HideFlags.NotEditable;
            EditorUtility.SetDirty(exceldata);
            AssetDatabase.CreateAsset(exceldata,createpath);
            AssetDatabase.SaveAssets();
            EditorUtility.FocusProjectWindow();
        }
    }

 public void TestLoadSample()
 {
     XSSFWorkbook workbook = new XSSFWorkbook(_ssSampels.OpenResourceAsStream("sample.xlsx"));
     Assert.AreEqual(3, workbook.NumberOfSheets);
     Assert.AreEqual("Sheet1", workbook.GetSheetName(0));
     ISheet sheet = workbook.GetSheetAt(0);
     IRow row = sheet.GetRow(0);
     ICell cell = row.GetCell((short)1);
     Assert.IsNotNull(cell);
     Assert.AreEqual(111.0, cell.NumericCellValue, 0.0);
     cell = row.GetCell((short)0);
     Assert.AreEqual("Lorem", cell.RichStringCellValue.String);
 }

        public void TestCreate()
        {
            XSSFWorkbook workbook = new XSSFWorkbook();
            XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
            XSSFRow row = sheet.CreateRow(0) as XSSFRow;
            XSSFCreationHelper CreateHelper = workbook.GetCreationHelper() as XSSFCreationHelper;

            String[] urls = {
                "http://apache.org/",
                "www.apache.org",
                "/temp",
                "file:///c:/temp",
                "http://apache.org/default.php?s=isTramsformed&submit=Search&la=*&li=*"};
            for (int i = 0; i < urls.Length; i++)
            {
                String s = urls[i];
                XSSFHyperlink link = CreateHelper.CreateHyperlink(HyperlinkType.Url) as XSSFHyperlink;
                link.Address=(s);

                XSSFCell cell = row.CreateCell(i) as XSSFCell;
                cell.Hyperlink=(link);
            }
            workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
            sheet = workbook.GetSheetAt(0) as XSSFSheet;
            PackageRelationshipCollection rels = sheet.GetPackagePart().Relationships;
            Assert.AreEqual(urls.Length, rels.Size);
            for (int i = 0; i < rels.Size; i++)
            {
                PackageRelationship rel = rels.GetRelationship(i);
                if (rel.TargetUri.IsAbsoluteUri&&rel.TargetUri.IsFile)
                    Assert.AreEqual(urls[i].Replace("file:///","").Replace("/","\\"),rel.TargetUri.LocalPath);
                else
                    // there should be a relationship for each URL
                    Assert.AreEqual(urls[i], rel.TargetUri.ToString());
            }

            // Bugzilla 53041: Hyperlink relations are duplicated when saving XSSF file
            workbook = XSSFTestDataSamples.WriteOutAndReadBack(workbook) as XSSFWorkbook;
            sheet = workbook.GetSheetAt(0) as XSSFSheet;
            rels = sheet.GetPackagePart().Relationships;
            Assert.AreEqual(urls.Length, rels.Size);
            for (int i = 0; i < rels.Size; i++)
            {
                PackageRelationship rel = rels.GetRelationship(i);
                if (rel.TargetUri.IsAbsoluteUri && rel.TargetUri.IsFile)
                    Assert.AreEqual(urls[i].Replace("file:///", "").Replace("/", "\\"), rel.TargetUri.LocalPath);
                else
                    // there should be a relationship for each URL
                    Assert.AreEqual(urls[i], rel.TargetUri.ToString());
            }
        }

 public void SetUp()
 {
     if (workbook == null)
     {
         Stream is1 = HSSFTestDataSamples.OpenSampleFileStream(SS.FILENAME);
         OPCPackage pkg = OPCPackage.Open(is1);
         workbook = new XSSFWorkbook(pkg);
         sheet = workbook.GetSheetAt(0);
     }
     _functionFailureCount = 0;
     _functionSuccessCount = 0;
     _EvaluationFailureCount = 0;
     _EvaluationSuccessCount = 0;
 }