Excel cell Formating Using C#

veeruu · #1 (**permalink**) March 23rd, 2006, 07:25 AM

Hi..Friends,

how to format cells patterns,Border color, Font styles,
and Rename Worksheet Name for MS-Excel.
Using C#.

Plz help meee...

Thanks & Regards,
Veeruu...

laure · #2 (**permalink**) April 5th, 2006, 04:52 AM

//Variables
protected Excel.Worksheet feuilleExcel;
protected Excel.Range Cellules;
protected Excel.Range UneCellule;
protected Excel.Range DeuxCellules;
protected ExcelApplication xls;

//Open worksheet #1
this.feuilleExcel = (Excel.Worksheet)xls.__Excel.Worksheets[1];
this.feuilleExcel.Activate();
//get entire cells
this.Cellules = this.feuilleExcel.Cells;

//work on 1 cell
this.UneCellule=(Excel.Range)this.Cellules[XListe,YListe];
//value inserted
this.UneCellule.Value=this.ListeTypesCarte[i];
//cell border
this.UneCellule.BorderAround(Excel.XlLineStyle.xlC ontinuous,Excel.XlBorderWeight.xlThin,Excel.XlColo rIndex.xlColorIndexAutomatic,1);
//font style
this.UneCellule.Font.Bold=true;
//background color
this.UneCellule.Interior.Color=16777164;

//work on 2 cells
//insert values on first cell
this.Cellules[X,1]=rs.GetString(0);
//get cells
this.DeuxCellules = (Excel.Range)this.feuilleExcel.get_Range(this.Cell ules[X,1],this.Cellules[X,2]);
//cell border
this.DeuxCellules.BorderAround(Excel.XlLineStyle.x lContinuous,Excel.XlBorderWeight.xlThin,Excel.XlCo lorIndex.xlColorIndexAutomatic,1);
//background color
this.DeuxCellules.Interior.Color=10092543;
//font style
this.DeuxCellules.Font.Bold=true;

***********************************
EXCEL CLASS
***********************************
using System;
using System.Reflection;

namespace ControlOffice
{
public class ExcelApplication
    {
        public Excel.Application __Excel; // Application est une interface
        public object opt= Missing.Value;
        public string f;

        public ExcelApplication(string fich)
        {
            //
            // TODO : ajoutez ici la logique du constructeur
            //

            __Excel=new Excel.ApplicationClass();
            __Excel.Workbooks.Open(fich,opt,opt,opt,opt,opt,op t,opt,opt,opt,opt,opt, opt);
            f =fich;

        }

        public bool Visible
        {
            get
            {
                return __Excel.Visible;
            }
            set
            {
                __Excel.Visible=value;
            }
        }

        public Excel.Workbook nomClasseur
        {
            get
            {
                return __Excel.ActiveWorkbook;
            }
        }

        public Excel.Worksheet nomFeuil
        {
            get
            {
                return (Excel.Worksheet) __Excel.ActiveWorkbook.ActiveSheet;
            }
        }

        public Excel.Range Cellules
        {
            get
            {

                return nomFeuil.Cells;

            }
        }

        public void quit(bool save)
        {

            //__Excel.ActiveWorkbook.Close(save,opt,opt);
            __Excel.ActiveWorkbook.Close(save,this.opt,this.op t);
            __Excel.Quit();

        }
    }
}

****IMPORTANT*****
Don't forget join Office and Excel references to project

veeruu · #3 (**permalink**) April 7th, 2006, 01:32 AM

hi laure,
Thanks alot for your response.
"If you dont mind.." Can you please mail your application to me.
to finish my Task a bit fast..

Thanks & regards,
veeruuu...

prasad7478@yahoo.com

cikaPero · #4 (**permalink**) March 4th, 2010, 03:42 AM

Hi,

for easier Excel manipulation, I recommend this Excel C# component.

Here is a code snippet for all tasks you need:

Code:

var excelFile = new ExcelFile();
var worksheet = excelFile.Worksheets.Add("Worksheet1");
ws.Cells[0, 0].Value = DateTime.Now; // Type is System.DateTime

// Some Font Styles
ws.Cells[0, 0].Style.Font.Color = Color.Blue;
ws.Cells[0, 0].Style.Font.Italic = true;
ws.Cells[0, 0].Style.Font.Name = "Comic Sans MS";
ws.Cells[0, 0].Style.Font.Size = 150;

// Setting border on cell range
var mergedRange = ws.Cells.GetSubrangeAbsolute(0, 0, 10, 5);
var cellStyle = new CellStyle();
cellStyle.Borders.SetBorders(MultipleBorders.Right | MultipleBorders.Top, Color.Black, LineStyle.Thin);
mergedRange.Style = cellStyle;

// Change worksheet name
worksheet.Name = "Another name";

// Saves the excel file.
excelFile.SaveXls("excelFile.xls");

hu_yang · #5 (**permalink**) March 9th, 2010, 12:32 AM

go through the following link

http://csharp.net-informations.com/e...rmat-excel.htm

hu.

Philky001 · #6 (**permalink**) September 21st, 2011, 01:56 PM

I put this way but there is an syntax error: newSheet.Cells[i + 2, 3] = drugorder.DATE; HorizontalAlignment == ExcelAlignment.xlLeft; Error 2 'System.Windows.Forms.HorizontalAlignment' is a 'type' but is used like a 'variable' C:\ADL\ExcelReader\ExcelReader\Form1.cs 257 68 ExcelReader Error 3 Only assignment, call, increment, decrement, and new object expressions can be used as a statement C:\ADL\ExcelReader\ExcelReader\Form1.cs 257 68 ExcelReader

Need to download code?

Navigation