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

[Tonny_Bennet]

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

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

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

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

[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»:

http://img708.imageshack.us/img708/9605/m79i.png

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

P.S. Вы не ответили:
желательно со сквозной нумерацией. »
Поясните это пожелание.

[Tonny_Bennet]

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

Создаётся файл только с первым телефоном. Потом вылезает ошибка.
http://i080.radikal.ru/1306/74/4d10a964c733.png

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

Т.е. если обработав первую страницу получилось 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