Excel файлы повсеместно применяются для хранения данных в виде таблиц, объем данных и хранение однотипных данных в разных файлах excel усложняют обработку их при помощи персонального компьютера.
Базы данных (БД) используются для хранения различной информации. Для извлечения информации из них используется SQL (structured query language — язык структурированных запросов). Для редактирования БД используют системы управления базами данных, через которые возможно манипулировать данными. Для рядового пользователя разбираться в синтаксисе SQL является трудозатратным процессом. В практике работы с БД встречаются однотипные запросы с изменением даты или количественных измерений.
Оптимальным вариантом для решения задачи обработки данных Excel файлов и предоставления данных по определенным критериям является создание приложения, которое будет обрабатывать заданные критерии и выдавать необходимые данные на стороне сервера. Для упрощения взаимодействия пользователя с создаваемым приложением необходимо обеспечить взаимодействие пользователя с приложением при помощи удобного интерфейса и легкого доступа.
Работа с базой данных в табличном процессоре Microsoft Excel
В качестве интерфейса самым простым и понятным интерфейсом является web-интерфейс. Одним из распространённых фреймворков является “ADO.NET MVC”. Пользователь через браузер отправляет запрос, если необходима информация из БД, модель передает их в представление:
Для обработки excel файлов необходимо установить ExcelDataReader и ExcelDataReader.DataSet.
Excel файл выглядит следующим образом:
Модель
«M» в MVC обозначает модель. Модель для данных из Excel в простом виде будет записана следующим образом:
public class Worker < public int Id < get; set; >public string FullName < get; set; >public string Passport < get; set; >public string Address < get; set; >public string Birthday < get; set; >public string Sex < get; set; >public string Post < get; set; >public int Аmount_of_children < get; set; >>
Контроллер
Буква «C» в MVC означает контроллер. Контроллер собирает данные и передает их в представление. Создадим контроллер для передачи файла:
public class WorkerController : Controller < private readonly WorkerDbContext dbContext; public WorkerController() < dbContext = new WorkerDbContext(); >// GET: Workers public ActionResult Index() < return View(); >[HttpPost] public async Task ImportFile() < return View(«Index»); >>
Добавим в контроллер метод для обработки файла в данном примере будем использовать csv формат:
private List parsingFile(Stream stream) < var wrkList = new List(); try < using (var exRead = ExcelReaderFactory.CreateCsvReader(stream)) < var dSet = exRead.AsDataSet(new ExcelDataSetConfiguration < ConfigureDataTable = _ =>new ExcelDataTableConfiguration < UseHeaderRow = true >>); var table = dSet.Tables[0]; foreach (DataRow dataRow in table.Rows) < if (dataRow.ItemArray.All(x =>string.IsNullOrEmpty(x?.ToString()))) continue; wrkList.Add(new Worker() < номер»].ToString()), FullName = dataRow[«ФИО»].ToString(), Passport = dataRow[«Паспорт»].ToString(), Address = dataRow[«Адрес»].ToString(), Birthday = dataRow[«Дата рождения»].ToString(), Sex = dataRow[«Пол»].ToString(), Аmount_of_children = null, >); > > > catch (Exception) < throw; >return wrkList; >
Представление
Как создать график работы в excel
Буква «V» в MVC означает представление. Представление может быть самостоятельным, или обеспечивать различные способы представления данных, которые получены из модели. Представление может быть макетом и заполняться данными. Представления могут быть различными и контроллер выбирает, какой вид подходит для текущей ситуации.
Напишем представление для загрузки файла:
В результате представления будут выглядеть следующим образом.
Форма загрузки файла:
Все данные вымышленные.
В результате мы можем произвести фильтрацию по отделу на определенную дату, что поможет провести аналитику уже отфильтрованной информации, не используя вычислительные ресурсы персонального компьютера и обойдя поиск и совмещение этой информации из различных файлов.
В результате выбранных выше критериев мы получили уменьшенный объем информации:
Также в результате работы были сгруппированы сотрудники по отделам, что также облегчает просмотр данных объединённых одним критерием:
В результате создания web-приложения были выделены следующие преимущества:
- возможность обрабатывать большие файлы, не используя вычислительные ресурсы персонального компьютера
- простота создания подключения к БД
- использование группировки данных и вывод данных по заданным критериям из excel файлов по определенным столбцам
В дальнейшем данную технологию можно применять для получения файлов других форматов и последующей обработки данных при помощи машинного обучения и др.
Источник: newtechaudit.ru
Работа с Excel в C#
Всем доброго времени суток. На связи Алексей Гулынин. В данной статье я бы хотел рассказать о том, как работать с Microsoft Excel, используя язык C#. Для этого в проект необходимо добавить ссылку на библиотеку Microsoft Excel Object Library . Версия библиотеки будет зависеть от того, какая версия офиса у вас установлена. Давайте реализуем следующий пример: необходимо в ячейки первой строки записать числа от 1 до 10, на второй строке в первой ячейке посчитать их сумму, используя формулу в Excel . Также у данной ячейки необходимо выделить границы.
Также в данном примере я хочу показать, как строить круговую диаграмму в Excel на C# и заполнять её данными.
Сразу приведу код:
using Excel = Microsoft.Office.Interop.Excel; namespace TestProjectExcel < class Program < static void Main(string[] args) < // Создаём экземпляр нашего приложения Excel.Application excelApp = new Excel.Application(); // Создаём экземпляр рабочий книги Excel Excel.Workbook workBook; // Создаём экземпляр листа Excel Excel.Worksheet workSheet; workBook = excelApp.Workbooks.Add(); workSheet = (Excel.Worksheet)workBook.Worksheets.get_Item(1); // Заполняем первую строку числами от 1 до 10 for (int j = 1; j // Вычисляем сумму этих чисел Excel.Range rng = workSheet.Range[«A2»]; rng.Formula = «=SUM(A1:L1)»; rng.FormulaHidden = false; // Выделяем границы у этой ячейки Excel.Borders border = rng.Borders; border.LineStyle = Excel.XlLineStyle.xlContinuous; // Строим круговую диаграмму Excel.ChartObjects chartObjs = (Excel.ChartObjects)workSheet.ChartObjects(); Excel.ChartObject chartObj = chartObjs.Add(5, 50, 300, 300); Excel.Chart xlChart = chartObj.Chart; Excel.Range rng2 = workSheet.Range[«A1:L1»]; // Устанавливаем тип диаграммы xlChart.ChartType = Excel.XlChartType.xlPie; // Устанавливаем источник данных (значения от 1 до 10) xlChart.SetSourceData(rng2); // Открываем созданный excel-файл excelApp.Visible = true; excelApp.UserControl = true; > > >
В данной статье вы немного познакомились с тем, как работать с Excel, используя C#.
В статьях, посвященных вставке текста в Word, используя закладки (Bookmarks) мы познакомились с технологиями OpenXML и GemBox . С файлами Excel эти технологии также успешно работают, а OpenXML является предпочтительным вариантом работы, так как бесплатен и не требует установки Microsoft Excel. Возможно в следующих статьях рассмотрим пару примеров.
На связи был Алексей Гулынин, оставляйте свои комментарии, увидимся в следующих статьях.
Источник: alekseygulynin.ru
Работа с Excel с помощью C# (Microsoft.Office.Interop.Excel)
Привожу фрагменты кода, которые искал когда-то сам для работы с Excel документами.
Наработки очень пригодились в работе для формирования отчетности.
Прежде всего нужно подключить библиотеку Microsoft.Office.Interop.Excel.
Далее создаем псевдоним для работы с 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 «, 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;
Цвет рамки можно установить так:
Выравнивания в диапазоне задаются так:
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(«=СУММ(«, 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 — Добавить вертикальный разрыв
Сохраняем документ
Как открыть существующий документ Excel
Комментарии
При работе с Excel с помощью C# большую помощь может оказать редактор Visual Basic, встроенный в Excel.
Для этого в настройках ленты надо добавить пункт «Разработчик». Далее начинаем запись макроса, производим действия и останавливаем запись.
Далее заходим в редактор Visual Basic и смотрим код, который туда записался:
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# коде.