将数据从datagridview传输到excel时没有问题,因此代码有效。但有一个问题;关闭我的计算机后,然后打开空白的Excel表。当我将数据从DataGridView导出到Excel时,打开计算机并打开计算机后出于某种原因发生这种情况。这是我的代码:
SaveFileDialog sfd = new SaveFileDialog();
sfd.InitialDirectory = "C:";
sfd.Title = "Save as an Excel File";
sfd.FileName = "MyList";
sfd.Filter = "Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
if(sfd.ShowDialog() != DialogResult.Cancel)
{
Microsoft.Office.Interop.Excel.Application ea = new Microsoft.Office.Interop.Excel.Application();
ea.Application.Workbooks.Add(Type.Missing);
ea.Columns.ColumnWidth = 45;
for (int a = 1; a < dgv.Columns.Count + 1; a++)
{
ExcelApp.Cells[1, a] = dgv.Columns[a - 1].HeaderText;
}
for (int b1 = 0; b1 < dgv.Rows.Count - 1; b1++)
{
for (int b2 = 0; b2 < dgv.Columns.Count; b2++)
{
ea.Cells[b1 + 2, b2 + 1] = dgv.Rows[b1].Cells[b2].Value;
}
}
ea.ActiveWorkbook.SaveCopyAs(sfd.FileName.ToString());
ea.ActiveWorkbook.Saved = true;
ea.Quit();
}
请帮助。
您的代码看起来过于复杂。您可以尝试下面的示例并反馈吗?
using System;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using Excel = Microsoft.Office.Interop.Excel;
namespace WindowsApplication1
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
SqlConnection cnn ;
string connectionString = null;
string sql = null;
connectionString = "data source=servername;initial catalog=databasename;user id=username;password=password;";
cnn = new SqlConnection(connectionString);
cnn.Open();
sql = "SELECT * FROM Product";
SqlDataAdapter dscmd = new SqlDataAdapter(sql, cnn);
DataSet ds = new DataSet();
dscmd.Fill(ds);
dataGridView1.DataSource = ds.Tables[0];
}
private void button2_Click(object sender, EventArgs e)
{
Excel.Application xlApp ;
Excel.Workbook xlWorkBook ;
Excel.Worksheet xlWorkSheet ;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.Application();
xlWorkBook = xlApp.Workbooks.Add(misValue);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
int i = 0;
int j = 0;
for (i = 0; i <= dataGridView1.RowCount - 1; i++)
{
for (j = 0; j <= dataGridView1.ColumnCount - 1; j++)
{
DataGridViewCell cell = dataGridView1[j, i];
xlWorkSheet.Cells[i + 1, j + 1] = cell.Value;
}
}
xlWorkBook.SaveAs("csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();
releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);
MessageBox.Show("Excel file created , you can find the file c:\csharp.net-informations.xls");
}
private void releaseObject(object obj)
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
obj = null;
}
catch (Exception ex)
{
obj = null;
MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
}
finally
{
GC.Collect();
}
}
}
}
http://csharp.net-informations.com/excel/csharp-excel-datagridview.htm