DEV Community: Yusuke arakawa

Why I want to use JSON in LibreOffice Calc.

Yusuke arakawa — Tue, 11 Jun 2019 07:47:02 +0000

Recently, Web Service exports JSON.
It is not SOAP, not XML.
Web Data is JSON.

Address search from PostalCode
Weather

....etc

If Calc can use JSON and WebAPI,
Calc borrow Functions from Web.

Use Python, It is easy.
Is it need to use by calc?

No.
Office Worker don't use Python.
They said.
VLOOKUP is wonderful!
VBA is awesome!
Python? what's?
I'm not a Programmer.

It is need to deal JSON and WebAPI in Calc for us.

Excel can use JSON.
Connect to a json file
LibreOffice don't have this function.

You make this function.

So, I develop underline Calc extensions.

It may be not enough.
ParseJSON don't work :-(

Finally, I want to make Calc Standerd Function them.

Make LibreOffice Draw Document to use odfpy

Yusuke arakawa — Sun, 03 Dec 2017 04:47:40 +0000

Odfpy is famous Python library to make OpenDocument Format.
But,How to use odfpy?

odfpy official manual is only api-for-odfpy.odt and example program in github.

OpenDocument Text and OpenDocument Spreadsheet's odfpy tutorial is a few found.
OpenDocument Graphic's odfpy tutorial is not found!

I want to have Getting started at Draw Document's odfpy.

Enviroment

Windows10 Pro/Home x64
Windows System for Linux(WSL)
Ubuntu16.04
Python3
Python3-pip (pip)
odfpy
iPython
LibreOffice Draw for Windows

I use WSL.
I don't explain to install WSL.
if don't install WSL, check it under url.

Install the Windows Subsystem for Linux

Setup Python

$ sudo apt update
$ sudo apt upgrade
# Python
$ sudo apt install Python3
# pip
$ sudo apt Python3-pip
$ pip3 install -U pip -user
# odfpy iPython
$ pip3 install odfpy,iPython -user
$ pip3 list --format columns
# folder
$ mkdir odfpy
$ cd odfpy

Make Document and Page

At first,I start iPython.
iPython is very useful.


$ ipython 
Python 3.6.3 (default, Nov  6 2017, 23:30:23) 
Type 'copyright', 'credits' or 'license' for more information
IPython 6.2.1 -- An enhanced Interactive Python. Type '?' for help.

In [1]:

Check your current directory.
odfpy make Draw Document in this directory.
Put picture and python script to run.


# check your current directory
In [1]: %pwd
Out [1]:'/home/username/odfpy'

Make docucment

In:[2] from odf.opendocument import OpenDocumentDrawing
In:[3] doc=Open # press your tab key
 open()          OpenDocumentDrawing()

Type OpenDocu... press tab key.
Amazing! Type-Ahead.

OK?
Umm,drawing in addText method.
try,it.

In[3]:doc=OpenDocmentDrawing()
In[3]:doc.drawing.addText('aaaa')
....(Abbreviation)...
IllegalText: The <office:drawing> element does not allow text

In[4]:

can't add Text.
Draw Document can't put direct text and Shape.
make Page.
Text and Shape in Page.

Open Another Terminal and make createpage.py file in odfpy folder.

from odf.opendocument import OpenDocumentDrawing
from odf.draw import Page
from odf.style import Style,MasterPage,PageLayout

## Create the drawing page style
dpstyle = Style(family="drawing-page",name="DP1")
doc.automaticstyles.addElement(dpstyle)

## Create page layout specifying dimensions
plstyle = PageLayout(name="PM1")
doc.automaticstyles.addElement(plstyle)

## Create a master page
masterpage = MasterPage(stylename=dpstyle, name="Default", pagelayoutname=plstyle)
doc.masterstyles.addElement(masterpage)

## Create a page to contain the drawing
drawpage = Page(masterpagename=masterpage, name="page1", stylename=dpstyle)
doc.drawing.addElement(drawpage)

To make Page

make PageStyle and PageLayout.
make Masterpage
make Page form MasterPage
add Page to Draw Document

It's too much bother to make Page.
That’s how it is.

That run in iPython.

# check File
In[4]: %ls
createpage.py
In[5]: %run createpage.py

If it is not error,Success to add Page.

PageProperties

How to change PageProperties?
I try a little.

# load PageLayoutProperties
In[6]:from odf.style import PageLayoutPropretis
In[7]:plsytle.addElement(PageProperties(margin="0cm",pageheight="210mm",pagewidth="297mm", printorientation="portrait"))
In[8]:doc.automaticstyles.addElement(plsytle)

Nothing PaperSize,so we change pageheight and pagewidth.
and save it.

In[9]: doc.save('draw01',True)

it copy to Windows Desktop.

$ cp draw01.odg /mnt/c/Users/username/Desktop

open it on Windows to use LibreOffice Draw.

OK.

Add Text

It's too much bother to make Page for me.
So, it do.

In [1]:from odf.opendocument import OpenDocumentDrawing,load
In [2]:doc=load('draw01.odg')
In [3]:doc.getMediaType()
Out[3]: 'application/vnd.oasis.opendocument.graphics'

load draw document.

To add Text,

Make Text
Make TextBox and add Text
Make Frame and add TextBox
Take out Page
Add Frame to Page

Text can't directly add to Page.
To add Text,Text add to Frame or Rect...etc.

This is code underline.

In[4]: from odf.draw import Page,TextBox,Frame
# get Page
In[5]: page=doc.getElementByType(Page)
In[6]: page=page[0]
In[7]: from odf.text import P
# Create Text
In[8]: text=P()
In[9]: text.addText('こんにちは')
# Text add to TextBox
In[10]: tb=TextBox()
In[11]: tb.addElement(text)
# TextBox add to Frame.
# x,y is position.
In[12]: frame=Frame(width='100mm',height='20mm',x='2cm',y='2cm')
In[13]: frame.addElement(tb)
# Page add to Frame.
In[14]: page.addElement(frame)
# Save
In[15]: doc.save('draw02',True)

LibreOffice Draw open draw02.odg.

good!
I want to change font size and background color.

TextProperties and GraphicProperties

TextProperties change Text's font-size.
GraphicProperties change Frame's background color.

In [1]: from odf.opendocument import load
In [2]: from odf.draw import Page,Frame,TextBox
In [3]: from odf.style import Style,TextProperties,GraphicProper
   ...: ties
In [4]: from odf.text import P
# load draw file
In [5]: doc=load('draw02.odg')
# Take out Frame and Text
In [6]: page=doc.getElementsByType(Page)
In [7]: page=page[0]
In [8]: frame=page.getElementsByType(Frame)
In [9]: frame=frame[0]
In [10]: tb=frame.getElementsByType(TextBox)
In [11]: tb=tb[0]
In [12]: text=tb.getElementsByType(P)
In [13]: text=text[0]
# make Style
In [14]: s=Style(name='P1',family='paragraph')
# English is fontsize But, Chinese Japanese Korean font is fontsizeasian.
# 「こんにちは」=fontasian
In [15]: tp=TextProperties(fontsize='32pt',fontsizeasian='32pt')
In [16]: gs=Style(name='GR1',family='graphic')
In [17]: gp=GraphicProperties(fill='none',stroke='none')
# add Style to Frame and Text
In [18]: text.setAttribute('stylename',s)
In [19]: frame.setAttribute('stylename',gs)
# add Style to Draw Document
In [20]: doc.automaticstyles.addElement(s)
In [21]: doc.automaticstyles.addElement(gs)
# Save
In [22]: doc.save('draw03',True)

got it!
Next,I want to add image.

add Image

Get Image

Get LibreOffice Draw's icon png from Wikimedia Common.
rename drawicon.png

$ pwd
/home/users/odfpy

$ curl -O https://upload.wikimedia.org/wikipedia/commons/thumb/8/80/LibreOffice_4.0_Draw_Icon.svg/200px-LibreOffice_4.0_Draw_Icon.svg.png
$ mv 200px-LibreOffice_4.0_Draw_Icon.svg.png drawicon.png
$ ls drawicon.png
drawicon.png

OK, I get image.

Add Image

# load file.
In [1]: from odf.opendocument import load
In [2]: doc=load('draw01.odg')
# import need's Object
In [3]: from odf.draw import Page,Image,Frame
# Take out Page
In [4]: page=doc.getElementsByType(Page)
In [5]: page=page[0]
# make PhotoFrame. x,y is position,height width is size.
# This image is square,so height and width is same scale.
In [6]: photoframe=Frame(x='10mm',y='40mm',height='26mm',width='26mm')
# add Image to Draw Document.
In [7]: href=doc.addPicture('drawicon.png')
# add Image to photoframe
In [8]: photoframe.addElement(Image(href=href))
# add photoframe to Page
In [9]: page.addElement(photoframe)
# Save
In [10]: doc.save('draw04',True)

Do you add Image to Document?
OK, Check Document.

Success!

　Conclusion

PageProperties
Put Text
Put Image and Shape
Save

I believe it to make odfpy's Getting Started.

Reference

API for Odfpy - odt
API Reference.
example program's explain is a little.

odfpy - example
sample program.

Official is only two Reference.

Search and replace text odfpy - StackOverflow
I know it add set Style to Text.P to use SetAttribute.

iPXE chainloading to use dnsmasq and proxydhcp

Yusuke arakawa — Mon, 20 Nov 2017 05:44:57 +0000

I want to try chainloading.
tftp is very slow and to transfer a little data.
http is fast and to able to transfer more heavy data.
It is underline figure that chainloading.

LAN Boot > dhcp-boot > undionly.kpxe from tftpd > boot_menu.ipxe from local httpd > global httpd boot

Environment

I'm not Network Administrator.
I cannot change DHCP-Server Setting.
So, I use Proxy DHCP.

The proxyDHCP server providing TFTP server IP address and name of the NBP only to PXE identified booting clients. ¹

To use proxyDHCP Major software is Dnsmasq.
Dnsmasq provides network infrastructure.
Dnsmasq provides network boot and proxy-dhcp,too.
I use only network boot and proxy-dhcp.
Dnsmasq can install to use yum or apt-get.

Server
- Debian Wheezy (Linux pxeserver 3.2.0-4-amd64 #1 SMP Debian 3.2.78-1 x86_64 GNU/Linux)
- ECS 945GCT-M
- Intel(R) Pentium(R) Dual CPU E2160 @1.80GHz
- Dnsmasq 2.62-3+deb7u3
- Proxy DHCP
- iPXE (undionly.kpxe)
Clilent
- Desktop PC
- ECS G45T-M5
- Realtec PCIe GBE Family Controller
- Intel UNDI,PXE-2.0 (bulid 082)
- Realtek RTL8139(A/B/C)/RTL8130 PCI Fast Ethernet Controller v2.11
Network
- 172.20.0.0/16
- GW
- 172.20.0.254
- DHCP-Server
- 172.20.0.70

Setup

dnsmasq install
dnsmasq config Setting
iPXE Source Get from git
iPXE Build to undionly.kpxe
deploy undionly.kpxe
web boot ipxe menu
Let's try boot.

Dnsmasq

At First,
I install dnsmasq.
I often use debian and ubuntu.
CentOS/RedHat's dnsmasq is same package name and same configuration file destination.

install

Debian/Ubuntu

$ sudo apt-get install dnsmasq

Redhat/CentOS

$ sudo yum install dnsmasq

Settings

Debian/Ubuntu/CentOS/Redhat

$ cat /etc/dnsmasq.conf | grep -v -e ^# -e ^$
dhcp-range=172.20.0.0,proxy
dhcp-boot=tag:!ipxe,undionly.kpxe,172.20.0.225
dhcp-match=set:ipxe,175 # gPXE/iPXE sends a 175 option.
dhcp-boot=tag:!ipxe,undionly.kpxe
dhcp-boot=http://172.20.0.225/a.ipxe,172.20.0.225,172.20.0.225
pxe-service=tag:!ipxe,x86PC,"splash",undionly.kpxe
enable-tftp
tftp-root=/var/ftpd
log-queries
conf-dir=/etc/dnsmasq.d

iPXE

It is easy to download from undionly.kpxe.
But I want to build source over one years age.
undionly.kpxe to build　from source show parent code,
when chainloading.
I want to check parent hash code.
I want to check fix date from parent hash code.
So, I get source form git repository and build it.

Get Source

I need to install git.
I install git-core As described below

$ sudo apt-get install git-core

I need underline package to build iPXE.

gcc
binutils
make
perl
syslinux
liblzma
xz

I install underline.

$ sudo apt-get install gcc,binutils,make,perl
$ sudo apt-get install syslinux,lzma-dev,liblzma5

I get ipxe source to my current directory from git repository.

I execute git as underline.

$ cd ~
$ git clone git://git.ipxe.org/ipxe.git

Build

It is ipxe direcotry in current directory.
I enter into ipxe direcotry as underline and build undionly.kpxe.

$ cd ~/ipxe/source
$ make bin/undionly.kpxe

deploy

$ cd ~/ipxe/source
$ mv bin/undionly.kpxe /var/ftpd

httpd

I prepare apache2.

You may prepare httpd that you like nginx, perl's or python's simple one liner web server...etc.

Install

$ sudo apt-get install apache2

DocumentRoot

Debian and Ubuntu's apache2 is virtual host default on.
Default DocumentRoot is /var/www.

Check virtual hosts

$ cat /etc/apache2/apache2.conf | grep sites-enabled
#       `-- sites-enabled
# * Configuration files in the mods-enabled/ and sites-enabled/ directories
Include sites-enabled/

Check DocumentRoot

$ cat /etc/apache2/sites-enabled/000-default | grep DocumentRoot
        DocumentRoot /var/www

Boot Menu

I move web deploy folder, /var/www/.

I make boot.ipxe to use Editor.

Please,show underline.

$ sudo cd /var/www
$ sudo vi boot.ipxe
#!ipxe
menu install menu
item ubuntu Ubuntu installation
item centos7 Centos7 installation
item --gap
item back install menu
choose --timeout 20000 --default back target && goto ${target} || goto menu

:ubuntu
set ubuntu http://archive.ubuntu.com/ubuntu/dists/trusty/main/installer-amd64/current/images/netboot/ubuntu-installer/amd64
initrd ${ubuntu}/initrd.gz
kernel ${ubuntu}/linux tasks=standard vga=788 -- quiet
boot

:centos7
set centos7 http://mirror.centos.org/centos/7/os/x86_64
initrd ${centos7}/image/pxeboot/initrd.img
kernel ${centos7}/image/pxeboot/vmlinuz vga=788 repo=${centos7}
boot

:back
exit

and Save boot.ipxe.

PXE Boot

Prepared OK!

I switch on Client PC.
First, BIOS show up.
show underline.
I want to show BBS(BIOS Boot Screen).
So,soon I press F11 Key when PC boot.

American
Megatrends

AMIBIOS(C) 2008 American Megatrends. Inc.
G43T-M5 BIOS Release 09/28/2009 for MCJ
CPU : Intel(R) Celeron(R) CPU E3300 @ 2.5GHz

Press DEL to run Setup
Press F11 for BBS POPUP
The MCH is operation with DDR2 800
DRAM Timing: Tcl:6/Tras:18/Trcd:6/Twr:6/Trfc:52/Twtr:3/Trrd:3/Trtp:3
Single Chanel Mode
2048MB OK (64MB Shared Memory SIze Used)
Auto-Detecting Pri Slave...IDE Hard Disk
Auto-Detecting 3rd Master..ATAPI CDROM
Pri Slave : SAMSUNG HD252HJ 1AC01118
            Ultra DMA Mode-5, S.M.A.R.T. Capable and Status OK
3rd Master: HL-DT-ST DVDDAM GH24NS50 XP01
            Ultra DMA Mode-5

client show BIOS Boot Screen.

I select Realtec Boot Agent and press Enter Key.

Press select boot device:
--------------------------------
   SAMSUNG HD252HJ
   HL-DT-ST DVDRAM GH24NS50
   Genetic USB SD Reader
   Genetic USB CF Reader
   Genetic USB SM Reader
   Genetic USB MS Reader
-> Realtec Boot Agent

--------------------------------
^ and ^ to menu selection
ENTER to select boot device
ESC to boot using defaults

Client change display.

Realtec RTL8111B/8111C Gigabit Boot Agent
Press Shift-F10 to configure ..........

Start PXE Boot Conlose.

Intel UNDI. PXE-2.1 (build 002)
Copyright (C) 1997-2000 Intel Corporation

For RealTek RTL8111B/8111C Gigabyte Ethernet Controller v2.14(000225)

CLIENT MAC ADDR: 00 25 11 CB 41 1B GUID 00020003-0004-0005-0006-00700080009
CLIENT IP: 172.20.10.83 MASK 255.255.0.0
DHCP IP: 172.20.0.70 PROXY IP:172.20.0.225
GATEWAY IP: 172.20.0.254

Auto-Select:
     splash

BOOT SERVER IP : 172.20.0.225

PXE-EB: !PXE at 9C3E:0070,entry point at 9C3E:0109
             UNDI code segment 9C3E:1A7A, data segment 923D:A010 (584-632kB)
             UNID device is PCI 02:00.0, type DIX+802.3
             584kB free base memory after PXE unload
iPXE initialising devices...ok

iPXE 1.0.0+ (55e4) -- Open Source Network Boot Firmware -- http://ipxe.org
Features: DNS HTTP iSCSI TFTP AoE ELF MBOOT PXE bzImage Menu PXEXT

Press Ctrl+B for the iPXE command line...

Boot Menu from Web Server.

Select Ubuntu installation or CentOS7 installation and Press Enter key.

Linux Installer boot from internet resource.

            Install Menu

Ubuntu installation
CentOS7 installation

References

Dnsmasq
- Package Software for PXE and proxyDHCP
iPXE chainloading
- How to use iPXE chainloading
iPXE Download
- How to build iPXE from source code in Source code Section.
Preboot_Execution_Environment
- Explain Preboot eXecution Environment(PXE)
- Integration section expain proxyDHCP for us.
How To Install Git on Debian 8
- To get ipxe source from git
iPXE - The Versatile Boot Loader
- Easy iPXE Boot Menu

Preboot_Execution_Environment Integration Section ↩