关于把datagradview里面的数据导出为excel表的问题

Question

求代码，谢谢
saveFileDialog1.Filter = "Excle Files (*.xls)|*.xls";
            saveFileDialog1.ShowDialog();            string myname = saveFileDialog1.FileName;            if (myname == "")
                return;
            if (System.IO.File.Exists(myname))
            {
                System.IO.File.Delete(myname);
            }            DateTime dt = DateTime.Now;
            string mytablename = "专业基本信息表";            try
            {
                string sql1 = "记录序号int(4),专业名称varchat(30),院校名称 varchar(40),年级 int(4),是否临床 char(2),是否重点char(2),本届人数 smallint(2)";
                sql1 = "Create Table " + mytablename + "(" + sql1 + ")";
                string MyConnectionstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + myname + "';Excel 8.0;HDR=YES";
                OleDbConnection olconn = new OleDbConnection(MyConnectionstring);
                olconn.Open();
                OleDbCommand cmd = new OleDbCommand(sql1, olconn);
                cmd8.ExecuteNonQuery();                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    string a = ds.Tables[0].Rows[i][0].ToString();
                    string b = ds.Tables[0].Rows[i][1].ToString();
                    string c = ds.Tables[0].Rows[i][2].ToString();
                    string d = ds.Tables[0].Rows[i][3].ToString();
                    string k = ds.Tables[0].Rows[i][4].ToString();
                    string f = ds.Tables[0].Rows[i][5].ToString();
                    string g = ds.Tables[0].Rows[i][6].ToString();
                    string h = ds.Tables[0].Rows[i][7].ToString();                    sql1 = "INSERT INTO [" + mytablename + "$]([记录序号],[专业名称],[院校名称],[年级],[是否临床],[是否重点],[本届人数])VALUES('" + a + "','" + b + "','" + c + "','" + d + "','" + k + "','" + f + "','" + g + "','" + h + "')";
                    OleDbCommand cmd1 = new OleDbCommand(sql1, olconn);
                    cmd8.ExecuteNonQuery();                }
                olconn.Close();
                MessageBox.Show("Excel文件：" + mytablename + "创建成功！", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }我这个代码倒不出来，求完整版

Answer 1

dt = dataGridView3.DataSource as DataTable;            //文件存在时先删除文件后再进行下一步操作 
            FileInfo file = new FileInfo(@"C:\Documents and Settings\Administrator\桌面\aa"); //路径
            if (file.Exists)
            {
                file.Delete();
            }
            int rowIndex = 3;      //开始写入数据的单元格行 
            int colIndex = 0;      //开始写入数据的单元格列 
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.ApplicationClass mExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();
            mExcel.Visible = false;
            Microsoft.Office.Interop.Excel.Workbooks mBooks = (Microsoft.Office.Interop.Excel.Workbooks)mExcel.Workbooks;
            Microsoft.Office.Interop.Excel.Workbook mBook = (Microsoft.Office.Interop.Excel.Workbook)(mBooks.Add(miss));
            Microsoft.Office.Interop.Excel.Worksheet mSheet = (Microsoft.Office.Interop.Excel.Worksheet)mBook.ActiveSheet;
            Microsoft.Office.Interop.Excel.Range er = mSheet.get_Range((object)"A1", System.Reflection.Missing.Value); //向Excel文件中写入标题文本 
            try
            {
                foreach (DataColumn col in dt.Columns)    //将所得到的表的列名,赋值给单元格 
                {
                    colIndex++;
                    mSheet.Cells[3, colIndex] = col.ColumnName;
                }
                foreach (DataRow row in dt.Rows)    //同样方法处理数据 
                {
                    rowIndex++;
                    colIndex = 0;
                    foreach (DataColumn col in dt.Columns)
                    {
                        colIndex++;
                        if (colIndex == 2)
                        {
                            mSheet.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();//第二行数据为银行帐号信息转换为字符防止首位0丢失 
                        }
                        else
                        {
                            mSheet.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
                        }
                    }
                }
                //保存工作已写入数据的工作表 
                mBook.SaveAs(@"C:\Documents and Settings\Administrator\桌面\用友财务软件会计科目1\会计科目", miss, miss, miss, miss, miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss);                MessageBox.Show("导出成功");
            }
            catch (Exception ee)
            {
                throw new Exception(ee.Message);
            } 
            finally    //finally中的代码主要用来释放内存和中止进程() 
            {
                mBook.Close(false, miss, miss);
                mBooks.Close();
                mExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(er);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mSheet);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mBook);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mBooks);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(mExcel);
                GC.Collect();
            }
        }

Answer 2

       public void ExportExcel(DataSet ds)        //以DataSet- 导出Excel文件   
        {
            if (ds == null) return;
            Microsoft.office.Interop.Excel.Application xlApp = new Microsoft.office.Interop.Excel.Application();            if (xlApp == null)
            {
                MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
                return;
            }
            Microsoft.office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
            Microsoft.office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
            Microsoft.office.Interop.Excel.Worksheet worksheet = (Microsoft.office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得Sheet1
            Microsoft.office.Interop.Excel.Range range;
            long totalCount = ds.Tables[0].Rows.Count;            long rowRead = 0;
            float percent = 0;            //worksheet.Cells[1, 1] = "报表标题";            //写入字段
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                worksheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
                range = (Microsoft.office.Interop.Excel.Range)worksheet.Cells[1, i + 1];
                range.Interior.ColorIndex = 15;
                range.Font.Bold = true;
            }
            //写入数值
            for (int r = 0; r < ds.Tables[0].Rows.Count; r++)
            {
                for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
                {
                    worksheet.Cells[r + 2, i + 1] = ds.Tables[0].Rows[r][i];
                }
                rowRead++;
                percent = ((float)(100 * rowRead)) / totalCount;
            }
            xlApp.Visible = true;
        }下面是将DataGridView 转成DataSet public static DataSet GetDataSetFromDataGridView(DataGridView ucgrd)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();            for (int j = 0; j < ucgrd.Columns.Count; j++)
            {
                dt.Columns.Add(ucgrd.Columns[j].HeaderCell.Value.ToString());
            }            for (int j = 0; j < ucgrd.Rows.Count; j++)
            {
                DataRow dr = dt.NewRow();
                for (int i = 0; i < ucgrd.Columns.Count; i++)
                {
                    if (ucgrd.Rows[j].Cells[i].Value != null)
                    {
                        dr[i] = ucgrd.Rows[j].Cells[i].Value.ToString();
                    }
                    else
                    {
                        dr[i] = "";
                    }
                }
                dt.Rows.Add(dr);
            }
            ds.Tables.Add(dt);            return ds;
        }

Answer 3

 private void ToExcel1()
    {
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        Response.AppendHeader("Content-Disposition", "attachment;filename=FileName.xls");
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        //设置输出流为简体中文   
        Response.ContentType = "application/ms-excel";
        //设置输出文件类型为excel文件。   
        this.EnableViewState = false;
        System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("ZH-CN", true);
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.GridView1.RenderControl(oHtmlTextWriter);        Response.Write(oStringWriter.ToString());
        Response.End();
    }

Answer 4

不使用 interop 来操控 excel application,
而是直接写成 xml 格式的 excel 文档才是正确的路线.否则, 客户端多的时候, 就等着解决各种莫名其妙的问题吧.

Answer 5

我家里有这样的代码，就是把datagridview中的内容导出到excel中，单位没有，如果明天你还解决，我再发给你！