PIX роботы
Глава 1
Задача 0.
Как открыть новое приложение Эксель и потом с ним работать? Подключаемся к книге через библиотеку Microsoft.Office.Interop Microsoft.Office.Interop.Excel.Application oExcel = new() {Visible = true, DisplayAlerts = false}; //полный список параметров запуска смотри на сайте МелкоМягких
теперь сквозь переменную oExcel - возможно управлять новым приложением Эксель
------------------------------------------------------------------------------------------------------------------------------------------------------
Задача 1. Есть книга Excel - надо удались оттуда все листы кроме листа с определённым именем. Примечание 1 - метод не сработает если на листах защита. Примечание 2 - метод уронит скрипт если листа с нужным именем нет. Решение. Подключаемся к книге через библиотеку Microsoft.Office.Interop Получаем переменную типа wbP (work book [to] processing) Microsoft.Office.Interop.Excel.Workbook wbP = oExcel.Workbooks["ИмяКнижки"]; //где oExcel - это наше приложение Эксель в формате переменной типа Microsoft.Office.Interop.Excel.Application
foreach (Microsoft.Office.Interop.Excel.Worksheet shP in wbP.Worksheets) //Запускаем цикл форыч по всем листам {
if (shP.Name != "DATA") //если лист называется не так как надо то...
{
shP.Delete(); //удаляем его
} }
------------------------------------------------------------------------------------------------------------------------------------------------------
Задача 2. Есть книга Excel - надо удались оттуда все VBA компоненты. Примечание - метод не сработает если стоит защита на VBA компонентах, и если СБ зарежут такие права, а они могут.
Решение 1. Подключаемся к книге через библиотеку Microsoft.Office.Interop Получаем переменную типа wbP, см задача 1
int iLC; //(тип integer Lines Count) объявляем целочисленную переменную для хранения промежуточных данных foreach (Microsoft.Vbe.Interop.VBComponent oVB in wbP.VBProject.VBComponents) //Запускаем цикл форыч по всем компонентам VBA. {
if (oVB.Type == (Microsoft.Vbe.Interop.vbext_ComponentType) 100) //С этим типом компонентов надо поступать по особому
{
iLC = oVB.CodeModule.CountOfLines;
oVB.CodeModule.DeleteLines( 1, iLC);
}
else //а с остальными штатно
{oVB.Collection.Remove(oVB);} } Решение 2. Не зависит от воли внешних сил, но требует дополнительного дискового пространства, и основательно медленнее. Подключаемся к книге через библиотеку Microsoft.Office.Interop Получаем переменную типа wbP, см задача 1
oExcel.DisplayAlerts = false; //отключаем оповещения string sName = s_TempFolder + wbP.Name.Replace(".староерасширение", ".xlsx"); //(тип string Name) объявляем строковую переменную для хранения промежуточных данных, присваиваем ей значение = адрес временной папки + имя книги, но заменяем старое расширение (позволяющее хранить VBA объекты) на формат запрещающий такое wbP.SaveAs (sName, 51); //сохраняем с новым именем, 51= xlOpenXMLWorkbook, то есть .XLSX wbP.Close(); //закрываем книгу, и т.о. уничтожаем все VBA объекты что хранились в самой книжке wbP = oExcel.Workbooks.Open(sName); //заново открываем книжку, теперь она чиста как слеза младенца oExcel.DisplayAlerts = true; //включаем оповещения
------------------------------------------------------------------------------------------------------------------------------------------------------
Задача 3. Из листа Excel выделить в отдельную книгу данные соответствующие определённому критерию.
Решение 1 - штатными средствами Ecxel На данном этапе мы уже подключены к переменной wbP (см задача 1)
//подключаемся к нужному листу Microsoft.Office.Interop.Excel.Worksheet wsDATA = (Microsoft.Office.Interop.Excel.Worksheet) wbP.Sheets["DATA"];
//активируем его, необходимо для корректной работы метода Copy wsDATA.Activate();
//открываем скрытые элементы листа wsDATA.Outline.ShowLevels(RowLevels: 0, ColumnLevels: 2); wsDATA.AutoFilterMode = false;
/*получаем количество строк в листе, по первой колонке и запоминаем в переменную iTH, System.Int64 =long*/ System.Int64 iTH = ((Microsoft.Office.Interop.Excel.Range) wsDATA.Cells[wsDATA.Rows.Count, 1])
.End[(Microsoft.Office.Interop.Excel.XlDirection)(-4162)].Row; //-4162 = xlUp, то есть направление вверх
//filtration ((Microsoft.Office.Interop.Excel.Range) wsDATA.Range["A2:BO" + iTH]).AutoFilter(Field: 56, Criteria1: "<>ПлохойКритерий");
((Microsoft.Office.Interop.Excel.Range) wsDATA.Range["A2:BO" + iTH]).AutoFilter( Field: 8 /*по столбцу 8 = H*/ , Criteria1: "=НужныйКритерий1*" *- , Operator: (Microsoft.Office.Interop.Excel.XlAutoFilterOperator) 2 /*2 = xlOr, то есть или */ , Criteria2: "=НужныйКритерий2*");
//если есть нужные данные (применяем тот же метод что и в получение высоты таблицы, однако обращаемся к колонке не по номеру а по имени) if (
(((Microsoft.Office.Interop.Excel.Range) wsDATA.AutoFilter.Range.Columns["A"])
.SpecialCells((Microsoft.Office.Interop.Excel.XlCellType)12).Cells.Count - 1)>0 ) /*12 = xlCellTypeVisible это значит берём только видимые ячейки */ { Microsoft.Office.Interop.Excel.Workbook wbTemp =
((Microsoft.Office.Interop.Excel.Workbooks) oExcel.Workbooks).Add(); //добавляем книжку - реципиента
Microsoft.Office.Interop.Excel.Worksheet wsTemp =
(Microsoft.Office.Interop.Excel.Worksheet) wbTemp.Sheets[1]; wsTemp.Name = "Data"; /*подключаемся к первому листу и называем его Data*/
wsDATA.Range["A2:BP" + iTH].Copy(); //копируем данные с учётом фильтра
//активируем нужное место для переноса данных wbTemp.Activate(); wsTemp.Activate(); wsTemp.Range["A1"].Select();
//вставляем данные wsTemp.Range["A1"] .PasteSpecial( Paste: (Microsoft.Office.Interop.Excel.XlPasteType)(-4104) /*xlPasteAll - переносим всё*/ , Operation: (Microsoft.Office.Interop.Excel.XlPasteSpecialOperation)(-4142) /*xlPasteSpecialOperationNone - без пересчёта*/ , SkipBlanks: false, Transpose: false); //запоминаем имя нового файла в спецпеременную string sBf = "ПутьИИмяФайла.xlsb"; System.IO.File.Delete(sBf); //если есть старая версия - удаляем wbTemp.SaveAs(sBf, 50); //сохраняем 50 = .XLSB wbTemp.Close(false); //закрываем книгу реципиента }
------------------------------------------------------------------------------------------------------------------------------------------------------
Задача 4.
Необходимо получить количество строк в определённом приложении Excel, определённой книге, определённом листе по определённой колонке
Решение через библиотеку Microsoft.Office.Interop
//объявляем константу хлюп, она обозначает - движение по листу Эксель - вверх
Microsoft.Office.Interop.Excel.XlDirection i_xlUp = (Microsoft.Office.Interop.Excel.XlDirection)(-4162);
//объявляем функцию с именем TbHght и типом long (int не годится, в него не влезет количество строк современной //Эксельки) //параметры: apXl - нужное приложение эксель, Clm - номер (не имя) колонки, Sht - лист для измерения, WB - рабочая //книга для измерения, три нижних параметра можно опустить, тогда будет мерять по первой колонке, и по активным //книге/листу //как подключатся к книгам и листам смотрите в задачах выше
long TbHght(Microsoft.Office.Interop.Excel.Application apXl , long Clm = 1, object Sht = null, object WB = null) {
Microsoft.Office.Interop.Excel.Workbook wbP; //processed workbook
Microsoft.Office.Interop.Excel.Worksheet shP; //processed worksheet
long iCl; //processed column number
//get processed book
if (WB == null) {wbP = (Microsoft.Office.Interop.Excel.Workbook)apXl.ActiveWorkbook;}
else {wbP = (Microsoft.Office.Interop.Excel.Workbook)WB;}
//get processed sheet
if (Sht == null) {shP = (Microsoft.Office.Interop.Excel.Worksheet)apXl.ActiveSheet;}
else {shP = (Microsoft.Office.Interop.Excel.Worksheet)Sht;}
//get number of processed column
iCl = Clm;
return( ((Microsoft.Office.Interop.Excel.Range) shP.Cells[shP.Rows.Count, iCl]).End[i_xlUp].Row ); } Кстати функцию можно обернуть в скрипт и пользоваться ей из любых участков проекта PIX
------------------------------------------------------------------------------------------------------------------------------------------------------
Задача 5.
Разместить на листе Эксель формулу
Решение через библиотеку Microsoft.Office.Interop
//получаем высоту таблицы через функцию в задаче 4 long iTH = TbHght(oExcel, 1, shP, wbP); //подключаемся к диапазону, если надо взаимодействовать с диапазоном лишь однажды, переменную можно не //объявлять, достаточно применять действия к правой части, то есть //((Microsoft.Office.Interop.Excel.Range) shP.Range[$"R2:R{iTH}"]).ДелайСНейЧтоНадо.....; Microsoft.Office.Interop.Excel.Range rnP = ((Microsoft.Office.Interop.Excel.Range) shP.Range[$"R2:R{iTH}"]); //присваиваем диапазону значение, которое является формулой //конкретно тут - ВПР вложенный в ЕСЛИОШИБКА rnP.Value = "=IFERROR(VLOOKUP(RC[-17],[10_Книжка.xlsb]Листик!C1:C4,4,0),\"нет_данных\")"; //если нужен результат формулы, то меняем значение на значение, и оно магическим образом преобразуется rnP.Value = rnP.Value;
Спасибо за чтение
Вернуться к книге