Insert null excel cell as a blank cell in database

Question

I have a simple button event to import excel sheet into the database. The piece/part of code is like this..

private void button6_Click(object sender, EventArgs e)
    {
        OpenFileDialog theDialog = new OpenFileDialog();
        theDialog.Title = "Open Text File";
        theDialog.Filter = "Excel Files|*.xls;*.xlsx;*.xlsm;*.xltm;*.xltx";
        theDialog.InitialDirectory = @"C:\";
        if (theDialog.ShowDialog() == DialogResult.OK)
        {
            try
            {
                foreach (var worksheetx in Workbook.Worksheets(theDialog.FileName.ToString()))
                {
                    foreach (var row in worksheetx.Rows)
                    {
                            String Temp = @"INSERT INTO [dbo].[myTable]
                                       ([Status]
                                       ,[NName]
                                       ,[Notes]
                                       )
                                 VALUES
                                       ('<Status>'
                                       ,'<NName>'
                                       ,'<Notes>'
                                       )";

                            String Temp1 = Temp;
                            bool ForceContinue = false;

                        foreach (var cell in row.Cells)
                        {                                                             
                            if (cell != null)
                            {                                  
                                if (cell.ColumnIndex == 0)Temp1 = Temp1.Replace("<Status>", cell.Text);
                                if (cell.ColumnIndex == 1)Temp1 = Temp1.Replace("<NName>", cell.Text);                                   
                                if (cell.ColumnIndex == 2)Temp1 = Temp1.Replace("<Notes>", cell.Text);                                                                                                                                                    
                            }
                            else
                            {
                             //Looking for this part- How to insert 'NULL' or Blank cell.Text
                            }
                        }

                        DBConn.Execute(Temp1);

For example if my excel sheet column - 'Notes' is like

| Check |
|       |
|       |

It is currently inserted in DB like

| Check |
|<Notes>|
|<Notes>|

I want it to be like this where nulls are inserted as blanks

| Check |
|       |
|       |

Golam Rabbi · Answer 1 · 2015-10-13T16:56:58.200

Replace the code block in inner 'foreach (var cell in row.Cells)' and replace it with the code written bellow.

if (cell != null) { if (cell.ColumnIndex == 0)Temp1 = Temp1.Replace("<Status>", cell.Text); if (cell.ColumnIndex == 1)Temp1 = Temp1.Replace("<NName>", cell.Text); if (cell.ColumnIndex == 2)Temp1 = Temp1.Replace("<Notes>", cell.Text); } else { Temp1 = "" ; }

score 1 · Answer 2 · answered Oct 13 '15 at 16:35

Try combining the ?: operator with DBNull instead of the if statement.

foreach (var cell in row.Cells)
{
        if (cell.ColumnIndex == 0) Temp1 = Temp1.Replace("<Status>", string.IsNullOrWhiteSpace(cell.Text) ? DBNull.Value : cell.Text);
        if (cell.ColumnIndex == 1) Temp1 = Temp1.Replace("<NName>", string.IsNullOrWhiteSpace(cell.Text) ? DBNull.Value : cell.Text);
        if (cell.ColumnIndex == 2) Temp1 = Temp1.Replace("<Notes>", string.IsNullOrWhiteSpace(cell.Text) ? DBNull.Value : cell.Text);
}

For more reading here are some links. ?: and DBNull

score 1 · Answer 3 · answered Oct 13 '15 at 17:18

The first problem is that you don't have any logic to capture a "null" excel cell--you just have code to capture a non-null cell.

Second, a cell is never null in this code, since you just instantiated it as an object in your foreach loop. The properties Value, Value2 or Text, however, might be blank or empty. Check the appropriate property for emptiness.

Depending on what you are trying to do, use this check:

if (cell.Value != null)

or

if (String.IsNullOrEmpty(cell.Text))

, but I don't think you even need that check, since your replace method does that automatically.

Third, maybe I have a different Excel interop language, but this does not even compile for me:

if (cell.ColumnIndex == 0)

I was able to use:

if (cell.Column == 0)

but this threw an exception, because Excel numbers columns beginning with 1, not 0.

Fourth, you have a very long foreach loop to go through each row. Try this to cut down processing time:

foreach (Range row in sheet.UsedRange)

Fifth, you don't need to instantiate the string Temp each time. Move that code before the first loop.

Last, I just used this code to replace the appropriate text:

            foreach (Range cell in row.Cells)
            {
                if (cell.Column == 1) Temp1 = Temp1.Replace("<Status>", cell.Text);
                if (cell.Column == 2) Temp1 = Temp1.Replace("<NName>", cell.Text);
                if (cell.Column == 3) Temp1 = Temp1.Replace("<Notes>", cell.Text);
            }

Insert null excel cell as a blank cell in database

3 Answers3