C# is one of the most popular languages today and there are many reasons for this. It has many qualities that make it suitable for development for users at most all levels. Let’s discuss how we can create an Excel Spread Sheet using C#.
Microsoft Excel is perhaps the most important computer software program used in the workplace today.
Simple Excel spreadsheet Creation:
Step #1
To use the features of a Microsoft Office application from an Office project, you must use the primary interop assembly (PIA) for the application. The PIA enables managed code to interact with a Microsoft Office application’s COM-based object model.
So, Add an Extension “Microsoft Office Interop Excel“.
Project -> Add Reference -> Extension
Step #2
Include the “Microsoft Office Interop Excel” in your class file
|
1 2 3 |
using Excel = Microsoft.Office.Interop.Excel; using Microsoft.Win32; using System.Windows; |
Step #3
The Following C# Code Creates the Simple Excel Sheet
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp != null) { Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); xlWorkSheet.Cells[1, 1] = "First Excel"; xlWorkBook.SaveAs("D:\\First_Excel.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } |
Classes used
- Excel.Application -Top level object in the Excel object model, used to specify application level properties and application level methods
- Excel.Worbook – Represents a single workbook within the Excel application
- Excel.Worksheet – A member of the Worksheets collection in the Workbook object
Let us consider the List of Mobiles which is constructed in the following C# List
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
public class Mobile { public string Name { get; set; } public string OS { get; set; } public string Category { get; set; } public int Year { get; set; } } void Main() { List<Mobile> MobileList = new List<Mobile>() { new Mobile() { Name = "iPhone 6", OS = "iOS", Category = "Premium Smart Phone", Year = 2014 }, new Mobile() { Name = "iPhone 6S", OS = "iOS", Category = "Premium Smart Phone", Year = 2015 }, new Mobile() { Name = "Galaxy S6", OS = "Android", Category = "Premium Smart Phone", Year = 2015 }, new Mobile() { Name = "Galaxy A7", OS = "Android", Category = "Smart Phone", Year = 2015 }, new Mobile() { Name = "Lumina 9900", OS = "Windows", Category = "Smart Phone", Year = 2011 } }; } |
Now Let us see how to create an Excel Spread Sheet for the above said List<Mobile> MobileList
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 |
Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application(); if (xlApp != null) { Excel.Workbook xlWorkBook; Excel.Worksheet xlWorkSheet; object misValue = System.Reflection.Missing.Value; xlWorkBook = xlApp.Workbooks.Add(misValue); xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1); int rowCount = 1; /// Header xlWorkSheet.Cells[rowCount, 1] = "Name"; xlWorkSheet.Cells[rowCount, 2] = "OS"; xlWorkSheet.Cells[rowCount, 3] = "Category"; xlWorkSheet.Cells[rowCount, 4] = "Year"; rowCount++; foreach(var item in MobileList) { xlWorkSheet.Cells[rowCount, 1] = item.Name; xlWorkSheet.Cells[rowCount, 2] = item.OS; xlWorkSheet.Cells[rowCount, 3] = item.Category; xlWorkSheet.Cells[rowCount, 4] = item.Year; rowCount++; } xlWorkBook.SaveAs("D:\\Mobile_List.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue); xlWorkBook.Close(true, misValue, misValue); xlApp.Quit(); } |
Output Snapshot:
Advanced Excel Spread Sheet:
Cell Selection:
To format the Sheet, first select the cells, it may be single, multiple, entire row or entire column. The Interface Excel.Range (Microsoft.Office.Interop.Excel.Range) is used here to select the Cells.
Excel.Range formatRange;
To Select a Single Cell:
|
1 |
formatRange = xlWorkSheet.Range["b6"]; |
To Select a Range of Cells:
|
1 |
formatRange = xlWorkSheet.get_Range("a1", "d1"); |
|
1 |
formatRange = xlWorkSheet.get_Range("a1", "a4"); |
|
1 |
formatRange = xlWorkSheet.get_Range("a1", "d6"); |
To Select a Row:
|
1 |
formatRange = xlWorkSheet.get_Range("a2").EntireRow; |
To Select a Column:
|
1 |
formatRange = xlWorkSheet.get_Range("b1").EntireColumn; |
Font Style and Cell Alignment
Font Bold:
|
1 2 3 |
formatRange = xlWorkSheet.get_Range("a1"); formatRange.EntireRow.Font.Bold = true; |
Font Color:
|
1 2 3 |
formatRange = xlWorkSheet.get_Range("a1"); formatRange.EntireColumn.Font.Color = Excel.XlRgbColor.rgbBrown; |
Cell Alignment:
|
1 2 3 4 |
formatRange = xlWorkSheet.get_Range("a1"); formatRange.EntireRow.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; |
Cell Background Color:
|
1 |
formatRange = xlWorkSheet.get_Range("a1", "a4"); formatRange.Interior.Color = Excel.XlRgbColor.rgbSilver; |
Sort data in an Excel table:
|
1 2 3 |
formatRange = xlWorkSheet.get_Range("b1"); formatRange.Sort(Type.Missing, Excel.XlSortOrder.xlAscending); |
Filter data in an Excel table:
|
1 |
formatRange = xlWorkSheet.Range[xlWorkSheet.Cells[1, 1], xlWorkSheet.Cells[1, rowCount - 1]]; formatRange.AutoFilter(1, Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true); |
Conclusion:
This article targets at understanding Excel file manipulations programmatically using C#. This article is intended for the beginner/intermediate level. I hope, this post will solve all your basic functionality implementation of Excel Spread Sheet. Think Big… Start Small… Do Fast…
