Mixiのライフサイクルイベントの署名付きリクエスト / Mixi Lifecycle Event OAuth signatures

Mixiのライフサイクルイベント(アプリが追加された、アプリがマイアプリから削除されたといったイベント)の署名付きリクエストは他のよりまた少し違うみたいです。公開鍵を使うところは他にもありますが、ライフサイクルイベントの場合は、OAuth情報がHTTPのAuthorizationヘッダーに入っているため、お使いのOAuthライブラリーによってうまくいかない場合があるかもしれません。Rubyのoauthではほぼ動くのですが、

xoauth_signature_publickey=lc_20131107

が署名に含まれないため失敗します。

signature = OAuth::Signature.build(request, {:parameters => {'xoauth_signature_publickey' => 'lc_20131107'}}) do

のようにOAuth::Signature.build()に渡すと署名に含まれ、署名の検証が通ります。

検証が通ったbase stringの例:

GET&http%3A%2F%2FXXXXXXXXXX%2FXXXXXXXXXX%2FXXXXXXXXXX%2Faddapp&eventtype%3Devent.addapp%26id%3Dmo3XXXXXXX7fr%26mixi_invite_from%3DmgwXXXXXXXnt8%26oauth_consumer_key%3Dmixi.jp%26oauth_nonce%3D719445958eb7ae359824%26oauth_signature_method%3DRSA-SHA1%26oauth_timestamp%3D1468335606%26oauth_version%3D1.0%26opensocial_app_id%3D41345%26xoauth_signature_publickey%3Dlc_20131107

ちなみに、xoauth_signature_publickeyが一生変わらないわけではありません。Mixiの公開鍵と同じタイミングで変わる予定です。現在使われている公開鍵の有効期限は確か2020年だったと思いますので、まだしばらく大丈夫かもしれないですね。

英訳の下のmixi_signed_request?関数の例もご参照ください。

Mixi’s Lifecycle events (add app and remove app) use an OAuth scheme that is slightly different from Mixi’s other OAuth implementations: the OAuth headers are included in the HTTP Authorization header. Depending on your OAuth library,  the non-standard xoauth_signature_publickey (passed in the HTTP header) may not be included when calculating the signature. However, Mixi includes this parameter. You’ll have to pass it manually to OAuth::Signature.build(), e.g., like this:

signature = OAuth::Signature.build(request, {:parameters => {'xoauth_signature_publickey' => 'lc_20131107'}}) do

Here’s an example of a base string that could pass validation:

GET&http%3A%2F%2FXXXXXXXXXX%2FXXXXXXXXXX%2FXXXXXXXXXX%2Faddapp&eventtype%3Devent.addapp%26id%3Dmo3XXXXXXX7fr%26mixi_invite_from%3DmgwXXXXXXXnt8%26oauth_consumer_key%3Dmixi.jp%26oauth_nonce%3D719445958eb7ae359824%26oauth_signature_method%3DRSA-SHA1%26oauth_timestamp%3D1468335606%26oauth_version%3D1.0%26opensocial_app_id%3D41345%26xoauth_signature_publickey%3Dlc_20131107

Note that the certificate and the “lc_20131107” are linked. When there is a change, both will be updated. The current certificate is valid until 2020 or so.

The full verification code could look like this:

  def mixi_signed_request?
    mixi_certificate = <<END
-----BEGIN CERTIFICATE-----
MIIDNzCCAh+gAwIBAgIJAIQ3zDiILtpzMA0GCSqGSIb3DQEBBQUAMDIxCzAJBgNV
BAYTAkpQMREwDwYDVQQKDAhtaXhpIEluYzEQMA4GA1UEAwwHbWl4aS5qcDAeFw0x
MzExMDcwODQwNTBaFw0yMzExMDUwODQwNTBaMDIxCzAJBgNVBAYTAkpQMREwDwYD
VQQKDAhtaXhpIEluYzEQMA4GA1UEAwwHbWl4aS5qcDCCASIwDQYJKoZIhvcNAQEB
BQADggEPADCCAQoCggEBAMxzCu9sUctGAzL/X0/sH2MSRmc/+X2Wx87ObZDpEd5P
19mIUQXW6hCXObB3SkE7kMuXiRhtrxwsnB9fjYIUEq/1vsTHkLJoJVUFIumqe6EH
c/WZaTmu34WpEUFXNDS4htidXyVqikoDQZF9wdczyH7bLPbekQfRAcyek3E6/7Qi
B00yWUqK8FcUOD4ILmtSHXsz4BNqekNgEzfUi5WkBYKtuD5zSunZalbWUPS7xa57
o1auVdclaHBfqe8dC5DTbxIe0szpHckQrJF9fJ/bIQSmvY6ADBRGfoLF7Fgoc5x+
R5my9weytzg4WdDUjYrxmhy5IpjxytipQqrFDqAUxl8CAwEAAaNQME4wHQYDVR0O
BBYEFKCQSlssCWLqd0tT7NVtoBUNzCasMB8GA1UdIwQYMBaAFKCQSlssCWLqd0tT
7NVtoBUNzCasMAwGA1UdEwQFMAMBAf8wDQYJKoZIhvcNAQEFBQADggEBALoI7elk
ZCv+pUpi6aJepzLnQDYHB2eXpDkpWEUrF1WMvx8ovmWdVeviHqUdFtGL0XZ5tSoV
vGE/KQTavag+MfbafKaff4iHXNyNMygeFP7r/FaFQQRafyNfhaXF6sWfwKrOk/Bc
jIXFN9tYWN6LEwNgYT0C+OSOppQJzt2y1am15FExAHQcIEFYc+3T+MGGJ7e9H8tn
Qz84WmIgNZRUMYQC0PJTsMNVvr+/DTIzjabKz4W8qodXGA7AxXiRYdgC+3RUj/rA
lR09PXZ6nRaKiB0KBDIMUlPu/0u0Vw+GBt0ckH0htOKSxsn09jkITFhy3NX7Slbk
jlnY4pS9JO+avmM=
-----END CERTIFICATE-----
END

    require 'oauth'
    require 'oauth/signature/rsa/sha1'
    begin
      consumer = OAuth::Consumer.new(nil, mixi_certificate, {:signature_method => 'RSA-SHA1'})
      signature = OAuth::Signature.build(request, {:parameters => {'xoauth_signature_publickey' => 'lc_20131107'}}) do
        [nil, consumer.secret]
      end
      return signature.verify
    rescue => e
      logger.debug(e.inspect)
      return false
    end
  end

さくらクラウドの料金システムについて / Sakura’s Cloud Pricing System

さくらクラウドの料金システムには、月額、日額、時間額と、3つの価格があります。月額が日額などよりもお得であることは、プラン変更の際に頭に入れておいた方が良いかもしれません。
損をする例を見てみましょう:
プラン/2Core-2GB   30 + 0時間   3,240円 ← 高いスペックで通常の月額
プラン/2Core-2GB   17 + 14時間   2,916円 ← 高いスペックで17日間分
プラン/1Core-1GB   12 + 9時間   982円 ← 安いスペックで12日間分
12日間スペックが低かったのに、3898円と、高いスペックの通常の月額より658円高いです。
皆さん、気をつけてください。

Just here to document a peculiar feature of Sakura Cloud’s pricing system.
Let’s say you’ve been running on high-spec’d servers and want to reduce these specs a bit. For example, you would like to go from プラン/2Core-2GB down to プラン/1Core-1GB.
Depending on the day you do the change, you may end up paying more than necessary. Here’s an example:
プラン/2Core-2GB    30 + 0時間    3,240円 ← This is the normal monthly price for 2 core/2 GB plan
プラン/2Core-2GB    17 + 14時間    2,916円 ← Let’s say you reduced the specs on the 17th – you’ll pay almost a month’s worth of server fees for the 2 core/2 GB plan
プラン/1Core-1GB    12 + 9時間    982円 ← And the remaining days for the 1 core/1 GB plan
So that month you’ll pay 3898 JPY, even though you were running on lower specs.

Mixiの(モバイルでの)署名付きリクエスト

この度、ガラケー向けのMixiアプリを作ることになりました。こういうアプリのリクエストは、実機 → Mixiが運営しているサーバー → アプリ用サーバー、という流れで送られるようです。(もちろんサーバーから実機への回答は逆の流れです。)Mixiのサーバーは、各リクエストに、ユーザーのMixi IDみたいな文字列を付けてくれて、そして本当にMixiのサーバーから来たリクエストかどうかを検証できるように、署名も付けてくれます。

Mixiの公式ドキュメンテーションはhttp://developer.mixi.co.jp/appli/spec/mob/validate-oauth-signature/にありますが、残念ながら、現在のところ、多少足りない部分がありまして…

まずは、下記のGETリクエストを例として上げているのだが、

http://example.com/foo/?opensocial_app_id=123&opensocial_owner_id=xxxxxxxx

「Ky/6LlDHpHX1EZMRi5mfUl9vxqY=」という署名になるには、「opensocial_owner_id」を「xxxxxxxx」ではなく、「456」に設定しないといけません。
なんで知ってるの!?
archive.orgのおかげです: https://web.archive.org/web/20100912034001/http://developer.mixi.co.jp/appli/spec/mob/validate-oauth-signature

続いて、「特殊な文字(漢字など)を含んでいるパラメーターはどうすればいいですか?」という質問に対する明らかな答えもなく、困っていました。
いろいろ試したところ、二重エンコーディングで対応できました。
つまり、「example.com/foo?test=テスト」みたいなリクエストは、ユーザー側のブラウザーから「example.com/foo?test=%E3%83%86%E3%82%B9%E3%83%88」のように送られます。それを「%25E3%2583%2586%25E3%2582%25B9%25E3%2583%2588」にすると検証が通ります。

test[foo]=barみたいな、角括弧が入っているパラメーターも同じように二重エンコーディングします。

Ruby on Railsだと、下記のようなコードで検証できます。

def mixi_signed_request_mobile?
    require 'cgi'
    oauth_header = request.headers["HTTP_AUTHORIZATION"].split(/\s*,\s*/)
    oauth_header_hash = Hash[oauth_header.map { |keqv| keqv.gsub('"', '').split('=') }]

    base_string_array = [
      "oauth_consumer_key=" + @client_id,
      "oauth_nonce=" + oauth_header_hash["oauth_nonce"],
      "oauth_signature_method=" + "HMAC-SHA1",
      "oauth_timestamp=" + oauth_header_hash["oauth_timestamp"],
      "oauth_version=" + "1.0"
    ]
    if request.get?
      uri = URI.parse(request.url)
      query_array = URI.decode_www_form(uri.query)
      base_string_array += query_array.map{|param| "#{CGI.escape(param[0])}=#{CGI.escape(param[1])}"} # takes care of opensocial_app_id and opensocial_owner_id as well
    else
      # don't need the actual parameters for post requests (mixi-specific awkwardness)
      base_string_array += ["opensocial_app_id=#{params["opensocial_app_id"]}", "opensocial_owner_id=#{params["opensocial_owner_id"]}"]
    end
    base_string_array.sort!
    url = request.original_url.gsub(/\?.*/, '') # can't use request.base_url + request.path because the trailing slash is sometimes(?) cut off
    baseString = request.method + "&" + CGI.escape(url) + "&" + CGI.escape(base_string_array.join("&")) # [] and utf-8 are doubly percent-encoded!! e.g. "テスト" becomes "%25E3%2583%2586%25E3%2582%25B9%25E3%2583%2588"!!!!!

    sha1 = OpenSSL::HMAC::digest(OpenSSL::Digest::SHA1.new, @client_secret + "&", baseString)
    base64 = Base64.strict_encode64(sha1)

    return CGI.escape(base64) == oauth_header_hash["oauth_signature"]
  end

(The above explanation and code show how to verify Mixi’s OAuth 1.0 signatures, as used with Japanese feature phone apps.)

Excel character counter / エクセル文字カウンター

At work, somebody sometimes needs to count the length of selected text in Excel cells. Doing it in one’s head is understandably unpleasant, so why not see if there’s software out there that can count the number of characters in selected text? Well, I got pretty close at http://www.donationcoder.com/Software/Skrommel/index.html#DragKing. Since this is written in AutoHotkey, I’d just have to modify it a bit to count while the mouse cursor is being dragged. (Watch the video below to see it in action.) Unfortunately, pressing Ctrl+V while selecting text doesn’t work, so we instead send the WM_COPY message, like this:

SendMessage 0x301,0,0,%Control%, %WinTitle%  ; WM_COPY

Also it’s not cool if the script gets rid of your clipboard contents, so we restore that too. Get the script here: moji_counter_v3.ahk
Note that it’s partially localized to Japanese.

会社に、エクセルセル内の文字を数えたり区切ったりする作業をしている人がいるのですが、頭の中で数えるのはあまり楽しくないので、この度、どうにかならないかという相談を受けました。早速ネットで調べてみると、http://www.donationcoder.com/Software/Skrommel/index.html#DragKingでほぼ同仕様のソフトがありました!しかもAutoHotkeyなので、すぐにうちの用途に合せられます。

DragKingではマウスボタンを外すとテキストをコピーして文字列の長さをカウントしているのですが、文字列を選択している間にカウントできたらもっと便利なんじゃないかなと思って…しかし、Ctrl+Vを押しても文字がコピーされないので、

SendMessage 0x301,0,0,%Control%, %WinTitle%  ; WM_COPY

変わりましてWM_COPYというメッセージを送ってコピーさせます。後、DragKingの仕様と違って、コピー・カウントの後、クリップボードの内容を復活させます。
AutoHotkeyスクリプトを下記のリンクでダウンロードできます。
moji_counter_v3.ahk

ブラック心理テスト

I recently started a job at a Japanese app development company that is mostly making fortune telling apps and the occasional personality test. I’m getting to work on some interesting stuff, including app development, web development, one-off utilities, and server administration. My first assignment was a personality test, and since it’s released now, I think I’m just going to link to it below.

最近転職し、占いアプリなどを取り扱っている日本の会社で活躍することになりました。「ブラック心理テスト」というアプリの開発を頼まれたのですが、ようやくリリースできましたのでリンクを貼らせていただきます〜

ブラック心理テスト@Play Store (Android)
ブラック心理テスト@App Store (iPhone)

まぁ、とても簡単なアプリですが、友だちが「楽しいかも!」と言ってくれました^^

Male and female kanji (And how to process ENAMDICT’s XML in Perl)

The other day, someone told me that the kanji 漢 (the 漢 in 漢字) usually indicates a male name. This made me wonder what other kanji there are that might indicate a female or male name. So I downloaded JMnedict.xml and processed it a little bit.

Side note: Both XML::LibXML and MySQL’s import from XML thingy turned out to be uselessly slow, so I did this using XML::Twig. Here’s some lazy code to extract all the <keb> elements and their associated name_type:

#!/usr/bin/perl -w

use XML::Twig;
use strict;

binmode(STDOUT, ":utf8");

my $t = XML::Twig->new(twig_handlers => { entry => \&entry });

$t->parsefile($ARGV[0]);

sub entry {
    my ($t, $entry) = @_;
    my ($keb, $name_type);

    eval {
        $keb = $entry->first_child("k_ele")->first_child("keb")->text;
        $name_type = $entry->first_child("trans")->first_child("name_type")->text;
        $entry->purge;
    };
    print "$keb\t$name_type\n" if (!$@);
}

Now we have a file that looks like this:

ゝ泉    given name or forename, gender not specified
〆      female given name or forename
...

Great names, eh? Then we just do:

grep female all_names.csv > female_names.csv
grep "[^e]male" all_names.csv > male_names.csv
perl -C -n -e 'while ($_ =~ s/(\p{Block=CJK_Unified_Ideographs})//) { print "$1\n" }' female_names.csv | sort -n | uniq -c | sort -n > female_kanji.csv # Perl's -C option enables unicode everywhere. Unfortunately, this option doesn't work on the #! line.
perl -C -n -e 'while ($_ =~ s/(\p{Block=CJK_Unified_Ideographs})//) { print "$1\n" }' male_names.csv | sort -n | uniq -c | sort -n > male_kanji.csv
sed -i "s/^\s*//" female_kanji.csv # fix leading whitespace chars from uniq -c
sed -i "s/^\s*//" male_kanji.csv

Then we put the two files into two different sheets of the same file in a spreadsheet program. (I used LibreOffice, but Excel is better. Seriously.) Call one sheet “Female”, the other “Male”, and on a third sheet, concatenate the two lists of kanji and filter out the duplicates (in column A), and use the following formulas for columns B to E, respectively:

=IF(ISERROR(VLOOKUP(A2, Female.$A$1:$B$1601, 2, 0)), 0, VLOOKUP(A2, Female.$A$1:$B$1601, 2, 0))
=IF(ISERROR(VLOOKUP(A2, Male.$A$1:$B$1601, 2, 0)), 0, VLOOKUP(A2, Male.$A$1:$B$1601, 2, 0))
=IF(ISERROR(B2/C2), 1000000, B2/C2)
=B2+C2

(In LibreOffice Calc, you use a period instead of an exclamation mark to reference cells on a different sheet in formulas. LibreOffice Calc doesn’t support the IFERROR() function. I know that 1,000,000 is not the answer to n/0, but we’d like a high number for sorting purposes.) Copy the formulas down and perhaps add the following headers in the top row: Kanji, Female, Male, Ratio, Count. Copy the whole thing without formulas to a new sheet, sort by ratio, and then by count. Perhaps filter out all the kanji that have a count < 10. Here’s a link to my files: kanji_usage_in_names.ods (OpenDocument) and kanji_usage_in_names.xlsx (Excel 2007+).

So it turns out that there are hundreds of kanji that are strong indicators for the gender of a name. By the way, JMnedict.xml’s data isn’t very good: for example, even names like 大介 aren’t gender-classified yet. We’ve got only 1,719 unique kanji for all the gender-classified names, 1,601 unique kanji for female names, and 873 unique kanji for male names. Pretty low and weird numbers. So don’t expect too much accuracy.

Automatically resizing images with Word VBA

For not-too-interested readers: Use the third code snippet.

It’s time for a new post on VBA! Somebody at work is creating a whole lot of training material in Word. Great. Fast forward a couple months, and all of a sudden it’s my job to fix these up. They have all kinds of formatting inconsistencies, and many of the screenshots were resized without preserving their aspect ratios.

I thought, great, I’ll just write some VBA that fixes the screenshots. I quickly came up with something like the following:

Sub resize_all_images_to_page_width()
    For Each inline_shape In ThisDocument.InlineShapes
        inline_shape.LockAspectRatio = msoFalse
        inline_shape.ScaleWidth = 100
        inline_shape.ScaleHeight = 100
        percent = doc.PageSetup.TextColumns.Width / inline_shape.Width
        inline_shape.ScaleWidth = percent * 100
        inline_shape.ScaleHeight = percent * 100
    Next
End Sub

This would in theory scale every image to fit to the page’s width.

And it worked. For almost all of the images. For some, it did something incomprehensible (Word 2010) and turned images into a very long and extremely thin strip of pixels. I tried various things (including .Reset) and did some googling, but couldn’t find a better way to fix the images. What I really wanted was to find out an image’s original width and height, just the way it’s displayed in the Format Picture dialog in the Size tab, but there’s no way to access those values.
So to get an image’s original size, I decided to create a new temporary document, copy and paste the image there, set the .ScaleWidth and .ScaleHeight options to 100, and then look at its .Width and .Height properties. Yay, there we go!
I also decided I don’t want to scale images beyond 100% and added some extra logic to handle that. Here’s the result:

Sub resize_all_images_up_to_page_width()
    Set current_doc = ThisDocument
    Set new_doc = Documents.Add(DocumentType:=wdNewBlankDocument)

    current_doc.Activate
    For Each ishape In current_doc.InlineShapes
        ' ishape.Copy ' doesn't work
        ishape.Select ' <work-around>
        Selection.Copy ' </work-around>
        new_doc.Content.PasteAndFormat (wdPasteDefault)
        Set new_ishape = new_doc.InlineShapes(1)
        new_ishape.LockAspectRatio = msoFalse
        new_ishape.ScaleWidth = 100
        new_ishape.ScaleHeight = 100
        ishape.LockAspectRatio = msoFalse
        If (new_ishape.Width > current_doc.PageSetup.TextColumns.Width) Then
            ishape.Width = current_doc.PageSetup.TextColumns.Width
            ishape.Height = (current_doc.PageSetup.TextColumns.Width / new_ishape.Width) * new_ishape.Height
        Else
            ishape.Width = new_ishape.Width
            ishape.Height = new_ishape.Height
        End If
        new_ishape.Delete
        ishape.LockAspectRatio = msoTrue
    Next
End Sub

And finally, we’d like to make sure images don’t get longer than the length of a page:

Sub resize_all_images_up_to_page_width()
    Set current_doc = ThisDocument
    Set new_doc = Documents.Add(DocumentType:=wdNewBlankDocument)
    page_width = current_doc.PageSetup.TextColumns.Width
    page_height = current_doc.PageSetup.PageHeight - current_doc.PageSetup.TopMargin - current_doc.PageSetup.BottomMargin

    current_doc.Activate
    For Each ishape In current_doc.InlineShapes
        ' ishape.Copy ' doesn't work
        ishape.Select ' <work-around>
        Selection.Copy ' </work-around>
        new_doc.Content.PasteAndFormat (wdPasteDefault)
        Set new_ishape = new_doc.InlineShapes(1)
        new_ishape.LockAspectRatio = msoFalse
        new_ishape.ScaleWidth = 100
        new_ishape.ScaleHeight = 100
        ishape.LockAspectRatio = msoFalse
        If (new_ishape.Width > page_width) Then
            If ((page_width / new_ishape.Width) * new_ishape.Height > page_height) Then
                ishape.Width = page_height / new_ishape.Height * page_width
                ishape.Height = page_height
            Else
                ishape.Width = page_width
                ishape.Height = (page_width / new_ishape.Width) * new_ishape.Height
            End If
        ElseIf (new_ishape.Height > page_height) Then ' going to be shrinking both height and width, and width is okay already, so it'll be even okayer
            ishape.Width = page_height / new_ishape.Height * new_ishape.Width
            ishape.Height = page_height
        Else
            ishape.Width = new_ishape.Width
            ishape.Height = new_ishape.Height
        End If
        new_ishape.Delete
        ishape.LockAspectRatio = msoTrue
    Next
End Sub

Hope this helps! If you have any  questions, feel free to leave a comment.

Twitter and Google+

We’re now on Twitter and Google+! Hope we’ll come up with some good tweets. Got to be careful not to turn it into a microblog for Visio haters. ;D

Wir sind jetzt auch auf Twitter und Google+! Hoffe, dass uns bald ein paar gute Tweets einfallen werden, und dass die Angelegenheit nicht zu einem Anti-Visio-Microblog verkommt. ;D

Twitter (ツイッター)Google+を始めました!ビジオに関するツイートを控えるようにします!約束です!(・∀・)

How to Create Word Documents from Excel using VBA and Fit Strings into Single Lines

On my day job, I was tasked to add a button to an Excel spreadsheet that converts that spreadsheet into a Word file, for improved formatting and aesthetics. This is a pretty straightforward task:

  1. Add a reference to the Microsoft Word Object Library (Tools → References) to get access to the wd-constants
  2. It’s easiest to access things in table cells, so create a Word template that uses tables wherever you want your macro to copy something over from the Excel file.
  3. Write your code around the following skeleton code:
Set objWord = CreateObject("Word.Application") ' Create Word object
Set objDoc = objWord.Documents.Add(Template:=ActiveWorkbook.Path & "\template.dotx") ' Open new document from given template
Set objTable = objDoc.Tables(1) ' Get reference to first table in template
objTable.Cell(1, 1).Range.Text = Cells(1, 1).Text ' Copy cell at row 1, column 1 in current Excel sheet to row 1, column 1 in first table in Word file
' Save file (we don't care about the .docx and just want a .pdf)
FileName = Application.GetSaveAsFilename(fileFilter:="PDF files (*.pdf), *.pdf") ' Note that this is Excel's dialog, but that doesn't matter, as we only need the user to enter a file name/select a file
objDoc.SaveAs2 save_name, wdFormatPDF
objWord.Quit wdDoNotSaveChanges
ActiveWorkbook.FollowHyperlink save_name

In case you need backwards compatibility with older versions of Office, add checks like the following before specifying a template or saving as a PDF file:

If (Application.Version > 12) Then
    ' ...
Else
    ' ...
End If

For older versions of Office, you will need a PDF printer to create PDFs. You can display the print dialog box by doing:

objWord.Dialogs(wdDialogFilePrint).Show

If, unlike me, you do want to display the Word document, get rid of the objWord.Quit line (and perhaps the lines related to saving to PDF) and do the following:

objWord.Visible = True

That’s that, and now we come to the actually interesting part. The part I’ve alluded to in the title of this post. Say you have a table in your Word template that gets populated with a person’s name and email address. You’ve carefully set your column widths to sane values, and don’t want them to change, so you’ve got the “Automatically resize to fit contents” option (Table Properties → Table → Options) turned off. What happens when you get a long name or email address? The name or email address gets split somewhere, and now you’ve got two lines. Ugly! You’ve tried the “Fit text” option (Table Properties → Cell → Options) and felt sorely disappointed. What do we do? Perhaps you’ve tried to access the row’s Height property, and quickly realized that it’s not very useful, as you either get a value of 9999,  or 12 if you’ve changed the height policy, along with a table row that doesn’t look like before. I am sure there are other ways to solve this problem, but this is what I did:

n_lines_before = objDoc.ComputeStatistics(wdStatisticLines) ' Get total number of lines in document
.Cell(3, 4).Range.Text = Cells(26, "AJ").Text
n_lines_after = objDoc.ComputeStatistics(wdStatisticLines) ' Get total number of lines in document, again
Do While (n_lines_after > n_lines_before) ' If the data didn't fit in our cell, the total number of lines will have increased
    .Cell(3, 4).Range.Font.Size = .Cell(3, 4).Range.Font.Size - 1 ' Make the font size a little smaller until the total number of lines returns to being the same as before again
    n_lines_after = objDoc.ComputeStatistics(wdStatisticLines)
Loop

This post is not exactly related to Qiqitori, but I hope it’s of some use to someone, somewhere! It certainly serves as documentation for the code I’ve written, if anything. If you have any questions or better ways of doing things, feel free to leave a comment.

Free eBook/Kostenloses eBook: 小松左京『お召し』Komatsu Sakyô: “Omeshi”/„Der Abruf“ (German)

In this post, we provide a download link to a free (German) eBook for your reading pleasure (if you can read German, that is): Komatsu Sakyô’s “Omeshi”. This is a German translation of a Japanese short story, so it doesn’t make much sense to write a post about this in English, soeverything below this paragraph will be in German. It might still be a good idea to convey a tiny bit of information in English, however: Komatsu Sakyô (1931-2011) (where “Komatsu” is the family name) was a Japanese science fiction author. “Omeshi” was first published in Japan’s S-Fマガジン (S-F Magazine) in 1964. As far as we know, this short story has never been translated into a western language before. We do not have the distribution rights for the Japanese version, and we do not have the rights to sell our translation for money. Nonetheless, donations are appreciated! If you don’t want to spend any money, it would be just as awesome (or even awesomer!) if you could tell possibly interested friends or acquaintances about Qiqitori, this blog, and/or about this eBook. Please note that we do not have the rights to pass on distribution rights of this eBook to you. This means that you shouldn’t just pass on the files you have downloaded here, but rather link to this blog post. We would license this eBook under a Creative Commons license if we could, but as far as we know, we can’t. :(

In diesem Blog-Eintrag stellen wir ein kostenloses eBook zur Verfügung: „Der Abruf“ von Komatsu Sakyô (1931-2011) (wobei Komatsu der Familienname ist), einem japanischen Science-Fiction-Autor. „Omeshi“ ist eine Kurzgeschichte und wurde das erste Mal im japanischen S-Fマガジン (S-F Magazine) im Jahre 1964 veröffentlicht. Nach unserem Wissen hat es bisher noch keine Übersetzungen in eine westliche Sprache gegeben. Wir sind leider nicht im Besitz der notwendigen Rechte, um die japanische Version zum kostenlosen Herunterladen zur Verfügung zu stellen, und die deutsche Version dürfen wir auch nur kostenlos anbieten, aber falls Sie merken, dass Ihnen das Lesen dieser Kurzgeschichte Spaß gemacht hat, würden wir uns um eine kleine Spende freuen. Noch besser wäre es jedoch, wenn Sie möglicherweise interessierte Freunde und Bekannte auf Qiqitori, dieses Blog und/oder dieses eBook aufmerksam machen könnten! Bitte beachten Sie, dass wir nicht im Besitz der Rechte sind, das Distributionsrecht dieser Übersetzung an Sie weiterzugeben. Das heißt, dass Sie möglicherweise Interessierten nicht die Datei selber, sondern einen Link zu diesem Blog-Eintrag geben sollten. Wir würden dieses eBook unter einer Creative-Commons-Lizenz veröffentlichen, wenn wir könnten, aber soweit wir wissen, dürfen wir das nicht. :(

Damit Sie dieses eBook auf möglichst angenehme Art und Weise lesen können, stellen wir es in verschiedenen Formaten zur Verfügung. Drücken Sie auf einen der folgenden Links, um das eBook herunterzuladen.

Aktuelle Versionsnummer: 1.0 für alle Formate.

Komatsu Sakyo – Der Abruf (PDF, A5-Format)

Komatsu Sakyo – Der Abruf (Mobipocket (.mobi), unter anderem für den Amazon Kindle)

Komatsu Sakyo – Der Abruf (EPUB, unter anderem für den Barnes & Noble Nook, Kobo eReader, Sony Reader und viele eBook-Apps auf Smartphones)