Creating Excel workbook in C#

Creating Excel workbook in C#

Creating an Excel workbook in C# can be achieved using various libraries available in the .NET ecosystem. One of the most popular libraries for working with Excel files in C# is EPPlus. EPPlus allows you to create, read, and manipulate Excel files without requiring Excel to be installed on the machine. Here's how you can create an Excel workbook using EPPlus:

Step-by-Step Guide to Create an Excel Workbook

  1. Install EPPlus:

    First, you need to install the EPPlus package via NuGet Package Manager in Visual Studio. You can do this by right-clicking on your project, selecting "Manage NuGet Packages", and searching for "EPPlus". Install the latest version.

  2. Create and Populate an Excel Workbook:

    Here's a basic example of creating an Excel workbook with a single worksheet and adding data to it:

    using OfficeOpenXml; // Import EPPlus namespace using System; using System.IO; public class ExcelWriter { public void CreateExcelFile() { // Create a new Excel package using (ExcelPackage excelPackage = new ExcelPackage()) { // Add a worksheet to the workbook ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1"); // Add some data to cells worksheet.Cells["A1"].Value = "Name"; worksheet.Cells["B1"].Value = "Age"; worksheet.Cells["A2"].Value = "John"; worksheet.Cells["B2"].Value = 30; worksheet.Cells["A3"].Value = "Jane"; worksheet.Cells["B3"].Value = 25; // Save the Excel package to a file FileInfo excelFile = new FileInfo(@"C:\path\to\your\file.xlsx"); excelPackage.SaveAs(excelFile); } Console.WriteLine("Excel file created successfully!"); } }

    Explanation:

    • ExcelPackage: Represents the entire Excel workbook.
    • ExcelWorksheet: Represents a single worksheet within the Excel workbook.
    • Cells["A1"].Value: Sets the value of a cell. You can use Excel cell references (e.g., "A1", "B2") or numeric indices.
    • SaveAs: Saves the Excel workbook to the specified file path.

  3. Using the ExcelWriter Class:

    In your application, instantiate the ExcelWriter class and call the CreateExcelFile method to generate the Excel workbook:

    class Program { static void Main(string[] args) { ExcelWriter excelWriter = new ExcelWriter(); excelWriter.CreateExcelFile(); } }

  4. Further Customization:

    • Formatting: EPPlus allows you to format cells, set column widths, apply styles, and more.
    • Multiple Worksheets: You can add multiple worksheets to the same Excel workbook using excelPackage.Workbook.Worksheets.Add("Sheet2"), excelPackage.Workbook.Worksheets.Add("Sheet3"), etc.
    • Advanced Features: Explore EPPlus documentation for advanced features like charts, formulas, conditional formatting, and more.

Additional Considerations:

  • Performance: EPPlus is efficient for most Excel manipulation tasks but may not be suitable for extremely large datasets. Consider optimizations for such scenarios.
  • Error Handling: Implement error handling to manage exceptions during file operations or EPPlus interactions.
  • Security: Always validate and sanitize input to prevent potential security risks, especially when writing user-generated content to Excel files.

By following these steps, you can effectively create an Excel workbook in C# using EPPlus, populate it with data, and save it to a specified file location. Adjust the example code according to your specific requirements and integrate it into your application as needed.

Examples

  1. C# Excel interop create workbook Description: Learn how to use Excel Interop in C# to create a new Excel workbook. Code:

    // C# Excel interop create workbook example using Excel = Microsoft.Office.Interop.Excel; using System; class Program { static void Main() { // Create Excel application object Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; // Add a new workbook Excel.Workbook workbook = excelApp.Workbooks.Add(); // Optionally, save the workbook string filePath = @"C:\path\to\new\workbook.xlsx"; workbook.SaveAs(filePath); // Close Excel and release resources excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); workbook = null; excelApp = null; Console.WriteLine("Excel workbook created successfully."); } }

  2. C# Excel package create workbook Description: Explore how to create a new Excel workbook using third-party libraries like EPPlus in C#. Code:

    // C# EPPlus create workbook example using OfficeOpenXml; using System; using System.IO; class Program { static void Main() { // Create a new Excel package using (ExcelPackage excelPackage = new ExcelPackage()) { // Add a worksheet to the workbook ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1"); // Optionally, save the workbook string filePath = @"C:\path\to\new\workbook.xlsx"; FileInfo excelFile = new FileInfo(filePath); excelPackage.SaveAs(excelFile); Console.WriteLine("Excel workbook created successfully."); } } }

  3. C# create Excel workbook with multiple sheets Description: Learn how to create an Excel workbook with multiple sheets using Excel Interop in C#. Code:

    // C# Excel interop create workbook with multiple sheets example using Excel = Microsoft.Office.Interop.Excel; using System; class Program { static void Main() { // Create Excel application object Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; // Add a new workbook Excel.Workbook workbook = excelApp.Workbooks.Add(); // Add multiple worksheets to the workbook for (int i = 1; i <= 3; i++) { Excel.Worksheet worksheet = workbook.Worksheets.Add(); worksheet.Name = $"Sheet{i}"; } // Optionally, save the workbook string filePath = @"C:\path\to\new\workbook.xlsx"; workbook.SaveAs(filePath); // Close Excel and release resources excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); workbook = null; excelApp = null; Console.WriteLine("Excel workbook with multiple sheets created successfully."); } }

  4. C# Excel interop create workbook with data Description: Implement how to create an Excel workbook and populate it with data using Excel Interop in C#. Code:

    // C# Excel interop create workbook with data example using Excel = Microsoft.Office.Interop.Excel; using System; class Program { static void Main() { // Create Excel application object Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; // Add a new workbook Excel.Workbook workbook = excelApp.Workbooks.Add(); // Get the first worksheet in the workbook Excel.Worksheet worksheet = workbook.Worksheets[1]; // Populate data in the worksheet worksheet.Cells[1, 1] = "Name"; worksheet.Cells[1, 2] = "Age"; worksheet.Cells[2, 1] = "John Doe"; worksheet.Cells[2, 2] = 30; worksheet.Cells[3, 1] = "Jane Smith"; worksheet.Cells[3, 2] = 25; // Optionally, save the workbook string filePath = @"C:\path\to\new\workbook.xlsx"; workbook.SaveAs(filePath); // Close Excel and release resources excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); workbook = null; excelApp = null; Console.WriteLine("Excel workbook with data created successfully."); } }

  5. C# Excel create workbook from datatable Description: Create an Excel workbook from a DataTable using EPPlus in C#. Code:

    // C# EPPlus create workbook from DataTable example using OfficeOpenXml; using System; using System.Data; using System.IO; class Program { static void Main() { // Sample DataTable DataTable dataTable = new DataTable("Employees"); dataTable.Columns.Add("ID", typeof(int)); dataTable.Columns.Add("Name", typeof(string)); dataTable.Rows.Add(1, "John Doe"); dataTable.Rows.Add(2, "Jane Smith"); // Create a new Excel package using (ExcelPackage excelPackage = new ExcelPackage()) { // Add a worksheet to the workbook ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets.Add("Sheet1"); // Load data from DataTable to worksheet worksheet.Cells["A1"].LoadFromDataTable(dataTable, true); // Optionally, save the workbook string filePath = @"C:\path\to\new\workbook.xlsx"; FileInfo excelFile = new FileInfo(filePath); excelPackage.SaveAs(excelFile); Console.WriteLine("Excel workbook created successfully from DataTable."); } } }

  6. C# create Excel workbook with formatting Description: Learn how to create an Excel workbook with formatted cells using Excel Interop in C#. Code:

    // C# Excel interop create workbook with formatting example using Excel = Microsoft.Office.Interop.Excel; using System; class Program { static void Main() { // Create Excel application object Excel.Application excelApp = new Excel.Application(); excelApp.Visible = true; // Add a new workbook Excel.Workbook workbook = excelApp.Workbooks.Add(); // Get the first worksheet in the workbook Excel.Worksheet worksheet = workbook.Worksheets[1]; // Set cell values and formatting worksheet.Cells[1, 1] = "Product"; worksheet.Cells[1, 2] = "Price"; worksheet.Cells[2, 1] = "Laptop"; worksheet.Cells[2, 2] = 1200; worksheet.Cells[3, 1] = "Monitor"; worksheet.Cells[3, 2] = 300; // Apply formatting to cells worksheet.Cells[1, 1].Font.Bold = true; worksheet.Cells[1, 2].Font.Bold = true; worksheet.Cells[2, 2].Style.Numberformat.Format = "#,##0.00"; worksheet.Cells[3, 2].Style.Numberformat.Format = "#,##0.00"; // Optionally, save the workbook string filePath = @"C:\path\to\new\workbook.xlsx"; workbook.SaveAs(filePath); // Close Excel and release resources excelApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); workbook = null; excelApp = null; Console.WriteLine("Excel workbook with formatting created successfully."); } }

More Tags

jqgrid guid gitlab-8 elixir imagefield ms-access-2003 react-hook-form q# ng-build spark-excel

More Programming Questions

More Entertainment Anecdotes Calculators

More Organic chemistry Calculators

More Weather Calculators

More Date and Time Calculators