Insert integers into excel sheet

Question

Inserting data using oledb into an excel sheet, integers are only displayed as integers:

I already tried to change the properties of the following connection string:

string szConnectionString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" + myFilePath + "';Extended Properties=\"Excel 12.0;HDR=YES;Readonly=False;\"";

Additionally I tried to create a new sheet/table with data types:

cmd.CommandText = "CREATE TABLE [data3] (quarter VARCHAR, value INT);";

Following my current code inserting data:

cmd.CommandText = "CREATE TABLE [data3] (quarter VARCHAR, value INT);";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO [data3$]([quarter ],[value]) VALUES(?,?)";
cmd.Parameters.AddWithValue("@p1", "2018.06");
cmd.Parameters.AddWithValue("@p2", 13); // <-- INT
cmd.ExecuteNonQuery();

My expectation is that the data are inserted into excel correctly, meaning in the correct format.

Off-topic, but why are you using an interpolated string for no reason? You could write your code as `$"Provider=...;Data Source='{myFilePath}';..."` — Maximilian Burszley, Mar 27 '19 at 12:24
In general, the OLEDB interfact to Excel does not support *formatting* values. If you want full control over the result, use a .NET Excel library. You can find a list [here](https://stackoverflow.com/q/151005/87698). (Personally, I use SpreadsheetLight.) — Heinzi, Mar 27 '19 at 12:30
Yes you are right, I changed the code snippet to "int". Inserting the data into excel and open the excel file all data are displayed as string instead of integer. In the Screenshot you can see the green edge at each column stating "this string could be an integer" — SNO, Mar 27 '19 at 12:30
@SNO avoid `AddWithValues`. That method has to guess what type, size, scale etc to use based on the input. Use `Add` instead and specify the type you want. — Panagiotis Kanavos, Mar 27 '19 at 12:41
@SNO a far better option is to use a library like [EPPlus](https://www.nuget.org/packages/EPPlus/) to create `xlsx` files directly. You don't have to install the Jet driver , you can manipulate styles, tables etc. Loading data into a sheet could be as simple as `sheet.Cells.LoadFromDataTable(someTable)` or `sheet.Cells.LoadFromCollection(someList)` — Panagiotis Kanavos, Mar 27 '19 at 12:43
@Panagiotis Kanavos Thank you for the reply. Do you have any experience with the performance of EPPlus especially for many requests? And (I know I can look that up) does the library need any Excel/Office installation on server or is that standalone? — SNO, Mar 27 '19 at 13:36
@SNO `xlsx` is a zip package that contains XML files. The schema is documented so you could just create those files manually, or use [the Open XML SDK](https://learn.microsoft.com/en-us/office/open-xml/how-to-create-a-spreadsheet-document-by-providing-a-file-name). You don't need Excel or the Jet driver at all. Epplus offers a far friendlier interface though. — Panagiotis Kanavos, Mar 27 '19 at 13:38
@SNO as for performance, you'll have to test it with your workload. Epplus and similar libraries create all elements in memory and write them out when you call `SaveAs`. This can take a lot of memory if you try to load 100K rows. On the other hand, there's no interop with COM, no connections that need disposing. Finally, you can save an Excel workbook directly to a stream. This can be *very* useful in a web application that produces relatively small files as you don't have to create and manage temporary files — Panagiotis Kanavos, Mar 27 '19 at 13:43

score 3 · Answer 1 · answered Mar 27 '19 at 12:36

I ran this code and had no issues after changing it to the following:

cmd.CommandText = "CREATE TABLE [data3] (quarter VARCHAR, value INT);";
cmd.ExecuteNonQuery();

cmd.CommandText = "INSERT INTO [data3$]([quarter],[value]) VALUES(@Q1,@V1)";
cmd.Parameters.AddWithValue("@Q1", "2018.06");
cmd.Parameters.Add("@V1", SqlDbType.Int);
cmd.Parameters["@V1"].Value = 13;
cmd.ExecuteNonQuery();

I made a few changes and forced the value parameter to be an integer.

Insert integers into excel sheet

1 Answers1