Создаем сводные таблицы на C# в Excel

Сегодня программируем сводные таблицы на C#. Для этого надо будет иметь заготовленную таблицу с данными (обычную).

Заготовленная таблица будет иметь данные о телефонных звонках.

Пример сводной таблицы:

Сводные таблицы на C#

Открываем документ и создаем диапазоны для сводной таблицы

Откроем Excel документ с таблицей:

ex.Workbooks.Open("Excel.xlsx",
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);

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

Excel.Range pivotDestination = sheetPivot.get_Range("A1", "A1");

Объявляем диапазон сводной таблицы:

Excel.Range all = sheetPivot.get_Range("A1", "D1000");

Пишем код создания сводной таблицы

Код для создания сводной таблицы из обычной таблицы с данными имеет такой вид:

workBook.PivotTableWizard(
  Excel.XlPivotTableSourceType.xlDatabase,
all,                        //Таблица, на основе которой строим сводную
pivotDestination,              //Где сохранять
"NumberA",                        //Название таблицы
true,
true,
true,
true,
Type.Missing,
Type.Missing,
false,
false,
Excel.XlOrder.xlDownThenOver,
0,
Type.Missing,
Type.Missing
);

Распределяем поля сводной таблицы

Далее надо определить, какие поля будут отвечать за значения, за столбцы и строки.

Для этого создаем экземпляры полей на основе заголовков столбцов таблицы:

//Создаем поля таблицы
Excel.PivotTable pivotTable = (Excel.PivotTable)sheetPivot.PivotTables("Number");
Excel.PivotField Y = ((Excel.PivotField)pivotTable.PivotFields("Y"));
Excel.PivotField M = ((Excel.PivotField)pivotTable.PivotFields("M"));
Excel.PivotField Src_country = ((Excel.PivotField)pivotTable.PivotFields("Country"));
Excel.PivotField Operator_Name = ((Excel.PivotField)pivotTable.PivotFields("Operator"));
Excel.PivotField Minutes = ((Excel.PivotField)pivotTable.PivotFields("Minutes"));

Распределяем поля:

//Расставляем строки, столбцы и данные
Y.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
M.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
Src_country.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Operator_Name.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
Minutes.Orientation = Excel.XlPivotFieldOrientation.xlDataField;
//Функция, применяемая к данным - есть еще количество, MAX, MIN и т.д.
Minutes.Function = Excel.XlConsolidationFunction.xlSum;

Создаем сводную таблицу без исходной таблицы с данными

Описываю, как создать сводную таблицу на основе SQL-запроса к БД.

Для этого понадобится библиотека ADODB (adodb.dll можно скачать в интернете).

Подключим adodb.dll к проекту:

using ADODB;

Объявим строку подключения (драйвер SQLOLEDB.1):

Provider=SQLOLEDB.1;Password=123456;Persist Security Info=True;User ID=sqlserver;Initial Catalog=DBName;Data Source=serverNameOrIp

Далее пишем код для работы со сводной таблицей:

//Создаем подключение и открываем его
ADODB.Connection sql = new Connection();
sql.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
sql.ConnectionString = conString;
sql.Open();

//Объявляем команду. Здесь функция getQuery() возвращает SQL-запрос
ADODB.Command cmd = new Command();
cmd.CommandType = CommandTypeEnum.adCmdText;
cmd.CommandText = getQuery();
cmd.CommandTimeout = 5000;
cmd.ActiveConnection = sql;

//Объявляем Recordset и выполняем запрос, сохраняя в него данные
ADODB.Recordset rs = new Recordset();
rs.Open(cmd, Type.Missing, ADODB.CursorTypeEnum.adOpenForwardOnly, ADODB.LockTypeEnum.adLockReadOnly, 1);
//тут хранится результат запроса

Excel.PivotCache pc = wb.PivotCaches().Add(
Excel.XlPivotTableSourceType.xlExternal
, Type.Missing);

pc.Recordset = rs;
//Ссылка на все сводные таблицы на листе
Excel.PivotTables pts = (Excel.PivotTables)sheet.GetType().InvokeMember("PivotTables", System.Reflection.BindingFlags.GetProperty, null, sheet, null);
//Создаем новую сводную таблицу
Excel.PivotTable pt = pts.Add(pc, sheet.get_Range("A3", Type.Missing), "NumA", Type.Missing, Excel.XlPivotTableVersionList.xlPivotTableVersionCurrent);

//Распределяю поля и настраиваю форматы полей
((Excel.PivotField)pt.PivotFields("Year")).Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
((Excel.PivotField)pt.PivotFields("Month")).Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
((Excel.PivotField)pt.PivotFields("Country")).Orientation = Excel.XlPivotFieldOrientation.xlRowField;
((Excel.PivotField)pt.PivotFields("Operator")).Orientation = Excel.XlPivotFieldOrientation.xlRowField;
((Excel.PivotField)pt.PivotFields("Minutes")).Orientation = Excel.XlPivotFieldOrientation.xlDataField;
((Excel.PivotField)pt.PivotFields("Сумма по полю Minutes")).Function = Excel.XlConsolidationFunction.xlSum;
((Excel.PivotField)pt.PivotFields("Сумма по полю Minutes")).NumberFormat = "# ##0";
((Excel.PivotField)pt.PivotFields("Operator")).ShowDetail=false;
((Excel.PivotField)pt.PivotFields("Country")).ShowDetail = false;

pt.TableStyle2 = "PivotStyleMedium13";     //Стиль сводной таблицы. Взял через VBA

При работе со сводными таблицами так же окажет помощь встроенный в Excel редактор VBA, о котором речь шла в теме Работа с Excel с помощью C# (Microsoft.Office.Interop.Excel).

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *