C# HSSFWorkbook.GetSheet方法代码示例

        /// <summary>
        /// 读取文件获取合同信息
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public static List<Contract> getContractsFromExcel(string fileName)
        {
            tFileName = fileName;
            List<Contract> list = new List<Contract>();

            FileStream f = new FileStream(fileName,FileMode.OpenOrCreate);
            Workbook workbook = new HSSFWorkbook(f);

            Sheet sheet = workbook.GetSheet("合同信息表");
            Row headerRow = sheet.GetRow(0);

            //判断是否是正确的协议存储文件
            if (headerRow.GetCell(0).ToString() != "协议类型")
            {
                statusLab = "Excel文件：" + fileName + " 不是正确的合同信息数据文件。";
                return null;
            }

            //读取协议内容
            for (int i = 1; i <= sheet.LastRowNum; i++)
            {
                Row dataRow = sheet.GetRow(i);
                Contract con = getContractFromRow(dataRow);

                if (con != null)
                {
                    list.Add(con);
                }
            }

            f.Close();
            f.Dispose();

            return list;
        }

        static void Main(string[] args)
        {
            string strFilePath = string.Format("D:\\1.xls");

            HSSFWorkbook wk;
            using (FileStream fs = new FileStream(strFilePath, FileMode.Open, FileAccess.ReadWrite))
            {
                wk = new HSSFWorkbook(fs);
            }
            HSSFSheet hst;
            hst = (HSSFSheet)wk.GetSheet("執行情形統計表(里辦公處)");
            HSSFRow hr;

            int dlastrow = 0; dlastrow = hst.LastRowNum;
            #region Get Sheet Name

            string strSheetname = hst.SheetName;

            #endregion Get Sheet Name
            hr = (HSSFRow)hst.GetRow(0);
            int dLastNum = hr.LastCellNum;
            for (int j = 1; j <= hst.LastRowNum; j++)
            {
                hr = (HSSFRow)hst.GetRow(j);
                for (int i = 0; i < dLastNum; i++)//Column
                {
                    string strcell = hr.GetCell(i) == null ? "0" : hr.GetCell(i).ToString();
                }
            }
        }

        private static bool addRecord(float data)
        {
            try
            {
                //close the FileStream before specifying any changes in the HSSFSheet object
                FileStream fs = new FileStream(pathExcel, FileMode.Open, FileAccess.ReadWrite);
                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs);
                HSSFSheet sheet = (HSSFSheet)templateWorkbook.GetSheet("Arkusz1");
                fs.Close();

                DateTime current = DateTime.Now;
                DateTime thisDay = DateTime.Today;

                Row row = sheet.CreateRow(sheet.LastRowNum + 1);
                row.CreateCell(0).SetCellValue(data);
                row.CreateCell(1).SetCellValue(DateTime.Now.ToShortTimeString());
                row.CreateCell(2).SetCellValue(thisDay.ToString("d"));

                //reinitialize data before having the workbook object write to the Stream
                sheet.ForceFormulaRecalculation = true;
                fs = new FileStream(pathExcel, FileMode.Open, FileAccess.ReadWrite);
                templateWorkbook.Write(fs);
                fs.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

    static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
    {
        foreach (string asset in importedAssets)
        {
            if (!filePath.Equals(asset))
                continue;

            using (FileStream stream = File.Open (filePath, FileMode.Open, FileAccess.Read))
            {
                var book = new HSSFWorkbook(stream);

                foreach (string sheetName in sheetNames)
                {
                    var exportPath = "Assets/Resources/Data/" + sheetName + ".asset";
                    
                    // check scriptable object
                    var data = (Entity_daimyo_mst)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_daimyo_mst));
                    if (data == null)
                    {
                        data = ScriptableObject.CreateInstance<Entity_daimyo_mst>();
                        AssetDatabase.CreateAsset((ScriptableObject)data, exportPath);
                        data.hideFlags = HideFlags.NotEditable;
                    }
                    data.param.Clear();

					// check sheet
                    var sheet = book.GetSheet(sheetName);
                    if (sheet == null)
                    {
                        Debug.LogError("[QuestData] sheet not found:" + sheetName);
                        continue;
                    }

                	// add infomation
                    for (int i=1; i<= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        ICell cell = null;
                        
                        var p = new Entity_daimyo_mst.Param();
			
					cell = row.GetCell(0); p.daimyoId = (int)(cell == null ? 0 : cell.NumericCellValue);
					cell = row.GetCell(1); p.daimyoName = (cell == null ? "" : cell.StringCellValue);
					cell = row.GetCell(2); p.colorR = (int)(cell == null ? 0 : cell.NumericCellValue);
					cell = row.GetCell(3); p.colorG = (int)(cell == null ? 0 : cell.NumericCellValue);
					cell = row.GetCell(4); p.colorB = (int)(cell == null ? 0 : cell.NumericCellValue);
					cell = row.GetCell(5); p.busyoId = (int)(cell == null ? 0 : cell.NumericCellValue);
					cell = row.GetCell(6); p.senryoku = (int)(cell == null ? 0 : cell.NumericCellValue);

                        data.param.Add(p);
                    }
                    
                    // save scriptable object
                    ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject;
                    EditorUtility.SetDirty(obj);
                }
            }

        }
    }

        public static DataTable RenderDataTableFromExcel(Stream ExcelFileStream, string SheetName, int HeaderRowIndex)
        {
            HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
            HSSFSheet sheet = workbook.GetSheet(SheetName);

            DataTable table = new DataTable();

            HSSFRow headerRow = sheet.GetRow(HeaderRowIndex);
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                table.Columns.Add(column);
            }

            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i);
                DataRow dataRow = table.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                    dataRow[j] = row.GetCell(j).ToString();
            }

            ExcelFileStream.Close();
            workbook = null;
            sheet = null;
            return table;
        }

 public override void ExecuteResult(ControllerContext context)
 {
     var fs = new FileStream(context.HttpContext.Server.MapPath(
         @"\Content\UpdatePeople.xls"), FileMode.Open, FileAccess.Read);
     var wb = new HSSFWorkbook(fs, true);
     var sheet = wb.GetSheet("Sheet1");
     var r = 1;
     foreach (var p in UpdatePeopleRows())
     {
         var row = sheet.CreateRow(r++);
         var c = 0;
         row.CreateCell(c++).SetCellValue(p.PeopleId);
         row.CreateCell(c++).SetCellValue(p.Title);
         row.CreateCell(c++).SetCellValue(p.First);
         row.CreateCell(c++).SetCellValue(p.GoesBy);
         row.CreateCell(c++).SetCellValue(p.Last);
         row.CreateCell(c++).SetCellValue(p.Suffix);
         row.CreateCell(c++).SetCellValue(p.Email1);
         row.CreateCell(c++).SetCellValue(p.Email2);
         row.CreateCell(c++).SetCellValue(p.Gender);
         if (p.BirthDate.HasValue)
             row.CreateCell(c++).SetCellValue(p.BirthDate.Value);
         else
             row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
         if (p.Anniversary.HasValue)
             row.CreateCell(c++).SetCellValue(p.Anniversary.Value);
         else
             row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
         if (p.Joined.HasValue)
             row.CreateCell(c++).SetCellValue(p.Joined.Value);
         else
             row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
         row.CreateCell(c++).SetCellValue(p.Cell);
         row.CreateCell(c++).SetCellValue(p.Work);
         row.CreateCell(c++).SetCellValue(p.Member);
         if (p.Grade.HasValue)
             row.CreateCell(c++).SetCellValue(p.Grade.Value);
         else
             row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
         row.CreateCell(c++).SetCellValue(p.Marital);
         row.CreateCell(c++).SetCellValue(p.FamilyPos);
         row.CreateCell(c++).SetCellValue(p.AltName);
         row.CreateCell(c++).SetCellValue(p.Campus);
         row.CreateCell(c++).SetCellValue(p.School);
         row.CreateCell(c++).SetCellValue(p.Occupation);
         row.CreateCell(c++).SetCellValue(p.Employer);
         if (p.Deceased.HasValue)
             row.CreateCell(c++).SetCellValue(p.Deceased.Value);
         else
             row.CreateCell(c++, NPOI.SS.UserModel.CellType.Blank);
     }
     var Response = context.HttpContext.Response;
     Response.Buffer = true;
     Response.ContentType = "application/vnd.ms-excel";
     Response.AddHeader("Content-Disposition", "attachment;filename=UpdatePeople.xls");
     Response.Charset = "";
     wb.Write(Response.OutputStream);
 }

    static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
    {
        foreach (string asset in importedAssets) {
            if (!filePath.Equals (asset))
                continue;

            Entity_Job data = (Entity_Job)AssetDatabase.LoadAssetAtPath (exportPath, typeof(Entity_Job));
            if (data == null) {
                data = ScriptableObject.CreateInstance<Entity_Job> ();
                AssetDatabase.CreateAsset ((ScriptableObject)data, exportPath);
                data.hideFlags = HideFlags.NotEditable;
            }

            data.sheets.Clear ();
            using (FileStream stream = File.Open (filePath, FileMode.Open, FileAccess.Read)) {
                IWorkbook book = new HSSFWorkbook (stream);

                foreach(string sheetName in sheetNames) {
                    ISheet sheet = book.GetSheet(sheetName);
                    if( sheet == null ) {
                        Debug.LogError("[QuestData] sheet not found:" + sheetName);
                        continue;
                    }

                    Entity_Job.Sheet s = new Entity_Job.Sheet ();
                    s.name = sheetName;

                    for (int i=1; i<= sheet.LastRowNum; i++) {
                        IRow row = sheet.GetRow (i);
                        ICell cell = null;

                        Entity_Job.Param p = new Entity_Job.Param ();

                    cell = row.GetCell(0); p.id = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(1); p.name = (cell == null ? "" : cell.StringCellValue);
                    cell = row.GetCell(2); p.lv = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(3); p.difficult = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(4); p.attack = (float)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(5); p.defens = (float)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(6); p.sp = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(7); p.cooltime = (float)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(8); p.casttime = (float)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(9); p.effect = (float)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(10); p.point = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(11); p.bonus = (float)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(12); p.type = (int)(cell == null ? 0 : cell.NumericCellValue);
                        s.list.Add (p);
                    }
                    data.sheets.Add(s);
                }
            }

            ScriptableObject obj = AssetDatabase.LoadAssetAtPath (exportPath, typeof(ScriptableObject)) as ScriptableObject;
            EditorUtility.SetDirty (obj);
        }
    }

        public string ReadExcle(string path = @"~/up/b.xls", int from = 1)
        {
            HSSFWorkbook _book = new HSSFWorkbook();
            string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path);

            FileStream file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read);
            IWorkbook hssfworkbook = new HSSFWorkbook(file);
            ISheet sheet = hssfworkbook.GetSheet("Sheet1");
            string guid = Guid.NewGuid().ToString();
            string saveFileName = xlsPath.Path(guid);

            StringBuilder sb2 = new StringBuilder();

            string courty = "";

            //获取sheet的首行
            var headerRow = sheet.GetRow(0);

            //一行最后一个方格的编号 即总的列数
            int cellCount = headerRow.LastCellNum;

            //for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            //{

            //}

            //最后一列的标号  即总的行数
            int rowCount = sheet.LastRowNum;

            for (int i = 0; i <= sheet.LastRowNum; i++)
            {

                var row = sheet.GetRow(i);

                if (row != null)
                {
                    courty = row.GetCell(0).ToString();
                    if (!string.IsNullOrWhiteSpace(courty))
                    {
                        courty = courty.Split(' ')[0];
                        sb2.Append(string.Format("'{0}',", courty));

                    }
                }

            }
            var da = sb2.ToString();

            hssfworkbook = null;
            sheet = null;

            //一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
            return string.Format("../../up/{0}.xls", guid);
            //记录日志
        }

        public void AddExcelUserNPOI()
        {
            FileStream file = new FileStream(@"d:\temp\test.xls", FileMode.Open, FileAccess.Read);
            HSSFWorkbook hssfWork = new HSSFWorkbook(file);
            ISheet iSheet = hssfWork.GetSheet("new sheet");
            iSheet.CreateRow(iSheet.LastRowNum + 1).CreateCell(0).SetCellValue("uuuu");

            FileStream fss = new FileStream(@"d:\temp\test.xls", FileMode.Create);
            hssfWork.Write(fss);
            file.Close();
        }

    /// <summary>
    /// Excel文档流转换成DataTable
    /// </summary>
    /// <param name="excelFileStream">Excel文档流</param>
    /// <param name="sheetName">表名称</param>
    /// <param name="headerRowIndex">标题行索引号，如第一行为0</param>
    /// <returns></returns>
    public static DataTable RenderFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
    {
        DataTable table = null;

        using (excelFileStream)
        {
            IWorkbook workbook = new HSSFWorkbook(excelFileStream);
            ISheet sheet = workbook.GetSheet(sheetName);
            table = RenderFromExcel(sheet, headerRowIndex);
        }
        return table;
    }

 public static void Init()
 {
     try
     {
         File = new FileStream(dataFolder + @"\DataSpelers.xls", FileMode.Open, FileAccess.Read);
         Workbook = new HSSFWorkbook(File);
         Sheet = Workbook.GetSheet("Users");
     }
     catch (IOException)
     {
         MessageBox.Show("DataSpelers.xls is open, gelieve deze te sluiten en het programma opnieuw op te starten.", "Excel lees fout");
         Environment.Exit(0);
     }
 }

    static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
    {
        foreach (string asset in importedAssets) {
            if (!filePath.Equals (asset))
                continue;

            QuestRarityData data = (QuestRarityData)AssetDatabase.LoadAssetAtPath (exportPath, typeof(QuestRarityData));
            if (data == null) {
                data = ScriptableObject.CreateInstance<QuestRarityData> ();
                AssetDatabase.CreateAsset ((ScriptableObject)data, exportPath);
                data.hideFlags = HideFlags.NotEditable;
            }

            data.sheets.Clear ();
            using (FileStream stream = File.Open (filePath, FileMode.Open, FileAccess.Read)) {
                IWorkbook book = new HSSFWorkbook (stream);

                foreach(string sheetName in sheetNames) {
                    ISheet sheet = book.GetSheet(sheetName);
                    if( sheet == null ) {
                        Debug.LogError("[QuestData] sheet not found:" + sheetName);
                        continue;
                    }

                    QuestRarityData.Sheet s = new QuestRarityData.Sheet ();
                    s.name = sheetName;

                    for (int i=1; i< sheet.LastRowNum; i++) {
                        IRow row = sheet.GetRow (i);
                        ICell cell = null;

                        QuestRarityData.Param p = new QuestRarityData.Param ();

                    cell = row.GetCell(0); p.kind = (cell == null ? "" : cell.StringCellValue);
                    cell = row.GetCell(1); p.id = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(2); p.name = (cell == null ? "" : cell.StringCellValue);
                    cell = row.GetCell(3); p.minStep = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(4); p.maxStep = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(5); p.rarity = (int)(cell == null ? 0 : cell.NumericCellValue);
                    cell = row.GetCell(6); p.rarityType = (int)(cell == null ? 0 : cell.NumericCellValue);
                        s.list.Add (p);
                    }
                    data.sheets.Add(s);
                }
            }

            ScriptableObject obj = AssetDatabase.LoadAssetAtPath (exportPath, typeof(ScriptableObject)) as ScriptableObject;
            EditorUtility.SetDirty (obj);
        }
    }

        public FileResult aj_excelEquipmentDown(q_Equipment_Chiller q)
        {
            HSSFWorkbook wbXLS;
            FileStream fileStream = null;
            MemoryStream outputStream = new MemoryStream();
            try
            {
                db0 = getDB0();
                fileStream = new FileStream(Server.MapPath("~/_Code/RPTExcel/Equipment.xls"), FileMode.Open, FileAccess.ReadWrite);
                wbXLS = new HSSFWorkbook(fileStream);
                ISheet getSheet = wbXLS.GetSheet("設備表");

                Apply_User getUserData = null;
                if (this.UserId != null)
                {
                    getUserData = db0.Apply_User.Where(x => x.USERID == this.UserId).FirstOrDefault();
                }

                SetCellValue(getSheet, "C3", getUserData.USERNAME);
                SetCellValue(getSheet, "E2", DateTime.Now.ToString("yyyy/MM/dd"));
                SetCellValue(getSheet, "E3", getUserData.USERID);

                wbXLS.Write(outputStream);

                outputStream.Position = 0;
                string setFileName = this.UserId + "-01-設備-" + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";

                if (Request.Browser.Browser == "IE" && Request.Browser.Version == "8.0")
                {
                    byte[] bytes = outputStream.ToArray();
                    Response.Clear();
                    Response.ContentType = "application/xls";
                    Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(setFileName));
                    Response.BinaryWrite(bytes);
                    Response.End();
                }

                return File(outputStream, "application/vnd.ms-excel", setFileName);
            }
            catch (Exception ex)
            {
                Log.Write(ex.Message + ex.StackTrace);
                return null;
            }
            finally
            {
                db0.Dispose();
            }
        }

        public static Sheet GetSheet(HSSFWorkbook workbook, string sheetName)
        {
            if (workbook != null)
            {
                var sheet = workbook.GetSheet(sheetName);
                if (sheet != null)
                {
                    return sheet;
                }

                return workbook.CreateSheet(sheetName);
            }

            return null;
        }

        public string ReadExcle(string path = @"~/up/a.xls", int from = 1)
        {
            HSSFWorkbook _book = new HSSFWorkbook();
            string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path);

            FileStream file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read);
            IWorkbook hssfworkbook = new HSSFWorkbook(file);
            ISheet sheet = hssfworkbook.GetSheet("Sheet1");
            string guid = Guid.NewGuid().ToString();
            string saveFileName = xlsPath.Path(guid);

            StringBuilder sb = new StringBuilder();
            string temp="";
            //获取sheet的首行
            var headerRow = sheet.GetRow(0);

            //一行最后一个方格的编号 即总的列数
            int cellCount = headerRow.LastCellNum;

            for (int i = headerRow.FirstCellNum; i < cellCount; i++)
            {
                DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);

            }
            //最后一列的标号  即总的行数
            int rowCount = sheet.LastRowNum;

            for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        sb.Append( row.GetCell(j).ToString());
                }

            }

            hssfworkbook = null;
            sheet = null;

            //一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
            return string.Format("../../up/{0}.xls", guid);
            //记录日志
        }