/// <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;
}