#include "hbclass.ch"

#define HBXML_ERROR_NOT_LT        1
#define HBXML_ERROR_NOT_GT        2
#define HBXML_ERROR_WRONG_TAG_END 3
#define HBXML_ERROR_WRONG_END     4
#define HBXML_ERROR_WRONG_ENTITY  5
#define HBXML_ERROR_NOT_QUOTE     6
#define HBXML_ERROR_TERMINATION   7

#define HBXML_TYPE_TAG            0
#define HBXML_TYPE_SINGLE         1
#define HBXML_TYPE_COMMENT        2
#define HBXML_TYPE_CDATA          3
#define HBXML_TYPE_PI             4


STATIC xObj,xParent
STATIC cListSep:=";"


CLASS HOOCalc

   DATA cPath, cFileName, cTmpDir
   DATA oContent
   DATA oBody
   DATA oSpreadSheet
   DATA oTable
   DATA nRow
   DATA nCols
   DATA nColumn
   DATA cFont
   DATA nFont 
   DATA aColWidth
   DATA aMaxColWidth
   DATA nStyleType
   DATA aWidthObj,lOptimize

   METHOD New(cFileName,cTabName,cTmpDir,cFont,nFont)
   METHOD AddRow( aCells, nStyleType, nMerge, cString ) //запись строки массивом
   METHOD AddCell(xValue, nMerge,nColumn) //добавление ячейки
   METHOD Save(lShow)
   METHOD ColWidth(nLen)
   METHOD RowHeight()
ENDCLASS


METHOD New(cFileName,cTabName,aColWidth,aPicture,cTmpDir,cFont,nFont,lOptimize) CLASS HOOCalc
LOCAL i,aObj
//cTmpDir - папка для временных файлов - задавать обязательно

::cTmpDir:=cTmpDir
::cPath := cTmpDir+"ods"+StrZero(ROUND(HB_Random(1,9999999999),0),10,0)
::cFileName:=cFileName
::nRow:=0
::nCols:=LEN(aColWidth)
::cFont:=IF(EMPTY(cFont),"Courier New",cFont)
::nFont:=IF(EMPTY(nFont),8,nFont)
::aColWidth:=aColWidth
::aWidthObj:=ARRAY(::nCols)
::aMaxColWidth:=ARRAY(::nCols)
::lOptimize:=IF(lOptimize==NIL,.T.,lOptimize)
AFILL(::aMaxColWidth,0)
IF EMPTY(cTabName)
   cTabName:="Report"
ENDIF

   ::oContent := HXMLDoc():New()

   ::oContent:Add( HXMLNode():New( "office:document-content",, { ;
           {"xmlns:office","urn:oasis:names:tc:opendocument:xmlns:office:1.0"},;
           {"xmlns:style","urn:oasis:names:tc:opendocument:xmlns:style:1.0"},;
           {"xmlns:text","urn:oasis:names:tc:opendocument:xmlns:text:1.0"},;
           {"xmlns:table","urn:oasis:names:tc:opendocument:xmlns:table:1.0"},;
           {"xmlns:draw","urn:oasis:names:tc:opendocument:xmlns:drawing:1.0"},;
           {"xmlns:fo","urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"},;
           {"xmlns:xlink","http://www.w3.org/1999/xlink"},;
           {"xmlns:dc","http://purl.org/dc/elements/1.1/"},;
           {"xmlns:meta","urn:oasis:names:tc:opendocument:xmlns:meta:1.0"},;
           {"xmlns:number","urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"},;
           {"xmlns:svg","urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0"},;
           {"xmlns:chart","urn:oasis:names:tc:opendocument:xmlns:chart:1.0"},;
           {"xmlns:dr3d","urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0"},;
           {"xmlns:math","http://www.w3.org/1998/Math/MathML"},;
           {"xmlns:form","urn:oasis:names:tc:opendocument:xmlns:form:1.0"},;
           {"xmlns:script","urn:oasis:names:tc:opendocument:xmlns:script:1.0"},;
           {"xmlns:ooo","http://openoffice.org/2004/office"},;
           {"xmlns:ooow","http://openoffice.org/2004/writer"},;
           {"xmlns:oooc","http://openoffice.org/2004/calc"},;
           {"xmlns:dom","http://www.w3.org/2001/xml-events"},;
           {"xmlns:xforms","http://www.w3.org/2002/xforms"},;
           {"xmlns:xsd","http://www.w3.org/2001/XMLSchema"},;
           {"xmlns:xsi","http://www.w3.org/2001/XMLSchema-instance"},;
           {"office:version","1.0"} } ) )
   ::oContent:aItems[1]:Add( HXMLNode():New( "office:scripts" ) )
   ::oContent:aItems[1]:Add( HXMLNode():New( "office:font-face-decls" ) )
   xFix(::oContent:aItems[1]:aItems[2])
   xAdd("style:font-face",{;
   {"style:name","Courier New"},; 
   {"svg:font-family","Courier New"},; 
   {"style:font-family-generic","modern"},; 
   {"style:font-pitch","fixed"};
   })
   xAdd("style:font-face",{;
   {"style:name","Tahoma"},; 
   {"svg:font-family","Tahoma"},; 
   {"style:font-pitch","variable"};
   })

   ::oContent:aItems[1]:Add( HXMLNode():New( "office:automatic-styles" ) )
   altd()

   //Стили для колонок
   FOR i:=1 TO ::nCols

   xFix(::oContent:aItems[1]:aItems[3])
   xAdd("style:style",;
             {{"style:name","co"+Column(i)},;
             {"style:family","table-column"}})

      XRight()

      ::aWidthObj[i]:=xAdd("style:table-column-properties",;
           {{"fo:break-before","auto"},;
            {"style:column-width",::ColWidth(::aColWidth[i])}})
      xLeft()

   NEXT

   //Стиль для строк 
   xAdd("style:style",;
     {{"style:name","ro1"},;
      {"style:family","table-row"} } ) 
      xRight()
      xAdd("style:table-row-properties",;
      {{"fo:break-before","auto"},;
      ::RowHeight()}) 
      xLeft()

   //Стиль для строк с разрывом страницы перед строкой
   xAdd("style:style",;
     {{"style:name","ro2"},;
      {"style:family","table-row"} } ) 
      xRight()
      xAdd("style:table-row-properties",;
      {{"fo:break-before","page"},;
      ::RowHeight()})
      xLeft()


   //Стиль для таблицы
   xAdd("style:style",;
      {{"style:name","ta1"},;
      {"style:family","table"},;
      {"style:master-page-name","Default"};
      }) 
      xRight()
      xAdd("style:table-properties",;
          {;
          {"table:display","true"},; 
          {"style:writing-mode","lr-tb"};
          })
      xLeft()




   //стили для чисел
   FOR i:=0 TO 6 

   xAdd("number:number-style",;
     {{"style:name","N"+STR(i,1,0)};
     })
      xRight()
      xAdd("number:number",;
        {;
        {"number:decimal-places",STR(i,1,0)},; 
        {"number:min-integer-digits","1"};
        })
      xLeft()
   NEXT


  //текстовой стиль
  xAdd("number:text-style",;
     {{"style:name","N100"};
     })
     xRight()
     xAdd("number:text-content")
     xLeft()

   //текст для верхних заголовоков (цвет обычный, центрируем, нет переноса рамок нет)
   xAdd("style:style",;
     {{"style:name","ce_text"},;
      {"style:family","table-cell"},;
      {"style:data-style-name","N100"}; 
     }) 
      xRight()
      xAdd("style:text-properties",;
        {;
        {"style:font-name",::cFont},;
        {"fo:font-size",ALLTRIM(STR(::nFont))+"pt"};
        })
      xAdd("style:paragraph-properties",;
        {;
        {"fo:text-align","center"}; 
        })
      xLeft()

   //текст для заголовков (цвет серый, центрируем, нет переноса)
   xAdd("style:style",;
     {{"style:name","cehe_text"},;
      {"style:family","table-cell"},;
      {"style:data-style-name","N100"}; 
     }) 
      xRight()
      xAdd("style:table-cell-properties",;
        {;
        {"style:text-align-source","fix"},;
        {"style:repeat-content","false"},;
        {"fo:background-color","#c0c0c0"},; 
        {"fo:border-bottom","0.001cm solid #000000" },;
        {"fo:border-left","0.001cm solid #000000"},; 
        {"fo:border-right","0.001cm solid #000000"},; 
        {"fo:border-top","0.001cm solid #000000"};
        })
      xAdd("style:text-properties",;
        {;
        {"style:font-name",::cFont},;
        {"fo:font-size",ALLTRIM(STR(::nFont))+"pt"},;
        {"fo:color","#000000"}; 
        })
      xAdd("style:paragraph-properties",;
        {;
        {"fo:text-align","center"}; 
        })
      xLeft()

   //текст для итогов (цвет голубенький, не центрируем, есть перенос)
   xAdd("style:style",;
     {{"style:name","ceto_text"},;
      {"style:family","table-cell"},;
      {"style:data-style-name","N100"}; 
     }) 
      xRight()
      xAdd("style:table-cell-properties",;
        {;
        {"fo:wrap-option","wrap"},; 
        {"style:vertical-align","top"},;
        {"fo:background-color","#99ccff"},; 
        {"fo:border-bottom","0.001cm solid #000000" },;
        {"fo:border-left","none"},; 
        {"fo:border-right","none"},; 
        {"fo:border-top","0.001cm solid #000000"};
        })
      xAdd("style:text-properties",;
        {;
        {"style:font-name",::cFont},;
        {"fo:font-size",ALLTRIM(STR(::nFont))+"pt"},;
        {"fo:color","#000000"}; 
        })
      xLeft()



   //стили для ячеек
   FOR i:=1 TO ::nCols 

   xAdd("style:style",;
     {{"style:name","ce"+Column(i)},;
      {"style:family","table-cell"},;
      {"style:parent-style-name","co"+Column(i)},;
      {"style:data-style-name",GetDataStyleName(aPicture[i])}; 
     }) 
     XRight()
      xAdd("style:table-cell-properties",;
        {;
        {"fo:wrap-option","wrap"},; 
        {"style:vertical-align","top"};
        })
      xAdd("style:text-properties",;
        {;
        {"style:font-name",::cFont},;
        {"fo:font-size",ALLTRIM(STR(::nFont))+"pt"};
        })
     XLeft()
   xAdd("style:style",;
     {{"style:name","ceto"+Column(i)},;
      {"style:family","table-cell"},;
      {"style:parent-style-name","co"+Column(i)},;
      {"style:data-style-name",GetDataStyleName(aPicture[i])}; 
     }) 
      xRight()
      xAdd("style:table-cell-properties",;
        {;
        {"fo:wrap-option","wrap"},; 
        {"style:vertical-align","top"},;
        {"fo:background-color","#99ccff"},; 
        {"fo:border-bottom","0.001cm solid #000000" },;
        {"fo:border-left","none"},; 
        {"fo:border-right","none"},; 
        {"fo:border-top","0.001cm solid #000000"};
        })
      xAdd("style:text-properties",;
        {;
        {"style:font-name",::cFont},;
        {"fo:font-size",ALLTRIM(STR(::nFont))+"pt"},;
        {"fo:color","#000000"}; 
        })

      xLeft()

   xAdd("style:style",;
     {{"style:name","cehe"+Column(i)},;
      {"style:family","table-cell"},;
      {"style:parent-style-name","co"+Column(i)},;
      {"style:data-style-name",GetDataStyleName(aPicture[i])}; 
     }) 
      xRight()
      xAdd("style:table-cell-properties",;
        {;
        {"fo:wrap-option","wrap"},; 
        {"style:vertical-align","top"},;
        {"fo:background-color","#c0c0c0"},; 
        {"fo:border-bottom","0.001cm solid #000000" },;
        {"fo:border-left","0.001cm solid #000000"},; 
        {"fo:border-right","0.001cm solid #000000"},; 
        {"fo:border-top","0.001cm solid #000000"};
        })
      xAdd("style:text-properties",;
        {;
        {"style:font-name",::cFont},;
        {"fo:font-size",ALLTRIM(STR(::nFont))+"pt"},;
        {"fo:color","#000000"}; 
        })

      xLeft()

   NEXT







   ::oContent:aItems[1]:Add( HXMLNode():New( "office:body" ) )
   ::oBody := ::oContent:aItems[1]:aItems[4]
   ::oBody:Add( HXMLNode():New( "office:spreadsheet" ) )
   ::oSpreadSheet := ::oBody:aItems[1]

   ::oSpreadSheet:Add( HXMLNode():New( "table:table",, ;
   {{"table:name",cTabName},;
    {"table:style-name","ta1"},;
    {"table:print","false"}} ) )

   ::oTable := ::oSpreadSheet:aItems[1]
   xFix(::oTable)
   FOR i:=1 TO ::nCols

   xAdd("table:table-column", ;
   {{"table:style-name","co"+Column(i)},;
    {"table:default-cell-style-name","ce"+Column(i)};
   })
   NEXT

return self


METHOD AddRow(aValue,nStyleType, nMerge, aFormul, lBreakPage) CLASS HOOCalc
/****
nStyleType=NIL - обычная строка
nStyleType=1 - заголовок (серый с границами)
nStyleType=2 - итоговая строка (светло-голубая с границами)
Если указано nMerge, то сцепить первые nMerge ячеек и записать туда aValue[1].
Если записывается только строка, то aValue может быть стрингом, 
тогда nMerge можно не указывать.
Структура aFormul:
{nColumn,cFo} или для подитогов
{nColumn, Row1, Row2, List1, List2}
*********/

LOCAL oRow, i, v, oCell,i0:=1,cString

//altd()

IF VALTYPE(aValue)=="C".AND.CHR(12)$aValue
   aValue:=""
   lBreakPage:=.T.
ENDIF

oRow := ::oTable:Add( HXMLNode():New( "table:table-row",, ;
            {{"table:style-name",IF(EMPTY(lBreakPage),"ro1","ro2")}} ) )
::nColumn:=0
::nRow++
::nStyleType:=nStyleType


IF (!EMPTY(nMerge).AND.nMerge>1).OR.VALTYPE(aValue)=="C"

    IF VALTYPE(aValue)=="C"
      nMerge:=::nCols
      cString:=aValue
    ELSE
      cString:=aValue[1]
    ENDIF
    i0:=nMerge+1
    ::AddCell(cString,nMerge,1)
    FOR i:=i0 TO ::nCols
       ::AddCell(aValue[i],,i,aFormul)
    NEXT
ELSEIF aValue!=NIL
   FOR i:=1 TO ::nCols
   IF i<=LEN(aValue)
      ::AddCell(aValue[i],,,aFormul)
   ENDIF
   NEXT
ENDIF
Return self


METHOD AddCell(xValue,nMerge,nColumn,aFormul,nStyleType) CLASS HOOCalc
//lWrap по умолчанию .T. - перенос
//lCenter по умолчанию .F. - прижато влево
LOCAL cType:=VALTYPE(xValue),oCell,iFo,cFo,v0
LOCAL v:=OEM2UTF8(v0:=var2char(xValue,@cType))
LOCAL oRow:=ATAIL(::oTable:aItems)
IF nStyleType==NIL
   nStyleType:=::nStyleType
ENDIF
IF EMPTY(nColumn)
   ::nColumn+=IF(EMPTY(nMerge),1,nMerge)
ELSE
   ::nColumn:=nColumn
ENDIF
IF ::lOptimize
   ::aMaxColWidth[::nColumn]:=MAX(::aMaxColWidth[::nColumn],LEN(v0))
ENDIF

IF !EMPTY(aFormul).AND.(iFo:=ASCAN(aFormul,{|x|x[1]==::nColumn}))>0
   cFo:=aFormul[iFo]
   IF LEN(cFo)>2
      cFo:="=SUBTOTAL(9"+cListSep+"["+Range(cFo[1],cFo[1],cFo[2],cFo[3],cFo[4],cFo[5])+"])" 
   ELSE
      cFo:=cFo[2]
   ENDIF
ENDIF

oCell := oRow:Add(HXMLNode():New( "table:table-cell",,CellAttribute(v,cType,::nColumn,nStyleType,nMerge,cFo)))
IF v!=NIL
   oCell:Add(HXMLNode():New("text:p",,,v))
ENDIF

IF !EMPTY(nMerge).AND.nMerge>1
   IF EMPTY(nStyleType)
      oRow:Add(HXMLNode():New("table:covered-table-cell",,;
      {{"table:number-columns-repeated",ALLTRIM(STR(nMerge-1))}}))
   ELSE
      oRow:Add(HXMLNode():New("table:covered-table-cell",,;
      {;
      {"table:number-columns-repeated",ALLTRIM(STR(nMerge-1))},;
      {"table:style-name",IF(nStyleType=1,"cehe_text",IF(nStyleType==2,"ceto_text","ce_text"))};
      }))
   ENDIF
ENDIF

RETURN self


METHOD Save(lShow) CLASS HOOCalc
Local oDoc,aFiles:={},i
LOCAL cSep := set( _SET_DIRSEPARATOR )
Local handle, cDir,cFullDir
LOCAL cOldPath := DirName(),cOldDisk := DiskName()
IF EMPTY(cSep)
   cSep:="\"
ENDIF

 
IF ::lOptimize
   FOR i:=1 TO ::nCols
   IF !EMPTY(::aMaxColWidth[i]).AND.::aMaxColWidth[i]<::aColWidth[i]
      ::aWidthObj[i]:aAttr[2,2]=::ColWidth(::aMaxColWidth[i])
   ENDIF
   NEXT
ENDIF





   xFix(::oTable) //форматируем хвост
   xAdd("table:table-row",;
   {{"table:style-name","ro1"},;
    {"table:number-rows-repeated",alltrim(str(65535-::nRow))};
   })
   xRight()
   xAdd("table:table-cell",;
    {{"table:number-columns-repeated",alltrim(str(::nCols))}})


   cDir :=::cPath
   

   IF Makedir( cDir ) != 0
      EraseDir(cDir)
   ENDIF

   IF MakeDir1( cDir + cSep + "META-INF" ) != 0
      EraseDir( cDir + cSep + "META-INF")
   ENDIF
   oDoc := HXMLDoc():New()
   oDoc:Add( HXMLNode():New( "manifest:manifest",,;
   { {"xmlns:manifest","urn:oasis:names:tc:opendocument:xmlns:manifest:1.0"} } ) )

   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type","application/vnd.oasis.opendocument.spreadsheet"},;
           {"manifest:full-path","/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/statusbar/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/accelerator/current.xml"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/accelerator/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/floater/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/popupmenu/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/progressbar/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/menubar/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/toolbar/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/images/Bitmaps/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Configurations2/images/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type","application/vnd.sun.xml.ui.configuration"},;
           {"manifest:full-path","Configurations2/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type","text/xml"},;
           {"manifest:full-path","content.xml"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type","text/xml"},;
           {"manifest:full-path","styles.xml"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type","text/xml"},;
           {"manifest:full-path","meta.xml"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Thumbnails/thumbnail.png"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type",""},;
           {"manifest:full-path","Thumbnails/"} } ) )
   oDoc:aItems[1]:Add( HXMLNode():New( "manifest:file-entry", HBXML_TYPE_SINGLE, ;
         { {"manifest:media-type","text/xml"},;
           {"manifest:full-path","settings.xml"} } ) )
   FErase(cDir+cSep+"META-INF\manifest.xml")
   oDoc:Save(cDir+cSep+"META-INF\manifest.xml" )


   oDoc := HXMLDoc():New()
   oDoc:Add( HXMLNode():New( "office:document-meta",, {       ;
           {"xmlns:office","urn:oasis:names:tc:opendocument:xmlns:office:1.0"}, ;
           {"xmlns:xlink","http://www.w3.org/1999/xlink" },      ;
           {"xmlns:dc","http://purl.org/dc/elements/1.1/"},      ;
           {"xmlns:meta","urn:oasis:names:tc:opendocument:xmlns:meta:1.0"},     ;
           {"xmlns:ooo","http://openoffice.org/2004/office"},     ;
           {"office:version","1.0"} } ) )

   oDoc:aItems[1]:Add( HXMLNode():New( "office:meta" ) )

   oDoc:Save( cDir + cSep + "meta.xml" )

   handle := FCreate(cDir+cSep+"mimetype" )
   FWrite( handle,"application/vnd.oasis.opendocument.spreadsheet" )
   FClose( handle )

   ::oContent:Save( cDir + cSep + "content.xml" )
    


//   AADD(aFiles,cDir + cSep + "content.xml"

    cFullDir:=TruePath(cDir)



    
   aFiles:=DirectoryRecurse(cFullDir+cSep+"*","D")

    AEVAL(aFiles,{|x,i|aFiles[i]:=SUBSTR(x[1],AT(UPPER(cDir),UPPER(x[1]))+LEN(cDir)+1)})


   FERASE(::cFileName)

   IF FILE(::cFileName)
      SayError("Не могу записать "+::cFileName)
   ELSE

      IF (i := AT( ':', cFullDir ) ) <> 0
         DISKCHANGE( LEFT( cFullDir, i-1 ) )
      ENDIF
      DIRCHANGE(cFullDir)

      HB_ZIPFILE(::cFileName,aFiles,,,,,.T.)    

      DISKCHANGE( cOldDisk )
      DIRCHANGE( cOldPath )
   ENDIF

   IF !EMPTY(lShow)
      ViewCalc(::cFileName,.T.)
   ENDIF
   altd()
   EraseDir(cFullDir)
  //запись в файл дочистки, так как архиватор не отпускает папку
   EraseList(cFullDir,::cTmpDir) 
RETURN .T.


METHOD ColWidth(nLen) CLASS HOOCalc
LOCAL n:=0
STATIC aTahoma,aCourier_New,aArial,aSS
//altd()
IF EMPTY(aTahoma)
   aCourier_New:=ARRAY(15)
   aCourier_New[6]:=7.5
   aCourier_New[7]:=7.5
   aCourier_New[8]:=10
   aCourier_New[9]:=10
   aCourier_New[10]:=11.4
   aCourier_New[11]:=12.8
   aCourier_New[12]:=14.3
   aCourier_New[13]:=14.3
   aCourier_New[14]:=15.7
   aCourier_New[15]:=17

   aTahoma:=ARRAY(15)
   aTahoma[6]:=6.5
   aTahoma[7]:=8
   aTahoma[8]:=9
   aTahoma[9]:=10.5
   aTahoma[10]:=10.5
   aTahoma[11]:=12.5
   aTahoma[12]:=13.5
   aTahoma[13]:=13.5
   aTahoma[14]:=15
   aTahoma[15]:=16.5

   aArial:=ARRAY(15)
   aArial[6]:=5.7
   aArial[7]:=7
   aArial[8]:=8.4
   aArial[9]:=9.8
   aArial[10]:=9.8
   aArial[11]:=11.2
   aArial[12]:=12.5
   aArial[13]:=12.5
   aArial[14]:=15.3
   aArial[15]:=15.3

   aSS:=ARRAY(15)
   aSS[6]:=8.4
   aSS[7]:=8.4
   aSS[8]:=8.4
   aSS[9]:=8.4
   aSS[10]:=9.8
   aSS[11]:=9.8
   aSS[12]:=12.5
   aSS[13]:=12.5
   aSS[14]:=14
   aSS[15]:=14


ENDIF

IF UPPER(::cFont)=="COURIER NEW"
   n:=(nLen+1)*aCourier_New[::nFont]/50
ELSEIF UPPER(::cFont)=="TAHOMA"
   n:=(nLen+1)*aTahoma[::nFont]/50
ELSEIF UPPER(::cFont)=="MS SANS SERIF"
   n:=(nLen+1)*aSS[::nFont]/50
ELSEIF UPPER(::cFont)=="ARIAL"
   n:=(nLen+1)*aArial[::nFont]/50
ENDIF
RETURN ALLTRIM(STR(n,6,3))+"cm"



METHOD RowHeight() CLASS HOOCalc
LOCAL n:=0
STATIC aCourier_New
//altd()
IF EMPTY(aCourier_New)
   aCourier_New:=ARRAY(15)
   aCourier_New[6]:=0.25
   aCourier_New[7]:=0.28
   aCourier_New[8]:=0.372
   aCourier_New[9]:=0.38
   aCourier_New[10]:=0.4
   aCourier_New[11]:=0.45
   aCourier_New[12]:=0.47
   aCourier_New[13]:=0.54
   aCourier_New[14]:=0.58
   aCourier_New[15]:=0.64
ENDIF
IF UPPER(::cFont)=="COURIER NEW"
   RETURN {"style:row-height",ALLTRIM(STR(aCourier_New[::nFont],6,3))+"cm"}
ENDIF
RETURN {"style:use-optimal-row-height","true"}


static function C1TOSTRING(c)
local s
  do case
  case c == "C"
    s := 'string'
  case c == "N"
    s := 'float'
  case c == "D"
    s := 'string'
  otherwise
    s := c
  endcase
return s





STATIC FUNCTION Var2Char(xValue,cType) 
   LOCAL c
   c := 'NIL'
   IF xValue == NIL
      RETURN c
   ENDIF
   BEGIN SEQUENCE
   IF cType==NIL
      cType := ValType( xValue )
   ENDIF
   IF cType=="N".AND.EMPTY(xValue)
      cType:="C"
      xValue:=""
   ENDIF
   DO CASE 
      CASE cType == 'C'
         c := TRIM(xValue)
      CASE cType == 'D'
         c := dToc( xValue )
      CASE cType == 'L'
         c := IIF( xValue, '.T.', '.F.' )
      CASE cType == 'N'
         c := alltrim(Str(xValue))
      CASE cType == 'M'
         c := TRIM(xValue)
      Otherwise
         c := "Type: " + cType
   endcase
  END SEQUENCE
RETURN c


PROCEDURE EraseList(cDir,cTmpDir) //сделать видимой и убрать из PLL
LOCAL cFile:=cTmpDir+"mustdel.lst"
LOCAL aList := FParse(cFile)
LOCAL nHandle,i
IF !EMPTY(aList)
   FOR i:=1 TO LEN(aList)
   EraseDir(aList[i,1])
   IF !IsDir(aList[i,1])
      aList[i]:=NIL
   ENDIF
   NEXT
ENDIF
AADD(aList,{cDir})
nHandle := fCreate(cFile)
FOR i:=1 TO LEN(aList)
IF !EMPTY(aList[i])
   fWrite(nHandle,aList[i,1]+CHR(13)+CHR(10))
ENDIF
NEXT
FCLOSE(nHandle)
RETURN


STATIC FUNCTION xFix(o)
xObj:=o
RETURN xObj

STATIC FUNCTION xAdd(cName,aa,v)
RETURN xObj:Add(HXMLNode():New(cName,,aa,v))

STATIC FUNCTION xRight()
xParent:=xObj
xObj:=ATAIL(xObj:aItems)
RETURN xObj

STATIC FUNCTION xLeft()
xObj:=xParent
RETURN xObj


STATIC FUNCTION Column(i)
RETURN ALLTRIM(STR(i))


STATIC FUNCTION GetDataStyleName(cPict)
LOCAL i:=RAT(".",cPict)
LOCAL n
IF "9"$cPict
   IF LEN(cPict)==8.AND."/"$cPict
      RETURN "N100" //дату текстом даем
   ELSEIF i=0
      n:=0
   ELSE
      n:=LEN(cPict)-i
   ENDIF
   RETURN "N"+STR(n,1,0)
ENDIF
RETURN "N100"

STATIC FUNCTION CellAttribute(v,cType,nColumn,nStyleType,nMerge,cFormula)
LOCAL aRet
IF v!=NIL
   IF EMPTY(nStyleType)
      aRet:={;
       {"office:value-type",C1TOSTRING(cType)}}
   ELSE
      aRet:={;
       {"table:style-name",IF(cType=="C",;
          IF(nStyleType=1,"cehe_text",IF(nStyleType=2,"ceto_text","ce_text")),;
          IF(nStyleType=1,"cehe","ceto")+Column(nColumn));
       },;
       {"office:value-type",C1TOSTRING(cType)}}
   ENDIF
   IF !EMPTY(cFormula)
      AADD(aRet,{"table:formula",cFormula})
   ENDIF
   IF cType="N"
      AADD(aRet,{"office:value", v})
   ENDIF
   IF !EMPTY(nMerge).AND.nMerge>1
      AADD(aRet,{"table:number-columns-spanned",ALLTRIM(STR(nMerge))})
      AADD(aRet,{"table:number-rows-spanned","1"})
   ENDIF
ENDIF
RETURN aRet



FUNCTION ViewCalc(cFile,lAsTemplate)
LOCAL oApp,oDeskTop,oDoc,oSheets,oAS,oView
LOCAL aProp:={}

TRY
   oApp := CreateObject( "com.sun.star.ServiceManager" )
CATCH
   ALERT( "OOCalc не доступен!" )
   ClearTotRepStatic()
   RETURN .F.
END

oDeskTop:=oApp:createInstance("com.sun.star.frame.Desktop")

IF !EMPTY(lAsTemplate)
   aProp:=ARRAY(1)
   oProperty:=oApp:Bridge_GetStruct("com.sun.star.beans.PropertyValue")  
   oProperty:Name:= "AsTemplate"  
   oProperty:Value:= .T.  
   aProp[1]:=oProperty
ENDIF


oDoc:=oDeskTop:LoadComponentFromURL(ConvertToURL(cFile),"_blank", 0,aProp) 
RETURN oDoc

FUNCTION ConvertToURL(cFile)
LOCAL cRet:="",i,ch
FOR i:=1 TO LEN(cFile)
ch:=cFile[i]
SWITCH ch
   case ' ' 
   cRet+= "%20"
   exit
   case '\' 
   cRet += "/"
   exit
   default
   cRet += ch
END       
NEXT
RETURN "file://localhost/" + cRet


STATIC FUNCTION Range( nCol1, nCol2, nRow1, nRow2,nSheet1,nSheet2 )
RETURN GetMySheet(nSheet1)+GetMyCol( nCol1 ) + ALLTRIM( STR( nRow1 ) ) + ":" + GetMySheet(nSheet2)+GetMyCol( nCol1 ) + ALLTRIM( STR( nRow2 ) )

STATIC FUNCTION GetMyCol( nCol )
   IF nCol <= 26
      RETURN CHR( ASC( "A" ) + nCol - 1 )
   ELSEIF nCol > 26 * 8
      RETURN "H" + CHR( ASC( "A" ) + nCol - 26 * 8 - 1 )
   ELSEIF nCol > 26 * 7
      RETURN "G" + CHR( ASC( "A" ) + nCol - 26 * 7 - 1 )
   ELSEIF nCol > 26 * 6
      RETURN "F" + CHR( ASC( "A" ) + nCol - 26 * 6 - 1 )
   ELSEIF nCol > 26 * 5
      RETURN "E" + CHR( ASC( "A" ) + nCol - 26 * 5 - 1 )
   ELSEIF nCol > 26 * 4
      RETURN "D" + CHR( ASC( "A" ) + nCol - 26 * 4 - 1 )
   ELSEIF nCol > 26 * 3
      RETURN "C" + CHR( ASC( "A" ) + nCol - 26 * 3 - 1 )
   ELSEIF nCol > 26 * 2
      RETURN "B" + CHR( ASC( "A" ) + nCol - 26 * 2 - 1 )
   ELSEIF nCol > 26
      RETURN "A" + CHR( ASC( "A" ) + nCol - 26 - 1 )
   ENDIF
RETURN CHR( ASC( "A" ) + nCol - 1 )


STATIC FUNCTION GetMySheet( nSheet )
//IF nSheet==NIL
   RETURN "."
//ENDIF
//RETURN "Report"+IF(nSheet==1,"",ALLTRIM(STR(nSheet)))+"!"








