读写Excel

需求：现有2个Excel，一个7000，一个20W，7000在20W是完全存在的。现要分离20W的，拆分成19W3和7000。

条件：两个Excel都有“登录名”，然后用“登录名”去关联2个Excel

引用：Aspose.Cells.dll

public void Excel()
{
    //获取第一个Excel,20W
    string filePath = AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/测试20W.xlsx";
    System.Data.DataTable table = GetTableFromExcel("sheet1", filePath);

    //克隆
    System.Data.DataTable table20W_new = table.Clone();
    System.Data.DataTable table7000_new = table.Clone();    

    //获取第二个Excel,7000
    string filePath_7000 = AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/测试7000.xls";
    System.Data.DataTable table_7000 = GetTableFromExcel("sheet1", filePath_7000);

    //循环20W人中的挑出来
    for (int i = ; i < table.Rows.Count; ++i)
    {
        //20W
        DataRow dateRow = table.Rows[i];
        string login_name = dateRow["登录名"].ToString();

        //
        DataRow[] drss = table_7000.Select("登录名 = '" + login_name + "'");
　　　　
        if (drss.Length > )
        {
            table7000_new.ImportRow(dateRow);
        }
        else
        {
            table20W_new.ImportRow(dateRow);
        }
    }
    //导出Excel
    DataTableExport(table7000_new, AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/7000.xlsx");
    DataTableExport(table20W_new, AppDomain.CurrentDomain.BaseDirectory.Replace("\\", "/") + "daochu/22W.xlsx");
}

获取Excel内容，转成DataTable。

/// <summary>
/// 获取Excel内容。
/// </summary>
/// <param name="sheetName">工作表名称，例：sheet1</param>
/// <param name="filePath">Excel路径</param>
/// <returns></returns>
private DataTable GetTableFromExcel(string sheetName, string filePath)
{
    const string connStrTemplate = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0;HDR=Yes;\"";
    DataTable dt = null;
    if (!System.IO.File.Exists(filePath))
    {
        // don't find file
        return null;
    }
    OleDbConnection conn = new OleDbConnection(string.Format(connStrTemplate, filePath));
    try
    {
        conn.Open();
        if (sheetName == null || sheetName.Trim().Length == )
        {
            DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
            sheetName = schemaTable.Rows[]["TABLE_NAME"].ToString().Trim();
        }
        else
        {
            sheetName += "$";
        }

        string strSQL = "Select * From [" + sheetName + "]";
        OleDbDataAdapter da = new OleDbDataAdapter(strSQL, conn);
        DataSet ds = new DataSet();
        da.Fill(ds);
        dt = ds.Tables[];
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {
        conn.Close();
    }

    return dt;
}

将DataTable的数据写进Excel里(用的Aspose.Cells.dll)

/// <summary>
/// DataTable数据导出Excel
/// </summary>
/// <param name="data"></param>
/// <param name="filepath"></param>
public static void DataTableExport(DataTable data, string filepath)
{
    try
    {
        Workbook book = new Workbook(); //创建工作簿
        Worksheet sheet = book.Worksheets[0]; //创建工作表
        Cells cells = sheet.Cells; //单元格
        //创建样式
        Aspose.Cells.Style style = book.Styles[book.Styles.Add()];
        style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 左边界线
        style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 右边界线
        style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 上边界线
        style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin; //应用边界线 下边界线
        style.HorizontalAlignment = TextAlignmentType.Center; //单元格内容的水平对齐方式文字居中
        style.Font.Name = "宋体"; //字体
        //style1.Font.IsBold = true; //设置粗体
        style.Font.Size = 11; //设置字体大小
        //style.ForegroundColor = System.Drawing.Color.FromArgb(153, 204, 0); //背景色
        //style.Pattern = Aspose.Cells.BackgroundType.Solid;  

        int Colnum = data.Columns.Count;//表格列数
        int Rownum = data.Rows.Count;//表格行数
        //生成行 列名行
        for (int i = 0; i < Colnum; i++)
        {
            cells[0, i].PutValue(data.Columns[i].ColumnName); //添加表头
            cells[0, i].SetStyle(style); //添加样式
        }
        //生成数据行
        for (int i = 0; i < Rownum; i++)
        {
            for (int k = 0; k < Colnum; k++)
            {
                cells[1 + i, k].PutValue(data.Rows[i][k].ToString()); //添加数据
                cells[1 + i, k].SetStyle(style); //添加样式
            }
        }
        sheet.AutoFitColumns(); //自适应宽
        book.Save(filepath); //保存
        GC.Collect();
    }
    catch (Exception e)
    {
        logger.Error("生成excel出错：" + e.Message);
    }
}

将List的数据写进Excel里(用的Aspose.Cells.dll)

/// <summary>
/// 导出excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="data">Ilist集合</param>
/// <param name="filepath">保存的地址</param>
public static void Export<T>(IList<T> data, string filepath)
{
    try
    {
        Workbook workbook = new Workbook();
        Worksheet sheet = (Worksheet)workbook.Worksheets[];

        PropertyInfo[] ps = typeof(T).GetProperties();
        var colIndex = "A";
        foreach (var p in ps)
        {
            //    sheet.Cells[colIndex + 1].PutValue(p.Name);//设置表头名称  要求表头为中文所以不用 p.name 为字段名称 可在list第一条数据为表头名称
            int i = ;
            foreach (var d in data)
            {
                sheet.Cells[colIndex + i].PutValue(p.GetValue(d, null));
                i++;
            }
            colIndex = getxls_top(colIndex); //((char)(colIndex[0] + 1)).ToString();//表头  A1/A2/
        }
        //workbook.Shared = true;
        workbook.Save(filepath);
        GC.Collect();
    }
    catch (Exception e)
    {
        logger.Error("生成excel出错：" + e.Message);
    }
}
/// <summary>
/// 生成新的对应的列  A-Z  AA-ZZ
/// </summary>
/// <param name="top">当前列</param>
/// <returns></returns>
private static string getxls_top(string top)
{
    char[] toplist = top.ToArray();
    var itemtop = top.Last();
    string topstr = string.Empty;
    if ((char)itemtop == )//最后一个是Z
    {
        if (toplist.Count() == )
        {
            topstr = "AA";
        }
        else
        {
            toplist[] = (char)(toplist[] + );
            toplist[toplist.Count() - ] = 'A';
            foreach (var item in toplist)
            {
                topstr += item.ToString();
            }
        }
    }
    else//最后一个不是Z  包括top为两个字符
    {
        itemtop = (char)(itemtop + );
        toplist[toplist.Count() - ] = itemtop;

        foreach (var item in toplist)
        {
            topstr += item.ToString();
        }
    }
    return topstr;
}

将DataTable的数据写进Excel里(用的Microsoft.Office.Interop.Excel.dll)

此方法在大量数据的时候很慢，例如22W条数据，建议使用Aspose.Cells.dll，速度快很多

/// <summary>
/// 将DataTable的数据写进Excel里
/// </summary>
/// <param name="tmpDataTable">DataTable数据</param>
/// <param name="strFileName">Excel路径</param>
public static void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
    if (tmpDataTable == null)
    {
        return;
    }
    int rowNum = tmpDataTable.Rows.Count;
    int columnNum = tmpDataTable.Columns.Count;
    int rowIndex = ;
    int columnIndex = ;

    //需要引用Microsoft.Office.Interop.Excel.dll
    Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
    xlApp.DefaultFilePath = "";
    xlApp.DisplayAlerts = true;
    xlApp.SheetsInNewWorkbook = ;
    Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true);

    //将DataTable的列名导入Excel表第一行
    foreach (DataColumn dc in tmpDataTable.Columns)
    {
        columnIndex++;
        xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName;
    }

    //将DataTable中的数据导入Excel中
    for (int i = ; i < rowNum; i++)
    {
        rowIndex++;
        columnIndex = ;
        for (int j = ; j < columnNum; j++)
        {
            columnIndex++;
            xlApp.Cells[rowIndex, columnIndex] = tmpDataTable.Rows[i][j].ToString();
        }
    }
    //xlBook.SaveCopyAs(HttpUtility.UrlDecode(strFileName, System.Text.Encoding.UTF8));
    xlBook.SaveCopyAs(strFileName);
}

原生的DataTable生成Excel(无需引用第三方dll)

/// <summary>
/// 将DataTable的数据写进Excel里
/// </summary>
/// <param name="tdKeChengZhuanJiaTongJi">DataTable</param>
/// <param name="sheet">sheet自定义名称</param>
/// <param name="fileName">Excel路径</param>
public static void DataTabletoExcel(DataTable dt, string sheet, string fileName)
{
    String sConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0 Xml;\"";
    //string sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;'";
    //String sConnectionString = "Provider=Microsoft.ACE.OLEDB.14.0;Data Source=" + fileName + ";Extended Properties=\"Excel 14.0 Xml;\"";
    //String sConnectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" + fileName + ";Extended Properties=\"Excel 16.0 Xml;HDR=YES;\"";

    OleDbConnection cn = new OleDbConnection(sConnectionString);

    int rowNum = dt.Rows.Count;//获取行数
    int colNum = dt.Columns.Count;//获取列数
    string sqlText = "";//带类型的列名
    string sqlValues = "";//值
    string colCaption = "";//列名
    for (int i = ; i < colNum; i++)
    {
        if (i != )
        {
            sqlText += " , ";
            colCaption += " , ";
        }
        sqlText += "[" + dt.Columns[i].Caption.ToString() + "] VarChar";//生成带VarChar列的标题
        colCaption += "[" + dt.Columns[i].Caption.ToString() + "]";//生成列的标题
    }
    try
    {
        //打开连接
        cn.Open();
        string sqlCreate = "CREATE TABLE [" + sheet.ToString() + "] (" + sqlText + ")";
        OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);
        //创建Excel文件
        cmd.ExecuteNonQuery();
        for (int srow = ; srow < rowNum; srow++)
        {
            sqlValues = "";
            for (int col = ; col < colNum; col++)
            {
                if (col != )
                {
                    sqlValues += " , ";
                }
                sqlValues += "'" + dt.Rows[srow][col].ToString() + "'";//拼接Value语句
            }
            String queryString = "INSERT INTO [" + sheet.ToString() + "] (" + colCaption + ") VALUES (" + sqlValues + ")";
            cmd.CommandText = queryString;
            cmd.ExecuteNonQuery();//插入数据
        }
    }
    catch
    {
　　　　//生成日志
    }
    finally
    {
        cn.Close();
    }
}