问题
如何将dataGridView 导出到Excel
需求：
dataGridView控件承载了查询得来的数据，现在需要通过一个button控件将dataGridView控件中的数据导出到Excel中。
已经测试4种方法，但都有问题：
方法1：导出成功，但Excel列错乱，无法正常使用该Excel。
方法2：导出成功，但Excel打开有警告: “您尝试打开的文件“XX.XLS”的格式与文件扩展名指定的格式不一致。打开文件前请验证文件没有损坏且来源可信。是否立即打开该文件？”
方法3：导出失败
方法4：导出失败
我想请教， 微软建议通过什么方法来导出dataGridView为Excel ?
以下为代码， 供参考：
``````````````````````````````````````````````````````````````
方法1:
private void button_Excel_Click(object sender, EventArgs e)
{
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();
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("11.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:\\11.xls");
//button_Excel_Click 代码结束
}
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();
}
}
方法2：
private void button_Excel_Click(object sender, EventArgs e)
{
SaveFileDialog dlg = new SaveFileDialog();
dlg.Filter = "Execl files (*.xls)|*.xls";
dlg.FilterIndex = 0;
dlg.RestoreDirectory = true;
dlg.CreatePrompt = true;
dlg.Title = "保存为Excel文件";
if (dlg.ShowDialog() == DialogResult.OK)
{
Stream myStream;
myStream = dlg.OpenFile();
//StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
//StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("gb2312"));
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding("utf-32"));
string columnTitle = "";
try
{
//写入列标题
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i > 0)
{
columnTitle += "\t";
}
columnTitle += dataGridView1.Columns[i].HeaderText;
}
sw.WriteLine(columnTitle);
//写入列内容
for (int j = 0; j < dataGridView1.Rows.Count; j++)
{
string columnValue = "";
for (int k = 0; k < dataGridView1.Columns.Count; k++)
{
if (k > 0)
{
columnValue += "\t";
}
if (dataGridView1.Rows[j].Cells[k].Value == null)
columnValue += "";
else
columnValue += dataGridView1.Rows[j].Cells[k].Value.ToString().Trim();
}
sw.WriteLine(columnValue);
}
sw.Close();
myStream.Close();
}
catch (Exception e_excel)
{
MessageBox.Show(e_excel.ToString());
}
finally
{
sw.Close();
myStream.Close();
MessageBox.Show("导出成功！");
}
}
方法3：
private void button_Excel_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog1 = new SaveFileDialog();
saveFileDialog1.Filter = "Excel files (*.xls)|*.xls";
saveFileDialog1.FilterIndex = 0;
saveFileDialog1.RestoreDirectory = true;
saveFileDialog1.CreatePrompt = true;
saveFileDialog1.Title = "Export Excel File To";
saveFileDialog1.ShowDialog();
string strName = saveFileDialog1.FileName;
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Excel.Application excel = new Excel.ApplicationClass();
Excel.Workbooks books = (Excel.Workbooks)excel.Workbooks;
Excel.Workbook book = (Excel.Workbook)(books.Add(miss));
Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
sheet.Name = "test";
int colIndex=0;
foreach (DataGridViewColumn column in dataGridView1.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = column.HeaderText;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
excel.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value.ToString();
}
}
sheet.SaveAs(strName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss);
book.Close(false, miss, miss);
books.Close();
excel.Quit();
excel = null;
//System.Runtime.InteropServices.Marshal.ReleaseComObject();
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();
}
方法4：
private void button_Excel_Click(object sender, EventArgs e)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Execl files (*.xls)|*.xls";
saveFileDialog.FilterIndex = 0;
saveFileDialog.RestoreDirectory = true;
saveFileDialog.CreatePrompt = true;
saveFileDialog.Title = "Export Excel File To";
saveFileDialog.ShowDialog();
Stream myStream;
myStream = saveFileDialog.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding(-0));
string str = "";
try
{
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i > 0)
{
str += "\t";
}
str += dataGridView1.Columns[i].HeaderText;
}
sw.WriteLine(str);
for (int j = 0; j < dataGridView1.Rows.Count; j++)
{
string tempStr = "";
for (int k = 0; k < dataGridView1.Columns.Count; k++)
{
if (k > 0)
{
tempStr += "\t";
}
tempStr += dataGridView1.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(tempStr);
}
sw.Close();
myStream.Close();
}
catch (Exception e)
{
MessageBox.Show(e.ToString());
}
finally
{
sw.Close();
myStream.Close();
}
}
|
|
| ssmilelake | |
| 注册 Nov 2011 | |