Экспорт из xls в txt по заданному формату - Компьютерный форум OSzone.net

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Microsoft Office (Word, Excel, Outlook и т.д.) (http://forum.oszone.net/forumdisplay.php?f=115)

- - Экспорт из xls в txt по заданному формату (http://forum.oszone.net/showthread.php?t=262780)

Экспорт из xls в txt по заданному формату

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

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

Иван Иванов: +79281234567
Пётр Петров: +79881234567

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

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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 дело не имел и думаю не просто будет разобраться.

Код:

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 файл, в котором несколько однотипных лисов. На листах в одинаковом виде содержится »

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

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

3.

Цитата:

Цитата Tonny_Bennet

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

Поясните.

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. Какова должна быть кодировка выходного текстового файла?

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

ANSI

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

Цитата:

Цитата Tonny_Bennet

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

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

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

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

Цитата:

Цитата Tonny_Bennet

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

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

Цитата:

Цитата Iska

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

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

Цитата:

Цитата Iska

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

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

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

Цитата:

Цитата Tonny_Bennet

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

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

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