C#中如何导入/导出Excel


首先要添加引用,没有添加引用会找不到命名空间
添加using Microsoft.Office.Interop.Excel;和using System.Data.OleDb;命名空间
加载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  
    {  
    }  

}

导入Excel时如果失败,请检查你导入的是03版Excle还是07版的Excel,如果操作07版的Excel会报“找不到可安装的ISAM”错误,解决方法是换掉连接字符串为Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

声明:初心|版权所有,违者必究|如未注明,均为原创|本网站采用BY-NC-SA协议进行授权

转载:转载请注明原文链接 - C#中如何导入/导出Excel


愿你勿忘初心,并从一而终