I generally is converted into a text format, set the date to date format, and long, with four Numbers, use VARTYPE first () to get the type of the value, the date format, with the dtoc take 10 (), "2019-01-01", the numerical format is according to the need to take a small digital, transcript, other general NULL (NULL) and text value, removes white Spaces at the before and after the import directly;
SET the DATE YMD LONG
The SET CENTURY ON
Alltrim (dtoc (excel_value, 1))
Alltrim (STR (excel_value, 100, 2)) & amp; & According to the situation in small digital
Another: VARTYPE () function and TYPE () function is similar, but VARTYPE () to perform faster, and do not need to use quotation marks (" ") specifies the expression enclosed,
Excel file the following
Serial number of EXCEL value type
Type 1 date/time 2019/1/1 11:01
Type 2 date on January 1, 2019, Tuesday,
3 numeric 20190101.00
Type 4 currency RMB 20190101.00
5 regular 20190101
The program code is as follows:
SET the DATE YMD LONG
The SET CENTURY ON
The SET TALK OFF
CLOSE the DATABASES all
The CREATE TABLE ll FREE (B001 C (100), null, B002 C (100) null) & amp; & Create a data table
The SELECT ll
M_File=GETFILE (' XLS, XLSX ', 'please select EXCEL file')
IF M_File==""
=MESSAGEBOX (" please choose to import ECCEL file!" , 0 + 48, "tip")
RETURN
The ELSE
IF JUSTEXT (M_FILE) # "XLS" AND JUSTEXT (M_FILE) # "XLSX & amp;" & Get file extension
=MESSAGEBOX (" you choose is not EXCEL document!" , 0 + 48, "tip")
RETURN
ENDIF
ENDIF
My_excel=createobject (' excel. Application) & amp; & Create an object
My_excel. Workbooks. Open (M_File) & amp; & Open the specified file
I=1
FOR EACH oMyVar IN My_Excel. Sheets & amp; & The name of each worksheet in the workbook
M_sheet="M_sheet" + ALLTRIM (STR) (I)
I=I + 1
& M_sheet=oMyVar. Name
NEXT oMyVar
My_excel. Worksheets (M_sheet1). Activate & amp; & Activate the first table
UsedRange=My_excel. Worksheets (M_sheet1). UsedRange & amp; & Return to work the area can be used in the table, UsedRange table attribute
O_rows=UsedRange. Rows. Count & amp; & Summary row
O_cols=UsedRange. Columns. Count & amp; & Summary list
I=2
J=3
The DO while. T.
excel_value=https://bbs.csdn.net/topics/my_excel.cells (I, j). The value
DO CASE
CASE vartype (excel_value)="T" & amp; & Date/time type
C_value=https://bbs.csdn.net/topics/ALLTRIM (DTOC (excel_value, 1))
CASE vartype (excel_value)="D" & amp; & The date type
C_value=https://bbs.csdn.net/topics/alltrim (dtoc (excel_value, 1))
CASE vartype (excel_value)="N" & amp; & Numeric
C_value=https://bbs.csdn.net/topics/alltrim (STR (excel_value, 100, 2)) && depending on the situation in small digital
CASE vartype (excel_value)="Y" & amp; & Currency type
C_value=https://bbs.csdn.net/topics/alltrim (STR (excel_value, 100, 2)) && depending on the situation in small digital
OTHERWISE & amp; & Other
C_value=https://bbs.csdn.net/topics/alltrim (excel_value) & other take original value removes white Spaces at the before and after the
ENDCASE
The SELECT ll
APPEND BLANK
The REPLACE B001 WITH C_value and B002 WITH ALLTRIM (my_excel. Cells (I, 2). The value)
I=I + 1
IF i>=o_rows
The EXIT
ENDIF
ENDDO
The SELECT ll
BROWSE the LAST
My_excel. Application. The Visible=. T. & amp; & Buy EXCEL visible
My_excel. WORKBOOKS. CLOSE & amp; & Close the workbook
My_excel. QUIT & amp; & Exit Excel
CLOSE all
RETURN
CodePudding user response:
Not used temporarily, to learn,CodePudding user response:
Also can test the learning, thank you