2003/XP/2000 - Экспорт из xls в txt по заданному формату

[Tonny_Bennet]

Здравствуйте.



Есть некоторый xls файл, в котором несколько однотипных лисов. На листах в одинаковом виде содержится контактная информация о сотрудниках: Фамилия Имя, почта, мобильный телефон и т.д. Мне нужно из этого файла сделать экспорт и на выходе получить файл txt некоторого формата. К примеру



Иван Иванов: +79281234567

Пётр Петров: +79881234567



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

[Iska]

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

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

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

[okshef]

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

И всё.

[Tonny_Bennet]

Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

Код:

Код: Выделить всё

--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

Цитата Tonny_Bennet:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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



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



3.
Цитата Tonny_Bennet:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

Поясните.



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]

Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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




Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

ANSI





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

[Iska]

Цитата Tonny_Bennet:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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

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

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



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


Цитата Tonny_Bennet:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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

[Iska]

Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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






Цитата Iska:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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

[Tonny_Bennet]

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




Цитата Tonny_Bennet:



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

2003/XP/2000 - Экспорт из xls в txt по заданному формату

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

[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