[Iska]

1. Приложите образец рабочей книги.
2. Экспорт нужен со всех листов в один файл?
3. Приведите точный вид выходного файла, который требуется получить в итоге.

[okshef]

Файл -> Сохранить как... -> В списке форматов выбирайте на свой вкус любой текстовый формат -> OK
И всё.

[Tonny_Bennet]

1. Приложите образец рабочей книги. »

Прикрепил. В книге только один лист для примера. Остальные идентичны этому листу.

. Экспорт нужен со всех листов в один файл? »

Да, желательно со сквозной нумерацией.

3. Приведите точный вид выходного файла, который требуется получить в итоге. »

Код:

--Phone Book--     :
Item1 Name         :Иванов Альберт (моб1)
Item1 Number       :79031234567
Item1 Ring         :0
Item2 Name         :Иванов Альберт (моб2)
Item2 Number       :79051244747
Item2 Ring         :0
Item3 Name         :Иванов Альберт (внут)
Item3 Number       :103
Item3 Ring         :0

Смысл в том, чтобы по каждому телефонному номеру сотрудника из файла (будь то один из мобильных или внутренний телефон) нужно создать запись в телефонной книге.

P.S. В итоге получается часть конфигурационного файла для IP телефона D-Link DPH-150s. Хочу загрузить в него телефоны всей компании.
Подумываю, что может стоит перевести всё в csv и просто написать скрипт на bash или powershell, котороый будет парсить всё это и создавать файл таким как мне вздумается. С VBA дело не имел и думаю не просто будет разобраться.

[Iska]

Код:

Option Explicit

Sub OutToFile()
    Const adOpenStatic = 3
    Const adLockOptimistic = 3
    Const adCmdText = 1
    
    
    Dim objWorksheet As Worksheet
    
    Dim objConnection As Object
    Dim objRecordset As Object
    
    Dim objFSO As Object
    Dim objTS As Object
    
    Dim intRow As Integer
    
    
    Set objConnection = CreateObject("ADODB.Connection")
    Set objRecordset = CreateObject("ADODB.Recordset")
    
    objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=""" & ThisWorkbook.FullName & """;Extended Properties=""Excel 8.0;HDR=Yes;"";"
    
    intRow = 0
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    Set objTS = objFSO.CreateTextFile(objFSO.BuildPath(ThisWorkbook.Path, objFSO.GetBaseName(ThisWorkbook.Name) & ".txt"), True)
    
    For Each objWorksheet In ThisWorkbook.Worksheets
        With objRecordset
            .Open "SELECT * FROM [" & objWorksheet.Name & "$] WHERE [Фамилия] IS NOT NULL;", objConnection, adOpenStatic, adLockOptimistic, adCmdText
            
            objTS.WriteLine "--Phone Book--     :"
            
            Do Until .EOF
                With .Fields
                    If Not IsNull(.Item("мобильнный 1")) Then
                        intRow = intRow + 1
                        
                        objTS.WriteLine "Item" & CStr(intRow) & " Name         :" & .Item("Фамилия").Value & " " & .Item("Имя").Value & " (моб1)"
                        objTS.WriteLine "Item" & CStr(intRow) & " Number       :" & .Item("мобильнный 1").Value
                        objTS.WriteLine "Item" & CStr(intRow) & " Ring         :0"
                    End If
                
                    If Not IsNull(.Item("мобильнный 2")) Then
                        intRow = intRow + 1
                        
                        objTS.WriteLine "Item" & CStr(intRow) & " Name         :" & .Item("Фамилия").Value & " " & .Item("Имя").Value & " (моб2)"
                        objTS.WriteLine "Item" & CStr(intRow) & " Number       :" & .Item("мобильнный 2").Value
                        objTS.WriteLine "Item" & CStr(intRow) & " Ring         :0"
                    End If
                
                    If Not IsNull(.Item("доб# №")) Then
                        intRow = intRow + 1
                        
                        objTS.WriteLine "Item" & CStr(intRow) & " Name         :" & .Item("Фамилия").Value & " " & .Item("Имя").Value & " (внут)"
                        objTS.WriteLine "Item" & CStr(intRow) & " Number       :" & .Item("доб# №").Value
                        objTS.WriteLine "Item" & CStr(intRow) & " Ring         :0"
                    End If
                End With
                
                .MoveNext
            Loop
            
            .Close
        End With
    Next objWorksheet
    
    objTS.Close
    
    Set objTS = Nothing
    Set objFSO = Nothing
    
    Set objRecordset = Nothing
    
    objConnection.Close
    Set objConnection = Nothing
End Sub

Есть некоторый xls файл, в котором несколько однотипных лисов. На листах в одинаковом виде содержится »

1. Не вижу однотипных листов. И что-то сомневаюсь, что они будут однотипные.

2. Информация не одинаковая.

3.

желательно со сквозной нумерацией. »

Поясните.

4.

Код: Item1 Name :Иванов Альберт (моб1) Item1 Number :79031234567 Item1 Ring :0 Item2 Name :Иванов Альберт (моб2) Item2 Number :79051244747 Item2 Ring :0 Item3 Name :Иванов Альберт (внут) Item3 Number :103 Item3 Ring :0

Откуда берётся «103», в принципе, — понятно, откуда взялось «79031234567» — тоже более-менее понятно. Откуда взялось «79051244747»? В рабочей книге номер «79051244767».

5. Почему строка с «Светлана Криворучка» «стоит» отдельно?

6. Как поступать с отсутствующими номерами, исключать из вывода?

7. «Рабочий телефон» — не выводится вовсе?

8. В реальности тоже будет куча пустых рабочих листов, або нет? Из предложенного примера я убрал пустые листы.

9. Какова должна быть кодировка выходного текстового файла?

[Tonny_Bennet]

1. Не вижу однотипных листов. И что-то сомневаюсь, что они будут однотипные. »

Шапки у листов одинаковые и заполнение идентичное.

2. Информация не одинаковая. »

Да, информация разная т.к. нет сотрудников с повторяющимися именами и фамилиями, а также с повторяющимися номерами телефонов.

Откуда берётся «103», в принципе, — понятно, откуда взялось «79031234567» — тоже более-менее понятно. Откуда взялось «79051244747»? В рабочей книге номер «79051244767». »

Я конечно мог накосячить в примерах, но по сути нужно вывести внутренний телефон (если он есть), первый мобильный (если он есть), второй мобильнный (если он есть).

5. Почему строка с «Светлана Криворучка» «стоит» отдельно? »

В файле в том же столбце, что и фамилия встречается название отдела. У Светланы я случайно удалил название отдела. Она там одна работает.

6. Как поступать с отсутствующими номерами, исключать из вывода? »

Да, если номера нет пустую запись создавать не нужно.

7. «Рабочий телефон» — не выводится вовсе? »

Да, рабочий телефон не нужен.

8. В реальности тоже будет куча пустых рабочих листов, або нет? Из предложенного примера я убрал пустые листы. »

Нет, в рабочей книге все листы заполнены. Пустые могу удалить.

9. Какова должна быть кодировка выходного текстового файла? »

ANSI


P.S. Спасибо за макрос. Проблема в том, что я даже не знаю куда его вставить ... дайте пару ссылок на тематические ресурсы пожалуйста.

[Iska]

Проблема в том, что я даже не знаю куда его вставить ... »

Откройте рабочую книгу со списком сотрудников и номерами. Нажмите Alt-F11, в окне «Project - VBAProject» найдите проект этой рабочей книги, раскройте дерево до объекта «ЭтаКнига», вызовите для него контекстное меню, выберите из контекстного меню «View Code»:

читать дальше »

В открывшееся окно редактора VBA кода модуля «ЭтаКнига» вставьте скопированный отсюда код макроса. Затем установите курсор ввода куда-нибудь внутрь вставленного текста процедуры «OutToFile()» и нажмите «F5». Если всё пройдёт успешно — рядом с файлом рабочей книги будет создан одноимённый текстовый файл.

P.S. Вы не ответили:

желательно со сквозной нумерацией. »

Поясните это пожелание.

[Tonny_Bennet]

Если всё пройдёт успешно »

Создаётся файл только с первым телефоном. Потом вылезает ошибка.

Поясните это пожелание. »

Т.е. если обработав первую страницу получилось 46 записей, то телефоны второй страницы должны добавляться в этот же файл начиная с Item 47 и т.д.

[Iska]

Tonny_Bennet, упакуйте документ с реальными данными в архив под паролем, выложите на RGhost, ссылку и пароль в личку или на почту (если последнее — отдельными письмами).

Т.е. если обработав первую страницу получилось 46 записей, то телефоны второй страницы должны добавляться в этот же файл начиная с Item 47 и т.д. »

Ясно. Так и было реализовано.

[Tonny_Bennet]

Iska, огромное спасибо вам за труды. Но я что-то подумал и решил набросать скрипт на bash-е. Который будет парсить *.csv.

IP телефон умеет обновлять конфигурацию через ftp или tftp сервер, а они у меня в любом случае под Ubunt-ой стоят. Вот я и сделал всё на одной платформе.

Ниже скрипт, который у меня получился. Пусть не ругают меня скриптописатели, работет и ладно

Код:

#!/bin/bash

#Скрипт парсит файлы формата *.csv (дирректория сфайлами $1) и создаёт телефонную книгу для d-link dph-150s (параметр $2)

#номер, с которого начинаются записи в телефонной книге
#счётчик записей выходного файла
n=1

function translit() {
text=$1
text=${text//а/a}
text=${text//А/A}
text=${text//б/b}
text=${text//Б/B}
text=${text//в/v}
text=${text//В/V}
text=${text//г/g}
text=${text//Г/G}
text=${text//д/d}
text=${text//Д/D}
text=${text//е/e}
text=${text//Е/E}
text=${text//ё/e}
text=${text//Ё/E}
text=${text//ж/zj}
text=${text//Ж/Zj}
text=${text//з/z}
text=${text//З/Z}
text=${text//и/i}
text=${text//И/I}
text=${text//й/y}
text=${text//к/k}
text=${text//К/K}
text=${text//л/l}
text=${text//Л/L}
text=${text//м/m}
text=${text//М/M}
text=${text//н/n}
text=${text//Н/N}
text=${text//о/o}
text=${text//О/O}
text=${text//п/p}
text=${text//П/P}
text=${text//р/r}
text=${text//Р/R}
text=${text//с/s}
text=${text//С/S}
text=${text//т/t}
text=${text//Т/T}
text=${text//у/u}
text=${text//У/U}
text=${text//ф/f}
text=${text//Ф/F}
text=${text//х/h}
text=${text//Х/H}
text=${text//ц/ts}
text=${text//Ц/Ts}
text=${text//ч/ch}
text=${text//Ч/Ch}
text=${text//ш/sh}
text=${text//Ш/Sh}
text=${text//щ/sh}
text=${text//Щ/Sh}
text=${text//ь/}
text=${text//ы/y}
text=${text//ъ/}
text=${text//э/e}
text=${text//Э/E}
text=${text//ю/yu}
text=${text//Ю/Yu}
text=${text//я/ya}
text=${text//Я/Ya}

echo $text
}

#список файлов с расширением *.csv
file_list_in=`ls  $1 | grep ".csv$"`

#Виндовый символ переноса строки.
CR=$(printf '\015')

#просматриваем все файлы по очереди
for pb_file_in in $file_list_in;
    do
    echo "Обрабатывать файл $pb_file_in? (1/0)"
    read is_file

    #если файл обрабатывается
    if [[ $is_file = 1 ]]
        then

        #перекодируем исходный файл в UTF-8
        iconv -t UTF-8 -o $1$pb_file_in $1$pb_file_in

        echo "Выгружать внутренние номера? (1/0)"
        read is_internal
        echo "Выгружать номера мобильный 1? (1/0)"
        read is_mob1
        echo "Выгружать номера мобильный 2? (1/0)"
        read is_mob2
        #читаем первые 150 строк из исходного файла преобразуем прочтённое в массив
        head -n 150 $1$pb_file_in | while IFS=';' read -a item line
            do
            #Если выбрано выгружать внутренние номера
            if [[ $is_internal = 1 ]]
                then
                #Если добавочный номер попадает под регулярное выражение (три цифры) добавляем номер телефона в книгу
                if [[ "${item[5]}" =~ [0-9]{3} ]]
                    then
                    echo Item$n Name         :$(translit ${item[2]}) $(translit ${item[1]})'(int)'$CR  >> $2
                    echo Item$n Number       :$(translit ${item[5]})$CR                                >> $2
                    echo Item$n Ring         :0$CR                                                     >> $2
                    n=$[$n+1]
                fi
            fi

            #Если выбрано выгружать номера номера мобильный 1
            if [[ $is_mob1 = 1 ]]
                then
                #Если первый мобильный телефон попадает под регулярное выражение ( 7(XXX)XXX-XX-XX) добавляем номер телефона в книгу
                if [[ "${item[6]}" =~ 7[(]{0,1}[0-9]{3}[)]{0,1}[0-9]{3}[-]{0,1}[0-9]{2}[-]{0,1}[0-9]{2} ]]
                    then
                    # Удаляем лишние символы в номере телефона: ()-
                    item[6]=${item[6]//(/}
                    item[6]=${item[6]//)/}
                    item[6]=${item[6]//-/}

                    echo Item$n Name         :$(translit ${item[2]}) $(translit ${item[1]})'(mob1)'$CR >> $2
                    echo Item$n Number       :$(translit ${item[6]})$CR                                >> $2
                    echo Item$n Ring         :0$CR                                                     >> $2
                    n=$[$n+1]
                fi
            fi

            #Если выбрано выгружать номера номера мобильный 2
            if [[ $is_mob2 = 1 ]]
                then
                #Если второй мобильный телефон попадает под регулярное выражение ( 7(XXX)XXX-XX-XX) добавляем номер телефона в книгу
                if [[ "${item[7]}" =~ 7[(]{0,1}[0-9]{3}[)]{0,1}[0-9]{3}[-]{0,1}[0-9]{2}[-]{0,1}[0-9]{2} ]]
                    then
                    # Удаляем лишние символы в номере телефона
                    item[7]=${item[7]//(/}
                    item[7]=${item[7]//)/}
                    item[7]=${item[7]//-/}

                    echo Item$n Name         :$(translit ${item[2]}) $(translit ${item[1]})'(mob2)'$CR  >> $2
                    echo Item$n Number       :$(translit ${item[7]})$CR                                 >> $2
                    echo Item$n Ring         :0$CR                                                      >> $2
                    n=$[$n+1]
                fi
            fi

        done
    fi
done

exit 0