/// <summary>
/// 将ExcelColumnCollection表头值装载到当前工作表中
/// </summary>
/// <param name="columns">数据源</param>
/// <param name="rowIndex">当前行号</param>
/// <param name="firstColumnIndex">起始列号</param>
public void SetColumnName(ExcelColumnCollection columns, int rowIndex, int firstColumnIndex)
{
int curColumnIndex = firstColumnIndex;
SetColumnWithByColumns(columns, firstColumnIndex);
IRow row = CreateRow(rowIndex, curColumnIndex, curColumnIndex + columns.RealCount - 1, null);
for (int i = 0; i < columns.Count; i++)
{
for (int columnIndex = curColumnIndex; columnIndex < columns[i].ColumnCount + curColumnIndex; columnIndex++)
{
if (row.GetCell(columnIndex) == null)
{
row.CreateCell(columnIndex);
}
row.GetCell(columnIndex).CellStyle
= columns[i].ColumnExcelCellStyle == null?this._defaultExcelCellStyle.GetCellStyle(this.WorkBook) : columns[i].ColumnExcelCellStyle.GetCellStyle(this.WorkBook);
int kk = this.WorkBook.NumCellStyles;
}
if (columns[i].ColumnCount > 1)
{
this._currentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, curColumnIndex, curColumnIndex + columns[i].ColumnCount - 1));
}
row.GetCell(curColumnIndex).SetCellValue(columns[i].ColumnName);
curColumnIndex += columns[i].ColumnCount;
}
}
/// <summary>
/// 更新ExcelCollection对象中的单元格格式，如果不NULL，则为当前默认值。
/// </summary>
/// <param name="columns"></param>
private void UpdateExcelColumnsOfCellStycle(ExcelColumnCollection columns)
{
foreach (ExcelColumn column in columns)
{
if (column.DefaultExcelCellStyle == null)
{
column.DefaultExcelCellStyle = this._defaultExcelCellStyle;
}
if (column.ColumnExcelCellStyle == null)
{
column.ColumnExcelCellStyle = this._defaultExcelCellStyle;
}
}
}
/// <summary>
/// 将DataRow[]的值装载到当前工作表中
/// </summary>
/// <param name="columns">数据源对应工作表中的列头集合</param>
/// <param name="dataRows">数据源</param>
/// <param name="rowIndex">起始行号</param>
/// <param name="firstColumnIndex">起始列号</param>
public void SetColumnValue(ExcelColumnCollection columns, DataRow[] dataRows, int rowIndex, int firstColumnIndex)
{
if (dataRows == null || dataRows.Length <= 0)
{
return;
}
//SetColumnWithByColumns(columns, firstColumnIndex);
int curColumnIndex = 0;
int pageCount = 1;
string firstSheetName = _currentSheet.SheetName;
for (int i = 0; i < dataRows.Length; i++, rowIndex++)
{
//if (i > 65000)
// return;
curColumnIndex = firstColumnIndex;
IRow row = CreateRow(rowIndex, firstColumnIndex, columns.RealCount + firstColumnIndex - 1, null);
for (int cIndex = 0; cIndex < columns.Count; cIndex++)
{
for (int columnIndex = curColumnIndex; columnIndex < columns[cIndex].ColumnCount + curColumnIndex; columnIndex++)
{
if (row.GetCell(columnIndex) == null)
{
row.CreateCell(columnIndex);
}
row.GetCell(columnIndex).CellStyle
= columns[cIndex].DefaultExcelCellStyle == null?this._defaultExcelCellStyle.GetCellStyle(this.WorkBook) : columns[cIndex].DefaultExcelCellStyle.GetCellStyle(this.WorkBook);
int kk = this.WorkBook.NumCellStyles;
}
if (columns[cIndex].ColumnCount > 1)
{
this._currentSheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, curColumnIndex, curColumnIndex + columns[cIndex].ColumnCount - 1));
}
SetCellValue(dataRows[i][columns[cIndex].DataPropertyName], row, curColumnIndex);
curColumnIndex += columns[cIndex].ColumnCount;
}
if (this._currentSheet.PhysicalNumberOfRows > 65000)
{
AddSheet(firstSheetName + pageCount.ToString());
SetColumnName(columns, 0, firstColumnIndex);
pageCount += 1;
rowIndex = 0;
}
}
}
/// <summary>
/// 设置列的宽
/// </summary>
/// <param name="columns"></param>
/// <param name="firstColumnIndex"></param>
private void SetColumnWithByColumns(ExcelColumnCollection columns, int firstColumnIndex)
{
if (columns == null)
{
return;
}
for (int i = 0; i < columns.Count; i++)
{
if (columns[i].IsSetWith)
{
for (int cellIndex = 0; cellIndex < columns[i].ColumnCount; cellIndex++)
{
this._currentSheet.SetColumnWidth(cellIndex + firstColumnIndex, columns[i].RealityWith);
}
}
firstColumnIndex += columns[i].ColumnCount;
}
}
/// <summary>
/// 创建一个新的表格
/// </summary>
public ExcelTable()
{
_columns = new ExcelColumnCollection();
_rows = new ExcelRowCollection();
}