c#中Excel数据的导入、导出

/// <summary>

/// 导出到 Excel 文件

/// </summary>

/// <param name="fileName">含完整路径</param>

/// <param name="dataTable">含字段标题名</param>

public void ExpExcel(string fileName ,DataTable dataTable)

{

    Excel.ApplicationClass apc =new Excel.ApplicationClass();

    apc.Visible = false ;

    Excel.Workbook wkbook = apc.Workbooks.Add( true ) ;

    Excel.Worksheet wksheet = (Excel.Worksheet)wkbook.ActiveSheet;

    int rowIndex = 2;

    int colIndex = 1;

    wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).NumberFormat = "@";

    //取得列标题

    foreach (DataColumn dc in dataTable.Columns)

    {

        colIndex ++;

        wksheet.Cells[1,colIndex] = dc.ColumnName;

    }

    //取得表格中数据

    foreach (DataRow dr in dataTable.Rows)

    {

        colIndex = 1;

        foreach (DataColumn dc in dataTable.Columns)

        {

            if(dc.DataType == System.Type.GetType("System.DateTime"))

            {

                apc.Cells[rowIndex,colIndex] = "'"+(Convert.ToDateTime(dr[dc.ColumnName].ToString())).ToString("yyyy-MM-dd");

            }

            else

                if(dc.DataType == System.Type.GetType("System.String"))

            {

                apc.Cells[rowIndex,colIndex] = "'"+dr[dc.ColumnName].ToString();

            }

            else

            {

                apc.Cells[rowIndex,colIndex] = "'"+dr[dc.ColumnName].ToString();

            }

            wksheet.get_Range(apc.Cells[rowIndex,colIndex],apc.Cells[rowIndex,colIndex]).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

            colIndex++;

        }

        rowIndex++;

    }

    

    //设置表格样式

    wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Interior.ColorIndex = 20; 

    wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Font.ColorIndex = 3;

    wksheet.get_Range(apc.Cells[1,1],apc.Cells[1,dataTable.Columns.Count]).Borders.Weight = Excel.XlBorderWeight.xlThin;

    wksheet.get_Range(apc.Cells[1,1],apc.Cells[dataTable.Rows.Count,dataTable.Columns.Count]).Columns.AutoFit();

    if(File.Exists(fileName))

    {

        File.Delete(fileName);

    }

    wkbook.SaveAs( fileName ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange ,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);

   

    wkbook.Close(Type.Missing,Type.Missing,Type.Missing);

    apc.Quit();

    wkbook = null;

    apc = null;

    GC.Collect();

}

/// <summary>

/// 从Excel导入帐户(逐单元格读取)

/// </summary>

/// <param name="fileName">完整路径名</param>

public IList ImpExcel(string fileName)

{

    IList alExcel = new ArrayList();

    UserInfo userInfo = new UserInfo();

    Excel.Application app;

    Excel.Workbooks wbs;

    Excel.Worksheet ws;

    app = new Excel.Application();

    wbs = app.Workbooks;

    wbs.Add(fileName);

    ws= (Excel.Worksheet)app.Worksheets.get_Item(1);

    int a = ws.Rows.Count;

    int b = ws.Columns.Count;

    

    for ( int i = 2; i < 4; i++)

    {

        for ( int j = 1; j < 21; j++)

        {

            Excel.Range range = ws.get_Range(app.Cells[i,j],app.Cells[i,j]);

            range.Select();

            alExcel.Add( app.ActiveCell.Text.ToString() );

        }

    }

    return alExcel;

}

/// <summary>

/// 从Excel导入帐户(新建oleDb连接,Excel整表读取,适于无合并单元格时)

/// </summary>

/// <param name="fileName">完整路径名</param>

/// <returns></returns>

public DataTable ImpExcelDt (string fileName)

{

    string strCon = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0" ;

    OleDbConnection myConn = new OleDbConnection ( strCon ) ;

    string strCom = " SELECT * FROM [Sheet1$] " ;

    myConn.Open ( ) ;

    OleDbDataAdapter myCommand = new OleDbDataAdapter ( strCom , myConn ) ;

    DataSet myDataSet = new DataSet ( ) ;

    myCommand.Fill ( myDataSet , "[Sheet1$]" ) ;

    myConn.Close ( ) ;

    DataTable dtUsers = myDataSet.Tables[0];

    return dtUsers;

}

dataGrid中显示：

DataGrid1.DataMember= "[Sheet1$]" ;

DataGrid1.DataSource = myDataSet ;