You can execute INSERT statements when working with worksheets as tables. You can specify the column names detected from a header row or specify the alphabetical column names in Excel Online. You can also configure how the connector parses the input values into Microsoft Excel Online cell formats.

Set the Header property to configure column names. Set ValueInputOption to configure input parsing.

Note that inserting to ranges is not supported. Use Batch Inserts or Temporary Tables instead.

Inserting with Headers

When the Header property is set to true, you can specify cells in a header row as the column names.

INSERT INTO Test_xlsx_Sheet1 (Name, Amount) VALUES ('Test', 10)

Inserting without Headers

When the Header property is set to false, provide the alphabetical column names.

INSERT INTO Test_xlsx_Sheet1 (A, B) VALUES ('Test', 10)

Configuring Input Parsing

By default, the connector parses all values you input in the SQL statement the same as if you entered the input in the Microsoft Excel Online UI. For example, strings may be converted to numbers, dates, etc.

To disable the parsing of input values, set ValueInputOption. Additionally, see Using Formulas to configure formula evaluation.

Example: Bulk Inserts using Temporary Tables

If using temporary tables, they must be defined and inserted within the same connection. Closing the connection will clear out any temporary tables in memory.

Creating Temporary Tables

Insert the rows you need in a temporary table. Then insert all the rows from that temporary table into the actual table.

Code Example

Below is the complete code to insert 2 rows into Test_xlsx_Sheet1:

Connection conn = DriverManager.getConnection("jdbc:excelonline:InitiateOAuth=GETANDREFRESH;");
Statement stat = conn.createStatement();

stat.executeUpdate("INSERT INTO [Test_xlsx_Sheet1#TEMP] (Column1, Column2) VALUES ('value1', 'value2')");
stat.executeUpdate("INSERT INTO [Test_xlsx_Sheet1#TEMP] (Column1, Column2) VALUES ('value3', 'value4')");

stat.executeUpdate("INSERT INTO [Test_xlsx_Sheet1] (Column1, Column2) SELECT Column1, Column2 FROM [Test_xlsx_Sheet1#TEMP]");

stat.close();