C# Excel导入操作，将集合类转换为DataTable

DataTable dataTable = Cxt.DataTableEx.CreateTable("客户代码,客户名称,日期,单据编号,通知单号,商品代码,商品名称,颜色代码,颜色名称,尺码代码,尺码名称,sku", ',');
private void darkButton3_Click(object sender, EventArgs e)
{
    tabControl1.SelectTab(1);
    dataTable.Rows.Clear();
    using (OpenFileDialog fd = new OpenFileDialog())
    {
        fd.Title = "请选择待处理订单EXCEL文件";
        fd.Filter = "Excel 2007文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";
        if (fd.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
        {
            try
            {
                using (ExcelPackage pack = new ExcelPackage(new FileInfo(fd.FileName)))
                {
                    ExcelWorksheet sheet = pack.Workbook.Worksheets["Sheet1"];
                    var rowCount = sheet.Dimension.End.Row;
                    var columnCount = sheet.Dimension.End.Column;
                    List<int> colList = new List<int>();
                    for (int j = 1; j <= columnCount; j++)
                        if (dataTable.Columns.Contains(sheet.Cells[1, j].Value.ToString()))
                            colList.Add(j);

                    for (int i = 2; i <= rowCount; i++)
                    {
                        dataTable.Rows.Add(dataTable.NewRow());
                        foreach (var j in colList) 
                            dataTable.Rows[i - 2][j - 1] = sheet.Cells[i, j].Value; 
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(this, ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
        }
    }
    foreach (DataRow item in dataTable.Rows)
    {
        item["sku"] = item["商品代码"].ToString() + item["颜色代码"] + item["尺码代码"];
    }
    dataGridView1.DataSource = dataTable;
}

        DataTable dataTable = Cxt.DataTableEx.CreateTable("客户代码,客户名称,日期,单据编号,通知单号,商品代码,商品名称,颜色代码,颜色名称,尺码代码,尺码名称,sku,数量", ',');
        private void darkButton3_Click(object sender, EventArgs e)
        {
            tabControl1.SelectTab(1);
            dataTable.Rows.Clear();
            using (OpenFileDialog fd = new OpenFileDialog())
            {
                fd.Title = "请选择待处理订单EXCEL文件";
                fd.Filter = "Excel 2007文件(*.xlsx)|*.xlsx|所有文件(*.*)|*.*";
                if (fd.ShowDialog(this) == System.Windows.Forms.DialogResult.OK)
                {
                    try
                    {
                        using (ExcelPackage pack = new ExcelPackage(new FileInfo(fd.FileName)))
                        {
                            ExcelWorksheet sheet = pack.Workbook.Worksheets["Sheet1"];
                            var rowCount = sheet.Dimension.End.Row;
                            var columnCount = sheet.Dimension.End.Column;
                            List<int> colList = new List<int>();
                            foreach (System.Data.DataColumn colum in dataTable.Columns)
                            {
                                int b = 0;//列号，找到用对应列,找不到用空代替
                                for (int j = 1; j <= columnCount; j++)
                                    if (colum.Caption == (sheet.Cells[1, j].Value.ToString()))
                                        b = j;
                                colList.Add(b);
                            } 

                            for (int i = 2; i <= rowCount; i++)
                            {
                                dataTable.Rows.Add(dataTable.NewRow());
                                for (int j = 0; j < colList.Count; j++)
                                    if (colList[j] != 0)
                                        dataTable.Rows[i - 2][j] = sheet.Cells[i, colList[j]].Value ;
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(this, ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                    }
                }
            }
            foreach (DataRow item in dataTable.Rows)
            {
                item["sku"] = item["商品代码"].ToString() + item["颜色代码"] + item["尺码代码"];
            }
            dataGridView1.DataSource = dataTable;
        }

 

/// <summary>
/// 将集合类转换为DataTable
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static DataTable ToDataTableTow(System.Collections.IList list)
{
    if (list.Count == 0) return null;
    DataTable dt = new DataTable();
    System.Reflection.PropertyInfo[] propertys = list[0].GetType().GetProperties();
    foreach (System.Reflection.PropertyInfo pi in propertys)
    {
        dt.Columns.Add(pi.Name, pi.PropertyType);//添加列
    }
    List<object> tempList = null;
    for (int i = 0; i < list.Count; i++)
    {
        tempList = new List<object>();
        foreach (System.Reflection.PropertyInfo pi in propertys)
        {
            tempList.Add(pi.GetValue(list[i], null));
        }
        dt.LoadDataRow(tempList.ToArray(), true);
    }
    return dt;
}

/// <summary>
/// 将集合类转换为DataTable
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static DataTable DicToDataTableTow(System.Collections.IList list)
{
    if (list.Count == 0) return null;
    DataTable dt = new DataTable();

    if (list[0].GetType() == typeof(Dictionary<string, object>))
    {
        foreach (var item in list[0] as Dictionary<string, object>)
        {
            dt.Columns.Add(item.Key, item.Value == null ? typeof(System.String) : item.Value.GetType());//添加列 item.Key
        }
    }
    foreach (Dictionary<string, object> dic in list)
    {
        List<object> tempList = new List<object>();
        foreach (var item in dic)
        {
            tempList.Add(item.Value);
        }
        dt.LoadDataRow(tempList.ToArray(), true);
    }

    return dt;
}