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