/// <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);
}
/// <summary>
/// 转换成地址
/// </summary>
/// <returns></returns>
public override string ToString()
{
StringBuilder result = new StringBuilder();
if (this._WorkSheet != null)
{
result.Append(this._WorkSheet.Name);
result.Append("!$");
}
if (this.StartColumn != -1 || this.StartRow != -1)
{
result.Append(ExcelHelper.GetColumnLetterFromNumber(this.StartColumn));
result.Append("$");
result.Append(this.StartRow.ToString(CultureInfo.InvariantCulture));
if (this.StartColumn != this.EndColumn || this.StartRow != this.EndRow || this._EndColumn != -1 || this._EndRow != -1)
{
result.Append(":");
result.Append("$");
result.Append(ExcelHelper.GetColumnLetterFromNumber(this.EndColumn));
result.Append("$");
result.Append(this.EndRow.ToString(CultureInfo.InvariantCulture));
}
}
return(result.ToString());
}
/// <summary>
/// 转换在NameRange地址
/// </summary>
/// <returns></returns>
public string ToDefinedNameAddress()
{
StringBuilder result = new StringBuilder();
//if (!string.IsNullOrEmpty(this._SheetNme))
if (this._WorkSheet != null)
{
result.Append("'");
result.Append(this._WorkSheet.Name);
result.Append("'!$");
}
result.Append(ExcelHelper.GetColumnLetterFromNumber(this.StartColumn));
result.Append("$");
result.Append(this.StartRow.ToString(CultureInfo.InvariantCulture));
if (this.StartColumn != this.EndColumn || this.StartRow != this.EndRow)
{
result.Append(":");
result.Append("$");
result.Append(ExcelHelper.GetColumnLetterFromNumber(this.EndColumn));
result.Append("$");
result.Append(this.EndRow.ToString(CultureInfo.InvariantCulture));
}
return(result.ToString());
}
/// <summary>
/// 不包含工作薄名称
/// </summary>
/// <returns></returns>
public string ToAddress()
{
StringBuilder result = new StringBuilder();
if (this.StartColumn != -1 || this.StartRow != -1)
{
result.Append(ExcelHelper.GetColumnLetterFromNumber(this.StartColumn));
result.Append(this.StartRow.ToString(CultureInfo.InvariantCulture));
if (this.StartColumn != this.EndColumn || this.StartRow != this.EndRow || this._EndColumn != -1 || this._EndRow != -1)
{
result.Append(":");
result.Append(ExcelHelper.GetColumnLetterFromNumber(this.EndColumn));
result.Append(this.EndRow.ToString(CultureInfo.InvariantCulture));
}
}
return(result.ToString());
}
/// <summary>
/// 返回地址
/// </summary>
/// <returns></returns>
public override string ToString()
{
return(string.Format("{0}{1}", ExcelHelper.GetColumnLetterFromNumber(this.ColumnIndex), this.RowIndex));
}
public string ToAddress()
{
return(string.Format("${0}${1}", ExcelHelper.GetColumnLetterFromNumber(this.Column.Index), this.Row.Index.ToString(CultureInfo.InvariantCulture)));
}
public override string ToString()
{
return(string.Format("{0}{1}", ExcelHelper.GetColumnLetterFromNumber(this.Column.Index), this.Row.Index.ToString(CultureInfo.InvariantCulture)));
}