/// <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));
}
/// <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="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());
}
/// <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);
}
