Net-informations.com
SiteMap  | About    

How to format an Excel file using C#

How to enter data with leading zero in excel

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 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 zeo 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 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;

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 coloring

Cell background color

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 font color , 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 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 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 create a mark list in Excel file and format the cells. First we MERGE excel cell and create the heading , then the students name and totals make as BOLD . And finally create a border for the whole mark list part.

Output:

C# Excel fomatting




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();
            }
        } 
    }
}

 CSharp Excel - Related Contents