怎么把数据导出到Excel（或Word)_语雀中数据表如何导出成excel

// 作用：把DataSet数据集内数据转化为Excel、Word文件 // 描述：这些关于Excel、Word的导出方法，基本可以实现日常须要，其中有些方法可以把数据导出后 //       生成Xml格式，再导入数据库！有些屏蔽内容没有去掉，保留下来方便学习参考用之。    // 备注：请引用Office相应COM组件，导出Excel对象的一个方法要调用其中的一些方法和属性。 public void DataSetToExcel(DataSet ds,string FileName){   try   {      //Web页面定义       //System.Web.UI.Page mypage=new System.Web.UI.Page();       HttpResponse resp;      resp=HttpContext.Current.Response;      resp.ContentEncoding=System.Text.Encoding.GetEncoding("GB2312");      resp.AppendHeader("Content-disposition","attachment;filename="+FileName+".xls");      resp.ContentType="application/ms-excel";      //变量定义       string colHeaders=null;      string Is_item=null;      //显示格式定义       //文件流操作定义       //FileStream fs=new FileStream(FileName,FileMode.Create,FileAccess.Write);       //StreamWriter sw=new StreamWriter(fs,System.Text.Encoding.GetEncoding("GB2312"));       StringWriter sfw=new StringWriter();      //定义表对象与行对象，同时用DataSet对其值进行初始化       System.Data.DataTable dt=ds.Tables[0];      DataRow[] myRow=dt.Select();      int i=0;      int cl=dt.Columns.Count;      //取得数据表各列标题，各标题之间以/t分割，最后一个列标题后加回车符       for(i=0;i<cl;i++)      {         //if(i==(cl-1))  //最后一列，加/n          // colHeaders+=dt.Columns[i].Caption.ToString();          //else          colHeaders+=dt.Columns[i].Caption.ToString()+"/t";      }      sfw.WriteLine(colHeaders);      //sw.WriteLine(colHeaders);       //逐行处理数据       foreach(DataRow row in myRow)      {         //当前数据写入          for(i=0;i<cl;i++)         {          //if(i==(cl-1))           //   Is_item+=row[i].ToString()+"/n";           //else           Is_item+=row[i].ToString()+"/t";         }         sfw.WriteLine(Is_item);         //sw.WriteLine(Is_item);          Is_item=null;      }      resp.Write(sfw);      //resp.Clear();       resp.End();   }   catch(Exception e)   {      throw e;   }}

/// <summary> /// ExportFiles 的摘要说明。 /// 作用：把DataSet数据集内数据转化为Excel文件 /// 描述：导出Excel文件    /// 备注：请引用Office相应COM组件，导出Excel对象的一个方法要调用其中的一些方法和属性。 /// </summary> public class ExportFiles{    private string filePath = "";    public ExportFiles(string excel_path)    {        //         // TODO: 在此处添加构造函数逻辑         //         filePath = excel_path;    }    /// <summary>     /// 将指定的Dataset导出到Excel文件     /// </summary>     /// <param name="dt"></param>     /// <returns></returns>     public bool ExportToExcel(System.Data.DataSet ds, string ReportName)    {        if (ds.Tables[0].Rows.Count == 0)        {            MessageBox.Show("数据集为空");        }        Microsoft.Office.Interop.Excel._Application xlapp = new ApplicationClass();        Workbook xlbook = xlapp.Workbooks.Add(true);        Worksheet xlsheet = (Worksheet)xlbook.Worksheets[1];        Range range = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, ds.Tables[0].Columns.Count]);        range.MergeCells = true;        xlapp.ActiveCell.FormulaR1C1 = ReportName;        xlapp.ActiveCell.Font.Size = 20;        xlapp.ActiveCell.Font.Bold = true;        xlapp.ActiveCell.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter;        int colIndex = 0;        int RowIndex = 2;        //开始写入每列的标题         foreach (DataColumn dc in ds.Tables[0].Columns)        {            colIndex++;            xlsheet.Cells[RowIndex, colIndex] = dc.Caption;        }        //开始写入内容         int RowCount = ds.Tables[0].Rows.Count;//行数         for (int i = 0; i < RowCount; i++)        {            RowIndex++;            int ColCount = ds.Tables[0].Columns.Count;//列数             for (colIndex = 1; colIndex <= ColCount; colIndex++)            {                xlsheet.Cells[RowIndex, colIndex] = ds.Tables[0].Rows[i][colIndex - 1];//dg[i, colIndex - 1];                 xlsheet.Cells.ColumnWidth = ds.Tables[0].Rows[i][colIndex - 1].ToString().Length;            }        }        xlbook.Saved = true;        xlbook.SaveCopyAs(filePath);        xlapp.Quit();        GC.Collect();        return true;    }    public bool ExportToExcelOF(System.Data.DataSet ds, string ReportName)    {        if (ds.Tables[0].Rows.Count == 0)        {            MessageBox.Show("数据集为空");        }        string FileName = filePath;        //System.Data.DataTable dt = new System.Data.DataTable();         FileStream objFileStream;        StreamWriter objStreamWriter;        string strLine = "";        objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);        objStreamWriter = new StreamWriter(objFileStream, System.Text.Encoding.Unicode);        strLine = ReportName;        objStreamWriter.WriteLine(strLine);        strLine = "";        for (int i = 0; i < ds.Tables[0].Columns.Count; i++)        {            strLine = strLine + ds.Tables[0].Columns[i].ColumnName.ToString() + "          " + Convert.ToChar(9);        }        objStreamWriter.WriteLine(strLine);        strLine = "";        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)        {            strLine = strLine + (i + 1) + Convert.ToChar(9);            for (int j = 1; j < ds.Tables[0].Columns.Count; j++)            {                strLine = strLine + ds.Tables[0].Rows[i][j].ToString() + Convert.ToChar(9);            }            objStreamWriter.WriteLine(strLine);            strLine = "";        }        objStreamWriter.Close();        objFileStream.Close();        //Microsoft.Office.Interop.Excel._Application xlapp = new ApplicationClass();         //Workbook xlbook = xlapp.Workbooks.Add(true);         //Worksheet xlsheet = (Worksheet)xlbook.Worksheets[1];         //Range range = xlsheet.get_Range(xlapp.Cells[1, 1], xlapp.Cells[1, ds.Tables[0].Columns.Count]);         //range.EntireColumn.AutoFit();         //xlapp.Quit();         return true;    }     }

//导出GridView到Excel中的关键之处 //用法： ToExcel(GVStaff, TextBox1.Text); public static void ToExcel(System.Web.UI.Control ctl,string FileName){    HttpContext.Current.Response.Charset ="UTF-8";    HttpContext.Current.Response.ContentEncoding =System.Text.Encoding.Default;    HttpContext.Current.Response.ContentType ="application/ms-excel";    HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename="+""+FileName+".xls");    ctl.Page.EnableViewState =false;    System.IO.StringWriter  tw = new System.IO.StringWriter();    HtmlTextWriter hw = new HtmlTextWriter(tw);    ctl.RenderControl(hw);    HttpContext.Current.Response.Write(tw.ToString());    HttpContext.Current.Response.End();}             必须有下面这句！否则不会通过！public override void VerifyRenderingInServerForm(Control control){    // Confirms that an HtmlForm control is rendered for }

using System;using Microsoft.Office.Interop.Excel;using System.Windows.Forms;namespace DongVI{/// <summary> /// DataTable导出到Excel /// 整理:dongVi /// </summary> public class DataTableToExcel{  private DataTableToExcel()  {  }  /// <summary>   /// 导出Excel   /// </summary>   /// <param name="dt">要导出的DataTable</param>   public static void ExportToExcel(System.Data.DataTable dt )  {   if (dt == null) return;      Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();   if (xlApp == null)   {    // lblMsg.Text = "无法创建Excel对象，可能您的电脑未安装Excel";     MessageBox.Show( "无法创建Excel对象，可能您的电脑未安装Excel" );    return;   }   System.Windows.Forms.SaveFileDialog saveDia = new SaveFileDialog();   saveDia.Filter = "Excel|*.xls";   saveDia.Title = "导出为Excel文件";   if(saveDia.ShowDialog()== System.Windows.Forms.DialogResult.OK    && !string.Empty.Equals(saveDia.FileName))   {    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 = null;    long totalCount = dt.Rows.Count;    long rowRead = 0;    float percent = 0;    string fileName = saveDia.FileName;    //写入标题     for (int i = 0; i < dt.Columns.Count; i++)    {     worksheet.Cells[1, i + 1] = dt.Columns[i].ColumnName;     range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, i + 1];     //range.Interior.ColorIndex = 15;//背景颜色      range.Font.Bold = true;//粗体      range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;//居中      //加边框      range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);     //range.ColumnWidth = 4.63;//设置列宽      //range.EntireColumn.AutoFit();//自动调整列宽      //r1.EntireRow.AutoFit();//自动调整行高     }    //写入内容     for (int r = 0; r < dt.DefaultView.Count; r++)    {     for (int i = 0; i < dt.Columns.Count; i++)     {      worksheet.Cells[r + 2, i + 1] = dt.DefaultView[r][i];      range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[r + 2, i + 1];      range.Font.Size = 9;//字体大小       //加边框 range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);      range.EntireColumn.AutoFit();//自动调整列宽      }     rowRead++;     percent = ((float)(100 * rowRead)) / totalCount;     System.Windows.Forms.Application.DoEvents();    }    range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;    if (dt.Columns.Count > 1)    {     range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;    }    try    {     workbook.Saved = true;     workbook.SaveCopyAs(fileName);    }    catch (Exception ex)    {     //lblMsg.Text = "导出文件时出错,文件可能正被打开！/n" + ex.Message;      MessageBox.Show( "导出文件时出错,文件可能正被打开！/n" + ex.Message );     return;    }    workbooks.Close();    if (xlApp != null)    {     xlApp.Workbooks.Close();     xlApp.Quit();     int generation = System.GC.GetGeneration(xlApp);     System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);     xlApp = null;     System.GC.Collect(generation);    }    GC.Collect();//强行销毁     #region 强行杀死最近打开的Excel进程     System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL");    System.DateTime startTime = new DateTime();    int m, killId = 0;    for (m = 0; m < excelProc.Length; m++)    {     if (startTime < excelProc[m].StartTime)     {      startTime = excelProc[m].StartTime;      killId = m;     }    }    if (excelProc[killId].HasExited == false)    {     excelProc[killId].Kill();    }     #endregion     MessageBox.Show( "导出成功!" );   }  }}}

StringWriter stringWriter = new StringWriter();HtmlTextWriter htmlWriter = new HtmlTextWriter( stringWriter );DataGrid excel = new DataGrid();System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();AlternatingStyle.BackColor = System.Drawing.Color.LightGray;headerStyle.BackColor =System.Drawing.Color.LightGray;headerStyle.Font.Bold = true;headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;; excel.AlternatingItemStyle.MergeWith(AlternatingStyle);excel.HeaderStyle.MergeWith(headerStyle);excel.ItemStyle.MergeWith(itemStyle); excel.GridLines = GridLines.Both;excel.HeaderStyle.Font.Bold = true;excel.DataSource = dt.DefaultView;//输出DataTable的内容 excel.DataBind();excel.RenderControl(htmlWriter);  string filestr = "d://data//"+filePath;  //filePath是文件的路径 int pos = filestr.LastIndexOf( "//");string file = filestr.Substring(0,pos);if( !Directory.Exists( file ) ){  Directory.CreateDirectory(file);}System.IO.StreamWriter sw = new StreamWriter(filestr);sw.Write(stringWriter.ToString());sw.Close();

exec master..xp_cmdshell @# bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout c:/test.xls -c -S"soa" -U"sa" -P"sa" @#

//dt为数据源（数据表）  //ExcelFileName 为要导出的Ｅｘｃｌｅ文件 //ModelFile为模板文件，该文件与数据源中的表一致。否则数据会导出失败。 //ModelFile文件里，需要有一张 与 dt.TableName 一致的表，而且字段也要一致。 //注明：如果不用ModelFile的话，可以用一个空白Excel文件，不过，要去掉下面创建表的注释，让OleDb自己创建一个空白表。 public static string TableToExcelFile(DataTable dt,string ExcelFileName,string ModelFile){    File.Copy(ModelFile,ExcelFileName);  //复制一个空文件，提供写入数据用         if(File.Exists(ExcelFileName)==false)    {        return "系统创建临时文件失败，请与系统管理员联系！";    }    if(dt == null)    {        return "DataTable不能为空";    }    int rows = dt.Rows.Count;    int cols = dt.Columns.Count;    StringBuilder sb;    string connString;    if(rows == 0)    {        return "没有数据";    }    sb = new StringBuilder();        connString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ExcelFileName+";Extended Properties=Excel 8.0;";            //生成创建表的脚本     //----sb.Append("DROP TABLE "+dt.TableName);         /*    sb.Append("CREATE TABLE ");    sb.Append(dt.TableName + " ( ");    for(int i=0;i<cols;i++)    {        if(i < cols - 1)        sb.Append(string.Format("{0} varchar,",dt.Columns[i].ColumnName));        else        sb.Append(string.Format("{0} varchar)",dt.Columns[i].ColumnName));    }        */        //return sb.ToString();     OleDbConnection objConn = new OleDbConnection(connString);    OleDbCommand objCmd = new OleDbCommand();    objCmd.Connection = objConn;    //objCmd.CommandText=sb.ToString();     try    {        objConn.Open();        //objCmd.ExecuteNonQuery();     }    catch(Exception e)    {        return "在Excel中创建表失败，错误信息：" + e.Message;    }    sb.Remove(0,sb.Length);    sb.Append("INSERT INTO ");    sb.Append(dt.TableName + " ( ");    for(int i=0;i<cols;i++)    {        if(i < cols - 1)            sb.Append(dt.Columns[i].ColumnName + ",");        else            sb.Append(dt.Columns[i].ColumnName + ") values (");    }    for(int i=0;i<cols;i++)    {        if(i < cols - 1)            sb.Append("@" + dt.Columns[i].ColumnName + ",");        else            sb.Append("@" + dt.Columns[i].ColumnName + ")");    }    //建立插入动作的Command     objCmd.CommandText = sb.ToString();    OleDbParameterCollection param = objCmd.Parameters;    for(int i=0;i<cols;i++)    {        param.Add(new OleDbParameter("@" + dt.Columns[i].ColumnName, OleDbType.VarChar));    }    //遍历DataTable将数据插入新建的Excel文件中     foreach (DataRow row in dt.Rows)    {           for (int i=0; i<param.Count; i++)        {            param[i].Value = row[i];        }        objCmd.ExecuteNonQuery();    }    return "数据已成功导入Excel";}  //   Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=754176

public static void exportToExcelByDataset(string filePath, DataSet ds,XmlNode node){    string sqlstr;     if(fi.Exists)    {         fi.Delete();         //throw new Exception("文件删除失败");         }    else    {         fi.Create();    }       string mailto:sqlcon=@%22Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended ProPerties=Excel 8.0;";    OleDbConnection olecon = new OleDbConnection(sqlcon);    OleDbCommand olecmd = new OleDbCommand();    olecmd.Connection = olecon;    olecmd.CommandType = CommandType.Text;    try    {        olecon.Open();                    XmlNode nodec=node.SelectSingleNode("./Method/ShowField");        int ii = 0;        sqlstr = "CREATE TABLE sheet1(";        foreach(XmlNode xnode in nodec.ChildNodes )        {           if(ii == nodec.ChildNodes.Count - 1)           {               if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")               {                   sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number)";                      }               else               {                   sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";               }               // sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text)";            }           else           {               if(xnode.Attributes["type"].Value.ToLower() == "int"||xnode.Attributes["type"].Value.ToLower() == "decimal")               {                    sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " number,";                      }               else               {                    sqlstr=sqlstr + xnode.Attributes["displayname"].Value + " text,";               }           }           //  sqlstr =sqlstr + xnode.Attributes["displayname"].Value + " text";                   ii++;       }       olecmd.CommandText = sqlstr;       olecmd.ExecuteNonQuery();       for(int i=0;i<ds.Tables[0].Rows.Count;i++)       {           sqlstr = "INSERT INTO sheet1 VALUES(";           int jj=0;           foreach(XmlNode inode in nodec.ChildNodes )           {                if(jj == nodec.ChildNodes.Count-1)                {                    if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")                    {                         sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + ")" ;                      }                    else                    {                         sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "')" ;                    }                }                else                {                    if(inode.Attributes["type"].Value.ToLower() == "int"||inode.Attributes["type"].Value.ToLower() == "decimal")                    {                         sqlstr = sqlstr + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString()) + "," ;                    }                    else                    {                         sqlstr = sqlstr + "'" + isnull(ds.Tables[0].Rows[i].ItemArray[jj].ToString().Replace("'","''")) + "'," ;                    }                }                jj++;           }           olecmd.CommandText = sqlstr;           olecmd.ExecuteNonQuery();        }          MessageBox.Show(@"Excel文件：" + filePath + " 导出成功！");    }    catch(Exception ex)    {        MessageBox.Show(ex.Message);    }    finally    {        olecmd.Dispose();        olecon.Close();        olecon.Dispose();    }}// 判断对象为空 private static string isnull(string obj){    if(obj.Length >0)    {     return obj;    }    else    {     return "null";    } }