public static void ToMergeDgv <Title, Model>(this MergeDataGridView mdgv, List <Model> objs)
{
if (mdgv.MergeColumnNames == null)
{
mdgv.MergeColumnNames = new List <string>();
}
if (mdgv.ColumnCount == 0)
{
var ps = typeof(Model).GetProperties();
foreach (var p in ps)
{
var pDcrp = p.GetDcrp();
var column = new DataGridViewTextBoxColumn
{
Name = p.Name,
Tag = p.GetReference(),
DataPropertyName = p.Name,
HeaderText = pDcrp,
FillWeight = GetColumnWeight(pDcrp)
};
mdgv.Columns.Add(column);
mdgv.MergeColumnNames.Add(p.Name);
}
}
mdgv.DataSource = objs;
mdgv.SetMdgvStyle();
MergeHeaders <Title>(mdgv);
mdgv.SetCellInfos();
}
public static void SetMdgvStyle(this MergeDataGridView mdgv)
{
mdgv.SetDgvStyle();
mdgv.DefaultCellStyle.SelectionBackColor = mdgv.DefaultCellStyle.BackColor;
mdgv.DefaultCellStyle.SelectionForeColor = mdgv.DefaultCellStyle.ForeColor;
}
/// <summary>
/// 合并列标题
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="mdgv"></param>
public static void MergeHeaders <T>(this MergeDataGridView mdgv)
{
var lastSum = 0;
for (var i = 0; i < typeof(T).GetProperties().Length; i++)
{
var p = typeof(T).GetProperties()[i];
var spanNum = GetSpan(p);
mdgv.AddSpanHeader(GetDcrp(p), lastSum, spanNum);
lastSum += spanNum;
}
}
/// <summary>
/// 导入到表格控件中
/// </summary>
/// <typeparam name="Title"></typeparam>
/// <typeparam name="Model"></typeparam>
/// <param name="mdgv"></param>
/// <param name="objs"></param>
/// <param name="mergeCell"></param>
public static void ImportDgv <Title, Model>(this MergeDataGridView mdgv, List <Model> objs, bool mergeCell = true)
{
if (mdgv.ColumnCount == 0)
{
for (var i = 0; i < typeof(Model).GetProperties().Length; i++)
{
var p = typeof(Model).GetProperties()[i];
var pDcrp = GetDcrp(p);
var column = new DataGridViewTextBoxColumn
{
Name = p.Name,
Tag = GetReference(p),
DataPropertyName = p.Name,
HeaderText = string.IsNullOrEmpty(pDcrp) ? string.Empty : pDcrp,
FillWeight = pDcrp == null || pDcrp.Length > 10 ? 7
: pDcrp.Length > 6 ? 4
: pDcrp.Length < 4 ? 3
: pDcrp.Length
};
mdgv.Columns.Add(column);
mdgv.MergeColumnNames.Add(p.Name);
}
}
// 数据源
mdgv.DataSource = objs;
// 设置表格样式
mdgv.SetDgvStyle();
// 设置跨列合并单元格
MergeHeaders <Title>(mdgv);
// 设置合并内容单元格
MergeCell(mdgv);
}
public static ExcelPackage ToExcel <Title>(this MergeDataGridView mdgv, ExcelParam param, bool createHeader = true)
{
if (!param.FilePath.Exists)
{
File.Copy(param.TemplatePath.FullName, param.FilePath.FullName);
}
var excel = param.GetExcelPackage(out var sheet);
var columns = mdgv.Columns.Cast <DataGridViewColumn>().Where(w => w.Visible).ToList();
if (createHeader)
{
var lastSum = param.ColumnIndex;
var ps = typeof(Title).GetProperties();
// Merges first title and sets first title value.
foreach (var p in ps)
{
var spanNum = p.GetSpan();
var columnDcrp = p.GetDcrp();
var range = sheet.Cells[param.RowIndex, lastSum, param.RowIndex, lastSum + spanNum - 1];
range.Value = columnDcrp;
if (string.IsNullOrEmpty(sheet.Cells[param.RowIndex, param.ColumnIndex].Value?.ToString()))
{
sheet.Column(lastSum).Width = GetSheetWidth(columnDcrp);
}
// If the span num equal 1, don't set merge.
if (spanNum != 1 && !range.Merge)
{
range.Merge = true;
}
lastSum += spanNum;
}
// Merges Second title.
for (var i = 0; i < columns.Count; i++)
{
sheet.Cells[param.RowIndex + 1, i + param.ColumnIndex].Value = columns[i].HeaderText;
}
// Merges title .
for (var i = 0; i < columns.Count; i++)
{
var cell = sheet.Cells[param.RowIndex, i + param.ColumnIndex];
// If merged, continue.
if (cell.Merge)
{
continue;
}
// Value not equal, continue.
if (cell.Value?.ToString() != sheet.Cells[param.RowIndex + 1, i + param.ColumnIndex].Value?.ToString())
{
continue;
}
// Sets merge.
sheet.Cells[param.RowIndex, i + param.ColumnIndex, param.RowIndex + 1, i + param.ColumnIndex].Merge = true;
}
}
// Sets content cell value.
for (var i = 0; i < mdgv.RowCount; i++)
{
for (var j = 0; j < columns.Count; j++)
{
sheet.Cells[i + param.RowIndex + 2, j + param.ColumnIndex].Value = mdgv.Rows[i].Cells[columns[j].Name].Value;
}
}
if (createHeader)
{
// Merges content cell.
for (var i = 0; i < mdgv.ColumnCount; i++)
{
for (var j = 0; j < mdgv.RowCount; j++)
{
var upRowNum = mdgv.GetUpRowNum(j, i) - 1;
var downRowNum = mdgv.GetDownRowNum(j, i) - 1;
var cell = sheet.Cells[j + param.RowIndex, i + param.ColumnIndex];
var tag = mdgv.Columns[i].Tag?.ToString();
if (!string.IsNullOrEmpty(tag))
{
var tempIndex = mdgv.Columns[tag]?.Index;
upRowNum = mdgv.GetUpRowNum(j, tempIndex ?? 0) - 1;
downRowNum = mdgv.GetDownRowNum(j, tempIndex ?? 0) - 1;
}
if (cell.Merge)
{
continue;
}
var range = sheet.Cells[j + param.RowIndex - upRowNum, i + param.ColumnIndex, j + param.RowIndex + downRowNum, i + param.ColumnIndex];
if (!range.Merge)
{
range.Merge = true;
}
}
}
}
sheet.SetExcelStyle();
excel.Save();
return(excel);
}
public static void ToMergeDgv <Title, Model>(this MergeDataGridView mdgv, ExcelParam param)
{
var data = param.AsList <Model>();
mdgv.ToMergeDgv <Title, Model>(data);
}
/// <summary>
/// 导出到文件
/// </summary>
/// <param name="mdgv"></param>
/// <param name="filePath"></param>
/// <param name="sheetName"></param>
public static ExcelPackage ExportFile <Title, Model>(this MergeDataGridView mdgv, string filePath, string sheetName = "Sheet1")
{
var fileInfo = new FileInfo(filePath);
var excel = new ExcelPackage(fileInfo);
if (excel.Workbook.Worksheets.FirstOrDefault(f => f.Name == sheetName) != null)
{
excel.Workbook.Worksheets.Delete(sheetName);
}
var worksheet = excel.Workbook.Worksheets.Add(sheetName);
var index = 0;
var lastSum = 1;
worksheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
worksheet.Cells.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
// 一级标题
foreach (var p in typeof(Title).GetProperties())
{
var spanNum = GetSpan(p);
var columnDcrp = GetDcrp(p);
// 合并格子和索引简单的数学关系可知道需要减1，坐标轴图示即可
worksheet.Cells[1, lastSum, 1, lastSum + spanNum - 1].Value = columnDcrp;
worksheet.Column(lastSum).Width = columnDcrp.Length > 10 ? 15
: columnDcrp.Length > 6 ? 20
: columnDcrp.Length < 4 ? 8 : 10;
// 只有一个格子不要修改融合属性
if (lastSum != lastSum + spanNum - 1)
{
worksheet.Cells[1, lastSum, 1, lastSum + spanNum - 1].Merge = true;
}
lastSum += spanNum;
}
// 二级标题
foreach (var column in mdgv.Columns.Cast <DataGridViewColumn>().Where(w => w.Visible).ToList())
{
worksheet.Cells[2, index + 1].Value = column.HeaderText;
index++;
}
// 标题融合
for (var i = 0; i < typeof(Model).GetProperties().Length; i++)
{
// 融合过的，跳过
if (worksheet.Cells[1, i + 1].Merge)
{
continue;
}
// 值不等，跳过
if (worksheet.Cells[1, i + 1].Value?.ToString() != worksheet.Cells[2, i + 1].Value?.ToString())
{
continue;
}
// 融合
worksheet.Cells[1, i + 1, 2, i + 1].Merge = true;
// 垂直居中
worksheet.Cells[1, i + 1, 2, i + 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
}
// 单元格赋值
for (var i = 0; i < mdgv.RowCount; i++)
{
index = 0;
foreach (var column in mdgv.Columns.Cast <DataGridViewColumn>().Where(w => w.Visible).ToList())
{
worksheet.Cells[i + 3, index + 1].Value = mdgv.Rows[i].Cells[column.Name].Value;
index++;
}
}
// 单元格合并
// 遍历列
for (var i = 1; i <= worksheet.Dimension.Columns; i++)
{
// 遍历行
for (var j = 3; j <= worksheet.Dimension.Rows; j++)
{
var upRowsNum = mdgv.GetUpRowNum(j - 3, i - 1) - 1;
var downRowNum = mdgv.GetDownRowNum(j - 3, i - 1) - 1;
var curCell = worksheet.Cells[j, i];
// 控件列索引从0开始
var tag = mdgv.Columns[i - 1].Tag.ToString();
if (tag != string.Empty)
{
var columnIndex = mdgv.Columns[tag]?.Index;
upRowsNum = mdgv.GetUpRowNum(j - 3, columnIndex ?? 0) - 1;
downRowNum = mdgv.GetDownRowNum(j - 3, columnIndex ?? 0) - 1;
}
if (curCell.Merge)
{
continue;
}
if (worksheet.Cells[j - upRowsNum, i].Merge)
{
continue;
}
// 融合
worksheet.Cells[j - upRowsNum, i, j + downRowNum, i].Merge = true;
}
}
excel.Save();
return(excel);
}
/// <summary>
/// 合并内容单元格
/// </summary>
public static void MergeCell(this MergeDataGridView mdgv)
{
mdgv.SetCellInfos();
}
