From mboxrd@z Thu Jan 1 00:00:00 1970 Path: news.gmane.io!.POSTED.blaine.gmane.org!not-for-mail From: =?windows-1258?Q?Vincent_Bela=EFche?= Newsgroups: gmane.emacs.devel Subject: RE: using ses programatically (was: a ses question) Date: Sun, 5 Nov 2023 10:25:34 +0000 Message-ID: References: Mime-Version: 1.0 Content-Type: multipart/alternative; boundary="_000_PAXP192MB1608FA33568A719048AE07B184ABAPAXP192MB1608EURP_" Injection-Info: ciao.gmane.io; posting-host="blaine.gmane.org:116.202.254.214"; logging-data="6597"; mail-complaints-to="usenet@ciao.gmane.io" Cc: emacs-devel To: =?windows-1258?Q?andr=E9s_ram=EDrez?= Original-X-From: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Sun Nov 05 13:42:25 2023 Return-path: Envelope-to: ged-emacs-devel@m.gmane-mx.org Original-Received: from lists.gnu.org ([209.51.188.17]) by ciao.gmane.io with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1qzcSN-0001Wi-Sb for ged-emacs-devel@m.gmane-mx.org; Sun, 05 Nov 2023 13:42:24 +0100 Original-Received: from localhost ([::1] helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1qzcRV-0007j9-6A; Sun, 05 Nov 2023 07:41:29 -0500 Original-Received: from eggs.gnu.org ([2001:470:142:3::10]) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qzaOx-00034C-Kx for emacs-devel@gnu.org; Sun, 05 Nov 2023 05:30:43 -0500 Original-Received: from mail-db3eur04olkn0807.outbound.protection.outlook.com ([2a01:111:f400:fe0c::807] helo=EUR04-DB3-obe.outbound.protection.outlook.com) by eggs.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1qzaOu-0005Do-Gj for emacs-devel@gnu.org; Sun, 05 Nov 2023 05:30:42 -0500 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=QUNSIHjdJ3IDnUm692D6o/Q1gGeC0YzEFG96O47hxleIeI8co6ykDvnekC1amm2cwwY2fwlnZpscBDb7mpsMjATOQY9EXL7RixgnBkO35C9tw3drJfw8Dth0m8yrRnG4IAR2+Ks4uYis68hDDNcmmKqvIe6ROOsW2fWTCO9Ebhz79vfaC7u5IqfSwenFXG8Drb641JL8fQJj0gvvyyQ27pKTIqKTbKst4dL32tnq21dViftRYMfNohVLweYWeYSYyWmxMJztE17OaFGtOdEnjB54ujIeATSAJFP7gSIWonixVdCNRPK5u1aYlU7Th7NUMy6i3oh8Ey5Q0gtvYXIe3w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=Y2qcYHRwkIrp3ft13om50zLhAovxG9bmO8biKgKQld0=; b=S/efbgwBO2+bZQjpt2PcKSo0Xj9RiBUH0nSdaS82YQBJqOb7qzW+dV7TabMV+YYjPRJPgbwtmwlsCzvp3D9lVI9w8BGi2TYG5Qd9c9C9B9h670j7CTzamhjaWjGmU9lzN68oKnpHRCDKZXMnAPqeVmoNUrct+l8eubvUugiKia91+Ro2aLP0BUQhYThVfOFgj4h7AkZQ+0pYh0I1zST/2/BNSDAqqaMKoCjWlLsN7u7YC03UZPPS2jG4Pk6uT2FM+Q8Wwa6q9BMDr938xOAnH3jduTAquJXTFYhUy4MxHr4JvlEe5M3Gw4/D4L8K+z05tEW5A8t3BHk4txgk2kxnJQ== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none Original-Received: from PAXP192MB1608.EURP192.PROD.OUTLOOK.COM (2603:10a6:102:28c::22) by VE1P192MB0814.EURP192.PROD.OUTLOOK.COM (2603:10a6:800:163::9) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.6954.21; Sun, 5 Nov 2023 10:25:34 +0000 Original-Received: from PAXP192MB1608.EURP192.PROD.OUTLOOK.COM ([fe80::a0e9:c28e:ff43:2125]) by PAXP192MB1608.EURP192.PROD.OUTLOOK.COM ([fe80::a0e9:c28e:ff43:2125%6]) with mapi id 15.20.6954.025; Sun, 5 Nov 2023 10:25:34 +0000 Thread-Topic: using ses programatically (was: a ses question) Thread-Index: AQHaDyaIFxl+uTQAXkSovHGVyH2ZGrBrhKoN In-Reply-To: Accept-Language: fr-FR, en-US Content-Language: fr-FR x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [g2f0gqzvBc7KtsnY+13cII3CG5JXBpyW] x-ms-publictraffictype: Email x-ms-traffictypediagnostic: PAXP192MB1608:EE_|VE1P192MB0814:EE_ x-ms-office365-filtering-correlation-id: 1d1c10a7-8256-4d38-4cc7-08dbdde98b9a x-microsoft-antispam: BCL:0; x-microsoft-antispam-message-info: DbnxQX2FLSa52ifZd6Igfabfb8Mxi3Uanetl72Hqyjm/SaqM0X7slyggj4UFoJosAGCOqzSLkWodEyrWXi4YPjd3DSxhotQT835+KgiIcLGUph1z/h1WHIm/DZSdtyt4UfWPm8uutFDxa81dv9gl1ylI0Txe+Kj1jKVC1VlFlDUFHTyYqUyQNDqmtGpQBSSxZBgO4SZSH2mH1Sxcdg8oZbwPNZwHAJ9SxicDAzzmXp48m9zIQIPdqlfXob6lnpMIcsgeBRRJRn8/nOMlgiJE0E3hUSQwP145eUEJ2TFGlmRaz2h/6BhFu2CVsf0MvaYhtj2xQBaKL2mGdwOhbMUXDDNnh8mfMp37lPjxx03fMG4YQ/DPwH38c46QuetB+8edGUOVWNT7u8URxM0t+9Qhb4kIL/DzHrji2L2IXR+stTOdLFTshuPhU9wsfMf3eqtIad+upnWuDVXadDULk6QzybOkKLdJI4hqZG1QXz4KHDHEpNkpdsx4modrg61SREfUdGKmRCnBwj6Q2fM+8XxdMz2qvbnqyAUrOL4QP7b8FbMJCGTcD5lWwpksFkY3ZIVbL2NxD1XGvwULS9OUHVpi72pmWKrni0PKLRu6iq+/bVhqXGJ9AbacJpRfJgXsxUbBzvfqtrbVr2rPdBaoi9YSOdmTc0kU3rxsOWP16e5LpjO8eYp0SufhsEUMSXaY/dRZP3h7uN5XfoKuf3crqnolG8iWViEjbM3cchUZMKWDsh6ZKN/bagERmRfzwGgVh tWE8GYLpsIQKzRMGrPijacKRllmJJGwQoJ/VMBmpgjwWmU= x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?windows-1258?Q?KJS8y+uKJt19xFvcl2HSos+ah/salg6H2mnv+SRSh7k6VNMcmLfZLq4s?= =?windows-1258?Q?tUpL5xeowlGs9GTS6XJXPaUpKaX8DAvsiolzocvm99RW/LlYG22eshHT?= =?windows-1258?Q?BuVSVaogKGywfJHlN2CvVzzyZQ6qNW+b2XVQ9a9nOvffw0wheHEzQbHX?= =?windows-1258?Q?rjETFZRLsmJKqZD8pU4Ypzyruq26xUO3msdQGbd+L4fTmFIeAtcEbLPx?= =?windows-1258?Q?A7KwG9R0LrNbF9SmbBXGy7YIX0f4cDkiuib9Jha0w4+DchOCKj50oQ7k?= =?windows-1258?Q?vCMM9COE0S+pcjdx/nh/5tLDgpoajjNe9mCyE6vNOhPJR5drIXbUadWf?= =?windows-1258?Q?tOo3Ue0KxVe6GMNE1w6HhW3Uq9hdWrjqoVi+0YysQAt68NQMbOWOHwiU?= =?windows-1258?Q?sPXRKzquJLPURLTTrn44BFpQam9WSsOHafngBy/LcKEFW5/cRrc8+K1Z?= =?windows-1258?Q?CIYZdOaECOGZ5SV0jFh+n8tLNr3+duIZWHPkS6N902aZoSumfYsXCd2W?= =?windows-1258?Q?zS5ZooVwHjvYRhZEWOf+Z64sjoT006MI+/cKo71bdKp3qb7NeIfVaqWu?= =?windows-1258?Q?nt+7laEtkHozgWQARF8TXdRcEgEurJ/ojTLHnl+wVqTYSTB7TWX5+9dl?= =?windows-1258? X-OriginatorOrg: sct-15-20-4755-11-msonline-outlook-fb43a.templateTenant X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: PAXP192MB1608.EURP192.PROD.OUTLOOK.COM X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 1d1c10a7-8256-4d38-4cc7-08dbdde98b9a X-MS-Exchange-CrossTenant-originalarrivaltime: 05 Nov 2023 10:25:34.1907 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: VE1P192MB0814 Received-SPF: pass client-ip=2a01:111:f400:fe0c::807; envelope-from=vincent.b.1@hotmail.fr; helo=EUR04-DB3-obe.outbound.protection.outlook.com X-Spam_score_int: -15 X-Spam_score: -1.6 X-Spam_bar: - X-Spam_report: (-1.6 / 5.0 requ) BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=0.25, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001, T_REMOTE_IMAGE=0.01, T_SCC_BODY_TEXT_LINE=-0.01, URIBL_SBL_A=0.1 autolearn=no autolearn_force=no X-Spam_action: no action X-Mailman-Approved-At: Sun, 05 Nov 2023 07:41:28 -0500 X-BeenThere: emacs-devel@gnu.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: "Emacs development discussions." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Original-Sender: emacs-devel-bounces+ged-emacs-devel=m.gmane-mx.org@gnu.org Xref: news.gmane.io gmane.emacs.devel:312243 Archived-At: --_000_PAXP192MB1608FA33568A719048AE07B184ABAPAXP192MB1608EURP_ Content-Type: text/plain; charset="windows-1258" Content-Transfer-Encoding: quoted-printable Dear Andr=E8s, I drafted a macro ses-setq to allow access a SES spreadsheet programaticall= y, and I updated your code to use it. This allowed to remove all the workar= ounds. Please note that ses-setq is just a draft, far from complete. I inte= nd to add some switches to allow access the cells eaither by cell name or b= y row col, and to modify either value or formula. [https://res.cdn.office.net/assets/mail/file-icon/png/generic_16x16.png]and= re=CCs.el [https://res.cdn.office.net/assets/mail/file-icon/png/generic_16x16.png]ses= .el PS: I need to improve the macro, notable setting a cell value should also a= ffect its formula if any. ________________________________ De : andr=E9s ram=EDrez Envoy=E9 : samedi 4 novembre 2023 14:55 =C0 : Vincent Bela=EFche Objet : using ses programatically (was: a ses question) Hi Vincent. Thanks again for fixing the ses-range macro. After applying that change, which btw fixed one of the workarounds in the elisp code we have been improving on this thread. The other workaround is the use of the yank function. When I apply this patch to the code: --8<---------------cut here---------------start------------->8--- diff -u /tmp/smeter.el /tmp/smeter_wished.el --- /tmp/smeter.el 2023-11-04 13:24:42.030386021 +0000 +++ /tmp/smeter_wished.el 2023-11-04 13:26:25.128947532 +0000 @@ -15,7 +15,7 @@ (setq beg (point)) (forward-line 4) (setq prevmeasu (split-string (buf= fer-substring-no-properties beg (point)) "\n")) (setq beg (point)) (setq waterbill (buffer-substring-no-properties beg= (line-end-position))) (forward-line 1) - (setq beg (point)) (forward-line 4) (setq measu (buffer-substring-no-p= roperties beg (point))) + (setq beg (point)) (forward-line 4) (setq measu (split-string (buffer-= substring-no-properties beg (point)) "\n")) (setq beg (point)) (setq myyyyymm (buffer-substring-no-properties beg = (line-end-position))) (if (not (file-readable-p workbook-filename)) (message (format "file not found: '%s'" workbook-filename)) @@ -25,11 +25,11 @@ (ses-set-cell 3 1 'value (string-to-number (nth 1 prevmeasu))) (ses-set-cell 4 1 'value (string-to-number (nth 2 prevmeasu))) (ses-set-cell 5 1 'value (string-to-number (nth 3 prevmeasu))) ;;; B= 6 - (ses-jump-safe "C3") ;;; paste 4 cells - (kill-new measu) - (yank) + (ses-set-cell 2 2 'value (string-to-number (nth 0 measu))) ;;; C3 + (ses-set-cell 3 2 'value (string-to-number (nth 1 measu))) + (ses-set-cell 4 2 'value (string-to-number (nth 2 measu))) + (ses-set-cell 5 2 'value (string-to-number (nth 3 measu))) ;;; C6 (let ((range (ses-range E3 E6))) (ses-recalculate-cell)) - (ses-write-cells) ;;; '*1' formatting to vector also the range is the computation-resu= lt (let ((range (ses-range N3 N6 *1))) Diff finished. Sat Nov 4 13:51:04 2023 --8<---------------cut here---------------end--------------->8--- The computation result is not the expected one (compared to the one that uses yank). So it seems something else is needed. Any ideas why that patch does not do the right thing? Best Regards Andr=E9s Ram=EDrez --_000_PAXP192MB1608FA33568A719048AE07B184ABAPAXP192MB1608EURP_ Content-Type: text/html; charset="windows-1258" Content-Transfer-Encoding: quoted-printable
Dear Andr=E8s,

I drafted a macro ses-setq to allow access a SES spreadshe= et programatically, and I updated your code to use it. This allowed to remo= ve all the workarounds. Please note that ses-setq is just a draft, far from= complete. I intend to add some switches to allow access the cells eaither by cell name or by row col, and to modif= y either value or formula.


3D""andre=CCs.el
3D""ses.el

PS: I need to improve the macro, notable setting a cell value should also a= ffect its formula if any. 
De : andr=E9s ram=EDrez <= ;rrandresf@hotmail.com>
Envoy=E9 : samedi 4 novembre 2023 14:55
=C0 : Vincent Bela=EFche <vincent.b.1@hotmail.fr>
Objet : using ses programatically (was: a ses question)
 
Hi Vincent.

Thanks again for fixing the ses-range macro.
After applying that change, which btw fixed one of the workarounds in
the elisp code we have been improving on this thread.

The other workaround is the use of the yank function.

When I apply this patch to the code:
--8<---------------cut here---------------start------------->8---
diff -u /tmp/smeter.el /tmp/smeter_wished.el
--- /tmp/smeter.el      2023-11-04 13:24:42.030386= 021 +0000
+++ /tmp/smeter_wished.el       2023-11-04 13= :26:25.128947532 +0000
@@ -15,7 +15,7 @@
     (setq beg (point)) (forward-line 4) (setq prevmeas= u (split-string (buffer-substring-no-properties beg (point)) "\n"= ))
     (setq beg (point)) (setq waterbill (buffer-substri= ng-no-properties beg (line-end-position)))
     (forward-line 1)
-    (setq beg (point)) (forward-line 4) (setq measu (buffer= -substring-no-properties beg (point)))
+    (setq beg (point)) (forward-line 4) (setq measu (split-= string (buffer-substring-no-properties beg (point)) "\n"))
     (setq beg (point)) (setq myyyyymm (buffer-substrin= g-no-properties beg (line-end-position)))
     (if (not (file-readable-p workbook-filename))
         (message (format "fil= e not found: '%s'"  workbook-filename))
@@ -25,11 +25,11 @@
       (ses-set-cell 3 1 'value (string-to-nu= mber (nth 1 prevmeasu)))
       (ses-set-cell 4 1 'value (string-to-nu= mber (nth 2 prevmeasu)))
       (ses-set-cell 5 1 'value (string-to-nu= mber (nth 3 prevmeasu))) ;;; B6
-      (ses-jump-safe "C3") ;;; paste 4 = cells
-      (kill-new measu)
-      (yank)
+      (ses-set-cell 2 2 'value (string-to-number = (nth 0 measu))) ;;; C3
+      (ses-set-cell 3 2 'value (string-to-number = (nth 1 measu)))
+      (ses-set-cell 4 2 'value (string-to-number = (nth 2 measu)))
+      (ses-set-cell 5 2 'value (string-to-number = (nth 3 measu))) ;;; C6
       (let ((range (ses-range E3 E6))) (ses-= recalculate-cell))
-
       (ses-write-cells)
       ;;; '*1' formatting to vector also the= range is the computation-result
       (let ((range  (ses-range N3 N6 *1= )))

Diff finished.  Sat Nov  4 13:51:04 2023
--8<---------------cut here---------------end--------------->8---

The computation result is not the expected one (compared to the one that uses yank). So it seems something
else is needed.

Any ideas why that patch does not do the right thing?

Best Regards
Andr=E9s Ram=EDrez
--_000_PAXP192MB1608FA33568A719048AE07B184ABAPAXP192MB1608EURP_--