来访人员登记系统（八）C#datagridview导出Excel文档

导出Excel文件

using Microsoft.Office.Interop.Excel;
private void button_log_excel_Click(object sender, EventArgs e)
{
    if (MessageBox.Show("确定导出Excel文件？", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
    {
        string fileName = "来访日志";
        string saveFileName = "";
        SaveFileDialog saveDialog = new SaveFileDialog();
        saveDialog.DefaultExt = "xls";
        saveDialog.Filter = "Excel文件|*.xls";
        saveDialog.FileName = fileName;
        saveDialog.ShowDialog();
        saveFileName = saveDialog.FileName;
                
        // 取消操作
        if (saveFileName.IndexOf(":") < 0)
        {
            return ;
        }

        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        if (xlApp == null)
        {
            MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
            return;
        }
        Workbooks workbooks = xlApp.Workbooks;
        Workbook workbook = workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Worksheet worksheet = (Worksheet)workbook.Worksheets[1];

        // 写入标题
        for (int i = 0; i < dataGridView5.ColumnCount; i++)
        {
            worksheet.Cells[1, i + 1] = dataGridView5.Columns[i].HeaderText;
            worksheet.Cells[1, i + 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;
        }

        // 写入数值
        for (int r = 0; r < dataGridView5.Rows.Count; r++)
        {
            for (int i = 0; i < dataGridView5.ColumnCount; i++)
            {
                if (i != 1)
                    worksheet.Cells[r + 2, i + 1] = dataGridView5.Rows[r].Cells[i].Value;
                else
                    worksheet.Cells[r + 2, i + 1] = "'" + dataGridView5.Rows[r].Cells[i].Value;
            }
            System.Windows.Forms.Application.DoEvents();
        }

        worksheet.Columns.EntireColumn.AutoFit();

        // 获取Excel导出格式
        int FormatNum;
        string Version;
        Version = xlApp.Version;
        if (Convert.ToDouble(Version) < 12)
        {
            FormatNum = -4143;
        }
        else
        {
            FormatNum = 56;
        }

        // 导出文件
        if (saveFileName != "")
        {
            try
            {
                workbook.Saved = true;
                workbook.SaveAs(saveFileName, FormatNum);
            }
            catch (Exception ex)
            {
                MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
            }
        }
        xlApp.Quit();
        GC.Collect();
        MessageBox.Show("文件： " + fileName + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
}

---

总结

如果字段的值太长导致写入时出现科学计数法，如本例中的身份证号，则可以在写入时，在值的前面加一个'符号，将该值设置为excel中的文本类型；
导出时需要对excel表格的版本进行设置，否则导出的文件可能出现打不开的问题，设置方法是在SaveAs()中加入版本参数，参数可以由excel应用对象获取。