/// <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 static Range Parse(WorkSheet workSheet, int startColumn, int startRow, int endColumn, int endRow) { return(new Range() { _WorkSheet = workSheet, StartRow = startRow, StartColumn = startColumn, EndRow = endRow, EndColumn = endColumn }); }
internal Table(WorkSheet workSheet, TableDescription tbDes) { this._WorkSheet = workSheet; this.Name = tbDes.TableName; this.Address = Range.Parse(workSheet, tbDes.BeginAddress.ColumnIndex, tbDes.BeginAddress.RowIndex, tbDes.BeginAddress.ColumnIndex, tbDes.BeginAddress.RowIndex); this.Columns.InitColumns(tbDes.AllColumns); }
/// <summary> /// 创建一个带计算公式的ExcelTable模版 /// </summary> /// <param name="tableName">工作表名称</param> /// <param name="beginAddress">开始址址</param> /// <param name="colums">字典存储,key,列名,value,计算公式 /// (例如:"ROUND(IF(OR([@社保缴纳类别值]=1,[@社保缴纳类别值]=3,[@社保缴纳类别值]=5), [@医疗有效基数], 0)*0.008,2)"); /// </param> /// <returns></returns> public static byte[] CreateFormulaTableTemplate(string tableName, string beginAddress, IDictionary <string, string> colums) { WorkBook workbook = WorkBook.CreateNew(); WorkSheet worksheet = workbook.Sheets["sheet1"]; worksheet.Tables.Add(tableName, beginAddress, colums); return(workbook.SaveAsBytes()); }
internal TableCollection Clone(WorkSheet worksheet) { TableCollection cloneResult = new TableCollection(worksheet); foreach (Table table in this) { cloneResult.Add(table.Clone(worksheet)); } return(cloneResult); }
internal Table(WorkSheet workSheet, string name, Range rangeAddress) { this._WorkSheet = workSheet; this.Name = name; if (this.Address.StartColumn != -1 && this.Address.StartRow != -1) { this.OldRange = this.Address; } this.Address = rangeAddress; }
/// <summary> /// 新创建一个Excel,里面包含一个工作簿sheet1 /// </summary> /// <returns></returns> public static WorkBook CreateNew() { WorkBook result = new WorkBook(); WorkBookView wbView = new WorkBookView(result); WorkSheet ws = new WorkSheet(result, DefaultSheetName); result.Views.Add(wbView); result.Sheets.Add(ws); return(result); }
public static Range Parse(WorkSheet workSheet, string rangeAddress) { rangeAddress.IsNullOrEmpty().TrueThrow <ArgumentNullException>("Rang地址不能为空"); int _StartRow, _StartColumn, _EndRow, _EndColumn; ExcelHelper.GetRowColFromAddress(rangeAddress, out _StartRow, out _StartColumn, out _EndRow, out _EndColumn); return(new Range() { _WorkSheet = workSheet, StartRow = _StartRow, StartColumn = _StartColumn, EndRow = _EndRow, EndColumn = _EndColumn }); }
/// <summary> /// 指定起始单元格,提取Excel文件数据,起始行使用Excel名称定义 /// </summary> /// <param name="workbook">工作簿对象</param> /// <param name="workSheetName">工作表名称</param> /// <param name="beginAddress">开始位置</param> /// <param name="throwException">数据不存在时是否抛出异常</param> /// <returns>返回首行创建成TableHeader</returns> public static DataTable GetRangeValuesAsTable(WorkBook workbook, string workSheetName, string beginAddress, bool throwException = false) { workbook.NullCheck("workbook"); workSheetName.CheckStringIsNullOrEmpty("workSheetName"); WorkSheet sheet = workbook.Sheets[workSheetName]; sheet.NullCheck(string.Format("不存在指定的{0}工薄!", workSheetName)); return(GetRangeValuesAsTable(sheet, beginAddress, throwException)); }
internal CellCollection Clone(WorkSheet workSheet) { CellCollection result = new CellCollection(workSheet); foreach (Cell cell in this) { result.Add(cell.Clone(workSheet)); } return(result); }
/// <summary> /// 将数据返回指定Excel中指定工作薄上,指定ExcelTabel的数据 /// </summary> /// <param name="input">Excel文件流</param> /// <param name="worksheetName">Excel工作薄名称</param> /// <param name="tableName">ExcelTable名称</param> /// <returns></returns> public static DataTable GetExcelTableData(Stream input, string worksheetName, string tableName) { input.NullCheck("数据模板为空!"); WorkBook workbook = WorkBook.Load(input); WorkSheet worksheet = workbook.Sheets[worksheetName]; worksheet.NullCheck(string.Format("不存在指定的{0}工作薄!", worksheetName)); Table table = worksheet.Tables[tableName]; table.NullCheck(string.Format("不存在指定的{0}Excel表格!", tableName)); return(table.AsDataTable()); }
/// <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> /// 根据指定工作表上的ExcelTable名称,填充数据 /// </summary> /// <param name="input">Excel模版流文件</param> /// <param name="dv">待填充数据</param> /// <param name="worksheetName">工作薄名称</param> /// <param name="tableName">ExcelTable名称</param> /// <returns></returns> public static byte[] FillExcelTable(Stream input, DataView dv, string worksheetName, string tableName) { dv.NullCheck("数据源不能为空!"); input.NullCheck("数据模板为空!"); WorkBook workbook = WorkBook.Load(input); WorkSheet worksheet = workbook.Sheets[worksheetName]; worksheet.NullCheck(string.Format("不存在指定的{0}工作薄!", worksheetName)); Table excelTable = worksheet.Tables[tableName]; excelTable.NullCheck(string.Format("不存在指定的{0}Excel表格!", tableName)); excelTable.Rows.Clear(); excelTable.FillData(dv); return(workbook.SaveAsBytes()); }
internal SheetView Clone(WorkSheet worksheet) { SheetView cloneSheetView = new SheetView(worksheet) { _TabSelected = this._TabSelected, TopLeftCell = this.TopLeftCell, SelectedRange = this.SelectedRange, ShowZeros = this.ShowZeros, _ZoomScale = this._ZoomScale, WindowProtection = this.WindowProtection, ShowWhiteSpace = this.ShowWhiteSpace, ShowHeaders = this.ShowHeaders, ShowGridLines = this.ShowGridLines, ShowFormulas = this.ShowFormulas, ShowRowColHeaders = this.ShowRowColHeaders }; return(cloneSheetView); }
public WorkSheet Clone(string sheetName) { WorkSheet cloneSheet = new WorkSheet(this.WorkBook, sheetName, this.Hidden) { _PhoneticProperties = this._PhoneticProperties, _PageSetup = this._PageSetup, Dimension = this.Dimension, _Validations = this._Validations, _Drawings = this._Drawings, ShowOutlineSymbols = this.ShowOutlineSymbols, OutLineApplyStyle = this.OutLineApplyStyle, OutLineSummaryBelow = this.OutLineSummaryBelow, _HeaderFooter = this._HeaderFooter, _TabColor = this._TabColor }; this.CloneData(cloneSheet); return(cloneSheet); }
/// <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); }
/// <summary> /// 根据WorkSheet定义单元格名称,转换成DataView /// </summary> /// <param name="fileSource">Excel文件流</param> /// <param name="sheetName">工作薄名称</param> /// <returns></returns> public static DataView ByDefinedNameExportSheetData(Stream fileSource, string sheetName) { DataTable dt = new DataTable(); WorkBook workbook = WorkBook.Load(fileSource); WorkSheet worksheet = workbook.Sheets[sheetName]; Dictionary <string, int> excelDataViewHeader = new Dictionary <string, int>(); int beginRowIndex = CreateBuyDefinedNameExportSheetDataTableHeader(worksheet, dt, excelDataViewHeader); beginRowIndex++; for (int i = beginRowIndex; i <= worksheet.Dimension.EndRow; i++) { DataRow dr = dt.NewRow(); foreach (KeyValuePair <string, int> dataKey in excelDataViewHeader) { dr[dataKey.Key] = worksheet.Cells[i, dataKey.Value].Value; } dt.Rows.Add(dr); } return(dt.DefaultView); }
private static int CreateBuyDefinedNameExportSheetDataTableHeader(WorkSheet worksheet, DataTable dt, Dictionary <string, int> excelDataViewHeader) { int beginRowIndex = 0; bool isGetBeginRowindex = true; foreach (DefinedName nameCell in worksheet.Names) { if (nameCell.Address.StartColumn == nameCell.Address.EndColumn && nameCell.Address.StartRow == nameCell.Address.EndRow) { DataColumn dc = new DataColumn(nameCell.Name); dc.Caption = nameCell.NameValue.ToString(); dt.Columns.Add(dc); excelDataViewHeader.Add(nameCell.Name, nameCell.Address.StartColumn); if (isGetBeginRowindex) { beginRowIndex = nameCell.Address.StartRow; } } } return(beginRowIndex); }
internal Table Clone(WorkSheet worksheet) { Table cloneTable = new Table(worksheet, this.Name, this.Address) { _NextColumnID = this._NextColumnID, _TableStyle = this._TableStyle, _StyleName = this._StyleName, _ShowFirstColumn = this._ShowFirstColumn, ShowLastColumn = this.ShowLastColumn, _ShowRowStripes = this._ShowRowStripes, ShowColumnStripes = this.ShowColumnStripes }; foreach (KeyValuePair <string, string> item in this.Attributes) { cloneTable.Attributes[item.Key] = item.Value; } cloneTable.DisplayName = string.Format("{0}_Clone{1}", this.Name, worksheet.WorkBook.GetTablesCount()); if (this.Attributes.ContainsKey("id") == true) { this.Attributes.Remove("id"); } if (this._Columns != null) { cloneTable._Columns = this._Columns.Clone(cloneTable); } if (this._Rows != null) { cloneTable._Rows = this._Rows.Clone(cloneTable); } return(cloneTable); }
private void CloneData(WorkSheet worksheet) { if (this._Rows != null) { worksheet._Rows = new ExcelIndexCollection <Row>(worksheet); foreach (Row row in this._Rows) { worksheet._Rows.Add(row.Clone(row.Index)); } } if (this._Columns != null) { worksheet._Columns = new ExcelIndexCollection <Column>(worksheet); foreach (Column column in this._Columns) { worksheet._Columns.Add(column.Clone(column.Index)); } } if (this._Cells != null) { worksheet._Cells = this._Cells.Clone(worksheet); } if (this._SheetView != null) { worksheet._SheetView = this._SheetView.Clone(worksheet); } if (this._Tables != null) { worksheet._Tables = this._Tables.Clone(worksheet); } }
internal Cell Clone(WorkSheet workSheet) { Cell cloneCell = new Cell(workSheet.Rows[this.Row.Index], workSheet.Columns[this.Column.Index]); cloneCell.IsMerge = this.IsMerge; cloneCell.IsRichText = this.IsRichText; cloneCell.Formula = this.Formula; cloneCell._SharedIndex = this._SharedIndex; cloneCell.FormulaSharedIndex = this.FormulaSharedIndex; cloneCell.DataType = this.DataType; if (this._StyleID != 0) { cloneCell.StyleID = this.StyleID; } if (this._Style != null) { cloneCell._Style = this._Style; } if (this._Hyperlink != null) { cloneCell._Hyperlink = this._Hyperlink; } if (this._Comment != null) { cloneCell._Comment = this._Comment.Clone(cloneCell); } cloneCell.Value = this.Value; return(cloneCell); }
public CellCollection(WorkSheet sheet) { this._Sheet = sheet; }
internal CommentCollection(WorkSheet worksheet, string relationshipID) : this(worksheet) { this._RelationshipID = relationshipID; }
public CommentCollection(WorkSheet worksheet) { this._WorkSheet = worksheet; }
public SheetView(WorkSheet sheet) { this._WrokSheet = sheet; }
internal VmlDrawingPictureCollection(WorkSheet worksheet, string relationshipID) : this(worksheet) { this._RelationshipID = relationshipID; }
public VmlDrawingPictureCollection(WorkSheet worksheet) { this._WorkSheet = worksheet; }
protected internal ExcelDrawing(WorkSheet worksheet) { this._WorkSheet = worksheet; }
internal DrawingCollection(WorkSheet worksheet, string relationshipId) : this(worksheet) { this.RelationshipID = relationshipId; }
public DrawingCollection(WorkSheet worksheet) { this._WorkSheet = worksheet; }