『壹』 如何打开一个excel文件读取多个文件
准备一下工具和原料：
1.一台Windows7系统笔记本电脑。
2.软件Excel 2007。
excel表格批量加载方法：
1.选择要加载的excel表格，点击鼠标右键，选择打开。
『贰』 如何用excel按条件来读取txt文件中的数据
既然是交通数据，涉及国民安全。那么，我来试着帮你扣我一五九六三九七零
『叁』 EXCEL 自动读取指定文件内容
文件中的C1格中数值？还是文件中Sheet1中的C1单元格数值？在B2单元格输入以下公式，然后版向下填充公权式=INDIRECT("E:\ABC2014["&LEFT(A$1,3)&–RIGHT(A1)-3+ROW(A1)&"]Sheet1!C1")
『肆』 excel中读取另一excel中的文件信息
在B4单元格输入以下公式，然后向下填充公式=VLOOKUP(A4,INDIRECT("'["&B$1&"*.xls]Sheet1!'A:B"),2,0)公式表示：通过INDIRECT函数，引用"'["&B$1&"*.xls]Sheet1!'A:B"字符串构成的查找引用区域，并通过VLOOKUP函数在其A列精确匹配与A4单元格相同的单元格，并返回对应B列的数据。
『伍』 如何读取excel中的文本文件
使用以下代码即可实现：Sub test() Dim reg As Object Dim wjm As String Set reg = CreateObject("vbscript.regexp") With reg .Global = True .Pattern = "{""id"".*?}" End With wjm = Dir(ThisWorkbook.Path & "\*.txt") m = 2 Do While wjm <> "" txtm = ThisWorkbook.Path & "\" & wjm Open txtm For Input As #1 Do While Not EOF(1) Line Input #1, ss If Left(Trim(ss), 4) = "list" Then ss = Replace(ss, Chr(13), Chr(10) & Chr(13)) Set mathcs = reg.Execute(ss) For i = 0 To mathcs.Count – 1 s = Replace(mathcs(i), """", "") s = Replace(s, ":", ",") xm = Split(s, ",") For j = 1 To 3 Cells(m, j + 1) = xm(j * 2 – 1) Next For j = 4 To 5 Cells(m, j + 1) = xm(j * 2 + 1) Next Cells(m, 1) = wjm m = m + 1 Next Exit Do End If Loop Close #1 wjm = Dir LoopEnd Sub
『陆』 excel中怎样读取另一个文件
第一种方法:打开另一个文件,,paste:触发按钮单机事件,VBA如下:[code=vb] Private Sub CommandButton1_Click()Dim towb As WorkbookDim tows As WorksheetDim torow As IntegerDim fromwb As WorkbookDim fromws As WorksheetDim fromrow As IntegerDim projectnameDim iDim openfiles 'input the filepath of your selectionSet towk = Application.ActiveWorkbookSet tows = ActiveSheettorow = [a65536].End(3).Row + 1 'get the last row of data by column A 'get the active worksheet 'call openfile functionopenfiles = openfile()If openfiles <> "" ThenSet fromwb = Application.Workbooks.Open(openfiles)Set fromws = fromwb.Sheets("IPIS") 'set ID tows.Activate tows.Cells(torow, 1) = tows.Cells(torow – 1, 1) + 1 'set "Go/No Go" tows.Activate tows.Cells(torow, 2) = "Go" 'set "Project Name" fromwb.Activate fromws.Activate fromws.Cells(5, 1).Select Selection.Copy tows.Activate tows.Cells(torow, 7).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False projectname = tows.Cells(torow, 7) 'set "Customer" by projectname tows.Activate tows.Cells(torow, 4) = Split(projectname, " ", 2)(0) ' range（"A1"） Like "*1234*" End Iftowk.ActivateEnd SubFunction openfile() As Variant 'Declare a variable as a FileDialog object. Dim fd As FileDialog 'Create a FileDialog object as a File Picker dialog box. Set fd = Application.FileDialog(msoFileDialogFilePicker) 'Declare a variable to contain the path 'of each selected item. Even though the path is aString, 'the variable must be a Variant because For Each…Next 'routines only work with Variants and Objects. Dim vrtSelectedItem As Variant 'Use a With…End With block to reference the FileDialog object. With fd 'Allow the selection of multiple files. .AllowMultiSelect = True 'Use the Show method to display the file picker dialog and return the user's action. 'If the user presses the button… If .Show = -1 Then 'Step through each string in the FileDialogSelectedItems collection. For Each vrtSelectedItem In .SelectedItems 'vrtSelectedItem is aString that contains the path of each selected item. 'You can use any file I/O functions that you want to work with this path. 'This example displays the path in a message box. MsgBox "Selected item's path: " & vrtSelectedItem openfile = vrtSelectedItem Next 'If the user presses Cancel… Else End If End With 'Set the object variable to Nothing. Set fd = NothingEnd Function[/code]总结:这种方法可以实现,但是需要打开对应的选择文件才行.第二种方法:利用引用来显示另一个表的内容,不打开文件,VBA代码如下:触发按钮单机事件:[code=vb] Private Sub CommandButton1_Click()Dim towb As WorkbookDim tows As WorksheetDim torow As IntegerDim fromwb As WorkbookDim fromws As WorksheetDim fromrow As IntegerDim projectnameDim iDim openfiles 'input the filepath of your selectionDim filenameSet towk = Application.ActiveWorkbookSet tows = ActiveSheettorow = [a65536].End(3).Row + 1 'get the last row of data by column A 'get the active worksheetApplication.ScreenUpdating = False 'call openfile functionopenfiles = openfile()If openfiles <> "" Then'Set fromwb = Application.Workbooks.Open(openfiles)'Set fromws = fromwb.Sheets("IPIS")'set ID tows.Activate tows.Cells(torow, 1) = tows.Cells(torow – 1, 1) + 1 'set "Go/No Go" tows.Activate tows.Cells(torow, 2) = "Go" 'set "Project Name" filename = dealstr(openfiles) ActiveSheet.Cells(torow, 7).Formula = "='" & filename & "IPIS'!$A$5" projectname = tows.Cells(torow, 7) 'set "Customer" by projectname tows.Activate tows.Cells(torow, 4) = Split(projectname, " ", 2)(0) ' range（"A1"） Like "*1234*" End Iftowk.ActivateApplication.ScreenUpdating = TrueEnd SubFunction dealstr(f As Variant) As Variant Z = Len(f) For ii = Z To 1 Step -1 If Mid(f, ii, 1) = "\" Then Exit For End If Next ii For i = Len(f) To y Step -1 If Mid(f, i, 1) <= "z" Then Exit For End If Next i a = Mid(f, ii + 1, i – ii) b = Mid(f, 1, ii) dealstr = b & "[" & a & "]"End FunctionFunction openfile() As Variant 'Declare a variable as a FileDialog object. Dim fd As FileDialog 'Create a FileDialog object as a File Picker dialog box. Set fd = Application.FileDialog(msoFileDialogFilePicker) 'Declare a variable to contain the path 'of each selected item. Even though the path is aString, 'the variable must be a Variant because For Each…Next 'routines only work with Variants and Objects. Dim vrtSelectedItem As Variant 'Use a With…End With block to reference the FileDialog object. With fd 'Allow the selection of multiple files. .AllowMultiSelect = True 'Use the Show method to display the file picker dialog and return the user's action. 'If the user presses the button… If .Show = -1 Then 'Step through each string in the FileDialogSelectedItems collection. For Each vrtSelectedItem In .SelectedItems 'vrtSelectedItem is aString that contains the path of each selected item. 'You can use any file I/O functions that you want to work with this path. 'This example displays the path in a message box. openfile = vrtSelectedItem Next 'If the user presses Cancel… Else End If End With 'Set the object variable to Nothing. Set fd = NothingEnd Function[/code]总结:这种方法,不用再打开选择的文件,但是,利用引用的方式显示另一个文件的内容,显得有些藕断丝连,不方便.第三种方法:利用ExecuteExcel4Macro,不打开文件就能读取内容,不再是引用的关系,VBA代码如下:触发按钮单机事件:[code=vb]Private Sub CommandButton1_Click()Dim towb As WorkbookDim tows As WorksheetDim torow As IntegerDim cnn As New ADODB.ConnectionDim rs As New ADODB.RecordsetDim i As IntegerDim SQL As String, cnnStr As String, sFileName As StringDim wb As WorkbookDim ws As WorksheetDim projectnameDim openfiles 'input the filepath of your selectionDim filenameSet towk = Application.ActiveWorkbookSet tows = ActiveSheettorow = [a65536].End(3).Row + 1 'get the last row of data by column A 'get the active worksheetApplication.ScreenUpdating = False 'call openfile functionopenfiles = openfile()If openfiles <> "" Then If GetValue(getpathname(openfiles), getfilename(openfiles), "IPIS", "A2") = "error" Then MsgBox "选取文件有误" Else 'set ID tows.Activate tows.Cells(torow, 1) = tows.Cells(torow – 1, 1) + 1 'set "Go/No Go" tows.Activate tows.Cells(torow, 2) = "Go" 'set "Project Name" tows.Cells(torow, 7) = GetValue(getpathname(openfiles), getfilename(openfiles), "IPIS", "A5") projectname = tows.Cells(torow, 7) 'set "Customer" by projectname tows.Activate tows.Cells(torow, 4) = Split(projectname, " ", 2)(0) End IfEnd IfApplication.ScreenUpdating = TrueEnd SubPrivate Function GetValue(path, filename, sheet, ref) ' 从关闭的工作薄返回值 Dim MyPath As String '确定文件是否存在 If Right(path, 1) <> "\" Then path = path & "\" If Dir(path & filename) = "" Then GetValue = "error" Exit Function End If '创建公式 MyPath = "'" & path & "[" & filename & "]" & sheet & "'!" & Range(ref).Range("A1").Address(, , xlR1C1) '执行EXCEL4宏函数 GetValue = Application.ExecuteExcel4Macro(MyPath)End FunctionFunction openfile() As Variant'Declare a variable as a FileDialog object. Dim fd As FileDialog 'Create a FileDialog object as a File Picker dialog box. Set fd = Application.FileDialog(msoFileDialogFilePicker) 'Declare a variable to contain the path 'of each selected item. Even though the path is aString, 'the variable must be a Variant because For Each…Next 'routines only work with Variants and Objects. Dim vrtSelectedItem As Variant 'Use a With…End With block to reference the FileDialog object. With fd 'Allow the selection of multiple files. .AllowMultiSelect = True 'Use the Show method to display the file picker dialog and return the user's action. 'If the user presses the button… If .Show = -1 Then 'Step through each string in the FileDialogSelectedItems collection. For Each vrtSelectedItem In .SelectedItems 'vrtSelectedItem is aString that contains the path of each selected item. 'You can use any file I/O functions that you want to work with this path. 'This example displays the path in a message box. openfile = vrtSelectedItem Next 'If the user presses Cancel… Else End If End With 'Set the object variable to Nothing. Set fd = NothingEnd FunctionFunction getfilename(f As Variant) As Variant Z = Len(f) For ii = Z To 1 Step -1 If Mid(f, ii, 1) = "\" Then Exit For End If Next ii For i = Len(f) To y Step -1 If Mid(f, i, 1) <= "z" Then Exit For End If Next i getfilename = Mid(f, ii + 1, i – ii)End FunctionFunction getpathname(f As Variant) As Variant Z = Len(f) For ii = Z To 1 Step -1 If Mid(f, ii, 1) = "\" Then Exit For End If Next ii For i = Len(f) To y Step -1 If Mid(f, i, 1) <= "z" Then Exit For End If Next i getpathname = Mid(f, 1, ii)End Function[/code]总结:感觉还是这种方式比较好~
『柒』 如何在excel表格中读取txt文件
点击最上方一行的“数据”——导入外部数据——导入数据,,,然后选择你要导入的txt文件，根据提示设置你要显示的格式与行列，都可以选择设置
『捌』 vc++怎样从excel文件中读写数据
转载：首先利用Visual C++ 6.0，建立一个MFC基于对话框的工程项目，共享DLL，Win32平台。工程名称ExcelTest。在主对话框中加入一个按钮，ID IDC_EXCELTESTCaption Test Excel双击该按钮，增加成员函数void CExcelTestDlg::OnExceltest()。在BOOL CExcelTestApp::InitInstance()中，dlg.DoModal();之前增加代码：if (CoInitialize(NULL)!=0){AfxMessageBox("初始化COM支持库失败!");exit(1);}在return FALSE; 语句前，加入：CoUninitialize();选择Menu->View->ClassWizade，打开ClassWizade窗口，选择Add Class->From a type library，选择D:\Program Files\Microsoft Office\office\Excel9.OLB(D:\Program Files\Microsoft Office\是本机上Microsoft Office 2000的安装目录，可根据个人机器上的实际安装目录修改)。选择_Application、Workbooks、_Workbook、 Worksheets、_Worksheet、Range，加入新类，分别为_Application、Workbooks、_Workbook、 Worksheets、_Worksheet、Range，头文件Excel9.h，源文件Excel9.cpp。在ExcelTestDlg.cpp文件的头部，#include "ExcelTestDlg.h"语句之下，增加 ：#include "comdef.h"#include "Excel9.h"在void CExcelTestDlg::OnExceltest() 函数中增加如下代码：void CExcelTestDlg::OnExceltest(){_Application ExcelApp;Workbooks wbsMyBooks;_Workbook wbMyBook;Worksheets wssMysheets;_Worksheet wsMysheet;Range rgMyRge;//创建Excel 2000服务器(启动Excel)if (!ExcelApp.CreateDispatch("Excel.Application",NULL)){AfxMessageBox("创建Excel服务失败!");exit(1);}//利用模板文件建立新文档wbsMyBooks.AttachDispatch(ExcelApp.GetWorkbooks(),true);wbMyBook.AttachDispatch(wbsMyBooks.Add(_variant_t("g:\\exceltest\\MyTemplate.xlt")));//得到WorksheetswssMysheets.AttachDispatch(wbMyBook.GetWorksheets(),true);//得到sheet1wsMysheet.AttachDispatch(wssMysheets.GetItem(_variant_t("sheet1")),true);//得到全部Cells，此时,rgMyRge是cells的集合rgMyRge.AttachDispatch(wsMysheet.GetCells(),true);//设置1行1列的单元的值rgMyRge.SetItem(_variant_t((long)1),_variant_t((long)1),_variant_t("This Is A Excel Test Program!"));//得到所有的列rgMyRge.AttachDispatch(wsMysheet.GetColumns(),true);//得到第一列rgMyRge.AttachDispatch(rgMyRge.GetItem(_variant_t((long)1),vtMissing).pdispVal,true);//设置列宽rgMyRge.SetColumnWidth(_variant_t((long)200));//调用模板中预先存放的宏ExcelApp.Run(_variant_t("CopyRow"),_variant_t((long)10),vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing,vtMissing);//打印预览wbMyBook.SetSaved(true);ExcelApp.SetVisible(true);wbMyBook.PrintPreview(_variant_t(false));//释放对象rgMyRge.ReleaseDispatch();wsMysheet.ReleaseDispatch();wssMysheets.ReleaseDispatch();wbMyBook.ReleaseDispatch();wbsMyBooks.ReleaseDispatch();ExcelApp.ReleaseDispatch();}
『玖』 [c++]推荐一个可以读写EXCEL的方法！
想要通过ODBC直接读、写Excel表格文件，首先，应确保ODBC中已安装有Excel表格文件的驱动"MICROSOFT EXCEL DRIVER (*.XLS)"。然后，可根据下面步骤进行：1. 在StdAfx.h文件中加入：include <afxdb.h> include <odbcinst.h>2. 通过ODBC直接创建Excel文件(暂定文件名：Demo.xls)//创建并写入Excel文件void CRWExcel::WriteToExcel(){CDatabase database;CString sDriver = "MICROSOFT EXCEL DRIVER (*.XLS)"; // Excel安装驱动CString sExcelFile = "c:\\demo.xls"; // 要建立的Excel文件CString sSql;TRY{// 创建进行存取的字符串sSql.Format("DRIVER={%s};DSN='';FIRSTROWHASNAMES=1;READONLY=FALSE;CREATE_DB=\"%s\";DBQ=%s",sDriver, sExcelFile, sExcelFile);// 创建数据库 (既Excel表格文件)if( database.OpenEx(sSql,CDatabase::noOdbcDialog) ){// 创建表结构(姓名、年龄)sSql = "CREATE TABLE demo (Name TEXT,Age NUMBER)";database.ExecuteSQL(sSql);// 插入数值sSql = "INSERT INTO demo (Name,Age) VALUES ('徐景周',26)";database.ExecuteSQL(sSql);sSql = "INSERT INTO demo (Name,Age) VALUES ('徐志慧',22)";database.ExecuteSQL(sSql);sSql = "INSERT INTO demo (Name,Age) VALUES ('郭徽',27)";database.ExecuteSQL(sSql);} // 关闭数据库database.Close();}CATCH_ALL(e){TRACE1("Excel驱动没有安装: %s",sDriver);}END_CATCH_ALL;}3. 通过ODBC直接读取Excel文件(暂定文件名：Demo.xls)// 读取Excel文件void CRWExcel::ReadFromExcel() {CDatabase database;CString sSql;CString sItem1, sItem2;CString sDriver;CString sDsn;CString sFile = "Demo.xls"; // 将被读取的Excel文件名// 检索是否安装有Excel驱动 "Microsoft Excel Driver (*.xls)" sDriver = GetExcelDriver();if (sDriver.IsEmpty()){// 没有发现Excel驱动AfxMessageBox("没有安装Excel驱动!");return;}// 创建进行存取的字符串sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s", sDriver, sFile);TRY{// 打开数据库(既Excel文件)database.Open(NULL, false, false, sDsn);CRecordset recset(&database);// 设置读取的查询语句.sSql = "SELECT Name, Age " "FROM demo " "ORDER BY Name ";// 执行查询语句recset.Open(CRecordset::forwardOnly, sSql, CRecordset::readOnly);// 获取查询结果while (!recset.IsEOF()){//读取Excel内部数值recset.GetFieldValue("Name ", sItem1);recset.GetFieldValue("Age", sItem2);// 移到下一行recset.MoveNext();}// 关闭数据库database.Close();}CATCH(CDBException, e){// 数据库操作产生异常时…AfxMessageBox("数据库错误: " + e->m_strError);}END_CATCH;}// 获取ODBC中Excel驱动CString CRWExcel::GetExcelDriver(){char szBuf[2001];WORD cbBufMax = 2000;WORD cbBufOut;char *pszBuf = szBuf;CString sDriver;// 获取已安装驱动的名称(涵数在odbcinst.h里)if (!SQLGetInstalledDrivers(szBuf, cbBufMax, &cbBufOut))return "";// 检索已安装的驱动是否有Excel…do{if (strstr(pszBuf, "Excel") != 0){//发现 !sDriver = CString(pszBuf);break;}pszBuf = strchr(pszBuf, '\0') + 1;}while (pszBuf[1] != '\0');return sDriver;}
『拾』 EXCEL 在线读取TXT文件
将代码改成这个：
PrivateSubCommandButton1_Click()Range("A3:Q8000").ClearDimXmlHttpAsObject,tmpAsStringSetXmlHttp=CreateObject("MSXML2.XMLHTTP")WithXmlHttp.Open"GET","http://www.17500.cn/getData/3d.TXT",False.sendtmp=.responsetextEndWithDimarr,s,brr(),tAsStringarr=Split(tmp,Chr(10))ReDimbrr(0ToUBound(arr))Fori=0ToUBound(arr)IfLen(arr(i))>0Thens=Split(arr(i),"")Forj=0To4t=t&s(j)&","Nextt=Left(t,Len(t)-1)brr(i)=tt=""EndIfNextCells(2,1)="开奖期号"Cells(2,2)="开奖日期"Cells(2,3)="开"Cells(2,4)="奖"Cells(2,5)="号"Fori=0ToUBound(brr)Cells(i+3,1).Resize(1,5)=Split(brr(i),",")NextEndSub
未经允许不得转载：山九号 » excel读写文件|EXCEL 在线读取TXT文件
