Re: Import data from excel
Did you create the Sql connection string and all the objects associated with inserting into the db?
I suggest that you write the query and use parameters...
cmdText is a string with the value of the select statement. i.e. "insert into tblTableName (intID, strValue) Values(@intID, @strValue);"
Code:
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
cmd.CommandText = cmdText;
cmd.Parameters.AddWithValue("@intID",intID);
cmd.Parameters.AddWithValue("@strValue",strValue);
cmd.ExecuteNonQuery();
}
catch (SqlException sqlExc)
{
//SqlException handling here
}
catch (Exception exc)
{
//Exception handling here
}
}
Hope that helps.
Re: Import data from excel
Physically writing to the database isn't the problem. What i'm struggling with is putting the data i want from the excel document into something like a gridview from where i will write it to my database.
You see i want to take only certain columns from the excel spreadsheet from a number of rows.
Any idea as to how i will put the data into a gridview??
Cheers,
Adrian.
Re: Import data from excel
Do you want them in a gridView or do you want them in the db? I thought you wanted them in the db but to accomplish this you were going to try putting them in the GridView...
You realize that you don't need to put it into a grid view or any other object to insert into the db? You've already shown that you can get the values...
Re: Import data from excel
Yes thats correct i don't actually want to put the information into a gridview unless i have to.
The problem is is that i do not know how to pull specific columns from my excel document. At the moment i can only read the value in A1.
Do you know how i can read in multiple specific values?
Thanks,
Adrian.
Re: Import data from excel
Well I did some programming and research for you that you most likely could have done just as easily...
You can specify a range like the article that I posted for you shows. It didn't show how to get specific cells from that range but I was able to figure it out just by guess and test.
Code:
Excel.Range excelCell = (Excel.Range)excelWorksheet.get_Range("A1", "A2");
object[,] cells = (object[,])excelCell.Value2;
MessageBox.Show(cells[1,1].ToString());
MessageBox.Show(cells[2,1].ToString());
Re: Import data from excel
1. What value are you trying to read from Excel? There are two types of values in Excel i. Formatted value (displayed to the user) ii. Underlying unformatted value.
2. You can use the following code to get the "Used Range".
Code:
private Excel.Range GetUsedRange(Excel.Worksheet excelSheet, Excel.Range allCells, out int rowCount, out int columnCount)
{
Excel.Range rangedCells = null;
rowCount = 0;
columnCount = 0;
if (allCells != null)
{
Excel.Range lastCell = null;
try
{
// Get the last cell to find out the number of
// columns and rows
lastCell = allCells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell, missingValue);
// Get the last row and column information
rowCount = lastCell.Row;
columnCount = lastCell.Column;
// we don't need last cell reference anymore. Release it here.
Marshal.ReleaseComObject(lastCell);
lastCell = null;
if ((rowCount > 0) && (columnCount > 0))
{
// find the used range
// User range string should be something like "A1:D4" etc.
// To find the last cell index, we do the following thing.
string lastColumn = GetLastColumnName(columnCount);
string usedRange = "A1:" + lastColumn + rowCount.ToString();
rangedCells = excelSheet.get_Range(usedRange, missingValue);
}
}
finally
{
// release the last cell COM object
this.ReleaseComObject(lastCell);
}
}
return rangedCells;
}
private void ReleaseComObject(object comObject)
{
if (comObject != null)
{
int referenceCount = 0;
// call the Marshal.ReleaseComObject recursively until the referenceCount becomes 0.
do
{
referenceCount = Marshal.ReleaseComObject(comObject);
}while (referenceCount > 0);
// set the object to null.
comObject = null;
}
}
private string GetLastColumnName(int lastColumnIndex)
{
string lastColumn = "";
// check whether the column count is > 26
if (lastColumnIndex > 26)
{
// If the column count is > 26, the the last column index will be something
// like "AA", "DE", "BC" etc
// Get the first letter
// ASCII index 65 represent char. 'A'. So, we use 64 in this calculation as a starting point
char first = Convert.ToChar(64 + ((lastColumnIndex - 1)/26));
// Get the second letter
char second = Convert.ToChar(64 + (lastColumnIndex%26 == 0? 26 : lastColumnIndex%26));
// Concat. them
lastColumn = first.ToString() + second.ToString();
}
else
{
// ASCII index 65 represent char. 'A'. So, we use 64 in this calculation as a starting point
lastColumn = Convert.ToChar(64 + lastColumnIndex).ToString();
}
return lastColumn;
}
Excel.Range rangedCells = GetUsedRange(excelSheet, excelSheet.Cells, out rowCount, out columnCount);
if (rangedCells != null)
{
// get the non formatted values
object[,] nonFormattedValues = rangedCells.get_Value(Excel.XlRangeValueDataType.xlRangeValueDefault) as object[,];
..................
You can use ADO.NET too to read the excel file. But, I had few troubles with that approach, I don't remember it now. I will post another message if I can find the ADO.NET code.
Re: Import data from excel
Here is the ADO.NET code...
// Construct the connection string
string strConnection = "Provider=Microsoft.Jet.Oledb.4.0;" +
"Data Source="+ fileName + ";" +
"Extended Properties=" + (char)34 + "Excel 8.0;Hdr=No;Imex=1;" + (char)34;
// Connect to the database
dbConnection = new OleDbConnection(strConnection);
dbConnection.Open();
// Get the data
OleDbDataAdapter dbAdapter = new OleDbDataAdapter("Select * from " + worksheet, dbConnection);
// Create and fill the dataset
DataSet dataSet = new DataSet();
dataSet.Locale = CultureInfo.InvariantCulture;
dbAdapter.Fill(dataSet);
// close the database connection. no use for it now.
dbConnection.Close();
the value for the "worksheet" variable should be "[<WorksheetNameInExcel>$]"
Ex: "Select * from [EmployeeExpense$]"
Re: Import data from excel
Cheers for that lads i think the problem is well and trully solved!!
Thanks again,
Adrian
Re: Import data from excel
When i have problems about doing things to automate Office, i record macros and then check the code it has generated. It helps me a lot.