Click here to Skip to main content
15,896,330 members
Please Sign up or sign in to vote.
2.33/5 (2 votes)
See more:
Hello,

I have some small problem, (which I can not solve :(

I have dgv with imported excel file in, it, and one columns holds some numbers, what I need is each cell in that column to be filled with value (CAN NOT BE EMPTY) and on button click I was trying to programatically filled it but it's not working

this is the code on button click

foreach (DataGridViewRow row in dgImport.Rows)
{
if (row.Cells["Expense"].Value == null)
{
row.Cells["Expense"].Value = "0";
}

}
dgImport.Update();
dgImport.Refresh();
Posted
Comments
Maciej Los 9-Dec-13 6:44am    
"Not working" is not informative at all.
How do you import data from Excel to GridView?
shonezi 9-Dec-13 6:55am    
I can show the code for importing

OpenFileDialog openDialog = new OpenFileDialog();
openDialog.Title = "Select file";
openDialog.Filter = "Excel Sheet(*.xls)|*.xls|All Files(*.*)|*.*";
openDialog.FilterIndex = 1;
openDialog.RestoreDirectory = true;
if (openDialog.ShowDialog() == DialogResult.OK)
{
if (openDialog.FileName != "")
{
textBox1.Text = openDialog.FileName;
cmbExcelSheet.DataSource = GetExcelSheetNames(openDialog.FileName);
foreach (string s in cmbExcelSheet.Items)
{
cmbExcelSheet1.ComboBox.Items.Add(s);
}

}

else
{
MessageBox.Show("Choose Excel sheet path..", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}

and GetExcelSheetNames

private String[] GetExcelSheetNames(string excelFile)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;

try
{
// Connection String. Change the excel file to the file you

// will search.

String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + excelFile + ";Extended Properties=Excel 8.0;";
// Create connection object by using the preceding connection string.

objConn = new OleDbConnection(connString);
// Open connection with the database.

objConn.Open();
// Get the data table containg the schema guid.

dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

if (dt == null)
{
return null;
}

String[] excelSheets = new String[dt.Rows.Count];
int i = 0;

// Add the sheet name to the string array.

foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}

// Loop through all of the sheets if you want too...

for (int j = 0; j < excelSheets.Length; j++)
{
// Query each excel sheet.

}

return excelSheets;
}
catch (Exception ex)
{
return null;
}
finally
{
// Clean up.

if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
Have you debugged and checked whether it goes inside the if statement?
shonezi 9-Dec-13 7:16am    
sorry for bothering you all , I found solution, this works, thank you all, thank you

foreach (DataGridViewRow row in dgImport.Rows)
{
if (string.IsNullOrEmpty(row.Cells["Potrosnja"].Value.ToString()) == true)
{
row.Cells["Expense"].Value = 0;
}

}
dgImport.Refresh();
I wanted to suggest you this type of technique only. :) But you have done yourself. Good work.

I have added one answer on behalf of you. Please accept that so that it will move to answered list.

I would recommend you to read these articles:
Using ADO.NET to work with Excel files[^]
How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET[^]

To avoid nulls or empty strings when numeric value is needed, please use IIF function:
SQL
SELECT IIF(NumericField1<>'',NumericField1, 0) AS NumericField1, TextField2, OtherField3
FROM [Sheet1$]
 
Share this answer
 
On behalf of OP...
Quote:
sorry for bothering you all , I found solution, this works, thank you all, thank you
C#
foreach (DataGridViewRow row in dgImport.Rows)
{
    if (string.IsNullOrEmpty(row.Cells["Potrosnja"].Value.ToString()))
    {
        row.Cells["Expense"].Value = 0;
    }   
}
dgImport.Refresh();
 
Share this answer
 
v2
Comments
shonezi 9-Dec-13 7:38am    
thank you again
Most welcome buddy. :)

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900