Update Excel Sheet using C#, Where Excel not Installed on Server
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
{
}
}
Comments