locked
Extracting data from Excel to import to SQL RRS feed

  • Question

  • User-718146471 posted

    Ok guys, I know Excel isn't a database so let's get that one out of the way. What I'm wondering is if URL fields inside of an excel sheet are accessible from c# in code behind? What I'm trying to do is to take two fields from a spreadsheet that has the following information:

    Course Name | Course Number (has URL for profile information)

    What I'm looking to do is to extract these two fields and capture the information from the URL. I know how to handle the screen scraping, what I need to know is how to pull that information in from Excel.

    Wednesday, June 26, 2013 1:34 PM

Answers

  • User-718146471 posted

    Decided since Interop is the only clean way of doing this without having to spend $400, I build a windows forms app to do it. The code that gets the data sorted out is actually pretty cool, so I'm posting it for anyone else who might run across this. Keeping that in mind, this code is for Windows Forms. I'm only posting to help others avoid hours of frustration.

     

    // This is the code that determines the fields, flat text (Value2) versus actual hyperlinks
    // Leaving out extraneous code that does non-related tasks
    
            private void button1_Click(object sender, EventArgs e)
            {
                string ConnStr = string.Empty;
                //Lets me decide in the app config if its dev or prod  
                string ConnChoice = ConfigurationManager.AppSettings["ConnChoice"];
                if (ConnChoice == "DEV")
                {
                    ConnStr = ConfigurationManager.ConnectionStrings["ConnStrDev"].ConnectionString.ToString();
                }
                else
                {
                    ConnStr = ConfigurationManager.ConnectionStrings["ConnStrProd"].ConnectionString.ToString();
                }
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range range;
                string textval = string.Empty;
                string textval2 = string.Empty;
                string str = string.Empty;
                int rCnt = 0;
                int cCnt = 0;
                int HyperLink = 2;
                string ExcelFileName = label1.Text.ToString();
    
                xlWorkBook = xlApp.Workbooks.Open(ExcelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    // get_Item(1) is sheet #1. You could probably put this into an int loop increment so it grabs every sheet
    // providing they have the same format.
    range = xlWorkSheet.UsedRange; str = string.Empty; string myString = string.Empty; for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { for (cCnt = 1; cCnt <= HyperLink; cCnt++) { // this .Hyperlinks.Count check for != 0 tells me if Column B is hyperlink if (range.Cells[rCnt, HyperLink].Hyperlinks.Count != 0) { if (cCnt == HyperLink) { textval = Convert.ToString(((range.Cells[rCnt, cCnt] as Excel.Range).Value2)); textval2 = ((range.Cells[rCnt, cCnt] as Excel.Range).Cells.Hyperlinks[1].Address); } else { str = Convert.ToString((range.Cells[rCnt, cCnt] as Excel.Range).Value2); } } } // str is the class title // textval is the class number // textval2 is the embedded URL // Do your db insert/update work here str = string.Empty; textval = string.Empty; textval2 = string.Empty; } } // Close the Workbook and Excel xlWorkBook.Close(true, null, null); xlApp.Quit(); // Kill off all objects and do garbage collection releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); // Leave the importer application since the work is done Application.Exit(); } private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Unable to release the Object " + ex.ToString()); } finally { GC.Collect(); } } private void btnOpenFile_Click(object sender, EventArgs e) { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Excel 2007/2010 File (*.xlsx)|"; dialog.InitialDirectory = @"C:\"; dialog.Title = "Select excel file"; dialog.ShowDialog(); foreach (var file in dialog.FileNames) { label1.Text = file.ToString(); } button1.Visible = true; } }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 11, 2013 12:27 PM

All replies

  • User269602965 posted

    OLEDB

    Pretty much just like a database call from .NET. 

    Create connection statement using the OLDB driver, create select statement, execute the statement into  datareader, use the data as desired by the app, close your datareader and connection.

    Examples abound on web

    http://stackoverflow.com/questions/6951420/dynamically-select-range-of-cells-in-excel-sheet-in-c-net

     

     

    Wednesday, June 26, 2013 6:06 PM
  • User-718146471 posted

    Alright, I'll give this a go and get back to you once I know something, thanks.

    Thursday, June 27, 2013 2:43 PM
  • User-718146471 posted

    It's not working, it gives me an error about the fields

    No value given for one or more required parameters.:

                    try
                    {
                        string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Myfiles.ToString() + "; Extended Properties=Excel 12.0 Xml;";
                        DataTable Sheets = new DataTable();
                        using (OleDbConnection conn = new OleDbConnection(ConnectionString))
                        {
                            conn.Open();
    
                            Sheets = conn.GetSchema("Tables");
    
                            foreach (DataRow sheet in Sheets.Rows)
                            {
                                lblExcelOut.Text = sheet.ToString();
                            }
                            string firstSheet = Sheets.Rows[0][2].ToString();
    
                            DataSet Contents = new DataSet();
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter("Select A, B from [" + firstSheet + "]", conn))
                            {
                                adapter.Fill(Contents, "MyTable");
                            }
                            //Display the contents
                            foreach (DataRow content in Contents.Tables["MyTable"].Rows)
                            {
                                lblExcelOut.Text = content[0] + " | " + content[1];
                            }
                            Console.WriteLine();
    
                            //Remove First Row; Note: This is not heading!
                            Contents.Tables["MyTable"].Rows.RemoveAt(0);
    
                            //Since the first row is removed, Second Row becames first row now.
                            //Clearing the LastName of our First Row.
                            Contents.Tables["MyTable"].Rows[0][1] = "";
    
                            //Displaying the Contents
                            foreach (DataRow content in Contents.Tables["MyTable"].Rows)
                            {
                                lblExcelOut.Text = content[0] + " | " + content[1];
                            }
                        }
                        // now delete the excel file and continue to next file
                        System.IO.File.Delete(Myfiles.ToString());
                        // Increment i to go to the next file
                        i++;
                    }
                    catch (Exception ex)
                    {
                        lblFileError.Text = lblFileError.Text + "Error has occurred during processing: " + ex.ToString() + "</br></br>";
                        // now delete the excel file and continue to next file
                        System.IO.File.Delete(Myfiles.ToString());
                        // Increment i to go to the next file
                        i++;
                    }
    

    Thursday, June 27, 2013 4:21 PM
  • User269602965 posted

    Extended Properties=Excel 12.0 Xml;

    Study all the variations on the Extended Properties  Word XML may not be required.

    It varies if you are querying XLS vs. XLSX vs. XLSM

    And other parameters.

    I know I always have to fiddle with it to get it right.

    If you use HEADERS = yes then it treats the headers like Column names in a database

    If you use HEADERS = no then you can access specific cells or range of cells

    then the IMEX parameter has various effects on different data types in Excel.

    etc.

    Thursday, June 27, 2013 7:09 PM
  • User-718146471 posted

    Ok, I'll try that tomorrow and get back to you.

    Thursday, June 27, 2013 8:41 PM
  • User269602965 posted
       ' Update TABLE_NAME into ENTITY_TO_LOAD Worksheet cells C7, C8, and C9'
        Try
          Dim strOLEDBConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strTargetXLSM & ";" & "Extended Properties='Excel 12.0 Macro;HDR=NO;'"
          Using OLEDBConn As New OleDbConnection(strOLEDBConn)
            OLEDBConn.Open()
            Using cmdUpdate As OleDbCommand = OLEDBConn.CreateCommand()
              cmdUpdate.CommandText = "UPDATE [ENTITY_TO_LOAD$C7:C9] SET F1='" & strTableName & "'"
              cmdUpdate.ExecuteNonQuery()
            End Using
            OLEDBConn.Close()
          End Using
        Catch oledbex As OleDbException
          MsgBox("Error: OLEDB failed to update ENTITY_TO_LOAD worksheet.  Details logged.  " & oledbex.ToString, MsgBoxStyle.OkOnly, "Error: OLEDB error logged")
          Call OLEDBExceptionLogging(oledbex)
        End Try
    
    

    Thursday, June 27, 2013 10:07 PM
  • User269602965 posted

    Just uploaded some example code of updating specific range of cells, which also could be read by SELECT statement as well.

    Need to be sure you have the ACE OLEDB driver installed

    Microsoft Access Database Engine 2010 Redistributable which has the OLEDB driver (COM stuff installed in program files > common > office)

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    works for XLS legacy, XLSX and XLSM office 2007 and 2010 (maybe 2013 too)

    SP1 of the Microsoft Access Database Engine 2010 Redistributable is out.

    http://support.microsoft.com/kb/2460011

     

    Thursday, June 27, 2013 10:19 PM
  • User269602965 posted

    And reading from a range of cell with SELECT on worksheet and cell range

    and once in dataset, can use in your app as needed for populating text boxes, lists, datagrid, etc.

        Try
          Dim strOLEDBConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & strTargetXLSM & ";" & "Extended Properties='Excel 12.0 Macro;HDR=NO;'"
          Dim query As String = "SELECT * FROM [ENTITY_TO_LOAD$C7:C9]"
          Using OLEDBConn As New OleDbConnection(strOLEDBConn)
            OLEDBConn.Open()
            Using da = New OleDbDataAdapter(query, OLEDBConn)
              Dim ds As New DataSet()
              da.Fill(ds)
            End Using
          End Using
        Catch oledbex As OleDbException
          MsgBox("Error: OLEDB failed to select from ENTITY_TO_LOAD worksheet.  Details logged.  " & oledbex.ToString, MsgBoxStyle.OkOnly, "Error: OLEDB error logged")
          Call OLEDBExceptionLogging(oledbex)
        End Try
    

    Thursday, June 27, 2013 10:52 PM
  • User-718146471 posted

    Ok, that is definitely closer. Now if I don't know what the name of Sheet 1 is, could I use a referencial name instead? Also, when I load the data into dataset ds, how do I get the data out of it?

    Friday, June 28, 2013 7:04 AM
  • User-718146471 posted

    Ok, I'm closer. It says it can't find the name of my spreadsheet yet its pulling it out of the spreadsheet. Here's my code:

            protected void ASPxUploadControl1_FileUploadComplete(object sender, DevExpress.Web.ASPxUploadControl.FileUploadCompleteEventArgs e)
            {
                string MyText = string.Empty;
                int i = 0;
                while (i < ASPxUploadControl1.FileInputCount)
                {
                    string Myfiles = Server.MapPath("~/Files/") + ASPxUploadControl1.UploadedFiles[i].FileName.ToString();
                    ASPxUploadControl1.UploadedFiles[i].SaveAs(Myfiles, true);
                    ArrayList al = new ArrayList();
                    Application excelApp = new Application();
                    Workbook excelWkBook = excelApp.Workbooks.Open(Myfiles, 0, true, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                    Sheets excelSheets = excelWkBook.Worksheets;
                    Worksheet excelWorksheet = null;
                    if (excelSheets != null)
                    {
                        for (int ii = 1; ii <= excelSheets.Count; ii++)
                        {
                            excelWorksheet = (Worksheet)excelSheets.get_Item((object)ii);
                            al.Add(excelWorksheet.Name.ToString());
                        }
    
                    }
    
                    // now do the import of the data from the excel file
                    try
                    {
                        foreach (string a in al)
                        {
                            string ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Myfiles.ToString() + ";" + "Extended Properties='Excel 12.0;HDR=NO;IMEX=1'";
                            string query = "SELECT A, B FROM [" + a.ToString() + "]";
                            using (OleDbConnection OLEDBConn = new OleDbConnection(ConnStr))
                            {
                                OLEDBConn.Open();
                                OleDbDataAdapter da = new OleDbDataAdapter(query, OLEDBConn);
                                {
                                    DataSet ds = new DataSet();
                                    da.Fill(ds);
                                    MyText = ds.Tables.ToString();
                                }
                            }
                        }
     
                        // now delete the excel file and continue to next file
                        System.IO.File.Delete(Myfiles.ToString());
                        // Increment i to go to the next file
                        i++;
                    }
                    catch (Exception ex)
                    {
                        lblFileError.Text = lblFileError.Text + "Error has occurred during processing: " + ex.ToString() + "</br></br>";
                        // now delete the excel file and continue to next file
                        System.IO.File.Delete(Myfiles.ToString());
                        // Increment i to go to the next file
                        i++;
                    }
                }
                Response.Write(MyText);
            }
        }
    

    Friday, June 28, 2013 8:07 AM
  • User-718146471 posted

    Ok, the error I had was because I didn't put a $ after the table name variable. Now, if any of my fields are empty or NULL, I'm getting this error:

    "No value given for one or more required parameters."

    How do I handle this problem in-code? Also, how do I get the values from the dataset I've built from the data adapter?

    Friday, June 28, 2013 9:35 AM
  • User269602965 posted
    DataTable dt = ds.Tables[0];
     
    foreach (DataRow dr in dt.Rows)
     {
       MessageBox.Show(dr["ColumnName"].ToStr…‡
     }

    Unfortuantely, that is more work when you do not know the SHEET name.  Sheets are kept in an unordered collection, so when the XLS has more than one sheet, doing SELECT * FROM [{0}] returns data from first sheet it encounters... but which one in unordered collection do you get each trip to the excel world???  If you have one sheet in the XLS, you are okay.

    Friday, June 28, 2013 5:33 PM
  • User-718146471 posted

    It gave me an error saying it can't find Table 0. Here's the code:

     

            protected void ASPxUploadControl1_FileUploadComplete(object sender, DevExpress.Web.ASPxUploadControl.FileUploadCompleteEventArgs e)
            {
                string MyText = string.Empty;
                string HyperLink = string.Empty;
                int i = 0;
                while (i < ASPxUploadControl1.FileInputCount)
                {
                    string Myfiles = Server.MapPath("~/Files/") + ASPxUploadControl1.UploadedFiles[i].FileName.ToString();
                    ASPxUploadControl1.UploadedFiles[i].SaveAs(Myfiles, true);
                    ArrayList al = new ArrayList();
                    Application excelApp = new Application();
                    Workbook excelWkBook = excelApp.Workbooks.Open(Myfiles, 0, true, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
                    Sheets excelSheets = excelWkBook.Worksheets;
                    Worksheet excelWorksheet = null;
                    if (excelSheets != null)
                    {
                        for (int ii = 1; ii <= excelSheets.Count; ii++)
                        {
                            excelWorksheet = (Worksheet)excelSheets.get_Item((object)ii);
                            al.Add(excelWorksheet.Name.ToString());
                        }
    
                    }
    
                    // now do the import of the data from the excel file
                    try
                    {
                        foreach (String a in al)
                        {
                            string ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Myfiles.ToString() + ";" + "Extended Properties='Excel 12.0;HDR=NO;IMEX=1'";
                            string query = "SELECT a, b FROM [" + a.ToString() + "$]";
                            using (OleDbConnection OLEDBConn = new OleDbConnection(ConnStr))
                            {
                                OLEDBConn.Open();
                                OleDbDataAdapter da = new OleDbDataAdapter(query, OLEDBConn);
                                {
                                    DataSet ds = new DataSet();
                                    System.Data.DataTable dt = ds.Tables[0];
    
                                    foreach (DataRow dr in dt.Rows)
                                    {
                                        lblExcelOut.Text = dr["A"].ToString() + "<br />" + dr["B"].ToString() + "<br />";
                                    }
                                }
                            }
                        }
     
                        // now delete the excel file and continue to next file
                        System.IO.File.Delete(Myfiles.ToString());
                        // Increment i to go to the next file
                        i++;
                    }
                    catch (Exception ex)
                    {
                        lblFileError.Text = lblFileError.Text + "Error has occurred during processing: " + ex.ToString() + "</br></br>";
                        // now delete the excel file and continue to next file
                        System.IO.File.Delete(Myfiles.ToString());
                        // Increment i to go to the next file
                        i++;
                    }
                }
            }
        }
    

    Monday, July 1, 2013 8:51 AM
  • User269602965 posted

    da.Fill(ds)

    you have to fill your dataset with data adapter data

    then table(0) (the default table) should have data.

    dataset can have one or more tables, but in this case it will only have one table

     

    Monday, July 1, 2013 6:19 PM
  • User-718146471 posted

    Ok, I'll try that. Now my second field has a url inside it. How do I access the text and the URL?

    Monday, July 1, 2013 6:22 PM
  • User269602965 posted

    You will have to read the string value of the URL (the way it is seen in Excel data cell) as plain TEXT from Excel and then convert to URL on the application side with formatting and construct the URL.

    Monday, July 1, 2013 8:11 PM
  • User-718146471 posted


    da.Fill(ds)
    you have to fill your dataset with data adapter data
    then table(0) (the default table) should have data.
    dataset can have one or more tables, but in this case it will only have one table

    Ok, I put it in and now I'm getting an error:

    "No value given for one or more required parameters."

     

                            using (OleDbConnection OLEDBConn = new OleDbConnection(ConnStr))
                            {
                                OLEDBConn.Open();
                                OleDbDataAdapter da = new OleDbDataAdapter(query, OLEDBConn);
                                {
                                    DataSet ds = new DataSet();
                                    da.Fill(ds); // the error I get is on this line.
                                    System.Data.DataTable dt = ds.Tables[0];

    Tuesday, July 2, 2013 9:11 AM
  • User269602965 posted
    DataSet ds = new DataSet();
    
    using (OleDbConnection connection = new OleDbConnection(connectionString))
    using (OleDbCommand command = new OleDbCommand(query, connection))
    using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
    {
        adapter.Fill(ds);
    }
    
    

    make connect, make command, make adapter, then fill the dataset

    Tuesday, July 2, 2013 11:49 AM
  • User-718146471 posted

    Still getting the error. No value given for one or more required parameters.

    Tuesday, July 2, 2013 11:56 AM
  • User-718146471 posted

    Ok, the syntax of my query was wonky. Take a look:

     

                        foreach (String a in al)
                        {
                            string ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Myfiles.ToString() + ";" + "Extended Properties='Excel 12.0;HDR=NO;IMEX=1'";
                            string query = "SELECT * FROM [" + a.ToString() + "$]";
                            DataSet ds = new DataSet();
                            using (OleDbConnection connection = new OleDbConnection(ConnStr))
                            using (OleDbCommand command = new OleDbCommand(query, connection))
                            using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                            {
                                adapter.Fill(ds);
                            }
                            System.Data.DataTable dt = ds.Tables[0];
    
                            foreach (DataRow dr in dt.Rows)
                            {
                                lblExcelOut.Text = dr[0].ToString() + "<br />" + dr[1].ToString() + "<br />";
                            }
    
                        }
    

    I'm getting the text for dr[1] but what I want is to access the URL from the excel sheet. It only displays the text in that field.

    Tuesday, July 2, 2013 12:12 PM
  • User269602965 posted

    Is the returned TEXT not an ASCII string value (representation) of your hyperlink (aka URL, aka web address) like

    http://www.acme.com   (this is just text without the hyperlink underline, but still a valid web address)

    And thus the text representation can be converted to hyperlink on your Winform, WPF, Web Page user interface.

     

     

    Tuesday, July 2, 2013 8:50 PM
  • User-718146471 posted

    This is what I have:

    Text in cell for hyperlink: This is Cool

    Actual hyperlink: http://www.thecoolsite.com/

    I'm getting the text from the cell, what I want is the hyperlink.

    Wednesday, July 3, 2013 10:16 AM
  • User269602965 posted

    Sometimes Excel formatting and datatypes do not translate well across OLEDB.

    Since OLEDB does not seem to support such complex extractions from Excel (you and I have looked high and low), then:

    1. use Excel .NET interop as you have in the past

    or

    2. write VBA function or macro to extract the url address into a TEXT column in EXCEL and then use the TEXT copy of the URL for extraction by OLEDB then convert to Hyperlink on application side.

    But for 2, you will need control of the spreadsheets, which you may or may not have.

     

    Wednesday, July 3, 2013 2:43 PM
  • User-718146471 posted

    Being that these excel files are newer xlsx format, wouldn't they be readable as XML? I won't be able to use interop since I'm in a shared hosting environment. The IT department will not allow excel on web servers here.

    Wednesday, July 3, 2013 3:01 PM
  • User269602965 posted

    XML Perhaps

    Open a test XLSX document with standard text col1 and hyperlink col2,

    populate with test data,

    and examine with text editor like UltraEdit, and XML Parser like Altova XML

    And see if your URL is present and you can understand the XML model.

    BUT can OLEDB walk an XML document??  Oracle can, but what about OLEDB??

     

    Wednesday, July 3, 2013 4:53 PM
  • User269602965 posted

    Well, I did that text.xlsx, and sure it might be XML but it is a compressed (zipped-like) document.

    Saved the test XLSX as HTML file type and got back the URL in TABLE TR and TD tags... but that would be a pain to parse!!

    <tr height=20 style='height:15.0pt'>  

    <td height=20 style='height:15.0pt'>MYTEXT1</td>  

    <td class="xl65"><a href="http://www.thatscool1.org/" target="_parent">THATS_COOL1</a></td>  

    </tr>

    So that little stinker URL must be stored and retrievable in some manner with Excel object model... and if so, why not by OLEDB.

    Wednesday, July 3, 2013 5:25 PM
  • User269602965 posted

    Another option is a mix of OLEDB and INTEROP in a third party DLL.

    I have built some utility apps to manipulate Excel data with SPREADSHEETGEAR (disclosure, I have no business relationship with vendor)

    which I drop into my .NET APP /BIN folder, make reference, 

    the DLL then becomes a MINI-EXCEL without EXCEL installed or running,

    and then you can load a sheet and do interop like functions with RANGE, etc.

    With that, you could use your interop methodology (circa 2010 forum) to retrieve the URL string.

    Wednesday, July 3, 2013 6:03 PM
  • User-718146471 posted

    I may give that some thought. Worst case scenario I could build a console application that would take the xlsx file using interop and then directly import into the DB using that method. For now, I'm going to keep trying under webforms.

    Friday, July 5, 2013 9:21 AM
  • User269602965 posted

    I had to do that with MAPPOINT one time... prepared route maps as JPGs with console app, stored in Oracle database, and then loaded into web app, refreshed nightly.

     

    SpeadsheetGear works with WinForm, WPF, or ASP.NET.

    Friday, July 5, 2013 2:10 PM
  • User-718146471 posted

    Lannie, had any experiences with NPOI? It is free and seems to give the most common functionality for spreadsheet manipulation.

    http://npoi.codeplex.com

    Tuesday, July 9, 2013 8:14 AM
  • User269602965 posted

    No

    But thanks

    ++++++++++

    I also looked in OLEDB documentation and found nothing on parsing HYPERLINK datatype.

    Hint it might be possible in MS Access OLEDB with no code examples,

    but not mentioned for Excel

    Tuesday, July 9, 2013 9:55 AM
  • User-718146471 posted

    Well that stinks. Unless there were a way to convert excel to Access without losing the hyperlink. I have some code that is working but I need to access the hyperlink column of the current row. The code cycles through all rows instead of taking the current row. Here's the code:

     

    for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
                {
                    var hyperlinks = range.Cells.Hyperlinks.OfType<Hyperlink>();
                    for (cCnt = 1; cCnt <= 2; cCnt++)
                    {
                        str = Convert.ToString((range.Cells[rCnt, cCnt] as Excel.Range).Value2);
                        if (cCnt == 2 && rCnt == range.Row) // tried to use this to only specify the current row
                        {
                            foreach (var h in hyperlinks)
                            {
                                str = "text: " + h.TextToDisplay + " address: " + h.Address + "";
                            }
                        }
                    }
                    MessageBox.Show(str);
                }

    Tuesday, July 9, 2013 1:55 PM
  • User-718146471 posted

    Now, just for clarification. My spreadsheet has two columns I'm concerned about, A & B. What I'd like to do is ignore any column in the sheet where B is empty. So if Column B is empty on row 1, skip it and go to the next row and so on. My data fields kind of look like this:

     

    Classes Available
    Level 1
    Fundamentals of Engineering 916842
    Fundamentals of Engineering II 919845
    Fundamentals of Engineering III 949850
    Level 2

     

     So, I want to ignore the rows with Classes Available, empty rows, and Levels, etc. If there is no URL, skip the line.

    Tuesday, July 9, 2013 2:50 PM
  • User-718146471 posted

    Decided since Interop is the only clean way of doing this without having to spend $400, I build a windows forms app to do it. The code that gets the data sorted out is actually pretty cool, so I'm posting it for anyone else who might run across this. Keeping that in mind, this code is for Windows Forms. I'm only posting to help others avoid hours of frustration.

     

    // This is the code that determines the fields, flat text (Value2) versus actual hyperlinks
    // Leaving out extraneous code that does non-related tasks
    
            private void button1_Click(object sender, EventArgs e)
            {
                string ConnStr = string.Empty;
                //Lets me decide in the app config if its dev or prod  
                string ConnChoice = ConfigurationManager.AppSettings["ConnChoice"];
                if (ConnChoice == "DEV")
                {
                    ConnStr = ConfigurationManager.ConnectionStrings["ConnStrDev"].ConnectionString.ToString();
                }
                else
                {
                    ConnStr = ConfigurationManager.ConnectionStrings["ConnStrProd"].ConnectionString.ToString();
                }
                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkBook;
                Excel.Worksheet xlWorkSheet;
                Excel.Range range;
                string textval = string.Empty;
                string textval2 = string.Empty;
                string str = string.Empty;
                int rCnt = 0;
                int cCnt = 0;
                int HyperLink = 2;
                string ExcelFileName = label1.Text.ToString();
    
                xlWorkBook = xlApp.Workbooks.Open(ExcelFileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
                xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
    // get_Item(1) is sheet #1. You could probably put this into an int loop increment so it grabs every sheet
    // providing they have the same format.
    range = xlWorkSheet.UsedRange; str = string.Empty; string myString = string.Empty; for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++) { for (cCnt = 1; cCnt <= HyperLink; cCnt++) { // this .Hyperlinks.Count check for != 0 tells me if Column B is hyperlink if (range.Cells[rCnt, HyperLink].Hyperlinks.Count != 0) { if (cCnt == HyperLink) { textval = Convert.ToString(((range.Cells[rCnt, cCnt] as Excel.Range).Value2)); textval2 = ((range.Cells[rCnt, cCnt] as Excel.Range).Cells.Hyperlinks[1].Address); } else { str = Convert.ToString((range.Cells[rCnt, cCnt] as Excel.Range).Value2); } } } // str is the class title // textval is the class number // textval2 is the embedded URL // Do your db insert/update work here str = string.Empty; textval = string.Empty; textval2 = string.Empty; } } // Close the Workbook and Excel xlWorkBook.Close(true, null, null); xlApp.Quit(); // Kill off all objects and do garbage collection releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); // Leave the importer application since the work is done Application.Exit(); } private void releaseObject(object obj) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(obj); obj = null; } catch (Exception ex) { obj = null; MessageBox.Show("Unable to release the Object " + ex.ToString()); } finally { GC.Collect(); } } private void btnOpenFile_Click(object sender, EventArgs e) { OpenFileDialog dialog = new OpenFileDialog(); dialog.Filter = "Excel 2007/2010 File (*.xlsx)|"; dialog.InitialDirectory = @"C:\"; dialog.Title = "Select excel file"; dialog.ShowDialog(); foreach (var file in dialog.FileNames) { label1.Text = file.ToString(); } button1.Visible = true; } }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 11, 2013 12:27 PM