PB读取EXCEL文件

string path_name,file_nameif GetFileOpenName( "选择导入的文件... ", path_name, file_name, "XLS ", "Excel Files (*.XLS),*.XLS ") = 1 thenelse returnend ifif fileexists(path_name) thenelse returnend iflong i,ll_row,i_returnoleobject xlapp,xlsubxlapp = CREATE OLEObjecti_return = xlapp.ConnectToNewObject( "Excel.Application")if i_return <> 0 then MessageBox( "错误！ ", "无法启动excel程序！ ") returnend ifxlapp.application.workbooks.open(path_name)xlsub = xlapp.application.activeworkbook.worksheets[2]do while len(string(xlsub.cells(i,9).value)) > 0goods_code = trim(string(xlsub.cells(i,9).value))goods_num = double(xlsub.cells(i,5).value)goods_price = double(xlsub.cells(i,6).value)goods_sum_value = round(double(xlsub.cells(i,7).value),2)goods_name = trim(string(xlsub.cells(i,1).value))i = i + 1loopxlapp.DisplayAlerts = Falsexlapp.Workbooks.closexlapp.DisplayAlerts = truexlapp.DisconnectObject()Destroy xlapp

 

以下是凌乱的代码：

Ole_object.Workbooks.add//新建一个Excel文件Ole_object.Workbooks.Open( "FileName ")//打开一个已存在Excel文件Ole_object.ActiveWorkBook.Sheets( "SheetName ").Select//选择文件中一个工作表Ole_object.Application.Run( "MacroName ")//运行宏Ole_object.Application.Visible=TRUE//Excel文件可见Ole_object.Application.ScreenUpdating=true//设置可见属性②格式设置Ole_object.ActiveSheet.Columns( "A:U ").AutoFit//列宽自动调整Ole_object.ActiveSheet.Columns( "A:Z ").ColumnWidth=6.75//列宽Ole_object.ActiveSheet.Rows( "1:100 ").RowHeight=12//行高Ole_object.Application.StandardFont= "ArialNarrow ";//设置字体Ole_object.Application.StandardFontSize= "8 "//设置字号Ole_object.ActiveSheet.Font.Size= "8 "//设置字号Ole_object.ActiveSheet.Font.Bold=True//粗体Ole_object.ActiveSheet.Font.Italic=True//斜体Ole_object.ActiveSheet.Font.Underline=True//下划线Ole_object.ActiveSheet.Font.StrikeThrough=True//删除线Ole_object.ActiveSheet.HorizontalAlignment=3//水平：4靠右；3居中；2靠左Ole_object.ActiveSheet.VerticalAlignment=2//垂直：3靠下；2居中；1靠上Ole_object.ActiveSheet.cells(2,1).font.name=’黑体’//设置字体Ole_object.ActiveSheet.cells(2,1).font.size=25//设置字体大小③工作区域操作：Ole_object.ActiveSheet.Range( "A1:Z10 ").Property=value//设置一个工作区域内的属性值Ole_object.ActiveSheet.Range( "A1:Z10 ").Merge//合并单元格Ole_object.ActiveSheet.Range( "A1:Z10 ").WrapText=False//自动换行禁止Ole_object.ActiveSheet.Range( "A1:Z10 ").Borders(b_type).Weight=i_val//b_type:1左边界；2右边界；3上边界；4下边界；5左上倾斜；6右上倾斜(以上为对单元格的操作)7左边界；8上边界；9下边界；10右边界；11内部垂直边界；12内部水平边界（以上为对区域的操作）//i_val:0无边界线；然后1，2，3。。。依次边界线加粗Ole_object.ActiveSheet.Range( "A1:Z10 ").Borders(b_type).linestyle=1//1与7—细实、2—细虚、4—点虚、9—双细实线④赋值操作Ole_object.ActiveSheet.range( "A1:Z10 ").Cells.value=“姓名”Ole_object.ActiveSheet.range( "A1:Z10 ").Cells.value=1Ole_object.ActiveSheet.range( "A1:Z10 ").Cells.value=“2003-01-01”Ole_object.ActiveSheet.Range( "A1:Z10 ").Cells(1,2).value=“年龄”//区域内一个单元格的赋值Ole_object.ActiveSheet.Range( "A1:Z1 ").Cells(1,1).value=“SUM(RC[-9]:RC[-1])”ls_filename = "C:/alt_report/wf_alt_report.xls "if FileExists(ls_filename) then FileDelete(ls_filename) OleObject Loo_ExcelLoo_Excel=create oleobjectif Loo_Excel.ConnectToNewObject( "Excel.Application ") <> 0 then messagebox( '提示 ', '系統未安裝Excel,無法運行此操作! ')returnend if Loo_Excel.Application.Workbooks.Add()Loo_Excel.Application.Workbooks(1).worksheets(1)Loo_Excel.Application.Visible = TrueLoo_Excel.Application.ScreenUpdating = false // '正在處理表頭...... 'Loo_Excel.Range( "A1:AB1 ").SelectLoo_Excel.Selection.MergeCells = TrueLoo_Excel.Selection.HorizontalAlignment = 3Loo_Excel.Selection.VerticalAlignment = 3Loo_Excel.Selection.Font.Bold = TrueLoo_Excel.Selection.Font.Size = 14Loo_Excel.Selection.RowHeight = 28.50Loo_Excel.ActiveCell.FormulaR1C1 = dw_3.describe( "ls_title "+ ".text ")///写标头Loo_Excel.application.workbooks(1).worksheets(1).cells(3, 1 ).value = "排單號碼 " // dw_2.describe( "po_no "+ ".name "+ "_t.text ") Loo_Excel.application.workbooks(1).worksheets(1).cells(3, 2 ).value = "客人款號 "Loo_Excel.application.workbooks(1).worksheets(1).cells(3, 3 ).value = dw_2.describe( "cust_nam_t.text ") ///内容for i=1 to dw_2.rowcount() Loo_Excel.application.workbooks(1).worksheets(1).cells(3+i, 1).value = string( dw_2.object.po_no[i] )nextLoo_Excel.application.ActiveWorkbook.SaveAs(ls_filename)Loo_Excel.Application.ScreenUpdating = TrueLoo_Excel.DisconnectObject()-----------------------------------------------------------------也可以从Excel提取信息，假设你想知道当前活动Sheet的A1单元(Cell)的值，你可以使用如下代码：ANY MyValueMyValue = ExcelServer.ActiveSheet.Cells(1,1).valueCHOOSE CASE ClassName(MyValue)CASE 'string '//assign to string//other data types here......END CASE注意ANY变量的用法。返回的Excel对象的method/property可能是各种各样的数据类型，所以除非你绝对确定返回类型(例如一个Cell对象的ColumnWidth属性一般都返回的是integer变量)，你一般都要使用ANY变量，然后再用ClassName函数来确定对应的值。

 

VBA 把excel 另存为 csv的方法：

workbook.SaveAs(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
其中FileFormat,即代表文件格式,

名称     值
xlCSV     6