Export a simple Excel table with PHP (with formatting!)

I was looking for a way to export a table of data with some formatting (different font weights, different borders, some combined cells) from a PHP server back-end. There are some solutions which are either commercial or require some more package installed in your PHP environment which I didn’t want to do for just one table.

There is always the way to export a CSV file and with some tricks you can even force MS Excel to import it as UTF instead of ancient limited codepages (the trick seems to be to create your file in UTF16 with a valid UTF16 BOM instead of UTF8). Also you can force Excel to tread numbers as numbers instead of strings. But that’s it. No further formatting can be done.

Here the new XLSX file format comes into the game. You can create a XLSX file on your server with PHP and serve it to your users. For this I started with the data which I already saved locally as CSV files. I also created a XLSX file on my computer and (because it is just a zipped directory of different files) extract its content with 7-Zip. Now you find different files here. Some are not important to the content I want to deliver and so I just copied them into my PHP and they will be written without any change.

The files which are important for what I want to achieve are

  • /xl/styles.xml (here you define the styles used for the cells)
  • /xl/sharedStrings.xml (here you can just put all strings your cells contains)
  • /xl/worksheets/sheet1.xml (that’s the resulting table)

The styles define fonts, fills, borders and put them together in cellXfs. You can then point to one of this style in your table cells:
<c r="A1" s="1" t="s">
s= is the property which points to the index of the style
t= is the property which defines the type of call (with s for string and n for number)

A quite good placed to see what you can use is right here in the docs from MS.

At the end you get a nice looking Excel sheet with borders and different fonts and combined cells. All I wanted to have…
Read the rest of this post »

Juni 6, 2020 · admin · No Comments
Posted in: Allgemein

Another helpful Windows utility

Running low on disk space on your Windows system partition? Ever asked you if all the big files in C:\Windows\Installer are still used and referenced? It seems someone wrote a little utility to help you:

PatchCleaner

September 3, 2015 · admin · No Comments
Posted in: Allgemein

Online Trello tool

Some months ago I was forced to use Trello.com Well, I’m not that big fan of it, but for the easy of using I wrote a little tool to make working with it more convenient:

Trollo

Just allow access and you will see every activity on your boards sorted by date and time. It even works from mobile browsers (shitty browsers too).

September 2, 2015 · admin · One Comment
Posted in: Allgemein

No more blurred fonts in Windows 10

Do you use 125% DPI settings in Windows 10? Do you encounter blurred fonts in some programs (mmc.exe for instance)? Read here about it:
http://www.msfn.org/board/topic/174172-font-rendering-is-a-mess-in-windows-10/
or here:
http://forums.mydigitallife.info/threads/62528-SOLVED-Windows-10-higher-DPI-Win8DpiScaling-problem
From the last thread this little tool comes to rescue:
http://windows10_dpi_blurry_fix.xpexplorer.com/
Thumbs up for this little helper and thanks a lot to its creator!

September 2, 2015 · admin · No Comments
Posted in: Allgemein

Download certificate files

Please see here how to download certificate files from servers for manual install.

April 27, 2015 · admin · No Comments
Posted in: Allgemein

Windows 8.1 network problems

http://www.nerd-supreme.de/2013/12/21/blackberry-nach-link-installation-gibt-es-probleme-mit-dem-netzwerk/

Since some days I had really strange network problems: My computer got a wifi connection but couldn’t open any website. I tried to ping www.google.com but without success. I tried to connect to my router without success. Very mysterious, my Smartphone had no problems to access the Internet or the router using the same wifi network.

After two days of blaming my wifi card in my computer I recognized that existing connections on my desktop still worked when I’m unable to create a new connection. Opening a cmd-window and issuing a netstat command showed a bunch of open, established or even waiting connections. Maybe I did run out of free sockets?

After some deeper examinations (netstat -b is your friend) I found many entries from mDNSResponder.exe and tunmgr.exe. Both belong to Blackberry Link software which I actually was forced to install some days ago as I connected my little Blackberry Alpha C device.

I went to Software and Features in System Settings, searched for Blackberry Link software and pressed uninstall. After a couple of minutes my whole network issue went away. What a terrible piece of software…

tl;dr; uninstall Blackberry Link software if you have strange network connection problems.

Juni 4, 2014 · admin · No Comments
Posted in: Allgemein

Github projects to check out

I just wanted to give the github projects I’m currently involved with some more publicity:

1st) An EnyoJS based RSS reader client for your own TinyTiny RSS installation. Successfully tested on a range of mobile devices (I run it on my webOS based Pre2).
https://github.com/linuxq/tt-rssenyo

2nd) A Midi parsing app for Windows written in plain C. Used to create the input files for the next project (AVRMidiPlayer) and might be a godd starting point for your own midi file experiments.
https://github.com/metaview/MidiParser

And finally an ATmega8 based player for converted midi files written in C. The great AVR-SID emulator was the base. I’ve added some code for a fading LED, increased the number of voices to 5 (it’s configurable, but with my ATmega8 at 14.3MHz, 5 voices seem to be the max) and added some code to play converted midi files.
https://github.com/metaview/AVRMidiPlayer

Enjoy and participate!

Mai 28, 2013 · admin · 3 Comments
Posted in: Developing

USBtiny and Windows 8

To use the USBtiny AVR programming adapter under windows 8 you need to do the following steps:

1st get the driver from here: http://forums.adafruit.com/viewtopic.php?f=20&t=17112#p87192
2nd switch on installing unsigned drivers as explained here: http://www.nextofwindows.com/how-to-install-an-un-signed-3rd-party-driver-in-windows-8/

Mai 9, 2013 · admin · No Comments
Posted in: Allgemein

Some little notes about BlackBerry debug token and stuff

Just stumbled upon:

The debug token of my Playbook was invalid and I had to renew it. Of course I have stored my BlackBerry keys (author.p12, barsigner.csk, barsigner.db) on a save place and I already wrote a handy little batch file to create and install the debug token:


REM Playbook
set OLDDIR=%CD%
cd C:\Program Files\Research In Motion\BlackBerry 10 WebWorks SDK 1.0.4.7\dependencies\tools\bin
REM cd c:\Program Files (x86)\Research In Motion\BlackBerry WebWorks SDK for TabletOS 2.2.0.5\bbwp\blackberry-tablet-sdk\bin
c:
call blackberry-debugtokenrequest -storepass xxx -devicepin 50209B1D d:\Dropbox\BB_Keys\debug_token_playbook.bar
call blackberry-deploy -installDebugToken d:\Dropbox\BB_Keys\debug_token_playbook.bar -device 169.254.219.125 -password yyy
chdir /d %OLDDIR% &rem restore current directory

But even if this whole setup worked for month before, it didn’t worked this time. The error I got was “Error: Not yet registered to request debug tokens”. This surprised me much as I was registered to request debug tokens and already did it successfully before with exactly the same command. So as always when BlackBerry puts some obstacles (here: confusing key magic) onto my developing road, ‘ve asked Google for help. Unfortunately not much help was discovered. Some suggest to reregister but I always fear of losing my publisher keys during this procedure.
After some thinking I found the solution: BlackBerry just disabled requesting debug token from old WebWorks SDK installations. As you can see in my script I used WebWorks SDK for TabletOS 2.2.0.5. After switching to the BlackBerry 10 WebWorks SDK path I was able to request and receive my debug token.
tl;dr Don’t give a damn on error messages (as they are mostly completely misleading) but keep your dev environment updated.

Singing key for release is a copy of your author.p12 file renamed to sigtool.p12. You need to copy into the %SDK%/bbwp/bin folder.

[EDIT] Eventually you will run into another problem on Windows 8: you will be unable to sign your release build: “Error: Keystore load: C:\Users\…\Research In Motion\author.p12 (The system cannot find the file specified)”. I run into this and couldn’t find a solution. I dropped the whole Blackberry stuff for quite a while until recently my brother pointed me to this forum post about Windows 8 and signing keys: http://supportforums.blackberry.com/t5/Testing-and-Deployment/Windows-8-Code-Signing/td-p/2014201

You might also run into this error: “Error: Code signing request failed because Application-Development-Mode in Manifest is present and is not set to [false].” Solution sign your app during the build process, add -g password as command line argument. Read here about it: http://supportforums.blackberry.com/t5/Web-and-WebWorks-Development/Error-Code-signing-request-failed/td-p/1008911

When building webworks SDK apps for Blackberry 10 you will encounter some new blocks on the road. The blackberry object isn’t defined by default. You will need to manually include “a reference to webworks.js in your project. This file is located in the /Framework/clientFiles folder.” and add this feature into your config.xml


<feature id="blackberry.app" required="true" version="1.0.0.0"/>

See here for more infos: http://208.74.204.192/t5/Web-and-WebWorks-Development/How-to-Migrate-your-BlackBerry-WebWorks-application-from/ta-p/1988289

I had to copy the file into my local project folder and to add this into my index.html

<!-- load the webworks sdk -->
<script type="text/javascript" charset="utf-8" src="webworks-1.0.4.7.js"></script>

And now to something completely different. Get and save your current working directory from a windows batch file:

http://blogs.msdn.com/b/oldnewthing/archive/2005/01/28/362565.aspx

Februar 28, 2013 · admin · No Comments
Posted in: Allgemein, Developing

Google and Nokia Maps in MapTool for webOS

Did you know that with the last update of MapTool and MapTool Pro you can use many new map providers like Nokia (Here) Maps and Google Maps on your webOS device? Let me tell you how:
First you need either the free MapTool app or even better you’ve purchased MapTool Pro. Launch it and tap an the 3-dots (…) in the bottom right corner. A menu will pop up. Select the latest item, it’s called settings in your locale language. It’s a webOS feature, any other item will appear in plain English, sorry.

After selecting the ‘settings’-item a new scene will pop up with a lot of options. One of the first options is the ‘Renderer’. The renderer is just the source of the map tiles you can see in MapTool. When you open the popup list you can see ‘Custom 1’ and ‘Custom 2’ at the bottom of the list. These are to select your own map tile sources.

To define your own map tile sources we need to scroll the settings scene just to the very bottom. One of the last entries are fields to enter map tile servers, called ‘Server 1’ and ‘Server 2’. Additional you have a selection for the image type the map tiles are stored: jpeg or png.

The latest MapTool versions support map tile urls with pattern for latitude, longitude, x coordinate, y coordinate and zoom value. This allows us for instance to define Google Map tile server as Custom map tile provider. Also the Nokia map tile server (known as Here map) could be used. If you just specify an URL without pattern the map tile url is created like this:

http://tile.opencyclemap.org/cycle/[z]/[x]/[y].png
with “Server 1”: http://tile.opencyclemap.org/cycle/ and “Extension”: PNG

Using pattern you can define urls like this:

http://maptile.maps.svc.ovi.com/maptiler/maptile/newest/normal.day/{z}/{x}/{y}/256/png8

Possible patterns are
{x} x – tile index
{y} y – tile index
{z} zoom value
{lat} latitude of center of map tile
{lon} longitude of center of map tile
{lng} longitude of center of map tile

Here is a list of possible map tile servers and there url pattern.

Please note that MapTool uses {} to separate patterns and that MapTool doesn’t support alternative servers.

As you can see in the list, there are urls given for Google Maps. they probably don#t work anymore. At the moment a valid Google Maps map tile url looks like this:

http://mt0.google.com/vt/lyrs=m@169000000&hl=en&x={x}&y={y}&z={z}&s=Ga

You can use it if you like. Please note that Google Maps forbids caching of map tile from their servers. Their servers send the no-caching attribute with each tile which is processed by webOS and therefore neither downloading nor smart-caching in MapTool will work. You need to switch smart-caching off to use Google Map tile servers.

For Nokia (Here) map tile servers you need your own app id registered. Go to here to read more about it. Once you did it you can specify a Server-URL like

http://m.nok.it/?nord&app_id=_appid&app_code=appcode&c={lat},{lng}&h=256&w=256&z={z}&i=0&nodot&t=8

Find here a list of parameters. You might especially have a look for the ml and t parameter.

Januar 16, 2013 · admin · No Comments
Tags:  Â· Posted in: Allgemein