C# HSSFWorkbook.GetSheetAt方法代码示例

        static void Main(string[] args)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;

            //here, we must insert at least one sheet to the workbook. otherwise, Excel will say 'data lost in file'
            //So we insert three sheet just like what Excel does
            hssfworkbook.CreateSheet("Sheet1");
            hssfworkbook.CreateSheet("Sheet2");
            hssfworkbook.CreateSheet("Sheet3");
            hssfworkbook.CreateSheet("Sheet4");

            ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeFormula = false;
            ((HSSFSheet)hssfworkbook.GetSheetAt(0)).AlternativeExpression = false;

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

        protected override HSSFWorkbook InternalGenerate()
        {
            var workbook = new HSSFWorkbook(new MemoryStream(Templates.ExceptionSchedule));

            styles = new StandardCellStyles(workbook);

            AddDataToReport(workbook.GetSheetAt(0), GetDefaultExceptionScheduleData());
            AddDataToReport(workbook.GetSheetAt(1), GetServiceExceptionScheduleData(), false);

            return workbook;
        }

        /// <summary>
        /// </summary>
        /// <param name="excelFile">文件路径</param>
        /// <param name="sheetIndex">0 start</param>
        /// <param name="skipRow">0 start</param>
        public void Read(Stream excelFile, int sheetIndex, int skipRow)
        {
            using (excelFile)
            {
                var workbook = new HSSFWorkbook(excelFile, false);
                ISheet sheet = workbook.GetSheetAt(0);
                int start = sheet.FirstRowNum > skipRow ? sheet.FirstRowNum : skipRow;
                int end = sheet.LastRowNum;
                if (start >= end)
                {
                    return;
                }

                for (int i = start; i <= end; i++)
                {
                    IRow row = sheet.GetRow(i);

                    var array = new List<ICell>();
                    int cellsNumber = row.Cells.Count;
                    for (short cellIndex = row.FirstCellNum; cellIndex <= cellsNumber; cellIndex++)
                    {
                        array.Add(row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK));
                    }
                    List<ValidationResult> result;
                    _process.Process(array.ToArray(), out result);
                    RowsResult.Add(result);
                }
            }
        }

        //读取xls文件
        private ActionResult ReadExcel()
        {
            StringBuilder sbr = new StringBuilder();
            FileStream fs = new FileStream(@"f:/test.xls",FileMode.Open);//打开myxls.xls文件

                HSSFWorkbook wk = new HSSFWorkbook(fs);   //把xls文件中的数据写入wk中
                for (int i = 0; i < wk.NumberOfSheets; i++)  //NumberOfSheets是myxls.xls中总共的表数
                {
                    ISheet sheet = wk.GetSheetAt(i);   //读取当前表数据
                    for (int j = 0; j <= sheet.LastRowNum; j++)  //LastRowNum 是当前表的总行数
                    {
                        IRow row = sheet.GetRow(j);  //读取当前行数据
                        if (row != null)
                        {
                            sbr.Append("-------------------------------------\r\n"); //读取行与行之间的提示界限
                            for (int k = 0; k <= row.LastCellNum; k++)  //LastCellNum 是当前行的总列数
                            {
                                ICell cell = row.GetCell(k);  //当前表格
                                if (cell != null)
                                {
                                    sbr.Append(cell.ToString());   //获取表格中的数据并转换为字符串类型
                                }
                            }
                        }
                    }
                }

            sbr.ToString();
            StreamWriter wr = new StreamWriter(new FileStream(@"f:/myText.txt", FileMode.Append));  //把读取xls文件的数据写入myText.txt文件中

                wr.Write(sbr.ToString());
                wr.Flush();

                return View();
        }

 Workbook CreateWorkbook(HSSFWorkbook workbook)
 {
     var worksheets = new List<Worksheet>();
     for (int i = 0; i < workbook.NumberOfSheets; i++)
         worksheets.Add(CreateWorksheet(workbook.GetSheetAt(i)));
     return new Workbook(worksheets);
 }

	static void LoadMissionData(string path,MasterShop data)
	{
		using (FileStream stream = File.Open(path, FileMode.Open, FileAccess.Read))
		{
			IWorkbook book = new HSSFWorkbook(stream);

			ISheet sheet = book.GetSheetAt(0);
			Debug.Log(sheet.SheetName);

            IRow row0 = sheet.GetRow(0);

            //一番最初のフィールドは見出しなので無視
            for (int i = 1; i < sheet.LastRowNum; i++)
            {

                IRow row = sheet.GetRow(i);
                //エクセルデータを編集したらこことMasterCharacterに追加すれば更新できるよ
                MasterShop.param p = new MasterShop.param();
                p.id = (int)row.GetCell(0).NumericCellValue;
                p.name = row.GetCell(1).StringCellValue;
                p.subscripsion = row.GetCell(2).StringCellValue;
                p.gold = (int)row.GetCell(3).NumericCellValue;
                p.hot = (int)row.GetCell(4).NumericCellValue;
                p.stress = (int)row.GetCell(5).NumericCellValue;
                p.category = (int)row.GetCell(6).NumericCellValue;
                p.res = row.GetCell(7).StringCellValue;
                p.color = row.GetCell(8).StringCellValue;
                p.gread = (int)row.GetCell(9).NumericCellValue;
                data.list.Add(p);
            }

        }
	}

        public void TestGetPatriarch()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            Assert.IsNull(sh.DrawingPatriarch);

            HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            Assert.IsNotNull(patriarch);
            patriarch.CreateSimpleShape(new HSSFClientAnchor());
            patriarch.CreateSimpleShape(new HSSFClientAnchor());

            Assert.AreSame(patriarch, sh.DrawingPatriarch);

            EscherAggregate agg = patriarch.GetBoundAggregate();

            EscherDgRecord dg = agg.GetEscherContainer().GetChildById(EscherDgRecord.RECORD_ID) as EscherDgRecord;
            int lastId = dg.LastMSOSPID;

            Assert.AreSame(patriarch, sh.CreateDrawingPatriarch());

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            dg = patriarch.GetBoundAggregate().GetEscherContainer().GetChildById(EscherDgRecord.RECORD_ID) as EscherDgRecord;

            Assert.AreEqual(lastId, dg.LastMSOSPID);
        }

    protected void GSD_btn_Click(object sender, EventArgs e)
    {
        try
        {
            this.workbook = new HSSFWorkbook(PYS_FileUpload.FileContent);
            this.u_sheet = (HSSFSheet)workbook.GetSheetAt(0);  //取得第0個Sheet
            //不同於Microsoft Object Model，NPOI都是從索引0開始算起
            //從第一個Worksheet讀資料        
            SaveOrInsertSheet(this.u_sheet);
            ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入完成", "alert('匯入完成');", true);
        }
        catch (Exception)
        {
            ClientScript.RegisterClientScriptBlock(typeof(System.Web.UI.Page), "匯入失敗", "alert('匯入失敗');", true);
        }
        finally
        {
            //釋放 NPOI的資源 
            if (this.workbook != null) this.workbook = null;
            if (this.u_sheet != null) this.u_sheet = null;

            //是否刪除Server上的Excel檔(預設true)
            /*bool isDeleteFileFromServer = false;
            if (isDeleteFileFromServer)
            {
                System.IO.File.Delete(excel_filePath);
            }*/
            GC.Collect();
        }
    }

        /// <summary>
        /// 读取2007以上版本.xlsx
        /// </summary>
        /// <param name="path"></param>
        /// <returns></returns>
        public static string Read2003ToString(string path)
        {
            HSSFWorkbook hssfworkbook;
            path = HttpContext.Current.Server.MapPath(path);

            using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }

            HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            StringBuilder sb = new StringBuilder();
            int irow = 0;
            sb.Append("<table>");
            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                irow++;
                sb.Append("<tr>");
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    HSSFCell cell = (HSSFCell)row.GetCell(i);
                    string dr = "";
                    if (cell == null) { dr = ""; }
                    else { dr = cell.ToString(); }
                    sb.Append("<td>" + dr + "</td>");//("+irow+","+i+")"+
                }
                sb.Append("</tr>");
            }
            sb.Append("</table>");
            return sb.ToString();
        }

        public void Transfer()
        {
            string oldExcelPath = string.Empty;

            /*erp标准格式*/
            string xslmodelPath = string.Empty;
            FileStream fsModel = new FileStream(xslmodelPath, FileMode.Open);
            HSSFWorkbook xslmodel = new HSSFWorkbook(fsModel);
            var modelSheet = xslmodel.GetSheetAt(0);
            /*现有的格式*/
            using (FileStream fs = new FileStream(oldExcelPath, FileMode.Open))
            {
                HSSFWorkbook oldbook = new HSSFWorkbook(fs);
                var firstSheet = oldbook.GetSheetAt(0);
                System.Collections.IEnumerator rows = firstSheet.GetRowEnumerator();
                //遍历每一行
                while (rows.MoveNext())
                {
                    HSSFRow row = (HSSFRow)rows.Current;
                    //TODO::Create DataTable row

                    for (int i = 0; i < row.LastCellNum; i++)
                    {
                        HSSFCell cell = (HSSFCell)row.GetCell(i);
                        FillIntoModel((HSSFSheet)modelSheet, cell);
                    }
                }
            }
        }

        public void TestAddToExistingFile()
        {
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
            HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
            HSSFShapeGroup group1 = patriarch.CreateGroup(new HSSFClientAnchor());
            HSSFShapeGroup group2 = patriarch.CreateGroup(new HSSFClientAnchor());

            group1.SetCoordinates(1, 2, 3, 4);
            group2.SetCoordinates(5, 6, 7, 8);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sh.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(patriarch.Children.Count, 2);

            HSSFShapeGroup group3 = patriarch.CreateGroup(new HSSFClientAnchor());
            group3.SetCoordinates(9, 10, 11, 12);

            wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
            sh = wb.GetSheetAt(0) as HSSFSheet;
            patriarch = sh.DrawingPatriarch as HSSFPatriarch;

            Assert.AreEqual(patriarch.Children.Count, 3);
        }

 /// <summary>
 /// 由Excel导入DataTable
 /// </summary>
 /// <param name="excelFileStream">Excel文件流</param>
 /// <param name="sheetName">Excel工作表索引</param>
 /// <param name="headerRowIndex">Excel表头行索引</param>
 /// <returns>DataTable</returns>
 public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex, int headerColIndex = 0)
 {
     HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
     HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
     DataTable table = new DataTable();
     HSSFRow headerRow = (HSSFRow)sheet.GetRow(headerRowIndex);
     int cellCount = headerRow.LastCellNum;
     for (int i = headerColIndex; i < cellCount; i++)
     {
         if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
         {
             // 如果遇到第一个空列，则不再继续向后读取
             cellCount = i + headerColIndex - 1;
             break;
         }
         DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
         table.Columns.Add(column);
     }
     for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
     {
         HSSFRow row = (HSSFRow)sheet.GetRow(i);
         DataRow dataRow = table.NewRow();
         for (int j = headerColIndex; j < cellCount; j++)
         {
             dataRow[j - headerColIndex] = row.GetCell(j) == null ? string.Empty : row.GetCell(j).ToString();
         }
         table.Rows.Add(dataRow);
     }
     excelFileStream.Close();
     workbook = null;
     sheet = null;
     return table;
 }

        /*
        * 响应到客户端
        *
        * Param fileName 文件名
        */
        public static void WriteToClient(String fileName, HSSFWorkbook workbook)
        {
            //Write the stream data of workbook to the root directory
            //FileStream file = new FileStream(@"c:/test.xls", FileMode.Create);
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.ClearContent();
            HttpContext.Current.Response.ClearHeaders();

            HttpContext.Current.Response.Buffer = true;
            HttpContext.Current.Response.Expires = 0;
            HttpContext.Current.Response.CacheControl = "no-cache";

            HttpContext.Current.Response.ContentType = "application/x-excel";
            //inline
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + UTF_FileName(fileName));
            HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
            workbook.Write(HttpContext.Current.Response.OutputStream);

            Sheet sheet = workbook.GetSheetAt(0);
            sheet = null;
            workbook.Dispose();
            workbook = null;

            HttpContext.Current.Response.Flush();
            HttpContext.Current.Response.End();
            //file.Close();
        }

        static void Main(string[] args)
        {
            //Excel worksheet combine example
            //You will be prompted to select two Excel files. test.xls will be created that combines the sheets
			//Note: This example does not check for duplicate sheet names. Your test files should have different sheet names.
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel document (*.xls)|*.xls";
            ofd.Title = "Select first Excel document";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                HSSFWorkbook book1 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
                ofd.Title = "Select second Excel document";
                if (ofd.ShowDialog() == DialogResult.OK)
                {
                    HSSFWorkbook book2 = new HSSFWorkbook(new FileStream(ofd.FileName, FileMode.Open));
                    HSSFWorkbook product = new HSSFWorkbook();

                    for (int i = 0; i < book1.NumberOfSheets; i++)
                    {
                        HSSFSheet sheet1 = book1.GetSheetAt(i) as HSSFSheet;
                        sheet1.CopyTo(product, sheet1.SheetName, true, true);
                    }
                    for (int j = 0; j < book2.NumberOfSheets; j++)
                    {
                        HSSFSheet sheet2 = book2.GetSheetAt(j) as HSSFSheet;
                        sheet2.CopyTo(product, sheet2.SheetName, true, true);
                    }
                    product.Write(new FileStream("test.xls", FileMode.Create, FileAccess.ReadWrite));
                }
                
            }
        }

        public void Test27349()
        {
            // 27349-vLookupAcrossSheets.xls is bugzilla/attachment.cgi?id=10622
            Stream is1 = HSSFTestDataSamples.OpenSampleFileStream("27349-vLookupAcrossSheets.xls");
            HSSFWorkbook wb;
            try
            {
                // original bug may have thrown exception here, or output warning to
                // stderr
                wb = new HSSFWorkbook(is1);
            }
            catch (IOException e)
            {
                throw new SystemException(e.Message);
            }

            ISheet sheet = wb.GetSheetAt(0);
            IRow row = sheet.GetRow(1);
            ICell cell = row.GetCell(0);

            // this defInitely would have failed due to 27349
            Assert.AreEqual("VLOOKUP(1,'DATA TABLE'!$A$8:'DATA TABLE'!$B$10,2)", cell
                    .CellFormula);

            // We might as well Evaluate the formula
            HSSFFormulaEvaluator fe = new HSSFFormulaEvaluator(wb);
            CellValue cv = fe.Evaluate(cell);

            Assert.AreEqual(CellType.Numeric, cv.CellType);
            Assert.AreEqual(3.0, cv.NumberValue, 0.0);
        }