/// <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 bool HasCell(string name) { CellAddress address = CellAddress.Parse(name); return(this.HasCell(address)); }
/// <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> /// 从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 void LoadFromDataView(DataView dv) { this.LoadFromDataView(CellAddress.Parse("A1"), ExcelTableStyles.Light12, dv, null); }
/// <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> /// 将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()); }