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>
/// 简单列表数据导出
/// 这里DataTable数据是已经排序过的，表头名称
/// </summary>
/// <returns></returns>
private string RunExportSimple(ExportRunEntity helpEntity, params DataTable[] dtData)
{
/** 模板类型：标题行和内容行有边框， 其他网格线取消
* 标题行 XX XX XX XX
* 内容行 xx xx xx xx
*
* 自定义内容 XX:xx XX:xx
* */
string errorMessage = string.Empty;
if (dtData == null)
{
return("导出数据为空");
}
foreach (var data in dtData)
{
if (data == null || data.Rows.Count == 0)
{
errorMessage = "导出数据为空";
break;
}
}
if (!string.IsNullOrEmpty(errorMessage))
{
return(errorMessage);
}
if (helpEntity.ExportColumns == null || helpEntity.ExportColumns.Count() == 0)
{
return("导出配置为空");
}
if (!string.IsNullOrEmpty(errorMessage))
{
return(errorMessage);
}
// 处理工作簿、文件名
string fileName = BeforeDealSheet(helpEntity);
// 处理每个工作表数据
ExportSheetEntity sheetEntity = null;
for (int i = 0; i < dtData.Length; i++)
{
sheetEntity = new ExportSheetEntity();
if (i < helpEntity.SheetName.Count)
{
sheetEntity.SheetName = helpEntity.SheetName[i];
}
if (i < helpEntity.SheetTitle.Count)
{
sheetEntity.SheetTitle = helpEntity.SheetTitle[i];
}
errorMessage = BuildSinpleSheet(helpEntity, sheetEntity, dtData[i]);
if (!string.IsNullOrEmpty(errorMessage))
{
break;
}
}
// 如果存在错误，则终止执行
if (!string.IsNullOrEmpty(errorMessage))
{
return(errorMessage);
}
// 写入Http
HttpWrite(fileName);
return(errorMessage);
}
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 BuildMergeSheet(ExportRunEntity helpEntity, ExportSheetEntity sheetEntity, DataTable dtData)
{
string errorMessage = string.Empty;
// 设置开始行、列
int beginRow = 0 + helpEntity.SkipRowNum;
int beginCol = 0 + helpEntity.SkipColNum;
ISheet sheet = workBook.CreateSheet();
#region 标题处理
IRow rowTitle1 = sheet.CreateRow(beginRow);
rowTitle1.Height = helpEntity.THeight;
int colIndex = beginCol;
// 先赋值合并行标题
foreach (var mergeName in sheetEntity.MergeNames)
{
var temp = helpEntity.ExportColumns.Where(t => t.MergeName == mergeName);
var len = temp.Count();
// 获取合并标题开始列位置
int index = GetIndex(helpEntity.ExportColumns, mergeName);
colIndex = index;
ICell cellMerge = rowTitle1.CreateCell(colIndex);
cellMerge.SetCellValue(mergeName);
//cellMerge.CellStyle = cellStyleHead;
// 设置合并
sheet.AddMergedRegion(new CellRangeAddress(beginRow, beginRow, colIndex, colIndex + len - 1));
//for (int tempIndex = colIndex; tempIndex < colIndex + len; tempIndex++)
// HSSFCellUtil.GetCell(rowTitle1, tempIndex).CellStyle = cellStyleHead;
}
beginRow++;
IRow rowTitle2 = sheet.CreateRow(beginRow);
rowTitle2.Height = helpEntity.THeight;
colIndex = beginCol;
foreach (var item in helpEntity.ExportColumns)
{
ICell cellTitle = rowTitle2.CreateCell(colIndex);
cellTitle.SetCellValue(item.ExcelName);
cellTitle.CellStyle = item.CellStyle;
// 如果此标题在上述没有合并，则向上合并一列，保持美观
if (string.IsNullOrEmpty(item.MergeName))
{
sheet.AddMergedRegion(new CellRangeAddress(beginRow - 1, beginRow, colIndex, colIndex));
ICell cellTemp = HSSFCellUtil.GetCell(rowTitle1, colIndex);
//cellTemp.CellStyle = cellStyleHead;
cellTemp.SetCellValue(item.ExcelName);
}
sheet.SetColumnWidth(colIndex, item.Width);
colIndex++;
}
// 冻结标题行
if (helpEntity.FreezeTitleRow)
{
sheet.CreateFreezePane(beginCol + helpEntity.MergeColNum, 2, beginCol + helpEntity.MergeColNum, 2);
}
#endregion
beginRow++;
// 循环赋值列表内容
foreach (DataRow dr in dtData.Rows)
{
IRow rowContent = sheet.CreateRow(beginRow);
rowContent.Height = helpEntity.CHeight;
colIndex = beginCol;
ICell cell = null;
// 赋值内容
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 = helpEntity.CHeight;
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);
}
/// <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);
}
/// <summary>
/// 对账单类型导出
/// </summary>
/// <param name="title">标题</param>
/// <param name="dtMain"></param>
/// <param name="dtSub"></param>
/// <param name="helpEntity"></param>
/// <returns></returns>
private string RunExportReconciliation(ExportRunEntity helpEntity, DataTable dtMain, params DataTable[] dtSub)
{
/** 模板类型：标题行以上（含）需要冻结，使excel拉动时一直显示
* xxx 对账单 （加上下划线）
* 供方：xxx 电话：xxx 传值：xxx 货币类别：xxx(需要加批准)
* 客户：xxx 电话：xxx 传值：xxx 核对日期：xxx
* 扣款金额：xxx 金额合计：xxx
* 标题行 XXX XXX XXX XXX XXX XXX XXX XXX
* 内容行 xxx xxx xxx xxx xxx xxx xxx xxx
*
* 批准：如下
* 提示:
* RMB:人民币,单价为含税价格
* USD:美元,单价为不含税价格
* */
#region 前期判判断和导出准备
string errorMessage = string.Empty;
if (dtMain == null || dtMain.Rows.Count == 0 || dtSub == null || (dtMain.Rows.Count != dtSub.Length))
{
return("导出数据为空");
}
if (helpEntity.ExportColumns == null || helpEntity.ExportColumns.Any() == false)
{
return("配置数据为空");
}
var masterColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == true);
var subColumn = helpEntity.ExportColumns.Where(t => t.PrimaryMark == false);
if (masterColumn == null || masterColumn.Any() == false)
{
return("主表配置数据为空");
}
if (subColumn == null || subColumn.Any() == false)
{
return("子表配置数据为空");
}
int maxColIndex = masterColumn.Select(t => t.ColIndex).Max();
int minColIndex = masterColumn.Select(t => t.ColIndex).Min();
if (minColIndex == maxColIndex)
{
return("主表配置列不能全在一个位置");
}
#endregion
var temp = masterColumn.Select(t => t.RowIndex).Distinct();
foreach (var rowIndex in temp)
{
List <int> colIndexs = masterColumn.Where(t => t.RowIndex == rowIndex).OrderBy(t => t.ColIndex).Select(t => t.ColIndex).Distinct().ToList();
bool result = EqualDiif(colIndexs, 1);
if (result == false)
{
errorMessage = "列间距必须为1";
break;
}
}
if (!string.IsNullOrEmpty(errorMessage))
{
return(errorMessage);
}
// 处理工作簿、文件名
string fileName = BeforeDealSheet(helpEntity);
ExportSheetEntity sheetEntity = null;
for (int i = 0; i < dtMain.Rows.Count; i++)
{
var dtData = dtSub[i];
sheetEntity = new ExportSheetEntity();
if (i < helpEntity.SheetName.Count)
{
sheetEntity.SheetName = helpEntity.SheetName[i];
}
if (i < helpEntity.SheetTitle.Count)
{
sheetEntity.SheetTitle = helpEntity.SheetTitle[i];
}
BuildRecSheet(helpEntity, sheetEntity, dtMain.Rows[i], dtData);
}
// 写入Http
HttpWrite(fileName);
return(errorMessage);
}
/// <summary>
/// 导出标题合并类型
/// </summary>
/// <param name="dtData"></param>
/// <param name="helpEntity"></param>
/// <returns></returns>
private string RunExportMerge(ExportRunEntity helpEntity, params DataTable[] dtData)
{
/** 模板类型：
* 合并标题行 第5周 第6周
* 标题行 编号 名称 型号 采购周期 需求 总需求 即时库存 需求 总需求 即时库存
* 内容行 xx xx xx xx xx xx xx xx xx xx
* */
string errorMessage = string.Empty;
if (dtData == null)
{
return("导出数据为空");
}
foreach (var dt in dtData)
{
if (dt == null || dt.Rows.Count == 0)
{
errorMessage = "导出数据为空";
break;
}
}
if (!string.IsNullOrEmpty(errorMessage))
{
return(errorMessage);
}
if (helpEntity.ExportColumns == null || helpEntity.ExportColumns.Any() == false)
{
return("导出配置为空");
}
IEnumerable <string> mergeNames = helpEntity.ExportColumns.Where(t => string.IsNullOrEmpty(t.MergeName) == false).Select(t => t.MergeName).Distinct();
if (mergeNames == null || mergeNames.Any() == false)
{
return("合并标题为空");
}
// 处理工作簿、文件名
string fileName = BeforeDealSheet(helpEntity);
// 处理每个工作表数据
ExportSheetEntity sheetEntity = null;
for (int i = 0; i < dtData.Length; i++)
{
sheetEntity = new ExportSheetEntity();
if (i < helpEntity.SheetName.Count)
{
sheetEntity.SheetName = helpEntity.SheetName[i];
}
if (i < helpEntity.SheetTitle.Count)
{
sheetEntity.SheetTitle = helpEntity.SheetTitle[i];
}
sheetEntity.MergeNames = mergeNames;
errorMessage = BuildMergeSheet(helpEntity, sheetEntity, dtData[i]);
if (!string.IsNullOrEmpty(errorMessage))
{
break;
}
}
// 如果存在错误，则终止执行
if (!string.IsNullOrEmpty(errorMessage))
{
return(errorMessage);
}
// 写入Http
HttpWrite(fileName);
return(errorMessage);
}
