Программирование PC C#: работа с файлами Excel Mon, November 11 2024  

Поделиться

Нашли опечатку?

Пожалуйста, сообщите об этом - просто выделите ошибочное слово или фразу и нажмите Shift Enter.


C#: работа с файлами Excel Печать
Добавил(а) microsin   

В этой статье приведен краткий обзор нескольких способов работы с файлами Excel - создание, чтение, запись.

[ExcelLibrary]

Open source проект ExcelLibrary предназначен как традиционное решение на платформе .NET [2], пригодное для создания, чтения и изменения файлов Excel без использования механимов COM или соединения с базой данных OLEDB.

В настоящее время поддерживается формат *.xls (BIFF8). В будущем может быть добавлен формат *.xlsx (Excel 2007).

Пример кода с созданием таблицы Excel вручную:

//Создание нового файла .xls:
string file = "C:\\newdoc.xls";
Workbook workbook = new Workbook();
Worksheet worksheet = new Worksheet("First Sheet");
worksheet.Cells[0, 1] = new Cell((short)1);
worksheet.Cells[2, 0] = new Cell(9999999);
worksheet.Cells[3, 3] = new Cell((decimal)3.45);
worksheet.Cells[2, 2] = new Cell("Text string");
worksheet.Cells[2, 4] = new Cell("Second string");
worksheet.Cells[4, 0] = new Cell(32764.5, "#,##0.00");
worksheet.Cells[5, 1] = new Cell(DateTime.Now, @"YYYY-MM-DD");
worksheet.Cells.ColumnWidth[0, 1] = 3000;
workbook.Worksheets.Add(worksheet);
workbook.Save(file);
 
// Открытие файла книги xls:
Workbook book = Workbook.Load(file);
Worksheet sheet = book.Worksheets[0];
 
// Итерация по ячейкам листа:
foreach (Pair, Cell > cell in sheet.Cells)
{
   dgvCells[cell.Left.Right, cell.Left.Left].Value = cell.Right.Value;
}
 
// Итерация по строкам с использованием индекса:
for (int rowIndex = sheet.Cells.FirstRowIndex;
     rowIndex < = sheet.Cells.LastRowIndex;
     rowIndex++)
{
   Row row = sheet.Cells.GetRow(rowIndex);
   for (int colIndex = row.FirstColIndex;
        colIndex < = row.LastColIndex;
        colIndex++)
   {
      Cell cell = row.GetCell(colIndex);
   }
}

Пример заполнения листа Excel содержимым базы данных:

// Создание набора данных (DataSet) и таблицы данных (DataTable):
DataSet ds = new DataSet("New_DataSet");
DataTable dt = new DataTable("New_DataTable");
 
// Установка локали для них:
ds.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
dt.Locale = System.Threading.Thread.CurrentThread.CurrentCulture;
 
// Подключение к базе данных (в этом примере база OleDB)
OleDbConnection con = new OleDbConnection(dbConnectionString);
con.Open();
 
// Создание таблицы и заполнение её данными из базы:
string sql = "SELECT Whatever FROM MyDBTable;";
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adptr = new OleDbDataAdapter();
 
adptr.SelectCommand = cmd;
adptr.Fill(dt);
con.Close();
 
// Добавление таблицы к DataSet:
ds.Tables.Add(dt);

Простой пример создания книги из существующего файла Excel:

ExcelLibrary.DataSetHelper.CreateWorkbook("MyExcelFile.xls", ds);

[EPPlus]

EPPlus это библиотека .NET, которая читает и записывает файлы Excel в формате Office Open XML (xlsx). У EPPlus нет никаких зависимостей, кроме как от самой системы .NET. На сегодняшний день вероятно это самое лучшее решение для работы с файлами Excel на C#.

EPPlus поддерживает VBA, диапазоны ячеек, стили ячеек (Border, Color, Fill, Font, Number, Alignments), проверку данных, условное форматирование, вычисление формул, вставку диаграмм, картинок, фигур, комментарий. Можно создавать сводные таблицы (Pivot tables) и обычные таблицы, с защитой, шифрованием.

Проект EPPlus с лицензией LGPL [3] больше не развивается (поддержка ограничивается только постами на сайте Stack Overflow). Существует библиотека EPPlus с коммерческой лицензией.

Адреса ячеек, числовые форматы и формулы не чувствительны к представлению данных, зависящему от языка (culture-insensitive). Это означает, что при написании кода все может выглядеть немного иначе. Используется способ хранения данных OOXML и последующего перевода в вашу культуру при открытии книги в Excel.

Элементы адреса отделяются друг от друга запятой, например:

worksheet.Cells["A1:C1,C3"].Style.Font.Bold = true;

Форматы числе используют десятичную точку для дробной части (.) и запятую (,) для выделения тысяч. Пример:

worksheet.Cells["B2:B3"].Style.NumberFormat.Format = "#,##0.00";

В формулах для разделения параметров используется запятая (,):

worksheet.Cells["C11"].Formula="SUBTOTAL(9,\"C1:C10\")";

ExcelPackage. Первое, что нужно сделать, это создать экземпляр класса ExcelPackage. Для этого директивой using надо добавить пространство имен OfficeOpenXml (это верхнее пространство имен в EPPlus):

using OfficeOpenXml;

Теперь можно ссылаться на класс Excelpackage непосредственно из ваших классов. У класса ExcelPackage есть несколько разных конструкторов, которые можно использовать в зависимости от поставленной цели.

using System.IO;  // Для FileInfo
 
// Создание чистой книги(workbook). Используйте оператор using,
// чтобы удалить пакет по завершению работы.
using (var p = new ExcelPackage())
{
   // Книга должна состоять как минимум из листа и нескольких ячеек.
   // Добавим их.
   var ws=p.Workbook.Worksheets.Add("MySheet");
   // Для  установки значений на листе используется индексатор Cells.
   ws.Cells["A1"].Value = "This is cell A1";
   // Сохранение новой книги. Имя файла не было указано, поэтому
   // используйте метод SaveAs (сохранить как).
   p.SaveAs(new FileInfo(@"c:\workbooks\myworkbook.xlsx"));
}

Также можно задать имя файла напрямую в конструкторе:

// Открыть книгу (или создать её, если такого файла нет):
var fi=new FileInfo(@"c:\workbooks\myworkbook.xlsx");
using (var p = new ExcelPackage(fi))
{
   // Получение листа (Worksheet), созданного в предыдущем примере:
   var ws=p.Workbook.Worksheets["MySheet"];
   // Установка значения ячейки с использованием строки и колонки.
   ws.Cells[2, 1].Value = "This is cell A2. It is set to bolds";
   // Объект Style дает доступ к форматированию и стилям содержимого
   // ячеек. В этом примере текст делается "жирным":
   ws.Cells[2, 1].Style.Font.Bold=true;
   // Сохранение файла и закрытие пакета:
   p.Save();
}

Еще пример:

// Создание нового экземпляра ExcelPackage:
using (ExcelPackage excelPackage = new ExcelPackage())
{
   // Установка некоторых свойств документа Excel:
   excelPackage.Workbook.Properties.Author = "VDWWD";
   excelPackage.Workbook.Properties.Title = "Title of Document";
   excelPackage.Workbook.Properties.Subject = "EPPlus demo export data";
   excelPackage.Workbook.Properties.Created = DateTime.Now;
 
   // Создание листа:
   ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet 1");
 
   // Добавление текста в ячейку A1:
   worksheet.Cells["A1"].Value = "My first EPPlus spreadsheet!";
 
   // Для адресации ячейки можно также использовать нотацию [строка, столбец]:
   worksheet.Cells[1,2].Value = "This is cell B1!";
 
   // Сохранение файла:
   FileInfo fi = new FileInfo(@"Path\To\Your\File.xlsx");
   excelPackage.SaveAs(fi);
}
 
// Открытие существующего файла Excel:
FileInfo fi = new FileInfo(@"Path\To\Your\File.xlsx");
using (ExcelPackage excelPackage = new ExcelPackage(fi))
{
   // Получение листа по индексу. Обратите внимание, что EPPlus
   // использует индексы начиная с 1, не с 0!
   ExcelWorksheet firstWorksheet = excelPackage.Workbook.Worksheets[1];
    
   // Получение листа по имени. Если этот worksheet не существует,
   // выбрасывается исключение:
   ExcelWorksheet namedWorksheet = excelPackage.Workbook.Worksheets["SomeWorksheet"];
 
   // Если неизвестно, существует ли лист, можно использовать LINQ,
   // чтобы не попасть в исключение. Тогда будет возвращен null,
   // если лист не найден:
   ExcelWorksheet anotherWorksheet = 
      excelPackage.Workbook.Worksheets.FirstOrDefault(x=>x.Name=="SomeWorksheet");
 
   // Получение содержимого ячеек из A1 и B1 в виде строки, с помощью 
   // двух разных нотаций:
   string valA1 = firstWorksheet.Cells["A1"].Value.ToString();
   string valB1 = firstWorksheet.Cells[1,2].Value.ToString();
 
   // Сохранение файла:
   excelPackage.Save();
}

EPPlus может работать с книгами как потоками (stream). Это может быть полезно при чтении файлов с web-сервера, или когда нужно передать книги без наличия физического файла. В конструктор также можно передать пароль, если создается зашифрованная книга.

Подробнее про работу с EPPlus см. [4].

Скомпилировать библиотеку в DLL-файлы можно из исходного кода [3] с помощью Visual Studio 2019. Готовые скомпилированные DLL можно скачать в архиве [8].

[NPOI]

Этот проект представляет собой .NET-версию проекта POI Java. С помощью NPOI можно читать/записывать файлы Office 2003/2007. Проект не обновляется с 2018 года. Библиотека полностью бесплатна для использования. Поддерживаются большинство возможностей листа Excel - стиль ячеек, формат данных, формулы и т. п. Поддерживаются форматы xls, xlsx, docx, экспорт и импорт файлов. Библиотеку не требует установленного офиса, её можно использовать также и под Windows, и под Linux.

Пример чтения файла Excel:

static string ReadExcel()
{
   DataTable dtTable = new DataTable();
   List< string> rowList = new List< string>();
   ISheet sheet;
   using (var stream = new FileStream("TestData.xlsx", FileMode.Open))
   {
      stream.Position = 0;
      XSSFWorkbook xssWorkbook = new XSSFWorkbook(stream);
      sheet = xssWorkbook.GetSheetAt(0);
      IRow headerRow = sheet.GetRow(0);
      int cellCount = headerRow.LastCellNum;
      for (int j = 0; j < cellCount; j++)
      {
         ICell cell = headerRow.GetCell(j);
         if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue;
         {
            dtTable.Columns.Add(cell.ToString());
         } 
      }
      for (int i = (sheet.FirstRowNum + 1); i < = sheet.LastRowNum; i++)
      {
         IRow row = sheet.GetRow(i);
         if (row == null) continue;
         if (row.Cells.All(d => d.CellType == CellType.Blank)) continue;
         for (int j = row.FirstCellNum; j < cellCount; j++)
         {
            if (row.GetCell(j) != null)
            {
               if (!string.IsNullOrEmpty(row.GetCell(j).ToString()) &&
                   !string.IsNullOrWhiteSpace(row.GetCell(j).ToString()))
               {
                  rowList.Add(row.GetCell(j).ToString());
               }
            }
         }
         if(rowList.Count > 0)
            dtTable.Rows.Add(rowList.ToArray());
         rowList.Clear(); 
      }
   }
   return JsonConvert.SerializeObject(dtTable);
}

Пример экспорта/записи файла Excel:

static void WriteExcel()
{
   List< UserDetails> persons = new List< UserDetails>()
   {
       new UserDetails() {ID="1001", Name="ABCD", City ="City1", Country="USA"},
       new UserDetails() {ID="1002", Name="PQRS", City ="City2", Country="INDIA"},
       new UserDetails() {ID="1003", Name="XYZZ", City ="City3", Country="CHINA"},
       new UserDetails() {ID="1004", Name="LMNO", City ="City4", Country="UK"},
   };
 
   // Преобразуем наши данные объектов в таблицу Datatable.
   // Datatable дает простой способ чтения и форматирования
   // сложных типов данных.
   DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons),
                                                              (typeof(DataTable)));
   var memoryStream = new MemoryStream();
    
   using (var fs = new FileStream("Result.xlsx", FileMode.Create, FileAccess.Write))
   {
      IWorkbook workbook = new XSSFWorkbook();
      ISheet excelSheet = workbook.CreateSheet("Sheet1");
      List< String> columns = new List< string>();
      IRow row = excelSheet.CreateRow(0);
      int columnIndex = 0;
      
      foreach (System.Data.DataColumn column in table.Columns)
      {
         columns.Add(column.ColumnName);
         row.CreateCell(columnIndex).SetCellValue(column.ColumnName);
         columnIndex++;
      }
      int rowIndex = 1;
      foreach (DataRow dsrow in table.Rows)
      {
         row = excelSheet.CreateRow(rowIndex);
         int cellIndex = 0;
         foreach (String col in columns)
         {
            row.CreateCell(cellIndex).SetCellValue(dsrow[col].ToString());
            cellIndex++;
         }
         rowIndex++;
      }
      workbook.Write(fs);
   }
}

[OpenXML SDK]

Это open source SDK от Microsoft, который позволяет работать с файлами Office Word, Excel и PowerPoint. Необходимо установить пакет DocumentFormat.OpenXml.

Пример чтения файла Excel:

static void ReadExcelFile()
{
   try
   {
      // Откроем существующий файл Excel и прочитаем его содержимое.
      using (SpreadsheetDocument doc = SpreadsheetDocument.Open("testdata.xlsx", false))
      {
         // Создадим объект для части книги (класс WorkbookPart):
         WorkbookPart workbookPart = doc.WorkbookPart;
         Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild< Sheets>();
         StringBuilder excelResult = new StringBuilder();
 
         // Пройдемся в цикле по листу в thesheetcollection:  
         foreach (Sheet thesheet in thesheetcollection)
         {
            excelResult.AppendLine("Excel Sheet Name : " + thesheet.Name);
            excelResult.AppendLine("----------------------------------------------- ");
            // Оператор для получения объекта worksheet по идентификатору листа:
            Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;
 
            SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild< SheetData>();
            foreach (Row thecurrentrow in thesheetdata)
            {
               foreach (Cell thecurrentcell in thecurrentrow)
               {
                  // Оператор для взятия значения целого числа из ячейки:
                  string currentcellvalue = string.Empty;
                  if (thecurrentcell.DataType != null)
                  {
                     if (thecurrentcell.DataType == CellValues.SharedString)
                     {
                        int id;
                        if (Int32.TryParse(thecurrentcell.InnerText, out id))
                        {
                           SharedStringItem item = workbookPart.SharedStringTablePart.
                                    SharedStringTable.Elements< SharedStringItem>().ElementAt(id);
                           if (item.Text != null)
                           {
                              // Код получения значения строки:
                              excelResult.Append(item.Text.Text + " ");
                           }
                           else if (item.InnerText != null)
                           {
                              currentcellvalue = item.InnerText;
                           }
                           else if (item.InnerXml != null)
                           {
                              currentcellvalue = item.InnerXml;
                           }
                        }
                     }
                  }
                  else
                  {
                     excelResult.Append(Convert.ToInt16(thecurrentcell.InnerText) + " ");
                  }
               }
               excelResult.AppendLine();
            }
            excelResult.Append("");
            Console.WriteLine(excelResult.ToString());
            Console.ReadLine();
         }
      }
   }
   catch (Exception)
   {
   }
}

Пример экспорта/записи файла Excel:

static void WriteExcelFile()
{
   List< UserDetails> persons = new List< UserDetails>()
   {
      new UserDetails() {ID="1001", Name="ABCD", City ="City1", Country="USA"},
      new UserDetails() {ID="1002", Name="PQRS", City ="City2", Country="INDIA"},
      new UserDetails() {ID="1003", Name="XYZZ", City ="City3", Country="CHINA"},
      new UserDetails() {ID="1004", Name="LMNO", City ="City4", Country="UK"},
   };
 
   DataTable table = (DataTable)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(persons),
                                                              (typeof(DataTable)));
   using (SpreadsheetDocument document = SpreadsheetDocument.Create("TestNewData.xlsx",
                                                     SpreadsheetDocumentType.Workbook))
   {
      WorkbookPart workbookPart = document.AddWorkbookPart();
      workbookPart.Workbook = new Workbook();
 
      WorksheetPart worksheetPart = workbookPart.AddNewPart< WorksheetPart>();
      var sheetData = new SheetData();
      worksheetPart.Worksheet = new Worksheet(sheetData);
 
      Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
      Sheet sheet = new Sheet()
      {
         Id = workbookPart.GetIdOfPart(worksheetPart),
         SheetId = 1,
         Name = "Sheet1"
      };
 
      sheets.Append(sheet);
      Row headerRow = new Row();
 
      List< String> columns = new List< string>();
      foreach (System.Data.DataColumn column in table.Columns)
      {
         columns.Add(column.ColumnName);
         Cell cell = new Cell();
         cell.DataType = CellValues.String;
         cell.CellValue = new CellValue(column.ColumnName);
         headerRow.AppendChild(cell);
      }
 
      sheetData.AppendChild(headerRow);
      foreach (DataRow dsrow in table.Rows)
      {
         Row newRow = new Row();
         foreach (String col in columns)
         {
            Cell cell = new Cell();
            cell.DataType = CellValues.String;
            cell.CellValue = new CellValue(dsrow[col].ToString());
            newRow.AppendChild(cell);
         }
         sheetData.AppendChild(newRow);
      }
      workbookPart.Workbook.Save();
   }
}

[Ссылки]

1. How do I create an Excel (.XLS and .XLSX) file in C# without installing Microsoft Office? site:stackoverflow.com.
2. ExcelLibrary site:code.google.com.
3. JanKallman / EPPlus site:github.com.
4. Getting Started EPPlus site:github.com.
5. nissl-lab / npoi site:github.com.
6. Getting Started with NPOI site:github.com.
7. Read and Create/Write Excel file in C# .NET Core site:thecodebuzz.com.
8. 201024ExcelCsharpLibs.zip.

 

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


Защитный код
Обновить

Top of Page