dataset to excel

using System;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Reflection;

namespace CB_ReportDAL
{
    public static class ExcelOperator
    {
        /// <summary>
        /// Passed DataSet, path. Write excel file function
        /// </summary>
        /// <param name="ds">DataSet Object</param>
        /// <param name="strurl">Path of the file you want to keep</param>
        /// <returns>The success of the state export Excel</returns>
        public static string DataSetToExcelXls(DataSet ds, string strurl, string status)
        {
            try
            {
                if (ds.Tables.Count < 1)
                    return "DataSetobject error";
                if (ds.Tables.Count == 1)
                {
                    return DataTableToExcelXls(ds.Tables[0], strurl);
                }
                else
                {
                    string strFileName = strurl + "//" + DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + "_" + status + ".XLS";
                    object my_object = Missing.Value;
                    Microsoft.Office.Interop.Excel.Application excel = new Excel.ApplicationClass();
                    Excel.WorkbookClass oWB;
                    Excel.Worksheet oSheet;

                    oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
                    oWB.Sheets.Add(my_object, my_object, ds.Tables.Count == 1 ? 1 : ds.Tables.Count - 1, my_object);

                    int dstabcount = ds.Tables.Count;
                    for (int dst = 0; dst < dstabcount; dst++)
                    {
                        oSheet = (Excel.Worksheet)oWB.Sheets.get_Item(dst + 1);
                        DataRow dr;
                        int count = ds.Tables[dst].Rows.Count + 1;
                        int I, J;
                        for (I = 2; I < count; I++)
                        {
                            dr = ds.Tables[dst].Rows[I - 2];
                            for (J = 1; J < (ds.Tables[dst].Columns.Count + 1); J++)
                            {
                                oSheet.Cells[I, J] = dr[J - 1].ToString().Trim();
                            }
                        }
                        oSheet.Name = ds.Tables[dst].TableName.ToString();
                    }
                    if (!Directory.Exists(strurl))  //Determine whether the path already exists, and if not, to create a path
                    {
                        try
                        {
                            DirectoryInfo newSubDir = Directory.CreateDirectory(strurl);

                        }
                        catch (Exception)
                        {
                            return strurl + "Does not exist, please manually create the appropriate directory";
                        }

                    }
                    oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
     null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);

                    oWB.Close(false, null, null);
                    excel.Workbooks.Close();
                    excel.Quit();

                    System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);

                    oSheet = null;
                    oWB = null;
                    excel = null;
                    GC.Collect();
                    GC.WaitForPendingFinalizers();
                    return "Excel successfully generated! " + DateTime.Now.ToString("yyy-MM-dd hh:mm:ss");
                }
            }
            catch
            {
                return "Generate Excel fails, please try again! " + DateTime.Now.ToString("yyy-MM-dd hh:mm:ss");
            }
        }

        /// <summary>
        /// Incoming DataTable, path. Write excel file function
        /// </summary>
        /// <param name="ds">DataTable Object</param>
        /// <param name="strurl">Path of the file you want to keep</param>
        /// <returns>The success of the state export Excel</returns>
        public static string DataTableToExcelXls(System.Data.DataTable ds, string strurl)
        {
            try
            {
                string strFileName = strurl + "//" + DateTime.Now.ToString("yyyyMMdd") + DateTime.Now.Hour + DateTime.Now.Minute + DateTime.Now.Second + ".XLS";

                Excel.Application excel = new Excel.ApplicationClass();
                Excel.WorkbookClass oWB;
                Excel.Worksheet oSheet;
                oWB = (Excel.WorkbookClass)(excel.Workbooks.Add(true));
                oSheet = (Excel.Worksheet)oWB.ActiveSheet;
                object my_object = Missing.Value;
                DataRow dr;
                int count = ds.Rows.Count + 2;
                int I, J;
                for (I = 2; I < count; I++)
                {
                    dr = ds.Rows[I - 2];
                    for (J = 1; J < (ds.Columns.Count + 1); J++)
                    {
                        oSheet.Cells[I, J] = dr[J - 1].ToString().Trim();
                    }
                }
                oSheet.Name = ds.TableName;
                if (!Directory.Exists(strurl))  //Determine whether the path already exists, and if not, to create a path
                {
                    try
                    {
                        Directory.CreateDirectory(strurl);
                    }
                    catch (Exception)
                    {
                        return strurl + "Does not exist, please manually create the appropriate directory";
                    }
                }
                oWB.SaveAs(strFileName, Excel.XlFileFormat.xlWorkbookNormal,
    null, null, false, false, Excel.XlSaveAsAccessMode.xlShared, false, false, null, null, null);
                oWB.Close(false, null, null);
                excel.Workbooks.Close();
                excel.Quit();

                System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oWB);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);

                oSheet = null;
                oWB = null;
                excel = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
                return "Excel successfully generated! " + DateTime.Now.ToString("yyy-MM-dd hh:mm:ss");
            }
            catch
            {
                return "Generate Excel fails, please try again! " + DateTime.Now.ToString("yyy-MM-dd hh:mm:ss");
            }
        }

    }
}

转----------------------------------------------------------------------------------------------------------------------------------------------------

http://hi.baidu.com/superww058/blog/item/ee19d990755ad182a977a4e6.html

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Reflection;

namespace DMS
{
/// <summary>
/// C#操作Excel类
/// </summary>
class ExcelOperate
{
//法一
//public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;
//    int columnNumber = dataTable.Columns.Count;

//    if (rowNumber == 0)
//    {
//        MessageBox.Show("没有任何数据可以导入到Excel文件！");
//        return false;
//    }

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//    excel.Application.Workbooks.Add(true);
//    excel.Visible = isShowExcle;//是否打开该Excel文件

//    //填充数据
//    for (int c = 0; c < rowNumber; c++)
//    {
//        for (int j = 0; j < columnNumber; j++)
//        {
//            excel.Cells[c + 1, j + 1] = dataTable.Rows[c].ItemArray[j];
//        }
//    }

//    return true;
//}


//法二
//public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;

//    int rowIndex = 1;
//    int colIndex = 0;


//    if (rowNumber == 0)
//    {
//        return false;
//    }

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//    excel.Application.Workbooks.Add(true);
//    excel.Visible = isShowExcle;

//    //生成字段名称
//    foreach (DataColumn col in dataTable.Columns)
//    {
//        colIndex++;
//        excel.Cells[1, colIndex] = col.ColumnName;
//    }

//    //填充数据
//    foreach (DataRow row in dataTable.Rows)
//    {
//        rowIndex++;
//        colIndex = 0;
//        foreach (DataColumn col in dataTable.Columns)
//        {
//            colIndex++;
//            excel.Cells[rowIndex, colIndex] = row[col.ColumnName];
//        }
//    }

//    return true;
//}

//法三（速度最快）
/// <summary>
/// 将数据集中的数据导出到EXCEL文件
/// </summary>
/// <param name="dataSet">输入数据集</param>
/// <param name="isShowExcle">是否显示该EXCEL文件</param>
/// <returns></returns>
public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
{
DataTable dataTable = dataSet.Tables[0];
int rowNumber = dataTable.Rows.Count;//不包括字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;

if (rowNumber == 0)
{
return false;
}

//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = isShowExcle;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;

//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}

object[,] objData = new object[rowNumber, columnNumber];

for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
//Application.DoEvents();
}

// 写入Excel
range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
//range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = objData;
worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

return true;
}

//法四
//public bool DataSetToExcel(DataSet dataSet, bool isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;
//    int columnNumber = dataTable.Columns.Count;
//    String stringBuffer = "";

//    if (rowNumber == 0)
//    {
//        MessageBox.Show("没有任何数据可以导入到Excel文件！");
//        return false;
//    }

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//    excel.Application.Workbooks.Add(true);
//    excel.Visible = isShowExcle;//是否打开该Excel文件

//    //填充数据
//    for (int i = 0; i < rowNumber; i++)
//    {
//        for (int j = 0; j < columnNumber; j++)
//        {
//            stringBuffer += dataTable.Rows[i].ItemArray[j].ToString();
//            if (j < columnNumber - 1)
//            {
//                stringBuffer += "/t";
//            }
//        }
//        stringBuffer += "/n";
//    }
//    Clipboard.Clear();
//    Clipboard.SetDataObject(stringBuffer);
//    ((Microsoft.Office.Interop.Excel.Range)excel.Cells[1, 1]).Select();
//    ((Microsoft.Office.Interop.Excel.Worksheet)excel.ActiveWorkbook.ActiveSheet).Paste(Missing.Value, Missing.Value);
//    Clipboard.Clear();

//    return true;
//}

//public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;
//    int columnNumber = dataTable.Columns.Count;

//    if (rowNumber == 0)
//    {
//        MessageBox.Show("没有任何数据可以导入到Excel文件！");
//        return false;
//    }

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//    Microsoft.Office.Interop.Excel.Workbook workBook = excel.Application.Workbooks.Add(true);
//    excel.Visible = false;//是否打开该Excel文件

//    //填充数据
//    for (int i = 0; i < rowNumber; i++)
//    {
//        for (int j = 0; j < columnNumber; j++)
//        {
//            excel.Cells[i + 1, j + 1] = dataTable.Rows[i].ItemArray[j];
//        }
//    }

//    //string fileName = path + "//" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
//    workBook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

//    try
//    {
//        workBook.Saved = true;
//        excel.UserControl = false;
//        //excelapp.Quit();
//    }
//    catch (Exception exception)
//    {
//        MessageBox.Show(exception.Message);
//    }
//    finally
//    {
//        workBook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
//        excel.Quit();
//    }

//    if (isShowExcle)
//    {
//        System.Diagnostics.Process.Start(fileName);
//    }
//    return true;
//}

//public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
//{
//    DataTable dataTable = dataSet.Tables[0];
//    int rowNumber = dataTable.Rows.Count;//不包括字段名
//    int columnNumber = dataTable.Columns.Count;
//    int colIndex = 0;

//    if (rowNumber == 0)
//    {
//        MessageBox.Show("没有任何数据可以导入到Excel文件！");
//        return false;
//    }

//    //建立Excel对象
//    Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//    //excel.Application.Workbooks.Add(true);
//    Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
//    Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
//    excel.Visible = isShowExcle;
//    //Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
//    worksheet.Name = "挠度数据";
//    Microsoft.Office.Interop.Excel.Range range;

//    //生成字段名称
//    foreach (DataColumn col in dataTable.Columns)
//    {
//        colIndex++;
//        excel.Cells[1, colIndex] = col.ColumnName;
//    }

//    object[,] objData = new object[rowNumber, columnNumber];

//    for (int r = 0; r < rowNumber; r++)
//    {
//        for (int c = 0; c < columnNumber; c++)
//        {
//            objData[r, c] = dataTable.Rows[r][c];
//        }
//        //Application.DoEvents();
//    }

//    // 写入Excel
//    range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
//    //range.NumberFormat = "@";//设置单元格为文本格式
//    range.Value2 = objData;
//    worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

//    //string fileName = path + "//" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
//    workbook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

//    try
//    {
//        workbook.Saved = true;
//        excel.UserControl = false;
//        //excelapp.Quit();
//    }
//    catch (Exception exception)
//    {
//        MessageBox.Show(exception.Message);
//    }
//    finally
//    {
//        workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
//        excel.Quit();
//    }

//    //if (isShowExcle)
//    //{
//    //    System.Diagnostics.Process.Start(fileName);
//    //}
//    return true;
//}

/// <summary>
/// 将数据集中的数据保存到EXCEL文件
/// </summary>
/// <param name="dataSet">输入数据集</param>
/// <param name="fileName">保存EXCEL文件的绝对路径名</param>
/// <param name="isShowExcle">是否打开EXCEL文件</param>
/// <returns></returns>
public bool DataSetToExcel(DataSet dataSet, string fileName, bool isShowExcle)
{
DataTable dataTable = dataSet.Tables[0];
int rowNumber = dataTable.Rows.Count;//不包括字段名
int columnNumber = dataTable.Columns.Count;
int colIndex = 0;

if (rowNumber == 0)
{
MessageBox.Show("没有任何数据可以导入到Excel文件！");
return false;
}

//建立Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
//excel.Application.Workbooks.Add(true);
Microsoft.Office.Interop.Excel.Workbook workbook = excel.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
excel.Visible = false;
//Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)excel.Worksheets[1];
Microsoft.Office.Interop.Excel.Range range;

//生成字段名称
foreach (DataColumn col in dataTable.Columns)
{
colIndex++;
excel.Cells[1, colIndex] = col.ColumnName;
}

object[,] objData = new object[rowNumber, columnNumber];

for (int r = 0; r < rowNumber; r++)
{
for (int c = 0; c < columnNumber; c++)
{
objData[r, c] = dataTable.Rows[r][c];
}
//Application.DoEvents();
}

// 写入Excel
range = worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, columnNumber]);
//range.NumberFormat = "@";//设置单元格为文本格式
range.Value2 = objData;
worksheet.get_Range(excel.Cells[2, 1], excel.Cells[rowNumber + 1, 1]).NumberFormat = "yyyy-m-d h:mm";

//string fileName = path + "//" + DateTime.Now.ToString().Replace(':', '_') + ".xls";
workbook.SaveAs(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

try
{
workbook.Saved = true;
excel.UserControl = false;
//excelapp.Quit();
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
finally
{
workbook.Close(Microsoft.Office.Interop.Excel.XlSaveAction.xlSaveChanges, Missing.Value, Missing.Value);
excel.Quit();
}

if (isShowExcle)
{
System.Diagnostics.Process.Start(fileName);
}
return true;
}
}
}