int xlOpenXMLWorkbook = 51;  // Тип файла xlsx
int xlExcel8 = 56;           // Тип файла xls (до 2007)

tCreateExcelObjects(fullFileName,Caption)
{
  _XlsObj = CreateObject("Excel.Application");
  _XlsObj.Visible = false;
  _XlsObj.Workbooks.Add(static_cast_to_string(fullFileName));
  _XlsObj.ActiveWindow.Caption = OemToAnsi(Caption);
  return 0;
}


tOpenExcelObjects(fullFileName,Caption)
{
  _XlsObj = CreateObject("Excel.Application");
  _XlsObj.Visible = false;
  _XlsObj.Workbooks.Open(static_cast_to_string(fullFileName));
  _XlsObj.ActiveWindow.Caption = OemToAnsi(Caption);
  return 0;
}

tSetExcelCaption(Caption)
{
  if ( IsObject(_XlsObj) )
     _XlsObj.Caption = OemToAnsi(Caption);
  return 0;
}

tMakeExcelVisible()
{
  if ( IsObject(_XlsObj) )
  {
     _XlsObj.Visible = true;
     ActivateExcelWindow(_XlsObj);
  }
  return 0;
}

tGetExcelWshByIndex(i)
{
//_WshObj = _XlsObj.Workbooks.Item(1).Worksheets.Item(i);
  _WshObj = _XlsObj.Worksheets(i);
  _WshIndex = i;
  return 0;
}
tGetExcelWshByName(name)
{
  _WshObj = _XlsObj.Worksheets(static_cast_to_string(name));
  return 0;
}
tWriteStringToExcelCell(cell,str)
{
  FreeSpaceString(str,3,"");
  _WshObj.Range(static_cast_to_string(cell)).Value = static_cast_to_string(str);
  return 0;
}

tWriteStringToExcelCell2(i,j,str)
{
  FreeSpaceString(str,3,"");
  _WshObj.Cells(i,j).Value = static_cast_to_string(str);
  return 0;
}

tWriteNumToExcelCell(cell,num)
{
  _WshObj.Range(static_cast_to_string(cell)).Value = num;
  return 0;
}

tWriteNumToExcelCell2(i,j,num)
{
  _WshObj.Cells(i,j).Value = num;
  return 0;
}

tReadStringFromExcelCell(cell)
{
  return _WshObj.Range(static_cast_to_string(cell)).Value;
}

tReadStringFromExcelCell2(i,j)
{
  return _WshObj.Cells(i,j).Value;
}

tReadNumFromExcelCell(cell)
{
  return _WshObj.Range(static_cast_to_string(cell)).Value;
}

tReadNumFromExcelCell2(i,j)
{
  return _WshObj.Cells(i,j).Value;
}


tHorizontalAlignmentExcelCell(cell,n)
{
  _WshObj.Range(static_cast_to_string(cell)).HorizontalAlignment = n;
  return 0;
}

tFontSizeExcelCell(cell,n)
{
  _WshObj.Range(static_cast_to_string(cell)).Font.Size = n;
  return 0;
}

tFontSizeExcelCell2(row1,col1,row2,col2,n)
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Font.Size = n;
  return 0;
}

tFontNameExcelCell(cell,str)
{
  _WshObj.Range(static_cast_to_string(cell)).Font.Name = str;
  return 0;
}

tWshExcelSelect()
{
  _WshObj.Select();
  return 0;
}

tCopyExcelRow(n)
{
  _WshObj.Rows(n-1).Select();
  _XlsObj.Selection.Copy(_WshObj.Rows(n));
  return 0;
}

tCopyExcelRows(n,count)
{
  char str[255];
  sprintf(str,"%d:%d",n-count,n-1);
  _WshObj.Rows(static_cast_to_string(str)).Select();
  _XlsObj.Selection.Copy(_WshObj.Rows(n));
  return 0;
}

tCopyExcelRows2(n1,n2,count)
{
  char str[255];
  sprintf(str,"%d:%d",n1,n1+count-1);
  _WshObj.Rows(static_cast_to_string(str)).Select();
  _XlsObj.Selection.Copy(_WshObj.Rows(n2));
  return 0;
}

tCopyExcelRange(row1,col1,row2,col2,row3,col3)
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Select();
  _XlsObj.Selection.Copy(_WshObj.Range(_WshObj.Cells(row3,col3),_WshObj.Cells(row3,col3)));
  return 0;
}


tCopyExcelColumns(n,count)
{
  var cell1 = _WshObj.Cells(1,n-count);
  var cell2 = _WshObj.Cells(512,n-1);
  var cell3 = _WshObj.Cells(1,n);
  _WshObj.Range(cell1,cell2).Copy();
  _WshObj.Range(cell3,cell3).Select();
  _WshObj.Paste();
  return 0;
}

tInsertExcelRows(n,count)
{
  for ( int i = 0; i < count; i++ ) 
    _WshObj.Rows(n).EntireRow.Insert();
  return 0;
}

tInsertExcelColumns(n,count)
{
  for ( int i = 0; i < count; i++ ) 
    _WshObj.Columns(n).Insert();
  return 0;
}


tDeleteExcelRows(n,count)
{
  for ( int i = 0; i < count; i++ ) 
    _WshObj.Rows(n).Delete();
  return 0;
}

tDeleteExcelColumns(n,count)
{
  for ( int i = 0; i < count; i++ ) 
    _WshObj.Columns(n).Delete();
  return 0;
}

tDeleteExcelRange(cell,shift) 
{
  // shift: 1 - сдвиг вверх, 0 - сдвиг влево
  int sh = -4159; if ( shift ) sh = -4162;
   _WshObj.Range(static_cast_to_string(cell)).Delete(sh);
  return 0;
}

tDeleteExcelRange2(row1,col1,row2,col2,shift) 
{
  // shift: 1 - сдвиг вверх, 0 - сдвиг влево
  int sh = -4159; if ( shift ) sh = -4162;

   _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Delete(sh);
  return 0;
}

tWshExcelCellSelect(n,m)
{
  _WshObj.Cells(n,m).Select();
  return 0;
}

tWshExcelHide()
{
  _WshObj.Visible = false;
  return 0;
}

tWshExcelRemove(i)
{
  _XlsObj.DisplayAlerts = false;// юЄъы■ўшЄ№ яЁхфєяЁхцфхэшх
  _XlsObj.Worksheets(i).Delete();
  _XlsObj.DisplayAlerts = true;
  return 0;
}

//////////////////////////////////////

tCopyExcelWshByIndex(i,str) 
{
  _XlsObj.Worksheets(i).Copy(_XlsObj.Worksheets(i));
  if ( atoi(_XlsObj.Version()) < 12 ) // версии Excel до 2007
  {
    _XlsObj.Worksheets(i+1).Select();
    _XlsObj.Worksheets(i+1).Cells.Select();
    _XlsObj.Selection.Copy();
    _XlsObj.Worksheets(i).Select();
    _XlsObj.Worksheets(i).Cells.Select();
    _XlsObj.ActiveSheet.Paste();
    _XlsObj.CutCopyMode = 0;
  }

  _XlsObj.Worksheets(i).Name=OemToAnsi(str);
  _WshIndex = i;
  return 0;
}

tCopyExcelWshByIndToInd(i1,i2,str) 
{
  _XlsObj.Worksheets(i1).Copy(_XlsObj.Worksheets(i2));
  _XlsObj.Worksheets(i2).Name=OemToAnsi(str);
  _WshIndex = i2;
  return 0;
}

tBordersExcelCell(Cell,l,r,t,b,n) 
{
   // левая граница - линия
   if(l) _WshObj.Range(static_cast_to_string(Cell)).Borders(1).LineStyle = n;
   // правая граница - линия
   if(r) _WshObj.Range(static_cast_to_string(Cell)).Borders(2).LineStyle = n;
   // верхняя граница - линия
   if(t) _WshObj.Range(static_cast_to_string(Cell)).Borders(3).LineStyle = n;
   // нижняя граница - линия
   if(b) _WshObj.Range(static_cast_to_string(Cell)).Borders(4).LineStyle = n;

   return 0;
}
   
tBordersExcelCell2(row1,col1,row2,col2,l,r,t,b,n) 
{
   // левая граница - линия
   if(l) _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Borders(1).LineStyle = n;
   // правая граница - линия
   if(r) _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Borders(2).LineStyle = n;
   // верхняя граница - линия
   if(t) _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Borders(3).LineStyle = n;
   // нижняя граница - линия
   if(b) _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Borders(4).LineStyle = n;

   return 0;
}

   
tMergeCellsExcelCell(cell,b) 
{
  _WshObj.Range(static_cast_to_string(cell)).MergeCells = b;
  return 0;
}

tMergeCellsExcel2(row1,col1,row2,col2,b)
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).MergeCells = b;
  return 0;
}

tExcelPageBreaks(row)
{
  char str[256]; 
  sprintf(str,"%d:%d",row,row);
  var comRange = _WshObj.Range(static_cast_to_string(str));
  var hBreak = _WshObj.HPageBreaks();
  hBreak.Add(comRange);
  return 0;
}

tExcelRowHeight(row,h)
{
  char str[256]; 
  sprintf(str,"%d:%d",row,row);  
  _WshObj.Rows(static_cast_to_string(str)).Select();
  _XlsObj.Selection.RowHeight = h; 
  return 0;
}
tExcelRowHeight2(row,h)
{
  _WshObj.Rows(static_cast_to_string(row)).RowHeight = h;
  return 0;
}
tExcelRowAutoHeight(row)
{
  try 
  { 
   char str[256]; 
   sprintf(str,"%s:%s",static_cast_to_string(row),static_cast_to_string(row));  
   _WshObj.Rows(static_cast_to_string(str)).AutoFit();
  }
  catch(...)
  {
   _WshObj.Rows(row).AutoFit();
  }
  return 0;
}
tExcelRowFontBold(row,b)
{
  _WshObj.Rows(to_string(row)).Font.Bold=b;
  return 0;
}
tExcelRangeInteriorColor(row1,col1,row2,col2,color)
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Interior.Color = color;
  return 0;
}
tExcelColumnWidth(col,h)
{
  try 
  { 
   _WshObj.Columns(col).Select();
   _XlsObj.Selection.ColumnWidth = h;
  }
  catch(...)
  {
   char str[256]; 
   sprintf(str,"%s:%s",static_cast_to_string(col),static_cast_to_string(col));  
   _WshObj.Columns(static_cast_to_string(str)).Select();
   _XlsObj.Selection.ColumnWidth = h; 
  }

  return 0;
}
tExcelColumnWidth2(col,h)
{
  //почему то адресация "1:1" не работает... поэтому пришлось "колходить"
  //падает _WshObj.Columns("1:1").ColumnWidth=x; или _WshObj.Columns("1").ColumnWidth=x;
  try
  {
    if (col>26) return;
    int charA=65;
    char str[256]; 
    sprintf(str,"%c:%c",charA+col-1,charA+col-1);  
    _WshObj.Columns(to_string(str)).ColumnWidth = h; 
  }
  catch(...)
  {
    ;
  }
  return 0;
}

tExcelColumnAutoWidth(col)
{
  try 
  { 
   _WshObj.Columns(col).AutoFit();
  }
  catch(...)
  {
   char str[256]; 
   sprintf(str,"%s:%s",static_cast_to_string(col),static_cast_to_string(col));  
   _WshObj.Columns(static_cast_to_string(str)).AutoFit();
  }
  return 0;
}

tHorizontalAlignmentExcel(cell,i) 
{
  _WshObj.Range(static_cast_to_string(cell)).HorizontalAlignment = i;  
  return 0;
}

tHorizontalAlignmentExcel2(row1,col1,row2,col2,i) 
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).HorizontalAlignment = i;  
  return 0;
}

tVerticalAlignmentExcel(cell,i) 
{
  _WshObj.Range(static_cast_to_string(cell)).VerticalAlignment = i;  
  return 0;
}

tVerticalAlignmentExcel2(row1,col1,row2,col2,i) 
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).VerticalAlignment = i;  
  return 0;
}

tTextWrapExcel(cell,b) 
{
  _WshObj.Range(static_cast_to_string(cell)).WrapText = b;  
  return 0;
}

tTextWrapExcel2(row1,col1,row2,col2,b) 
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).WrapText = b;  
  return 0;
}

tNumberFormatExcel(cell,s)
{
  _WshObj.Range(static_cast_to_string(cell)).NumberFormat = static_cast_to_string(s);  
  return 0;
}

tNumberFormatExcel2(row1,col1,row2,col2,s)
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).NumberFormat = static_cast_to_string(s);  
  return 0;
}

tSaveAs(&Workbook, FileName)
{
   // До 2007 проблем с сохранением не было
   // С 2007 если сохранять файл с расширением не соотвествующим содержимому
   // (расширение xls а пишется xlsx) то потом срабатывает система безопасности 
   // офиса при открытии таких файлов. Поэтому с 2007 Офиса писать надо с указанием
   // типа файла
   if ( atoi(_XlsObj.Version()) < 12 ) // версии Excel до 2007
      Workbook.SaveAs(FileName); 
   else
   {
      char Dir[MAX_PATH+1], Path[MAX_PATH+1], nam[MAX_PATH+1], ext[MAX_PATH+1];
      splitpath(FileName,Dir,Path,nam,ext);
      LowerString(ext);
      int DefaultType = xlExcel8;
      if ( ext==".xlsx" )
         DefaultType = xlOpenXMLWorkbook;
      
      Workbook.CheckCompatibility = false;
      Workbook.SaveAs(FileName,DefaultType);
   }
}

tSaveExcelObjects(FileName)
{
  if ( Str_Cmp(FileName," "," ") )
     tSaveAs(_XlsObj.ActiveWorkbook,FileName);
  else
  {
    if ( atoi(_XlsObj.Version()) >= 12 ) // версии Excel 2007 и выше
       _XlsObj.ActiveWorkbook.CheckCompatibility = false;

    _XlsObj.ActiveWorkbook.Save(); 
  }

  return 0;
}

tSaveExcelObjects1()
{
  if ( atoi(_XlsObj.Version()) >= 12 ) // версии Excel 2007 и выше
     _XlsObj.ActiveWorkbook.CheckCompatibility = false;

  _XlsObj.ActiveWorkbook.Save(); 
  return 0;
}

tUnderlineExcel(cell,i)
{
  bool b = static_cast_to_bool(i);
  _WshObj.Range(static_cast_to_string(cell)).Font.Underline = b;  
  return 0;
}

tAddExcelWorkbook(n,fullFileName,tmpnm)
{
  _XlsObj.Workbooks.Add(static_cast_to_string(fullFileName)); // создаем новую книгу на основе шаблона
  tSaveAs(_XlsObj.Workbooks(n),tmpnm);

  return 0;
}

tCopyExcelWshByName(i,name)
{
  _XlsObj.Worksheets(i).Copy(_XlsObj.Worksheets(i));
  _WshObj = _XlsObj.Worksheets(i);
  _WshIndex = i;
  _WshObj.Name = name;

  return 0;
}

tSaveExcelWorkbook(n,tmpnm)
{
  tSaveAs(_XlsObj.Workbooks(n),tmpnm);

  return 0;
}

tGetWshCountExcel(&tCount)
{
  tCount = _XlsObj.Worksheets.Count();
  return 0;
}

tWshExcelVisible(b)
{
  _WshObj.Visible = b;
  return 0;
}

tWshExcelOrientation(p)
{
  _WshObj.PageSetup.Orientation = p;
  //_WshObj.Visible = b;
  return 0;
}


tCopyExcelRowsWsh(wsh1,wsh2,n1,n2,count)
{
  _WshObj = _XlsObj.Worksheets.Item(wsh2);
  _WshObj.Select();
  _WshObj.Rows(n2).Select();

  _WshObj = _XlsObj.Worksheets.Item(wsh1);
  _WshObj.Select();
  char str[255];
  sprintf(str,"%d:%d",n1,n1+count-1);
  _WshObj.Rows(static_cast_to_string(str)).Select();
  _XlsObj.Selection.Copy();//_WshObj.Rows(n2));

  _WshObj = _XlsObj.Worksheets.Item(wsh2);
  _WshObj.Select();
  _WshObj.Rows(n2).Select();
  _WshObj.Paste();

  return 0;
}

tExcelClearContents(row1,col1,row2,col2,LineS)
{
  _WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2)).Select();
  _XlsObj.Selection().ClearContents();
  if ( LineS ) _XlsObj.Selection().Borders().LineStyle=0;                   
  return 0;
}

tExcelQuit()
{
  if ( IsObject(_XlsObj) )
  {
     _XlsObj.DisplayAlerts=0;
     _XlsObj.Quit();
     _XlsObj=0;
  }
  return 0;
}

tExcelWshCount()
{
  return _XlsObj.Worksheets.Count();
}

tExcelRefWshByIndex(i)
{
  return _XlsObj.Worksheets(i);
}

tDelExcelCol(sheet,n,count)
{
  for ( int i = 0; i < count; i++ ) 
    sheet.Columns(n).Delete();
  return 0;
}

tFreeExcelClipboard()
{
   if ( IsObject(_XlsObj) && IsObject(_WshObj) )
   {
      _WshObj.Range("A1").Select();
      _XlsObj.Selection.Copy();
      _XlsObj.CutCopyMode = false;
   }
   return 0;
}
tSetPrintAreaExcel(row1,col1,row2,col2)
{
  var range=_WshObj.Range(_WshObj.Cells(row1,col1),_WshObj.Cells(row2,col2));
  _WshObj.PageSetup.PrintArea=to_string(range.Address);
  return 0;
}
tPageSetupOptimalExcel()
{
  _WshObj.PageSetup.Zoom = false;
  _WshObj.PageSetup.FitToPagesWide = 1;
  _WshObj.PageSetup.FitToPagesTall = 1;
  return 0;
}

tPageSetupZoomExcel(p)
{
  _WshObj.PageSetup.Zoom = p;
  return 0;
}

tPageSetupMarginExcel(right,left,top,bottom)
{
  double inch = 2.54;
  if(right) _WshObj.PageSetup.RightMargin  = _XlsObj.InchesToPoints(right/inch);
  if(left)  _WshObj.PageSetup.LeftMargin   = _XlsObj.InchesToPoints(lef1/inch);
  if(top)   _WshObj.PageSetup.TopMargin    = _XlsObj.InchesToPoints(top1/inch);
  if(bottom)_WshObj.PageSetup.BottomMargin = _XlsObj.InchesToPoints(top1/inch);
  return 0;
}

tCenterHorizontallyExcel()
{
  _WshObj.PageSetup.CenterHorizontally = 1;
  return 0;
}

tProtectXls(sPassword,bStructure,bWindow)
{
   _XlsObj.Workbooks(1).Protect(static_cast_to_string(sPassword),bStructure,bWindow);
  return 0;
}

tIsWshExcelVisible()
{
  return _WshObj.Visible;
}