Getsuyodev, Programming is mainly used when constructing an application. Programming requires knowledge of application domains, algorithms, and programming language expertise. Programming can be developed in different logic based on developer knowledge.

How to format an Excel file using C Sharp



When programming with Microsoft Office Excel, you can interact with the objects provided by the Excel object model. You can programmatically create a worksheet, read worksheet, formatting cell etc. from your c# application. In this article, we will take a closer look at manipulating Excel cells from your C# application.

The following source code using Microsoft Excel 12.0 Object Library. In the previous section, we saw how to import Microsoft Excel 12.0 Object Library in the C# project.

Create an Excel file from CSharp

Format Excel Column (or Cell)

Format Excel cells to store values as text

Formating Excel cells to text format will solve the problem of losing leading zero values when you export data from other data sources to excel.

Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "@";
xlWorkSheet.Cells[1, 1] = "098";

Excel Number Formatting

Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "#,###,###";
xlWorkSheet.Cells[1, 1] = "1234567890";

Excel Currency Formatting

Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "$ #,###,###.00";
xlWorkSheet.Cells[1, 1] = "1234567890";

Excel Date Formatting

 
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1", "b1");
formatRange.NumberFormat = "mm/dd/yyyy";
//formatRange.NumberFormat = "mm/dd/yyyy hh:mm:ss";
xlWorkSheet.Cells[1, 1] = "31/5/2014";

Bold the fonts of a specific row or cell

Bold entire row

 
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("a1");
formatRange.EntireRow.Font.Bold = true;
xlWorkSheet.Cells[1, 5] = "Bold";

Bold specific cell

 
workSheet.Cells[2, 1].Font.Bold = true;

Add a border to a specific cell

Excel.Range formatRange = xlWorkSheet.UsedRange;
Excel.Range cell = formatRange.Cells[3, 3];
Excel.Borders border = cell.Borders;
border.LineStyle = Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;

The border around multiple cells in excel

Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("b2", "e9");
formatRange.BorderAround(Excel.XlLineStyle.xlContinuous,
Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic,
Excel.XlColorIndex.xlColorIndexAutomatic);

Excel Cell colouring

 
Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("b1", "b1");
formatRange.Interior.Color = System.Drawing.
ColorTranslator.ToOle(System.Drawing.Color.Red);
xlWorkSheet.Cells[1, 2] = "Red";

Cell background-colour

The cell font colour, size

Excel.Range formatRange;
formatRange = xlWorkSheet.get_Range("b1", "b1");
formatRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
formatRange.Font.Size  = 10;
xlWorkSheet.Cells[1, 2] = "Red";

Excel Styles to a named range

Excel.Style myStyle = Globals.ThisWorkbook.Styles.Add("myStyle");
myStyle.Font.Name = "Verdana";
myStyle.Font.Size = 12;
myStyle.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
myStyle.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Gray);
myStyle.Interior.Pattern = Excel.XlPattern.xlPatternSolid;

How to merge Excel cells

xlWorkSheet.get_Range("b2", "e3").Merge(false);

Adding a Custom header to the excel file

 
xlWorkSheet.get_Range("b2", "e3").Merge(false);
chartRange = xlWorkSheet.get_Range("b2", "e3");
chartRange.FormulaR1C1 = "Your Heading Here";
chartRange.HorizontalAlignment = 3;
chartRange.VerticalAlignment = 3;

The following C# program creates a mark list in an Excel file and format the cells. First, we MERGE excel cell and create the heading, then the student’s name and totals make as BOLD. And finally, create a border for the whole mark list part.

using System;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel; 

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            Excel.Application xlApp;
            Excel.Workbook xlWorkBook;
            Excel.Worksheet xlWorkSheet;
            object misValue = System.Reflection.Missing.Value;
            Excel.Range chartRange;

            xlApp = new Excel.Application();
            xlWorkBook = xlApp.Workbooks.Add(misValue);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            //add data 
            xlWorkSheet.Cells[4, 2] = "";
            xlWorkSheet.Cells[4, 3] = "Student1";
            xlWorkSheet.Cells[4, 4] = "Student2";
            xlWorkSheet.Cells[4, 5] = "Student3";

            xlWorkSheet.Cells[5, 2] = "Term1";
            xlWorkSheet.Cells[5, 3] = "80";
            xlWorkSheet.Cells[5, 4] = "65";
            xlWorkSheet.Cells[5, 5] = "45";

            xlWorkSheet.Cells[6, 2] = "Term2";
            xlWorkSheet.Cells[6, 3] = "78";
            xlWorkSheet.Cells[6, 4] = "72";
            xlWorkSheet.Cells[6, 5] = "60";

            xlWorkSheet.Cells[7, 2] = "Term3";
            xlWorkSheet.Cells[7, 3] = "82";
            xlWorkSheet.Cells[7, 4] = "80";
            xlWorkSheet.Cells[7, 5] = "65";

            xlWorkSheet.Cells[8, 2] = "Term4";
            xlWorkSheet.Cells[8, 3] = "75";
            xlWorkSheet.Cells[8, 4] = "82";
            xlWorkSheet.Cells[8, 5] = "68";

            xlWorkSheet.Cells[9, 2] = "Total";
            xlWorkSheet.Cells[9, 3] = "315";
            xlWorkSheet.Cells[9, 4] = "299";
            xlWorkSheet.Cells[9, 5] = "238";

            xlWorkSheet.get_Range("b2", "e3").Merge(false);

            chartRange = xlWorkSheet.get_Range("b2", "e3");
            chartRange.FormulaR1C1 = "MARK LIST";
            chartRange.HorizontalAlignment = 3;
            chartRange.VerticalAlignment = 3;
            chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow  );
            chartRange.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
            chartRange.Font.Size  = 20;

            chartRange = xlWorkSheet.get_Range("b4", "e4");
            chartRange.Font.Bold = true;
            chartRange = xlWorkSheet.get_Range("b9", "e9");
            chartRange.Font.Bold = true;

            chartRange = xlWorkSheet.get_Range("b2", "e9");
            chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);

            xlWorkBook.SaveAs("d:\\csharp.net-informations.xls", Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlApp);
            releaseObject(xlWorkBook);
            releaseObject(xlWorkSheet);

            MessageBox.Show("File created !");
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        } 
    }
}

Comments are closed.

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More