public Cell this[CellAddress address]
{
get
{
return(this[address.RowIndex, address.ColumnIndex]);
}
}
public void LoadFromDataView(CellAddress beginAddress, ExcelTableStyles tableStyle, DataView dv, CreatingDataCellAction <DataRowView> creatingDataCellAction)
{
dv.NullCheck <ArgumentNullException>("数据源不能为空！");
TableDescription tbDesp = SpreadSheetAttributeHelper.GetTableDescription(dv.Table);
tbDesp.BeginAddress = beginAddress;
if (tableStyle == ExcelTableStyles.None && tbDesp.TableName.IsNullOrEmpty())
{
this.LoadFromDataView(tbDesp, dv, creatingDataCellAction);
}
else
{
tbDesp.TableStyle = tableStyle;
Table table;
if (this.Tables.TryGetTable(tbDesp.TableName, out table) == false)
{
table = new Table(this, tbDesp);
this.Tables.Add(table);
}
table.FillData(dv, tbDesp, creatingDataCellAction);
}
}
public Table Add(string name, CellAddress begionAddress, IDictionary <string, string> colums, int rowCount)
{
int endRow = begionAddress.RowIndex + rowCount;
if (rowCount == 0)
{
endRow++;
}
//Range tableRange = Range.Parse(this._WorkSheet, begionAddress.ColumnIndex, begionAddress.RowIndex, begionAddress.ColumnIndex + colums.Count - 1, endRow);
Table createTable = new Table(this._WorkSheet, name, begionAddress);
int columIndex = 0;
foreach (KeyValuePair <string, string> columKey in colums)
{
TableColumn tcolumn = new TableColumn(createTable, columKey.Key, columIndex);
if (columKey.Value.IsNotEmpty())
{
tcolumn.ColumnFormula = ExcelHelper.UserTableColumFormulaToExcelColum(columKey.Value, name);
}
createTable.Columns.Add(tcolumn);
columIndex++;
}
createTable.NextColumnID = columIndex + 1;
return(this.Add(createTable));
}
/// <summary>
/// 将Excel文件中开始到结束位置的数据转换成DataTable
/// </summary>
/// <param name="fileStream">Excel文件流</param>
/// <param name="workSheetName">工作表名称</param>
/// <param name="beginAddress">开始位置</param>
/// <param name="endAddress">结束位置</param>
/// <returns></returns>
public static DataTable GetRangeValues(Stream fileStream, string workSheetName, string beginAddress, string endAddress)
{
DataTable dt = new DataTable("RangValue");
WorkBook workbook = WorkBook.Load(fileStream);
WorkSheet worksheet = workbook.Sheets[workSheetName];
CellAddress begionCell = CellAddress.Parse(beginAddress);
CellAddress endCell = CellAddress.Parse(endAddress);
for (int i = begionCell.ColumnIndex; i <= endCell.ColumnIndex; i++)
{
dt.Columns.Add(new DataColumn(ExcelHelper.GetColumnLetterFromNumber(i)));
}
for (int j = begionCell.RowIndex; j <= endCell.RowIndex; j++)
{
DataRow dr = dt.NewRow();
int temCol = begionCell.ColumnIndex;
foreach (DataColumn dc in dt.Columns)
{
dr[dc.ColumnName] = worksheet.Cells[j, temCol].Value;
temCol++;
}
dt.Rows.Add(dr);
}
return(dt);
}
public new Cell this[string name]
{
get
{
CellAddress address = CellAddress.Parse(name);
return(this[address]);
}
}
/// <summary>
/// 创建文档，并将数填充到ExcelTable中
/// </summary>
/// <param name="worksheetName">工作表名称</param>
/// <param name="beginAddress">开始单元格</param>
/// <param name="tableName">ExcelTable名称</param>
/// <param name="dvData">数据源</param>
/// <param name="tableStyle">ExcelTable 样式</param>
/// <param name="isPrintHeaders"></param>
/// <returns></returns>
public static byte[] CreateDocumentAndTable(string worksheetName, string beginAddress, string tableName, System.Data.DataView dvData, ExcelTableStyles tableStyle)
{
WorkBook workbook = WorkBook.CreateNew();
WorkSheet worksheet = workbook.Sheets["sheet1"];
if (worksheetName.IsNotEmpty())
{
worksheet.Name = worksheetName;
}
dvData.Table.TableName = tableName;
worksheet.LoadFromDataView(CellAddress.Parse(beginAddress), tableStyle, dvData, null);
//worksheet.LoadFromDataView(beginAddress, dvData, tableName, tableStyle);
return(workbook.SaveAsBytes());
}
/// <summary>
/// 创建一个指定Excel 工作表名称Excel
/// </summary>
/// <param name="worksheetName">工作表名称</param>
/// <param name="excelAddress">开始填充数据Excel地址（例如：B2)</param>
/// <param name="dvData">待填充数据</param>
/// <param name="isPrintHeaders">是否显示数据源列名</param>
/// <returns></returns>
public static MemoryStream DocumentBuilder(string worksheetName, string excelAddress, System.Data.DataView dvData)
{
excelAddress.NullCheck("工作薄名称不能为空");
MemoryStream createExcelStream = new MemoryStream();
WorkBook workbook = WorkBook.CreateNew();
WorkSheet worksheet = workbook.Sheets["sheet1"];
if (worksheetName.IsNotEmpty())
{
worksheet.Name = worksheetName;
}
dvData.Table.TableName = string.Empty;
worksheet.LoadFromDataView(CellAddress.Parse(excelAddress), ExcelTableStyles.None, dvData, null);
//worksheet.LoadFromDataTable(excelAddress, dvData);
workbook.Save(createExcelStream);
return(createExcelStream);
}
public void InitDescription(TableDescriptionAttribute tableAttribute)
{
this.TableName = tableAttribute.TableName;
this.BeginAddress = tableAttribute.BeginAddress;
}
public Table Add(string name, CellAddress begionAddress, IDictionary <string, string> colums)
{
return(this.Add(name, begionAddress, colums, 1));
}
/// <summary>
/// 创建一个带有公式的Table
/// </summary>
/// <param name="name"></param>
/// <param name="begionAddress"></param>
/// <param name="colums"></param>
/// <returns></returns>
public Table Add(string name, string begionAddress, IDictionary <string, string> colums)
{
CellAddress startCell = CellAddress.Parse(begionAddress);
return(this.Add(name, startCell, colums));
}
/// <summary>
/// 逐行获取Table数据
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="tbDesp"></param>
/// <param name="getemptyObj"></param>
/// <param name="validationOperator"></param>
public string ForEachTableRows <T>(TableDescription tbDesp, SpreadGetTableCollectionParams <T> param, out int upCount)
{
tbDesp.NullCheck("tbDesp");
param.ExportRow.NullCheck("exportRow");
this.CheckTableExists(tbDesp.TableName);
StringBuilder customLog = new StringBuilder();
Table tb = this.Tables[tbDesp.TableName];
int rowIndex = 0; upCount = 0;
foreach (TableRow tr in tb.Rows)
{
ExcportRowContext context = new ExcportRowContext()
{
RowIndex = rowIndex
};
foreach (TableColumnDescription tc in tbDesp.AllColumns)
{
if (tb.Columns.ContainsKey(tc.ColumnName))
{
context.PropertyDescriptions.Add(new ExportCellDescription(tc.PropertyName)
{
TableColumnName = tc.ColumnName, Value = tr[tb.Columns[tc.ColumnName]].Value, Address = CellAddress.Parse(tb.Columns[tc.ColumnName].Position + tb.Address.StartColumn, tr.RowIndex + tb.Address.StartRow + 1).ToString()
});
}
}
ExportRowResult <T> result = param.ExportRow(context);
customLog.Append(result.ErrorLog);
rowIndex++;
if (result.Validated)
{
upCount++;
}
else
if (param.ValidationOperator == ValidationErrorStopMode.Stop)
{
break;
}
}
return(customLog.ToString());
}
/// <summary>
/// 从WorkSheet提取DataTable
/// </summary>
/// <param name="sheet">工作表对象</param>
/// <param name="beginAddress">开始位置</param>
/// <param name="throwException">数据不存在时是否抛出异常</param>
/// <returns>返回首行创建成TableHeader</returns>
public static DataTable GetRangeValuesAsTable(WorkSheet sheet, string beginAddress, bool throwException = false)
{
sheet.NullCheck("workbook");
DataTable dt = new DataTable("RangValue");
CellAddress begionCell = CellAddress.Parse(beginAddress);
CellAddress endCell = CellAddress.Parse(sheet.Dimension.EndColumn, sheet.Dimension.EndRow);
for (int i = begionCell.ColumnIndex; i <= endCell.ColumnIndex; i++)
{
var headCellAdress = CellAddress.Parse(i, begionCell.RowIndex);
var namedRange = sheet.Names.FirstOrDefault(p => p.Address.StartRow == headCellAdress.RowIndex &&
p.Address.StartRow == p.Address.EndRow &&
p.Address.EndColumn == headCellAdress.ColumnIndex &&
p.Address.StartColumn == p.Address.EndColumn);
object objValue = sheet.Cells[begionCell.RowIndex, i].Value;
DataColumn column = null;
if (namedRange == null)
{
if (objValue != null)
{
column = new DataColumn(objValue.ToString());
}
}
else
{
column = new DataColumn(namedRange.Name);
if (objValue != null)
{
column.Caption = objValue.ToString();
}
}
if (column != null)
{
dt.Columns.Add(column);
}
}
for (int j = begionCell.RowIndex + 1; j <= endCell.RowIndex; j++)
{
DataRow dr = dt.NewRow();
int temCol = begionCell.ColumnIndex;
int colIndex = 0;
while (temCol <= endCell.ColumnIndex && colIndex < dt.Columns.Count)
{
dr[colIndex] = sheet.Cells[j, temCol].Value;
temCol++;
colIndex++;
}
bool isEmptyRow = true;
for (int i = 0; i < dt.Columns.Count; i++)
{
if (!string.IsNullOrEmpty(dr[i].ToString()))
{
isEmptyRow = false;
break;
}
}
if (isEmptyRow == false)
{
dt.Rows.Add(dr);
}
}
ExceptionHelper.TrueThrow(throwException && dt.Rows.Count == 0, "未在Excel中找到数据");
return(dt);
}
/// <summary>
/// 将Excel数据填充到指定的模板
/// </summary>
/// <param name="input">模板文件件流</param>
/// <param name="worksheetName">工作表名称</param>
/// <param name="dvData">待填充数据</param>
/// <returns></returns>
public static MemoryStream FillExcelTemplatesByDefinedName(Stream input, string worksheetName, System.Data.DataView dvData, bool buyColumnName = false)
{
input.NullCheck("模版流不能为这空！");
dvData.NullCheck("填充的数据不能为空");
worksheetName.NullCheck("工作薄名称不能为空");
MemoryStream fillExcelStream = new MemoryStream();
WorkBook package = WorkBook.Load(input);
WorkSheet worksheet = package.Sheets[worksheetName];
worksheet.NullCheck("指定的工作表名称不存在！");
Dictionary <string, CellAddress> dtExcel = new Dictionary <string, CellAddress>();
List <string> formulaDictionary = new List <string>();
DefinedName nameRange;
CellAddress cellAddress = default(CellAddress);
Cell formulaCell = null;
foreach (DataColumn dc in dvData.Table.Columns)
{
if (buyColumnName)
{
nameRange = worksheet.Names[dc.ColumnName];
}
else
{
nameRange = worksheet.Names[string.IsNullOrEmpty(dc.Caption) ? dc.ColumnName : dc.Caption];
}
if (nameRange != null)
{
cellAddress = CellAddress.Parse(nameRange.Address.StartColumn, nameRange.Address.StartRow);
dtExcel.Add(dc.ColumnName, cellAddress);
formulaCell = worksheet.Cells[cellAddress.RowIndex + 1, cellAddress.ColumnIndex];
if (formulaCell != null)
{
if (formulaCell.Formula.IsNotEmpty())
{
formulaDictionary.Add(dc.ColumnName);
}
}
}
}
int formulaRowIndex = cellAddress.RowIndex + 1, addRowCount = 0;
worksheet.InserRows(formulaRowIndex, dvData.Count - 1, formulaRowIndex);
foreach (DataRowView dr in dvData)
{
foreach (KeyValuePair <string, CellAddress> key in dtExcel)
{
if (!formulaDictionary.Contains(key.Key))
{
worksheet.Cells[formulaRowIndex + addRowCount, key.Value.ColumnIndex].Value = dr[key.Key];
}
}
addRowCount++;
}
package.Save(fillExcelStream);
fillExcelStream.Seek(0, SeekOrigin.Begin);
return(fillExcelStream);
}
public Table(WorkSheet workSheet, string name, CellAddress beginAddress)
: this(workSheet, name, Range.Parse(workSheet, beginAddress.ColumnIndex, beginAddress.RowIndex, beginAddress.ColumnIndex, beginAddress.RowIndex))
{
}
public void LoadFromDataView(DataView dv)
{
this.LoadFromDataView(CellAddress.Parse("A1"), ExcelTableStyles.Light12, dv, null);
}
public bool HasCell(string name)
{
CellAddress address = CellAddress.Parse(name);
return(this.HasCell(address));
}
public bool HasCell(CellAddress address)
{
return(this.HasCell(address.RowIndex, address.ColumnIndex));
}
/// <summary>
/// 将Excel数据直充到指定的Collection中
/// </summary>
/// <typeparam name="T">数据模型</typeparam>
/// <typeparam name="TCollection">待待充Collection 必须实现ICollection<T> 接口</typeparam>
/// <param name="collection">待待充Collection</param>
/// <param name="tbDesp">Table相关描述信息</param>
/// <param name="param">填充时所准备的相关参数，包括初始化每一个对象委托，根据相关数据设置相关属性值委托（默认反射）</param>
/// <returns></returns>
public string GetCollectionFromTable <T, TCollection>(TCollection collection, TableDescription tbDesp, SpreadGetTableCollectionParams <T> param) where TCollection : ICollection <T>
{
tbDesp.NullCheck("tbDesp");
collection.NullCheck("数据集合不能为空");
param.ExportRow.NullCheck("exportRow");
this.CheckTableExists(tbDesp.TableName);
StringBuilder customLog = new StringBuilder();
Table tb = this.Tables[tbDesp.TableName];
int rowIndex = 0;
foreach (TableRow tr in tb.Rows)
{
ExcportRowContext context = new ExcportRowContext();
context.RowIndex = rowIndex;
foreach (TableColumnDescription tc in tbDesp.AllColumns)
{
if (tb.Columns.ContainsKey(tc.ColumnName))
{
context.PropertyDescriptions.Add(new ExportCellDescription(tc.PropertyName)
{
TableColumnName = tc.ColumnName, Value = tr[tb.Columns[tc.ColumnName]].Value, Address = CellAddress.Parse(tb.Columns[tc.ColumnName].Position + tb.Address.StartColumn, tr.RowIndex + tb.Address.StartRow + 1).ToString()
});
}
}
ExportRowResult <T> result = param.ExportRow(context);
if (result.Validated == true && result.CurrentObject != null)
{
collection.Add(result.CurrentObject);
}
customLog.Append(result.ErrorLog);
rowIndex++;
if (result.Validated == false && param.ValidationOperator == ValidationErrorStopMode.Stop)
{
break;
}
}
return(customLog.ToString());
}
