Execl文件读写操作

/**/ /// <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 ;

首先需要添加一个引用：

文件开头增加：

using Microsoft.Office.Interop.Excel;

private void readBt_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.Application app;
            Workbooks wbs;
            Worksheet ws;

            app = new Microsoft.Office.Interop.Excel.Application();
            wbs = app.Workbooks;
            wbs.Add(@"d:/DataTableToExcel.xls");
            ws = (Worksheet)app.Worksheets.get_Item(2);
            int a = ws.Rows.Count;
            int b = ws.Columns.Count;
            MessageBox.Show(a.ToString() + "|" + b.ToString());
            //for (int i = 1; i < 3; i++)
            {
                for (int j = 1; j <= 15; j++)
                {
                    Range range = ws.get_Range(app.Cells[j, 1], app.Cells[j, 1]);
                    range.Select();
                    MessageBox.Show(app.ActiveCell.Text.ToString());
                    if (app.ActiveCell.Text.Equals("")) break;
                    range = ws.get_Range(app.Cells[j, 2], app.Cells[j, 2]);
                    range.Select();
                    MessageBox.Show(app.ActiveCell.Text.ToString());
                }
            }

        }

        private void writeBt_Click(object sender, EventArgs e)
        {
            Microsoft.Office.Interop.Excel.ApplicationClass apc = new ApplicationClass();
            apc.Visible = false;
            Workbook wkbook = apc.Workbooks.Add(true);
            Worksheet wksheet = (Worksheet)wkbook.ActiveSheet;
            int rowIndex = 2;
            int colIndex = 1;

            wksheet.get_Range(apc.Cells[1, 1], apc.Cells[5, 3]).NumberFormat = "@";
            //取得列标题
            for(int i=1;i<=3;i++)
            {
                wksheet.Cells[1, i] = "C" + i.ToString();
            }
            //写入行
            for (int i = 1; i <= 5; i++)
            {
                apc.Cells[i, 1] = "L" + i.ToString();
                apc.Cells[i, 2] = "L" + i.ToString();
                apc.Cells[i, 3] = "L" + i.ToString();
                wksheet.get_Range(apc.Cells[i, 1], apc.Cells[i, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
                wksheet.get_Range(apc.Cells[i, 2], apc.Cells[i, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
                wksheet.get_Range(apc.Cells[i, 3], apc.Cells[i, 3]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
            }

            //设置表格样式
            wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, 3]).Interior.ColorIndex = 20;
            wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, 3]).Font.ColorIndex = 3;
            wksheet.get_Range(apc.Cells[1, 1], apc.Cells[1, 3]).Borders.Weight = XlBorderWeight.xlThin;
            wksheet.get_Range(apc.Cells[1, 1], apc.Cells[5, 3]).Columns.AutoFit();

            if (File.Exists(@"d:/test.xls"))
            {
                File.Delete(@"d:/test.xls");
            }

            wkbook.SaveAs(@"d:/test.xls", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, 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;

        }