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 to use the .xlsx file as a lightweight database system that can use with out-of-the-box Windows feature.
• A schema mapped into a .xlsx file, and a table mapped into a sheet in the .xlsx file.
• If a .xlsx file or a sheet doesn't exist, it should be newly created automatically. If the file and the sheet already exist, data should be added to the sheet.
  • First, execute the CREATE TABLE statement with a suffix $ for the sheet name. If the book and the sheet already exist, this statement will be finished without error. If the book or the sheet doesn't exist, This statement throws OleDbException. Either way, no new sheet will be created with this operation, so you can test the existence of the sheet safely.
  • Second, execute the CREATE TABLE statement without a suffix $ for the sheet name if and only if the first CREATE TABLE statement throws OleDbException. It means the .xlsx file or the sheet doesn't exist, and the sheet (sheetname)1 will not be created with this operation.
• Erroneous data should be invalidated with datatype constraints. If you put erroneous data in the sheet, some exceptions should be thrown.
  • You have to execute CREATE TABLE first even if the sheet already exists. If the CREATE TABLE has not been executed in a connection, the data type constraint in the sheet will not have an effect. Also, you have to suffix the sheet name with $ in the INSERT statement. Otherwise, it fails if the sheet already exists (it means, CREATE TABLE [sheetname$] was executed).
• Sometimes you will want to show recorded data in some other format (ex. invoice or receipt).
  • You cannot specify the cell format with OLEDB. Even if you prepared the sheet with cells that have specified format, OLEDB will ignore those cells and use new rows to record data. So you have to have two sheets in a book: the first one (sheet M) stores recorded data and another one (sheet V) formats data.
  • If you don't want to touch raw data through the .xlsx file and want to show only the formatted sheet, it is preferable to hide sheet M. This script works regardless of whether the sheet is hidden or not. Also, it is preferable to point the focus to sheet V. The focus position on the sheet will not be changed with INSERT.
  • Also, you can protect sheet M and sheet V to avoid unintentional changes. OLEDB can insert records even if the sheet or cells are protected with passwords (confirmed with LibreOffice Calc 7.2.3.2).

Sample code

$fileName = "C:\tmp\SchemaName.xlsx"
$sheetName = "table"
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$checkExistenceSQL = "CREATE TABLE [${sheetName}$] (CHARVALUE CHAR(4), INTVALUE INTEGER)"
$ddlSQL            = "CREATE TABLE [$sheetName]    (CHARVALUE CHAR(4), INTVALUE INTEGER)"
$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand
$sqlCommand.Connection = $conn
$conn.open()

try {
  try {
    # Check whether the file and the sheet already exist.
    $sqlCommand.CommandText = $checkExistenceSQL
    $sqlCommand.ExecuteNonQuery() > $null
  } catch {
    try {
      # Create them when they don't exist.
      $sqlCommand.CommandText = $ddlSQL
      $sqlCommand.ExecuteNonQuery() > $null
    } catch {
      throw $PSItem
    }
  }

  $insertSQL = "INSERT INTO [${sheetName}$] VALUES ('{0}', '{1}')"
  $sqlCommand.CommandText = ($insertSQL -F 'ABCD', '123')
  $sqlCommand.ExecuteNonQuery() > $null
} finally {
  # You have to close the book here.
  # Otherwise, the file will be stay locked with the PowerShell process.
  $conn.close()
}