-
February 14th, 2007, 11:10 AM
#1
Import data from excel
Hello there,
I'm creating a web site using C# in visual studdio. I want to import data from an excel file to a SQL Server database.
I'm using the following code to open the excel file and read the data from cell A1 into a label:
protected void btnUpload_Click(object sender, EventArgs e)
{
Excel.Application excelApp = new Excel.ApplicationClass();
Excel.Workbook newWorkbook =excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
string workbookPath = "c:/test.xls";
Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "", false, false, 0, true, false, false);
Excel.Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = "Sheet1";
Excel.Worksheet excelWorksheet =(Excel.Worksheet)excelSheets.get_Item(currentSheet);
Excel.Range excelCell =(Excel.Range)excelWorksheet.get_Range("A1", "A1");
lblTitle.Text = excelCell.Value2.ToString();
}
However i want to read in multiple values from the workbook and write them to my database.
Any ideas as to how i go about this?
I was thinking of maybe reading the values into a gridview and writing them to the database from there. Not exactly sure how to do thiis though.
Any help would be much appreciated,
Thanks,
Adrian
-
February 14th, 2007, 12:24 PM
#2
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.
-
February 14th, 2007, 01:26 PM
#3
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.
-
February 14th, 2007, 01:42 PM
#4
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...
-
February 15th, 2007, 06:50 AM
#5
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.
-
February 15th, 2007, 09:49 AM
#6
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());
-
February 15th, 2007, 03:21 PM
#7
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.
-
February 15th, 2007, 03:33 PM
#8
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$]"
-
February 15th, 2007, 08:22 PM
#9
Re: Import data from excel
Cheers for that lads i think the problem is well and trully solved!!
Thanks again,
Adrian
-
February 16th, 2007, 10:22 AM
#10
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|