Question about writing to a .xlsx file.

Question

5.00/5 (1 vote)

See more:

I am attempting to use an update statement and oledb to write to my file but am having an issue passing empty values in the where clause of the update statement. The where clause is based on values from the file I am reading and some of them are blank sometimes.

How can I set values in my update statement's where clause that are essentially blank that excel will recognize as being empty? Please let me know if any clarification is needed or if this is even possible at all.

I have tried just using '' as the value if the cell being read is empty, but it does not work.

Thank you for any advice or suggestions.

Posted 26-Dec-12 6:32am

Richard C Bishop

Updated 26-Dec-12 6:38am

v4

Add a Solution

Comments

Herman<T>.Instance 28-Dec-12 8:44am

which component/interop do you use for your xlsx?

Richard C Bishop 7-Jan-13 9:53am

I am using OleDb.

3 solutions

Solution 2

As the above answer suggests, you DBNull.Value.

Posted 30-Dec-12 20:40pm

ycjames

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kiran Susarla · Accepted Answer · 2012-12-30T20:37:00

Solution 1

If you are using OLEDB, you can use DBNull.Value. This will insert null value into a table.

Posted 30-Dec-12 20:37pm

Kiran Susarla

Comments

Richard C Bishop 7-Jan-13 9:56am

It inserts a null value into a table, but does it do the same inserting into an Excel cell?

Richard C Bishop · Accepted Answer · 2013-01-07T09:14:00

Solution 3

I ended up taking a different approach to solve this issue. Instead of writing back to the .xlsx file I was initially reading from, I have written the same content I was expecting to a .csv file.

It gives me the same functionality without having to worry about datatypes in Excel.

Posted 7-Jan-13 9:14am

Richard C Bishop