Вы можете выгрузить данные из FreshOffice или, наоборот, загрузить в систему уже существующую базу данных из Excel.

1. Как сделать экспорт данных в Excel?

— Зайдите в модуль “Аналитика”.

— Кликните один раз по отчету, который вы хотите выгрузить.
— Нажмите кнопку “Экспорт”, и файл Excel сохранится на ваш компьютер.

О том, как настроить отчет, мы рассказываем в ответе на вопрос “Как работать с Отчетами?”

2. Как сделать импорт данных из Excel?

Вы можете импортировать в FreshOffice список контрагентов или список товаров и услуг.

— Подготовьте Excel файл: на одного контрагента/товар должна приходиться одна строка в таблице. Пример файла:

— Закройте файл Excel перед импортом.

— Очистите корзину в модуле Сервис.

— Откройте модуль Сервис, перейдите в Импорт данных, выберите данные, которые хотите импортировать.

 

— Укажите путь к Excel-файлу с данными.
— Теперь укажите, какие данные содержит ваш Excel файл, и как называются соответствующие столбцы (2). Слева указано название типа данных в FreshOffice, а справа вам необходимо ввести название столбца с аналогичными данными в Excel.
— Нажмите “Запустить” (3).

Обратите внимание на блок выбора вариантов «Искать дублирующихся по» и «Что делать с дублирующимися контрагентами?»

Набирать информацию с клавиатуры дело хлопотное и трудоемкое. И ладно бы в Word, а то в Excel, где нужно перемещаться между ячейками. Поэтому, если информация имеет место быть в текстовом виде, это уже хорошо. Рассмотрим, как быстро выполнить импорт данных в Excel из текстового файла или, говоря по другому, корректно вставить текст в таблицу Excel.

Очень-очень сложного тут ничего нет. Как я уже неоднократно отмечал, информация в таблице Excel должна быть представлена строго структурированной, следовательно, в текстовом файле эта структурированность также должна строго соблюдаться.

Возьмём такой жизненный и несложный пример — перенесем из текстового файла (с расширением .txt) в Excel список сотрудников, в котором перечислены фамилия, имя , отчество, год рождения и должность.

Допустим, вот наши герои:

Иванов Иван Иванович 1978 инспектор
Сидоров Петр Сидорович 1970 старший инспектор
Петров Василий Самуилович 1965 самый старший инспектор

Больше трех лень набивать 🙂 , но и их хватит.

Теперь определимся, какая нам нужна в Excel таблица? Пусть она имеет такие столбцы:
Фамилия, Имя, Отчество, Год рождения, Должность.

Все у нас имеется, но тут есть один нюанс в представлении должности. Для каждого сотрудника должность имеет разный размер – 1, 2 и 3 слова. Как же Excel указать, где начинается и заканчивается информация, заносимая по столбцам?

Для этого удобно использовать символ-разделитель. Таким символом может быть любой символ, который не встречается в Вашем тексте как знак препинания или спецсимвол. То есть, точка и запятая точно старайтесь не использовать, а вот звездочку или наклонную можно взять. В нашем случае, если бы должность у всех состояла из одного слова или так – ст.инспектор, сам.ст.инспектор (без пробелов), то подошел бы и пробел. Подходите творчески, чтобы меньше делать работы. 🙂

Структурируем наш список:

Фамилия/Имя/Отчество/Год рождения/Должность
Иванов/Иван/Иванович/1978/инспектор
Сидоров/Петр/Сидорович/1970/старший инспектор
Петров/Василий/Самуилович/1965/самый старший инспектор

и сохраняем в текстовом документе c расширением .txt . Теперь такой список замечательно перенесется в Excel.

Для этого заходим в Excel на вкладку Данные. В области Получение внешних данных нажимаем кнопку Из текста.

В открывшемся окне указываем на наш текстовый файл, из которого будем импортировать данные. После этого откроется Мастер импорта на первом шаге. Тут нужно указать структуру наших данных – “с разделителями” (которым у нас выступает наклонная «/»). После этого нажимаем кнопку Далее.

На следующем шаге мастера нужно указать символ-разделитель. Тут приведены некоторые варианты, но мы выбираем “другой” и в окошке вводим наш символ – наклонную. Сразу же в нижней области (с нашим списком) появляются вертикальные линии (столбцы), по которым можно проверить правильность разделения данных. Нажимаем кнопку Далее.

На третьем шаге можно указать формат данных для каждого или выбранного столбца, если в этом есть необходимость. Как правило, такое актуально для столбцов с датой. Мы же ничего не меняем и жмём кнопку Готово.

После этого нужно указать ячейку, с которой будет начинаться таблица – её левый верхний угол.

После выбора ячейки и нажатия OK мы увидим наши данные, разнесенные по столбцам! Вуаля! Наш текст в таблицу Excel вставлен красиво и правильно.

Теперь можно навести марафет, указав выравнивание и задав границы.

В продолжение темы, начатой в

предыдущей статье

, хочу поделиться своим опытом экспорта данных, в частности, в формате XLSX.

Итак, кому интересно, как заполнить XLSX без больших и сложных библиотек, прошу под кат.

Недавно передо мной возникла задача экспортировать непредсказуемый по размеру объем табличных данных в формате XLSX. Как любой здравомыслящий программист, первым делом полез искать готовые решения.

Почти сразу наткнулся на библиотеку

PHPExcel

. Мощное решение, с кучей разных функций и возможностей. Порывшись еще немного нашел отзывы программистов о ней. В частности, на форумах встречаются жалобы на скорость работы и отказ работать с большим объемом данных. Отметил библиотеку как один из вариантов решения и начал искать дальше.

Находил еще несколько библиотек для работы с XLSX, но все они были или забытыми, т.к. не обновлялись по 2-3 года, или обязательно тянули за собой сторонние библиотеки, или использовали

DOM

для работы с файлами, что мне не очень нравилось. Каждый раз, натыкаясь на очередную библиотеку и изучая механизмы ее работы, ловил себя на мысли, что все это «из пушки по воробьям». Не нужно мне такое сложное решение!

Признаюсь честно, изучив поверхностно каждое из найденных решений, не стал ставить и тестировать ни одного. Мне нужно было более простое и надежное, как танк, решение.

Задача

В общем, раз не нашел ничего подходящего, значит надо сформулировать технические требования к тому, что нужно. Требования, как и следовало ожидать, оказались тривиальными:

• Оформить экспортирующий механизм в виде автономного класса
• Реализовать в классе набор функций для записи значений ячеек и ряда
• Возможность работы с неограниченным объемом данных
• Распаковка и упаковка XLSX.

Отдельно остановлюсь только на последнем пункте. Как известно, XLSX представляет собой обычный zip-архив, который можно распаковать и увидеть, что он состоит из нескольких файлов и каталогов. Обратным образом его можно упаковать и переименовать в XLSX. Если все изменения правильные, то Microsoft Excel откроет файл без проблем.

Реализация

Изначально очень хотел создавать все файлы, из которых состоит XLSX, кодом, но, к счастью, быстро понял бессмысленность своей идеи. И родилось иное, более правильно и простое решение. Надо с помощью Microsoft Excel создать файл XLSX в таком виде, в каком он нужен в итоге, но без данных, иными словами — шаблон, а потом, с помощью кода, только добавить данные!

В таком случае, класс должен будет распаковывать шаблон в отдельный каталог, вносить изменения в /xl/worksheets/sheet1.xml и упаковывать содержимое каталога обратно в XLSX.

В объявлении класса присутствуют публичные переменные:

$templateFile – имя файла шаблона

$exportDir – папка, в которую будет распакован шаблон, разумеется с необходимыми правами доступа.

Конструктор класса принимает имя будущего файла, количество колонок и рядов. Потом проверяет, что имя файла корректно, папка для распаковки шаблона существует и формирует полное имя конечной папки для распаковки шаблона.

После создания класса можно распаковать шаблон и открыть на запись sheet1.xml. На самом деле я не просто дописываю в файл, а полностью его перезаписываю. Однажды взяв из него начальную строку, вношу в нее изменение в тэге dimension, который отражает размер экспортируемого диапазона, и записываю в файл.

public function openWriter() { 	if (is_dir($this->baseDir)) 		CFileHelper::removeDirectory($this->baseDir); 	mkdir($this->baseDir);  	exec("unzip $this->templateFullFilename -d "$this->baseDir"");  	$this->workSheetHandler = fopen($this->baseDir.'/xl/worksheets/sheet1.xml', 'w+');  	fwrite($this->workSheetHandler, ''); } 

Обеспечить скорость работы и возможность работы с большим объемом данных позволяют функции resetRow и flushRow. Они отвечают за очистку текущего ряда в памяти и запись текущего ряда на диск.

А вот сохранение значений ячеек с разными типами оказалось не такой простой задачей.

Запись строки

Казалось бы, что сложного записать строковое значение в файл. Однако, в XLSX все не так просто. Все строки внутри XLSX хранятся в отдельном файле /xl/sharedStrings.xml. В ячейки записываются не строковые значения, а их порядковые номера — индексы. Разумное решение с точки зрения сокращения размера файла.

Но такое решение неудобно с точки зрения программного заполнения шаблона. Если выполнять указанное требование, то мне бы пришлось выполнять отдельный проход по всем строковым значениям в массиве данных, исключать повторяющиеся, сохранять их в sharedStrings.xml, проиндексировать и вместо значений в исходном массиве вписать их индексы. Медленно и неудобно.

Оказывается, можно обойти требование и сохранять строковые значения ячеек прямо в ячейках. Но в этом случае формат записи будет иной:

public function appendCellString($value) { 	$this->curCel++; 	if (!empty($value)) { 		$value = htmlspecialchars($value, ENT_QUOTES, 'UTF-8'); 		$value = preg_replace( '//', '', $value ); 		$this->currentRow[] = ''.$value.''; 		$this->numStrings++; 	} } 

Запись числа

Никаких сложностей с записью целых или дробных чисел не возникло. Все просто:

public function appendCellNum($value) { 	$this->curCel++; 	$this->currentRow[] = ''.$value.''; } 

Запись даты и времени

Дата и время хранятся в виде количества секунд прошедших с 01.01.1970 поделенных на количество секунд в сутках. Причем, в вычислении допущена ошибка с определением високосного года. В общем, не вдаваясь в подробности, которые несложно найти в сети, чтобы корректно вычислять дату пришлось объявить в классе две константы:

ZERO_TIMESTAMP – смещение даты в формате Excel от UNIX_TIMESTAMP

SEC_IN_DAY – секунд в сутках.

После вычисления значения даты и времени, целая часть дроби – это дата, дробная часть – время:

const ZERO_TIMESTAMP = 2209161600; const SEC_IN_DAY = 86400;  public function appendCellDateTime($value) { 	$this->curCel++;  	if (empty($value)) 		$this->appendCellString(''); 	else 	{ 		$dt = new DateTime($value); 		$ts = $dt->getTimestamp() + self::ZERO_TIMESTAMP; 		$this->currentRow[] = ''.$ts/self::SEC_IN_DAY.''; 	} } 

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

Применение

Как и раньше, использование описанного класса основано на экспорте данных с помощью провайдера CArrayDataProvider. Предполагая, что объем экспортируемых данных может оказаться очень большим, применен специальный итератор CDataProviderIterator, который перебирает возвращаемые данные по 100 записей (можно указать иное число записей).

public function exportXLSX($organization, $user, &$filename) { 	$this->_provider = new CArrayDataProvider(/*query*/);  	Yii::import('ext.AlxdExportXLSX.AlxdExportXLSX'); 	$export = new AlxdExportXLSX($filename, count($this->_attributes), $this->_provider->getTotalItemCount() + 1);  	$export->openWriter(); 	$export->resetRow(); 	$export->openRow(true); 	foreach ($this->_attributes as $code => $format) 		$export->appendCellString($this->_objectref->getAttributeLabel($code)); 	$export->closeRow(); 	$export->flushRow();  	$rows = new CDataProviderIterator($this->_provider, 100); 	foreach ($rows as $row) 	{ 		$export->resetRow(); 		$export->openRow();  		foreach ($this->_attributes as $code => $format) 		{ 			switch ($format->type)             {                 case 'Num':                     $export->appendCellNum($row);                 /*other types*/                 default:                     $export->appendCellString('');					             } 		}  		$export->closeRow(); 		$export->flushRow(); 	} 	$export->closeWriter(); 	$export->zip();  	$filename = $export->getZipFullFileName(); } 

Кому интересно, может получить исходный код моего класса AlxdExportXLSX совершенно безвозмездно.

В продолжение темы, начатой в

предыдущей статье

, хочу поделиться своим опытом экспорта данных, в частности, в формате XLSX.

Итак, кому интересно, как заполнить XLSX без больших и сложных библиотек, прошу под кат.

Недавно передо мной возникла задача экспортировать непредсказуемый по размеру объем табличных данных в формате XLSX. Как любой здравомыслящий программист, первым делом полез искать готовые решения.

Почти сразу наткнулся на библиотеку

PHPExcel

. Мощное решение, с кучей разных функций и возможностей. Порывшись еще немного нашел отзывы программистов о ней. В частности, на форумах встречаются жалобы на скорость работы и отказ работать с большим объемом данных. Отметил библиотеку как один из вариантов решения и начал искать дальше.

Находил еще несколько библиотек для работы с XLSX, но все они были или забытыми, т.к. не обновлялись по 2-3 года, или обязательно тянули за собой сторонние библиотеки, или использовали

DOM

для работы с файлами, что мне не очень нравилось. Каждый раз, натыкаясь на очередную библиотеку и изучая механизмы ее работы, ловил себя на мысли, что все это «из пушки по воробьям». Не нужно мне такое сложное решение!

Признаюсь честно, изучив поверхностно каждое из найденных решений, не стал ставить и тестировать ни одного. Мне нужно было более простое и надежное, как танк, решение.

Задача

В общем, раз не нашел ничего подходящего, значит надо сформулировать технические требования к тому, что нужно. Требования, как и следовало ожидать, оказались тривиальными:

• Оформить экспортирующий механизм в виде автономного класса
• Реализовать в классе набор функций для записи значений ячеек и ряда
• Возможность работы с неограниченным объемом данных
• Распаковка и упаковка XLSX.

Отдельно остановлюсь только на последнем пункте. Как известно, XLSX представляет собой обычный zip-архив, который можно распаковать и увидеть, что он состоит из нескольких файлов и каталогов. Обратным образом его можно упаковать и переименовать в XLSX. Если все изменения правильные, то Microsoft Excel откроет файл без проблем.

Реализация

Изначально очень хотел создавать все файлы, из которых состоит XLSX, кодом, но, к счастью, быстро понял бессмысленность своей идеи. И родилось иное, более правильно и простое решение. Надо с помощью Microsoft Excel создать файл XLSX в таком виде, в каком он нужен в итоге, но без данных, иными словами — шаблон, а потом, с помощью кода, только добавить данные!

В таком случае, класс должен будет распаковывать шаблон в отдельный каталог, вносить изменения в /xl/worksheets/sheet1.xml и упаковывать содержимое каталога обратно в XLSX.

В объявлении класса присутствуют публичные переменные:

$templateFile – имя файла шаблона

$exportDir – папка, в которую будет распакован шаблон, разумеется с необходимыми правами доступа.

Конструктор класса принимает имя будущего файла, количество колонок и рядов. Потом проверяет, что имя файла корректно, папка для распаковки шаблона существует и формирует полное имя конечной папки для распаковки шаблона.

После создания класса можно распаковать шаблон и открыть на запись sheet1.xml. На самом деле я не просто дописываю в файл, а полностью его перезаписываю. Однажды взяв из него начальную строку, вношу в нее изменение в тэге dimension, который отражает размер экспортируемого диапазона, и записываю в файл.

public function openWriter() { 	if (is_dir($this->baseDir)) 		CFileHelper::removeDirectory($this->baseDir); 	mkdir($this->baseDir);  	exec("unzip $this->templateFullFilename -d "$this->baseDir"");  	$this->workSheetHandler = fopen($this->baseDir.'/xl/worksheets/sheet1.xml', 'w+');  	fwrite($this->workSheetHandler, ''); } 

Обеспечить скорость работы и возможность работы с большим объемом данных позволяют функции resetRow и flushRow. Они отвечают за очистку текущего ряда в памяти и запись текущего ряда на диск.

А вот сохранение значений ячеек с разными типами оказалось не такой простой задачей.

Запись строки

Казалось бы, что сложного записать строковое значение в файл. Однако, в XLSX все не так просто. Все строки внутри XLSX хранятся в отдельном файле /xl/sharedStrings.xml. В ячейки записываются не строковые значения, а их порядковые номера — индексы. Разумное решение с точки зрения сокращения размера файла.

Но такое решение неудобно с точки зрения программного заполнения шаблона. Если выполнять указанное требование, то мне бы пришлось выполнять отдельный проход по всем строковым значениям в массиве данных, исключать повторяющиеся, сохранять их в sharedStrings.xml, проиндексировать и вместо значений в исходном массиве вписать их индексы. Медленно и неудобно.

Оказывается, можно обойти требование и сохранять строковые значения ячеек прямо в ячейках. Но в этом случае формат записи будет иной:

public function appendCellString($value) { 	$this->curCel++; 	if (!empty($value)) { 		$value = htmlspecialchars($value, ENT_QUOTES, 'UTF-8'); 		$value = preg_replace( '//', '', $value ); 		$this->currentRow[] = ''.$value.''; 		$this->numStrings++; 	} } 

Запись числа

Никаких сложностей с записью целых или дробных чисел не возникло. Все просто:

public function appendCellNum($value) { 	$this->curCel++; 	$this->currentRow[] = ''.$value.''; } 

Запись даты и времени

Дата и время хранятся в виде количества секунд прошедших с 01.01.1970 поделенных на количество секунд в сутках. Причем, в вычислении допущена ошибка с определением високосного года. В общем, не вдаваясь в подробности, которые несложно найти в сети, чтобы корректно вычислять дату пришлось объявить в классе две константы:

ZERO_TIMESTAMP – смещение даты в формате Excel от UNIX_TIMESTAMP

SEC_IN_DAY – секунд в сутках.

После вычисления значения даты и времени, целая часть дроби – это дата, дробная часть – время:

const ZERO_TIMESTAMP = 2209161600; const SEC_IN_DAY = 86400;  public function appendCellDateTime($value) { 	$this->curCel++;  	if (empty($value)) 		$this->appendCellString(''); 	else 	{ 		$dt = new DateTime($value); 		$ts = $dt->getTimestamp() + self::ZERO_TIMESTAMP; 		$this->currentRow[] = ''.$ts/self::SEC_IN_DAY.''; 	} } 

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

Применение

Как и раньше, использование описанного класса основано на экспорте данных с помощью провайдера CArrayDataProvider. Предполагая, что объем экспортируемых данных может оказаться очень большим, применен специальный итератор CDataProviderIterator, который перебирает возвращаемые данные по 100 записей (можно указать иное число записей).

public function exportXLSX($organization, $user, &$filename) { 	$this->_provider = new CArrayDataProvider(/*query*/);  	Yii::import('ext.AlxdExportXLSX.AlxdExportXLSX'); 	$export = new AlxdExportXLSX($filename, count($this->_attributes), $this->_provider->getTotalItemCount() + 1);  	$export->openWriter(); 	$export->resetRow(); 	$export->openRow(true); 	foreach ($this->_attributes as $code => $format) 		$export->appendCellString($this->_objectref->getAttributeLabel($code)); 	$export->closeRow(); 	$export->flushRow();  	$rows = new CDataProviderIterator($this->_provider, 100); 	foreach ($rows as $row) 	{ 		$export->resetRow(); 		$export->openRow();  		foreach ($this->_attributes as $code => $format) 		{ 			switch ($format->type)             {                 case 'Num':                     $export->appendCellNum($row);                 /*other types*/                 default:                     $export->appendCellString('');					             } 		}  		$export->closeRow(); 		$export->flushRow(); 	} 	$export->closeWriter(); 	$export->zip();  	$filename = $export->getZipFullFileName(); } 

Кому интересно, может получить исходный код моего класса AlxdExportXLSX совершенно безвозмездно.

Microsoft Excel – удобный инструмент для организации и структурирования самых разнообразных данных. Он позволяет обрабатывать информацию разными методами, редактировать массивы данных.

Рассмотрим возможности использования его для формирования и обработки файлов веб-приложений. На конкретном примере изучим основы работы с XML в Excel.

Как создать XML-файл из Excel

XML – стандарт файла для передачи данных в Сети. Excel поддерживает его экспорт и импорт.

Рассмотрим создание XML-файла на примере производственного календаря.

1. Сделаем таблицу, по которой нужно создать XML файл в Excel и заполним ее данными.
2. Создадим и вставим карту XML с необходимой структурой документа.
3. Экспортируем данные таблицы в XML формат.

Итак.

1. Наша таблица – производственный календарь.
2. Создаем в любом текстовом редакторе (например, «Блокнот») желаемую карту XML структуры для генерации файла сохраним. В данном примере буде использовать следующую карту структуры:
3. Открываем инструмент «Разработчик». Диалоговое окно «Источник» на вкладке XML.
4. Если программа Excel не видит карт XML, их нужно добавить. Жмем «карты XML». И указываем путь к нашему файлу с выше указанной схемой карты созданной в текстовом редакторе.
5. Добавить ОК.
6. В правой колонке появляются элементы схемы. Их нужно перетащить на соответствующие названия столбцов таблицы.
7. Проверяем возможен ли экспорт.
8. Когда все элементы будут сопоставлены, щелкаем правой кнопкой мыши по любой ячейке в таблице – XML – экспорт.

Сохраняем в XML файл.

Другие способы получения XML-данных (схемы):

1. Скачать из базы данных, специализированного бизнес-приложения. Схемы могут предоставляться коммерческими сайтами, службами. Простые варианты находятся в открытом доступе.
2. Использовать готовые образцы для проверки карт XML. В образцах – основные элементы, структура XML. Копируете – вставляете в программу «Блокнот» — сохраняете с нужным расширением.

Как сохранить файл Excel в формате XML

Один из вариантов:

1. Нажимаем кнопку Office. Выбираем «Сохранить как» — «Другие форматы».
2. Назначаем имя. Выбираем место сохранения и тип файла – XML.

Сохранить.

Если выдает ошибку, книгу можно сохранить в виде таблицы XML 2003 либо веб-страницы. С этими форматами проблем, как правило, не возникает.

Еще варианты:

1. Скачать конвертер XLC в XML. Либо найти сервис, с помощью которого можно экспортировать файл онлайн.
2. Скачать с официального сайта Microsoft надстройку XML Tools Add-in. Она в бесплатном доступе.
3. Открываем новую книгу. Кнопка Office – «Открыть».

Как открыть XML файл в Excel

1. Меняем формат на «файлы XML». Выбираем нужный файл. Жмем «Открыть».
2. Способ открытия – XML-таблица. ОК.
3. Появляется оповещение типа

Жмем ОК. С полученной таблицей можно работать, как с любым файлом Excel.

Как преобразовать файл XML в Excel

1. Меню «Разработчик» — вкладка «Импорт».
2. В диалоговом окне выбираем файл XML, который хотим преобразовать.
3. Нажимаем «Импорт». Программа Excel предложит самостоятельно создать схему на основе полученных данных. Соглашаемся – ОК. Откроется окно, где нужно выбрать место для импортируемого файла.
4. Назначаем диапазон для импорта. Лучше брать с «запасом». Жмем ОК.

Созданную таблицу редактируем и сохраняем уже в формате Excel.

Как собрать данные из XML файлов в Excel

Принцип сбора информации из нескольких XML-файлов такой же, как и принцип преобразования. Когда мы импортируем данные в Excel, одновременно передается карта XML. В эту же схему можно переносить и другие данные.

Каждый новый файл будет привязываться к имеющейся карте. Каждому элементу в структуре таблицы соответствует элемент в карте. Допускается только одна привязка данных.

Чтобы настроить параметры привязки, откройте в меню «Разработчик» инструмент «Свойства карты».

Возможности:

1. Каждый новый файл будет проверяться программой Excel на соответствие установленной карте (если поставим галочку напротив этого пункта).
2. Данные могут обновляться. Либо новая информация добавится в существующую таблицу (имеет смысл, если нужно собрать данные из похожих файлов).

Это все ручные способы импорта и экспорта файлов.