This article is taken from the 9CBS forum post collection and summary version:
--- September 3, 2004
--- Original post:
http://community.9cbs.net/expert/topic/3328/3328715.xml?temp=8.050799e-04
In view of the problem that many friends ask PB data to export to Excel, the format and data type are not right, write several user objects, I hope to throw bricks, strengthen technical exchange, and get everyone's support.
1. NVO_EXCEL can define an Excel title public function integer uf_toExcel (Datastore Ads_TATA, READONLY STRING AS_TITA) as long as it is two interface functions.
Export the data of the data window, you can define the title public function integer uf_toExcel (DataWindow Adw_TITA, READONLY STRING AS_TITL)
2. The following is the SRU file of two user objects, imported into the project $ PBEXPORTHEADER $ UO_DS_BASE.SRU $ PBEXPORTCOMMENTS $ Data Storage Foundation ForwardGlobal Type UO_DS_BASE from Datastorend TypeEnd Forward
GLOBAL TYPE UO_DS_BASE from DatastoreString DataObject = "D_Expression" End Typeglobal UO_DS_BASE UO_DS_BASE
forward prototypespublic function any uf_getitemvalue (long al_row, string as_colname) public function string uf_about () public function string uf_globalreplace (string as_source, string as_old, string as_new) end prototypes
PUBLIC FUNCTION ANY UF_GETITEMVALUE (long al_row, string as_colname); // **************************************************** ************* //// function: Get any column value // PARM: // 1. AL_ROW: Specified row // 2. AS_COLNAME: Specify the column name /// RETURN: -1 is five or success is value to you // author: hzh // Date: 2002.11.05 // ************************ *********************** //
String s_tempcoltypeany a_rets_tempcoltype = limited (this.describe (as_colname ".coltype"))))
// for string typeif left (s_tempcoltype, 4) = "char" or left (s_tempcoltype, 4) = "varc" TEN A_RET = this.getItemString (al_row, as_colname) Endix
// for decimal typeIF Left (s_tempcoltype, 7) = "decimal" then a_ret = THIS.GetItemDecimal (al_row, as_colname) END IF // for date typeIF s_tempcoltype = "date" THEN a_ret = THIS.GetItemDate (al_row, as_colname) END IF
// for datetime typeif s_tempcoltype = "datetime" THEN A_RET = this.getitemdatetime (al_row, as_colname) Endix
// for Number Typeif S_TempColType = "Number" TEN A_RET = this.getitemnumber (al_row, as_colname) endiff
// for time type = "time" TEN A_RET = this.getitemtime (al_row, as_colname) Endiff
// for timestamp typeif s_tempcoltype = "timestamp" Then A_ret = this.getitemtime (al_row, as_colname) endiff
// for int or longif s_tempcoltype = "int" or s_tempcoltype = "long" THEN A_RET = this.getitemnumber (al_row, as_colname) Endiff
IF isnull (A_RET) THEN RETURN-11END IF
Return A_Ret End Function
Public function string uf_about (); string s_func = ""
S_Func = "1. Value (uf_evaluate)" & "to express (UF_EVALUATE)" & ", create data storage (uf_setsqlselect) ~ r ~ n" & "3. Get any list (uf_getitemvalue) ~ r ~ n "// s_func = super :: UF_ABOUT ()
Return "UO_DS_BASE Object Member's Functions: ~ R ~ N" S_FUNC
End functionpublic function string uf_globalReplace (string as_source, string as_old, string as_new); // ****************************************** ***************************** / / / / FUNCTION: Replace the specified string with the specified string // PARM: / / 1. AS_SOURCE: The original string // 2. AS_OLD: String // 3. as_new: String // Return: new string // Author: hzh // Date: 2002.11.14 // ************************************************************* *************** / / long L_Newlen, L_oldlen, L_StartString S_null, S_Source
IF IsNull (as_source) OR IsNull (as_old) OR IsNull (as_new) THEN SetNull (s_null) RETURN s_nullELSE l_oldlen = Len (as_old) l_newlen = Len (as_new) as_Old = Lower (as_old) s_Source = Lower (as_source) END IF
l_start = POS (s_source, as_old)
Do While L_Start> 0 as_source = replace (as_source, l_start, l_oldlen, as_new) s_source = limited (as_source) l_start = POS (s_source, as_old, (l_start l_newlen) LOOP
Return as_source
END FUNCTIONON UO_DS_BASE.CREATECALL Super :: Createtriggerevent (this, "constructor") End on ON
ON UO_DS_BASE.DESTROYTROYTRIGGEREVENT (this, "destructor") Call Super :: destroynd on
-------------------------------------------------- ---------------
$ PBEXPORTHEADER $ NVO_EXCEL.SRU $ PBEXPORTCOMMENTS $ and EXCEL communication function Forwardglobal Type NVO_EXCEL from NonvisualObject TeypeEnd Forward
GLOBAL TYPE NVO_EXCEL from NonvisualObjectnd Typeglobal NVO_EXCEL NVO_EXCEL
Type PrototypeSprivate: Function Uint getModuleFileNamea (Ulong HModule, Ref string lpfilename, ulong nsize) library "kernel32.dll" end prototype
Type Variables
Private: / / Store data to be exported UO_DS_BASE IDS_DATA
// Column String is_columnname []
// Title String is_columntitle []
// Column display format String is_columnformat [] // Type String is_columntype [] End Variables
forward prototypespublic function integer uf_toexcel (datastore ads_data, readonly string as_title) public function integer uf_toexcel (datawindow adw_data, readonly string as_title) private function integer uf_setdatasource (datawindow adw_data) private function integer uf_setdatasource (datastore ads_data) private function integer uf_datatoexcel (string as_title) public Function string uf_about () private function integer uf_initcolumn () end prototypes
Public Function Integer Uf_toExcel (Datastore Ads_Data, Readonly String As_title);
/ ************************************************** ******** /// function: Conversion data to Excel // PARM: // 1. ADS_DATA: Objects containing source data // 2. AS_TITLE: Excel Title // Return: 1 IS SUCCESS AND -1 is fail // Author: hzh // Date: 2003.12.08 / ****************************************** ************************ /
IF this.uf_setdataroup (ads_data) <> 1 THEN RETURN -1
IF not isvalid (IDS_DATA) THEN RETURN -1
IF IDS_DATA.ROWCOUNT () <1 Ten Return -1
THIS.UF_INITCOLUMN ()
This.uf_datatoexcel (as_title)
Return 1END FUNCTION
Public Function Integer Uf_TOEXCEL (DataWindow Adw_data, Readonly String As_title); / ***************************************************** ********************* / / / function: conversion data to Excel // PARM: // 1. ADW_DATA: Objects containing source data // 2. AS_TITLE: EXCEL Title // Return: 1 Is Success and -1 IS Fail // Author: hzh // Date: 2003.12.08 / ****************************** *********************************************** /
IF this.uf_setdataserce (adw_data) <> 1 Then Return -1if Not Isvalid (IDS_DATA) THEN RETURN -1
IF IDS_DATA.ROWCOUNT () <1 Ten Return -1
THIS.UF_INITCOLUMN ()
This.uf_datatoexcel (as_title)
Return 1END FUNCTION
Private function integer Uf_setdataroup (datawindow adw_data); / **************************************************** ****************** /// function: Setting data storage // prm: none // Return: // Author: hzh // Date: 2003.12.08 / ** *********************************************************** ****** /
IDS_DATA.DataObject = adw_data.dataObject // ids_data.uf_setddObject () return adw_data.shaedata (IDS_DATA)
END FUNCTION
PRIVATE FUNCTION INTEGER UF_SETDATASOURCE (DataStore Ads_Data); / ********************************************************************************************************************************************************************************************************************************************************* ****************** /// function: Setting data storage // prm: none // Return: // Author: hzh // Date: 2003.12.08 / ** *********************************************************** ****** /
IDS_DATA.DATAOBJECT = ads_data.dataObject // ids_data.uf_setddObject () Return Ads_Data.shaedata (IDS_DATA) End Function
Private function integer Uf_datatoexcel (string as_title); / **************************************************** ****************** /// Function: Conversion data to Excel // PARM: // 1. AS_TITLE: Excel Title // Return: 1 is success and -1 Is fail // author: hzh // Date: 2003.12.08 // modifier: // 1. 2003.12.10 by hzh // REAson: // 1. Add to calculate the processing / ******** *********************************************************** / long L_cnt, L_i, L_COLS, L_ROWS
String s_colnum, s_colname, S_RANGE
OleObject XLAPP, XLSUB
// l_cols = long (ids_data.object.dataWindow.column.count) L_cols = Upperbound (is_columntitle)
L_ROWS = IDS_DATA.ROWCOUNT ()
IF not isvalid (xlapp) THEN XLAPP = CREATE OLEOBJECTEND IF
If XLAPP.CONNECTONEWOBJECT ("Excel.Application") <0 Then MessageBox ('GA_APP.DWMESSAGETETLE', "You cannot connect the Excel server, please check your computer is ~ r ~ n" & "no MS EXCEL? Installation, please contact the program supplier! ", Questions!) Return -1END IF
// Increase empty document (Excel Table) xlapp.Application.Workbooks.Add () xlapp.application.visible = true
If not isvalid (xlsub) THEN XLSUB = CREATE OLEOBJECTEND IF
/ / Position to the first XLSUB = XLapp.Application.ActiveWorkbook.Worksheets [1]
// Get the last column alphabetic expression if long (l_cols)> 26 THEN / / AA, AB ... or BA, BB, BC ... INT i_COLSTART, I_COLEND I_COLSTART = MOD (L_COLS, 26) i_colend = l_cols / 26 S_COLNUM = "'" char (i_colstart 96) char (i_colend 96) "'" ELSE / / is a, b, c ... format s_colnum = char (l_cols 96) end if
// Title setting xlsub.cells [1,1] = as_title xlsub.cells [1,1] .hizontalalignment = 3XLSUB.CELLS [1,1] .verticalalignment = 3xlsub.cells [1,1] .font.size = 18 // Plaid xlsub.range ("A1:" S_COLNUM "1"). MERGE ()
For l_i = 1 to l_cols // Set the name of the title column xlsub.cells [2, l_i] = is_columntitle [l_i] next
-------------------------------------------------- -
// Painting line // data line starts S_RANGE = "A2: S_Colnum Trim (String (L_ROWS 2)). Borders (1) .LINESTYLE = 1XLSUB.RANGE ( s_range). Borders (2) .LINESTYLE = 1XLSUB.RANGE (S_RANGE). Borders (3) .LINESTYLE = 1XLSUB.RANGE (S_RANGE). Borders (4) .LINESTYLE = 1
// writes data EXECLFOR l_i = 1 TO l_cols FOR l_cnt = 1 TO l_rows IF is_columntype [l_i] = 'column' THEN String s_evaluate s_evaluate = "Evaluate ( 'LookUpDisplay (" is_columnname [l_i] s_evaluate = ")' , " String (l_cnt) ')' xlsub.cells [l_cnt 2, l_i] .FormulaR1C1 = ids_data.Describe (s_evaluate) ELSE xlsub.cells [l_cnt 2, l_i] .FormulaR1C1 = & ids_data.uf_getitemvalue (l_cnt , is_columnname [l_i]) end if xlsub.cells [l_cnt 2, l_i] .NumberformatLocal = IS_COLUMNFORMAT [L_i] NextXEXT
XLAPP.Application.ActiveWorkbook.saved = false
XLapp.disconnectObject ()
IF isvalid (xlapp) Then Destroy Xlappendix
IF isvalid (xlsub) Then Destroy XlsuBend IF
Return 1END FUNCTION
Public function string uf_about ();
/ ************************************************** ******** /// Function: User Object Features // PARM: NONE // Return: Description Text and Corresponding Public Functions // Author: hzh // Date: 2003.12.08 / ** *********************************************************** ****** / STRING S_FUNC = ""
// s_func = super :: UF_ABOUT ()
S_Func = S_FUNC "~ R ~ NVO_EXCEL OBJECT MEMBER's Functions: ~ R ~ N"
S_FUNC = "1. User Object Function Description (uf_about) ~ R ~ N" & "2. The program data is exported to the Excel table, has been overloaded (UF_TOEXCEL) ~ R ~ N"
Return S_Func
End functionprivate function integer uf_initcolumn (); / **************************************************** ***************** /// Function: Initialized column information // PARM: // Return: of no us // author: hzh // Date: 2003.12.08 // Modifier: // 1. 2003.12.10 By HzH // Reason: // 1. Added processing of computational columns / *********************** ***************************************** /
INT i_cntstring s_colname, s_datatype
// Clear objects FOR i_cnt = 1 TO UpperBound (is_columnname) SetNull (is_columnname [i_cnt]) SetNull (is_columntitle [i_cnt]) SetNull (is_columnformat [i_cnt]) SetNull (is_columntype [i_cnt]) END FOR
String s_objectsuo_ds_base ds_excelif not isvalid (DS_EXCEL) THEN DS_EXCEL = CREATE UO_DS_BASEEND IF
DS_EXCEL.DATAOBJECT = 'dw_excel_columns's_Objects = IDS_DATA.DESCRIBE ("DataWindow.Objects")
// Put all the visible targets in the Detail area into DS_EXCEL, //, sort the or -/pos long l_x string s_name, s_type, s_band, s_visible, s_objtype string s_posible, s_objtype string s_objzw, S_ZW i_pos = POS (S_Objects, "~ T") IF i_pos <= 0 THEN i_POS = LEN (S_Objects) 1 // Object Name S_Name = Left (S_Objects, I_POS - 1) S_Objects = MID (S_Objects, I_POS 1, LEN (S_Objects)) // Object Data Type S_TYPE = LOWER (IDS_Data.Describe (S_Name ".ColType") // Object X Coordinate L_x = long (ids_data.describe (s_name ".x") // Object regional s_band belongs = Lower (ids_data.Describe (s_name ".band")) // Object is visible s_visible = ids_data.Describe (s_name ".visible") // Object class s_objtype = Lower (ids_data.Describe (s_name ".type")) // If Object is in the Detail area, it is visible, and is a column or compute column if s_band = "detil" and s_visible = "1" and & (s_objtype = "column" or s_objtype = "compute") THEN
// Object Chinese header, support standard naming s_objzw = s_name "_t" s_zw = IDS_DATA.DESCRIBE (S_OBJZW ".text") // Remove the extra wanks in the header, space and quotation marks S_ZW = DS_EXCEL.UF_GLOBALREPLACE (S_ZW , "~ n", "") s_zw = ds_excel.uf_globalreplace (s_zw, "," ") s_zw = DS_EXCEL.UF_GLOBALREPLACE (S_ZW, '" "" ") long l_newrow l_newrow = DS_EXCEL.INSERTROW (0) DS_EXCEL. SetItem (l_newrow, "colname", s_name) ds_excel.SetItem (l_newrow, "x", l_x) ds_excel.SetItem (l_newrow, "coltype", s_type) ds_excel.SetItem (l_newrow, "coltitle", s_zw) ds_excel.SetItem ( l_newrow, "objtype", s_objtype) end ifloop // sort, set to DS_EXCEL.SETSORT ("XA") DS_EXCEL.SORT ()
FOR i_cnt = 1 TO ds_excel.RowCount () is_columnname [i_cnt] = Lower (ds_excel.GetItemString (i_cnt, 'colname')) is_columntitle [i_cnt] = ds_excel.GetItemString (i_cnt, 'coltitle') is_columntype [i_cnt] = ds_excel. GetItemString (i_cnt, 'objtype') s_datatype = Left (Lower (ds_excel.GetItemString (i_cnt, 'coltype')), 4) CHOOSE CASE s_datatype CASE 'char', 'varc', 'int', 'long' is_columnformat [i_cnt ] = 'G / universal format' // Special specified date dedicated format is char (10) s_datatype = limited (DS_EXCEL.GETITEMSTRING (i_cnt, 'colorype')) ife s_datatype = 'char (10)' Then I_ColumnFormat [i_cnt] = 'YYYY "" Month "" Month "" D "" Day ""' end if case 'deci' IS_COLUMNFORMAT [i_cnt] = "0.00_" Case 'Date', 'DateTime' Is_ColumnFormat [i_cnt] = 'YYYY "Year" "M" "Month" "D" "Day" "'Case'" SS "" Ss "" SS "" SS "" SS "" SS "" SS "" SS "" SS "" SS "" SS "" SS "" SS "" SS "" SS " [i_cnt] = 'g / universal format' end choosnextif isvalid (ds_excel) Then Destroy DS_EXCELEND IF
Return 1
End functionon nvo_excel.createcall super :: CreateTriggerevent (this, "constructor") End on
ON NVO_EXCEL.DESTROYTRIGEREVENT (this, "destructor") Call Super :: destroynd ON
Event constructor; if not isvalid (IDS_DATA) THEN IDS_DATA = CREATE UO_DS_BASEEND IFEND EVENT
Event destructor;
IF isvalid (IDS_DATA) THEN DESTROY IDS_DATAEND IFEND EVENT
--------------------------------------------
$ PBExportHeader $ dw_excel_columns.srd $ PBExportComments $ obtained conversion target temporary release 7; datawindow (units = 0 timer_interval = 0 color = 16777215 processing = 1 HTMLDW = no print.documentname = "" print.orientation = 0 print.margin.left = 110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96 print.paper.source = 0 print.paper.size = 0 Print.Prompt = no print.buttons = no print.preview. Buttons = no grid.lines = 0) Header (Height = 68 color = "536870912") Summary (Height = 0 color = "536870912") Footer (Height = 0 color = "536870912") Detail (height = 76 color = " 536870912 ") table (column = (type = char (40) updatewhereclause = no name = coltitle dbname =" coltitle ") column = (type = char (30) updatewhereclause = no name = colname dbname =" colname ") column = ( type = long updatewhereclause = no name = x dbname = "x") column = (type = char (10) updatewhereclause = no name = objtype dbname = "objtype") column = (type = char (20) updatewhereclause = no name = COLTYPE DBNAME = "colorype")) Text (band = header alignment = "2" text = "colorype" border = "2" color = "0" x = "718" y = "4" Height = "60" width = "361" Name = colorype_t font.face = "Song" font.height = "font.weight =" 400 "font.Family =" 0 "font.pitch =" 2 "font. Charset = "134" background.mode = "2" background.color = "80269524") Text (Band = header alignment = "0" text = "cool"
Border = "2" color = "0" x = "1088" y = "4" Height = "10" width = "434" name = t_1 font.face = "Song" font.height = "- 10" font. Weight = "400" font.family = "0" font.pitch = "2" font.charset = "134" background.mode = "2" background.color = "80269524") Text (Band = header alignment = "0 "Text =" objtype "border =" 2 "color =" 0 "x =" 1531 "y =" 4 "height =" 60 "width =" 320 "name = t_2 font.face =" Song "font.height = "-10" font.weight = "400" font.family = "0" font.pitch = "2" font.charset = "134" background.mode = "2" background.color = "80269524") Text (Band) = Header alignment = "2" text = "colname" border = "2" color = "0" x = "9" y = "4" height = "60" width = "457" name = colname_t font.face = " Song "font.height =" - 10 "font.weight =" 400 "font.family =" 0 "font.pitch =" 2 "font.charset =" 134 "background.mode =" 2 "Background.color =" 80269524 ") Text (band = header alignment =" 2 "text =" x "border =" 2 "color =" 0 "x =" 475 "y =" 4 "height =" 60 "width =" 233 "name = X_t font.face = "Song" font.height = "- 10" font.weight = "400" font.family = "0" font.pitch = "2" font.charset = "134" background.mode = "
2 "Background.color =" 80269524 ") Column (Band = DETAIL ID = 1 alignment =" 0 "tabsequence = 32766 border =" 2 "color =" 0 "x =" 1088 "y =" 0 "Height =" 72 "width =" 434 "format =" [general] "name = cool edit.limit = 0 Edit.case = any Edit.autOSELECT = yes font.face =" Song "font.height =" - 10 "font.weight = "400" font.family = "2" font.pitch = "2" font.charset = "134" background.mode = "2" background.color = "16777215") Column (Band = Detail ID = 4 alignment = " 0 "Tabsequence = 32766 border =" 2 "color =" 0 "x =" 1531 "y =" 0 "height =" 72 "width =" 320 "format =" [general] "name = objtype edit.limit = 0 Edit.case = any Edit.Autoselect = yes font.face = "Song" font.height = "font.weight =" 400 "font.Family =" 2 "font.pitch =" 2 "font.charset = "134" background.mode = "2" background.color = "1677215") color = "0" tabsequence = 10 border = "2" color = "0" x = "9" y = "0" Height = "72" width = "457" format = "[general]" name = colname edit.limit = 0 Edit.case = any Edit.focusRectangle = no edit.AutoSelect = yes edit.autohscroll = yes font . Face = "Song" font.height = "- 10" fon T.Weight = "400" font.family = "2" font.pitch = "2" font.charset = "134" background.mode = "
1 "Background.color =" 536870912 ") Column (Band = DETAIL ID = 3 alignment =" 1 "tabsequence = 20 border =" 2 "color =" 0 "x =" 475 "y =" 0 "Height =" 72 "width =" 233 "format =" [general] "name = x Edit.limit = 0 Edit.case = any Edit.focusRectangle = no edit.autoSelect = yes edit.autohscroll = yes font.face =" Song "font. Height = "- 10" font.weight = "400" font.family = "2" font.pitch = "2" font.charset = "134" background.mode = "1" background.color = "536870912") Column (Band = DETAIL ID = 5 alignment = "0" Tabsequence = 30 border = "2" color = "0" x = "718" y = "0" height = "72" width = "361" format = "[General ] "Name = ColType Edit.limit = 0 Edit.case = any Edit.focusRectangle = no Edit.Autoselect = yes Edit.autohscroll = yes font.face =" Song "font.height =" - 10 "font.weight =" 400 "font.family =" 2 "font.pitch =" 2 "font.charset =" 134 "background.mode =" 1 "background.color =" 536870912 ") htmltable (border =" 1 ") htmlgen (ClientEvents = "1" clientValidation = "1" clientcomputedfields = "1" clientformatting = "0" clientscriptable = "0" generatejavascript = "1") --------------------- --------------- ----------------------
The above program BUG list: 1.
/ ************************************************** ******** /// Function: Conversion Data to Excel // PARM: // 1. AS_TITLE: Excel Title // Return: 1 Is Success and -1 IS Fail // Author: hzh // Date: 2003.12.08 // modifier: // 1. 2003.12.10 By HzH // REASON: / / 1. Increase the processing of computed columns / **************** *************************************************** / long L_CNT, L_i, L_COLS, L_ROWS
String s_colnum, s_colname, S_RANGE
OleObject XLAPP, XLSUB
For L_i = 1 to UpperBound (is_columntitle) if isnull (is_columntitle [l_i]) THEN EXIT L_COLS NEXT
2. Function: UF_INITCOLUMN Processing the DateTime format is not very good
CASE 'date' //, 'datetime' s_datatype = Lower (ds_excel.GetItemString (i_cnt, 'coltype')) IF s_datatype = 'datetime' THEN is_columnformat [i_cnt] = 'yyyy-md h: mm' ELSE is_columnformat [i_cnt] = 'YYYY "" Year "" M "" Month "" D "" Day ""' End IF
3. This is not an error, you can strengthen the function // special specified date dedicated format is char (10) s_datatype = limited (DS_EXCEL.GETITEMSTRING (I_CNT, 'ColType')) IF s_datatype = 'char (10)' Then IS_COLUMNFORMAT [i_cnt ] = 'YYYY "" year "" Month "" D "" Day ""' end if // special specified time dedicated format is char (8) s_datatype = limited (DS_EXCEL.GETITEMSTRING (i_cnt, 'colorype') ) If s_datatype = 'char (8)' Then I_ColumnFormat [i_cnt] = 'h "" Time "" MM "" SS "" SS "" "" END IF
// Special specified date time dedicated format is char (19) s_datatype = lower (DS_EXCEL.GETITEMSTRING (i_cnt, 'colorype')) if s_datatype = 'char (19)' Then I_ColumnFormat [i_cnt] = 'YYYY-MD H: mm 'End if according to the code, only char (8), 10, 19, this is a dedicated format for my design date, time, etc.
-------------------------------------------------- ---------
I also passed another post into the Chinese dragon. The post may not be successfully added to FAQ because the 9CBS historical post management reasons cannot be successfully added.
Llitcwl (China Dragon):
/ / =========================================================================================================================================================================================== ==================== // [public] function uf_data2excel in u_data2word inherited from nonvisualObject // -------------------------------------------------------------- -------------------------------------------------- - // Description: Pour the data into Excel, support the calculation column and display format, require the calculation column to write tag values / / ----------------- -------------------------------------------------- - // Parameter 1: [Value] DataWindow Adw // Description: Data Window // ------------------------------- ------------------------------------- // Return: (Integer) successfully returned 1, unsuccessful Return 0 / / ---------------------------------------------- ---------------------- // author: cwl date: 2002.03.18 // ================ ============================================================================================================================================================================================================= == // Change Log: 020515 Join the support for the crosstab (mainly modified the head section)
constant integer ppLayoutBlank = 12OLEObject ole_objectole_object = CREATE OLEObjectinteger li_ret, li_crosstab = 0long ll_colnum, ll_rownumstring ls_valuestring ls_objects, ls_obj, ls_objs [], ls_objtag [] long ll_pos, ll_len, ll_num = 0 // head region long ll_headnumstring ls_head [], ls_headtag [ ] // Total area long ll_sumnum, i = 1, startpos = 1, endpos, li_postring ls_sum [], ls_sumtag [], ls_bind, token [], list, ls_temp, ls_crosstabcoln_cst_string Lu_String // PFC String Processing Object
Li_RET = OLE_OBJECT.CONNECTTOOBJECT ("Excel.Application") if li_ret <> 0 Then // If Excel has not been opened, it will be created. Li_RET = OLE_OBJECT.CONNECTTONEWOBJECT ("excel.application") if Li_ret <> 0 Then MessageBox ('Ole Error', 'OLE Unable to connect! Error Number:' string (li_ret)) Return 0 end if ole_Object.visible = FALSE / / Invisible end if
If adw.object.dataWindow.Processing = '4' TEN // Cross table Processing Adw.Object.dataWindow.crosstab.StaticMode = 'true' // Static LI_CROSSTAB = 1END IF
Pointer OldPointeroldPointer = setPointer (HOURGLASS!)
// Add a work area ole_Object.workbooks.add
Ls_Objects = Trim (Adw.describe ('DataWindow.Objects')) List = Ls_ObjectsendPos = POS (List, '~ T', StartPOS) // Get Object List Do While (Endpos> 0) token [i] = MID (List , StartPos, Endpos - StartPOS) i StartPos = Endpos 1 Endpos = POS (List, '~ T', StartPos) LoopToken [i] = MID (List, StartPOS) LL_ROWNUM = UpperBound (Token)
For i = 1 to ll_rownum ls_obj = token [i] ifl ls_obj = 'title' Then MessageBox ('', adw.describe (ls_obj '.type')) ifeer (Adw.describe (Ls_Obj '.type') ) = 'column' or & lower (ADW.DESCRIBE (LS_OBJ '.TYPE') = 'compute' Then Ls_bind = LOWER (Adw.describe (ls_obj '.band')) IF ls_bind = 'Detail' Then LL_NUM = 1 ls_objs [ll_num] = ls_obj if li_crosstab = 0 then // general processing ls_objtag [ll_num] = adw.Describe (ls_obj '_t.text') elseif li_crosstab = 1 then // cross-processing li_pos = lu_string.of_lastpos (ls_obj, '_', len (ls_obj)) // identify the last appearance of '_' location if li_pos = 0 or (not isnumber (MID (Ls_Obj, li_pos 1))) Then // is not a crosslining LS_OBJTAG [ll_num] = adw.describe (ls_obj '_t.text') else ls_temp = MID (ls_obj, li_pos) LS_CROSSTABCOL = MID (Ls_Obj, 1, li_pos - 1) // Remove the intersection // messagebox ('', LS_CROSSTABCOL ',,,,' LS_TEMP) LS_OBJTAG [LL_NUM] = Adw.describe (LS_CROSTABCOL "_T" LS_TEMP ". TEXT") // Remove the head of the crosstab END IF ELSEIF (ls_bind = 'summary " ) THEN LL_SUMNUM = 1 ls_sum [ll_sumnum] = ls_obj ls_suMTAG [ ll_sumnum] = adw.Describe (ls_obj '.tag') else ll_headnum = 1 ls_head [ll_headnum] = ls_obj ls_headtag [ll_headnum] = adw.Describe (ls_obj '.tag') end ifend if next
/ / Get the number of columns and rows of data window data (number of rows should be data line number 2) LL_COLNUM = LL_NUMLL_ROWNUM = Adw.Rowcount () 2
String column_namestring ls_colnameinteger j, k // Write head for i = 1 to ll_headnum ls_value = ls_headtag [i] if ls_value <> '?' Then Ole_Object.cells (1, (i - 1) * 2 1) .value = ls_value end if column_name = ls_head [i] ls_value = this.uf_getdata (adw, column_name, 1) ole_object.cells (1, (i) * 2) .value = ls_valuenext // write end for i = 1 to ll_sumnum ls_value = ls_sumtag [i] IF ls_value <>? 'Then OLE_OBJECT.CELLS (LL_ROWNUM 1, (i - 1) * 2 1) .Value = LS_VALUE END IF Column_name = ls_sum [i] ls_value = this.uf_getdata (adw, column_name , 1) OLE_OBJECT.CELLS (LL_ROWNUM 1, (i) * 2) .Value = ls_valuenext // Write Title for i = 1 to ll_colnum // Get the title of the title header = ls_objtag [i] ole_Object.cells (2, i) .value = ls_valuenext // write data for i = 3 to ll_rownum for j = 1 to ll_colnum color in_name = ls_objs [j] ls_value = this.uf_getdata (adw, column_name, i - 2) OLE_Object.cells (i, j) .value = ls_value nextNext
SetPointer (OldPointer) OLE_Object.visible = true_Object.disconnectObject () Destroy Ole_Object
Return 1
/ / =========================================================================================================================================================================================== ==================== // [public] function uf_getdata in u_data2word inherited from nonvisualObject // -------------------------------- -------------------------------------------------- - // Description: Get an accurate display value of a data window column and calculate the column // ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------- // Parameter 1: [Value] DataWindow DW_1 // Description: // Parameter 2: [Value] String COL // Description: Object Name // Parameter 3: [Value] INTEGER ROW // Description: line // --------------- -------------------------------------------------- --- // Return: (string) value // ------------------------------------- ------------------------------- // author: cwl date: 2002.03.18 // ======= ============================================================================================================================================================================================================= =========== String ls_edittype, ls_value, ls_formatinteger idls_edittype = limited (dw_1.describe (col ") // Get a choice of choose case ls_edittype case 'ddlb', 'DDDW '// should be displayed value ls_value = dw_1.describe ("Evaluate (" ")") " string (ring ") ") Case else id = long (dw_1.describe (col ". ID ")) LS_FORMAT = dw_1.describe (col ". format ") IF m ID (Ls_Format, 1, 1) = '[' or LS_FORMAT = '?' or ls_format = ''
THEN // does not format IF ID = 0 THEN / / Calculate column LS_Value = dw_1.describe ("Evaluate (~" DW_1.Describe (col '.Expression') & "~", " String (Row ) ")") ELSE LS_VALUE = String (dw_1.object.data [ROW, ID]) END IF ELSE IF ID = 0 THEN // Calculate Columns LS_VALUE = String (DW_1.Describe ("Evaluate DW_1. Describe (Col '.Expression') & "") ")") "), LS_FORMAT) ELSE LS_VALUE = String (dw_1.object.data [Row, ID], LS_FORMAT) end if end ifend Chooseif Isnull (Ls_Value) THEN LS_VALUE = '' Return Ls_Value or directly with HTML files
-------------------------------------------------- ------------
Continue to respond, this is the code for a prime brother:
/ ************************************************** ********* / / * Function Name: UF_DWSAVEAS_EXCEL Function: Export the data window data to the Excel file and replace the Excel file with the default English title to Chinese. Parameters: DataWindow DataWin, to the user to export the data window control name return value: Integer 1, Success; -1, Error Process Description: First use saveasascii () to fall out of the Excel file, replace the header as a Chinese name design :: YANHUI November 2003 Modified: Ye Wende 2004.4.8 Cause: In order to improve the readability of the program (such as increasing notes, change the typography style, etc.) * // ******** *********************************************************** /
/ **************** The following programs will be exported to Excel documents ***************** / INTEGER Li_RTN, II, Li_ASCString Ls_Name, LS_PathnameBoolean lb_existif datawin.rowcount () <1 THEN MessageBox ("Tips Information", "Please retrieve data and export to Excel!") Return -1 // erroorend ifli_rtn = getfilesavename ("Save File", LS_Pathname, Ls_Name, "XLS "," Excel file (* .xls), *. Xls ")
if Li_rtn = 1 THEN LB_EXIST = FILEEXISTS (LS_PathName) if lb_exist kiltn = MessageBox ("Save", LS_Pathname "already exists, is it overwritten?", Exclamation !, YESNO!) end if if li_rtn = 1 Then // When the file exists The user selects override, or when the file is not existed. Note Variable Li_RTN Li_RTN = DataWin.Saveascii (LS_Pathname) if Li_RTN = 1 THEN / / MESSAGEBOX ("Tips", "Export Data Success!") Else MessageBox ("Error Information", "Export Data Failed!") Return -1 // error END IFELSE RETURN-1END IF / *********** The following programs will be exported Excel English titles to Chinese characters ********* / Long Numcols, Numrows, C, RoleObject XLAPP, XLSUBINT RETNUMCOLS = Long (DataWin.Object.dataWindow.Column.count) Numrows = datawin.rowcount ()
// Generate an instance of oleObject xlapp = CREATE OLEOBJECT
// Connect OLE object RET = xlapp.connecttonewObject ("Excel.sheet") if Ret <0 Then MessageBox ("Connection Fail!" "& " Error Code: " String (RET) Return -1END IF // Open Excel file xlapp.application.workbooks.open (ls_pathname) makes files visible //xlapp.application.visible = true
// Get the reference to the active worksheet, improve program performance XLSUB = xlapp.application.activeworkbook.worksheads [1] string ls_colname, ls_text, ls_modistr, ls_col // Take a word name Change to the corresponding text TEXT value for c = 1 To Numcols ls_col = "#" string (c) ". name" ls_colname = datawin.describe (ls_col) ls_modistr = ls_colname "_ t.text" ls_text = datawin.describe (ls_modistr) xlsub.cells [1, c] = ls_textNEXT
XLapp.disconnectObject () Destroy XLAppMessageBox ("Tip Information", "Export Data Success!") Return 1 // Success
-------------------------------------------------- ----------------
A collection of exports to Excel (supports exporting packet strips, total belts, and supporting multi-layer nested report exports, basically seeing) ////// Parameters: ad_dw: dataWindow // as_file: file Name // Returns: true / false: bolean // description: Save the dataWindow ask as a Excel file.////////////////// SETPOINTER (HOURGLASS!)
// declare the local variableslong i, j, li_posstring ls_objects, ls_obj, ls_text, ls_err, ls_sqldatastore lds_saveas // export data window datastore lds_sort // get sorted according to column object.x (band = detail and visible = 1) is / computeboolean LB_RETURN / / Return Value String Ls_PBVER / / PB Version Information Environment ENV // Environment Variables
GetENVIRONMENT (ENV) LS_PBVER = String (env.pbmajorRevision)
// Creating a rank sequence Datastorelds_Sort = Create Datastorels_sql = 'column = (Type = char (1) Name = ztext dbname = "ztext")' '~ r ~ n' & 'column = (Type = char (1) Name = Zcol dbname = "zcol") ' ' ~ r ~ n ' &' colorn = (Type = long name = zx dbname = "zx") ' ' ~ r ~ n'ls_sql = 'Release' LS_PBVER '; ~ r ~ ntable (' ls_sql ')' lds_sort.create (ls_sql, ls_err) if len (ls_err)> 0 Then LB_RETURN = FALSE GOTO LAB1END IF
// Prepare data =============================================== ====== // all controlsls_Objects = ad_dw.describe ("DataWindow.Objects")
// Press to T position as a start cycle do while (POS (Ls_Objects, "~ T")> 0) Li_POS = POS (Ls_Objects, "~ T") ls_obj = left (ls_Objects, ls_pos - 1) ls_objects = Right ( Ls_Objects, Len (Ls_Objects) - LI_POS // (Column or Compute) At Detail and Visible IF (AD_DW.DESCRIBE (Ls_Obj ") =" Column "or & ad_dw.describe (ls_obj ". Type ") =" Compute ") and & (ad_dw.describe (ls_obj ". Band ") =" Detail ") and & & (ad_dw.describe (ls_obj ". Visible ") =" 1 ") THEN LS_TEXT = ad_dw.describe (ls_obj " _t .text ') if ls_text <>'! 'and ls_text <>'? 'Then Lds_Sort.NSertrow (0) lds_sort.setitem (lds_sort.rowcount (),' Ztext ', Ls_Text) LDS_SORT.SETITEM (LDS_SORT.ROWCOUNT () , 'Zcol', LS_OBJ) LDS_SORT.SETITEM (LDS_SORT.ROWCOUNT (), 'ZX', long (ad_dw.describe (ls_obj '.x')) end if endiffloop
// the last controlls_obj = ls_objectsIF (ad_dw.Describe (ls_obj ". Type") = "column" or & ad_dw.Describe (ls_obj ". Type") = "compute") AND & (ad_dw.Describe (ls_obj ". Band ") =" detail ") and & & (ad_dw.describe (ls_obj ". Visible ") =" 1 ") THEN LS_TEXT = ad_dw.describe (ls_obj " _t.text ') if ls_text <>!' and LS_Text < > '?' then lds_sort.insertrow (0) lds_sort.setitem (lds_sort.rowcount (), 'ztext', ls_text) lds_sort.setitem (lds_sort.rowcount (), 'zcol', ls_obj) lds_sort.setitem (lds_sort.rowcount (), 'zx', long (AD_DW.DESCRIBE (LS_OBJ '.X'))))) End ifnd IF // If there is no column, IF LDS_SORT.ROWCOUNT () <1 THEN GOTO LAB1 / / Sort by Object.x lds_sort.setsort ( 'zx A') lds_sort.sort () // Create derived datastorelds_saveas = create datastorels_sql = '' for i = 1 to lds_sort.rowcount () ls_obj = lds_sort.getitemstring (i, 'zcol') ls_sql = 'Column = (Type = char (1) DBNAME = "' ls_obj '")' '~ r ~ n'Extls_sql =' Release ' LS_PBVER '; ~ R ~ NTABLE (' ls_sql ') ' LDS_SAVEAS.CREATE (LS_SQL, LS_ERR) IF LEN (LS_ERR)> 0 THEN LB_RET URN = false goto lab1endiff
/ / Write data to LDS_SAVEAS for i = 1 to ad_dw.rowcount () yield () // Release message queue, if the amount of data is large, you can use this function LDS_SAVEAS.ISERTROW (0) for j = 1 to lds_sort.rowcount () ls_obj = lds_sort.getitemstring (j, 'zcol') if ad_dw.describe (ls_obj '.type') = 'column' then ls_text = ad_dw.describe ( 'evaluate (~' LookUpDisplay ( ' ls_obj ') ~ ',' string (i) ')') ELSE LS_TEXT = ad_dw.describe ('Evaluate (~' ls_obj '~', ' string (i) ') ') end if lds_saveas.setItem (i, j, ls_text) NextNextLDS_SAVEAS.INSERTROW (1) for i = 1 to lds_sort.rowcount () LDS_SAVEAS.SETITEM (1, I, LDS_SORT.GETITEMSTRING (i, 'ztext')) Next // Prepare data == =============================================================================================================================================================================================================
// saveas datawindowlb_return = (LDS_SAVEAS.SAVEAS (As_File, Excel !, false) = 1)
Lab1: destroy lds_sortdestroy lds_saveassetpoint (arrow!) Return LB_RETURN
