public static void ExportToExcelFileEPPlus(string _sTitle, DataGridView _DGV)
{
int iColCount = _DGV.Columns.Count;
int iRowCount = _DGV.Rows.Count;
using (ExcelPackage ExcelPkg = new ExcelPackage())
{
try
{
ExcelWorksheet Sheet = ExcelPkg.Workbook.Worksheets.Add("sheet1");
//Title Row
Sheet.Cells["A1"].Value = _sTitle;
ExcelRange Range = Sheet.Cells[1, 1, 1, iColCount];
Range.Merge = true;
Range.Style.Font.Size = 14;
Range.Style.Font.Bold = true;
Range.Style.Font.Color.SetColor(System.Drawing.Color.FromArgb(79, 129, 189));
Range.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
Range.Style.Border.Bottom.Style = ExcelBorderStyle.Medium;
//Name Row
for (int i = 0; i < iColCount; i++)
{
Sheet.Cells[2, i + 1].Value = _DGV.Columns[i].HeaderText;
Sheet.Cells[2, i + 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
Range = Sheet.Cells[2, 1, 2, iColCount];
Range.Style.Font.Size = 12;
Range.Style.Font.Bold = true;
Range.Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
//Value Row
for (int i = 0; i < iRowCount; i++)
{
for (int j = 0; j < iColCount; j++)
{
Sheet.Cells[i + 3, j + 1].Value = _DGV.Rows[i].Cells[j].Value.ToString();
}
Sheet.Cells[i + 3, 1, i + 3, iColCount].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
Sheet.Cells[i + 3, 1, i + 3, iColCount].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
}
//Column Line
for (int i = 0; i < iColCount; i++)
{
Sheet.Column(i + 1).AutoFit(5);
Sheet.Cells[1, i + 1, iRowCount + 2, iColCount].Style.Border.Right.Style = ExcelBorderStyle.Thin;
}
Sheet.Cells.Style.WrapText = true;
//BorderAround Line
Range = Sheet.Cells[1, 1, iRowCount + 2, iColCount];
Range.Style.Border.BorderAround(ExcelBorderStyle.Medium);
//Encrypt
Sheet.Protection.IsProtected = true;
Sheet.Protection.SetPassword("verybull");
Sheet.Protection.AllowAutoFilter = true;
Sheet.Protection.AllowSort = true;
Sheet.Protection.AllowFormatCells = true;
Sheet.Protection.AllowFormatColumns = true;
Sheet.Protection.AllowFormatRows = true;
Sheet.Protection.AllowSelectUnlockedCells = true;
Sheet.Protection.AllowSelectLockedCells = true;
Sheet.Protection.AllowDeleteColumns = false;
Sheet.Protection.AllowDeleteRows = false;
Sheet.Protection.AllowEditObject = false;
Sheet.Protection.AllowEditScenarios = false;
Sheet.Protection.AllowInsertColumns = false;
Sheet.Protection.AllowInsertHyperlinks = false;
Sheet.Protection.AllowInsertRows = false;
Sheet.Protection.AllowPivotTables = false;
//Save
SaveFileDialog SaveDlg = new SaveFileDialog();
SaveDlg.AddExtension = true;
SaveDlg.DefaultExt = "xlsx";
SaveDlg.Filter = "Excel Worksheets(*.xlsx)|*.xlsx";
SaveDlg.InitialDirectory = System.Environment.CurrentDirectory;
SaveDlg.ShowDialog();
if (!SaveDlg.FileName.Equals(""))
{
System.IO.Stream stream = SaveDlg.OpenFile();
ExcelPkg.SaveAs(stream);
stream.Close();
Dlg.ShowOKInfo("导出报表文件成功！");
}
}
catch (Exception Ex)
{
Dlg.ShowErrorInfo(string.Format("异常:{0}", Ex.Message));
}
}
}
