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

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.

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.

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

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.

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());

---

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.

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$]"

Cheers for that lads i think the problem is well and trully solved!!

Thanks again,

Adrian

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.