如何将DataTable写入到一个Excel中

Question

经过一系列的查询后，得到一个DataTable。接下来如何将这个表写入到Excel中。谢谢

Answer 1

http://blog.csdn.net/blackhero/archive/2006/08/25/1116399.aspx

Answer 2

http://www.chinabs.net/webdotnet/default.asp?infoid=417

Answer 3

//*****建立一个Windows   项目   
    
  //*****添加引用类   microsoft.offices   excel.90   
    
  //*****引入命名空间   
  using   system.data;   
  using   system.data.sqlclient;   
    
  //******导出数据到Excel   
  public   static   void   OutputExcel()   
  {   
                
              
              ///*****添加一个表薄   
              Excle.application   appExcel=new   excel.application();   
    
              Excel.workbook   xbook=new   Excle.workbook();   
    
              Excel.worksheet   xsheet=new   Excel.worksheet();   
    
              //****添加一个工作薄   
              xsheet=appExcle.worksheets.add("sheet");   
    
              DataColumn   col;   
    
              int   col=0;//******显示行   
              int   row=1;//******显示列   
    
              //*****打开Excel程序   
              appExcel.visible=true;   
    
              //***获取数据源   
              DataTable   dTable=new   DataTable();   
    
              dTable=GetTable();   
    
              //*****导出数据   
              foreach(DataColumn   col   in   dTable.Columname)   
              {   
                    col+=1;   
                    row=1;   
                    foreach(datarow   row   in   dTable.rows)   
                    {   
    
                          row+=1;   
                          col=1;   
                          appExcle.cell(row,col)=row(col.columname);   
                    }   
                }   

Answer 4

public static int ExportToExcel(DataSet ds, string filename)
    {
        int state = 0;
        int tablecount = ds.Tables.Count;
        if (tablecount <= 0) return 0;
        int index = 0;
        Excel.Application xlApp = null;
        Excel.Workbook xlBook = null;
        Excel.Worksheet xlSheet = null;
        int rowIndex = 1;
        int colIndex = 0;
        object missing = Missing.Value;        List<System.Data.DataTable> listTable = new List<System.Data.DataTable>();
        try
        {
            xlApp = new Excel.Application();            xlBook = xlApp.Workbooks.Add(true);            foreach (System.Data.DataTable dt in ds.Tables)
            {
                index++;
                rowIndex = 1;
                colIndex = 0;                xlSheet = (Excel.Worksheet)xlApp.Worksheets[1];                xlSheet.Name = dt.TableName;                foreach (DataColumn Col in dt.Columns)
                {
                    colIndex = colIndex + 1;
                    xlApp.Cells[1, colIndex] = " " + Col.ColumnName;
                }                //foreach (DataRow Row in dt.Rows)
                //{
                //    rowIndex = rowIndex + 1;                //    colIndex = 0;
                //    foreach (DataColumn Col in dt.Columns)
                //    {
                //        colIndex = colIndex + 1;
                //        xlApp.Cells[rowIndex, colIndex] = Row.ItemArray[colIndex - 1];
                //    }
                //}                int rowNum = dt.Rows.Count;
                int colNum = dt.Columns.Count;
                string[,] finalData = new string[rowNum, colNum];
                for (int i = 0; i < rowNum; i++)
                {
                    for (int j = 0; j < colNum; j++)
                    {
                        finalData[i, j] = dt.Rows[i][j].ToString();
                    }
                }
                xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[1, colIndex]).Font.Bold = true;
                xlSheet.get_Range(xlSheet.Cells[1, 1], xlSheet.Cells[rowIndex, colIndex]).Borders.LineStyle = 1;
                xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]).Value2 = finalData;
                xlSheet.get_Range(xlSheet.Cells[2, 1], xlSheet.Cells[rowNum + 1, colNum]).NumberFormatLocal = "@";                if (index < tablecount) xlSheet = (Excel.Worksheet)xlApp.Worksheets.Add(missing, missing, missing, missing);
            }            if (System.IO.File.Exists(filename)) System.IO.File.Delete(filename);
            xlApp.Visible = false;
            xlSheet.SaveAs(filename, missing, missing, missing, missing, missing, missing, missing, missing, missing);
            state = 1;
        }
        finally
        {
            object saveChange = true;
            xlBook.Close(saveChange, filename, missing);
            xlApp.Quit();
            if(xlSheet!=null) Marshal.ReleaseComObject(xlSheet);
            if (xlBook != null) Marshal.ReleaseComObject(xlBook);
            if (xlApp != null) Marshal.ReleaseComObject(xlApp);
            xlApp = null;
            xlBook = null;
            xlSheet = null;
            GC.Collect();
        }
        return state;
    }