c# 导入Excel 存到DataTable并进行行转列操作及合并DataTable相同行的值

最新推荐文章于 2025-02-19 15:54:02 发布

蝶恋花雨

最新推荐文章于 2025-02-19 15:54:02 发布

阅读量7.4k

点赞数 1

分类专栏： .NET And C# 文章标签： Linq DataTable 行转列 DataTable相同行进行合并

本文链接：https://blog.youkuaiyun.com/kongwei521/article/details/50554946

版权

.NET And C# 专栏收录该内容

136 篇文章

订阅专栏

由于公司需要就进行研究下并转化为代码：

原Excel数据

配送センターコード	店コード	总计	L	W	H
9999	0951	8	55	45	60
9999	0952	8	55	45	60
9999	0953	8	55	45	60
9999	0954	8	55	45	60
9999	0955	8	55	45	60
9999	0956	8	55	45	60
9999	0957	8	55	45	60
9999	0958	8	55	45	60
9999	0959	8	55	45	60
9999	0960	8	55	45	60
9991	0451	16	55	45	60
9991	0451	16	55	45	48
9991	0528	16	55	45	48
9991	0534	16	55	45	48
9991	0539	16	55	45	48
9991	0541	16	55	45	48
9991	0543	16	55	45	48
9991	0545	16	55	45	48
9991	0546	16	55	45	48
9991	0547	16	55	45	48
9991	0549	16	55	45	48
9991	0552	16	55	45	48
9992	2801	144	55	45	50
9991	0528	16	55	45	48
9991	0534	16	55	45	48
9991	0539	16	55	45	48
9991	0541	16	55	45	48
9991	0543	16	55	45	48
9991	0545	16	55	45	48
9991	0546	16	55	45	48
9991	0547	16	55	45	48
9991	0549	16	55	45	48
9991	0552	16	55	45	48
9992	2801	144	55	45	50
9992	2801	144	55	45	50
9992	2801	144	55	45	50
9992	2801	144	55	45	50
9992	2801	144	55	45	50
9992	2801	144	55	45	50
9992	2801	144	55	45	50
9992	2801	144	55	45	50

1：导入Excel ，利用aspose.cells 导入

    Workbook book = new Workbook();
                book.Open(oFD.FileName);
                Worksheet sheet = book.Worksheets[0];
                Cells cells = sheet.Cells;

                DataTable dtTotal = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);

导入后Dt里面显示的数据如excel一样, 进行分组筛选后得到的行数据如下：

但是这不是想要，要得到的效果，因此要在加工些，要把行数据转化为列

行转列代码如下：

   public static DataTable ConvertToTable(DataTable source)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("L");
            dt.Columns.Add("W");
            dt.Columns.Add("H");

            //以Item 字段为筛选条件  列转为行  下面有图
            var columns = (from x in source.Rows.Cast<DataRow>() select x[0].ToString()).Distinct();

            //把 Item 字段 做为新字段添加进去
            foreach (var item in columns) dt.Columns.Add(item).DefaultValue = 0;

            //进行Group分组提取数据
            var data = from x in source.Rows.Cast<DataRow>()
                       group x by new { ShopCode = x[0], L = x[3], W = x[4], H = x[5] } into g
                       select new
                       {
                           ShopCode = g.Key.ShopCode,
                           Items = g,
                           L = g.Key.L,
                           W = g.Key.W,
                           H = g.Key.H,
                           Vol = Convert.ToDecimal(g.Key.L) / 100 * Convert.ToDecimal(g.Key.W) / 100
                           * Convert.ToDecimal(g.Key.H) / 100 * g.Count(),
                           SumCount = g.Count()
                       };

            data.ToList().ForEach(x =>
            {
                //这里用的是一个string 数组 也可以用DataRow根据个人需要用
                string[] array = new string[dt.Columns.Count];
                array[0] = x.L.ToString(); array[1] = x.W.ToString(); array[2] = x.H.ToString();
                //  array[3] = x.Vol.ToString();   array[4] = x.SumCount.ToString(); ;
                codeid = 0;
                for (int i = 3; i < dt.Columns.Count; i++)
                {
                    array[i] = (from y in x.Items
                                where y[0].ToString().Contains(dt.Columns[i].ToString())
                                select y
              ).Count().ToString();
                    codeid++;
                }
                dt.Rows.Add(array);   //添加到table中
            });


            return dt;
        }

oK到此行转列至此结束,但一看结果感觉怪怪，因为 L W H 有两条一样的，只是数量和配送センターコード不一样，怎么办？只能继续进行

下一步。相同行数进行合并，代码如下：

  public static void FindComValueTest()
       {
           if (dataTotal.Rows.Count > 0)
           {
               string row1 = dataTotal.Rows[0][2].ToString();//取表的第一行第一列
               DataRow[] drr = dataTotal.Select("H=" + row1);
               drTemp = tabTemp.NewRow();//临时新的行
               foreach (DataRow row in drr)
               {
                   drTemp[0] = row[0].ToString();
                   drTemp[1] = row[1].ToString();
                   drTemp[2] = row[2].ToString();
                   for (int i = 3; i < dataTotal.Columns.Count; i++)
                   {
                       if (i != 0)
                       {
                           if (drTemp[i].ToString().Trim() == "")
                               drTemp[i] = "0";
                           drTemp[i] = Convert.ToInt32(drTemp[i].ToString()) + Convert.ToInt32(row[i].ToString() == "" ? "0" : row[i].ToString());//合并每一列的数据
                       }
                       else
                       {
                           drTemp[i] = row[i].ToString();//添加第一列，不需要相加
                       }

                   }

                   dataTotal.Rows.Remove(row);//清除此类的行
               }

               var sumCount = drTemp.ItemArray.Skip(3).ToList().Sum(x => Convert.ToInt32(x.ToString() == "" ? "0" : x.ToString()));
               //   var column = tabTemp.Columns.Count - 5;

               drTemp[3 + codeid] = Convert.ToDecimal(drTemp[0]) / 100 * Convert.ToDecimal(drTemp[1]) / 100
                        * Convert.ToDecimal(drTemp[2]) / 100 * sumCount;

               drTemp[4 + codeid] = sumCount;

               tabTemp.Rows.Add(drTemp);//添加临时新的行数据
               FindComValueTest();
           }
       }

执行完过后才是真正想要的结果了

最终由导入Excel 存放到DataTable 然后进行分组筛选后行转列，然后合并DataTable相同行的数据到此介绍。完整代码如下：

导入事件代码：

   private void btnImport_Click(object sender, EventArgs e)
        {
            OpenFileDialog oFD = new OpenFileDialog();
            oFD.Title = "打开文件";
           
            oFD.Filter = "Excel文件 (*.xls.*.xlsx)|*.xls;*.xlsx";//过滤格式
            oFD.FilterIndex = 1;                                    //格式索引
            oFD.RestoreDirectory = false;
            oFD.InitialDirectory = "c:\\";                          //默认路径
            oFD.Multiselect = true;                                 //是否多选
            if (oFD.ShowDialog() == DialogResult.OK)
            {
                ImportExportHandle.tabTemp.Clear();
                ImportExportHandle.dataTotal.Clear();
                Workbook book = new Workbook();
                book.Open(oFD.FileName);
                Worksheet sheet = book.Worksheets[0];
                Cells cells = sheet.Cells;

                DataTable dtTotal = cells.ExportDataTableAsString(0, 0, cells.MaxDataRow + 1, cells.MaxDataColumn + 1, true);

                if (dtTotal.Rows.Count > 0)
                {
                    ImportExportHandle.dataTotal = ImportExportHandle.ConvertToTable(dtTotal);
                    for (int j = 0; j < ImportExportHandle.dataTotal.Columns.Count; j++)
                    {
                        ImportExportHandle.tabTemp.Columns.Add(ImportExportHandle.dataTotal.Columns[j].ColumnName, ImportExportHandle.dataTotal.Columns[j].DataType);
                    }
                    ImportExportHandle.tabTemp.Columns.Add("体积");
                    ImportExportHandle.tabTemp.Columns.Add("总计");
                    ImportExportHandle.FindComValueTest();
                    dataGridViewX1.DataSource = ImportExportHandle.tabTemp;
                    //隔行变色
                    dataGridViewX1.RowsDefaultCellStyle.BackColor = Color.Bisque;
                    dataGridViewX1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige;
                    dataGridViewX1.AllowUserToAddRows = false;
                    MessageBox.Show("导入成功,请点击导出汇总统计结果"); 
                }
            }
        }

操作类：

 public class ImportExportHandle
    {
        public static DataTable tabTemp = new DataTable();
        public static DataRow drTemp = null;
        public static int codeid = 0;
        public static DataTable dataTotal = new DataTable();
       public static DataTable ConvertToTable(DataTable source)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("L");
            dt.Columns.Add("W");
            dt.Columns.Add("H");

            //以Item 字段为筛选条件  列转为行  下面有图
            var columns = (from x in source.Rows.Cast<DataRow>() select x[0].ToString()).Distinct();

            //把 Item 字段 做为新字段添加进去
            foreach (var item in columns) dt.Columns.Add(item).DefaultValue = 0;

            //进行Group分组提取数据
            var data = from x in source.Rows.Cast<DataRow>()
                       group x by new { ShopCode = x[0], L = x[3], W = x[4], H = x[5] } into g
                       select new
                       {
                           ShopCode = g.Key.ShopCode,
                           Items = g,
                           L = g.Key.L,
                           W = g.Key.W,
                           H = g.Key.H,
                           Vol = Convert.ToDecimal(g.Key.L) / 100 * Convert.ToDecimal(g.Key.W) / 100
                           * Convert.ToDecimal(g.Key.H) / 100 * g.Count(),
                           SumCount = g.Count()
                       };

            data.ToList().ForEach(x =>
            {
                //这里用的是一个string 数组 也可以用DataRow根据个人需要用
                string[] array = new string[dt.Columns.Count];
                array[0] = x.L.ToString(); array[1] = x.W.ToString(); array[2] = x.H.ToString();
                //  array[3] = x.Vol.ToString();   array[4] = x.SumCount.ToString(); ;
                codeid = 0;
                for (int i = 3; i < dt.Columns.Count; i++)
                {
                    array[i] = (from y in x.Items
                                where y[0].ToString().Contains(dt.Columns[i].ToString())
                                select y
              ).Count().ToString();
                    codeid++;
                }
                dt.Rows.Add(array);   //添加到table中
            });


            return dt;
        }

      public static void FindComValueTest()
       {
           if (dataTotal.Rows.Count > 0)
           {
               string row1 = dataTotal.Rows[0][2].ToString();//取表的第一行第一列
               DataRow[] drr = dataTotal.Select("H=" + row1);
               drTemp = tabTemp.NewRow();//临时新的行
               foreach (DataRow row in drr)
               {
                   drTemp[0] = row[0].ToString();
                   drTemp[1] = row[1].ToString();
                   drTemp[2] = row[2].ToString();
                   for (int i = 3; i < dataTotal.Columns.Count; i++)
                   {
                       if (i != 0)
                       {
                           if (drTemp[i].ToString().Trim() == "")
                               drTemp[i] = "0";
                           drTemp[i] = Convert.ToInt32(drTemp[i].ToString()) + Convert.ToInt32(row[i].ToString() == "" ? "0" : row[i].ToString());//合并每一列的数据
                       }
                       else
                       {
                           drTemp[i] = row[i].ToString();//添加第一列，不需要相加
                       }

                   }

                   dataTotal.Rows.Remove(row);//清除此类的行
               }

               var sumCount = drTemp.ItemArray.Skip(3).ToList().Sum(x => Convert.ToInt32(x.ToString() == "" ? "0" : x.ToString()));
               //   var column = tabTemp.Columns.Count - 5;

               drTemp[3 + codeid] = Convert.ToDecimal(drTemp[0]) / 100 * Convert.ToDecimal(drTemp[1]) / 100
                        * Convert.ToDecimal(drTemp[2]) / 100 * sumCount;

               drTemp[4 + codeid] = sumCount;

               tabTemp.Rows.Add(drTemp);//添加临时新的行数据
               FindComValueTest();
           }
       }

      
    }

参考如下：

行转列

http://www.cnblogs.com/li-peng/archive/2012/02/27/2370213.html

同一DataTable中合并相同条件的行数据

http://blog.youkuaiyun.com/jinru2560/article/details/1009920

其他代码转换

Here is the C# array object:
?
var data = new[] {
              new { Product = "Product 1", Year = 2009, Sales = 1212 },
              new { Product = "Product 2", Year = 2009, Sales = 522 },
              new { Product = "Product 1", Year = 2010, Sales = 1337 },
              new { Product = "Product 2", Year = 2011, Sales = 711 },
              new { Product = "Product 2", Year = 2012, Sales = 2245 },
              new { Product = "Product 3", Year = 2012, Sales = 1000 }
          };

On Googling, I found the following generic method in StackOverflow thread.
?
public static DataTable ToPivotTable<T, TColumn, TRow, TData>(
    this IEnumerable<T> source,
    Func<T, TColumn> columnSelector,
    Expression<Func<T, TRow>> rowSelector,
    Func<IEnumerable<T>, TData> dataSelector)
        {
            DataTable table = new DataTable();
            var rowName = ((MemberExpression)rowSelector.Body).Member.Name;
            table.Columns.Add(new DataColumn(rowName));
            var columns = source.Select(columnSelector).Distinct();
 
            foreach (var column in columns)
                table.Columns.Add(new DataColumn(column.ToString()));
 
            var rows = source.GroupBy(rowSelector.Compile())
                             .Select(rowGroup => new
                             {
                                 Key = rowGroup.Key,
                                 Values = columns.GroupJoin(
                                     rowGroup,
                                     c => c,
                                     r => columnSelector(r),
                                     (c, columnGroup) => dataSelector(columnGroup))
                             });
 
            foreach (var row in rows)
            {
                var dataRow = table.NewRow();
                var items = row.Values.Cast<object>().ToList();
                items.Insert(0, row.Key);
                dataRow.ItemArray = items.ToArray();
                table.Rows.Add(dataRow);
            }
 
            return table;
        }

You can create a static class for extension methods and put it there.
To convert Year values to columns and get Pivot DataTable:
?
var pivotTable = data.ToPivotTable(
              item => item.Year,
              item => item.Product, 
              items => items.Any() ? items.Sum(x=>x.Sales) : 0);

You will get the following output:

参考地址：

http://techbrij.com/pivot-c-array-datatable-convert-column-to-row-linq

http://stackoverflow.com/questions/17971921/how-to-convert-row-to-column-in-linq-and-sql

http://geekswithblogs.net/malisancube/archive/2011/12/05/simple-linq-pivot-to-one-row.aspx

http://www.telerik.com/blogs/transpose-or-just-rows-as-columns

https://craigwatson1962.wordpress.com/2012/03/07/linq-pivot-or-crosstab-extension-method/

http://codereview.stackexchange.com/questions/30714/faster-way-to-convert-datatable-to-list-of-class

http://www.codeproject.com/Articles/44274/Transpose-a-DataTable-using-C

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2009/04/08/how-to-convert-an-ienumerable-to-a-datatable-in-the-same-way-as-we-use-tolist-or-toarray.aspx

http://davefancher.com/2010/04/16/linq-ienumerable-to-datatable/

http://www.dotnetlearners.com/blogs/view/53/data-table-and-gridview-pivot-%28convert-rows-to-columns-and-columns-to-rows%29-in-aspnet-using-c-net.aspx

http://www.nullskull.com/q/10252757/how-to-convert-row-to-column-in-cnet-using-array-or-any-way.aspx

http://www.technologycrowds.com/2014/06/transpose-datatable-c.html

http://www.aspforums.net/Threads/973310/Rotate-DataTable-Convert-Rows-to-Columns-and-Columns-to-Rows-using-C-Net/