Click here to Skip to main content
15,905,867 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.

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. :)
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
 

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