private IEnumerable <CellMarkEntity> ProcessMarkCell <T>(ISheet sheet, ref string errorMessage)
where T : class, new()
{
IWorkbook workBook = sheet.Workbook;
int NumberOfNames = workBook.NumberOfNames;
List <CellMarkEntity> list = new List <CellMarkEntity>();
CellMarkEntity markEntity = null;
T entity = new T();
PropertyInfo[] infos = entity.GetType().GetProperties(System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance);
for (int index = 0; index < NumberOfNames; index++)
{
markEntity = new CellMarkEntity();
IName iname = workBook.GetNameAt(index);
string region = iname.RefersToFormula.Replace(iname.SheetName, "").Replace("!", "").Replace("$", "");
string[] regions = region.Split(':');
int rowIndex = 0;
int colIndex = 0;
// 获取行位置和列位置
DealPostion(regions[0], ref rowIndex, ref colIndex);
if (iname.NameName.StartsWith("&"))
{
markEntity.TransGain = true;
}
markEntity.CellIndex = colIndex;
markEntity.RowIndex = rowIndex;
markEntity.IName = iname;
markEntity.TitleName = sheet.GetRow(rowIndex).GetCell(colIndex).ToString();
string name = iname.NameName.Replace("$", "").Replace("&", "");
IEnumerable <PropertyInfo> tempInfo = infos.Where(t => t.Name.Equals(name, StringComparison.OrdinalIgnoreCase));
if (tempInfo != null && tempInfo.Any())
{
PropertyInfo info = tempInfo.FirstOrDefault();
markEntity.ColumnName = info.Name;
markEntity.PropertyInfo = info;
IEnumerable <Attribute> attributes = info.GetCustomAttributes();
if (attributes != null && attributes.Any())
{
markEntity.Attribute = attributes.FirstOrDefault();
}
list.Add(markEntity);
}
else
{
errorMessage = "第【{0}】行第【{1}】列配置不正确，请联系管理员";
errorMessage = string.Format(errorMessage, rowIndex + 1, ExportExcelUtil.IndexToColName(colIndex));
break;
}
}
return(list.AsEnumerable());
}
private CellDataEntity ProcessCell(ISheet sheet, CellMarkEntity item, IEnumerable <CellMergeEntity> cellMerges, IRow row = null)
{
CellDataEntity cellData = new CellDataEntity();
cellData.TitleName = item.TitleName;
cellData.ColumnName = item.ColumnName;
cellData.ColIndex = item.CellIndex;
ICell cell = null;
int rowIndex = 0;
if (row == null)
{
rowIndex = item.RowIndex;
cell = sheet.GetRow(item.RowIndex).GetCell(item.CellIndex + 1);
}
else
{
rowIndex = row.RowNum;
cell = row.GetCell(item.CellIndex);
}
if (cell == null)
{
return(null);
}
cellData.RowIndex = rowIndex;
if (cell.IsMergedCell == false)
{
cellData.CellPostion = string.Concat(ExportExcelUtil.IndexToColName(item.CellIndex), rowIndex + 1);
Type originalType = item.PropertyInfo.PropertyType.GetGenericArguments()[0];
string cellValue = "";
switch (cell.CellType)
{
case CellType.Numeric:
if (HSSFDateUtil.IsCellDateFormatted(cell))
{
cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
}
else
{
cellValue = cell.NumericCellValue.ToString();
}
break;
case CellType.String:
cellValue = cell.StringCellValue;
break;
case CellType.Boolean:
cellValue = cell.BooleanCellValue.ToString();
break;
case CellType.Formula:
if (originalType == typeof(DateTime))
{
cellValue = cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
}
else
{
cell.SetCellType(CellType.String);
cellValue = cell.ToString();
}
break;
}
cellData.IsEmpty = string.IsNullOrEmpty(cellValue);
cellData.CellValue = cellValue;
//if (originalType == typeof(DateTime))
//{
// cellData.CellValue = cell.DateCellValue.ToString();
//}
//else
//{
// if (cell.CellType == CellType.Formula)
// cell.SetCellType(CellType.String);
// cellData.CellValue = cell.ToString();
//}
}
else
{
CellDimension dimension = ProcessCellDimension(cell, cellMerges);
var merges = cellMerges.Where(t => t.FirstRow == dimension.FirstRowIndex && t.FirstColumn == dimension.FirstColIndex);
cellData.CellPostion = string.Concat(ExportExcelUtil.IndexToColName(dimension.FirstColIndex), dimension.FirstRowIndex + 1);
if (merges != null && merges.Any())
{
string cellValue = merges.FirstOrDefault().DataCell.ToString();
cellData.CellValue = cellValue;
}
}
cellData.Length = StringUtil.StrLength(cellData.CellValue);
return(cellData);
}
private string BuildRecSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataRow drMain, DataTable dtSub)
{
string errorMessage = string.Empty;
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 设置开始行和列
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
#region 对表头数据进行赋值
// 标题行
IRow rowTitle = sheet.CreateRow(beginRow);
ICell cellTitle = rowTitle.CreateCell(beginCol);
cellTitle.SetCellValue(sheetEntity.SheetTitle);
// 设置下划线
IFont fontLine = workBook.CreateFont();
fontLine.Underline = FontUnderlineType.Single;
if (helpEntity.TitleBoldMark == true)
{
fontLine.FontHeight = (double)FontBoldWeight.Bold;
}
cellTitle.CellStyle.SetFont(fontLine);
cellTitle.CellStyle.Alignment = HorizontalAlignment.Center;
cellTitle.CellStyle.VerticalAlignment = VerticalAlignment.Center;
rowTitle.Height = helpEntity.THeight;
// 标题行合并
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, beginCol, beginCol + helpEntity.ExportColumns.Count() - 1));
beginRow++;
// 表头字段集合
var masterColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
var subColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
int maxColIndex = masterColumn.Select(t => t.ColIndex).Max();
int minColIndex = masterColumn.Select(t => t.ColIndex).Min();
var listTitleContent = masterColumn;
var temp = masterColumn.Select(t => t.RowIndex).Distinct();
int colIndex = beginCol;
// 循环赋值表头数据
foreach (var rowIndex in temp)
{
IRow rowContent = sheet.CreateRow(beginRow);
var curRow = listTitleContent.Where(t => t.RowIndex == rowIndex).OrderBy(t => t.ColIndex);
colIndex = beginCol;
var curColMinIndex = curRow.Select(t => t.ColIndex).Min();
var diff = curColMinIndex - minColIndex;
// 此处判断主要是为了有跳过列的
if (diff > 0)
{
// 标题
colIndex += diff;
// 内容
colIndex += diff;
}
foreach (var item in curRow)
{
colIndex = colIndex - item.diffNum;
// 先赋值标题，再赋值
ICell curTitle = rowContent.CreateCell(colIndex);
curTitle.SetCellValue(item.ExcelName);
curTitle.CellStyle = item.CellStyle;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
ICell cell = rowContent.CreateCell(colIndex);
string curColumn = item.ColumnName;
object curValue = drMain[curColumn];
if (item.TitleColSpan > 1)
{
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + item.TitleColSpan - 1));
colIndex++;
}
cell.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
beginRow++;
}
#endregion
// 获取和循环设置表体表体行
var listTitle = helpEntity.ExportColumns;
IRow rowSubTitle = sheet.CreateRow(beginRow);
colIndex = beginCol;
foreach (var item in listTitle)
{
ICell cellSubTitle = rowSubTitle.CreateCell(colIndex);
cellSubTitle.SetCellValue(item.ExcelName);
sheet.SetColumnWidth(colIndex, item.Width);
colIndex++;
}
beginRow++;
// 冻结上述行和列
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(beginCol, beginRow - 1, beginCol, beginRow - 1);
}
// 循环赋值列表数据
foreach (DataRow dr in dtSub.Rows)
{
IRow rowSubContent = sheet.CreateRow(beginRow);
colIndex = beginCol;
foreach (var item in listTitle)
{
ICell cellSubContent = rowSubContent.CreateCell(colIndex);
object curValue = dr[item.ColumnName];
// 设置表体内容
cellSubContent.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
beginRow++;
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum + 1, 0 + helpEntity.SkipRowNum + 1, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
private string BuildBillSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtMain, DataTable[] dtSub)
{
string errorMessage = string.Empty;
// 在工作簿建立空白工作表
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 看是否有跳过
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
IRow rowHead = sheet.CreateRow(beginRow);
// 循环添加表头
int colIndex = beginCol;
var tempMast = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
var tempSub = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
var tempCol = tempMast.Union(tempSub);
foreach (var item in tempCol)
{
if (item.Hidden)
{
continue;
}
ICell cell = rowHead.CreateCell(colIndex);
cell.SetCellValue(item.ExcelName);
cell.CellStyle = item.CellStyle;
sheet.SetColumnWidth(colIndex, item.Width);
rowHead.Height = helpEntity.THeight;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
}
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1);
}
colIndex = beginCol;
beginRow++;
//循环赋值内容
for (int i = 0; i < dtMain.Rows.Count; i++)
{
DataRow drMain = dtMain.Rows[i];
DataTable dtData = dtSub[i];
for (int j = 0; j < dtData.Rows.Count; j++)
{
DataRow drSub = dtData.Rows[j];
IRow row = sheet.CreateRow(beginRow);
ICell cell = null;
colIndex = beginCol;
foreach (var col in tempCol)
{
if (col.Hidden)
{
continue;
}
cell = row.CreateCell(colIndex);
object value = null;
if (col.PrimaryMark == true)
{
value = drMain[col.ColumnName];
if (helpEntity.OneMain == true && j > 0)
{
colIndex++;
continue;
}
}
value = drSub[col.ColumnName];
cell.SetCellValue(value, col, col.CellStyle);
colIndex++;
}
beginRow++;
}
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
/// <summary>
/// 处理每个工作表数据
/// </summary>
/// <param name="dtData"></param>
/// <param name="helpEntity"></param>
/// <returns></returns>
private string BuildSinpleSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData)
{
string errorMessage = string.Empty;
// 在工作簿建立空白工作表
ISheet sheet = null;
if (!string.IsNullOrEmpty(sheetEntity.SheetName))
{
sheet = workBook.CreateSheet(sheetEntity.SheetName);
}
else
{
sheet = workBook.CreateSheet();
}
// 看是否有跳过
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
IRow rowHead = sheet.CreateRow(beginRow);
// 循环添加表头
int colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
if (item.Hidden)
{
continue;
}
ICell cell = rowHead.CreateCell(colIndex);
cell.SetCellValue(item.ExcelName);
cell.CellStyle = item.CellStyle;
sheet.SetColumnWidth(colIndex, item.Width);
rowHead.Height = helpEntity.THeight;
IName iname = workBook.CreateName();
iname.NameName = item.ColumnName;
iname.RefersToFormula = string.Concat(sheet.SheetName, "!$", ExportExcelUtil.IndexToColName(colIndex), "$", beginRow + 1);
colIndex++;
}
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(0, 0 + helpEntity.SkipRowNum + 1, 0, helpEntity.SkipRowNum + 1);
}
colIndex = beginCol;
beginRow++;
//循环赋值内容
foreach (DataRow dr in dtData.Rows)
{
IRow rowContent = sheet.CreateRow(beginRow);
ICell cell = null;
colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
if (item.Hidden)
{
continue;
}
cell = rowContent.CreateCell(colIndex);
object curValue = dr[item.ColumnName];
cell.SetCellValue(curValue, item, item.CellStyle);
colIndex++;
}
rowContent.Height = (short)helpEntity.CHeight;
beginRow++;
}
// 筛选
if (helpEntity.AutoFilter)
{
CellRangeAddress c = new CellRangeAddress(0 + helpEntity.SkipRowNum, 0 + helpEntity.SkipRowNum, beginCol, colIndex);
sheet.SetAutoFilter(c);
}
sheet.DisplayGridlines = helpEntity.ShowGridLine;
ProcessSheet(sheet, helpEntity);
return(errorMessage);
}
