Рубрики
Uncategorized

Создайте файлы Excel с функциями Excel 2019 в C # .NET Первая часть

Этот блог является частью одной из многоуровневых серий, которые подчеркивают функции Excel 2019 и показывают, как … Tagged Charp, dotnet, webdev, devops.

Этот блог является частью одной из многофункциональных серий, которые подчеркивают функции Excel 2019 и показывают, как создать отчет индекса качества воздуха (AQI) с использованием GCEXCECL API.

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

С выпуском Microsoft Excel 2019 представлена шесть новых функций, упрощая некоторые общие расчеты:

  1. Concat Функция может использоваться для объединения нескольких строковых значений, включая значения в диапазонах ячейки.
  2. Текстура Функция также может использоваться для объединения нескольких строковых значений, включая значения в диапазонах ячейки.   Кроме того, он позволяет определить разделитель и выбрать, следует ли игнорировать пустые ячейки.
  3. IFS Функция может оценить несколько условий для возврата значения, соответствующего первым истинным условиям.
  4. Переключатель Функция может быть использована для оценки одного значения (известное как выражение) в отношении списка пар-результатов пар, чтобы вернуть результат, соответствующий первому подходящему значению.
  5. Maxifs Функция может быть использована для возврата максимального значения между ячейками, указанными данным набором условий или критериев.
  6. Minifs Функция может быть использована для возврата минимального значения между ячейками, указанными данным набором условий или критериев.

Все эти вышеуказанные функции поддерживаются Бесплатные документы для библиотеки Excel , упоминается как Gcexcel Отказ

GCEXCECL — это высокопроизводительное решение электронной таблицы, которое обеспечивает всеобъемлющее API для быстрого создания, манипулирования, преобразованиями и совместно используемыми Microsoft Excel-совместимыми электронными удобствами. Обратитесь к этому быстрому руководству о том, как начать с GCEXCEL.

Давайте посмотрим, как реализовать эти функции в GCExcel для достижения реального сценария.

Используйте корпус: отчет индекса качества воздуха

Индекс качества воздуха (AQI) является индикатором для измерения качества воздуха в определенной области или области. В этом блоге мы создадим отчет AQI, используя вновь введенные функции в GCEXCECE, чтобы отобразить индекс качества воздуха из 10 столичных городов в разных странах и 4 крупных города США.

Источник данных

Данные ниже добавляются в рабочий лист «Данные» в рабочей книге, сохраненной в виде файла Excel, используя GCExcel.

Вот примерный фрагмент кода для добавления вышеуказанных данных в GCEXCECEL:

//Define source data
object[,] sourceData = new object[,] {
{ "Current AQI", "City", "Country"},
{ 31, "Oklahoma City","US"},
{ 5, "New York City","US"},
{ 101, "Philadelphia","US"},
{ 76, "Washington",  "US"},
{ 25, "London",  "United Kingdom"},
{ 148, "New Delhi",  "India"},
{ 35, "Tokyo",  "Japan"},
{ 174, "Beijing",  "China"},
{ 45, "Canberra",  "Australia"},
{ 56, "Rome", "Italy"},
{ 305, "Hanoi", "Vietnam"},
{ 161, "Doha", "Qatar"},
{ 209, "Dhaka","Bangladesh"}
};

//Add new worksheet and name it as 'Data'
var workbook = new GrapeCity.Documents.Excel.Workbook();
IWorksheet worksheet = workbook.Worksheets[0];
worksheet.Name = "Data";

//Add source data
worksheet.Range["A1:C14"].Value = sourceData;

Он будет использоваться в качестве источника данных для создания отчета AQI, как объяснено в разделе ниже.

Aqi доклад

Отчет AQI использует вышеупомянутые данные и реализуют функции над ним, чтобы отобразить следующее:

  • Расположение Столбец Отображает комбинацию колонн «Город» и «Страна» с использованием функций TEXTJOIN/CONCAT.
  • Текущий AQI Столбец, который отображает значения AQI прямо из «текущей колонны AQI».
  • Уровень здоровья. Столбец отображает серьезность проблем со здоровьем, используя функции IFS/Switch.

Помимо вышесказанного, отчет AQI также демонстрирует города США с худшим и лучшим AQI с помощью функций Maxifs и Minifs соответственно. Вот быстрый взгляд на то, как будет выглядеть окончательный отчет AQI:

Цветовая гамма, применяемая к отчету AQI в вышеуказанном изображении, выполняется с помощью другой полезной функции GCEXCECL, известной как условное форматирование. Пожалуйста, обратитесь к Условное форматирование В документации GCECECL для более подробной информации.

Примечание: Один и тот же отчет AQI создан в двух отдельных рабочих таблицах, а именно: «TextJoin & Ifs» и «Concat & Switch», чтобы продемонстрировать использование аналогичных функций, достигающих того же выхода.

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

Создание списка местоположения

Значения столбцов «местоположение» в отчете AQI генерируются с использованием функций TextJoin или Concat.

Функция TextJoin

Функция TextJoin помогает объединить текст из нескольких диапазонов и/или строк. Он также предоставляет аргументы «разделителя» и «INGLOREEPTY»:

Синтаксис: TEXTJOIN (разделитель, INGLOREEMPETY, TEXT1, [TEXT2], …)

Мы применили функцию TextJoin в листе «Textjoin & Ifs» для совместного объединения колонн «City» и «Country» из листа «Данные». Здесь запятая используется в качестве разделителя, и игнорировать пустые ячейки устанавливаются на true.

Вот примерный фрагмент кода для использования функции TextJoin в GCEXCECEL:

//Apply TEXTJOIN function
worksheet1.Range["A4"].Formula = "=TEXTJOIN(\", \", true, Data!B2, Data!C2)";
worksheet1.Range["A5"].Formula = "=TEXTJOIN(\", \", true, Data!B3, Data!C3 )";
worksheet1.Range["A6"].Formula = "=TEXTJOIN(\", \", true, Data!B4, Data!C4 )";
worksheet1.Range["A7"].Formula = "=TEXTJOIN(\", \", true, Data!B5, Data!C5 )";
worksheet1.Range["A8"].Formula = "=TEXTJOIN(\", \", true, Data!B6, Data!C6 )";
worksheet1.Range["A9"].Formula = "=TEXTJOIN(\", \", true, Data!B7, Data!C7 )";
worksheet1.Range["A10"].Formula = "=TEXTJOIN(\", \", true, Data!B8, Data!C8 )";
worksheet1.Range["A11"].Formula = "=TEXTJOIN(\", \", true, Data!B9, Data!C9 )";
worksheet1.Range["A12"].Formula = "=TEXTJOIN(\", \", true, Data!B10, Data!C10 )";
worksheet1.Range["A13"].Formula = "=TEXTJOIN(\", \", true, Data!B11, Data!C11 )";
worksheet1.Range["A14"].Formula = "=TEXTJOIN(\", \", true, Data!B12, Data!C12 )";
worksheet1.Range["A15"].Formula = "=TEXTJOIN(\", \", true, Data!B13, Data!C13 )";
worksheet1.Range["A16"].Formula = "=TEXTJOIN(\", \", true, Data!B14, Data!C14 )";

Вывод выглядит так, как это выглядит после применения функции TextJoin:

Функция CONCAT

Функция CONCAT помогает объединить текст из нескольких диапазонов и/или строк:

Синтаксис: Concat (Text1, [Text2], …)

Мы применили функцию CONCAT в листе «Concat & Switch» для достижения того же вывода, что и функция TextJoin. Как уже упоминалось, функция CONCAT не предоставляет возможности указать разделитель. Следовательно, разделитель также предоставляется в качестве текстового аргумента, который будет объединен как другие текстовые значения.

Вот примерный фрагмент кода для использования функции CONCAT в GCEXCECEL:

//Apply CONCAT function
worksheet2.Range["A4"].Formula = "=CONCAT(Data!B2, \", \", Data!C2)";
worksheet2.Range["A5"].Formula = "=CONCAT(Data!B3,  \", \", Data!C3 )";
worksheet2.Range["A6"].Formula = "=CONCAT(Data!B4, \", \", Data!C4 )";
worksheet2.Range["A7"].Formula = "=CONCAT(Data!B5, \", \", Data!C5 )";
worksheet2.Range["A8"].Formula = "=CONCAT(Data!B6,  \", \",Data!C6 )";
worksheet2.Range["A9"].Formula = "=CONCAT(Data!B7, \", \", Data!C7 )";
worksheet2.Range["A10"].Formula = "=CONCAT(Data!B8, \", \", Data!C8 )";
worksheet2.Range["A11"].Formula = "=CONCAT(Data!B9,  \", \",Data!C9 )";
worksheet2.Range["A12"].Formula = "=CONCAT(Data!B10, \", \", Data!C10 )";
worksheet2.Range["A13"].Formula = "=CONCAT(Data!B11, \", \", Data!C11 )";
worksheet2.Range["A14"].Formula = "=CONCAT(Data!B12, \", \", Data!C12 )";
worksheet2.Range["A15"].Formula = "=CONCAT(Data!B13, \", \", Data!C13 )";
worksheet2.Range["A16"].Formula = "=CONCAT(Data!B14, \", \", Data!C14 )";

Какой из них выбрать?

Textjoin — лучший кандидат, поскольку он обеспечивает большую гибкость и функциональность, чем функция CONCAT. TEXTJOIN предоставляет разделитель и INGLOREEMPTY аргументы как дополнительные варианты. Вот пример, чтобы понять это лучше:

Оценка уровня здоровья.

Значения колонны «Уровень здоровья» в отчете AQI могут быть оценены с использованием функций IFS или Switch.

В приведенной ниже таблице показано, как «уровень здоровья» оценивается на основе того, где стоимость текущего AQI падает в определенные диапазоны. Эти значения диапазона действуют как критерии для условий или выражений, передаваемых IFS и функциями коммутатора.

IFS Функция

Функция IFS помогает проверить, выполнено ли одно или несколько условий и возвращает значение, которое соответствует первому истинному условию:

Синтаксис : Ifs (условие1, truevalue1, [условие 2, truevalue2], …)

Мы применили функцию IFS в листе «TEXTJOIN & IFS» для оценки нескольких условий, которые идентифицируют диапазон, в котором текущее значение AQI падает и возвращает соответствующий результат.

Вот примерный фрагмент кода для использования функции IFS в GCEXCECEL:

//Apply IFS function
worksheet1.Range["C4"].Formula = "=IFS(B4>300, \"Hazardous\", B4>200, \"Very Unhealthy\", B4>150, \"Unhealthy\", B4>100, \"Moderate\", B4>50, \"Satisfactory\", B4<=50, \"Good\")";
worksheet1.Range["C5"].Formula = "=IFS(B5>300, \"Hazardous\", B5>200, \"Very Unhealthy\", B5>150, \"Unhealthy\", B5>100, \"Moderate\", B5>50, \"Satisfactory\", B5<=50, \"Good\")";
worksheet1.Range["C6"].Formula = "=IFS(B6>300, \"Hazardous\", B6>200, \"Very Unhealthy\", B6>150, \"Unhealthy\", B6>100, \"Moderate\", B6>50, \"Satisfactory\", B6<=50, \"Good\")";
worksheet1.Range["C7"].Formula = "=IFS(B7>300, \"Hazardous\", B7>200, \"Very Unhealthy\", B7>150, \"Unhealthy\", B7>100, \"Moderate\", B7>50, \"Satisfactory\", B7<=50, \"Good\")";
worksheet1.Range["C8"].Formula = "=IFS(B8>300, \"Hazardous\", B8>200, \"Very Unhealthy\", B8>150, \"Unhealthy\", B8>100, \"Moderate\", B8>50, \"Satisfactory\", B8<=50, \"Good\")";
worksheet1.Range["C9"].Formula = "=IFS(B9>300, \"Hazardous\", B9>200, \"Very Unhealthy\", B9>150, \"Unhealthy\", B9>100, \"Moderate\", B9>50, \"Satisfactory\", B9<=50, \"Good\")";
worksheet1.Range["C10"].Formula = "=IFS(B10>300, \"Hazardous\", B10>200, \"Very Unhealthy\", B10>150, \"Unhealthy\", B10>100, \"Moderate\", B10>50, \"Satisfactory\", B10<=50, \"Good\")";
worksheet1.Range["C11"].Formula = "=IFS(B11>300, \"Hazardous\", B11>200, \"Very Unhealthy\", B11>150, \"Unhealthy\", B11>100, \"Moderate\", B11>50, \"Satisfactory\", B11<=50, \"Good\")";
worksheet1.Range["C12"].Formula = "=IFS(B12>300, \"Hazardous\", B12>200, \"Very Unhealthy\", B12>150, \"Unhealthy\", B12>100, \"Moderate\", B12>50, \"Satisfactory\", B12<=50, \"Good\")";
worksheet1.Range["C13"].Formula = "=IFS(B13>300, \"Hazardous\", B13>200, \"Very Unhealthy\", B13>150, \"Unhealthy\", B13>100, \"Moderate\", B13>50, \"Satisfactory\", B13<=50, \"Good\")";
worksheet1.Range["C14"].Formula = "=IFS(B14>300, \"Hazardous\", B14>200, \"Very Unhealthy\", B14>150, \"Unhealthy\", B14>100, \"Moderate\", B14>50, \"Satisfactory\", B14<=50, \"Good\")";
worksheet1.Range["C15"].Formula = "=IFS(B15>300, \"Hazardous\", B15>200, \"Very Unhealthy\", B15>150, \"Unhealthy\", B15>100, \"Moderate\", B15>50, \"Satisfactory\", B15<=50, \"Good\")";
worksheet1.Range["C16"].Formula = "=IFS(B16>300, \"Hazardous\", B16>200, \"Very Unhealthy\", B16>150, \"Unhealthy\", B16>100, \"Moderate\", B16>50, \"Satisfactory\", B16<=50, \"Good\")";

Вот данные после применения функции IFS:

Функция переключения

Функция выключателя помогает оценить одно значение (называемое выражением) для списка значений и возвращает результат, соответствующий первому значению соответствия:

Синтаксис: Переключатель (выражение, значение1, результат1, [по умолчанию или значение2, результат2] …)

Мы применили функцию переключателя в листе «Concat & Switch» для достижения того же вывода, что и функция IFS. Как можно наблюдать через фрагмент кода, приведенный ниже, функция выключателя указывает значение выражения как «TRUE».

Кроме того, он оценивает несколько условий для определения диапазона, в котором текущее значение AQI падает и возвращает результат, соответствующий первому условию, которое имеет значение true (поскольку выражение предполагает).

Вот примерный фрагмент кода для использования функции переключателя в GCEXCECEL:

//Apply SWITCH function
worksheet2.Range["C4"].Formula = "=SWITCH(true, B4>300, \"Hazardous\", B4>200, \"Very Unhealthy\", B4>150, \"Unhealthy\", B4>100, \"Moderate\", B4>50, \"Satisfactory\", B4<=50, \"Good\")";
worksheet2.Range["C5"].Formula = "=SWITCH(true, B5>300, \"Hazardous\", B5>200, \"Very Unhealthy\", B5>150, \"Unhealthy\", B5>100, \"Moderate\", B5>50, \"Satisfactory\", B5<=50, \"Good\")";
worksheet2.Range["C6"].Formula = "=SWITCH(true, B6>300, \"Hazardous\", B6>200, \"Very Unhealthy\", B6>150, \"Unhealthy\", B6>100, \"Moderate\", B6>50, \"Satisfactory\", B6<=50, \"Good\")";
worksheet2.Range["C7"].Formula = "=SWITCH(true, B7>300, \"Hazardous\", B7>200, \"Very Unhealthy\", B7>150, \"Unhealthy\", B7>100, \"Moderate\", B7>50, \"Satisfactory\", B7<=50, \"Good\")";
worksheet2.Range["C8"].Formula = "=SWITCH(true, B8>300, \"Hazardous\", B8>200, \"Very Unhealthy\", B8>150, \"Unhealthy\", B8>100, \"Moderate\", B8>50, \"Satisfactory\", B8<=50, \"Good\")";
worksheet2.Range["C9"].Formula = "=SWITCH(true, B9>300, \"Hazardous\", B9>200, \"Very Unhealthy\", B9>150, \"Unhealthy\", B9>100, \"Moderate\", B9>50, \"Satisfactory\", B9<=50, \"Good\")";
worksheet2.Range["C10"].Formula = "=SWITCH(true, B10>300, \"Hazardous\", B10>200, \"Very Unhealthy\", B10>150, \"Unhealthy\", B10>100, \"Moderate\", B10>50, \"Satisfactory\", B10<=50, \"Good\")";
worksheet2.Range["C11"].Formula = "=SWITCH(true, B11>300, \"Hazardous\", B11>200, \"Very Unhealthy\", B11>150, \"Unhealthy\", B11>100, \"Moderate\", B11>50, \"Satisfactory\", B11<=50, \"Good\")";
worksheet2.Range["C12"].Formula = "=SWITCH(true, B12>300, \"Hazardous\", B12>200, \"Very Unhealthy\", B12>150, \"Unhealthy\", B12>100, \"Moderate\", B12>50, \"Satisfactory\", B12<=50, \"Good\")";
worksheet2.Range["C13"].Formula = "=SWITCH(true, B13>300, \"Hazardous\", B13>200, \"Very Unhealthy\", B13>150, \"Unhealthy\", B13>100, \"Moderate\", B13>50, \"Satisfactory\", B13<=50, \"Good\")";
worksheet2.Range["C14"].Formula = "=SWITCH(true,B14>300, \"Hazardous\", B14>200, \"Very Unhealthy\", B14>150, \"Unhealthy\", B14>100, \"Moderate\", B14>50, \"Satisfactory\", B14<=50, \"Good\")";
worksheet2.Range["C15"].Formula = "=SWITCH(true,B15>300, \"Hazardous\", B15>200, \"Very Unhealthy\", B15>150, \"Unhealthy\", B15>100, \"Moderate\", B15>50, \"Satisfactory\", B15<=50, \"Good\")";
worksheet2.Range["C16"].Formula = "=SWITCH(true,B16>300, \"Hazardous\", B16>200, \"Very Unhealthy\", B16>150, \"Unhealthy\", B16>100, \"Moderate\", B16>50, \"Satisfactory\", B16<=50, \"Good\")";

Какой из них выбрать?

Использование функции IFS и Switch обычно зависит от сценария использования, как объяснено и показано ниже:

  • Функция IFS должна использоваться, если пары состояния-значения содержат разные выражения и операторы для каждого условия
  • Функция переключателя должна использоваться, если экспрессия и оператор одинаковы для оценки всех условий. Вы также можете указать значение по умолчанию в функции переключателя («Invalid» в случае приведенного ниже приведенного ниже пример), который возвращается, если в указанных условиях нет совпадения.

Отображение городов с лучшими и худшими AQI

Этот раздел помогает в понимании того, как оценить города с худшим и лучшим AQI, используя Maxifs и Minifs функционирует в «TextJoin IF» и «SCONAT Switch». Эти функции вложены вложенные в списке и индексных функциях для отображения желаемых результатов.

Maxifs и Minifs Функции

Функции Maxifs и Minifs помогают получить максимальные и минимальные значения между ячейками, указанными данным набором условий или критериев:

Синтаксис: Maxifs (max_range, criteria_range1, критерии1, [критерии_ранжен2, критерии2], …)

Синтаксис: Minifs (min_range, criter_range1, критерии1, [criterial_range2, критерии2], …)

Мы применили функции Maxifs и Minifs, чтобы найти максимальные и минимальные значения AQI из листа «Данные», где значение «Страна» является «US».

Вот фрагмент пример кода для использования функции Maxifs и Minifs в GCExcel. Как вы можете наблюдать, мы также использовали соответствие и индексные функции, чтобы узнать стоимость городов с худшим и лучшим AQI:

//Apply MAXIFS and MINIFS functions
worksheet1.Range["B20"].Formula = "=INDEX(Data!B2:B14, MATCH(MAXIFS(Data!A2:A14,Data!C2:C14,\"US\"),Data!A2:A14,0))";
worksheet1.Range["B21"].Formula = "=INDEX(Data!B2:B14, MATCH(MINIFS(Data!A2:A14,Data!C2:C14,\"US\"),Data!A2:A14,0))";

Вывод выглядит так, как это выглядит после нанесения Maxifs и Minifs, наряду с другими вложенными функциями:

Окончательный отчет AQI

//Save workbook
workbook.Save("AQI_Report.xlsx");

Так выглядит окончательный отчет AQI в обоих листах:

  • Рабочий лист «TEXTJOIN & IFS»: где используются TEXTJOIN, IFS и NESTED MAXIFS и MINIFS
  • Рабочий лист «Concat & Switch»: где используются Concat, Switch и Nested Maxifs и Minifs

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

В двух частях этого блога мы будем покрывать графики, представленные в Excel 2019 и как их использовать в GCEXCECEL.

Оригинал: «https://dev.to/grapecity/create-excel-files-with-excel-2016-features-in-c-net-part-one-375e»