В 10 статье я отметил, что процесс переноса данных из dbf в EXCEL файлы можно сделать почти также как из dbf в EXCEL. Но вот ведь в чем хорошо основано понятие, что при создании листа в EXCEL обязательно надо создать поле – заголовок. Это почти также как и создать dbf файл. Без полей Вы не создадите dbf файл. Так же не создадите EXCEL файл. Необходимо прописать хоть одно поле на создаваемом листе. И это во многом так, если у Вас на компьютере нет MS office (EXCEL). Если же он есть, то Вы можете легко обращаться напрямую c помощью CreateObject("Excel.Application"). Но бывало, Я удалял office и также мог обращаться напрямую. Но все равно это ничего не давало, потому что, для визуализации Visible=true необходима установка EXCEL приложения. Поэтому делать расчеты и другие действия Вы может только относительно полей самой книги на листе. Это Вы можете позволить себе. Здесь это так, а если есть у кого интересные подходы к этому, то ниже под статьей не поленитесь, и напишите про это.
В статье 10 Я указал, что при работе скрипта рабочий лист в EXCEL книги создается автоматически и не каждому пользователю есть желание это прояснять. Создается и создается. Данные переносятся и переносятся, а как называется лист (имя листа) и зачем это. Да, сейчас очень много людей, которые работают в EXCEL, и есть такие, которые дотошно его изучают и можно сказать фанатеют от него. Вот им хочется знать, как создать лист с именем, которое они могут сами придумать, да и наверно и знают они.
Вот такой фрагмент переноса данных из dbf в EXCEL
NameFil=SDefaultDir & "\_222.xls"
SQL="create table " & NameFil & ".[xls] ("
for i=0 to objKlFields-1
st=RS.Fields(i).Name
if i=objKlFields-1 then
SQL=SQL & "st" & i & " TEXT)"
else
SQL=SQL & "st" & i & " TEXT,"
end if
next
Создается EXCEL файл с листом xls
Если файл существует, то можно добавить еще лист, только с другим названием.
SQL="create table " & NameFil & ".[Sheet1] ("
А можно и так
cnnMain.Execute "CREATE TABLE Sheet1 (stt1 text)"
cnnMain.Execute "CREATE TABLE Sheet2 (stt2 text)"
cnnMain.Execute "CREATE TABLE Sheet3 (stt3 text)"
Создается лист Sheet1, Sheet2, Sheet3 и поле Stt1, Stt2, Stt3
А потом открыть эти листы и дописать
cnnMainR.open "SELECT * FROM [Sheet1$]",cnnMain,2,2
cnnMainR.Fields("Stt1").Value = "Привет1"
cnnMainR.Update
cnnMainR.Close
А можно и не делать SELECT а прям сразу перейти и добавить строку
cnnMainR.open "[Sheet3$]",cnnMain,1,3
cnnMainR.AddNew
cnnMainR.Fields("Stt3").Value = "Привет3"
cnnMainR.Update
cnnMainR.Close
Можете переходить между листами.
Но это когда Вы создаете новый рабочий лист. Можно создать новый рабочий лист и перенести в него данные того же dbf повторно. А вот добавить новые поля, вот здесь необходимо сделать так.
cnnMain.Execute("ALTER TABLE _222.xls.[xls$] ADD COLUMN str4 TEXT")
cnnMain.Execute("ALTER TABLE _222.xls.[xls$] ADD COLUMN str5 TEXT")
cnnMain.Execute("ALTER TABLE _222.xls.[xls$] ADD COLUMN str10 TEXT")
Причем это надо сделать до процесса переноса данных из dbf файла, а тогда когда копируются и создаются поля заголовки согласно, полей dbf файла. Если Вы переносите 3 колонки из dbf, то Вы можете добавить в create еще колонки, но здесь надо это грамотно это сделать. Например, если хотите добавить еще колонку st4, то это выглядит так.
SQL="create table " & NameFil & ".[xls] ("
for i=0 to objKlFields-1
if i=objKlFields-1 then
SQL=SQL & "st4" & " TEXT)"
Здесь надо убедить dbf файл, что у него 4 колонки. objKlFields= objKlFields+1
Доказать этому dbf файлу что перенос будет осуществляться из 3 колонок, а в четвертой колонке будут произведены и записаны расчеты
if j=objKlFields-1 then
st=2*i
stN=stN & "st4)"
vvv=vvv & st & "'"
else
st=RS.Fields(j)
stName="st"&j
stN=stN & stName & ","
vvv=vvv & st & "'" & "," & "'"
end if
Но лучше это сделать, как я указал с помощью ALTER. Все в Вашем понимании и нудном старании. Здесь хоть видно, что Вы добавляете, какие колонки (столбцы)
При записи также можете указать лист
SQL="insert into" & NameFil & ".[xls$]" & " " & stN & " values(" & vvv& ")"
Если делаете это не автоматически и если в книге 1 рабочий лист под указанное имя.
Можно позиционировать запись данных с помощью
cnnMainR.AbsolutePosition=10
cnnMainR.Fields(3).value="222"
cnnMainR.Update
cnnMainR.Requery
Можно определить количество строк в EXCEL файле
objKlRecs=RSQQ.RecordCount
и добавить результирующее решение
А можно добавить результирующее решение и так
objEndRecs=cnnMainR.RecordCount
cnnMainR.AbsolutePosition=objEndRecs-1 ' Предыдущая строка
cnnMainR.AbsolutePosition=objEndRecs
cnnMainR.AddNew ' Добавить новую строку
cnnMainR.Fields(3).value=SumSt4 'Можно и так
cnnMainR.Update
cnnMainR.Requery
где в общем SumSt4 посчитать.
А можно просто поставить
cnnMainR.AbsolutePosition=30 ' и перейти на конец записей и добавить результирующее решение. 30 взято условно. Если на рабочем листе 40 записей, то можно взять 41 и т.д. и т.п.
Все в Ваших руках и в Вашем понимании этих решений и Ваших познаний. Экспериментирование это стезя благородства в нужное время и концентрации этого понятия. Что быстрее это надо проверять на больших файлах.
И вот здесь если Вы добавляете колонки и сделайте расчеты, то все это можно перенести обратно в dbf файлы с новыми колонками.
Добавляем на каждый лист данные
cnnMain.close
Set cnnMainR = CreateObject("ADODB.Recordset")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
cnnMainR.open "SELECT * FROM [Sheet1$]",cnnMain,2,2
cnnMainR.AddNew
cnnMainR.Fields("Stt1").Value = "Привет1"
cnnMainR.Update
cnnMainR.Close
cnnMainR.open "SELECT * FROM [Sheet2$]",cnnMain,2,2
cnnMainR.Fields("Stt2").Value = "Привет2"
cnnMainR.Update
cnnMainR.Close
' cnnMainR.open "[Sheet3$]",cnnMain,2,2 " Можно и так
cnnMainR.open "[Sheet3$]",cnnMain,1,3
cnnMainR.AddNew
cnnMainR.Fields("Stt3").Value = "Привет3"
cnnMainR.Update
cnnMainR.Close
cnnMainR.open "SELECT * FROM [Sheet4$]",cnnMain,2,2
cnnMainR.AddNew
cnnMainR.Fields("Stt4").Value = "Привет4"
cnnMainR.Update
cnnMainR.Close
Начнем с 1 фрагмента. Здесь на листе xls с позиции 10 (строка) данные записанные в xSt4 переносим на рабочий лист Sheet3 в поле Stt3, а потом добавим эту переменную в другое поле Stt4 на рабочий лист Sheet4.
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 1 фрагмент
cnnMainR.open"[xls$]",cnnMain,2,2
cnnMainR.AbsolutePosition=10
xSt4=cnnMainR.Fields(3).value
cnnMain.close
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
cnnMainR.open "[Sheet3$]",cnnMain,2,2
cnnMainR.Fields("Stt3").Value = xSt4
cnnMainR.Update
cnnMainR.AddNew
cnnMainR.Fields("Str4").Value = xSt4
cnnMainR.Update
cnnMainR.Close
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
'WScript.Echo("Фрагмент 1. Файл создан!")
'WScript.Quit ' Если хотите просмотреть предыдущие решения
А вот здесь интересное. С листа xls переносим данные на лист Sheet3 с помощью выбора Select и записываем на странице Sheet3
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 2 фрагмент
cnnMain.close
Set cnnMainR = CreateObject("ADODB.Recordset")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
'strSQLn="SELECT * FROM [xls$]" ' Если все
'strSQLn="SELECT str4 FROM [xls$]" ' Если 1 колонку
strSQLn="SELECT * FROM [xls$D1:D10]" ' Если 1 колонку
Set RS = cnnMain.Execute(strSQLn)
KlFieldsN = RS.Fields.count
KlRecsN=RS.RecordCount
cnnMainR.open "[Sheet3$]",cnnMain,2,2
for i=1 to KlRecsN
cnnMainR.AddNew
stDn=RS.Fields(0)
cnnMainR.Fields("Str4").Value = stDn
cnnMainR.Update
RS.MoveNext
next
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Переносим данные 2 колонок с листа xls на лист Sheet3
Причем делаем выбор Select согласно ячейкам. Таким образом, можно сделать фильтр данных по имени поля и по выбранным ячейкам.
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& 3 фрагмент
cnnMain.close
Set cnnMainR = CreateObject("ADODB.Recordset")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
strSQLn="SELECT * FROM [xls$D1:E10]" ' Если 2 колонки
Set RS = cnnMain.Execute(strSQLn)
KlFieldsN = RS.Fields.count
KlRecsN=RS.RecordCount
cnnMainR.open "[Sheet3$]",cnnMain,2,2
cnnMainR.Requery
cnnMainR.AbsolutePosition=1
for i=1 to KlRecsN
cnnMainR.AddNew
stDn1=RS.Fields(0)
stDn2=RS.Fields(1)
cnnMainR.Fields("Stt3").Value = stDn1
cnnMainR.Fields("Str4").Value = stDn2
cnnMainR.Update
RS.MoveNext
next
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Здесь переносим данные с листа xls на лист Sheet1
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& фрагмент 4
cnnMain.close
Set cnnMainR = CreateObject("ADODB.Recordset")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
strSQLn="SELECT * FROM [xls$D1:D10]" ' Если 2 колонки
Set RS = cnnMain.Execute(strSQLn)
KlFieldsN = RS.Fields.count
KlRecsN=RS.RecordCount
cnnMainR.open "[Sheet1$]",cnnMain,2,2
cnnMainR.Requery
cnnMainR.AbsolutePosition=1
cnnMainR.MoveFirst
for i=1 to KlRecsN
cnnMainR.AddNew
stDn1=RS.Fields(0)
cnnMainR.Fields("Stt1").Value = stDn1
cnnMainR.Update
RS.MoveNext
next
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Другой способ переноса с листа xls в Sheet1
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& фрагмент 5
cnnMain.close
Set cnnMainR = CreateObject("ADODB.Recordset")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
strSQLn="SELECT * FROM [xls$D1:D10]" ' Если 2 колонки
Set RS = cnnMain.Execute(strSQLn)
KlFieldsN = RS.Fields.count
KlRecsN=RS.RecordCount
cnnMainR.open "[Sheet1$]",cnnMain,2,2
cnnMainR.Requery
cnnMainR.AbsolutePosition=1
for i=1 to KlRecsN
stDn1=RS.Fields(0)
SQL="insert into " & NameFil & ".[Sheet1$]" & " (stt1) values(" & "'" & stDn1 & "'" & ")"
cnnMain.Execute(SQL)
RS.MoveNext
next
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Перенос данных с листа xls на 4 рабочий лист Sheet4
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& фрагмент 6
cnnMain.close
Set cnnMainR = CreateObject("ADODB.Recordset")
cnnMain.Open "DRIVER={Microsoft Excel Driver (*.xls)};DBQ=" & NameFil & ";Deleted=0;ReadOnly=0;Exclusive=0;"
cnnMain.CursorLocation = 3
strSQLn="SELECT * FROM [xls$D1:D10]" ' Если 2 колонки
Set RS = cnnMain.Execute(strSQLn)
KlFieldsN = RS.Fields.count
KlRecsN=RS.RecordCount
cnnMainR.open "[Sheet4$]",cnnMain,2,2
cnnMainR.Requery
cnnMainR.AbsolutePosition=1 ' Можно оставить, а можно убрать не влияет
cnnMainR.MoveFirst
for i=1 to KlRecsN
cnnMainR.AddNew
stDn1=RS.Fields(0)
cnnMainR.Fields("Stt4").Value = stDn1
cnnMainR.Update ' Иногда не нужно, Можно проверять
RS.MoveNext
next
'&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&
Можно описывать все фрагменты, но прилагаю архивный файл Файлы11.rar и там, все понятно.
Но расскажу еще об одном фрагменте. В 12 фрагменте мне пришлось разработать особый алгоритм решения сортировки на листе.
for i=1 to KlRecsN
stDn1=RSK.Fields(0)
WScript.Echo(stDn1)
KlRecsNew=i+1
cnnMainR.Fields(5).Value=stDn1
strSQlDelNlist="DROP TABLE [Sheet4$F" & KlRecsNew & ":F]"
'Можно и так
strSQlDelNlist="DROP TABLE [Sheet4$F" & KlRecsNew & ":F" & KlRecsNew & "]"
cnnMain.Execute(strSQlDelNlist)
cnnMainR.Fields(0).Value=stDn1
strSQlDelNlist="DROP TABLE [Sheet4$A" & KlRecsNew & ":A21]"
cnnMain.Execute(strSQlDelNlist)
cnnMainR.MoveNext
RSK.MoveNext
next
Все дело в том, что при перезаписи данных появляется вот такая проблема. Если появляется хоть бы 1 одинаковая запись по колонке, и она находится дальше чем 1 такая запись, то курсор перескакивает в последнюю запись и перезаписывает там данные, а если эти данные находятся в самом конце колонки или близко, то появляется ошибка. Причем другие данные не изменяются. Конечно, можно сделать фильтр отсортированных записей на другой рабочий лист и этим решить такую проблему именно в EXCEL, но как это сделать в dbf файле. Вот здесь приходится отсортировать данные и записать их заново на свои позиции. Конечно, можно создать 2 dbf файл и туда записать отсортированные данные, но это все-таки 2 файл. Можно придумать и другие решения, но в ADO это надо поискать. ADO хранит не мало секретов…
Заранее сохраните файлы _333.xls и _444.xls в любом другом каталоге для повторного применения. В файле AdoED11A.vbs Я также добавил понятие поиска, фильтра и update, а также работу с рабочими листами и сортировку. Удаление данных с листа в EXCEL предполагает просто очистку строк и ячеек. Удалить строки не предоставляется драйвером. Также не предоставляется удаление рабочего листа. Я не нашел такого решения.
Зато на рабочем листе можно записать данные согласно, выбранных ячеек. Все это мной проверено, и Я проделывал это и на других компьютерах. Все работало и получалось. AdoED11A.vbs загружает файл _111.dbf и на выходе получаем файл _222. xls. В файле AdoED11B.vbs реализована сортировка на одном рабочем листе. Здесь алгоритм создан мною и себя оправдывает. Также проверял. Работает этот файл с файлами _333.xls и _444.xls . Можно отсортировывать и большие файлы. Главное не прерывать процесс сортировки. Поэтому заранее сохраните эти файлы. Можно конечно повторно сортировать по выбранным полям. Заголовки полей должны быть на латинице. Могу добавить, что работа с EXCEL файлом полностью отожествляет собой работу с базами данных и похоже на работу как бы с dbf файлами. Но здесь есть свои понятия, которые удобны. Это и работа с ячейками по колонкам и строкам. Немного напоминает работу с EXCEL приложением из Office. Работа и по полям-заголовкам. Напоминает работу с dbf файлами. Удобно и так и так. Очень занимательно. Даже есть какое-то преимущество в этом. И книга может содержать большое количество рабочих листов. Можете представить их как dbf файлы и области работы. Отдельные файлы dbf (таблицы) также можно собирать как бы в один контейнер. В FOXPRO файл DBC может включать в себя имена файлов и элементов, связанных с ним. Есть, конечно, свои отличия, но понятия почти похожи. Файл mdb в ACCESS, который может включать братство форматов. В следующей статье Я еще возможно допишу про некоторую работу с EXCEL, но постараюсь описать перенос данных с EXCEL файлов в текстовые файлы.
Файлы сохранил в архив Файлы11.rar
Пробуйте.
Ссылка на файл https://disk.yandex.ru/d/ZzpXLpmZ_ATRwg
Подписывайтесь на мой канал и ставьте лайки.
