Creating Excel files in C# without Microsoft Office installation
Excel is a widely used software for creating spreadsheets and organizing data. However, it can be quite expensive and not everyone has access to it. In this article, we will explore how to create Excel files in C# without the need for Microsoft Office installation.
Firstly, we need to understand the basic structure of an Excel file. An Excel file is essentially a collection of worksheets, which are made up of cells arranged in rows and columns. Each cell can contain data, formulas, or formatting. The Excel file format is based on the Open XML standard, which means we can create and manipulate Excel files without having to install Microsoft Office.
To get started, we will need to install the DocumentFormat.OpenXml NuGet package in our C# project. This package contains the necessary classes and methods to work with Excel files. Once installed, we can start by creating a new Excel document and adding a worksheet to it.
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml.Packaging;
// Create a new spreadsheet document
SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create("MyExcelFile.xlsx", SpreadsheetDocumentType.Workbook);
// Add a worksheet to the document
spreadsheetDocument.AddWorkbookPart();
spreadsheetDocument.WorkbookPart.Workbook = new Workbook();
spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
// Save the changes
spreadsheetDocument.WorkbookPart.Workbook.Save();
Next, we can add data to our worksheet by accessing the cells and setting their value. Let's say we want to add a simple table with names and ages of people.
// Access the first worksheet in the document
Worksheet worksheet = spreadsheetDocument.WorkbookPart.Worksheet.First();
// Add data to the cells
Cell cellA1 = InsertCellInWorksheet("A", 1, worksheet);
cellA1.CellValue = new CellValue("Name");
Cell cellB1 = InsertCellInWorksheet("B", 1, worksheet);
cellB1.CellValue = new CellValue("Age");
Cell cellA2 = InsertCellInWorksheet("A", 2, worksheet);
cellA2.CellValue = new CellValue("John");
Cell cellB2 = InsertCellInWorksheet("B", 2, worksheet);
cellB2.CellValue = new CellValue("30");
Cell cellA3 = InsertCellInWorksheet("A", 3, worksheet);
cellA3.CellValue = new CellValue("Jane");
Cell cellB3 = InsertCellInWorksheet("B", 3, worksheet);
cellB3.CellValue = new CellValue("25");
// Save the changes
worksheet.Save();
// Helper method to insert a cell in the worksheet
private static Cell InsertCellInWorksheet(string columnName, uint rowIndex, Worksheet worksheet)
{
SheetData sheetData = worksheet.GetFirstChild<SheetData>();
string cellReference = columnName + rowIndex;
// Check if the cell already exists
if (sheetData.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).Count() > 0)
{
return sheetData.Elements<Cell>().Where(c => c.CellReference.Value == cellReference).First();
}
else
{
// Add a new cell to the worksheet
Cell cell = new Cell() { CellReference = cellReference };
sheetData.Append(cell);
// Save the changes
worksheet.Save();
return cell;
}
}
Lastly, we can add formatting to our cells to make our Excel file more visually appealing. This can be done by accessing the CellFormat property of a cell and setting the Font, Fill, or Border properties.
