Esempio n. 1
0
        private IRow CreateRow(int index, int firstColumnIndex, int lastColumnIndex, ExcelCellStyle cellStyle)
        {
            IRow row = _currentSheet.GetRow(index);

            //ExcelCellStyle tempStyle = cellStyle == null ? _defaultExcelCellStyle : cellStyle;
            if (row == null)
            {
                row = this._currentSheet.CreateRow(index);
                for (int i = firstColumnIndex; i <= lastColumnIndex; i++)
                {
                    row.CreateCell(i);
                    row.GetCell(i).CellStyle = cellStyle == null?_defaultExcelCellStyle.GetCellStyle(this.WorkBook) : cellStyle.GetCellStyle(this.WorkBook);
                }
            }
            else
            {
                for (int i = firstColumnIndex; i <= lastColumnIndex; i++)
                {
                    if (row.GetCell(i) == null)
                    {
                        row.CreateCell(i);
                    }
                    row.GetCell(i).CellStyle = cellStyle == null?_defaultExcelCellStyle.GetCellStyle(this.WorkBook) : cellStyle.GetCellStyle(this.WorkBook);
                }
            }
            int kk = this.WorkBook.NumCellStyles;

            return(row);
        }
Esempio n. 2
0
        private short _defualtColor = NPOI.HSSF.Util.HSSFColor.BLACK.index;//默認顏色值
        #endregion 變量

        /// <summary>
        /// 創建一個新工作表對象,默認空表格。
        /// </summary>
        public ExcelOparete()
        {
            _workbook     = new HSSFWorkbook();
            _currentSheet = null;
            //_sheetRowCount = 0;
            _defaultExcelCellStyle = new ExcelCellStyle();
        }
Esempio n. 3
0
        public void SetSheetName(string sheetName, string newSheetName, ref int sheetIndex)
        {
            int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));

            _currentSheet = _workbook.GetSheetAt(i);
            _workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName);
            _workbook.SetSheetOrder(newSheetName, _workbook.NumberOfSheets);
            _defaultExcelCellStyle = new ExcelCellStyle();
        }
Esempio n. 4
0
        /// <summary>
        /// 增加一個新的表格,並將新增的表格作為活動表
        /// </summary>
        /// <param name="sheetName">表名稱</param>
        public void CloneSheet(string sheetName, string newSheetName, ref int sheetIndex)
        {
            int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));

            _currentSheet = _workbook.CloneSheet(i);
            sheetIndex    = _workbook.GetSheetIndex(_currentSheet);
            _workbook.SetSheetName(_workbook.GetSheetIndex(_currentSheet), newSheetName);
            _defaultExcelCellStyle = new ExcelCellStyle();
        }
Esempio n. 5
0
        private int _columnCount;                      //列佔的列數

        #endregion

        #region 構造函數

        /// <summary>
        /// 創建一個新的列頭對象
        /// </summary>
        private ExcelColumn()
        {
            _isSetWith             = false;
            _columnCount           = 1;
            _columnName            = string.Empty;
            _dataPropertyName      = string.Empty;
            _with                  = 20;
            _defaultExcelCellStyle = null; // new ExcelCellStyle();
            _columnExcelCellStyle  = null; //new ExcelCellStyle();
            //_warpText = false;
            //_index = 0;
        }
Esempio n. 6
0
        /// <summary>
        /// 設置標題值,自動合並單元格
        /// </summary>
        /// <param name="titleString">標題文本</param>
        /// <param name="firstRowIndex">開始行位置</param>
        /// <param name="lastRowIndex">結束行位置</param>
        /// <param name="firstColumnIndex">開始的列位置</param>
        /// <param name="lastColumnIndex">結束的列位置</param>
        /// <param name="titleExceCellStyle">單元格樣式,如果為NULL,則為默認(12號字體、加粗)</param>
        public void SetTitleValue(string titleString, int firstRowIndex, int lastRowIndex, int firstColumnIndex, int lastColumnIndex, ExcelCellStyle titleExceCellStyle)
        {
            if (titleExceCellStyle == null)
            {
                titleExceCellStyle = new ExcelCellStyle(12, FontBoldWeight.BOLD);
                titleExceCellStyle.HorizontalAlignment = HorizontalAlignment.CENTER;
            }
            for (int i = firstRowIndex; i <= lastRowIndex; i++)
            {
                CreateRow(i, firstColumnIndex, lastColumnIndex, titleExceCellStyle);
            }
            IRow row = this._currentSheet.GetRow(firstRowIndex);

            if (lastColumnIndex > firstColumnIndex)
            {
                this._currentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(firstRowIndex, lastRowIndex, firstColumnIndex, lastColumnIndex));
            }
            row.GetCell(firstColumnIndex).SetCellValue(titleString);
        }
Esempio n. 7
0
        /// <summary>
        /// 克隆一個新的單元格格式對象
        /// </summary>
        /// <returns></returns>
        public object Clone()
        {
            ExcelCellStyle style = new ExcelCellStyle();

            style.BorderBottom        = this._borderBottom;
            style.BorderLeft          = this._borderLeft;
            style.BorderRight         = this._borderRight;
            style.BorderTop           = this._borderTop;
            style.HorizontalAlignment = this._alignment;
            style.VerticalAlignment   = this._verticalAlignment;
            style.DataFormart         = this._dataFormart;
            style.FontBold            = this.FontBold;
            style.FontName            = this.FontName;
            style.FontSize            = this.FontSize;
            style.WarpText            = this.WarpText;
            style.FontName            = this._fontName;
            style.UnderLine           = this.UnderLine;
            style.FontIsItalic        = this._isItalic;
            style.FontColor           = this._fontColor;
            style.FontIsStrikeout     = this._isStrikeout;
            style._fontTypeOffset     = this._fontTypeOffset;
            return(style);
        }
Esempio n. 8
0
        private string GetYdPayLogOnExport(DataTable dtSource)
        {
            string fn       = "/XTemp/缴费明细单.xls";
            string filePath = System.Web.Hosting.HostingEnvironment.MapPath(@"/XTemp");

            if (System.IO.Directory.Exists(filePath) == false)
            {
                System.IO.Directory.CreateDirectory(filePath);
            }
            string filename = System.Web.Hosting.HostingEnvironment.MapPath(fn);

            if (System.IO.File.Exists(filename))/*先删除已存在的文件,再汇出Excel*/
            {
                System.IO.File.Delete(filename);
            }
            if (dtSource == null || dtSource.Rows.Count == 0)
            {
                throw new Exception("没有数据");
            }
            Excel.ExcelCellStyle columnCellStyle0 = new Excel.ExcelCellStyle();
            columnCellStyle0 = new Excel.ExcelCellStyle()
            {
                DataFormart         = "0.00",
                HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.RIGHT
            };
            Excel.ExcelCellStyle columnCellStyle1 = new Excel.ExcelCellStyle();
            columnCellStyle1 = new Excel.ExcelCellStyle()
            {
                DataFormart = "yyyy-MM-dd HH:mm:ss",
            };
            Excel.ExcelOparete excel = new Excel.ExcelOparete("缴费明细单");
            var cellStyle            = new ExcelCellStyle();

            cellStyle.HorizontalAlignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment   = NPOI.SS.UserModel.VerticalAlignment.CENTER;
            cellStyle.FontSize            = 12;
            excel.SetObjectValue("缴费明细单", 0, 0, 1, cellStyle);

            excel.SetObjectValue2("建筑名称", 1, 1, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["CoStrcName"], 1, 1, 1, 1);
            excel.SetObjectValue2("房间名称", 2, 2, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["CoName"], 2, 2, 1, 1);
            excel.SetObjectValue2("倍率", 3, 3, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["Multiply"], 3, 3, 1, 1);
            excel.SetObjectValue2("单价(元/kWh)", 4, 4, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["Price"], 4, 4, 1, 1);
            excel.SetObjectValue2("充值类型", 5, 5, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["IsPayS"], 5, 5, 1, 1);
            excel.SetObjectValue2("充值方式", 6, 6, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["payTypeS"], 6, 6, 1, 1);
            excel.SetObjectValue2("充值电量(kWh)", 7, 7, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["PayVal"], 7, 7, 1, 1);
            excel.SetObjectValue2("充值金额(元)", 8, 8, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["PayAmt"], 8, 8, 1, 1);
            excel.SetObjectValue2("金额(大写)", 9, 9, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["strAmt"], 9, 9, 1, 1);
            excel.SetObjectValue2("操作者", 10, 10, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["Create_by"], 10, 10, 1, 1);
            excel.SetObjectValue2("操作时间", 11, 11, 0, 0);
            excel.SetObjectValue2(dtSource.Rows[0]["Create_dt"], 11, 11, 1, 1);

            excel.SaveExcelByFullFileName(filename);
            return(fn);
        }
Esempio n. 9
0
 /// <summary>
 /// 創建一個新的列頭對象
 /// </summary>
 /// <param name="columnName">列頭名稱</param>
 /// <param name="dataPropertyName">數據的屬性名稱</param>
 /// <param name="with">列的寬</param>
 /// <param name="columnExcelCellStyle">列頭的單元格式</param>
 ///<param name="defaultExcelCellStyle">列值的單元格式</param>
 public ExcelColumn(string columnName, string dataPropertyName, int with, ExcelCellStyle columnExcelCellStyle, ExcelCellStyle defaultExcelCellStyle)
     : this(columnName, dataPropertyName, with)
 {
     _defaultExcelCellStyle = defaultExcelCellStyle;
     _columnExcelCellStyle  = columnExcelCellStyle;
 }
Esempio n. 10
0
        //public void setObjectValue(PCM.Common.Excel.ExcelOparete excel, object obj, int rowIndex, ref int columnIndex)
        //{
        //    columnIndex += 1;
        //    excel.SetObjectValue(obj, rowIndex, columnIndex, columnIndex);
        //}

        /// <summary>
        /// 設置一個對象的值,自動合並行列,單元格格為工作表默認的值
        /// </summary>
        /// <param name="value">對象</param>
        /// <param name="rowIndex1">開始行位置</param>
        /// <param name="rowIndex2">結束行位置</param>
        /// <param name="firstColumnIndex">起始列</param>
        /// <param name="lastColumnIndex">結束列</param>
        /// <param name="cellStyle">如果為NULL,則為工作表默認的值</param>
        public void SetObjectValue2(object value, int rowIndex1, int rowIndex2, int firstColumnIndex, int lastColumnIndex, ExcelCellStyle cellStyle)
        {
            IRow row = CreateRow(rowIndex1, firstColumnIndex, lastColumnIndex, cellStyle);

            if (lastColumnIndex > firstColumnIndex || rowIndex2 > rowIndex1)
            {
                this._currentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex1, rowIndex2, firstColumnIndex, lastColumnIndex));
            }
            SetCellValue(value, row, firstColumnIndex);
        }