学步园

这次我写了两个方法，一个是从Excel文件中的一个Sheet的内容导入到DataSet中；另一个是把DataSet里的内容导出到Excel中。 

/// <summary>
/// 从选择的Excel文件导入
/// </summary>
/// <returns>DataSet</returns>
public DataSet ImportFromExcel()
{
    DataSet ds=new DataSet();
    string file = "e:\\text.xls";
    string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +  file + ";" +
                "Extended Properties=Excel 8.0;";
    OleDbDataAdapter ExcelDA = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", strConn);

 

    DataSet excelDs = new DataSet();
    try
    {
        ExcelDA.Fill(ExcelDs, "ExcelInfo");      
    }
    catch(Exception err)
    {
        System.Console.WriteLine( err.ToString() );
    }

 

    return excelDs ;
}

 

/// <summary>
/// 导出用户选择的Excel文件
/// </summary>
/// <param name="ds">DataSet</param>
public void ExportToExcel(DataSet ds)
{
    string file = "e:\\text.xls";
    Excel.Application excel= new Excel.Application();
    int rowIndex=1;
    int colIndex=0;

 

    excel.Application.Workbooks.Add(true);
                  
    System.Data.DataTable table=ds.Tables[0] ;
    foreach(DataColumn col in table.Columns)
    {
        colIndex++;    
        excel.Cells[1,colIndex]=col.ColumnName;               
    }
    foreach(DataRow row in table.Rows)
    {
        rowIndex++;
        colIndex=0;
        foreach(DataColumn col in table.Columns)
        {
            colIndex++;
            excel.Cells[rowIndex,colIndex]=row[col.ColumnName].ToString();
        }
    }
    excel.Visible=false;
    excel.Sheets[0] = "sss";
    excel.ActiveWorkbook.SaveAs
strExcelFileName+".XLS",Excel.XlFileFormat.xlExcel9795,null,null,false,false,Excel.XlSaveAsAccessMode.xlNoChange,null,null,null,null);
                
    //wkbNew.SaveAs strBookName
    //excel.Save(strExcelFileName);
    excel.Quit();
    excel=null;

 

    GC.Collect();//垃圾回收
}