C# UserModel.HSSFSheet類代碼示例

 //[Obsolete]
 //public static void SetBorderBottom(NPOI.SS.UserModel.CellBorderType border, Region region, HSSFSheet sheet,
 //        HSSFWorkbook workbook)
 //{
 //    SetBorderBottom(border, toCRA(region), sheet, workbook);
 //}
 /// <summary>
 /// Sets the borderBottom attribute of the HSSFRegionUtil object
 /// </summary>
 /// <param name="border">The new border</param>
 /// <param name="region">The region that should have the border</param>
 /// <param name="sheet">The sheet that the region is on.</param>
 /// <param name="workbook">The workbook that the region is on.</param>
 public static void SetBorderBottom(NPOI.SS.UserModel.CellBorderType border, CellRangeAddress region, HSSFSheet sheet,
         HSSFWorkbook workbook)
 {
     int colStart = region.FirstColumn;
     int colEnd = region.LastColumn;
     int rowIndex = region.LastRow;
     CellPropertySetter cps = new CellPropertySetter(workbook, HSSFCellUtil.BORDER_BOTTOM, (int)border);
     NPOI.SS.UserModel.Row row = HSSFCellUtil.GetRow(rowIndex, sheet);
     for (int i = colStart; i <= colEnd; i++)
     {
         cps.SetProperty(row, i);
     }
 }

    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>
        /// Creates new HSSFRow from scratch. Only HSSFSheet should do this.
        /// </summary>
        /// <param name="book">low-level Workbook object containing the sheet that Contains this row</param>
        /// <param name="sheet">low-level Sheet object that Contains this Row</param>
        /// <param name="rowNum">the row number of this row (0 based)</param>
        ///<see cref="NPOI.HSSF.UserModel.HSSFSheet.CreateRow(int)"/>
        public HSSFRow(HSSFWorkbook book, HSSFSheet sheet, int rowNum)
        {
            this.rowNum = rowNum;
            this.book = book;
            this.sheet = sheet;
            row = new RowRecord(rowNum);

            RowNum=(rowNum);
        }

 /// <summary>
 /// Creates the patriarch.
 /// </summary>
 /// <param name="sheet">the sheet this patriarch is stored in.</param>
 /// <param name="boundAggregate">The bound aggregate.</param>
 public HSSFPatriarch(HSSFSheet sheet, EscherAggregate boundAggregate)
 {
     _boundAggregate = boundAggregate;
     _sheet = sheet;
     _mainSpgrContainer = _boundAggregate.GetEscherContainer().ChildContainers[0];
     EscherContainerRecord spContainer = (EscherContainerRecord)_boundAggregate.GetEscherContainer()
             .ChildContainers[0].GetChild(0);
     _spgrRecord = (EscherSpgrRecord)spContainer.GetChildById(EscherSpgrRecord.RECORD_ID);
     BuildShapeTree();
 }

        private void CreateFilterModeRecord(HSSFSheet sheet,int insertPos)
        {
            //look for the FilterModeRecord
            NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(FilterModeRecord.sid);

            // this local variable hides the class one: FilterModeRecord filtermode;
            //if not found, add a new one
            if (record == null)
            {
                filtermode = new FilterModeRecord();
                sheet.Sheet.Records.Insert(insertPos, filtermode);
            }
        }

 public CellEvaluationFrame(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum)
 {
     if (workbook == null)
     {
         throw new ArgumentException("workbook must not be null");
     }
     if (sheet == null)
     {
         throw new ArgumentException("sheet must not be null");
     }
     _workbook = workbook;
     _sheet = sheet;
     _srcRowNum = srcRowNum;
     _srcColNum = srcColNum;
 }

 private void CreateAutoFilterInfoRecord(HSSFSheet sheet, int insertPos, Area3DPtg ptg)
 {
     //look for the AutoFilterInfo Record
     NPOI.HSSF.Record.Record record = sheet.Sheet.FindFirstRecordBySid(AutoFilterInfoRecord.sid);
     AutoFilterInfoRecord info;
     if (record == null)
     {
         info = new AutoFilterInfoRecord();
         sheet.Sheet.Records.Insert(insertPos, info);
     }
     else
     {
         info = record as AutoFilterInfoRecord;
     }
     info.NumEntries = (short)(ptg.LastColumn - ptg.FirstColumn + 1);
 }

        public ExcalHelper(String sheetName, String[] rowTitle, int[] rowWidth, int dataRowNum)
        {
            //創建標題行
            workbook = new HSSFWorkbook();
            sheet = (HSSFSheet)workbook.CreateSheet(sheetName);
            this.dataRowNum = dataRowNum;

            HSSFRow rowtitle = (HSSFRow)sheet.CreateRow(0);

            for (int i = 0; i < rowTitle.Length; i++)
            {
                //設置列寬
                sheet.SetColumnWidth(i, rowWidth[i] * 255);
                rowtitle.CreateCell(i).SetCellValue(rowTitle[i]);
            }
        }

        /**
     * Creates a map (i.e. two-dimensional array) filled with ranges. Allow fast
     * retrieving {@link CellRangeAddress} of any cell, if cell is contained in
     * range.
     * 
     * @see #getMergedRange(CellRangeAddress[][], int, int)
     */
        public static CellRangeAddress[][] BuildMergedRangesMap(HSSFSheet sheet)
    {
        CellRangeAddress[][] mergedRanges = new CellRangeAddress[1][];
        for ( int m = 0; m < sheet.NumMergedRegions; m++ )
        {
            CellRangeAddress cellRangeAddress = sheet.GetMergedRegion( m );

            int requiredHeight = cellRangeAddress.LastRow + 1;
            if ( mergedRanges.Length < requiredHeight )
            {
                CellRangeAddress[][] newArray = new CellRangeAddress[requiredHeight][];
                Array.Copy( mergedRanges, 0, newArray, 0, mergedRanges.Length );
                mergedRanges = newArray;
            }

            for ( int r = cellRangeAddress.FirstRow; r <= cellRangeAddress.LastRow; r++ )
            {
                int requiredWidth = cellRangeAddress.LastColumn + 1;

                CellRangeAddress[] rowMerged = mergedRanges[r];
                if ( rowMerged == null )
                {
                    rowMerged = new CellRangeAddress[requiredWidth];
                    mergedRanges[r] = rowMerged;
                }
                else
                {
                     int rowMergedLength = rowMerged.Length;
                    if ( rowMergedLength < requiredWidth )
                    {
                        CellRangeAddress[] newRow = new CellRangeAddress[requiredWidth];
                        Array.Copy(rowMerged, 0, newRow, 0,rowMergedLength );

                        mergedRanges[r] = newRow;
                        rowMerged = newRow;
                    }
                }
               
                //Arrays.Fill( rowMerged, cellRangeAddress.FirstColumn, cellRangeAddress.LastColumn + 1, cellRangeAddress );
                for (int i = cellRangeAddress.FirstColumn; i < cellRangeAddress.LastColumn + 1; i++)
                {
                    rowMerged[i] = cellRangeAddress;
                }
            }
        }
        return mergedRanges;
    }

 public static HSSFPatriarch CreatePatriarch(HSSFPatriarch patriarch, HSSFSheet sheet)
 {
     HSSFPatriarch newPatriarch = new HSSFPatriarch(sheet, new EscherAggregate(true));
     newPatriarch.AfterCreate();
     foreach (HSSFShape shape in patriarch.Children)
     {
         HSSFShape newShape;
         if (shape is HSSFShapeGroup)
         {
             newShape = ((HSSFShapeGroup)shape).CloneShape(newPatriarch);
         }
         else
         {
             newShape = shape.CloneShape();
         }
         newPatriarch.OnCreate(newShape);
         newPatriarch.AddShape(newShape);
     }
     return newPatriarch;
 }

    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;
        }
    }

        /**
         * Notifies this evaluation tracker that the evaluation of the specified
         * cell Is complete. <p/>
         *
         * Every successful call to <tt>startEvaluate</tt> must be followed by a
         * call to <tt>endEvaluate</tt> (recommended in a finally block) to enable
         * proper tracking of which cells are being evaluated at any point in time.<p/>
         *
         * Assuming a well behaved client, parameters to this method would not be
         * required. However, they have been included to assert correct behaviour,
         * and form more meaningful error messages.
         */
        public void EndEvaluate(HSSFWorkbook workbook, HSSFSheet sheet, int srcRowNum, int srcColNum)
        {
            int nFrames = _evaluationFrames.Count;
            if (nFrames < 1)
            {
                throw new InvalidOperationException("Call to endEvaluate without matching call to startEvaluate");
            }

            nFrames--;
            CellEvaluationFrame cefExpected = (CellEvaluationFrame)_evaluationFrames[nFrames];
            CellEvaluationFrame cefActual = new CellEvaluationFrame(workbook, sheet, srcRowNum, srcColNum);
            if (!cefActual.Equals(cefExpected))
            {
                throw new Exception("Wrong cell specified. "
                        + "Corresponding startEvaluate() call was for cell {"
                        + cefExpected.FormatAsString() + "} this endEvaluate() call Is for cell {"
                        + cefActual.FormatAsString() + "}");
            }
            // else - no problems so pop current frame
            _evaluationFrames.Remove(nFrames);
        }

 /**
  * @return aggregate info or null if the sheet does not contain Drawing objects
  */
 internal static DrawingAggregateInfo Get(HSSFSheet sheet)
 {
     DrawingAggregateInfo info = null;
     InternalSheet isheet = HSSFTestHelper.GetSheetForTest(sheet);
     List<RecordBase> records = isheet.Records;
     for (int i = 0; i < records.Count; i++)
     {
         RecordBase rb = records[(i)];
         if ((rb is DrawingRecord) && info == null)
         {
             info = new DrawingAggregateInfo();
             info.startRecordIndex = i;
             info.endRecordIndex = i;
         }
         else if (info != null && (
               rb is DrawingRecord
                       || rb is ObjRecord
                       || rb is TextObjectRecord
                       || rb is ContinueRecord
                       || rb is NoteRecord
       ))
         {
             info.endRecordIndex = i;
         }
         else
         {
             if (rb is EscherAggregate)
                 throw new InvalidOperationException("Drawing data already aggregated. " +
                         "You should cal this method before the first invocation of HSSFSheet#getDrawingPatriarch()");
             if (info != null) break;
         }
     }
     if (info != null)
     {
         info.aggRecords = new List<RecordBase>(
                 records.GetRange(info.startRecordIndex, info.endRecordIndex + 1));
     }
     return info;
 }

 protected void Source_btn_Click(object sender, EventArgs e)
 {
     //try
     //{
         this.workbook = new HSSFWorkbook(Source_FileUpload.FileContent);
         this.u_sheet = (HSSFSheet)workbook.GetSheetAt(0);  //取得第0個Sheet
         //不同於Microsoft Object Model，NPOI都是從索引0開始算起
         //從第一個Worksheet讀資料        
         SaveOrInsertSheet_Source(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;
         GC.Collect();
     //}
 }

    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;
        }
    }