问题
如何将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();
}
}
答案
-
使用如下方法，
/// <summary>
/// 通过文件结构直接生成xls文件
/// </summary>
public class ExcelWriter
{
FileStream _wirter;
public ExcelWriter(string strPath)
{
_wirter = new FileStream(strPath, FileMode.OpenOrCreate);
}
/// <summary>
/// 写入short数组
/// </summary>
/// <param name="values"></param>
private void _writeFile(short[] values)
{
foreach (short v in values)
{
byte[] b = BitConverter.GetBytes(v);
_wirter.Write(b, 0, b.Length);
}
}
/// <summary>
/// 写文件头
/// </summary>
public void BeginWrite()
{
_writeFile(new short[] { 0x809, 8, 0, 0x10, 0, 0 });
}
/// <summary>
/// 写文件尾
/// </summary>
public void EndWrite()
{
_writeFile(new short[] { 0xa, 0 });
_wirter.Close();
}
/// <summary>
/// 写一个数字到单元格x,y
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void WriteNumber(short x, short y, double value)
{
_writeFile(new short[] { 0x203, 14, x, y, 0 });
byte[] b = BitConverter.GetBytes(value);
_wirter.Write(b, 0, b.Length);
}
/// <summary>
/// 写一个字符到单元格x,y
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void WriteString(short x, short y, string value)
{
byte[] b = Encoding.Default.GetBytes(value);
_writeFile(new short[] { 0x204, (short)(b.Length + 8), x, y, 0, (short)b.Length });
_wirter.Write(b, 0, b.Length);
}
}public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable("cart");
DataColumn dc1 = new DataColumn("areaid", Type.GetType("System.String"));
DataColumn dc2 = new DataColumn("house", Type.GetType("System.String"));
DataColumn dc3 = new DataColumn("seq", Type.GetType("System.String"));
DataColumn dc4 = new DataColumn("remark", Type.GetType("System.String"));
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);
dt.Columns.Add(dc4);
DataRow dr = dt.NewRow();
dr["areaid"] = "北京";
dr["house"] = "北京";
dr["seq"] = "2";
dr["remark"] = "货付款";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["areaid"] = "北京";
dr1["house"] = "上海";
dr1["seq"] = "1";
dr1["remark"] = "货付款";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["areaid"] = "上海";
dr2["house"] = "上海";
dr2["seq"] = "1";
dr2["remark"] = "货付款";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["areaid"] = "上海";
dr3["house"] = "北京";
dr3["seq"] = "1";
dr3["remark"] = "付款";
dt.Rows.Add(dr3);
string[] columns = {"城市","名称","顺序","备注"};
OutputXLSFromDataTable(columns, dt, saveFileDialog1);
}
/// <summary>
/// 导出DataTable为XLS，并打开生成的XLS
/// </summary>
/// <param name="columns">列名</param>
/// <param name="dt">表</param>
/// <param name="saveFileDialog">对话框</param>
/// <returns></returns>
public static bool OutputXLSFromDataTable(string[] columns, DataTable dt, SaveFileDialog saveFileDialog)
{
DialogResult rs = saveFileDialog.ShowDialog();
if (rs != DialogResult.OK)
{
return false;
}
try
{
ExcelWriter excel = new ExcelWriter(saveFileDialog.FileName);
excel.BeginWrite();
short cols = 0;
if (columns == null || columns.Length == 0)
{//若没有传列名，则以dt的列名做为Excel的列名
foreach (DataColumn col in dt.Columns)
{
excel.WriteString(0, cols, col.ColumnName);
cols++;
}
}
else
{
foreach (string column in columns)
{
excel.WriteString(0, cols, column);
cols++;
}
}
short rows = 1;
foreach (DataRow dr in dt.Rows)
{
cols = 0;
foreach (DataColumn col in dt.Columns)
{
excel.WriteString(rows, cols, dr[col].ToString());
cols++;
}
rows++;
}
excel.EndWrite();
Process.Start(saveFileDialog.FileName);
return true;
}
catch
{
return false;
}
}
}代码可以到我的博客中下载:
http://blog.csdn.net/zx13525079024/article/details/8053415
http://blog.csdn.net/zx13525079024
- 已标记为答案Lisa ZhuMicrosoft contingent staff, Moderator2012年10月16日 9:42
2012年10月9日 10:06
全部回复
-
使用如下方法，
/// <summary>
/// 通过文件结构直接生成xls文件
/// </summary>
public class ExcelWriter
{
FileStream _wirter;
public ExcelWriter(string strPath)
{
_wirter = new FileStream(strPath, FileMode.OpenOrCreate);
}
/// <summary>
/// 写入short数组
/// </summary>
/// <param name="values"></param>
private void _writeFile(short[] values)
{
foreach (short v in values)
{
byte[] b = BitConverter.GetBytes(v);
_wirter.Write(b, 0, b.Length);
}
}
/// <summary>
/// 写文件头
/// </summary>
public void BeginWrite()
{
_writeFile(new short[] { 0x809, 8, 0, 0x10, 0, 0 });
}
/// <summary>
/// 写文件尾
/// </summary>
public void EndWrite()
{
_writeFile(new short[] { 0xa, 0 });
_wirter.Close();
}
/// <summary>
/// 写一个数字到单元格x,y
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void WriteNumber(short x, short y, double value)
{
_writeFile(new short[] { 0x203, 14, x, y, 0 });
byte[] b = BitConverter.GetBytes(value);
_wirter.Write(b, 0, b.Length);
}
/// <summary>
/// 写一个字符到单元格x,y
/// </summary>
/// <param name="x"></param>
/// <param name="y"></param>
/// <param name="value"></param>
public void WriteString(short x, short y, string value)
{
byte[] b = Encoding.Default.GetBytes(value);
_writeFile(new short[] { 0x204, (short)(b.Length + 8), x, y, 0, (short)b.Length });
_wirter.Write(b, 0, b.Length);
}
}public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable("cart");
DataColumn dc1 = new DataColumn("areaid", Type.GetType("System.String"));
DataColumn dc2 = new DataColumn("house", Type.GetType("System.String"));
DataColumn dc3 = new DataColumn("seq", Type.GetType("System.String"));
DataColumn dc4 = new DataColumn("remark", Type.GetType("System.String"));
dt.Columns.Add(dc1);
dt.Columns.Add(dc2);
dt.Columns.Add(dc3);
dt.Columns.Add(dc4);
DataRow dr = dt.NewRow();
dr["areaid"] = "北京";
dr["house"] = "北京";
dr["seq"] = "2";
dr["remark"] = "货付款";
dt.Rows.Add(dr);
DataRow dr1 = dt.NewRow();
dr1["areaid"] = "北京";
dr1["house"] = "上海";
dr1["seq"] = "1";
dr1["remark"] = "货付款";
dt.Rows.Add(dr1);
DataRow dr2 = dt.NewRow();
dr2["areaid"] = "上海";
dr2["house"] = "上海";
dr2["seq"] = "1";
dr2["remark"] = "货付款";
dt.Rows.Add(dr2);
DataRow dr3 = dt.NewRow();
dr3["areaid"] = "上海";
dr3["house"] = "北京";
dr3["seq"] = "1";
dr3["remark"] = "付款";
dt.Rows.Add(dr3);
string[] columns = {"城市","名称","顺序","备注"};
OutputXLSFromDataTable(columns, dt, saveFileDialog1);
}
/// <summary>
/// 导出DataTable为XLS，并打开生成的XLS
/// </summary>
/// <param name="columns">列名</param>
/// <param name="dt">表</param>
/// <param name="saveFileDialog">对话框</param>
/// <returns></returns>
public static bool OutputXLSFromDataTable(string[] columns, DataTable dt, SaveFileDialog saveFileDialog)
{
DialogResult rs = saveFileDialog.ShowDialog();
if (rs != DialogResult.OK)
{
return false;
}
try
{
ExcelWriter excel = new ExcelWriter(saveFileDialog.FileName);
excel.BeginWrite();
short cols = 0;
if (columns == null || columns.Length == 0)
{//若没有传列名，则以dt的列名做为Excel的列名
foreach (DataColumn col in dt.Columns)
{
excel.WriteString(0, cols, col.ColumnName);
cols++;
}
}
else
{
foreach (string column in columns)
{
excel.WriteString(0, cols, column);
cols++;
}
}
short rows = 1;
foreach (DataRow dr in dt.Rows)
{
cols = 0;
foreach (DataColumn col in dt.Columns)
{
excel.WriteString(rows, cols, dr[col].ToString());
cols++;
}
rows++;
}
excel.EndWrite();
Process.Start(saveFileDialog.FileName);
return true;
}
catch
{
return false;
}
}
}代码可以到我的博客中下载:
http://blog.csdn.net/zx13525079024/article/details/8053415
http://blog.csdn.net/zx13525079024
- 已标记为答案Lisa ZhuMicrosoft contingent staff, Moderator2012年10月16日 9:42