[已關閉]EXCEL資料表如何更有效更新

excel sql

不明 2020-07-07 08:25:54 ‧ 2364 瀏覽

分享至

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

goodnight iT邦研究生 2 級 ‧ 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邦研究生 2 級 ‧ 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邦研究生 2 級 ‧ 2020-07-07 15:48:50 檢舉

如果用 vba 的話, 建議開一個新檔, 再用新檔的 vba 去開主檔+更新檔, 再用vba 去更新主檔

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

3 個回答

japhenchen

iT邦超人 1 級 ‧ 2020-07-07 08:47:18

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

回應 7
分享
檢舉

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

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、只要他們編輯巨集，你們公司的資料不就被人看光光？

不明檢舉

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