Lazy Diary @ Hatena Blog

PowerShell / Java / miscellaneous things about software development, Tips & Gochas. CC BY-SA 4.0/Apache License 2.0

Excel

How to write records into .xlsx with OLEDB

satob.hatenablog.com satob.hatenablog.com So, how should you write records into .xlsx files with OLEDB? Requirement and Implementation You want to record some data into a .xlsx file with relational database style. In other words, you want …

How to insert a new row with datatype constraints by using Microsoft.ACE.OLEDB

satob.hatenablog.com You can make an Excel file (.xlsx) without Excel by using Microsoft.ACE.OLEDB, but the behavior of INSERT is also a little bit weird. The behavior changes according to the CREATE TABLE is executed in the same connectio…

How to create a new sheet with the desired name by using Microsoft.ACE.OLEDB

You can make an Excel file (.xlsx) without Excel by using Microsoft.ACE.OLEDB, but the behavior of CREATE TABLE is a little bit weird. The behavior changes according to the name of the sheet, suffix of the sheet name ($) in the CREATE TABL…

Read from .xlsx in PowerShell (x64) without using Excel

This article shows how to read .xlsx in PowerShell without Excel. In Windows 10 (x64), you will have to do additional work. You have to install Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine_X64.exe) when you u…

一定桁数に満たない数値のゼロ埋め

Excelには、「数値をゼロ埋めしつ表示」という書式設定はないようだ。 (printfで言うところの"%03d"のような書式指定ができない) 必要であれば、マクロで同様の機能を実装するしかない模様。

ExcelでUnicode CSVをインポート

Unicode(たとえばUTF-8)で保存されたCSVを、ダブルクリックしてExcelで開こうとすると、文字コードが正しく認識されなかったり、区切り文字が正しく認識されなかったりする。SJISのCSVと完璧に同じ、たとえば ダブルクリックで開いてもメニューから「開く…