C# IWorkbook.GetSheet方法代码示例

 public static byte[] ExportToBuffer(IWorkbook workbook, params SheetFormatterContainer[] containers)
 {
     foreach (var container in containers)
     {
         var sheet = workbook.GetSheet(container.SheetName);
         var context = new SheetFormatterContext(sheet, container.Formatters);
         context.Format();
     }
     return workbook.SaveToBuffer();
 }

        internal void LoadFromCobie(IWorkbook workbook, TextWriter log, string version = "UK2012")
        {
            //fill in object attributes first
            var sheetName = GetSheetName(GetType(), version);
            if (sheetName == null)
            {
                log.WriteLine("There is no sheet maping for a {0}.", GetType().Name);
                return;
            }

            //try to get object sheet
            var sheet = workbook.GetSheet(sheetName);
            if (sheet == null)
            {
                log.WriteLine("There is no {0} sheet for a {1}.", sheetName, GetType().Name);
                return;
            }

            //get mappings
            var mappingAttributes = GetMapping(GetType(), version);
            var mappings = mappingAttributes as MappingAttribute[] ?? mappingAttributes.ToArray();
            if (!mappings.Any())
            {
                log.WriteLine("There is no mapping for a {0} parameters", GetType().Name);
                return;
            }

            //fill facility values using reflection
            foreach (var mapping in mappings)
            {
                const int cellIndex = 1;
                int cellRow;
                if (!int.TryParse(mapping.Column, out cellRow))
                {
                    log.WriteLine(
                        "Metadata are expected to be defined in a single column with numbered rows. This mapping ({0}) doesn't contain row number.",
                        version);
                    continue;
                }
                cellRow--; //convert number to index

                var row = sheet.GetRow(cellRow);
                if (row == null) continue;

                var cell = row.GetCell(cellIndex);
                if (cell == null) continue;

                //use reflection to set the value if the value is available
                if (cell.CellType == CellType.Blank || cell.CellType == CellType.Error)
                    continue;

                SetMemberValue(mapping.Path, cell, log);
            }
        }

 /// <summary>
 /// 格式化
 /// </summary>
 /// <param name="workbook">工作薄</param>
 public void Format(IWorkbook workbook)
 {
     ISheet sheet = workbook.GetSheet(SheetName);
     if (!sheet.IsNull() && !FormatterList.IsNullOrEmpty())
     {
         var sheetAdapter = new SheetAdapter(sheet);
         foreach (ElementFormatter formatter in FormatterList)
         {
             formatter.Format(sheetAdapter);
         }
     }
 }

        internal void WriteToCobie(IWorkbook workbook, TextWriter log, string version = "UK2012")
        {
            var mappings = GetMapping(GetType(), version).ToList();
            if (!mappings.Any())
            {
                log.WriteLine("There are no mappings for a type '{0}'", GetType().Name);
                return;
            }

            //get or create a sheet
            var sheetName = GetSheetName(GetType(), version);
            var sheet = workbook.GetSheet(sheetName) ?? workbook.CreateSheet(sheetName);

            //write columns
            foreach (var mapping in mappings)
            {
            
                int rowNum;
                if (!int.TryParse(mapping.Column, out rowNum))
                {
                    log.WriteLine(
                        "Metadata are expected to be defined in a single column with numbered rows. This mapping ({0}) doesn't contain row number.",
                        version);
                    continue;
                }
                rowNum--; //convert number to index

                var row = sheet.GetRow(rowNum) ?? sheet.CreateRow(rowNum);
                
                //header cell;
                var headerCell = row.GetCell(0) ?? row.CreateCell(0);
                if(headerCell.CellType == CellType.Blank)
                    headerCell.SetCellValue(mapping.Header);

                //value cell
                var cell = row.GetCell(1) ?? row.CreateCell(1);
                var info = GetType().GetProperty(mapping.Path);
                if (info == null)
                {
                    log.WriteLine("Property {0} is not defined in {1}", mapping.Path, GetType().Name);
                    continue;
                }
                var value = (info.GetValue(this) as string) ?? "n/a";
                cell.SetCellValue(value);
            }
        }

 private void CheckRowCount(IWorkbook wb)
 {
     Assert.IsNotNull(wb);
     ISheet sh = wb.GetSheet("Sheet 1");
     Assert.IsNotNull(sh);
     Assert.AreEqual(ROW_COUNT - 1, sh.LastRowNum);
 }

        /**
         * Read the flags from the workbook.  Flags are on the sheet named "Flags",
         * and consist of names in column A and values in column B.  These are Put
         * into a map that can be queried later.
         *
         * @param wb The workbook to look in1.
         */
        private void ReadFlags(IWorkbook wb)
        {
            ISheet flagSheet = wb.GetSheet("Flags");
            testFlags = new Dictionary<String, String>();
            if (flagSheet != null)
            {
                int end = flagSheet.LastRowNum;
                // Skip the header row, therefore "+ 1"
                for (int r = flagSheet.FirstRowNum + 1; r <= end; r++)
                {
                    IRow row = flagSheet.GetRow(r);
                    if (row == null)
                        continue;
                    String flagName = row.GetCell(0).StringCellValue;
                    String flagValue = row.GetCell(1).StringCellValue;
                    if (flagName.Length > 0)
                    {
                        testFlags.Add(flagName, flagValue);
                    }
                }
            }

            tryAllColors = flagBoolean("AllColors", true);
        }

        private void GenerateReport(IWorkbook workbook)
        {
            JobTracker jobtracker = new JobTracker();
            List<JobTracker> projectList = jobtracker.GetDistinctProjectListIncludingForApproval(Convert.ToDateTime(txtBoxFrom.Text+ " 00:00:00"), Convert.ToDateTime(txtBoxTo.Text+" 23:59:59"));
            int currentrow = 1;

            IDataFormat format = workbook.CreateDataFormat();
            ICellStyle fontBoldNoBorder = CreateSheetStyle(workbook, false, false, false, false, true, false, false, false,false);
            ICellStyle fontBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
            ICellStyle fontCurrencyBoldRigthBottom = CreateSheetStyle(workbook, false, false, true, true, true, false, false, false);
            fontCurrencyBoldRigthBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
            ICellStyle fontBoldTopBottom = CreateSheetStyle(workbook, false, true, false, true, true, false, false, false);
            fontCurrencyBoldRigthBottom.Alignment = HorizontalAlignment.Center;
            fontCurrencyBoldRigthBottom.VerticalAlignment = VerticalAlignment.Center;
            ICellStyle fontCurrencyBoldAllBorder = CreateSheetStyle(workbook, true, true, true, true, true, false, false, false);
            fontBoldAllBorder.Alignment = HorizontalAlignment.Center;
            fontCurrencyBoldAllBorder.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
            ICellStyle fontNormalBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
            fontBoldAllBorder.VerticalAlignment = VerticalAlignment.Center;
            ICellStyle fontNormalBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true,true, false, false, false, false);
            ICellStyle fontCurrencyBorderLeftRight = CreateSheetStyle(workbook, true, false, true, false, false, false, false, false);
            fontCurrencyBorderLeftRight.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
            ICellStyle fontCurrencyBorderLeftRightBottom = CreateSheetStyle(workbook, true, false, true, true, false, false, false, false);
            fontCurrencyBorderLeftRightBottom.DataFormat = format.GetFormat("$#,##0.00_);[Red]($#,##0.00);\"-\"");
            //fontNormalBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
            //fontNormalBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //fontNormalBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
            //fontCurrencyBorderLeftRightBottom.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
            //fontCurrencyBorderLeftRightBottom.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
            //fontCurrencyBorderLeftRightBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;

            //fontCurrencyBoldRigthBottom.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;

            ISheet sheetReport = workbook.GetSheet("Report");
            foreach (JobTracker project in projectList)
            {
                //Running Total Variable
                double runningNTApproved = 0;
                double runningNTForApproval = 0;
                double runningOTApproved = 0;
                double runningOTForApproval = 0;

                double runningNTApprovedCost = 0;
                double runningNTForApprovalCost = 0;
                double runningOTApprovedCost = 0;
                double runningOTForApprovalCost = 0;

                string jobheader = project.HWNo == null ? "" : project.HWNo.Trim() == "" ? "" : "HW SO: " + project.HWNo;
                jobheader += project.SWNo == null ? "" : project.SWNo.Trim() == "" ? "" : jobheader == "" ? "SW SO: " + project.SWNo : "; SW SO: " + project.SWNo;
                jobheader += project.EvalNo == null ? "" : project.EvalNo.Trim() == "" ? "" : jobheader == "" ? "EVAL NO: " + project.EvalNo : "; EVAL NO: " + project.EvalNo;
                currentrow += 2;
                IRow row = sheetReport.CreateRow(currentrow++);
                ICell cell = row.CreateCell(0);
                cell.CellStyle = fontBoldAllBorder;
                cell.SetCellValue(jobheader);
                for (int i = 1; i < 11; i++)
                {
                    cell = row.CreateCell(i);
                    cell.CellStyle = fontBoldAllBorder;
                }

                #region Header Upper Row
                row = sheetReport.CreateRow(currentrow);
                cell = row.CreateCell(0);
                cell.CellStyle = fontBoldAllBorder;
                cell.SetCellValue("JobStage");
                cell = row.CreateCell(1);
                cell.CellStyle = fontBoldAllBorder;
                cell.SetCellValue("Time");
                for (int i = 2; i < 5; i++)
                {
                    cell = row.CreateCell(i);
                    cell.CellStyle = fontBoldAllBorder;
                }

                cell = row.CreateCell(5);
                cell.CellStyle = fontBoldAllBorder;
                cell.SetCellValue("Labor Cost");
                for (int i = 6; i < 9; i++)
                {
                    cell = row.CreateCell(i);
                    cell.CellStyle = fontBoldAllBorder;
                }
                cell = row.CreateCell(9);
                cell.CellStyle = fontBoldAllBorder;
                cell.SetCellValue("Total");
                cell = row.CreateCell(10);
                cell.CellStyle = fontBoldAllBorder;

                //Merging
                sheetReport.AddMergedRegion(new CellRangeAddress(currentrow -1, currentrow-1, 0, 10));
                sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,1,4));
                sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,5,8));
                sheetReport.AddMergedRegion(new CellRangeAddress(currentrow,currentrow,9,10));

                #endregion

                #region Header Lower Row
                row = sheetReport.CreateRow(++currentrow);

//.........这里部分代码省略.........

        private static Int32 WriteDataToSheet(Object data, ref IWorkbook wBook, String sheetName, Int32 startColumn, Int32 startRow, out Int32 maxRow)
        {
            Int32 result = 0; // row affected
            Int32 currentRow = startRow;
            Int32 currentColumn = startColumn;

            maxRow = 0;

            // check whether the sheet is already exis or not
            if (data != null && wBook != null && !String.IsNullOrWhiteSpace(sheetName))
            {
                if (wBook.GetSheetIndex(sheetName) < 0)
                    wBook.CreateSheet(sheetName);

                ISheet sheet = wBook.GetSheet(sheetName);

                if (sheet != null)
                {
                    if (data.GetType().GetMethod("GetEnumerator") != null )
                    {
                        IEnumerator enumerator = (IEnumerator) data.GetType().GetMethod("GetEnumerator").Invoke(data, null);
                        Int32 count = (Int32) data.GetType().GetProperty("Count").GetValue(data);

                        Int32 i = 0;

                        while (enumerator.MoveNext())
                        {
                            currentColumn = WriteDataToSheet(enumerator.Current, ref wBook, sheetName, currentColumn, currentRow, out currentRow);

                            if (i < count - 1)
                            {
                                currentRow++;
                                currentColumn = startColumn;
                            }

                            if (currentRow > maxRow)
                                maxRow = currentRow;
                            else
                                currentRow = maxRow;

                            i++;
                        }
                    }
                    else
                    {
                        PropertyInfo [] propInfos = data.GetType().GetProperties();

                        if (propInfos != null && propInfos.Count() > 0)
                        {
                            foreach (var propInfo in propInfos)
                            {
                                ColumnName colNameAttribute = propInfo.GetCustomAttribute<ColumnName>(true);
                                Skipped skippedAttribute = propInfo.GetCustomAttribute<Skipped>(true);
                                String fieldFormat = (propInfo.GetCustomAttribute<DateFormat>(true) != null) ? propInfo.GetCustomAttribute<DateFormat>(true).Format : "";
                                String columnName = "";
                                String fieldName = propInfo.Name;
                                Type fieldType = propInfo.PropertyType;
                                Object fieldValue = propInfo.GetValue(data);

                                if (skippedAttribute == null || (skippedAttribute != null && !skippedAttribute.IsSkipped(sheetName)))
                                {
                                    if (!SystemTypes.Contains(fieldType) && !fieldType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                                    {
                                        Int32 curMaxRow = 0;
                                        currentColumn = WriteDataToSheet(fieldValue, ref wBook, sheetName, currentColumn, currentRow, out curMaxRow);

                                        if (curMaxRow > maxRow)
                                            maxRow = curMaxRow;
                                    }
                                    else
                                    {
                                        // Get Attributes[

                                        if (colNameAttribute != null)
                                            columnName = colNameAttribute.Name;
                                        else
                                            columnName = propInfo.Name;

                                        // write header if this is the first row
                                        if (currentRow == 0)
                                            WriteToCell(ref sheet, currentRow, currentColumn, columnName);

                                        // write the data
                                        if (currentRow == 0)
                                            WriteToCell(ref sheet, currentRow + 1, currentColumn, fieldValue, fieldFormat);
                                        else
                                            WriteToCell(ref sheet, currentRow, currentColumn, fieldValue, fieldFormat);

                                        currentColumn++;

                                    }
                                }
                            }

                            // add current row
                            if (currentRow == 0)
                                currentRow = 1;  // we must write header and the first data row

                            if (currentRow > maxRow)
                                maxRow = currentRow;
//.........这里部分代码省略.........

 protected virtual ISheet GetSheet(IWorkbook workbook, string sheetName)
 {
     return workbook.GetSheet(sheetName);
 }

        /// <summary>
        /// 从Excel中获取数据到DataTable
        /// </summary>
        /// <param name="workbook">要处理的工作薄</param>
        /// <param name="SheetName">要获取数据的工作表名称</param>
        /// <param name="HeaderRowIndex">工作表标题行所在行号(从0开始)</param>
        /// <returns></returns>
        public static DataTable RenderDataTableFromExcel(IWorkbook workbook, string SheetName, int HeaderRowIndex)
        {
            ISheet sheet = workbook.GetSheet(SheetName);
            DataTable table = new DataTable();
            try
            {
                IRow 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;

                #region 循环各行各列,写入数据到DataTable
                for (int i = (sheet.FirstRowNum + 1); i < sheet.LastRowNum; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; j++)
                    {
                        ICell cell = row.GetCell(j);
                        if (cell == null)
                        {
                            dataRow[j] = null;
                        }
                        else
                        {
                            //dataRow[j] = cell.ToString();
                            switch (cell.CellType)
                            {
                                case CellType.BLANK:
                                    dataRow[j] = null;
                                    break;
                                case CellType.BOOLEAN:
                                    dataRow[j] = cell.BooleanCellValue;
                                    break;
                                case CellType.NUMERIC:
                                    dataRow[j] = cell.ToString();
                                    break;
                                case CellType.STRING:
                                    dataRow[j] = cell.StringCellValue;
                                    break;
                                case CellType.ERROR:
                                    dataRow[j] = cell.ErrorCellValue;
                                    break;
                                case CellType.FORMULA:
                                default:
                                    dataRow[j] = "=" + cell.CellFormula;
                                    break;
                            }
                        }
                    }
                    table.Rows.Add(dataRow);
                    //dataRow[j] = row.GetCell(j).ToString();
                }
                #endregion
            }
            catch (System.Exception ex)
            {
                table.Clear();
                table.Columns.Clear();
                table.Columns.Add("出错了");
                DataRow dr = table.NewRow();
                dr[0] = ex.Message;
                table.Rows.Add(dr);
                return table;
            }
            finally
            {
                //sheet.Dispose();
                workbook = null;
                sheet = null;
            }
            #region 清除最后的空行
            for (int i = table.Rows.Count - 1; i > 0; i--)
            {
                bool isnull = true;
                for (int j = 0; j < table.Columns.Count; j++)
                {
                    if (table.Rows[i][j] != null)
                    {
                        if (table.Rows[i][j].ToString() != "")
                        {
                            isnull = false;
                            break;
                        }
                    }
                }
                if (isnull)
//.........这里部分代码省略.........

        public void Execute()
        {
            //set destination
            if (File.Exists(_settings.ApplicationSettings.FileLocations.Destination))
            {
                File.Delete(_settings.ApplicationSettings.FileLocations.Destination);
            }

            //get source files
            if (File.Exists(_settings.ApplicationSettings.FileLocations.Source))
            {
                _sourceFiles.Add(_settings.ApplicationSettings.FileLocations.Source);
            }
            else if (Directory.Exists(_settings.ApplicationSettings.FileLocations.Source))
            {
                GetSourceFiles(_settings.ApplicationSettings.FileLocations.Source);
            }


            _workbook = new XSSFWorkbook();

            //create sheets
            foreach (var sheet in _settings.Sheets)
            {
                if (_workbook.GetSheet(sheet.Name) == null)
                {
                    _workbook.CreateSheet(sheet.Name);
                }
            }

            var dateStyle = _workbook.CreateCellStyle();
            dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd-MM-yyyy");

            //add columns
            foreach (var sheet in _settings.Sheets)
            {
                var wbsheet = _workbook.GetSheet(sheet.Name);
                sheet.ItemCount = 0;
                foreach (var column in sheet.Elements.Columns)
                {
                    ICell cell = wbsheet.GetOrCreateCell(sheet.HeaderRow, column.HeaderCol);
                    cell.SetCellValue(column.Header);
                    cell.CellStyle = dateStyle;
                    if (column.Width != 0)
                    {
                        wbsheet.SetColumnWidth(column.HeaderCol, column.Width);
                    }
                    wbsheet.GetRow(sheet.HeaderRow).RowStyle = dateStyle;
                }
            }

            //run through all documents
            for (int i = 0; i < _sourceFiles.Count; i++)
            {
                Console.Write(string.Format("{0}...", Path.GetFileName(_sourceFiles[i])));
                var srcworkbook = WorkbookFactory.Create(_sourceFiles[i]);
                foreach (var sheet in _settings.Sheets)
                {
                    var wbsheet = _workbook.GetSheet(sheet.Name);
                    var srcwbsheet = srcworkbook.GetSheet(sheet.Name);
                    if (srcwbsheet != null)
                    {
                        //find first row by searching first column header name
                        var allheaders = (from a in sheet.Elements.Columns select a.Header.ToLower()).ToArray();
                        var indexes = new int?[allheaders.Length];
                        var rowenum = srcwbsheet.GetRowEnumerator();
                        while (rowenum.MoveNext())
                        {
                            var r = rowenum.Current as XSSFRow;
                            IRow ActiveRow = null;
                            var searchHeader = (from a in indexes where a != null select a).FirstOrDefault() == null;
                            if (!searchHeader)
                            {
                                ActiveRow = wbsheet.GetOrCreateRow(sheet.ItemCount + sheet.HeaderRow + 1);
                                sheet.ItemCount++;
                                ActiveRow.Height = r.Height;
                            }
                            foreach (var c in r.Cells)
                            {
                                if (searchHeader)
                                {
                                    var index = Array.IndexOf(allheaders, (c.StringCellValue ?? "").ToLower());
                                    if (index >= 0)
                                    {
                                        indexes[index] = c.ColumnIndex;
                                        if (sheet.ItemCount == 0)
                                        {
                                            if (sheet.Elements.Columns[index].Width == 0)
                                            {
                                                wbsheet.SetColumnWidth(sheet.Elements.Columns[index].HeaderCol, Math.Max(srcwbsheet.GetColumnWidth(c.ColumnIndex), wbsheet.GetColumnWidth(sheet.Elements.Columns[index].HeaderCol)));
                                            }
                                            if (r.RowStyle != null)
                                            {
                                                wbsheet.GetRow(sheet.HeaderRow).RowStyle.CloneStyleFrom(r.RowStyle);
                                            }
                                            wbsheet.GetRow(sheet.HeaderRow).GetCell(sheet.Elements.Columns[index].HeaderCol).CellStyle.CloneStyleFrom(c.CellStyle);
                                        }
                                    }
                                }
                                else
//.........这里部分代码省略.........

        public void Init(string Filename, string SheetName)
        {
            this.TempFile = File.OpenRead(Filename);
            this.Workbook = WorkbookFactory.Create(this.TempFile);

            if (SheetName != "")
            {
                this.CurrSheet = Workbook.GetSheet(SheetName);
                this.CurrRowNum = CurrSheet.FirstRowNum + 1;
                IRow HeaderRow = this.CurrSheet.GetRow(this.CurrSheet.FirstRowNum);
                this.NumCols = HeaderRow.LastCellNum - HeaderRow.FirstCellNum;
            }
        }