/// <summary>
/// 导出GridView的数据到Excel的方法--没有用服务器模式加载数据的使用
/// </summary>
/// <param name="GridView">要导出数据的GridView</param>
/// <param name="FileName">文件的名称</param>
/// <param name="TitleName">数据的标题</param>
/// <param name="Remark">备注</param>
private void ExportToExcel(GridView gridView, string fileName, string titleName, string[] remark)
{
#region
if (gridView.RowCount == 0)
{
MessageUtil.ShowError("没有数据可供导出!");
return;
}
else
{
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "导出文件保存路径";
saveFileDialog.FileName = fileName;
DialogResult dialog = saveFileDialog.ShowDialog();
if (dialog == DialogResult.Cancel) { return; }
string strName = saveFileDialog.FileName;
if (strName.Length != 0)
{
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true); ;
excel.Visible = false;//若是true,则在导出的时候会显示EXcel界面。
if (excel == null)
{
MessageUtil.ShowError("EXCEL无法启动!");
return;
}
Microsoft.Office.Interop.Excel.Workbooks books = (Microsoft.Office.Interop.Excel.Workbooks)excel.Workbooks;
Microsoft.Office.Interop.Excel.Workbook book = (Microsoft.Office.Interop.Excel.Workbook)(books.Add(miss));
Microsoft.Office.Interop.Excel.Worksheet sheet = (Microsoft.Office.Interop.Excel.Worksheet)book.ActiveSheet;
sheet.Name = "test";
//显示标题
excel.Cells[1, gridView.Columns.Count / 2] = titleName;
//生成字段名称
int columnCount = 0;//列的数量
/*数据一列一列的填充*/
for (int i = 0; i < gridView.Columns.Count; i++)
{
//只显示可见的列
if (gridView.Columns[i].Visible)
{
excel.Cells[3, columnCount + 1] = gridView.Columns[i].Caption.ToString();
columnCount++;
/*填充数据*/
for (int j = 0; j < gridView.RowCount; j++)
{
if (gridView.GetRowCellValue(j, gridView.Columns[i]).GetType() == typeof(string))
{
excel.Cells[j + 4, columnCount] = "'" + gridView.GetRowCellValue(j, gridView.Columns[i]).ToString();
}
else
{
excel.Cells[j + 4, columnCount] = gridView.GetRowCellValue(j, gridView.Columns[i]).ToString();
}
}
}
}
//加入备注
for (int s = 0; s < remark.Length; s++)
{
excel.Cells[gridView.RowCount + 5, s + 1] = remark[s];
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet);
System.Runtime.InteropServices.Marshal.ReleaseComObject(book);
System.Runtime.InteropServices.Marshal.ReleaseComObject(books);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
GC.Collect();
MessageUtil.ShowTips("数据已经成功导出!");
System.Diagnostics.Process.Start(strName);
}
}
#endregion
}