Сегодня расскажу о том, как очень быстро и удобно загрузить данные из MS EXCEL. При этом данные из xls-файлов можно сразу преобразовывать при загрузке через стандартные настройки, что сильно экономит время при разработке.
Пример 1. Загрузка простой таблицы из Excel
Для того, чтобы загрузить таблицу из MS EXCEL, заходим в редактор скрипта (Edit Script) в QlikView и создаем новую вкладку.
На ней пишем название будущей таблицы «январь».
После этого я нажала кнопку Table File и прописала путь к xls-файлу, из которого буду загружать данные. Открылось окно загрузки.
- В опции Tables выбираем вкладку xls-файла. Она у меня называется «январь».
- Опция Header Siz» отвечает за то, с какой строки данные будут загружаться. Так как у меня данные начинаются сразу с первой строки, то я оставила значение None.
- В опции Labels я выбрала значение Embedded Labels, чтобы первая строка загружаемых данных была заголовком.
- После этого я нажала кнопку Finish.
На вкладке появился текст скрипта:
1 2 3 4 5 6 7 8 9 10 11 |
январь: LOAD Дата, [Код товара], [Наименование товара], Цена, Количество, Сумма FROM [C:\Users\Polina\Downloads\001_Tips_ATK\01_Быстрая выгрузка из Excel\TEST.xlsx] (ooxml, embedded labels, table is январь); |
Я нажала кнопку Ок, а потом кнопку Reload на панели управления. Теперь данные из Excel загружены в QlikView.
После этого создаем таблицу с данными:
- Щелкаем по листу правой кнопкой мыши, выбираем пункт New Object, затем выбираем Table Box.
- В опции Show Fields from Table выбираем название загруженной таблицы «январь».
- Нажимаем кнопку Add All, чтобы все поля, которые относятся к этой таблице, были показаны
- Для сохранения порядка полей таким же, как и при загрузке данных, нажимаем Load Order.
- Затем нажимаем Ок.
В результате у меня получилась таблица как и была в таблице Excel.
Пример 2. Загрузка таблицы из Excel в другом формате
Повторяем все шаги для загрузки данных, что и в первом примере, только после первого окна кнопку Finish не нажимаем, а нажимаем кнопку Next.
Передо мной открылось следующее окно загрузки.
- Я нажала кнопку Enable Transformation Step
- После этого захожу на вкладку Rotate. Здесь я могу выбрать способ трансформации данных
- Нажимаю кнопку Transpose, и данные у меня изменили формат так, как мне нужно:
- Если, например, мне нужно не все столбцы или строки загружать, я иду на вкладку Garbage.
- Выделяю столбец или строчку, которые мне не нужны, и нажимаю кнопку Delete Marked.
А вот теперь можно нажать кнопку Next. Передо мной открылось окно загрузки и нажимаем кнопку «Finish».
На вкладке появился текст скрипта:
1 2 3 4 5 6 7 8 9 10 11 12 |
январь2: LOAD Дата, [Код товара], [Наименование товара], Цена, Количество, Сумма FROM [C:\Users\Polina\Downloads\001_Tips_ATK\01_Быстрая выгрузка из Excel\TEST.xlsx] (ooxml, embedded labels, table is январь2, filters( Transpose() )); |
Нажимаем кнопку Ок, а потом кнопку Reload на панели управления.
Теперь данные из xls-файла загружены в QlikView.
После этого я создаю таблицу с данными, как и в первом примере.
- Щелкаем по таблице правой кнопкой мыши и выбираем пункт Properties.
- Заходим на вкладку Number и для всех полей таблицы поставим флаг Override Document Settings.
- Для полей «Код товара», «Цена», «Количество» и «Сумма» я поставила формат Integer, а для поля «Дата» формат «Date».
- Нажимаю кнопку «Ок».
В результате у меня получилась таблица, как и в Excel-файле.
Пример 3. Загрузка данных одинакового формата из нескольких вкладок xls-файла.
Для начала нужно поменять уровень доступа для макросов документа с Limit Module Script to Safe Mode на Give System Access to Module Script.
И создаем функцию для сохранения названий вкладок Excel-файла. Для этого я:
- Зашла на панели управления QlikView в Settings.
- Выбрала пункт Document Properties и зашла на вкладку Triggers.
- Выбрала в опции Document Event Triggers пункт OnOpen и нажала кнопку Add Actoin(s).
- Появилось окно для добавления действий на открытие документа.
- Нажала кнопку Add, выбрала в Action Type тип действия External , а в Action Type — действие Run Macro.
- Нажала кнопку Ок.
- Ввела в поле Macro Name название макроса
- Нажала кнопку Edit Module.
- Ввела в окне ***Ready*** текст 1ой функции:
1 2 3 |
Function ModuleSecurity() ActiveDocument.SetCurrentModuleSecurity 2 End Function |
Эта функция отвечает за изменение уровня доступа для макросов документа.
- Ввела в окне ***Ready*** текст 2ой функции:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
Function NameSheets(P) Dim Book1_WS, Workbook Dim Sheet1_WS, Worksheet Dim SN, string Dim i, int Sn="" Set objExcel = CreateObject("Excel.Application") Set Book1_WS = objExcel.Application.Workbooks.Open(P) For i=1 to Book1_WS.Sheets.Count Set Sheet1_WS = Book1_WS.Sheets(i) if SN = "" then SN = Sheet1_WS.Name else SN = SN+";"+Sheet1_WS.Name end if Next Book1_WS.Close NameSheets = SN END FUNCTION |
Эта функция возвращает название вкладки Excel-файла.
- После этого я нажала кнопку Cheсk и кнопку Ok.
- Нажала кнопку Ok, чтобы сохранить изменения в окне Actions.
- Нажала кнопку Ok, чтобы сохранить изменения в окне Document properties.
Захожу в редактор скрипта (Edit Script) в QlikView и создаю новую вкладку.
- Объявляю переменную vSecurity, она отвечает за замену доступа к макросу
1 |
LET vSecurity = ModuleSecurity(); |
- С помощью переменной vPath выделяю часть пути к папке, где лежит Excel-файл
1 |
LET vPath = left(DocumentPath(),Index(DocumentPath(),'\',-2)-1); |
- В переменную vRootFile записываю полный путь к Excel-файлу и его название
1 |
LET vRootFile = '$(vPath)\Быстрая выгрузка из Excel\TEST.xlsx'; |
- Объявляю переменную vRoot, которая будет возвращать названия вкладок Excel-файла, которые нужно загрузить
1LET vRoot = NameSheets('$(vRootFile)');
- В переменную vFile записываю вырезанное из пути название Excel-файла
1LET vFile = Replace(Mid('$(vRootFile)',Index('$(vRootFile)','\', SubStringCount('$(vRootFile)','\'))+1),'.xlsx','');
- В переменной vLen считаю количество вкладок Excel-файла
1LET vLen = SubStringCount('$(vRoot)',';')+1;
- С помощью оператора управления For … NEXT создаю цикл. Цикл идет от 1 и до количества вкладок Excel-файла:
1 2 |
For i= 1 to vLen NEXT i; |
- Внутри цикла выделяется название вкладки Excel-файла и загружаются все данные на ней. И так до тех пор, пока данные на всех вкладках не будут загружены во временную таблицу tmp:
1LET vSheetName = SubField('$(vRoot)',';',$(i)); - Создаем таблицу «tmp»и загружаем в нее данные tmp:
1 2 3 4 5 |
LOAD * From [$(vFile).xlsx] (ooxml, embedded labels, table is $(vSheetName)); |
- Функция TRACE выдает сообщение, что выгрузка завершена:
1 2 |
TRACE; TRACE Выгрузка Завершена; |
- Нажимаю кнопку Ок, а потом кнопку Reload на панели управления.
В результате получается выполнения скрипта получается общая таблица с данными с трех вкладок Excel-файла.
Этот способ очень удобен тем, что не нужно загружать данные поочередно с каждой вкладки стандартным приемом, который был описан в первом примере. Здесь неважно, какое у нас количество вкладок, главное, чтобы их формат был одинаковым, иначе таблица будет выглядеть некорректно.
По своему усмотрению можно также загружать не все данные, а только часть, как показано во втором примере.
Файл для скачивания – пример загрузки данных из Excel, который я разбирала в посте.
На этом все. Пользуйтесь этими способами и комментируйте здесь.
Свежие комментарии