如何将dataGridView 导出到Excel

最新推荐文章于 2020-03-29 17:37:59 发布

yuanyuan_186

最新推荐文章于 2020-03-29 17:37:59 发布

阅读量500

点赞数

问题
0

登录进行投票

如何将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();

        }

}

2012年10月9日 9:25

回复

|

引用

ssmilelake

ssmilelake

IT Admin

0 分数 1 0 0

近期成就

第一次论坛回复

ssmilelake 的主题查看个人资料

IT Admin

0 分数

答案

登录进行投票

使用如下方法，

  /// <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

开心

软件开发

17,050 分数 11 4 2

近期成就

论坛回复 IV 论坛内容鉴别者 I 有建议性的解答者 I