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); }
private short _defualtColor = NPOI.HSSF.Util.HSSFColor.Black.Index;//默认颜色 #endregion 变量 /// <summary> /// 创建一个新工作表对象,默认空表格 /// </summary> public ExcelOparete() { _workbook = new HSSFWorkbook(); _currentSheet = null; //_sheetRowCount = 0; _defaultExcelCellStyle = new ExcelCellStyle(); }
/// <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); }
/// <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); }
/// <summary> /// 设置格式 /// </summary> /// <param name="firstRowIndex"></param> /// <param name="lastRowIndex"></param> /// <param name="firstColumnIndex"></param> /// <param name="lastColumnIndex"></param> /// <param name="cellStyle"></param> public void SetRowCellStyle(int firstRowIndex, int lastRowIndex, int firstColumnIndex, int lastColumnIndex, ExcelCellStyle cellStyle) { for (int rIndex = firstRowIndex; rIndex <= lastRowIndex; rIndex++) { IRow row = GetRow(rIndex); for (int cIndex = firstColumnIndex; cIndex <= lastColumnIndex; cIndex++) { row.GetCell(cIndex).CellStyle = cellStyle.GetCellStyle(this.WorkBook); } } }
/// <summary> /// 设置一个对象的值,自动合并列,单元格格为工作表默认的值 /// </summary> /// <param name="value">对象</param> /// <param name="rowIndex">行位置</param> /// <param name="firstColumnIndex">起始列</param> /// <param name="lastColumnIndex">结束列</param> /// <param name="cellStyle">如果为NULL,则为工作表默认的值</param> public void SetObjectValue(object value, int rowIndex, int firstColumnIndex, int lastColumnIndex, ExcelCellStyle cellStyle) { IRow row = CreateRow(rowIndex, firstColumnIndex, lastColumnIndex, cellStyle); if (lastColumnIndex > firstColumnIndex) { this._currentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, firstColumnIndex, lastColumnIndex)); } SetCellValue(value, row, firstColumnIndex); }