C# HSSFSheet.GetRow方法代码示例

    private void SaveOrInsertSheet_Source(HSSFSheet u_sheet)
    {
        Database.MSSQL DB = new Database.MSSQL("Web");
        List<SqlParameter> PMS = new List<SqlParameter>();
        //因為要讀取的資料列不包含標頭，所以i從u_sheet.FirstRowNum + 1開始讀
        /*一列一列地讀取資料*/
        int Company_check = 0;

        for (int i = u_sheet.FirstRowNum + 1; i <= u_sheet.LastRowNum; i++)
        {
            HSSFRow row = (HSSFRow)u_sheet.GetRow(i);//取得目前的資料列 2015/03/18[新增資料區段]

            for (int j = 0; j < Stock_Num.Rows.Count; j++)
            {
                if (Equals(Stock_Num.Rows[j][0], row.GetCell(0).ToString()))
                {
                    Company_check = 1;
                }
            }
            if (Company_check == 1)
            {
                PMS = new List<SqlParameter>();
                PMS.Add(new SqlParameter("@證券代號", row.GetCell(0).ToString()));
                PMS.Add(new SqlParameter("@成交股數", Convert.ToInt32(row.GetCell(2).ToString())));
                PMS.Add(new SqlParameter("@成交筆數", Convert.ToInt32(row.GetCell(3).ToString())));
                PMS.Add(new SqlParameter("@成交金額", Convert.ToSingle(row.GetCell(4).ToString())));
                PMS.Add(new SqlParameter("@開盤價", Convert.ToSingle(row.GetCell(5).ToString())));
                PMS.Add(new SqlParameter("@最高價", Convert.ToSingle(row.GetCell(6).ToString())));
                PMS.Add(new SqlParameter("@最低價", Convert.ToSingle(row.GetCell(7).ToString())));
                PMS.Add(new SqlParameter("@收盤價", Convert.ToSingle(row.GetCell(8).ToString())));
                PMS.Add(new SqlParameter("@最後揭示買價", Convert.ToSingle(row.GetCell(11).ToString())));
                PMS.Add(new SqlParameter("@最後揭示買量", Convert.ToInt32(row.GetCell(12).ToString())));
                PMS.Add(new SqlParameter("@最後揭示賣價", Convert.ToSingle(row.GetCell(13).ToString())));
                PMS.Add(new SqlParameter("@最後揭示賣量", Convert.ToInt32(row.GetCell(14).ToString())));
                PMS.Add(new SqlParameter("@資料日期", Convert.ToDateTime(DataTime_Source_txt.Text)));
                PMS.Add(new SqlParameter("@下載日期", dt));
                DB.ExecutionStoredProcedure("[Source_Insert]", PMS.ToArray());
            }
            Company_check = 0;
        }
    }

    private void SaveOrInsertSheet(HSSFSheet u_sheet)
    {
        Database.MSSQL DB = new Database.MSSQL("Web");
        List<SqlParameter> PMS = new List<SqlParameter>();
        //因為要讀取的資料列不包含標頭，所以i從u_sheet.FirstRowNum + 1開始讀
        /*一列一列地讀取資料*/
        int Company_check = 0;

        for (int i = u_sheet.FirstRowNum + 1; i <= u_sheet.LastRowNum; i++)
        {
            HSSFRow row = (HSSFRow)u_sheet.GetRow(i);//取得目前的資料列 2015/03/18[新增資料區段]
            for (int j = 0; j < Stock_Num.Rows.Count; j++ )
            {
                if (Equals(Stock_Num.Rows[j][0], row.GetCell(0).ToString()))
                {
                    Company_check = 1;
                }   
            }
            if (Company_check == 0)
            {
                PMS = new List<SqlParameter>();
                PMS.Add(new SqlParameter("@C_ID", row.GetCell(0).ToString()));
                PMS.Add(new SqlParameter("@C_Name", row.GetCell(1).ToString()));
                DB.ExecutionStoredProcedure("[GetStockNum_Insert]", PMS.ToArray());
            }

            PMS = new List<SqlParameter>();
            PMS.Add(new SqlParameter("@證券代號", row.GetCell(0).ToString()));
            PMS.Add(new SqlParameter("@本益比", Convert.ToSingle(row.GetCell(2).ToString())));
            PMS.Add(new SqlParameter("@殖利率",  Convert.ToSingle(row.GetCell(3).ToString())));
            PMS.Add(new SqlParameter("@股價淨值比",  Convert.ToSingle(row.GetCell(4).ToString())));
            PMS.Add(new SqlParameter("@資料日期", Convert.ToDateTime(DataTime_PYS_txt.Text)));
            PMS.Add(new SqlParameter("@下載日期", dt));
            DB.ExecutionStoredProcedure("[PYS_Insert]", PMS.ToArray());
            Company_check = 0;
        }
    }

        /// <summary>
        /// HSSFRow Copy Command
        ///
        /// Description:  Inserts a existing row into a new row, will automatically push down
        ///               any existing rows.  Copy is done cell by cell and supports, and the
        ///               command tries to copy all properties available (style, merged cells, values, etc...)
        /// </summary>
        /// <param name="workbook">Workbook containing the worksheet that will be changed</param>
        /// <param name="worksheet">WorkSheet containing rows to be copied</param>
        /// <param name="sourceRowNum">Source Row Number</param>
        /// <param name="destinationRowNum">Destination Row Number</param>
        private HSSFRow CopyRow(HSSFWorkbook workbook, HSSFSheet worksheet, int sourceRowNum, int destinationRowNum)
        {
            // Get the source / new row
            var newRow = (HSSFRow)worksheet.GetRow(destinationRowNum);
            var sourceRow = (HSSFRow)worksheet.GetRow(sourceRowNum);

            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
            }
            else
            {
                newRow = (HSSFRow)worksheet.CreateRow(destinationRowNum);
            }

            // Loop through source columns to add to new row
            for (var i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                var oldCell = (HSSFCell)sourceRow.GetCell(i);
                var newCell = (HSSFCell)newRow.CreateCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null) continue;

                // Copy style from old cell and apply to new cell
                var newCellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                newCellStyle.CloneStyleFrom(oldCell.CellStyle);
                newCell.CellStyle = newCellStyle;

                // If there is a cell comment, copy
                if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case CellType.BLANK:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.BOOLEAN:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.ERROR:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.FORMULA:
                        newCell.CellFormula = oldCell.CellFormula;
                        break;
                    case CellType.NUMERIC:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.STRING:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case CellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            for (var i = 0; i < worksheet.NumMergedRegions; i++)
            {
                var cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow != sourceRow.RowNum) continue;
                var newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                                                               (newRow.RowNum +
                                                                (cellRangeAddress.FirstRow -
                                                                 cellRangeAddress.LastRow)),
                                                               cellRangeAddress.FirstColumn,
                                                               cellRangeAddress.LastColumn);
                worksheet.AddMergedRegion(newCellRangeAddress);
            }

            return newRow;
        }

        private void FillSheetWithData(HSSFWorkbook workbook, HSSFSheet sheet, Items data)
        {
            //fill date of generating
            SetValue(sheet, _settings.CreateDate, DateTime.Today);

            //fill order dates and delivery dates for each order
            FillOrdersDates(sheet, data.Orders);

            //fill data cells
            for (var i = 0; i < data.Rows.Count; i++)
            {
                var dataRow = data.Rows[i];
                var row = CopyRow(workbook, sheet, _settings.FirstRow, _settings.FirstRow + i + 1);

                //fill data from data contract first
                SetRowStringValue(row, _settings.Values.Code, dataRow.Code);
                SetRowStringValue(row, _settings.Values.Name, dataRow.Name);
                SetRowNumericValue(row, _settings.Values.KgPerUnit, dataRow.KgPerUnit);
                SetRowNumericValue(row, _settings.Values.Usage2010, dataRow.Usages.ContainsKey("2010") ? dataRow.Usages["2010"] : 0);
                SetRowNumericValue(row, _settings.Values.Usage2011, dataRow.Usages.ContainsKey("2011") ? dataRow.Usages["2011"] : 0);
                SetRowNumericValue(row, _settings.Values.OnStock, dataRow.OnStock);

                for (var j = 0; j < _settings.Values.Ordered.Count; j++)
                {
                    if (j == _settings.Orders.Count) throw new IndexOutOfRangeException("Orders count is bigger then template can process");
                    if (j >= data.Orders.Count)
                    {
                        SetRowNumericValue(row, _settings.Values.Ordered[j], 0);
                        continue;
                    }
                    var order = dataRow.Ordered.SingleOrDefault(o => o.Id == data.Orders[j].Id);
                    SetRowNumericValue(row, _settings.Values.Ordered[j], order != null ? order.Value : 0);
                }

                //fill formulas to other specific fields
                foreach (var formula in _settings.Formulas)
                    SetRowFormula(row, formula.Cell, string.Format(formula.Value, _settings.FirstRow + i + 2));
            }

            //set total order sum at the end of the document
            SetRowFormula((HSSFRow)sheet.GetRow(_settings.FirstRow + data.Rows.Count + 3),
                _settings.OrderedSum.Cell,
                string.Format(_settings.OrderedSum.Value, _settings.FirstRow + 2, _settings.FirstRow + data.Rows.Count + 1));
        }

        private void InsertRows(HSSFSheet sheet1, int fromRowIndex, int rowCount)
        {
            sheet1.ShiftRows(fromRowIndex, sheet1.LastRowNum, rowCount, true, false, true);

            for (int rowIndex = fromRowIndex; rowIndex < fromRowIndex + rowCount; rowIndex++)
            {
                IRow rowSource = sheet1.GetRow(rowIndex + rowCount);
                IRow rowInsert = sheet1.CreateRow(rowIndex);
                rowInsert.Height = rowSource.Height;
                for (int colIndex = 0; colIndex < rowSource.LastCellNum; colIndex++)
                {
                    ICell cellSource = rowSource.GetCell(colIndex);
                    ICell cellInsert = rowInsert.CreateCell(colIndex);
                    if (cellSource != null)
                    {
                        cellInsert.CellStyle = cellSource.CellStyle;
                    }
                }
            }
        }

 private static HSSFCell GetCell(HSSFSheet sheet, string cellCoordinates)
 {
     if (!cellCoordinates.Contains(",")) throw new FormatException(string.Format("Invalid cell coordinates: {0}!", cellCoordinates));
     var coordinates = cellCoordinates.Split(',');
     return (HSSFCell)((HSSFRow)sheet.GetRow(Convert.ToInt32(coordinates[0]))).GetCell(Convert.ToInt32(coordinates[1]));
 }

        /// <summary>
        /// Get a row from the spreadsheet, and Create it if it doesn't exist.
        /// </summary>
        /// <param name="rowCounter">The 0 based row number</param>
        /// <param name="sheet">The sheet that the row is part of.</param>
        /// <returns>The row indicated by the rowCounter</returns>
        public static HSSFRow GetRow(int rowCounter, HSSFSheet sheet)
        {
            HSSFRow row = sheet.GetRow(rowCounter);
            if (row == null)
            {
                row = sheet.CreateRow(rowCounter);
            }

            return row;
        }

        public static void populateStrategyDetailData(HSSFWorkbook wb, HSSFSheet sheet, IEnumerable<ArchitecturalStrategy> data)
        {
            #region workbookStyles
            //add Styles to workbook
            HSSFCellStyle styleMiddle = wb.CreateCellStyle();
            styleMiddle.Alignment = CellHorizontalAlignment.CENTER;
            HSSFCellStyle styleLeftWrap = wb.CreateCellStyle();
            styleLeftWrap.Alignment = CellHorizontalAlignment.LEFT;
            styleMiddle.VerticalAlignment = CellVerticalAlignment.CENTER;
            styleLeftWrap.WrapText = true;    //wrap the text in the cell
            //----------------------------------------------------------

            //font style1:  italic, blue color, fontsize=20
            HSSFFont font1 = wb.CreateFont();
            font1.Color = HSSFColor.BLUE.index;
            font1.IsItalic = true;
            font1.Boldweight = HSSFFont.BOLDWEIGHT_BOLD;
            font1.Underline = (byte)HSSFBorderFormatting.BORDER_THIN;
               // font1.Underline = (byte)FontUnderlineType.DOUBLE;
               // font1.FontHeightInPoints = 20;

            //bind font with styleItalicBold
            HSSFCellStyle italicBold = wb.CreateCellStyle();
            italicBold.SetFont(font1);
            //----------------------------------------------------------

            //bind font with styleItalicBold
            HSSFCellStyle underline = wb.CreateCellStyle();
            underline.BorderBottom = CellBorderType.THIN;
            underline.BottomBorderColor = HSSFColor.BLUE_GREY.index;

            HSSFCellStyle topline = wb.CreateCellStyle();
            topline.BorderTop = CellBorderType.THIN;
            topline.TopBorderColor = HSSFColor.BLUE_GREY.index;

            #endregion
            //set headerRow and 1st column
            const int maxRows = 65536; //npoi uses excel 2003
            int hRowNum = 4; //row starts at 0.
            int startCol = 0;
            int errorRow = 2; //note errors
            int errorCol = 6; //note errors

            HSSFRow headerRow = sheet.GetRow(hRowNum);
            int colIndex = startCol;

            #region Headers
            //date
            sheet.GetRow(0).GetCell(1).SetCellValue(DateTime.Now);

            //Title
            sheet.GetRow(1).GetCell(1).SetCellValue(data.FirstOrDefault().Project.Name);

            // handling headers.
            headerRow.GetCell(colIndex).SetCellValue("Strategy");
            colIndex++;
            headerRow.GetCell(colIndex).SetCellValue("Name");
            colIndex++;
            headerRow.GetCell(colIndex).SetCellValue("Description");
            colIndex++;
            headerRow.GetCell(colIndex).SetCellValue("Scenarios Affected");
            colIndex++;
            headerRow.GetCell(colIndex).SetCellValue("Current Response");
            colIndex++;
            headerRow.GetCell(colIndex).SetCellValue("Expected Response");
            colIndex++;
            headerRow.GetCell(colIndex).SetCellValue("Current Utility");
            colIndex++;
            headerRow.GetCell(colIndex).SetCellValue("Expected Utility");
            colIndex++;
            #endregion //headers

            #region populateData
            // foreach (DataColumn column in propertyInfos)
            //     headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);

            // handling value.
            int rowIndex = hRowNum + 1;
            HSSFRow dataRow = sheet.CreateRow(rowIndex);
            Boolean newStrategyRow = true;
            var i = 0; //index for loops
            try
            {
                foreach (var item in data)
                {
                    dataRow = sheet.CreateRow(rowIndex);
                    if (rowIndex < maxRows - 1)
                    {
                        //write each field
                        newStrategyRow = true;
                        colIndex = startCol;
                        dataRow.CreateCell(colIndex).SetCellValue(item.ID);
                        dataRow.GetCell(colIndex).CellStyle = topline;
                        colIndex++;
                        dataRow.CreateCell(colIndex).SetCellValue(item.Name.ToString());
                        dataRow.GetCell(colIndex).CellStyle = topline;
                        colIndex++;
                        dataRow.CreateCell(colIndex).SetCellValue(item.Description.ToString());
                        dataRow.GetCell(colIndex).CellStyle = topline;
                        colIndex++;
//.........这里部分代码省略.........

        protected void ProcessSheet(HSSFSheet sheet)
        {
            ProcessSheetHeader(htmlDocumentFacade.Body, sheet);

            int physicalNumberOfRows = sheet.PhysicalNumberOfRows;
            if (physicalNumberOfRows <= 0)
                return;

            XmlElement table = htmlDocumentFacade.CreateTable();
            table.SetAttribute("class", cssClassTable);

            XmlElement tableBody = htmlDocumentFacade.CreateTableBody();

            CellRangeAddress[][] mergedRanges = ExcelToHtmlUtils.BuildMergedRangesMap(sheet);

            List<XmlElement> emptyRowElements = new List<XmlElement>(physicalNumberOfRows);
            int maxSheetColumns = 1;
            for (int r = 0; r < physicalNumberOfRows; r++)
            {
                HSSFRow row = (HSSFRow)sheet.GetRow(r);

                if (row == null)
                    continue;

                if (!OutputHiddenRows && row.ZeroHeight)
                    continue;

                XmlElement tableRowElement = htmlDocumentFacade.CreateTableRow();
                htmlDocumentFacade.AddStyleClass(tableRowElement, "r", "height:"
                        + (row.Height / 20f) + "pt;");

                int maxRowColumnNumber = ProcessRow(mergedRanges, row,
                        tableRowElement);

                if (maxRowColumnNumber == 0)
                {
                    emptyRowElements.Add(tableRowElement);
                }
                else
                {
                    if (emptyRowElements.Count > 0)
                    {
                        foreach (XmlElement emptyRowElement in emptyRowElements)
                        {
                            tableBody.AppendChild(emptyRowElement);
                        }
                        emptyRowElements.Clear();
                    }

                    tableBody.AppendChild(tableRowElement);
                }
                maxSheetColumns = Math.Max(maxSheetColumns, maxRowColumnNumber);
            }

            ProcessColumnWidths(sheet, maxSheetColumns, table);

            if (OutputColumnHeaders)
            {
                ProcessColumnHeaders(sheet, maxSheetColumns, table);
            }

            table.AppendChild(tableBody);

            htmlDocumentFacade.Body.AppendChild(table);
        }

 /// <summary>
 /// Gets the row height in points.
 /// </summary>
 /// <param name="sheet">The sheet.</param>
 /// <param name="rowNum">The row num.</param>
 /// <returns></returns>
 private float GetRowHeightInPoints(HSSFSheet sheet, int rowNum)
 {
     HSSFRow row = sheet.GetRow(rowNum);
     if (row == null)
         return sheet.DefaultRowHeightInPoints;
     else
         return row.HeightInPoints;
 }

 /// <summary>
 /// 解锁
 /// </summary>
 /// <param name="workbook"></param>
 /// <param name="sheet"></param>
 /// <param name="columnIndex"></param>
 public static void UnlockCell(HSSFWorkbook workbook, HSSFSheet sheet, int rowIndex, int columnIndex)
 {
     HSSFCellStyle unlocked = workbook.CreateCellStyle();
     unlocked.IsLocked = false;
     sheet.GetRow(rowIndex).GetCell(columnIndex).CellStyle = unlocked;
 }

 /// <summary>
 /// 往单元格写数据
 /// </summary>
 /// <param name="sheet"></param>
 /// <param name="columnIndex"></param>
 /// <param name="rowIndex"></param>
 /// <param name="obj"></param>
 /// <returns></returns>
 public static HSSFCell WriteCell(HSSFSheet sheet, int columnIndex, int rowIndex, object obj)
 {
     HSSFRow row = sheet.GetRow(rowIndex);
     if (row == null)
     {
         row = sheet.CreateRow(rowIndex);
     }
     return WriteCell(row, obj, columnIndex);
 }

 /// <summary>
 /// 解锁
 /// </summary>
 /// <param name="workbook"></param>
 /// <param name="sheet"></param>
 /// <param name="columnIndex"></param>
 public static void UnlockColumn(HSSFWorkbook workbook, HSSFSheet sheet, int columnIndex)
 {
     HSSFCellStyle unlocked = workbook.CreateCellStyle();
     unlocked.IsLocked = false;
     for (int i = sheet.FirstRowNum; i <= sheet.LastRowNum; i++)
     {
         sheet.GetRow(i).GetCell(columnIndex).CellStyle = unlocked;
     }
 }

 /// <summary>
 /// 创建数据行
 /// </summary>
 /// <param name="sheet"></param>
 private void GenerateDataRow(HSSFWorkbook workbook, HSSFSheet sheet)
 {
     for (var i = 1; i <= dataList.Count; i++)
     {
         var row = (HSSFRow) sheet.GetRow(i);
         Model.Database.DbBean dbBean = dataList[i - 1];
         //序号
         ICell cell0 = row.CreateCell(0);
         cell0.SetCellValue(i);
         cell0.CellStyle.ShrinkToFit = true;
         InitDataCellStyle(workbook, cell0);
         //设备类型
         cell0 = row.CreateCell(1);
         cell0.SetCellValue(dbBean.DeviceType);
         cell0.CellStyle.ShrinkToFit = true;
         InitDataCellStyle(workbook, cell0);
         //公里标
         cell0 = row.CreateCell(2);
         cell0.SetCellValue(dbBean.KilometerMark);
         cell0.CellStyle.ShrinkToFit = true;
         InitDataCellStyle(workbook, cell0);
         //侧向
         cell0 = row.CreateCell(3);
         cell0.SetCellValue(dbBean.SideDirection);
         cell0.CellStyle.ShrinkToFit = true;
         InitDataCellStyle(workbook, cell0);
         //距线路中心距离
         cell0 = row.CreateCell(4);
         cell0.SetCellValue(dbBean.DistanceToRail);
         cell0.CellStyle.ShrinkToFit = true;
         InitDataCellStyle(workbook, cell0);
         //经度
         double temp;
         if (double.TryParse(dbBean.Longitude, out temp))
         {
             cell0 = row.CreateCell(5);
             cell0.SetCellValue(Double.Parse((dbBean.Longitude)));
             cell0.CellStyle.ShrinkToFit = true;
             InitDataCellNumStyle(workbook, cell0);
         }
         else
         {
             cell0 = row.CreateCell(5);
             cell0.SetCellValue((dbBean.Longitude));
             cell0.CellStyle.ShrinkToFit = true;
             InitDataCellNumStyle(workbook, cell0);
         }
         //纬度
         if (double.TryParse(dbBean.Latitude, out temp))
         {
             cell0 = row.CreateCell(6);
             cell0.SetCellValue(double.Parse(dbBean.Latitude));
             cell0.CellStyle.ShrinkToFit = true;
             InitDataCellNumStyle(workbook, cell0);
         }
         else
         {
             cell0 = row.CreateCell(6);
             cell0.SetCellValue(dbBean.Latitude);
             cell0.CellStyle.ShrinkToFit = true;
             InitDataCellNumStyle(workbook, cell0);
         }
         //备注文本
         cell0 = row.CreateCell(7);
         cell0.SetCellValue(dbBean.Comment);
         cell0.CellStyle.ShrinkToFit = true;
         InitDataCellStyle(workbook, cell0);
     }
 }

 /// <summary>
 /// 创建Excel的表头
 /// </summary>
 private void GenerateHeaderRow(HSSFWorkbook workbook, HSSFSheet sheet)
 {
     //获取第一行
     HSSFRow row = (HSSFRow) sheet.GetRow(0);
     string[] columns = {"序号", "设备类型", "公里标", "侧向", "距线路中心距离", "经度", "纬度", "备注文本"};
     for (int i = 0; i<columns.Length; i++)
     {
         ICell cell = row.CreateCell(i);
         cell.SetCellValue(columns[i]);
         //cell.CellStyle.ShrinkToFit = true;
         InitHeaderCellStyle(workbook, cell);
     }
 }