How to convert a cross report of the PB into an Excel form (including the full form of the title and total)
Function section: (also used a function of the string processing of PFC and one value function in this object) / / ====================== ====================================================== // [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 head Calculation column to write TAG value // --------------------------------------- --------------------------- // Parameter 1: [Value] DataWindow Adw // Description: Data Window // ----- -------------------------------------------------- ------------- // Return: (Integer) successfully returned 1, not successful return 0 / / -------------------- ------------------------------------ // author: cwl date: 2002.03.18 // ======================================== =============================== Change log: 020515 Join support for crosstabs (mainly modified saving head section)
Constant Integer PPLAYOUTBLANK = 12oleObject ole_Objectole_Object = CREATE OLEOBJECT
integer 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_posstring ls_sum [], ls_sumtag [], ls_bind, token [], list, ls_temp, ls_crosstabcoln_cst_string lu_string // PFC string processed li_ret = ole_object.ConnectToObject ( "", "Excel.Application ") If li_ret <> 0 the// 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
---------------------------------------------------------------------------------------------------------------------------------------
PFC's string processing function (can be directly changed to global functions) / / =============================== ======================================================== // [public] function of_lastpos in n_cst_string inherited from n_ccu_base / / --------------------------------------------------- ------------------- // Description: Find a string in the last appearance in another string / / ----------- -------------------------------------------------- ------ // Parameter 1: [Value] string as_source // Description: Source string // Parameter 2: [value] string as_target // Description: Target Strings // Parameter 3: [Value] long al_start / / Description: Start location // ----------------------------------------- ------------------------- // Return: (long) From the right number, it will be found to find 0 // -------------------------------------------------- ---------------- Long LL_CNT, LL_POS
// Check for null parameters.if isnull (as_source) or isnull (AS_TARGET) or isnull (al_start) THEN setNull (LL_CNT) RETURN LL_CNTEND IF
// check for an empty stringif len (as_source) = 0 THEN RETURN 0nd IF
// Check for the starting position, 0 means start at the end.if al_start = 0 Then Al_Start = LEN (as_source) endiff
// perform findfor ll_cnt = al_start to 1 step -1 ll_pos = POS (as_source, as_target, ll_cnt) if ll_pos = l_cnt dam = ll_cnt dam
// String Was Not Foundreturn 0 --------------------------------
Get a data window column and calculate the accurate display value function: / / ================================== ======================================================================================================================================================================================================================================================== -------------------------------------------------- ---------------- // 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 COL // Description: Object Name // Parameters 3: [Value] INTEGER ROW // Description: line // -------------------------------------------------- ----------------- // Return: (string) value // ----------------------- --------------------------------------------- // author: cwl Date: 2002.03.18 / / ==================================================================== ==================================================================================================================================== Get the sake of choose case ls_edittype case 'ddlb', 'DDDW' // should get the display value ls_value = dw_1.describe ("Evaluate (" COL ") '," String (ROW) ")" Case Else ID = long (dw_1.describe (COL ". ID"))) LS_FORMAT = dw_1.describes e (col ". Format") IF MID (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 ") ")" ELSE LS_VALUE = String (dw_1.object.data [row, id]) end if else if id = 0 THEN // calculate the column 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 choosen isnull (ls_value) THEN LS_VALUE = '' Return Ls_Value -------------------------- The easiest solution, DW Changes the HTML file, then change the extension called .xls, everything is OK.