iT邦幫忙

0

C# Excel 處理問題

之前有發問知道怎麼做合併,只是現在有一個我比較不知道如何多處理。!https://ithelp.ithome.com.tw/upload/images/20210428/20136928DqdLr5XG2B.png
https://ithelp.ithome.com.tw/upload/images/20210428/201369284K3TyKiULl.png

現在有兩個表,會以第一個表為主。
我以顏色區分比較清楚

"A表黃色" 為主,抓 "B表的黃色"以及其他需要的欄位

需要的欄位
兩張表共通點只有 黃色部分
A表的黃色 藍色 紅色
B表的黃色 綠色 紅色

紅色部分為,兩張表的黃色相同,他們的紅色會做相加

要將這些合成一個表,只要黃色相同,其他欄位名稱相同的會變成一個(加起來)。

合成最終的表,欄位名稱又要改變,因為三張表欄位都不一樣。下面是為產出來的示意圖
https://ithelp.ithome.com.tw/upload/images/20210428/20136928Y7aLlCzohZ.png

我的想法是先將需要的欄位

先將A表需要的欄位,加到LIST1
再將B表需要的欄位,加到LIST2
以A表的黃色(欄位名稱)與B表黃色(欄位名稱)做篩選,然後存到 LIST3,再來將黃色(欄位名稱)來做合併,連同其他的需要相加的欄位一起處理,最後再輸出到 Excel 做成新的檔案。

目前只會先分別將 A表B表,分別加到LIST,可是還不知道如何固定用A表篩選,以及後續的處理。

下面是我將一個表加到LIST的程式碼的範例

           var dataOrigin = new List<DataOrigin>();               
            for(int r = 1; r <= totalRows; r++ )
            {
                dataOrigin.Add(new DataOrigin 
                { item = sh.Cells[r, 1].Value.ToString(), 
                    Apr = sh.Cells[r, 2].Value.ToString(),
                    May = sh.Cells[r, 3].Value.ToString(),
                    Jun = sh.Cells[r, 4].Value.ToString()
                });
            }
1
japhenchen
iT邦大師 1 級 ‧ 2021-04-29 10:39:26
最佳解答

延續使用epplus的ExcelPackage,就不用另存另一個xlsx檔
全部使用List來Group及取Sum

epplus有花式 .Cells[x,y].Formula = "=sum(A1:A11)";

我把變數用中文命名,Visualstudio2015之後允許使用UTF-8格式中文命名變數,雖然有人不建議如此,但我用好多年不曾出問題

變數中文命名最大的問題就是....只有華人看的懂,不利於全球交流

private void goExcel2()
        {
            using (ExcelPackage 你的Excel = new ExcelPackage(new FileInfo(@"s:\test\test.xlsx")))
            {
                var 工作表1 = 你的Excel.Workbook.Worksheets["工作表1"];
                var 工作表2 = 你的Excel.Workbook.Worksheets["工作表2"];
                var 工作表1資料 = new List<List<string>>();
                var 工作表2資料 = new List<List<string>>();
                for (int r = 2; r <= 工作表1.Dimension.Rows; r++)
                {
                    var 這個資料行 = new List<string>();
                    for (int c = 1; c <= 工作表1.Dimension.Columns; c++)
                    {
                        這個資料行.Add(GetStringValue(工作表1.Cells[r, c].Value));
                    }
                    工作表1資料.Add(這個資料行);
                }

                for (int r = 2; r <= 工作表2.Dimension.Rows; r++)
                {
                    var 這個資料行 = new List<string>();
                    for (int c = 1; c <= 工作表2.Dimension.Columns; c++)
                    {
                        這個資料行.Add(GetStringValue(工作表2.Cells[r, c].Value));
                    }
                    工作表2資料.Add(這個資料行);
                }
                var 結果表 = 你的Excel.Workbook.Worksheets["輸出結果"] != null
                    ? 你的Excel.Workbook.Worksheets["輸出結果"]
                    : 你的Excel.Workbook.Worksheets.Add("輸出結果");
                結果表.Cells.Clear();
                結果表.Cells[1,1].LoadFromText("Model,part,ITEM,APR,MAY,JUN,加總");
                int R = 1;
                foreach (var i in 工作表1資料.GroupBy(g => g[2])) // 以第一個工作表的第3行: im做群組
                {
                    var 工作表2關聯 = 工作表2資料.Where(p => p[0].Trim() == i.Key);
                    結果表.Cells[R + 1, 1].Value = R.ToString(); // model
                    結果表.Cells[R + 1, 2].Value = i.First()[1]; // part
                    結果表.Cells[R + 1, 3].Value = i.First()[2]; // item
                    if (工作表2關聯.Any())
                    {
                        結果表.Cells[R + 1, 4].Value = 工作表2關聯.Sum(s => StrToInt(s[3])); // April 
                        結果表.Cells[R + 1, 5].Value = 工作表2關聯.Sum(s => StrToInt(s[6])); // May 
                        結果表.Cells[R + 1, 6].Value = 工作表2關聯.Sum(s => StrToInt(s[9])); // Jun 
                        結果表.Cells[R + 1, 7].Formula = $"=sum({cellID(R + 1, 4)}:{cellID(R + 1, 6)})"; // 就是 =sum(D4:F4) 
                    }
                    R++;
                }
                你的Excel.Save();
            }
        }
        private Int32 StrToInt(string s)
        {
            Int32 ret = 0;
            if (!int.TryParse(s, out ret))
            {
                ret = 0;
            }
            return ret;
        }
        private static string cellID(int r, int c)
        {
            string ret = "";
            if (c > 26) ret = Convert.ToChar((int)Math.Truncate((decimal)c / 26) + 64).ToString();
            ret += Convert.ToChar((c % 26) + 64).ToString() + r.ToString();
            return ret;
        }

        private string GetStringValue(object v)
        {
            return v != null ? v.ToString() : ""; 
        }

工作表1
https://ithelp.ithome.com.tw/upload/images/20210429/20117954HrKnkBCBCa.jpg

工作表2
https://ithelp.ithome.com.tw/upload/images/20210429/20117954IFvmr5GdTJ.jpg

工作表3
https://ithelp.ithome.com.tw/upload/images/20210429/20117954nrYYBl4C9C.jpg

看更多先前的回應...收起先前的回應...

以後麻煩一下,提問題也把用到的素材檔放上Google雲或其他地方,放下載連結上來,不然我們只能看圖說故事,或是把圖片上的資料1比1的自己去輸入到EXCEL,太殘忍了吧?

好的,我不知道要這樣,謝謝你的建議。

您好,我大致上都了解怎麼做了,可是從這裡開始
private Int32 StrToInt(string s)
我就看不懂後面是要做甚麼了。

private Int32 StrToInt(string s)只是我用來字串轉數值的函數啦,你也可以用系統內建的Convert.ToInt32(),不過這個內建函數不會處理空字串或非數字字串的狀況,只能自己用TryParse去做Try Exception,只是如此

至於CellID ,因為EXCEL的內建函數只能儲存格的定位A5、B8、C15...做處理,所以我也只能把c#的for的自動累加數值轉成ABCDE....數字轉英文字母(甚至是AA1、AA2...)需要一點花招,這個DEMO就一併送大家了

您好,我參考您給的方法,可是我是寫在,namespace的Main,後面的Private出現下面這個錯誤,我加了Class也是會出現錯誤,這個該如何解決?

https://ithelp.ithome.com.tw/upload/images/20210503/20136928z6VeJVDCJZ.png

我忘了把CLASS弄上來了

在專案裡新增一個類別,把我寫的class複製貼進去
https://ithelp.ithome.com.tw/upload/images/20210503/20117954Mb6KyA0LNm.jpg

using System.Collections.Generic;

namespace myprogram
{
    class ManyKeysAndValue
    {
        public List<object> keys { get; set; }
        public object value { get; set; }
        public ManyKeysAndValue(object[] _keys, object v)
        {
            keys = new List<object>();
            keys.AddRange(_keys);
            value = v;
        }
    }
}

您好,我還是沒辦法使用

   private string GetStringValue(object v)
    {
        return v != null ? v.ToString() : "";
    }
 

這些指令,是我放錯地方了嗎?我沒有完全複製,我提供我目前的程式碼

using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using OfficeOpenXml;

namespace ConsoleApp2
{
private string GetStringValue(object v)
{
return v != null ? v.ToString() : "";
}
static void Main(string[] args)
{

    using (ExcelPackage IOSformat = new ExcelPackage(new FileInfo(@"s:\test\test.xlsx")))
    {
        var IOSFormat = IOSformat.Workbook.Worksheets[1];
        var IOSFormatList = new List<List<string>>();
        for (int r = 2; r <= IOSFormat.Dimension.Rows; r++)
        {
            var IOSrows = new List<string>();
            for (int c = 1; c <= IOSFormat.Dimension.Columns; c++)
            {
                IOSrows.Add(GetStringValue(IOSFormat.Cells[r, c]));
            }
            IOSFormatList.Add(IOSrows);
        }
    }
}

}

我已經有增加類別了,還是是因為我是使用

 static void Main(string[] args)

不好意思,以解決,我外圍沒有加上class括號應該連Main一起加進去

我一直對您給的答案很有疑問,你全部都丟到LIST,到底哪一段指令有指定我要哪些欄位啊?

LIST只是偷懶,把EXCEL的所有儲存格都當OBJECT(未定變數型態)來處置

我在程式裡有寫到

foreach (var i in 工作表1資料.GroupBy(g => g[2])) 

就是擺明以你的EXCEL 工作表1裡的第三行做加總關鍵(A為[0],B為1、C為2)

那我問另一個問題,您叫我把您的類別都加進去,可是我怎麼沒有看到類別有被用到?

https://ithelp.ithome.com.tw/upload/images/20210504/20136928gzB90pfLWp.png

而且不知道為什麼並沒有轉過去

附上修改的程式碼

  using System;
  using System.Collections.Generic;
  using System.IO;
  using System.Linq;
  using OfficeOpenXml;




  namespace ConsoleApp2
  {
      class Program
{
    private static string GetStringValue(object v)
    {
        return v != null ? v.ToString() : "";
    }
    static private Int32 StrToInt(string s)
    {
        Int32 ret = 0;
        if (!int.TryParse(s, out ret))
        {
            ret = 0;
        }
        return ret;
    }
    private static string cellID(int r, int c)
    {
        string ret = "";
        if (c > 26) ret = Convert.ToChar((int)Math.Truncate((decimal)c / 26) + 64).ToString();
        ret += Convert.ToChar((c % 26) + 64).ToString() + r.ToString();
        return ret;
    }
    static void Main(string[] args)
    {
        //先認證
        ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
        //來源 IOSformat
        using (ExcelPackage IOSformat = new ExcelPackage(new FileInfo(@"D:\test\List1.xlsx")))
        {

            //選擇工作表
            var IOSFormat = IOSformat.Workbook.Worksheets[0];
            //使用二維LIST,欄的LIST
            var IOSFormatList = new List<List<string>>();
            //選擇行,以及行的最大
            for (int r = 2; r <= IOSFormat.Dimension.Rows; r++)
            {
                //行的LIST
                var IOSrows = new List<string>();
                for (int c = 1; c <= IOSFormat.Dimension.Columns; c++)
                {
                    IOSrows.Add(GetStringValue(IOSFormat.Cells[r, c]));
                }
                IOSFormatList.Add(IOSrows);
            }
            using (ExcelPackage ME = new ExcelPackage(new FileInfo(@"D:\test\List2.xlsx")))
            {
                var me = ME.Workbook.Worksheets[0];

                //創建meList
                var meList = new List<List<string>>();
                for (int r = 2; r <= me.Dimension.Rows; r++)
                {
                    var merows = new List<string>();
                    for (int c = 1; c <= me.Dimension.Columns; c++)
                    {
                        merows.Add(GetStringValue(me.Cells[r, c]));
                    }
                    meList.Add(merows);
                }
                //新建summary by part在iosformat
                IOSformat.Workbook.Worksheets.Add("Summary-by part(1)");

                //判定有沒有null,以及清空所有字串
                var summary = IOSformat.Workbook.Worksheets["Summary-by part(1)"] != null ? IOSformat.Workbook.Worksheets["Summary-by part(1)"] : IOSformat.Workbook.Worksheets.Add("Summary-by part(1)");
                summary.Cells.Clear();

                //在summary工作表新增標題
                summary.Cells[1, 1].LoadFromText("Model,part,ITEM,APR,MAY,JUN,加總");


                int R = 1;
                foreach (var i in IOSFormatList.GroupBy(g => g[2]))
                {
                    var summaryOutput = meList.Where(p => p[0].Trim() == i.Key);

                    summary.Cells[R + 1, 1].Value = R.ToString();
                    summary.Cells[R + 1, 2].Value = i.First()[1];
                    summary.Cells[R + 1, 3].Value = i.First()[2];

                    if(summaryOutput.Any())
                    {
                        summary.Cells[R + 1, 4].Value = summaryOutput.Sum(s => StrToInt(s[3]));
                        summary.Cells[R + 1, 5].Value = summaryOutput.Sum(s => StrToInt(s[6]));
                        summary.Cells[R + 1, 6].Value = summaryOutput.Sum(s => StrToInt(s[9]));
                        summary.Cells[R + 1, 7].Formula = $"=sum({cellID(R + 1, 4)}:{cellID(R + 1, 6)})";

                    }
                    R++;
                }
                IOSformat.Save();
            }
        }
    }
}

}

應該是我誤解你的題意,所以我用了

summary.Cells[R + 1, 1].Value = R.ToString();

我直接用流水號,而不是MODEL編號

那你的MODEL是存在工作表1還是表2內?
因為我在你給的問題裡沒有發現MODEL的資料,所以不知從何而來https://ithelp.ithome.com.tw/upload/images/20210504/20117954g4kFGM53Rz.jpg

Model 是表一的Project,因為名稱都會不同,我自己製作時也很困擾

我有發新的文章,那個是我目前正在製作的,也有提供素材,您可以參考參考

0
海綿寶寶
iT邦大神 1 級 ‧ 2021-04-28 16:35:22

1.第一列的加總應該是 3,100
2.前一題如果解決了,就去選個最佳解
3.只須要用同一個 List 去讀 A 表和 B 表,像這樣(沒有值的欄位就填 0)

dataOrigin.Add(new DataOrigin 
                { 
                    Model = sh.Cells[r, 1].Value.ToString(), 
                    Part = sh.Cells[r, 2].Value.ToString(), 
                    item = sh.Cells[r, 3].Value.ToString(), 
                    Apr = sh.Cells[r, 4].Value.ToString(),
                    May = sh.Cells[r, 5].Value.ToString(),
                    Jun = sh.Cells[r, 6].Value.ToString(),
                    Sum = sh.Cells[r, 7].Value.ToString()                   
                });

你好,因為拿到的資料,無值全部都是空格,所以這也是我有點難搞的地方,因為資料幾百行,總不可能在幫他填值吧

0
a9864277
iT邦新手 5 級 ‧ 2021-04-29 11:52:04

你把List名稱改成你的試試看

            var queryTableA = from a in tableAList
                         group a by new { a.Project, a.Part, a.Im } into av
                         select new
                         {
                             av.Key.Project,
                             av.Key.Part,
                             av.Key.Im,
                             VM = av.Sum(x => x.VM)
                         };

            var result = (from a in queryTableA
                         join b in tableBList on a.Im equals b.Item
                         group b by new { a.Im, a.Project, a.Part, a.VM} into av
                         select new
                         {
                             Model = av.Key.Project,
                             av.Key.Part,
                             Item = av.Key.Im,
                             APR = av.Sum(x => x.Apr),
                             MAY = av.Sum(x => x.May),
                             JUN = av.Sum(x => x.Jun),
                             加總 = av.Sum(x => x.MD) + av.Key.VM
                         }).ToList();

https://ithelp.ithome.com.tw/upload/images/20210429/20135969SgXOPdR3Df.png

https://ithelp.ithome.com.tw/upload/images/20210503/20136928Ip6IJUiZly.png

您好,我之前也用過您給的方式,卡在這邊

a9864277 iT邦新手 5 級 ‧ 2021-05-04 14:05:06 檢舉

這是因為你的User這個Model的VM類別為string

public class User
    {
        //你目前的VM為string類別
        public string VM { get; set; }

        //改成int
        public int VM { get; set; }

    }

我看你的資料某些欄位都是數字型態,就把那些欄位都設成int了
而我看你原本是都設string
在取得資料時你也要改成int

//原本
Apr = sh.Cells[r, 2].Value.ToString(),
//改成
Apr = sh.Cells[r, 2].Value == NULL ? 0 : int.Parse(sh.Cells[r, 2].Value.ToString()),

我要發表回答

立即登入回答