Converting object datatype in vb.net

Question

I am new to VB.net 2010. I am trying to read and write from excel for my project. Currently I am facing issues of datatype conversions. I cannot convert object datatype to integer datatype for my calculations. It keeps on giving me error that: "Conversion from string "Serial Number Of Layup" to type 'Integer' is not valid"

Here is my code. (Before Checking the code be sure to add references, Project-Add References-COM Object-Microsoft Excel COM object 12.0)

Imports System.IO
Imports Excel = Microsoft.Office.Interop.Excel

Imports System.Drawing

Imports System.Windows.Forms

Imports Microsoft.Office

Imports Microsoft.Office.Interop.Excel


Public NumberofLayupsinStackingLibrary As Object

Public path As String = Directory.GetCurrentDirectory()

Public xlApp As Excel.Application

Public xlWorkBook As Excel.Workbook

Public xlWorkSheet As Excel.Worksheet


xlApp = New Excel.ApplicationClass

xlWorkBook = xlApp.Workbooks.Open(path + "\StackingLibrary")

xlWorkSheet = xlWorkBook.Worksheets("sheet1")

NumberofLayupsinStackingLibrary = xlWorkSheet.Cells(1, 1).value

Dim alpha As Integer = CInt(NumberofLayupsinStackingLibrary) - 1

xlWorkBook.Close(SaveChanges:=False)

xlApp.Quit()

Please tell me what to do. I shall be grateful.

Answer 1

Use the following code to read from a Excel sheet and write it to a gridview.

> using System; using System.Collections.Generic; using System.Linq;
> using System.Web; using System.Web.UI; using
> System.Web.UI.WebControls; using System.Data; using System.Data.Odbc;
> using System.Data.OleDb; using System.IO; using
> System.Web.Script.Services; using System.Web.Services;
> 
> namespace NEWCICR {
>     public partial class newer : System.Web.UI.Page
>     {
>         DataTable dt = new DataTable();
> 
> 
>         //Declare Variable (property)
> 
>         string currFilePath = string.Empty; //File Full Path
>         string currFileExtension = string.Empty;  //File Extension
> 
>         //Page_Load Event, Register Button Click Event
> 
>         protected void Page_Load(object sender, EventArgs e)
>         {
>        this.btnRead.Click += new EventHandler(btnRead_Click);
> 
>         }
> 
>         //Button Click Event  
> 
> 
>         protected void btnRead_Click(object sender, EventArgs e)
>         {
> 
>             Upload();  //Upload File Method
>             if (this.currFileExtension == ".xlsx" || this.currFileExtension == ".xls")
>             {
>                 DataTable dt = ReadExcelToTable(currFilePath);  //Read Excel File (.XLS and .XLSX Format)
>             }
>             else if (this.currFileExtension == ".csv")
>             {
>                 DataTable dt = ReadExcelWithStream(currFilePath);  //Read .CSV File
>             }
> 
> 
> 
>         }
> 
>         ///<summary>
> 
>         ///Upload File to Temporary Category
> 
>         ///</summary>
> 
>         private void Upload()
>         {
>             HttpPostedFile file = this.fileSelect.PostedFile;
>             string fileName = file.FileName;
>             string tempPath = System.IO.Path.GetTempPath();   //Get Temporary File Path
>             fileName = System.IO.Path.GetFileName(fileName); //Get File Name (not including path)
>             this.currFileExtension = System.IO.Path.GetExtension(fileName);   //Get File Extension
>             this.currFilePath = tempPath + fileName; //Get File Path after Uploading and Record to Former Declared Global Variable
>             file.SaveAs(this.currFilePath);  //Upload
>         }
> 
>         ///<summary>
>         ///Method to Read XLS/XLSX File
>         ///</summary>
>         ///<param name="path">Excel File Full Path</param>
>         ///<returns></returns>
> 
>         private DataTable ReadExcelToTable(string path)
>         {
> 
>             //Connection String
> 
>             string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended
> Properties='Excel 8.0;HDR=NO;IMEX=1';";   // Extra blank space cannot
> appear in Office 2007 and the last version. And we need to pay
> attention on semicolon.
> 
>             // string connstring = "Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended
> Properties='Excel 8.0;HDR=NO;IMEX=1';";  //This connection string is
> appropriate for Office 2007 and the older version. We can select the
> most suitable connection string according to Office version or our
> program.
> 
>             using (OleDbConnection conn = new OleDbConnection(connstring))
>             {
>                 conn.Open();
>                 DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null,
> null, null, "Table" });  //Get All Sheets Name
>                 string firstSheetName = sheetsName.Rows[0][2].ToString();   //Get the First Sheet Name
>                 string sql = string.Format("SELECT * FROM [{0}],firstSheetName");
>                 //Query String
>                 OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
>                 DataSet set = new DataSet();
>                 ada.Fill(set);
>                 return set.Tables[0];
>             }
>         }
> 
>         ///<summary>
>         ///Method to Read CSV Format
>         ///</summary>
>         ///<param name="path">Read File Full Path</param>
>         ///<returns></returns>
> 
> 
>         private DataTable ReadExcelWithStream(string path)
>         {
> 
>             bool isDtHasColumn = false;   //Mark if DataTable Generates Column
>             StreamReader reader = new StreamReader(path, System.Text.Encoding.Default);  //Data Stream
>             while (!reader.EndOfStream)
>             {
>                 string message = reader.ReadLine();
>                 string[] splitResult = message.Split(new char[] { ',' }, StringSplitOptions.None);  //Read One Row and Separate by Comma,
> Save to Array
>                 DataRow row = dt.NewRow();
>                 for (int i = 0; i < splitResult.Length; i++)
>                 {
>                     if (!isDtHasColumn) //If not Generate Column
>                     {
>                         dt.Columns.Add("column" + i, typeof(string));
>                     }
>                     row[i] = splitResult[i];
>                 }
>                 dt.Rows.Add(row); 
>                 isDtHasColumn = true;  //Mark the Existed Column after Read the First Row, Not Generate Column after Reading Later Rows
>             }
> 
>             
>             GridView1.DataSource = dt;
>             GridView1.DataBind();
>             DataTable js = dt;
> 
> 
>             return dt;
>         }
> 
> 
> 
>     }
> 
> }

Answer 2

The error message

Conversion from string "Serial Number Of Layup" to type 'Integer' is not valid

basically says it all:

You are trying to convert the value "Serial Number Of Layup" to integer. Obviously, this is not possible. What numeric value should this text represent?

Looking at your code reveals that you have only one integer conversion in there: CInt(NumberofLayupsinStackingLibrary). Reading further up your code shows that NumberofLayupsinStackingLibrary is filled from xlWorkSheet.Cells(1, 1).

Conclusion: Cell 1/1 in your worksheet contains the text Serial Number Of Layup, which is not an integer and cannot be converted to one. Your program behaves as expected.