Работа с Excel с помощью C# (Microsoft.Office.Interop.Excel)

Оставляю заметку по работе с Excel с помощью C#.

Привожу фрагменты кода, которые искал когда-то сам для работы с Excel документами.

Наработки очень пригодились в работе для формирования отчетности.

Прежде всего нужно подключить библиотеку Microsoft.Office.Interop.Excel.

Подключение Microsoft.Office.Interop.Excel
Visual Studio здесь довольно старой версии. Если у вас версия новая, отличаться будет только вид окна.

Далее создаем псевдоним для работы с Excel:

using Excel = Microsoft.Office.Interop.Excel;

//Объявляем приложение
Excel.Application ex = new Microsoft.Office.Interop.Excel.Application();

//Отобразить Excel
ex.Visible = true;

//Количество листов в рабочей книге
ex.SheetsInNewWorkbook = 2;

//Добавить рабочую книгу
Excel.Workbook workBook = ex.Workbooks.Add(Type.Missing);

//Отключить отображение окон с сообщениями
ex.DisplayAlerts = false;                                       

//Получаем первый лист документа (счет начинается с 1)
Excel.Worksheet sheet = (Excel.Worksheet)ex.Worksheets.get_Item(1);

//Название листа (вкладки снизу)
sheet.Name = "Отчет за 13.12.2017";

//Пример заполнения ячеек
for (int i = 1; i <= 9; i++)
{
  for (int j = 1; j < 9; j++)
  sheet.Cells[i, j] = String.Format("Boom {0} {1}", i, j);
}

//Захватываем диапазон ячеек
Excel.Range range1 = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[9, 9]);

//Шрифт для диапазона
range1.Cells.Font.Name = "Tahoma";
//Размер шрифта для диапазона
range1.Cells.Font.Size = 10;

//Захватываем другой диапазон ячеек
Excel.Range range2 = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[9, 2]);
range2.Cells.Font.Name = "Times New Roman";

//Задаем цвет этого диапазона. Необходимо подключить System.Drawing
range2.Cells.Font.Color = ColorTranslator.ToOle(Color.Green);
//Фоновый цвет
range2.Interior.Color = ColorTranslator.ToOle(Color.FromArgb(0xFF, 0xFF, 0xCC));

Расстановка рамок.

Расставляем рамки со всех сторон:

range2.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).LineStyle = Excel.XlLineStyle.xlContinuous;
range2.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).LineStyle = Excel.XlLineStyle.xlContinuous;
range2.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).LineStyle = Excel.XlLineStyle.xlContinuous;
range2.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).LineStyle = Excel.XlLineStyle.xlContinuous;
range2.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).LineStyle = Excel.XlLineStyle.xlContinuous;

Цвет рамки можно установить так:

range2.Borders.Color = ColorTranslator.ToOle(Color.Red);

Выравнивания в диапазоне задаются так:

rangeDate.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;
rangeDate.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

Формулы

Определим задачу: получить сумму диапазона ячеек A4:A10.

Для начала снова получим диапазон ячеек:

Excel.Range formulaRange = sheet.get_Range(sheet.Cells[4, 1], sheet.Cells[9, 1]);

Далее получим диапазон вида A4:A10 по адресу ячейки ( [4,1]; [9;1] ) описанному выше:

string adder = formulaRange.get_Address(1, 1, Excel.XlReferenceStyle.xlA1, Type.Missing, Type.Missing);

Теперь в переменной adder у нас хранится строковое значение диапазона ( [4,1]; [9;1] ), то есть A4:A10.

Вычисляем формулу:

//Одна ячейка как диапазон
Excel.Range r = sheet.Cells[10, 1] as Excel.Range;
//Оформления
r.Font.Name = "Times New Roman";
r.Font.Bold = true;
r.Font.Color = ColorTranslator.ToOle(Color.Blue);
//Задаем формулу суммы
r.Formula = String.Format("=СУММ({0}", adder);

Выделение ячейки или диапазона ячеек

Так же можно выделить ячейку или диапазон, как если бы мы выделили их мышкой:

sheet.get_Range("J3", "J8").Activate();
//или
sheet.get_Range("J3", "J8").Select();
//Можно вписать одну и ту же ячейку, тогда будет выделена одна ячейка.
sheet.get_Range("J3", "J3").Activate();
sheet.get_Range("J3", "J3").Select();

Авто ширина и авто высота

Чтобы настроить авто ширину и высоту для диапазона, используем такие команды:

range.EntireColumn.AutoFit(); 
range.EntireRow.AutoFit();

Получаем значения из ячеек

Чтобы получить значение из ячейки, используем такой код:

//Получение одной ячейки как ранга
Excel.Range forYach = sheet.Cells[ob + 1, 1] as Excel.Range;
//Получаем значение из ячейки и преобразуем в строку
string yach = forYach.Value2.ToString();

Добавляем лист в рабочую книгу

Чтобы добавить лист и дать ему заголовок, используем следующее:

var sh = workBook.Sheets;
Excel.Worksheet sheetPivot = (Excel.Worksheet)sh.Add(Type.Missing, sh[1], Type.Missing, Type.Missing);
sheetPivot.Name = "Сводная таблица";

Добавление разрыва страницы

//Ячейка, с которой будет разрыв
Excel.Range razr = sheet.Cells[n, m] as Excel.Range;
//Добавить горизонтальный разрыв (sheet - текущий лист)
sheet.HPageBreaks.Add(razr); 
//VPageBreaks - Добавить вертикальный разрыв

Сохраняем документ

ex.Application.ActiveWorkbook.SaveAs("doc.xlsx", Type.Missing,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange,
  Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

Как открыть существующий документ Excel

ex.Workbooks.Open(@"C:\Users\Myuser\Documents\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 с помощью C# большую помощь может оказать редактор Visual Basic, встроенный в Excel.

Для этого в настройках ленты надо добавить пункт «Разработчик». Далее начинаем запись макроса, производим действия и останавливаем запись.

Далее заходим в редактор Visual Basic и смотрим код, который туда записался:

Vusial Basic (VBA)

Например:

Sub Макрос1()
'
' Макрос1 Макрос
'

'
    Range("E88").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$118"), , xlYes).Name = _
        "Таблица1"
    Range("A1:F118").Select
    ActiveSheet.ListObjects("Таблица1").TableStyle = "TableStyleLight9"
    Range("E18").Select
    ActiveWindow.SmallScroll Down:=84
End Sub

В данном макросе записаны все действия, которые мы выполнили во время его записи. Эти методы и свойства можно использовать в C# коде.

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

//Складываем значения предыдущих 12 ячеек слева
rang.Formula = "=СУММ(RC[-12]:RC[-1])";

Так же во время работы может возникнуть ошибка: метод завершен неверно. Это может означать, что не выбран лист, с которым идет работа.

Чтобы выбрать лист, выполните sheetData.Select(Type.Missing); где sheetData это нужный лист.

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

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