public static void ToExcel(string typeName)
{
var baseColumnsFile = new FileInfo(Resources.Json_BaseColumns);
var baseColumns = JsonUtil.Deserialize <string[]>(baseColumnsFile);
var baseItemsFile = new FileInfo(Resources.Json_BaseItems);
var baseItems = JsonUtil.Deserialize <string[]>(baseItemsFile);
var customColumnFile = new FileInfo(Resources.Json_CustomColumns);
var customColumns = JsonUtil.Deserialize <string[]>(customColumnFile);
var customItemsFile = new FileInfo(Resources.Json_CustomItems);
var customItems = JsonUtil.Deserialize <string[][]>(customItemsFile);
var templatePath = new FileInfo(Resources.Excel_DefaultTemplate);
var columnInfo = new ColumnInfo(baseColumns, baseItems, customColumns, customItems);
var typeTemplate = new FileInfo(typeName + "_" + templatePath.Name);
var param1 = new ExcelParam(typeTemplate, templatePath);
param1.BuildTemplate(columnInfo);
var fileName = Path.GetFileNameWithoutExtension(typeTemplate.FullName);
var a1File = new FileInfo(fileName + DateTime.Now.ToString("_yyMMddHHmm") + typeTemplate.Extension);
var param2 = new ExcelParam(a1File, typeTemplate);
param2.BuildData(columnInfo);
}
public static void BuildData(this ExcelParam param, ColumnInfo columnInfo)
{
var items = new object[1][];
var offsetNum = 0;
var sumLength = 0;
foreach (var customItem in columnInfo.CustomItems)
{
sumLength += customItem.Length;
}
var tempItems = new string[sumLength - columnInfo.CustomItems.Length];
for (var i = 0; i < columnInfo.CustomItems[0].Length; i++)
{
for (var j = 0; j < columnInfo.CustomItems[i].Length; j++)
{
var tempIndex = i * columnInfo.CustomItems[i].Length + j - offsetNum;
if (j == columnInfo.SkipIndex)
{
offsetNum += 1;
continue;
}
tempItems[tempIndex] = columnInfo.CustomItems[i][j];
}
}
items[0] = columnInfo.BaseItems.Concat(tempItems).ToArray <object>();
param.ToExcel(items);
}
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 ToDgv <T>(this DataGridView dgv, ExcelParam param)
{
var data = param.AsList <T>();
dgv.ToDgv(data);
}
public static void ToMergeDgv <Title, Model>(this MergeDataGridView mdgv, ExcelParam param)
{
var data = param.AsList <Model>();
mdgv.ToMergeDgv <Title, Model>(data);
}