/// <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)); } if (!string.IsNullOrEmpty(columns[cIndex].DataPropertyName)) { 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(); }