Lazy Diary @ Hatena Blog

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

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 didn't execute 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, because it fails if the sheet already exists (it means, CREATE TABLE [sheetname$] was executed).
  • Sometimes you will want to show recorded data in some format (ex. invoice or receipt).
    • You cannot specify the cell format with OLEDB. If you prepare the sheet with cells that have specified format, OLEDB ignores those cells and uses 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()
}

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 connection, the suffix of the sheet name ($) in the INSERT statement, and whether the book was modified manually.

When the CREATE TABLE and INSERT are executed in the same connection

The following results are tested with the following script:

$fileName = "C:\tmp\createtest2.xlsx"
$sheetName = 'record'
$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
  }

  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}$] " + 'VALUES ("1234", 123)'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}$] " + 'VALUES ("1234", "ABC")'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}] " + 'VALUES ("1234", 123)'
  $sqlCommand.CommandText = "INSERT INTO [${sheetName}] " + 'VALUES ("1234", "ABC")'
  $sqlCommand.ExecuteNonQuery()

} finally {
  $conn.close()
}

If the CREATE TABLE statement and INSERT statement are executed in the same connection, the result is:

# Precondition INSERT INTO [record$] with valid values INSERT INTO [record$] with invalid values INSERT INTO [record] with valid values INSERT INTO [record] with invalid values
1 The book doesn't exist, the table is created with "CREATE TABLE [record]" Finished without Error, values inserted Finished without Error, values inserted Finished without Error, values inserted OleDbException (*1)
2 The book already exists and the sheet record doesn't exist, the table is created with "CREATE TABLE [record]" Finished without Error, values inserted Finished without Error, values inserted Finished without Error, values inserted OleDbException (*1)
3 The book and an empty sheet record have been manually created, the table is created with "CREATE TABLE [record$]" Finished without Error, values inserted OleDbException (*1) OleDbException (*2) OleDbException (*2)
4 The book and the sheet record have been created and a row has been inserted with OLEDB, and not modified manually. The table is created with "CREATE TABLE [record$]" Finished without Error, values inserted OleDbException (*1) OleDbException (*2) OleDbException (*2)
5 The book and the sheet record have been created with OLEDB and the book was modified manually(*3). The table is created with "CREATE TABLE [record$]" Finished without Error, values inserted OleDbException (*1) OleDbException (*2) OleDbException (*2)

(*1)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "抽出条件でデータ型が一致しません。"
発生場所 C:\tmp\CreateTest2.ps1:23 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*2)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "オブジェクト 'record' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'record' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
発生場所 C:\tmp\CreateTest2.ps1:24 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*3) Added a new sheet Sheet2.

When the CREATE TABLE and INSERT are executed in the different connections

The following results are tested with the following script:

$fileName = "C:\tmp\createtest2.xlsx"
$sheetName = 'record'
$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

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

try {
  # Create-Table

  $conn.open()
  $sqlCommand.CommandText = "INSERT INTO [${sheetName}$] " + 'VALUES ("1234", 123)'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}$] " + 'VALUES ("1234", "ABC")'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}] " + 'VALUES ("1234", 123)'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}] " + 'VALUES ("1234", "ABC")'
  $sqlCommand.ExecuteNonQuery()

} finally {
  $conn.close()
}

If the CREATE TABLE statement and INSERT statement are executed in the different connections, the result is:

# Precondition INSERT INTO [record$] with valid values INSERT INTO [record$] with invalid values INSERT INTO [record] with valid values INSERT INTO [record] with invalid values
1 The book doesn't exist OleDbException (*1) OleDbException (*1) OleDbException (*2) OleDbException (*2)
2 The book already exists and the sheet record doesn't exist OleDbException (*3) OleDbException (*3) OleDbException (*4) OleDbException (*4)
3 The book and an empty sheet record have been manually created OleDbException (*5) OleDbException (*5) OleDbException (*2) OleDbException (*2)
4 The book and the sheet record have been created with OLEDB and not modified manually Finished without Error, values inserted Finished without Error, values inserted Finished without Error, values inserted Finished without Error, values inserted
5 The book and the sheet record have been created with OLEDB and the book modified manually(*6) Finished without Error, values inserted Finished without Error, values inserted OleDbException (*2) OleDbException (*2)

(*1)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "オブジェクト 'record$' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'record$' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*2)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "オブジェクト 'record' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'record' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*3)

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

(*4)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "オブジェクト 'record' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'record' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*5)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "クエリの値と出力するフィールドの数が一致しません。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*6) Added a new sheet Sheet2.

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.

GitLabのビデオ会議・ビデオ通話のルール

GitLabは「民主主義的な会社でも、合意ドリブンな会社でもない*1」という前提のもとに立った前提なのでので、そうでない会社にどこまで当てはまるかは分からないけど、それでもフルリモート業務の先達として参考になるかと。

ちなみにGitLab社がabout.gitlab.comで公開しているドキュメントはMITライセンスになっている。今気付いたんだけど、よく「働きかたが文書化されていて、2000ページ以上ある」と言われる「GitLab Handbook」以外にもドキュメントがあるのね。 https://gitlab.com/gitlab-com/www-gitlab-com/-/blob/master/LICENCE

面白い点を挙げてみたけど、ビデオ会議・ビデオ通話のルールがごっちゃになってるかも。

  • まず本当にビデオ会議が必要か考える*2。そのうえで、必要であれば出席はオプショナルにする*3
    • 各担当者と非同期・個別にやりとりすると情報が共有されないのが心配だけど、結論は必ず文書化される*4から大丈夫、ということなのかな。
  • ビデオ会議に子供が突入してきてもOK*5、ペットも友達も家族もOK*6。その人を知る機会になる。現地語であいさつをしてみよう。
    • ISMS上どういう管理になっているんだろう。会議を公開情報に分類してるというわけでもなさそう*7。業務上知りえる情報のデフォルトの機密性がどの分類にあるかという話だと思うけど、情報の扱いの責任は各個人に帰属させた*8上で、GREENに分類されている情報*9の割合が多いということ?
  • ヘッドフォンを使え*10
  • やりとりが3回つづいたらビデオ会議に移る目安*11。Zoomが推奨。
  • push-to-talkにShushを使う*12
    • 常時マイクONじゃないってことですね。
  • ハイブリッドミーティング(一部メンバーは会議室でスピーカーとカメラ使用、他のメンバーは個別の端末で参加)は非推奨*13。会議室側で行われている会話の内容を共有しにくい、会議室側でマイクをOFFにしづらい、会議室側の参加者の反応が分かりづいらい、会議室側で誰が喋ってるか分かりづらい、通話のディレイに差ができる、個別メンバーの画面共有がしづらい、スクリーンが遠くにあると見づらい、画面が共有だと自分の見たいスライドに移動できない*14、みんなで同じ議事録に書き込みにくい、となりの人が話した内容が遅れてスピーカーから聞こえてくる、自分だけミュートしても他の人のマイクから音声が聞こえてしまう。全員個別の部屋で参加した方がよい。
  • 動画を録る・見る方が記録も理解も早いのであれば録画*15
  • 会議のあいだに余所事しててもOK*16*17。離席してもOK*18。飯を食べてもOK*19だけどマイクはOFFにしろ。
  • ちゃんとした服装をしろ、ちゃんとした服装というのは上半身と下半身が隠れていること(Properly dressed means that you are wearing clothing that covers the top and bottom parts of your body.)*20
  • 話している間の割り込み歓迎*21
  • ビデオ会議でも禁煙。どうしても吸いたければカメラはOFFにして*22
  • カメラは常時ON*23。OFFにするのは、タバコを吸う場合*24と、GitLab社外のメンバーが参加している会議で離席する場合*25・飯を食べる場合*26

*1:https://about.gitlab.com/handbook/leadership/

*2:https://about.gitlab.com/company/culture/all-remote/asynchronous/#question-every-meeting

*3:https://about.gitlab.com/company/culture/all-remote/meetings/#make-meeting-attendance-optional

*4:https://about.gitlab.com/company/culture/all-remote/meetings/#document-everything-live-yes-everything

*5:https://about.gitlab.com/company/culture/all-remote/meetings/#meetings-are-about-the-work-not-the-background

*6:https://about.gitlab.com/handbook/communication/#video-calls

*7:https://about.gitlab.com/handbook/engineering/security/data-classification-standard.html

*8:https://about.gitlab.com/handbook/engineering/security/data-classification-standard.html#gitlab-responsibilities

*9:https://about.gitlab.com/handbook/engineering/security/data-classification-standard.html#green

*10:https://about.gitlab.com/blog/2019/06/28/five-things-you-hear-from-gitlab-ceo/#5-can-you-put-your-headphones-on

*11:https://about.gitlab.com/handbook/communication/#video-calls

*12:https://about.gitlab.com/handbook/tools-and-tips/other-apps/#video-calling

*13:https://about.gitlab.com/handbook/communication/#hybrid-calls-are-annoying

*14:スライドのファイルを共有している場合の話だと思います

*15:https://about.gitlab.com/handbook/communication/#effective-communication-competency

*16:https://about.gitlab.com/handbook/communication/#you-are-the-manager-of-your-attention

*17:https://about.gitlab.com/company/culture/all-remote/meetings/#its-ok-to-look-away

*18:https://about.gitlab.com/company/culture/all-remote/meetings/#its-ok-to-look-away

*19:https://about.gitlab.com/handbook/communication/#video-calls

*20:https://about.gitlab.com/handbook/communication/#video-calls

*21:https://about.gitlab.com/handbook/communication/#video-calls

*22:https://about.gitlab.com/handbook/communication/#video-calls

*23:https://about.gitlab.com/handbook/communication/#you-are-the-manager-of-your-attention

*24:https://about.gitlab.com/handbook/communication/#video-calls

*25:https://about.gitlab.com/company/culture/all-remote/meetings/#its-ok-to-look-away

*26:https://about.gitlab.com/handbook/communication/#video-calls

ソフトウェア開発工程ごとの工数比率

ソフトウェア開発工程ごとの工数比率のデータ。

  • 1979年のNASAのデータで、設計20%、コーディングと単体テスト47.3%、システムテスト15%、受入テスト12%、その他5.7%。*1
  • 1994年のNASAのデータで、設計23%、コーディング21%、テスト30%、その他26%。*2

*1:Mcgarry, F., "Overview of the Software Engineering Laboratory", Proc. from the Fourth Summer Software Eng. Workshop, 1979. https://ntrs.nasa.gov/citations/19820068855

*2:"An overview of the Software Engineering Laboratory", 1994. https://ntrs.nasa.gov/citations/19950022293