Lazy Diary @ Hatena Blog

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

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 TABLE statement, the existence of the specified sheet, and whether the book was modified manually.

The following results are tested with the following script:

$fileName = "C:\tmp\createtest3.xlsx"
# $sheetName = 'record'
$sheetName = 'sheet1'
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand
$sqlCommand.Connection = $conn
$conn.open()

try {
  try {
    # $sqlCommand.CommandText = "CREATE TABLE [${sheetName}$] (CHARVALUE CHAR(4), INTVALUE INTEGER)"
    $sqlCommand.CommandText = "CREATE TABLE [${sheetName}] (CHARVALUE CHAR(4), INTVALUE INTEGER)"
    $sqlCommand.ExecuteNonQuery()
  } catch {
    $PSItem
  }
} finally {
  $conn.close()
}

When the sheet name is not sheet1

If the sheet name is not sheet1 (record in the example above), the result is:

# Precondition CREATE TABLE [record$] CREATE TABLE [record]
1 The book doesn't exist OleDbException (*1) Finished without Error, new sheet record is created
2 The book already exists and the sheet record doesn't exist OleDbException (*1) Finished without Error, new sheet record is created
3 The book and an empty sheet record have been manually created Finished without Error, no new sheet created Finished without Error, new sheet record1 is created
4 The book and the sheet record have been created with OLEDB and not modified manually Finished without Error, no new sheet created OleDbException (*2)
5 The book and the sheet record have been created with OLEDB and the book modified manually(*3) Finished without Error, no new sheet created Finished without Error, new sheet record1 is created

(*1)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "'record$' が見つかりません。パラメーターや別名が正しいこと、無効な文字や区切り記号が含まれていないこと、または名前が長すぎないことを確認してください。"
発生場所 C:\tmp\CreateTest3.ps1:14 文字:5
+     $sqlCommand.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*2)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "テーブル 'record' は既に存在しています。"
発生場所 C:\tmp\CreateTest3.ps1:15 文字:5
+     $sqlCommand.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*3) Added a new sheet Sheet2.

When the sheet name is sheet1

If the sheet name is sheet1, the result is:

# Precondition CREATE TABLE [sheet1$] CREATE TABLE [sheet1]
1 The book doesn't exist OleDbException (*4) Finished without Error, new sheet sheet1 - 1 is created
2 The book already exists but the sheet sheet1 doesn't exist OleDbException (*4) Finished without Error, new sheet sheet1 is created
3 The book and an empty sheet sheet1 have been manually created Finished without Error, no new sheet created Finished without Error, new sheet sheet11 is created
4 The book and the sheet sheet1 have been created/written with OLEDB and not modified manually Finished without Error, no new sheet created OleDbException (*5)
5 The book and the sheet sheet1 - 1 have been created with OLEDB and not modified manually Finished without Error, no new sheet created OleDbException (*5)
6 The book and the sheet sheet1 have been created/written with OLEDB and the book modified manually(*6) Finished without Error, no new sheet created OleDbException (*5)
7 The book and the sheet sheet1 - 1 have been created with OLEDB and the book modified manually(*6) OleDbException (*4) Finished without Error, new sheet sheet1 is created

(*4)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "'sheet1$' が見つかりません。パラメーターや別名が正しいこと、無効な文字や区切り記号が含まれていないこと、または名前が長すぎないことを確認してください。"
発生場所 C:\tmp\CreateTest3.ps1:16 文字:5
+     $sqlCommand.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*5)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "テーブル 'sheet1' は既に存在しています。"
発生場所 C:\tmp\CreateTest3.ps1:16 文字:5
+     $sqlCommand.ExecuteNonQuery()
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*6) Added a new sheet Sheet2.