字符串中的Excel工作表值 [英] Excel sheet values in a string

问题描述

如何将Excel工作表值存储在字符串中(不包括手机号码)
我有Windows应用程序，它具有手机号码字段.还有一个Excel表格，该表格在一列中包含手机号码.我必须从excel工作表中获取这些移动电话并将其存储在字符串中.

解决方案

使用Excel Interop访问工作表和所需的单元格.

< pre>上有关于该主题的大量资源.

< pre>
私有字符串strConnectionString，SelectString;
私有OleDbCommand objCmdSelect;
私有OleDbDataAdapter objAdapter;
私有OleDbConnection objConn;
私有数据集dsUpload，ds;
bool strRet;
</pre>

< pre>
objConn =新的OleDbConnection(strConnectionString);
objConn.Open();
DataTable SchemaTable = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables，new object [] {null，null，null，"TABLE"});
objConn.Close();
字符串ExcelFirstSheet =";
字符串NotUploadedFiles =";
String [] excelSheets = new String [SchemaTable.Rows.Count];
</pre>

< pre>
strRet = readExcelFile(SelectString);
</pre>

< pre>
私人布尔readExcelFile(string SelectString)
{
ds = new DataSet();
ds.Clear();
int idx = strFileUpldName.LastIndexOf(.");
如果(idx！= -1)
{
fileExtension = strFileUpldName.Substring(idx +1);
}
如果(fileExtension =="xls")
{
试试
{
objConn =新的OleDbConnection(strConnectionString);
objConn.Open();
OleDbCommand objCmdSelect =新的OleDbCommand(SelectString，objConn);
OleDbDataAdapter objAdapter1 =新的OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(ds);
会话["dsCRM"] = ds;
objConn.Close();
objConn.Dispose();
返回true;
}
catch(异常e)
{
如果(objConn！= null)
{
如果(objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
objConn = null;
返回false;
}
}
其他
{
返回false;
}
}
</pre>

这里是一些示例代码，是VB，但可以轻松转换为C#:

您需要添加对Microsoft.Office.Interop.Excel的引用.

<code>Dim oXL As Microsoft.Office.Interop.Excel.Application
Dim oBook As Microsoft.Office.Interop.Excel.Workbook
Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim vValue As Object

oXL = New Microsoft.Office.Interop.Excel.Application
oBook = oXL.Workbooks.Open(OpenFileDialog1.FileName)
oSheet = oBook.Worksheets("Sheet1")
''Here you have cell value, just build a loop to get the data you want
''This will loop through all rows in the first column

For i As Integer = 0 To oSheet.Rows.Count
vValue = vValue + ";" + oSheet.Cells(1, i).Value 
Next


myTextBox.Text = vValue</code>

How to store the excel sheet values in a string (ex mobile numbers)
I have windows application which has the mobile number field .and an excel sheet which contains mobile numbers in a column. I have to fetch those mobile numbers from excel sheet and store them in a string.

解决方案

Use Excel Interop to access the worksheet and cells necessary. There are a multitude of resources on the subject available here and elsewhere.

<pre>
private string strConnectionString, SelectString;
private OleDbCommand objCmdSelect;
private OleDbDataAdapter objAdapter;
private OleDbConnection objConn;
private DataSet dsUpload, ds;
bool strRet;
</pre>

<pre>
objConn = new OleDbConnection(strConnectionString);
objConn.Open();
DataTable SchemaTable = objConn.GetOleDbSchemaTable (OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
objConn.Close();
string ExcelFirstSheet = "";
string NotUploadedFiles = "";
String[] excelSheets = new String[SchemaTable.Rows.Count];
</pre>

<pre>
strRet = readExcelFile(SelectString);
</pre>

<pre>
private bool readExcelFile(string SelectString)
{
ds = new DataSet();
ds.Clear();
int idx = strFileUpldName.LastIndexOf(".");
if (idx != -1)
{
fileExtension = strFileUpldName.Substring(idx + 1);
}
if (fileExtension == "xls")
{
try
{
objConn = new OleDbConnection(strConnectionString);
objConn.Open();
OleDbCommand objCmdSelect = new OleDbCommand (SelectString,objConn);
OleDbDataAdapter objAdapter1 = new OleDbDataAdapter();
objAdapter1.SelectCommand = objCmdSelect;
objAdapter1.Fill(ds);
Session["dsCRM"] = ds;
objConn.Close();
objConn.Dispose();
return true;
}
catch (Exception e)
{
if (objConn != null)
{
if (objConn.State == ConnectionState.Open)
{
objConn.Close();
}
}
objConn = null;
return false;
}
}
else
{
return false;
}
}
</pre>

Here''s some sample code, is VB but is easily convertible to C#:

You need to add a reference to Microsoft.Office.Interop.Excel.

<code>Dim oXL As Microsoft.Office.Interop.Excel.Application
Dim oBook As Microsoft.Office.Interop.Excel.Workbook
Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim vValue As Object

oXL = New Microsoft.Office.Interop.Excel.Application
oBook = oXL.Workbooks.Open(OpenFileDialog1.FileName)
oSheet = oBook.Worksheets("Sheet1")
''Here you have cell value, just build a loop to get the data you want
''This will loop through all rows in the first column

For i As Integer = 0 To oSheet.Rows.Count
vValue = vValue + ";" + oSheet.Cells(1, i).Value 
Next


myTextBox.Text = vValue</code>

这篇关于字符串中的Excel工作表值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！