iT邦幫忙

2

EXCEL資料表 如何更有效更新

作業需要常常用EXCEL做價格表給下游廠商做參考 如題該如何優化
上游廠商常常每半個月(或一個月)給新資料 但數量繁多
一個廠牌大約一個EXCEL檔 其中一個EXCEL裡面會有各系列的Sheet
我想知道的事是否有類似SQL資料庫UPDATE語言的函數或方法來更新資料
附圖舉例假如本月7月價格如下
而上游廠商8月希望某幾樣商品價格如sheet2
因為下游廠商是傳統產業 目前做法是用印商品冊一本一本發
未來是想用SQL資料庫來單獨維護售價 再用python視覺化效果
針對此想聽聽各方意見
目前問題先解決EXCEL如何達成那樣的效果https://ithelp.ithome.com.tw/upload/images/20200707/20128467KXhGEQgir1.png

goodnight iT邦研究生 3 級 ‧ 2020-07-07 14:31:38 檢舉
如果你有sql + phyton計劃, 就可架一個 sql express 或是MariaDB, 先把你的總表匯入 sql server, 當然你要先建好資料表, 一個主資料表 (A), 一個更新用資料表(B), 當更新的excel來的時候, 匯入B資料來表, 然後 update A資料表, 輸出excel 時, 更新日期的就標顏色, 再轉成 PDF, 再讓下游下載就可以了 (但大部份要寫程式, 我並不是很懂), 反正你早晚都要進入 phyton + sql 的規劃, 不如就順便規劃好再作業

至於下游能不能接受電子檔方式, 要看你公司和下游的關係有多密切,
1.有哪些下游只能/堅持用紙本
2.有哪些下游希望紙本+電子檔 (pdf + excel)
3.有哪些下游可以接受電子檔 (pdf + excel)

另外 rogeryao 的方式也可以參考, 我也是用 rogeryao 提供的資料, 用 vba 寫出一個前期庫存與當期庫存差異對照表 (哪些品項增加, 哪些品項消失了, 哪些品項數量有異動)
goodnight iT邦研究生 3 級 ‧ 2020-07-07 15:05:31 檢舉
你也可以用最簡單的排序+移除重覆試試, 在 sheet2 增加一欄更新日期, 例 2020/07/07, 然後貼到 sheet 1, ,
1.排序條件: id (升冪), 調價日期 (降冪) -> 這樣最新的會排在最前面
2.移除重覆: 選擇 id , 這樣就刪除掉重覆的, 只留下最新的
3.如果想標出有異動的 id項目, 可以先在 id及price, 調價日期 欄位設定格式化條件
4.另存為新的 excel 或 pdf
5.完成後再把調價日期清空, 方便下次使用
以上請參考看看
goodnight iT邦研究生 3 級 ‧ 2020-07-07 15:48:50 檢舉
如果用 vba 的話, 建議開一個新檔, 再用新檔的 vba 去開主檔+更新檔, 再用vba 去更新主檔
1
japhenchen
iT邦研究生 1 級 ‧ 2020-07-07 08:47:18

1、讓下游廠商進你們的網站下載新的EXCEL報表,報表內容以程式產生,目前常見的網頁後端語言都有免費的EXCEL庫可用(而且不用在伺服器上安裝OFFICE),像PHP的PHPExcel,C#的Epplus,Python的Openpyxl.................
2、學證券公司用DDE

看更多先前的回應...收起先前的回應...
japhenchen iT邦研究生 1 級 ‧ 2020-07-07 09:23:26 檢舉

這是我寫的範例....網頁版九九乘法表DEMO
(資料庫的事由你自行去發揮)

http://60.249.187.158/csexcel

#nuget epplus

using OfficeOpenXml;
using System;
using System.Drawing;

public partial class csexcel_Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        Response.Clear();
        ExcelPackage ep = new ExcelPackage();
        ExcelWorksheet ews = ep.Workbook.Worksheets.Add("測試");
        ews.Cells.Style.Font.Size = 20; //預設字體大小
        ews.Cells.Style.Font.Name = "細明體";//細明體為等寬字體,這樣排起來較好看

        for (int x = 1; x <= 9; x++)
        {
            for (int y = 1; y <= 9; y++)
            {
                ews.Cells[y, x].Value = x * y;
            }
        }

        // 表格乘數著底色....黃
        ews.Cells[1, 1, 1, 9].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        ews.Cells[1, 1, 1, 9].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
        // 表格被乘數著底色....黃
        ews.Cells[1, 1, 9, 1].Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
        ews.Cells[1, 1, 9, 1].Style.Fill.BackgroundColor.SetColor(Color.Yellow);

        //網頁輸出
        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=九九乘法表" + DateTime.Now.Ticks.ToString() + ".xlsx");
        Response.BinaryWrite(respdata);
        Response.Flush();
        Response.End();
    }
}
japhenchen iT邦研究生 1 級 ‧ 2020-07-07 09:29:47 檢舉

我不愛用VBA的問題有3

1、安全性疑慮..現在的新版OFFICE會預設封鎖VBA
2、版本支援....要是有人用的是OFFICE2003,你們的卻是2013甚至更高,會有極大的機率無法執行
3、只要他們編輯巨集,你們公司的資料不就被人看光光?

dexingadm iT邦新手 5 級 ‧ 2020-07-07 09:38:39 檢舉

感謝撥空回答 事情是這樣的 您提到的對我來說是STEP2的部分
因為資料庫還是舊的資料 所以要更新資料才能產生新的EXCEL報表
STEP1是卡在 每半個月需要重複 更新500~1500筆不等的資料
且資料分散在各廠商EXCEL的各Sheet中
如何有效整合這些初始資料是目前問題
目前想法是如何用CSV來更新SQL 但各廠商給的初始資料凌亂
如果上游廠商能整合的話就方便許多
且下游廠商是真的傳統老店到要請他們上網下載資料 會到困難的地步
不過大致上有方向了 感謝

japhenchen iT邦研究生 1 級 ‧ 2020-07-07 09:39:37 檢舉

外加一個不愛VBA的原因: 效能奇差,跑個1000行要花掉幾十秒,OMG

japhenchen iT邦研究生 1 級 ‧ 2020-07-07 09:42:08 檢舉

dexterkuo
用排程跑個程式固定時間產生一份專供EXCEL用的TABLE嘛...每天跑一次或一周跑一次,再舊的資料都能整理的出來吧?

japhenchen iT邦研究生 1 級 ‧ 2020-07-07 10:59:57 檢舉

還是老話一句,如果你讓下游廠商自行用SQL更新EXCEL裡的資料,太危險!撇開1433不談,光是你把ConnectionString存在EXCEL的VBA裡,就是一件可怕的事,估且不論你們有沒有設定一個足夠小的權限給他們連,但1433暴露在外,就難以避免DDoS網路阻絕攻擊的發生.......

你想冒這險?

KYC World iT邦新手 5 級 ‧ 2020-07-07 14:25:05 檢舉

小小建議是全部都用PYTHON搞定就好,廠商只需要幫你把他們的EXCEL提供給你
流程可以是excel>phyhon >pandas >pandas.to_sql>SQL process

0
海綿寶寶
iT邦大神 1 級 ‧ 2020-07-07 09:20:39

我想知道的事是否有類似SQL資料庫UPDATE語言的函數或方法來更新資料

我的答案是「沒有」

如果是我的話
只會在價格主檔裡寫 VBA
讀上游廠商提供的更新價格
然後去更新價格主檔裡的資料

我要發表回答

立即登入回答