110,576
社区成员
发帖
与我相关
我的任务
分享
/// <summary>
/// 将DataGridView列表数据导出到Excel
/// </summary>
/// <param name="dgv">DataGridView控件名称</param>
/// <param name="title">导到Excl显示的标题</param>
public static void DataGridViewOutPutExcel(System.Windows.Forms.DataGridView dgv, string title)
{
try
{
int rowCount = dgv.RowCount;
if (rowCount <= 0)
{
MessageBox.Show("表格中没有数据,无法导出数据!", "系统提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
int columnCount = 0;
foreach (DataGridViewColumn dHeader in dgv.Columns)
{
if (dHeader.Visible == true)
columnCount++;
}
Microsoft.Office.Interop.Excel.Application exc = new Microsoft.Office.Interop.Excel.Application();
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");
}
Range r = worksheet.get_Range(exc.Cells[1, 1], exc.Cells[1, columnCount]);
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;
//列头
int ColIndex = 1;
foreach (DataGridViewColumn dHeader in dgv.Columns)
{
if (dHeader.Visible == true)
worksheet.Cells[2, ColIndex++] = dHeader.HeaderText;
}
//填充单元格
ColIndex = 0;
foreach (DataGridViewColumn col in dgv.Columns)
{
if (col.Visible == true)
{
ColIndex++;
for (int i = 0; i < rowCount; i++)
{
if (dgv.Rows[i].Cells[col.Index].FormattedValue.ToString() == null)
continue;
worksheet.Cells[i + 3, ColIndex] = dgv.Rows[i].Cells[col.Index].FormattedValue.ToString();
}
}
}
exc.Cells.EntireColumn.AutoFit();
exc.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
exc.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;
exc.Visible = true;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}