Update Excel Sheet using C#, Where Excel not Installed on Server

I am going to show two ways of Excel Sheet Update using C#.

Oneway is Where MS Excel got installed on the Server.

Steps to Update Excel File:

1) Get the File Name on the Server.
2) Create _Application calss Instance
3) Create _WorkBook calss Instace for _Application
4) Get the available Sheets for _WorkBook Object
5) Update Excel Sheet Columns with Desired Values.
6) Save the Workbook and Quit Application

Add the following Referece to the Program:

using Microsoft.Office.Interop.Excel;

///

/// Takes Newly Generated ECR No and Updated ECR Form with ECR No, Description

/// Need Excel Installed On Production Server using UpdateECRForm() method

///

///

protected void UpdateECRForm(int _ECRNo)

{

string xlsFileName = Server.MapPath(ConfigurationManager.AppSettings["NewECRFormName"].ToString() + _ECRNo + ".xlsx");

_Application xl = new ApplicationClass();

_Workbook wb = xl.Workbooks.Open(xlsFileName, 0, false, 5, Missing.Value, Missing.Value, false, Missing.Value, Missing.Value, true, false, Missing.Value, false, false, false);

try

{

Sheets xlsheets = wb.Sheets;

Worksheet xlsheet = (Worksheet)xlsheets[1];

//Update Excel Sheet Values

xlsheet.Cells[ConfigurationManager.AppSettings["CR#Row"], ConfigurationManager.AppSettings["CR#Column"]] = _ECRNo;

xlsheet.Cells[ConfigurationManager.AppSettings["DescriptionRow"], ConfigurationManager.AppSettings["DescriptionColumn"]] = "Test Excel Automation";

xlsheet.Cells[ConfigurationManager.AppSettings["RequestedByRow"], ConfigurationManager.AppSettings["RequestedByColumn"]] = "Test Excel Automation";

xlsheet.Cells[ConfigurationManager.AppSettings["DateRow"], ConfigurationManager.AppSettings["DateColumn"]] = "Test Excel Automation";

wb.Save();

xl.Quit();

}

catch

{

throw;

}

finally

{

wb = null;

xl = null;

}

}

Second Method, where Excel is not Installed on Server.

Here I am going to use ExcelPackage, which is available from CodePlex.

You can download ExcelPackage Dll from the following URL:

http://www.codeplex.com/ExcelPackage/Release/ProjectReleases.aspx?ReleaseId=1456

Add the following Reference to the program:

using OfficeOpenXml;

///

/// Takes Newly Generated ECR No and Updated ECR Form with ECR No, Description

/// Using UpdateECRFormByOpenXML(), avoid installation of Excel On Production Server

///

///

protected void UpdateECRFormByOpenXML(int _ECRNo)

{

try

{

string xlsFileName = Server.MapPath(ConfigurationManager.AppSettings["NewECRFormName"].ToString() + _ECRNo + ".xlsx");

FileInfo file = new FileInfo(xlsFileName);

using (ExcelPackage xlPackage = new ExcelPackage(file))

{

List<_ECR> _ecrRec = new List<_ECR>();

_ecrRec = GetECRSummaryByECRNo(_ECRNo);

if ((_ecrRec != null) && (_ecrRec.Count > 0))

{

//Get the WorkSheet

ExcelWorksheet worksheet = xlPackage.Workbook.Worksheets[1];

worksheet.Cell(Convert.ToInt32(ConfigurationManager.AppSettings["CR#Row"]), Convert.ToInt32(ConfigurationManager.AppSettings["CR#Column"])).Value = _ecrRec[0].ECRNo.ToString();

worksheet.Cell(Convert.ToInt32(ConfigurationManager.AppSettings["DescriptionRow"]), Convert.ToInt32(ConfigurationManager.AppSettings["DescriptionColumn"])).Value = _ecrRec[0].Description.Trim();

worksheet.Cell(Convert.ToInt32(ConfigurationManager.AppSettings["RequestedByRow"]), Convert.ToInt32(ConfigurationManager.AppSettings["RequestedByColumn"])).Value = _ecrRec[0].RequestedBy.Trim();

worksheet.Cell(Convert.ToInt32(ConfigurationManager.AppSettings["DateRow"]), Convert.ToInt32(ConfigurationManager.AppSettings["DateColumn"])).Value = _ecrRec[0].DateInit.ToString();

//Set Excel Properties If Available

xlPackage.Workbook.Properties.Title = "CR Form # " + _ECRNo;

xlPackage.Save();

}

}

}

catch

{

}

}