using ses programatically

[Andrés Ramírez]

Hi.

It is about using ses programatically for doing on the fly calculations.
The process has three parts:

1. input file '/tmp/ses202307'
2. template file '/tmp/workbook.ses'
3. elisp file '/tmp/smeter.el'

The output is the file '/tmp/smeter.mbox'

The process is working with a couple of workarounds.
workaround one: it needs yank.
workaround two: manual recalculation of affected cells

As attachments: You would fine
1. smeter.el (version with workarounds)
2. smeter-version-wished-works.el (the name explains it)


The questions are:
1. Is it a bug on 'ses-recalculate-all' (see smeter-version-wished-works.el)
2. why the yank workaround is needed. Why it causes it to work. (see
smeter.el)
3. which one is the right syntax for (ses-range) programatically.
4. how should a user consult a cell value programatically.

Best Regards
Andrés Ramírez

ses202307
Description: input

workbook.ses
Description: spreadsheet

;;; with a buffer visiting the file /tmp/ses202307 run this function
;;; /tmp/ses202307 is the output of a newt curses app
;;; /home/user/dev/c/cli/newt/smeter/
(defun smeter/further-processing ()
  "process input on as spreadsheet using ses formulae and composing an email 
with the ses-output, after ses-recalculate"
  (interactive)
  (let ( (sesbuf nil)
         (measu '())
         (prevmeasu '())
         (waterbill nil)
         (beg nil)
         (workbook-filename) myrowcol )
    (goto-char (point-min))
    (setq beg (point)) (setq workbook-filename (buffer-substring-no-properties 
beg (line-end-position)))
    (forward-line 1)
    (setq beg (point)) (forward-line 4) (setq prevmeasu (split-string 
(buffer-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 (split-string 
(buffer-substring-no-properties beg (point)) "\n"))
    (if (not (file-readable-p workbook-filename))
        (message (format "file not found: '%s'"  workbook-filename))
      (set-buffer (setq sesbuf (find-file-noselect workbook-filename)))
      (setq myrowcol (ses-sym-rowcol (intern "C13"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number waterbill))
      (setq myrowcol (ses-sym-rowcol (intern "B3"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 0 prevmeasu)))
      (setq myrowcol (ses-sym-rowcol (intern "B4"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 1 prevmeasu)))
      (setq myrowcol (ses-sym-rowcol (intern "B5"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 2 prevmeasu)))
      (setq myrowcol (ses-sym-rowcol (intern "B6"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 3 prevmeasu)))
      (setq myrowcol (ses-sym-rowcol (intern "C3"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 0 measu)))
      (setq myrowcol (ses-sym-rowcol (intern "C4"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 1 measu)))
      (setq myrowcol (ses-sym-rowcol (intern "C5"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 2 measu)))
      (setq myrowcol (ses-sym-rowcol (intern "C6"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 3 measu)))
      (ses-recalculate-all)
      (ses-jump-safe "N3") ;;; for killing from N3 to N9 the ses-output after 
applying formulaes 
      (setq beg (point)) (forward-line 4) (copy-region-as-kill beg (point)) 
      (with-temp-buffer ;;; ses-output to an email buffer
        (insert (current-kill 0))
        (goto-char (point-min))
        (dotimes (number (- (length prevmeasu) 1))
          (beginning-of-line)
          (insert (concat "| Dpto" (number-to-string (+ number 1)) "01 |" ))
          (end-of-line)
          (insert "|")
          (forward-line))
        (org-mode) ;; giving format to the org-table and sum
        (org-table-align)
        (org-table-insert-row 1)
        (org-table-goto-column 2)
        (insert (org-table-sum))
        (org-table-align)
        (message-mark-inserted-region (point-min) (point-max))
  ;;; complete composing mail with header and footer
        (goto-char (point-min))
        (insert "To: foo <h@pe.org>\nSubject: agua\nFrom: \"me\" 
<m@pe.org>\n--text follows this line--\nHi.\n\n")
        (goto-char (point-max))
        (insert "\nBest Regards\n")
        (write-file "/tmp/smeter.mbox")
        (message "smeter/further-processing ended"))
  ;;; kill ses buffer without confirmation (comment line below for not killing 
the buffer)
      (set-buffer-modified-p nil)
      (let ((kill-buffer-query-functions nil)) (kill-buffer sesbuf)))))

;;; with a buffer visiting the file /tmp/ses202307 run this function
;;; /tmp/ses202307 is the output of a newt curses app
;;; /home/user/dev/c/cli/newt/smeter/
(defun smeter/further-processing ()
  "process input on as spreadsheet using ses formulae and composing an email 
with the ses-output, after ses-recalculate"
  (interactive)
  (let ( (sesbuf nil)
         (measu nil)
         (prevmeasu '())
         (waterbill nil)
         (beg nil)
         (workbook-filename) myrowcol)
    (goto-char (point-min))
    (setq beg (point)) (setq workbook-filename (buffer-substring-no-properties 
beg (line-end-position)))
    (forward-line 1)
    (setq beg (point)) (forward-line 4) (setq prevmeasu (split-string 
(buffer-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-properties beg (point)))
    (if (not (file-readable-p workbook-filename))
        (message (format "file not found: '%s'"  workbook-filename))
      (set-buffer (setq sesbuf (find-file-noselect workbook-filename)))
      (setq myrowcol (ses-sym-rowcol (intern "B3"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 0 prevmeasu)))
      (setq myrowcol (ses-sym-rowcol (intern "B4"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 1 prevmeasu)))
      (setq myrowcol (ses-sym-rowcol (intern "B5"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 2 prevmeasu)))
      (setq myrowcol (ses-sym-rowcol (intern "B6"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number (nth 3 prevmeasu)))
      (setq myrowcol (ses-sym-rowcol (intern "C13"))) (ses-set-cell (car 
myrowcol) (cdr myrowcol) 'value (string-to-number waterbill))
      ;;; NOTE2ME: yank is needed workaround-one
      (ses-jump-safe "C3") ;;; paste 4 cells
      (kill-new measu)
      (yank)
      ;;; (ses-recalculate-all)
      ;;; NOTE2ME: manual refreshing is needed cos ses-recalculate-all is not 
doing the job then workaround-two
      ;;; Key:R1 refreshOne cos of measures
      ;;; NOTE2ME: ses-dorange should not be needed when ses-recalculate-all 
works. But It should benefit a lot of persons knowing the right syntax
      ;;;(ses-dorange (ses-range E3 E6) (ses-calculate-cell row col))
      (ses-jump-safe "E3") (ses-recalculate-cell) (ses-jump-safe "E4") 
(ses-recalculate-cell) (ses-jump-safe "E5") (ses-recalculate-cell) 
(ses-jump-safe "E6") (ses-recalculate-cell)
      (ses-jump-safe "F3") (ses-recalculate-cell) (ses-jump-safe "F4") 
(ses-recalculate-cell) (ses-jump-safe "F5") (ses-recalculate-cell) 
(ses-jump-safe "F6") (ses-recalculate-cell)
      ;;; Key:R2 refreshTwo cos of waterBill-change
      (ses-jump-safe "H3") (ses-recalculate-cell) (ses-jump-safe "H4") 
(ses-recalculate-cell) (ses-jump-safe "H5") (ses-recalculate-cell) 
(ses-jump-safe "H6") (ses-recalculate-cell)
      (ses-jump-safe "I3") (ses-recalculate-cell) (ses-jump-safe "I4") 
(ses-recalculate-cell) (ses-jump-safe "I5") (ses-recalculate-cell) 
(ses-jump-safe "I6") (ses-recalculate-cell)
      (ses-jump-safe "J3") (ses-recalculate-cell) (ses-jump-safe "J4") 
(ses-recalculate-cell) (ses-jump-safe "J5") (ses-recalculate-cell) 
(ses-jump-safe "J6") (ses-recalculate-cell)
      (ses-jump-safe "K3") (ses-recalculate-cell) (ses-jump-safe "K4") 
(ses-recalculate-cell) (ses-jump-safe "K5") (ses-recalculate-cell) 
(ses-jump-safe "K6") (ses-recalculate-cell)
      (ses-jump-safe "L3") (ses-recalculate-cell) (ses-jump-safe "L4") 
(ses-recalculate-cell) (ses-jump-safe "L5") (ses-recalculate-cell) 
(ses-jump-safe "L6") (ses-recalculate-cell)
      (ses-jump-safe "M3") (ses-recalculate-cell) (ses-jump-safe "M4") 
(ses-recalculate-cell) (ses-jump-safe "M5") (ses-recalculate-cell) 
(ses-jump-safe "N6") (ses-recalculate-cell)
      (ses-jump-safe "N3") (ses-recalculate-cell) (ses-jump-safe "N4") 
(ses-recalculate-cell) (ses-jump-safe "N5") (ses-recalculate-cell) 
(ses-jump-safe "N6") (ses-recalculate-cell)
      (ses-jump-safe "N3") ;;; for killing from N3 to N9 the ses-output after 
applying formulaes , ended-with-ses
      (setq beg (point)) (forward-line 4) (copy-region-as-kill beg (point)) 
      (with-temp-buffer ;;; ses-output to an email buffer
        (insert (current-kill 0))
        (goto-char (point-min))
         (dotimes (number (- (length prevmeasu) 1))
           (beginning-of-line)
           (insert (concat "| Dpto" (number-to-string (+ number 1)) "01 |" ))
           (end-of-line)
           (insert "|")
           (forward-line))
        (org-mode) ;; giving format to the org-table and sum
        (org-table-align)
        (org-table-insert-row 1)
        (org-table-goto-column 2)
        (insert (org-table-sum))
        (org-table-align)
        (message-mark-inserted-region (point-min) (point-max))
  ;;; complete composing mail with header and footer
        (goto-char (point-min))
        (insert "To: foo <h@pe.org>\nSubject: agua\nFrom: \"me\" 
<m@pe.org>\n--text follows this line--\nHi.\n\n")
        (goto-char (point-max))
        (insert "\nBest Regards\n")
        (write-file "/tmp/smeter.mbox")
        (message "smeter/further-processing ended"))
  ;;; kill ses buffer without confirmation (comment line below for not killing 
the buffer)
      (set-buffer-modified-p nil)
      (let ((kill-buffer-query-functions nil)) (kill-buffer sesbuf)))))

smeter.mbox
Description: plain