如何将数据从datagridview导出到excel

Question

这是一个导出到excel文件的简单方法

我尝试了Microsoft.Office.Interop.Excel，但是失败了。最后我找到了更简单的解决办法

Could not load file or assembly 'office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bc111e9429c' or one of its dependencies. The system cannot find the file specified

Answer 1

private void btnExport_Click(object sender, EventArgs e)
{
DataTable dataTable= new Database().SelectData("SelectAllSinhVien",lstPara);//return (DataTable) from database
string filePath = "";
SaveFileDialog dialog = new SaveFileDialog();
dialog.Filter = "Excel | *.xlsx";
if (dialog.ShowDialog() == DialogResult.OK)
{
filePath = dialog.FileName;
}
if (string.IsNullOrEmpty(filePath))
{
MessageBox.Show("Invalid!!!");
return;
}
ExcelPackage.LicenseContext= OfficeOpenXml.LicenseContext.NonCommercial;
try
{
using (ExcelPackage p = new ExcelPackage())
{
p.Workbook.Properties.Author = "Hà Văn Ri";
p.Workbook.Properties.Title = "Report File";
p.Workbook.Worksheets.Add("Sheet 1");
ExcelWorksheet ws = p.Workbook.Worksheets[0];
ws.Name = "Sheet 1";
ws.Cells.Style.Font.Size = 12;
ws.Cells.Style.Font.Bold = true;
ws.Cells.Style.Font.Name = "Times New Roman";
string[] arrColumnHeader =
{
"Mã sinh viên","Họ tên","Ngày sinh","Giới tính","Địa chỉ","Điện thoại","Email"//headerText list
};
var countColHeader = arrColumnHeader.Count();
ws.Cells[1, 1].Value = "Danh sách sinh viên";
ws.Cells[1, 1, 1, countColHeader].Merge = true;
ws.Cells[1, 1, 1, countColHeader].Style.HorizontalAlignment=OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
int colHeader = 1;
int rowHeader = 2;
foreach(var header in arrColumnHeader)
{

var cell = ws.Cells[rowHeader, colHeader];
var fill = cell.Style.Fill;
fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
fill.BackgroundColor.SetColor(System.Drawing.Color.LightBlue);

cell.Value = header;
colHeader++;
}
int colIndex = 1;
int rowIndex = 2;
foreach (DataRow dr in dataTable.Rows)//fields in obj
{
colIndex = 1;
rowIndex++;
ws.Cells[rowIndex, colIndex++].Value = dr["masinhvien"].ToString();
ws.Cells[rowIndex, colIndex++].Value = dr["hoten"].ToString();
ws.Cells[rowIndex, colIndex++].Value = dr["ngaysinh"].ToString();
ws.Cells[rowIndex, colIndex++].Value = dr["gioitinh"].ToString();
ws.Cells[rowIndex, colIndex++].Value = dr["diachi"].ToString();
ws.Cells[rowIndex, colIndex++].Value = dr["dienthoai"].ToString();
ws.Cells[rowIndex, colIndex++].Value = dr["email"].ToString();

}
Byte[] bin =p.GetAsByteArray();
File.WriteAllBytes(filePath, bin);
}
MessageBox.Show("Thành công");
}
catch (Exception ex )
{
MessageBox.Show("thất bại: "+ex);
}

}