wpf导入导出excel -- 使用微软自带

微软自带Microsoft.Office.Interop.Excel

此方法缺点不少，导出可能会比较慢，可能会受使用电脑环境影响。目前导出是xlsx格式，想导出xls格式另行研究。

//创建Excel对象
System.Reflection.Missing miss = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excelApp = new Microsoft.Office.Interop.Excel.Application();
excelApp.Visible = false; // 显示导出过程

//创建工作簿（WorkBook：即Excel文件主体本身）
Microsoft.Office.Interop.Excel.Workbook mBook = (Microsoft.Office.Interop.Excel.Workbook)excelApp.Workbooks.Add(miss);


Microsoft.Office.Interop.Excel.Worksheet mSheet = (Microsoft.Office.Interop.Excel.Worksheet)mBook.Worksheets.Add(miss, miss, 2, Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet);

// 创建工作表
Microsoft.Office.Interop.Excel.Worksheet ws1 = DataTableToWorksheet(mBook.Worksheets[1], project);
ws1.Name = "project";
Microsoft.Office.Interop.Excel.Worksheet ws2 = DataTableToWorksheet(mBook.Worksheets[2], archives);
ws2.Name = "archives";
Microsoft.Office.Interop.Excel.Worksheet ws3 = DataTableToWorksheet(mBook.Worksheets[3], volumes);
ws3.Name = "volumes";

mBook.SaveAs(TB1.Text + DateTime.Now.ToString("yyyyMMdd"));  //将其进行保存到指定的路径，TB1.Text当前文件保存目录
mBook.Close();
excelApp.Quit();  //KillAllExcel(excelApp); 释放可能还没释放的进程

DataTableToWorksheet 方法，将DataTable转为Worksheet并且可以设置单元格格式

public Microsoft.Office.Interop.Excel.Worksheet DataTableToWorksheet(Microsoft.Office.Interop.Excel.Worksheet excelWS, DataTable dt)
{
    int columnCount = dt.Columns.Count;
    int rowCount = dt.Rows.Count;

    // 设置标题
    Microsoft.Office.Interop.Excel.Range titleRange = excelWS.Range[excelWS.Cells[1, 1], excelWS.Cells[1, columnCount]]; // 选取单元格
    //titleRange.Merge(false);                                         //合并单元格
    titleRange.Font.Name = "微软雅黑";                              //设置字体
    titleRange.Font.Size = 16;                                      //字体大小
    titleRange.Font.Bold = true;                                    //加粗显示
    titleRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;       //水平居中
    titleRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;         //垂直居中
    titleRange.EntireColumn.AutoFit(); //自动调整列宽

    //设置每列格式
    for (int i = 0; i < columnCount; i++)
    {
        Microsoft.Office.Interop.Excel.Range contentRange = excelWS.Range[excelWS.Cells[2, i + 1], excelWS.Cells[rowCount, i + 1]]; // 不用标题则从第二行开始
        contentRange.Font.Name = "微软雅黑";
        contentRange.Font.Size = 10;
        contentRange.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; // 水平居中
        contentRange.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;   // 垂直居中
        contentRange.WrapText = true;                               // 自动换行
        contentRange.NumberFormatLocal = "@";                       // 文本格式
    }

    for (int i = 0; i < dt.Rows.Count; i++)
    {
        for (int j = 0; j < dt.Columns.Count; j++)
        {
            excelWS.Cells[i + 1, j + 1] = dt.Rows[i][j].ToString();   //Excel单元格第一个从索引1开始
        }
    }
    return excelWS;
}

目前只能接受xls格式，xlsx导入会报错

///   

///  读取Excel文件
///   
///   
///   
public DataTable LoadExcel(string pPath)
{

    string connString = " Driver={Driver do Microsoft Excel(*.xls)};DriverId=790;SafeTransactions=0;ReadOnly=1;MaxScanRows=16;Threads=3;MaxBufferSize=2024;UserCommitSync=Yes;FIL=excel 8.0;PageTimeout=5; ";
    connString += " DBQ= " + pPath;
    OdbcConnection conn = new OdbcConnection(connString);
    OdbcCommand cmd = new OdbcCommand();
    cmd.Connection = conn;
    // 获取Excel中第一个Sheet名称，作为查询时的表名
    string sheetName = this.GetExcelSheetName(pPath);
    string sql = "select * from [" + sheetName.Replace('.', '#') + "$]";
    cmd.CommandText = sql;
    OdbcDataAdapter da = new OdbcDataAdapter(cmd);
    DataSet ds = new DataSet();
    try
    {
        da.Fill(ds);
        return ds.Tables[0];
    }
    catch (Exception x)
    {
        ds = null;
        throw new Exception(" 从Excel文件中获取数据时发生错误！ ");
    }
    finally
    {
        cmd.Dispose();
        cmd = null;
        da.Dispose();
        da = null;
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
        }
        conn = null;
    }
}

// 获取工作表名称
private string GetExcelSheetName(string pPath)
{
    //打开一个Excel应用
    Microsoft.Office.Interop.Excel.Application excelApp;
    Microsoft.Office.Interop.Excel.Workbook excelWB;//创建工作簿（WorkBook：即Excel文件主体本身）
    Microsoft.Office.Interop.Excel.Workbooks excelWBs;
    Microsoft.Office.Interop.Excel.Worksheet excelWS;//创建工作表（即Excel里的子表sheet）

    Microsoft.Office.Interop.Excel.Sheets excelSts;

    excelApp = new Microsoft.Office.Interop.Excel.Application();
    if (excelApp == null)
    {
        throw new Exception("打开Excel应用时发生错误！");
    }
    excelWBs = excelApp.Workbooks;
    //打开一个现有的工作薄
    excelWB = excelWBs.Add(pPath);
    excelSts = excelWB.Sheets;
    //选择第一个Sheet页
    excelWS = excelSts.get_Item(1);
    string sheetName = excelWS.Name;

    ReleaseCOM(excelWS);
    ReleaseCOM(excelSts);
    ReleaseCOM(excelWB);
    ReleaseCOM(excelWBs);
    excelApp.Quit();
    ReleaseCOM(excelApp);
    return sheetName;
}

// 释放资源
private void ReleaseCOM(object pObj)
{
    try
    {
        System.Runtime.InteropServices.Marshal.ReleaseComObject(pObj);
    }
    catch
    {
        throw new Exception("释放资源时发生错误！");
    }
    finally
    {
        pObj = null;
    }
}