Javascript and excel vba

База данных из JavaScript для веб страницы из Excel на VBA модуле

Всем привет, в этой статье я покажу, как с помощью Excel можно формировать JS массивы на основе таблиц. Звучит как бред, однако в дальнейшем можно сохранить массив в js файл, изменить расширение файла на DB и полученную «базу данных» подключать к простой веб странице, можно использовать в Android приложении или расширении для браузера. Меняя содержимое файла можно изменять данные не затрагивая html код, и тут пригодится Excel, который возьмет на себя перевод данных из таблицы в массив. Разбор из файла в JSON будет происходить в веб странице.

Смотрите видео: База данных из JavaScript для веб страницы из Excel на VBA модуле.

Итак, сохраним книгу Excel с поддержкой макросов, работать будем с двумя листами, tab — таблица и test — лист с кнопкой и готовым результатом. В редакторе VBA добавим новый модуль, и внесем в него следующий код:

Sub main() Dim myCol, myRow As Integer Dim name1, name2 As String Dim namval myRow = 4 'строки myCol = 2 'столбцы name1 = "name" name2 = "age" namval = Sheets("tab").Range(Sheets("tab").Cells(1, 1), Sheets("tab").Cells(myRow, myCol)) For j = 1 To myRow Sheets("test").Cells(j, 1) = "," If j = myRow Then Sheets("test").Cells(j, 1) = "" End If Next j myRow = WorksheetFunction.CountA(Sheets("tab").Range("A:A")) 'строки myCol = WorksheetFunction.CountA(Sheets("tab").Range("A1:AAA1")) 'столбцы MsgBox "строки: " & myRow & " столбцы " & myCol End Sub

Привяжем кнопку к модулю и нажмем на нее. Полученные данные вставим в текстовый файл с текстом

между кавычками. Файл сохраняем как JS, но с расширением db.

В секции head веб страницы(я ипользовал bootstrap шаблон), добавим «базу данных» как если бы это был скрипт. Прим: при использовании уберите пробел перед левой кавычкой!

В секции body опишем таблицу для вывода данных и script , который будет формировать строки и столбцы на странице. Прим: при использовании уберите пробел перед левой кавычкой!

 < table > < thead> < tr>< th scope="col">Имя < th scope="col">Возраст < /tr> < /thead> < tbody> < script>for(x=0;x < arr.length;x++)< var serializedUser = JSON.stringify(arr[x]); var user = JSON.parse(serializedUser); document.write("< tr>< td>" + user.name + "< /td>< td>" + user.age + "< /td>< /tr>"); > < /script>< /tbody> < /table>

При запуске html страницы будет отображена таблица: заголовок Имя и Возраст и данные name и age .

Первый VBA модуль обладает одним недостатком: имена полей приходится указывать в коде и самих полей только два, данные начинаются с первой строки. Вторая версия модуля сама определяет имена полей и их количество, «шапка» таблицы в первой строке, данные начинаются со второй строки. Сделайте копии листов tab , test сохранив как tab2 , test2 , добавьте новый модуль, вставьте в него следующий код и присвойте кнопке Module2.

Sub main2() Dim myCol, myRow, namCount As Integer Dim name1, name2 As String Dim namval, nam Dim str0, str1, str2, str3, str4, mStr str0 = "," str4 = ">" 'myRow = 5 'строки 'myCol = 2 'столбцы myRow = WorksheetFunction.CountA(Sheets("tab2").Range("A:A")) 'строки myCol = WorksheetFunction.CountA(Sheets("tab2").Range("A1:AAA1")) 'столбцы namCount = WorksheetFunction.CountA(Sheets("tab2").Range("A1:N1")) 'количество столбцов namval = Sheets("tab2").Range(Sheets("tab2").Cells(2, 1), Sheets("tab2").Cells(myRow, myCol)) 'значения таблицы nam = Sheets("tab2").Range("A1:N1") 'шапка For j = 2 To myRow mStr = str0 For i = 1 To namCount mStr = mStr & nam(1, i) & str1 & namval(j - 1, i) & str2 Next i mStr = Left(mStr, Len(mStr) - 2) mStr = mStr & str3 Sheets("test2").Cells(j - 1, 1) = mStr If j = myRow Then mStr = str0 For i = 1 To namCount mStr = mStr & nam(1, i) & str1 & namval(j - 1, i) & str2 Next i mStr = Left(mStr, Len(mStr) - 2) mStr = mStr & str4 Sheets("test2").Cells(j - 1, 1) = mStr End If Next j End Sub

Данный код модуля позволяет автоматически определять количество строк и столбцов, названия заголовков шапки таблицы, которые будут впоследствии использованы для получаения данных. При этом код в html стрнице нормально работает и с русскими буквами: user.name и user.Имя распознаются одинаково.

На что еще хочу обратить внимание это кодировка страницы и файла «базы данных», они должны быть одинаковые, например UTF-8, для страницы указываем charset=utf8 , а файл test.db сохраняем в Notepad++, иначе вместо текста получатся «кракозябры».

Смотрите видео: База данных из JavaScript для веб страницы из Excel на VBA модуле.

Источник

Использование встроенных объектов JavaScript в сценариях Office

JavaScript предоставляет несколько встроенных объектов, которые можно использовать в сценариях Office, независимо от того, выполняете ли вы скрипты в JavaScript или TypeScript (надмножество JavaScript). В этой статье описывается использование некоторых встроенных объектов JavaScript в скриптах Office для Excel.

Полный список всех встроенных объектов JavaScript см. в статье Стандартные встроенные объекты Mozilla.

Array

Объект Array предоставляет стандартизированный способ работы с массивами в скрипте. Хотя массивы являются стандартными конструкциями JavaScript, они связаны со сценариями Office двумя основными способами: диапазонами и коллекциями.

Работа с диапазонами

Диапазоны содержат несколько двумерных массивов, которые напрямую сопоставляют ячейки в этом диапазоне. Эти массивы содержат конкретные сведения о каждой ячейке в этом диапазоне. Например, Range.getValues возвращает все значения в этих ячейках (со строками и столбцами двумерного массива, сопоставленными со строками и столбцами этого подраздела листа). Range.getFormulas и Range.getNumberFormats — это другие часто используемые методы, возвращающие массивы, такие как Range.getValues .

Следующий скрипт выполняет поиск в диапазоне A1:D4 для любого числового формата, содержащего «$». Скрипт задает цвет заливки в этих ячейках как «желтый».

function main(workbook: ExcelScript.Workbook) < // Get the range From A1 to D4. let range = workbook.getActiveWorksheet().getRange("A1:D4"); // Get the number formats for each cell in the range. let rangeNumberFormats = range.getNumberFormats(); // Iterate through the arrays of rows and columns corresponding to those in the range. rangeNumberFormats.forEach((rowItem, rowIndex) => < rangeNumberFormats[rowIndex].forEach((columnItem, columnIndex) =>< // Treat the numberFormat as a string so we can do text comparisons. let columnItemText = columnItem as string; if (columnItemText.indexOf("$") >= 0) < // Set the cell's fill to yellow. range.getCell(rowIndex, columnIndex).getFormat().getFill().setColor("yellow"); >>); >); > 

Работа с коллекциями

Многие объекты Excel содержатся в коллекции. Коллекция управляется API сценариев Office и предоставляется в виде массива. Например, все фигуры на листе содержатся в объекте Shape[] , возвращаемом методом Worksheet.getShapes . Этот массив можно использовать для чтения значений из коллекции или получить доступ к определенным объектам из методов родительского объекта get* .

Не добавляйте и не удаляйте объекты из этих массивов коллекций вручную. Используйте методы add для родительских объектов и delete методы для объектов типа коллекции. Например, добавьте таблицу на лист с Worksheet.addTable помощью метода и удалите с Table помощью Table.delete .

Следующий скрипт регистрирует тип каждой фигуры на текущем листе.

function main(workbook: ExcelScript.Workbook) < // Get the current worksheet. let selectedSheet = workbook.getActiveWorksheet(); // Get the shapes in this worksheet. let shapes = selectedSheet.getShapes(); // Log the type of every shape in the collection. shapes.forEach((shape) =>< console.log(shape.getType()); >); > 

Следующий скрипт удаляет старую фигуру на текущем листе.

function main(workbook: ExcelScript.Workbook) < // Get the current worksheet. let selectedSheet = workbook.getActiveWorksheet(); // Get the first (oldest) shape in the worksheet. // Note that this script will thrown an error if there are no shapes. let shape = selectedSheet.getShapes()[0]; // Remove the shape from the worksheet. shape.delete(); >

Дата

Объект Date предоставляет стандартизированный способ работы с датами в скрипте. Date.now() создает объект с текущими датами и временем, что полезно при добавлении меток времени в запись данных скрипта.

Следующий скрипт добавляет текущую дату на лист. Обратите внимание, что с помощью toLocaleDateString метода Excel распознает значение как дату и автоматически изменяет формат номера ячейки.

function main(workbook: ExcelScript.Workbook) < // Get the range for cell A1. let range = workbook.getActiveWorksheet().getRange("A1"); // Get the current date and time. let date = new Date(Date.now()); // Set the value at A1 to the current date, using a localized string. range.setValue(date.toLocaleDateString()); >

В разделе Работа с датами примеров содержится больше скриптов, связанных с датой.

математика;

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

Следующий скрипт используется для Math.min поиска и ведения журнала наименьшего числа в диапазоне A1:D4 . Обратите внимание, что в этом примере предполагается, что весь диапазон содержит только числа, а не строки.

function main(workbook: ExcelScript.Workbook) < // Get the range from A1 to D4. let comparisonRange = workbook.getActiveWorksheet().getRange("A1:D4"); // Load the range's values. let comparisonRangeValues = comparisonRange.getValues(); // Set the minimum values as the first value. let minimum = comparisonRangeValues[0][0]; // Iterate over each row looking for the smallest value. comparisonRangeValues.forEach((rowItem, rowIndex) => < // Iterate over each column looking for the smallest value. comparisonRangeValues[rowIndex].forEach((columnItem) =>< // Use `Math.min` to set the smallest value as either the current cell's value or the previous minimum. minimum = Math.min(minimum, columnItem); >); >); console.log(minimum); > 

Использование внешних библиотек JavaScript не поддерживается

Сценарии Office не поддерживают использование внешних сторонних библиотек. Скрипт может использовать только встроенные объекты JavaScript и API скриптов Office.

См. также

Источник

Объектная модель JavaScript для Excel в надстройках Office

В этой статье описано, как создавать надстройки для Excel 2016 или более поздней версии с помощью API JavaScript для Excel. В статье изложены основные принципы, которые являются фундаментальными при использовании этого API, а также имеются рекомендации по выполнению определенных задач, например чтению данных из большого диапазона или записи данных в него, изменения всех ячеек в диапазоне и много другого.

Сведения об асинхронном типе интерфейсов API Excel и принципах их работы с книгой см. в статье Использование модели API, зависящей от приложения.

Интерфейсы API Office.js для Excel

Надстройка Excel взаимодействует с объектами в Excel с помощью API JavaScript для Office, включающего две объектных модели JavaScript:

• API JavaScript для Excel. Появившийся в Office 2016 API JavaScript для Excel предоставляет строго типизированные объекты, с помощью которых можно получать доступ к листам, диапазонам, таблицам, диаграммам и другим объектам.
• Общие API. Появившиеся в Office 2013 общие API позволяют получать доступ к таким компонентам, как пользовательский интерфейс, диалоговые окна и параметры клиентов, общие для нескольких типов приложений Office. Ограниченная функциональность взаимодействия Excel в Общем API была заменена API JavaScript для Excel.

Хотя вы, скорее всего, будете использовать API JavaScript для Excel для разработки большинства функций, вы также будете использовать объекты в Общем API. Пример.

• Context. объект Context представляет среду выполнения надстройки и предоставляет доступ к ключевым объектам API. Он состоит из данных конфигурации книги, например contentLanguage и officeTheme , а также предоставляет сведения о среде выполнения надстройки, например host и platform . Кроме того, он предоставляет метод requirements.isSetSupported() , с помощью которого можно проверить, поддерживается ли указанный набор обязательных элементов приложением Excel, в котором работает надстройка.
• Document. Объект Document предоставляет метод getFileAsync() , позволяющий скачать файл Excel, в котором работает надстройка.

На рисунке ниже показано, когда можно использовать API JavaScript для Excel или общие API.

Объектная модель для Excel

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

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

В API JavaScript для Excel нет объекта или класса Cell. Вместо этого API JavaScript для Excel определяет все ячейки Excel как объекты Range . Отдельные ячейки в пользовательском интерфейсе Excel преобразуются в объект Range с одной ячейкой в API JavaScript для Excel. Один объект Range также может содержать несколько смежных ячеек. Дополнительные сведения см. в статье Работа с ячейками с использованием API JavaScript для Excel.

Диапазоны

Диапазон — это группа непрерывных ячеек в рабочей книге. В надстройках обычно используется нотация в стиле A1 (например, B3 для отдельной ячейки в столбце B и строке 3 или C2:F4 для ячеек из столбцов с C по F и строк с 2 по 4) для определения диапазонов.

Диапазоны имеют три основных свойства: values , formulas , и format . Эти свойства получают или устанавливают значения ячеек, формулы для оценки и визуальное форматирование ячеек.

Образец диапазона

В следующем примере показано, как создавать записи продаж. Эта функция использует объекты Range для установки значений, формул и форматов.

await Excel.run(async (context) => < let sheet = context.workbook.worksheets.getActiveWorksheet(); // Create the headers and format them to stand out. let headers = [ ["Product", "Quantity", "Unit Price", "Totals"] ]; let headerRange = sheet.getRange("B2:E2"); headerRange.values = headers; headerRange.format.fill.color = "#4472C4"; headerRange.format.font.color = "white"; // Create the product data rows. let productData = [ ["Almonds", 6, 7.5], ["Coffee", 20, 34.5], ["Chocolate", 10, 9.56], ]; let dataRange = sheet.getRange("B3:D5"); dataRange.values = productData; // Create the formulas to total the amounts sold. let totalFormulas = [ ["=C3 * D3"], ["=C4 * D4"], ["=C5 * D5"], ["=SUM(E3:E5)"] ]; let totalRange = sheet.getRange("E3:E6"); totalRange.formulas = totalFormulas; totalRange.format.font.bold = true; // Display the totals as US dollar amounts. totalRange.numberFormat = [["$0.00"]]; await context.sync(); >); 

В этом примере создаются следующие данные в текущем листе.

Диаграммы, таблицы и другие объекты данных

API JavaScript для Excel могут создавать и управлять структурами данных и визуализациями в Excel. Таблицы и диаграммы являются двумя наиболее часто используемыми объектами, но API поддерживают сводные таблицы, фигуры, изображения и многое другое.

Создание таблицы

Создавайте таблицы, используя заполненные данными диапазоны. Элементы управления форматированием и таблицами (например, фильтры) автоматически применяются к диапазону.

В следующем примере создается таблица с использованием диапазонов из предыдущего примера.

await Excel.run(async (context) => < let sheet = context.workbook.worksheets.getActiveWorksheet(); sheet.tables.add("B2:E5", true); await context.sync(); >); 

Использование этого примера кода на листе с предыдущими данными создает следующую таблицу.

Создание диаграммы

Создайте диаграммы для визуализации данных в диапазоне. API поддерживают десятки разновидностей диаграмм, каждая из которых может быть настроена в соответствии с вашими потребностями.

В следующем примере создается простая гистограмма для трех элементов, которая размещается на 100 пикселей ниже верхней части листа.

await Excel.run(async (context) => < let sheet = context.workbook.worksheets.getActiveWorksheet(); let chart = sheet.charts.add(Excel.ChartType.columnStacked, sheet.getRange("B3:C5")); chart.top = 100; await context.sync(); >); 

Выполнение этого примера на листе с предыдущей таблицей создает следующую диаграмму.

См. также

Источник