Дневник megavtogal.com

МЕНЮ
  • Контакты
  • Статьи

Команды excel в foxpro

Обновлено: 22.01.2025

For clarity's sake, the full object hierarchy is used for each command. Using a 'With oExcel' will tend to make your code more readable.

Connecting to Excel

We'll assume throughout this page that you named your Excel object oExcel and your workbook oWorkbook.

Constants for Excel

Excel Constants documents the constants that Excel's macros use (xlThis, xlThat etc)
2007/08/30 Mike Mohr - Excel 2003 Constants MSDN Library
2007/08/30 Mike Mohr - Excel 2007 (office 12) Constants MSDN Library

Opening an existing XLS

Creating a blank workbook:

Creating a new workbook based on a template:

You can turn off Excel's alerts with the Display Alerts property: when set to False, Excel automatically chooses the default answer to any message:

Saving a workbook as Excel 97/03 from Excel 2007

Using Save As while automating Excel 2007 creates a 2007 style workbook with an XLS extension (regardless of compatibility settings) unless the file format is specified:

Controlling visibility

If the Excel window is not visible it is harder for the user to interact with Excel. This makes it slightly safer for your automation as the user is less likely to issue commands in the middle of your automation.

Controlling Interaction

Also, if it is preferred that Excel be seen during automation set these two properties to .F.


After completing automation, return their value to .T. to allow the user to start interaction
The Interactive property is the one that controls whether the user is allowed to interact with Excel. When set to .F., the user gets the hourglass icon when they hover over Excel, and mouse clicks on the Excel application are ignored.

The UserControl property does NOT prevent the user from interacting with Excel. That property indicates whether the Excel application was opened by the user (.T.), or whether the Excel application was opened programmatically via CREATEOBJECT() (.F.). If you used GETOBJECT( , "Excel.Application" ) to get a reference to the Excel application, you can use this property to determine if you are sharing the application with the user or not. One cool thing is that this property is automatically updated if the user closes Excel.
See UserControl Property [Excel 2007 Developer Reference]
Bottom line: even though you CAN change UserControl , I recommend that you NOT do that.

Storing data to a cell

Set the font color and style

Set Excel Cell to Text Format

If your app exports user-entered text data from a field into an Excel cell, you always convert the cell's numberformat to text prior to setting the value. I forgot this in an app once, and a user entered a long string of equal signs into a notes field in VFP, which generated a COM error when setting the cell's value (Excel doesn't like "====== A1").NumberFormat = "@" oExcel.Range("A1").Value = cursorA.MemoField

Getting data into Excel

Assuming your table is selected. First the easy way. You can make a new file in an old Excel format which all version should be able to read.


Or if you have a pre-formatted template (.XLS or .XLT) that you want to paste into. Note that this method will not handle Memo fields.

Selecting a range using the Cells collection

Resize all columns

Insert two rows before the first row

Closing Excel

You'll still need to handle closing questions like saving changes and file format changes. And you'll need to release your oExcel object

I find that an instance of Excel remains after quiting. To Clear issue:

Closing Excel with all changes discarded - no question asked

Iterate through Excel's Worksheets

Finding text in Excel

Searching for "Tax"


Range("A1:H250") specifies that we're searching columns A to H (inclusive) and rows 1-250.
oExcel.ActiveCell is where to start searching, and -4123 is the constant for xlFormulas. I theorize that this means 'if there is a formula, search its code rather than its output.' 1 is the constant for xlWhole, meaning match against all the text in the cell. You could swap in 2 to get partial matches.
-- Tom Cerul

You have to be careful when specifying the extra parameters to Find as they persist between searches, as specified in the Excel documentation:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you don't specify values for these arguments the next time you call the method, the saved values are used. Setting these arguments changes the settings in the Find dialog box, and changing the settings in the Find dialog box changes the saved values that are used if you omit the arguments. To avoid problems, set these arguments explicitly each time you use this method.

Developing new code

Sometimes the easiest way to figure out how to code an automation routine is this: open Excel, tell it to record a macro, do the task you want to automate. Stop the recording and look at the code that it generated.

Putting it all together, a runnable example

First, COPY TO all fields (or some) in Excel Format

Having worked with excel a lot a few notes:
Office applications are not as much backward compatible as VFP is. For that reason I suggest not to use VFP's direct Excel related commands like:

copy to myExcel type xls && or xl5,xl8.
import . type xl8

etc. These commands are version dependant directly within VFP themselve. You immediately lose data with these commands.
-The number of rows you can copy is limited for example (VFP5 copied 16384 max while VFP9 copies 65536 max, but as new Excel versions come into market those limits are not sufficient anymore).
-Memo fields are immediately dropped as with any 'copy to' command

Data To Clip() - Do not use Data To Clip() for Excel transfers.
-You lose Memo fields but worse you get a transform() version of memo fields.
-You're limited on rowcount that you can transfer with this method. There is no exact number and sounds like related with Excel version + available memory. It's possible you end with much fewer rows than you could transfer using 'copy to'. Likely you'd get unpredictable exceptions.

Instead you can use:

Copy to myExcel.xls type fox2x && actually creating a dBaseIII compatible file. Excel recognizes internally
Copy to myExcel.csv type csv && CSV files are natively recognized

Both fail to transfer memo fields and CSV might have problems with datatypes converted correctly (mostly with date fields) but in general are better than Data To Clip() and 'copy . type xls'.

Similar to Data To Clip() you can copy to a tab delimited file, read it into clipboard with FileToStr() and pasteSpecial() in Excel. Works better than Data To Clip() but it again falls short of transferring memo fields.

Excel (especially newer versions) also recognizes XML and HTM ( table tags ).

My best preferance is to transfer data using ADO instead. Passing with ADO uses Excel's own VBA commands to 'paste' the data. Here is a sample sending data to Excel and putting data starting at A10:


Above code also shows another alternative to transferring data to Excel via array instead of pasting.

Getting Data from Excel


Using automation, you can write loops which go through a worksheet cell-by-cell reading the data. Although this technique is relatively simple, it appears to be universally acknowledged as being very slow.
Another technique which is promoted in different places is to use ADO or ODBC/OLEDB, which is quite fast, but a bit more complicated.
However, it was Cetin's example above, which uses an array to populate a worksheet, which helped me to discover a third option. It turns out that it is very simple, and also very fast, to use automation to read data from Excel into an array.

Note that this technique allows you to populate memo fields in a cursor. If a column contains cells with more than 254 characters of text in them, the entire text is copied to the array, and from there can dumped directly into a memo field using either of the FROM ARRAY techniques above. That's something you can't do with APPEND FROM .. XLS|XL5|XL8 .


Note that a data cell in Excel must be compatible with the data type of the VFP cursor field in which you wish to store that data. In most cases this isn't a problem, but one notable exception is if you wish to a copy a column of numbers from Excel into a Character field in VFP. Any cells in Excel which are stored as numbers will be ignored when you try to copy them into a VFP character field, leaving the VFP field empty. (Numbers stored as text in Excel are normally differentiated by a little green triangle in the upper left corner of the cell.) You could manually edit the Excel spreadsheet to correct this problem, but a far simpler solution is to modify your VFP code to transform the data read from Excel before copying it into the VFP cursor. The sample code above has been updated to include this solution.


If you are using VFP 8 or earlier, arrays are limited to 65,000 elements, so you will get an error if you attempt to read more that 65,000 cells from Excel into an array. However, VFP 9 raised the limit on arrays to 2GB, which should be more than enough for any import.

Незнаю, насколько с выходом VFP9 и появление объекта ReportListiner это нужно, но вопросы до сих пор задают. Может, это учебные задания?

Вобщем, написал "небольшую" статью по переводу кода макроса Excel в синтакси FoxPro. Сейчас там полно синтаксических ошибок.

Нужна ли эта статья вообще и если нужна, то ничего существенного не пропустил?

По поводу ускорения экспорта в Excel - это будет отдельная статья (если это надо). Там слишком много писать надо.

==========================
Кстати, может кто знает, как сделать "предварительное связывание" для объектов самого Excel.

Т.е. когда я пишу непосредственно в командном окне FoxPro

Работает механизм IntelliSens для всех трех объектов: loExcel, loBook, loSheet. Для каждого свой список свойств и методов выпадает. А вот непосредственно в программе получается задать только для loExcel


Можно ли как-то запустить тот же режим и для "вложенных" объектов внутри PRG?

Добрый день Владимир!

Можно ли как-то запустить тот же режим и для "вложенных" объектов внутри PRG?

Не уверен, но помоему не льзя так просто. Надо в foxcode.dbf забивать весь синтакси для объектов Excel. Муторное дело

Нужна ли эта статья вообще и если нужна, то ничего существенного не пропустил?

Очень нужно! Сам испытываю всегда определенную не уверенность при работе с Excel и вообще с VBA, так как мало для них програмировал.

Маленькое пожелание может быть тема отдельной статьи.
Простейшее форматирование у меня проходит. примерно так


Вроде юзеры довольны, но я нет. Я хотел построить сводную таблицу в этом же блоке . Бислся 2 дня но не смог хотя действия были примерно техже принципов как и в ващей статье (записал макрос и переводил его под VFP. Использовал HELP Excel и VFP).
Мне тут было нужно дать юзеру инструмент для анализа, а печать на бумагу вовсе не нужна

LOCAL loExcel AS Excel.Application, loBook as Excel.Workbook, loSheet as Excel.Worksheet


------------------
Лучше переесть, чем недоспать.
Не спеши, а то успеешь. AleksM
loBook as Excel.Workbook, loSheet as Excel.Worksheet

Хочется чтоб выподал список PEMS при вводе точки для loBook. и loSheet. именно при редактировании кода в форме или prg,но нет его

Отредактировано (11.05.05 09:53)

Не понял, что нужно сделать? Если удалось записать макрос, который делает все, что нужно, то какие проблемы перевести его в синтаксис FoxPro?

Сложности могут быть только если в качестве одного из параметров какой-либо функции используется массив. Но тут надо экспериментировать. Возможно, проще всего будет сохранить макрос и запускать его в момент открытия шаблона.

У меня все показывает


------------------
Лучше переесть, чем недоспать.
Не спеши, а то успеешь.

Обычно с массивами проблем не возникало. Передача по ссылке отрабатывает без проблем, например, при использовании подитогов. А вот со сводными таблицами, нестыковки могут возникнуть на уровне разных версий Excelя, в частности, 2000 и XP по разному описывают поля используемые в секции данных. Но это уже не относится к технологии переноса


------------------
Лучше переесть, чем недоспать.
Не спеши, а то успеешь. Я удалил тот код с которым я парился
Вот записал новый макрос в Excel и привожу проблемную строку из макроса

В этой строке не смог сделать коректный перевод в синтаксис VFP. Жирным выделил не понятное места. Хотя через месяц после тех дел могу и не точно объяснить суть той проблямы. Воспроизвести нет времени, прошу прошения.

Не могу сообразить как передавать параметры в методы сводной таблицы при ее содании или модификации. Извините если не очень понятно говорю о проблеме

Отредактировано (11.05.05 10:44)


------------------
Лучше переесть, чем недоспать.
Не спеши, а то успеешь. AleksM
А вот со сводными таблицами, нестыковки могут возникнуть на уровне разных версий Excelя, в частности, 2000 и XP по разному описывают поля используемые в секции данных Аказывается дела еще хуже чем я думал. у юзеров стотит Excel от 2000 до 2003. Хай сами строят сводные таблицы. Хотя это не выход, а первые эмоции на такие нюансы. Простите за флейм.


Еше раз прошу извинения за то что не могу конкретно описать суть дело было месяц назад. А ща горю по другой задаче. Нт времени воспроизводить код в VFP. Могу сказать что вот с этой строкой не справился

[quote=Amelichev]ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"лог!R1C1:R13C22").CreatePivotTable TableDestination:="", _
TableName:="СводнаяТаблица1", DefaultVersion:=xlPivotTableVersion10 [/quote
После CreatePivotTable пробел и далее мне непонятно как писать в VFP

Думая что это можно разрулить через четвертый параметр CreatePivotTable, где указывается версия сводной таблицы, взяв, например, за основу Excel 2000, или через анализ версии Экселя в коде и ветвления.


------------------
Лучше переесть, чем недоспать.
Не спеши, а то успеешь.

Так как в Фоксе нельзя осуществлять передачу через именованные параметры, то так: CreatePivotTable("","СводнаяТаблица1",.T.,xlPivotTableVersion10)

Creates a PivotTable report based on a PivotCache object. Returns a PivotTable object.
expression.CreatePivotTable(TableDestination, TableName, ReadData, DefaultVersion)

expression An expression that returns a PivotCache object.

TableDestination Required Variant. The cell in the upper-left corner of the PivotTable report’s destination range (the range on the worksheet where the resulting PivotTable report will be placed). The destination range must be on a worksheet in the workbook that contains the PivotCache object specified by expression.

TableName Optional Variant. The name of the new PivotTable report.

ReadData Optional Variant. True to create a PivotTable cache that contains all of the records from the external database; this cache can be very large. False to enable setting some of the fields as server-based page fields before the data is actually read.

DefaultVersion Optional Variant. The default version of the PivotTable report.

Remarks
For an alternative way to create a PivotTable report based on a PivotTable cache, see the Add method of the PivotTable object.

RELEASE ole1, ole2

Открытие при создании нового документа

* способ, когда не исчезает после выхода:
ole1 = CreateObject("Excel.Application")
ole1.WorkBooks.add
ole2=ole1.Application
* способ, когда таб Excel исчезает после выхода:
ole1 = GetObject("","Excel.Sheet")
ole2 = ole1.Application

Открытие для существующего документа (Excel исчезает после выхода)
fil_excel = cfg_tgdef + "RECHNUNG_"+nominv_r+".xls"
filnam_r = "RECHNUNG_"+nominv_r+".xls"
ole1 = GetObject(fil_excel)
ole1.Application.Windows(filnam_r).Activate
ole2 = ole1.Application
ole2.Visible = .T.

Выход
ole1.Application.Quit

  • В начале работы, сразу после создания обьекта
    ole2.WindowState = xlMinimized && минимизировать окно
    ole2.ScreenUpdating = .F. && не отображать изменения на экране
  • В конце работы, перед показом отчета
    ole2.ScreenUpdating = .T.
    ole2.WindowState = xlMaximized

Запоминание результата в файл
ole2.ActiveWorkBook.Save && в тот же файл
ole2.ActiveWorkBook.SaveAs(fil_excel) && в новый файл

Разное
ole2.DisplayAlerts =.F. && Убрать запрос "Сохранить файл?" (перед выходом)
ole2.DisplayFormulaBar = .F. && Убрать панель формул
ole2.Version && Возвращает версию Excel

Удаление макроса
workbooks("book1").VBProject.VBComponents(5).CodeModule.DeleteLines 2,1
Удаляет строку номер 2 из модуля номер 5

  • 1-й способ:
    Ole1 = CREATEOBJECT('Excel.Application') && создание осн. обьекта
    Ole1f = CREATEOBJECT('Excel.Application') && cоздание фиктивного обьекта
    Тогда при открытии XLS-документа будет задействована вторая ссылка (т.е. ole2f), а сам работаешь с первой, ole1.Visible останется равной FALSE. В нужном месте (после того как полностью сформировал свой отчет) можно вставить release ole2, который на работе XLS-документов, если таковые открыты к тому времени, не отразится.
  • 2-й способ
    local ole1 as Excel.Application
    ole1 = CreateObject("Excel.Application")
    ole1.IgnoreRemoteRequests = .t.
    После этого у xlApp отключается механизм DDE, при помощи которого Винда и открывает в нем другую книгу. Теперь при открытии xls запустится новая копия экселя. После формирования отчета
    xlApp.IgnoreRemoteRequests = .f. && А можно этого и не делать

Работа с ячейками

Размеры
.RowHeight = 2 && высота строки (2 - узкая, прокладка, 15-20 - нормальная)
.ColumnWidth = 5 && ширина столбца (примерно в символах Courier, 9)

Ссылка на ячейку или диапазон
Ole2.Cells(2,4) && ячейка D2
Ole2.Range(ole2.Cells(2,4), ole2.Cells(3,6)) && D2:F3
То есть в Cells() указывается сначала строка (ряд), а затем столбец, причем для столбца вместо букв (указывает номер (A - 1, B - 2, …)

Выделение ячеек
Ole2.Cells(2,4).Select
Ole2.Range(ole2.Cells(2,4), ole2.Cells(3,6)).Select

Уменьшение писанины с одной ячейкой
WITH Ole2.Cells(2,4)
.Select
….
ENDWITH

Уменьшение писанины с диапазоном
Ole2.Range(ole2.Cells(2,4), ole2.Cells(3,6)).Select
WITH Selection
…..
ENDWITH

Разное при работе с ячейками или диапазоном
.MergeCells = .T. && обьединение ячееке
.Value = zag3_r && присвоение значения (можно без .Value: ole2.Cells(2,3)="aa")
.WrapText = .T. && текст переносится автоматом по словам внутри яч
.NumberFormat = "@" && не цифровой формат (число в виде текста)
.NumberFormat = "0.00" && числовой, всегда 2 дес знака после запятой
.interior.colorindex = число && цвет фона другой (38,42,…)

Вставка рисунка в ячейку
Range("B6").Select
ActiveSheet.Pictures.Insert("C:\VFP\EL\vaz_logo.jpg").Select

Как добавить разрыв страницы в Excel'е из VFP
Ole2.Range("A15").Select
Ole1.ActiveWindow.SelectedSheets.HPageBreaks.Add(ole2.ActiveCell)
где ole2 и ole1 - объекты страницы и Excel.
Можно в одну строчку и без перемещения курсора
Ole2.HPageBreaks.Add(ole2.Range("A15"))


Как узнать координаты ячейки
Selection.Address, Selection.Row, Selection.Column,
Selection.Rows.Count, Selection.Column1.Count

  • 1-й способ (не сработал):
    ole2.ActiveCell.Formula = '=SUM(D7:D20)'
  • 2-й способ:
    .FormulaR1C1 = "=<формула>"
    Пример
    ole2.Cells(5,6).Select
    r = "=RC[+3]"
    FOR i=2 TO tlmmk_r
    r = r + "+RC["+ALLT(STR(i*3,6))+"]"
    ENDFOR
    ole2.ActiveCell.FormulaR1C1 = r && записать формулу
    Пример
    FOR j= 1 to 5 && столбцы
    FOR i = 1 TO 3 && строки
    Ole2.cells[i,j].value = I * 10 + j
    ENDFOR
    WITH ole2.cells[4,j]
    .Select
    .FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    ENDWITH
    ENDFOR

Процедура для облегчения работы с линиями
PROCEDURE cellborders && линии ячейки
PARAMETERS p1_cb, p2_cb, p3_cb, p4_cb
&& 1 - i нач, 2 - i кон, 3 - style, 4 - weight
FOR i_cb = p1_cb TO p2_cb
WITH .Borders(i_cb)
.LineStyle = p3_cb
IF p3_cb <> xlNone
.Weight = p4_cb
.ColorIndex = xlAutomatic
ENDIF
ENDWITH
ENDFOR
Вызов процедуры (пример):
DO cellborders WITH 7,12,xlnone,xlthin

В данной статье рассматривется вопрос о том каким образом используя COM-объект записать данные из таблицы FoxPro в книгу Excel. Данный пример тестировался в FoxPro версии 9 и дать гарантию, что он будет работать в младших версиях программы дать не могу.

Конечно последняя версия FoxPro вышла в октябре 2007 года, а поддержка завершилась в январе 2015 года статья может быть полезна для тех кто использует FoxPro в работе.

И так приступим.

Прежде всего подключимся к таблице из которой надо переписать данные в Excel.

Далее локальную переменную и привязываем к ней MS Excel и создаем внём новый документ

в полученном документе создаем новую книгу

в полученной книге создаем новый лист и определяем для некоторых колонок ширину в символах, т.е. буквах

далее создаем шапку документа, т.е. в определенные ячейки записываем текст по примеру того, что приведен ниже

m.loSheet.Cells(1,1).Value = «Каное-то название»

m.loSheet.Cells(3,5).Value = "по учету расчетов с депонентами на года"

m.loSheet.Cells(4,13).Value = "Дата увольнения"

далее осуществляем считывание из таблицы данные и записываем в ячейки начиная с пятой строки указывая из каких столбцов таблицы считывать и в какие ячейки записывать

далее нам необходимо осуществить сортировку. В данном примере показана сортировка по двум столбцам - «Фамилия» и «Цеху». Для этого создаем три переменные

В первую переменную запишем координаты того участка таблицы EXCEL в котором не обходимо произвести сортировку. В данном случае задаются координаты левой верхней ячейки и правой нижней ячейки в выделенной области будет производиться сортировка

далее указываем столбцы по которым необходимо осуществлять сортировку

добавляем в метод сортировки как столбцы, по которым будет производиться сортировка, так и область в которой будет производиться сортировка

далее определяем ориентацию сортировки и метод сортировки (т.е. фактически в алфавитном порядке)

Импортирует данные из файла внешнего формата, создавая новую таблицу Visual FoxPro.

  • FW2 - Задайте FW2, чтобы импортировать файлы FW2, созданные в Framework II.
  • MOD - Задайте MOD, чтобы импортировать файлы MOD, созданные в Microsoft Multiplan версии 4.01.
  • PDOX - Задайте PDOX, чтобы импортировать файлы Paradox. Файлы баз данных, созданные в системе Paradox версий 3.5 и 4.0 фирмы Borland, можно импортировать, задав опцию PDOX.
  • RPD - Задайте RPD, чтобы импортировать файлы RPD, созданные в RapidFile.
  • WK1 | WK3 | WKS - Задайте WK1, чтобы импортировать данные из электронной таблицы Lotus 1-2-3. Столбцы электронной таблицы становятся полями таблицы Visual FoxPro, а строки электронной таблицы становятся записями. Электронным таблицам, созданным в Lotus 1-2-3 редакции 2.x, назначается расширение имени WK1; электронным таблицам Lotus 1-2-3 редакции 3.x назначается расширение имени WK3; электронным таблицам, созданным в Lotus 1-2-3 редакции 1-A, назначается расширение имени .WKS.
  • WR1 | WRK - Задайте WR1, чтобы импортировать данные из электронной таблицы Lotus Symphony. Столбцы электронной таблицы становятся полями таблицы Visual FoxPro, а строки электронной таблицы становятся записями. Электронным таблицам, созданным в Symphony версии 1.10, назначается расширение имени WR1, а электронным таблицам, созданным в Symphony версии 1.01, назначается расширение имени .WRK.
  • XLS - Задайте XLS, чтобы импортировать данные из электронных таблиц Microsoft Excel версий 2.0, 3.0 и 4.0. Столбцы электронной таблицы становятся полями таблицы Visual FoxPro, а строки электронной таблицы становятся записями. Файлы электронных таблиц, созданные в Microsoft Excel, имеют расширение имени .XLS.
  • XL5 [SHEET cSheetName] - Задайте XL5, чтобы импортировать данные из Microsoft Excel версии 5.0. Столбцы электронной таблицы становятся полями таблицы Visual FoxPro, а строки электронной таблицы становятся записями. Файлы электронных таблиц, созданные в Microsoft Excel, имеют расширение имени .XLS.Если предложение SHEET опущено, импортируются данные из страницы Sheet1. Чтобы импортировать данные из другой страницы, укажите ключевое слово SHEET и задайте имя нужной страницы в качестве аргумента cSheetName.

Комментарии:
В большинстве программных продуктов данные хранятся в файлах таких форматов, которые нельзя открыть непосредственно в Visual FoxPro. Команда IMPORT создает новую таблицу Visual FoxPro из данных, хранящихся в файлах, которые Visual FoxPro не в состоянии сразу прочитать.
Новая таблица создается с тем же именем, что и у файла, из которого импортируются данные. Этой новой таблице присваивается расширение имени .DBF.

Читайте также:

      
  • Peak controller fl studio как пользоваться
    •   
  • Carcam программа для андроид
    •   
  • Как отключить adblock в microsoft edge
    •   
  • Тормозит gif анимация в браузерах
    •   
  • Vba excel посчитать количество файлов в папке
  • Контакты
  • Политика конфиденциальности