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