返回顶部

收藏

c#读取、写入excel总结

更多

下面是csdn网友gisfarmer提供的读取,写入excel的方法总结。

加载Excel(读取excel内容)返回值是一个DataSet

//加载Excel   
public static DataSet LoadDataFromExcel(string filePath)  
{  
    try  
    {  
        string strConn;  
        strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=False;IMEX=1'";  
        OleDbConnection OleConn = new OleDbConnection(strConn);  
        OleConn.Open();  
        String sql = "SELECT * FROM  [Sheet1$]";//可是更改Sheet名称,比如sheet2,等等   

        OleDbDataAdapter OleDaExcel = new OleDbDataAdapter(sql, OleConn);  
        DataSet OleDsExcle = new DataSet();  
        OleDaExcel.Fill(OleDsExcle, "Sheet1");  
        OleConn.Close();  
        return OleDsExcle;  
    }  
    catch (Exception err)  
    {  
        MessageBox.Show("数据绑定Excel失败!失败原因:" + err.Message, "提示信息",  
            MessageBoxButtons.OK, MessageBoxIcon.Information);  
        return null;  
    }  
}  

写入Excel内容,参数:excelTable是要导入excel的一个table表

public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath)  
{  
    Microsoft.Office.Interop.Excel.Application app =  
        new Microsoft.Office.Interop.Excel.ApplicationClass();  
    try  
    {  
        app.Visible = false;  
        Workbook wBook = app.Workbooks.Add(true);  
        Worksheet wSheet = wBook.Worksheets[1] as Worksheet;  
        if (excelTable.Rows.Count > 0)  
        {  
            int row = 0;  
            row = excelTable.Rows.Count;  
            int col = excelTable.Columns.Count;  
            for (int i = 0; i < row; i++)  
            {  
                for (int j = 0; j < col; j++)  
                {  
                    string str = excelTable.Rows[i][j].ToString();  
                    wSheet.Cells[i + 2, j + 1] = str;  
                }  
            }  
        }  

        int size = excelTable.Columns.Count;  
        for (int i = 0; i < size; i++)  
        {  
            wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName;  
        }  
        //设置禁止弹出保存和覆盖的询问提示框   
        app.DisplayAlerts = false;  
        app.AlertBeforeOverwriting = false;  
        //保存工作簿   
        wBook.Save();  
        //保存excel文件   
        app.Save(filePath);  
        app.SaveWorkspace(filePath);  
        app.Quit();  
        app = null;  
        return true;  
    }  
    catch (Exception err)  
    {  
        MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息",  
            MessageBoxButtons.OK, MessageBoxIcon.Information);  
        return false;  
    }  
    finally  
    {  
    }  
}  

标签:c#,excel

收藏

0人收藏

支持

1

反对

0

发表评论