iT邦幫忙

1

C# EXCEL合併問題

zhi 2020-12-30 10:37:001463 瀏覽

各位大神好,我是一個未滿一年的C#後端新手目前有接到一個需求 要將二個XLSX的檔案做合併
,因為已經卡了二天工時,問資深人員都說沒處理過所以等於沒方向可以找,才上來請教眾大神們,以下為二個XLSX的格式
XLSX File1
https://ithelp.ithome.com.tw/upload/images/20201230/20089940fEnakffD1d.png
XLSX File2
https://ithelp.ithome.com.tw/upload/images/20201230/20089940qFCESGrgmy.png
希望結果
https://ithelp.ithome.com.tw/upload/images/20201230/20089940ibgE5cFhBW.png
但我用了npoi或者是free spirt.xls 或者是 Microsoft.Office.Interop.Excel最多只會變成下面的結果
https://ithelp.ithome.com.tw/upload/images/20201230/20089940362OtJDBdU.png

請問眾大神有什麼建議嗎,之前有想過把EXCEL轉成LIST,但一直想不出來 要怎麼去比對前面的時間ORZ

看更多先前的討論...收起先前的討論...
微笑 iT邦新手 3 級 ‧ 2020-12-30 10:43:13 檢舉
複製貼上不能解決這問題嗎
微笑 iT邦新手 3 級 ‧ 2020-12-30 10:44:56 檢舉
表格內看起來也沒有時間欄位...
zhi iT邦新手 5 級 ‧ 2020-12-30 10:46:08 檢舉
必須從程式碼方面處理,因為這之後會把二個XLSX打包成壓縮檔讓使用者下載,下載之後就必須是處理後的結果@@
前面100 101 102是代表年份的。
1
最佳解答

可以把 年份地區 當成鍵值,然後用程式將資料併起來

不過我看到 XLSX1 的最後一行 107 + 空白,轉換後變成 107 高雄市 這邊不是很懂 @@

程式使用 Epplus 套件。

# 程式

static void Main(string[] args)
{
    //讀取資料的函數
    IEnumerable<Hashtable> readData(string fileName, int startRowIndex = 0, int endColIndex = 0)
    {
        using (var package = new ExcelPackage(new FileInfo(fileName)))
        {
            var worksheet = package.Workbook.Worksheets.First();

            for (var i = startRowIndex; i <= worksheet.Dimension.Rows; i++)
            {
                var year = worksheet.Cells[i, 1].Text;

                //檢查年份,如果年份不對就停止讀取
                if (int.TryParse(year, out var r))
                {
                    //不想建立 Class 所以使用 Hashtable
                    var obj = new Hashtable();
                    for (var j = 1; j <= endColIndex; j++)
                    {
                        obj.Add($"Col{j}", worksheet.Cells[i, j].Text);
                    }
                    yield return obj;
                }
            }
        }
    }

    //讀取資料並轉為字典
    var file1 = readData("file1.xlsx",
        startRowIndex: 4, endColIndex: 6)
        .ToDictionary(it => new
        {
            Col1 = it["Col1"].ToString(),
            Col2 = it["Col2"].ToString()
        });

    //讀取資料並轉為字典
    var file2 = readData("file2.xlsx",
        startRowIndex: 4, endColIndex: 7)
        .ToDictionary(it => new
        {
            Col1 = it["Col1"].ToString(),
            Col2 = it["Col2"].ToString()
        });

    //產生年份對應表
    var result = file1.Concat(file2)
        //去除 Key 重複的資料
        .Select(it => it.Key).Distinct()
        //重新排序
        .OrderBy(it => it.Col1).ThenBy(it => it.Col2)
        //產生對應表,其餘欄位補空白
        .Select(it =>
        {
            var obj = new Hashtable();
            obj.Add($"Col1", it.Col1);
            obj.Add($"Col2", it.Col2);
            for (var i = 0; i < 9; i++)
                obj.Add($"Col{3 + i}", "");
            return obj;
        })
        .ToList();

    //將兩張表合併
    foreach (var item in result)
    {
        var key = new
        {
            Col1 = item["Col1"].ToString(),
            Col2 = item["Col2"].ToString()
        };
        //填入表一資料
        if (file1.TryGetValue(key, out var o1))
        {
            for (var i = 1; i <= 4; i++)
                item[$"Col{2 + i}"] = o1[$"Col{2 + i}"];
        }

        //填入表二資料
        if (file2.TryGetValue(key, out var o2))
        {
            for (var i = 1; i <= 5; i++)
                item[$"Col{6 + i}"] = o2[$"Col{2 + i}"];
        }
    }

    //匯出新檔案
    using (var package = new ExcelPackage())
    {
        var worksheet = package.Workbook.Worksheets.Add("工作表1");

        var rowIndex = 4;

        foreach (var item in result)
        {
            for (var colIndex = 1; colIndex <= 11; colIndex++)
            {
                worksheet.Cells[rowIndex, colIndex].Value = item[$"Col{colIndex}"].ToString();
            }
            rowIndex++;
        }

        package.SaveAs(new FileInfo("new.xlsx"));
    }
}

# 測試資料

https://ithelp.ithome.com.tw/upload/images/20201230/20106865IHSbtAQ106.jpg
https://ithelp.ithome.com.tw/upload/images/20201230/20106865U74OoAgMpb.jpg

# 結果

https://ithelp.ithome.com.tw/upload/images/20201230/20106865PrBngoKuJr.jpg

加上標題並調整一下樣式就可以了。

zhi iT邦新手 5 級 ‧ 2020-12-30 19:16:03 檢舉

謝謝大大的教學,我會研究你的方法再試試

1

我只能將大約的邏輯給你。

畢竟我不知道c#能用的東西及工具有哪些。期待有其它人能告訴你。

1.試著往將xls讀取後,先轉成資料變數。

2.從程式中處理好及整理成你要的格式。

3.再將整理好的資料,重新輸出成一個新的xls。

依照你的實例結果,你因該只做到將資料往下貼的動作而已。
可能只是單純的合拼作用。

畢竟你想要的結果,格式是需要改變的。不先讀取成資料變數。
很難做直接操作才對(我不確定就是了)

看更多先前的回應...收起先前的回應...
zhi iT邦新手 5 級 ‧ 2020-12-30 10:48:31 檢舉

好的,非常感謝你的回答,我再試試看,再不行只能跟PM討論要不要直接從資料庫方面下手

那個xls是資料庫生成的?

zhi iT邦新手 5 級 ‧ 2020-12-30 10:52:54 檢舉

前面會有CODE先處理生成實體的XLSX,然後我就是負責後面把這二個XLSX合併的需求,因為前面的CODE寫的很死,我也看不太董@@,寫的人也不在了....

a500197 iT邦新手 5 級 ‧ 2020-12-30 10:59:30 檢舉

那研究一下DB直接從DB拉需要的資料輸出如何?

微笑 iT邦新手 3 級 ‧ 2020-12-30 11:01:11 檢舉

或是把兩個表格放在分頁2跟分頁3,然後分頁1用函數套資料合併呢

zhi iT邦新手 5 級 ‧ 2020-12-30 11:03:43 檢舉

好的 感謝二位的回答,我都試試看

認真來說,如果真的已經有現成的從資料庫拉出來成xls的東西。
我也會建議從那邊下手會比較快。

省的你還要讀取整理然後重新輸出。

zhi iT邦新手 5 級 ‧ 2020-12-30 11:29:11 檢舉

好的,我都會嘗試,畢竟只要有人可以給我方向我都會試試看,但無論結果如何,我都有學到東西,感謝你們的回答。

0
japhenchen
iT邦大師 1 級 ‧ 2020-12-30 11:01:13

你去nuget下載epplus

然後用下列指令,即可把a.xlsx(來源)的sheet1工作表,加到b.xlsx(目的)的工作表群裡

using (ExcelPackage epA = new ExcelPackage(new System.IO.FileInfo("a.xlsx")))
{
    using (ExcelPackage epB = new ExcelPackage(new System.IO.FileInfo("b.xlsx")))
    {
        var sheetA = epA.Workbook.Worksheets["sheet1"];
        epB.Workbook.Worksheets.Add("來自a的sheet1", sheetA);
        epB.Save();
    }
}

速度超快,且免安裝OFFICE,也無需掛載任何COM或interop,我用在webserver上就不用為了匯出EXCEL檔而安裝一套OFFICE(效能也太..)

看更多先前的回應...收起先前的回應...
zhi iT邦新手 5 級 ‧ 2020-12-30 11:04:04 檢舉

好的,我馬上試試 感謝你的回答

用using嵌套就不用麻煩手動dispose()那兩個excelpackage了

epplus有個優點我更喜歡,用在web server上,讓用戶點擊"匯出"下載excel檔時,無需回寫檔案到硬碟裡,就可以直接on fly下載,省得把我的全SSD網站可憐兮兮的寫入次數給消耗殆盡

我常這樣用

byte[] respdata = ep.GetAsByteArray();
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.AddHeader("content-length", respdata.Length.ToString());
Response.AddHeader("content-disposition", "attachment; filename=" + 採購案號 + "_材料編號統計表" + Guid.NewGuid().ToString() + ".xlsx");
Response.BinaryWrite(respdata);
Response.Flush();
Response.End();

快的原因也要拜office 2007之後採用open document之賜,任何能操作xml的語言,都能直接讀寫office的檔案,其實xlsx只是個zip壓縮檔,你用解壓縮工具對xlsx檔下解壓縮的動作,就會看到有一堆xml檔的資料出現,跟以前動不動就要安裝OFFICE才能操作DOC、XLS的世界,不可同日而語

zhi iT邦新手 5 級 ‧ 2020-12-30 11:28:05 檢舉

好的 感謝你的教學~,我有試了你的這個方法,但試的結果只是把XLSX2的工作表單純插入到xlsx1 中的工作表後面,雖然不符合我結果,但我也學了一招,很感謝你的教學。

如果你要複制大量的工作表,一樣比照辦理,因為從你的題目裡真的看不清楚來源跟目的間,有多大的關連和目標

如果你要合併a.xlsx跟b.xlsx

using (ExcelPackage epA = new ExcelPackage(new System.IO.FileInfo("a.xlsx")))
{
    using (ExcelPackage epB = new ExcelPackage(new System.IO.FileInfo("b.xlsx")))
    {
        foreach(var xs in epA.Workbook.Worksheets)
        {
            epB.Workbook.Worksheets.Add("a_"+xs.Name, xs);
            // a_ 的目的只是避免工作表名稱重覆而已,你自己可改慣用命名方法
        }
        epB.Save("c.xlsx"); 
        //合併後另存成c.xlsx,a跟b.xlsx都不動原稿
    }
}

突然看懂你的問題.........

超簡單

var 來源工作表=epA.Workbook.Worksheets[0];
var 目的工作表=epB.workbook.Worksheets[0];
int lastRow = 目的工作表.Dimantion.Rows + 3; //空3行
for(int i = 1 ;i<=來源工作表.Dimantion.Rows;i++){
    目的工作表.Rows(lastRow+i)=來源工作表.Rows(i);
}
epB.Save("C.xlsx");

打完,收工下班

zhi iT邦新手 5 級 ‧ 2020-12-30 19:26:09 檢舉

謝謝大大一直分享教學,我都會研究試試看的,非常感謝

0
powerc
iT邦新手 4 級 ‧ 2020-12-30 15:03:37

個人目前在使用的是OpenXmlSdk套件,一樣可以在nuget下載(名稱DocumentFormat.OpenXml),
要合併的話其實可以先讀檔,在重新寫一份新的檔案,不過這樣做其實會有記憶體資源的問題(如果資料越多的話記憶體占用也會增加),
中間你要做任何處理你自行決定,
不過這個讀excel檔會有遇到中文需要從ShareStringTable取值的問題(寫個方法解決即可)

zhi iT邦新手 5 級 ‧ 2020-12-30 19:17:17 檢舉

好的 謝謝大大分享,我會研究看看目前所接觸的套件真的不多

我要發表回答

立即登入回答