public void CreateExcelFile(string filePath)
{
DatabaseHelper db = new DatabaseHelper();
db.TestConnection();
DataTable tbTable = db.GetAllTable();
DataTable tbColumn = db.GetAllColumn();
//
ExcelPackage _excelPkg = new ExcelPackage();
for (int i = 0; i < tbTable.Rows.Count; i++)
{
var _columnList = db.GetColumnInfor(tbTable.Rows[i][TableName.NAME].ToString().ToUpper());
ExcelContentModel _excelModel = CreateExcelModel(tbTable.Rows[i][TableName.NAME].ToString().ToUpper(), _columnList);
CreateSheet(_excelPkg, _excelModel);
}
//
_excelPkg.SaveAs(new FileInfo(filePath));
//
Console.WriteLine("Finish");
OpenExcelFile();
}
private ExcelContentModel CreateExcelModel(string sheetName, DataTable data)
{
ExcelContentModel _return = new ExcelContentModel();
_return.SheetName = sheetName;
_return.ShowGridLines = false;
_return.IsProtected = false;
_return.AllowSelectLockedCells = false;
int _row = 7;
#region " [ Data ] "
_return.Data.Add(new ExcelDataDetailModel()
{
Row = 3,
Col = 2,
Value = "Nhóm",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = 4,
Col = 2,
Value = "Tên vật lý",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = 5,
Col = 2,
Value = "Tên logic",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 2,
Value = "STT",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 3,
Value = "Column name",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 4,
Value = "Physical name",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 5,
Value = "Primary key",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 6,
Value = "Data type",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 7,
Value = "Data length",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 8,
Value = "Allow null",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 9,
Value = "Index",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 10,
Value = "Indentity",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 11,
Value = "Init value",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 12,
Value = "Unique",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 13,
Value = "Foreign key",
Bold = false
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row,
Col = 14,
Value = "Memo",
Bold = false
});
for (int i = 0; i < data.Rows.Count; i++)
{
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 2,
Value = (i + 1 + 1000)
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 4,
Value = data.Rows[i][ColumnName.ColName].ToString()
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 5,
Value = data.Rows[i][ColumnName.PrimaryKey].ToString()
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 6,
Value = data.Rows[i][ColumnName.DataType].ToString()
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 7,
Value = data.Rows[i][ColumnName.MaxLength]
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 8,
Value = data.Rows[i][ColumnName.IsNull].ToString()
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 10,
Value = data.Rows[i][ColumnName.Identity].ToString()
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 11,
Value = data.Rows[i][ColumnName.Default].ToString()
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 12,
Value = data.Rows[i][ColumnName.Unique].ToString()
});
_return.Data.Add(new ExcelDataDetailModel()
{
Row = _row + i + 1,
Col = 13,
Value = data.Rows[i][ColumnName.ForeignKey].ToString()
});
}
#endregion
#region " [ Merge ] "
_return.Merges.Add(new ExcelMergeDetailModel()
{
FromRow = 3,
FromColumn = 3,
ToRow = 3,
ToColumn = 5
});
_return.Merges.Add(new ExcelMergeDetailModel()
{
FromRow = 4,
FromColumn = 3,
ToRow = 4,
ToColumn = 5
});
_return.Merges.Add(new ExcelMergeDetailModel()
{
FromRow = 5,
FromColumn = 3,
ToRow = 5,
ToColumn = 5
});
#endregion
#region " [ Horizontal ] "
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = 3,
FromColumn = 2,
ToRow = 5,
ToColumn = 5,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row,
FromColumn = 2,
ToRow = _row,
ToColumn = 14,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row + 1,
FromColumn = 2,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 2,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row + 1,
FromColumn = 5,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 5,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row + 1,
FromColumn = 7,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 7,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row + 1,
FromColumn = 8,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 8,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row + 1,
FromColumn = 9,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 9,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row + 1,
FromColumn = 10,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 10,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row + 1,
FromColumn = 11,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 11,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
_return.Horizontals.Add(new ExcelHorizontalDetailModel()
{
FromRow = _row + 1,
FromColumn = 12,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 12,
Horizontal = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center
});
#endregion
#region " [ Vertical ] "
#endregion
#region " [ Border ] "
_return.Borders.Add(new ExcelBorderDetailModel()
{
FromRow = 3,
FromColumn = 2,
ToRow = 5,
ToColumn = 5,
Border = OfficeOpenXml.Style.ExcelBorderStyle.Thin
});
_return.Borders.Add(new ExcelBorderDetailModel()
{
FromRow = _row,
FromColumn = 2,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 14,
Border = OfficeOpenXml.Style.ExcelBorderStyle.Thin
});
#endregion
#region " [ Formatting ] "
_return.Formatings.Add(new ExcelFromatingDetailModel()
{
FromRow = _row + 1,
FromColumn = 2,
ToRow = _row + data.Rows.Count + 1,
ToColumn = 2,
Format = "#,##0"
});
#endregion
return(_return);
}
private ExcelWorksheet CreateSheet(ExcelPackage excelPackage, ExcelContentModel excelModel)
{
ExcelWorksheet _sheet = excelPackage.Workbook.Worksheets.Add(excelModel.SheetName);
//Data content
foreach (var item in excelModel.Data)
{
_sheet.Cells[item.Row, item.Col].Value = item.Value;
if (item.Bold)
{
_sheet.Cells[item.Row, item.Col].Style.Font.Bold = item.Bold;
}
}
//Horizontal formating
foreach (var item in excelModel.Horizontals)
{
using (ExcelRange range = _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn])
{
range.Style.HorizontalAlignment = item.Horizontal;
}
}
//Vertical formating
foreach (var item in excelModel.Verticals)
{
using (ExcelRange range = _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn])
{
range.Style.VerticalAlignment = item.Vertical;
}
}
//Merge
foreach (var item in excelModel.Merges)
{
using (ExcelRange range = _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn])
{
range.Merge = true;
}
}
//Border
foreach (var item in excelModel.Borders)
{
using (ExcelRange range = _sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn])
{
range.Style.Border.Top.Style = item.Border;
range.Style.Border.Right.Style = item.Border;
range.Style.Border.Bottom.Style = item.Border;
range.Style.Border.Left.Style = item.Border;
}
}
//Set format text for all sheet
// _sheet.Cells.Style.Numberformat.Format = "@";
//Format specific cell
foreach (var item in excelModel.Formatings)
{
_sheet.Cells[item.FromRow, item.FromColumn, item.ToRow, item.ToColumn].Style.Numberformat.Format = item.Format;
}
//
_sheet.Cells.Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
_sheet.Cells.AutoFitColumns();
_sheet.View.ShowGridLines = excelModel.ShowGridLines;
_sheet.Protection.IsProtected = excelModel.IsProtected;
_sheet.Protection.AllowSelectLockedCells = excelModel.AllowSelectLockedCells;
return(_sheet);
}
