/// <summary>
/// 导出到Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="title"></param>
public static void DataGridViewToExcel(DataGridView dgv, string title)
{
if (dgv.DataSource == null || dgv.Columns.Count == 0)
{
return;
}
Excel.Application exc = new Excel.ApplicationClass();
if (exc == null)
{
throw new Exception("Excel无法启动");
}
Workbooks workbooks = exc.Workbooks;
_Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
Sheets sheets = exc.Sheets;
_Worksheet worksheet = (_Worksheet)sheets[1];
if (worksheet == null)
{
throw new Exception("Worksheet error");
}
DataGridViewClipboardCopyMode bakMode = dgv.ClipboardCopyMode;
bool copyCurrentCellText = true;
bool showRowHeader = dgv.RowHeadersVisible;
if (dgv is gvGridView)
{
copyCurrentCellText = ((gvGridView)dgv).CopyCurrentCellText;
((gvGridView)dgv).CopyCurrentCellText = false;
}
dgv.RowHeadersVisible = false;
IDataObject bakClipbordDasta = Clipboard.GetDataObject();
dgv.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText;
IDataObject exportData = dgv.GetClipboardContent();
if (exportData != null)
{
DataObject data = exportData as DataObject;
Clipboard.SetDataObject(data.GetText(TextDataFormat.UnicodeText));
}
if (dgv is gvGridView)
{
((gvGridView)dgv).CopyCurrentCellText = copyCurrentCellText;
}
dgv.RowHeadersVisible = showRowHeader;
if (exportData != null)
{
exc.Columns.NumberFormatLocal = "@";
worksheet.Paste(worksheet.get_Range("A2", System.Type.Missing), false);
}
Clipboard.SetDataObject(bakClipbordDasta);
Range r = worksheet.get_Range(exc.Cells[1, 1], exc.Cells[1, dgv.Columns.Count]);
exc.Visible = false;
r.MergeCells = true;
if (r == null)
{
MessageBox.Show("Range无法启动");
throw new Exception("Range error");
}
//以上是一些例行的初始化工作,下面进行具体的信息填充
//标题
exc.ActiveCell.FormulaR1C1 = title;
exc.ActiveCell.Font.Size = 12;
exc.ActiveCell.Font.Bold = true;
exc.Cells.EntireColumn.AutoFit();
//加边框
exc.Cells.EntireColumn.Borders.LineStyle = 1;
exc.Cells.EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThick;
exc.Cells.EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThick;
exc.Cells.EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThick;
exc.Cells.EntireColumn.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
exc.Cells.VerticalAlignment = Excel.Constants.xlCenter;
exc.Cells.HorizontalAlignment = Excel.Constants.xlCenter;
exc.Visible = true;
}