How to Update Dependent Excel Cells Automatically

-

I was facing some issue to update excel cell which was depended on some formula,
I need to change 'Comparison_Rate_Calculator' by Programming and need to read
Calculated value by formula '=+HLOOKUP(C3,Workings!$D$1:$AJ$2,2,FALSE)'
over 'Comparison_Rate_Calculator_Values' column.

I didn't get calculated value even i was able to change 'Comparison_Rate_Calculator' column's value.


So I got solutions by refreshing Excel WorkSheet and Here is the Solutions.

 File: Web.Config

<connectionstrings>
  <add
      name="xls"
      connectionstring="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\AshvinWorkspace\POCs\ReadExcelSheet\ReadExcelSheet\Files\HLComparisonRateCalculator.xls;Extended Properties=Excel 8.0">
  </add>
</connectionstrings>

File: Default.aspx.cs

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            DataTable dtXLSData = ChangeExcelSheetValue();
        }
        catch
        {
            throw;
        }
    }


    private DataTable ChangeExcelSheetValue()
    {
        string connString = ConfigurationManager.ConnectionStrings["xls"].ConnectionString;
        OleDbConnection oledbConn = new OleDbConnection(connString);
        OleDbDataAdapter XLSDataAdp = new OleDbDataAdapter();
        DataTable dtXLSData = new DataTable();
        XLSDataAdp.SelectCommand = new OleDbCommand();
        XLSDataAdp.SelectCommand.Connection = oledbConn;
        oledbConn.Open();

        XLSDataAdp.SelectCommand.CommandText = "SELECT * FROM [Sheet1$]";
        XLSDataAdp.Fill(dtXLSData);

        XLSDataAdp.SelectCommand.CommandText = "Update [Sheet1$] set Comparison_Rate_Calculator = 8.55 where SrNo=17";
        XLSDataAdp.SelectCommand.ExecuteNonQuery();
        // Need to close connection, to Refresh excel cell.
        oledbConn.Close();

        RefreshExcelFile();

        // Need to Open connection, Retrives Updated Data.
        oledbConn.Open();
        dtXLSData.Clear();
        XLSDataAdp.SelectCommand.CommandText = "SELECT * FROM [Sheet1$]";
        XLSDataAdp.Fill(dtXLSData);

        oledbConn.Close();
        return dtXLSData;
    }

    private static void RefreshExcelFile()
    {
        Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook excelBook = ExcelObj.Workbooks.Open(@"D:\AshvinWorkspace\POCs\ReadExcelSheet\ReadExcelSheet\Files\RateCalculator.xls", false, false, Type.Missing, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", false, false, 0, false, true, 0);
        excelBook.RefreshAll();
        excelBook.Save();

        ExcelObj.Workbooks.Close();
        ExcelObj.Quit();
        //KillProcesses("Excel");
        ReleaseComObject(ExcelObj);
    }

    private static void KillProcesses(string ProcessesName)
    {
        System.Diagnostics.Process[] processes = Process.GetProcessesByName(ProcessesName);
        foreach (System.Diagnostics.Process process in processes)
        {
            process.Kill();
        }
    }

    private static void ReleaseComObject(object ProcessReference)
    {
        try
        {
            // System.Runtime.InteropServices.Marshal.ReleaseComObject(ProcessReference);
            while (!(System.Runtime.InteropServices.Marshal.ReleaseComObject (ProcessReference) <= 0)) { }
        }
        catch { }
        finally
        {
            ProcessReference = null;
        }
    }
}

Comments

Post a Comment

Popular posts from this blog

Auto Sequence Number in Grid Control with Paging

-
we are going to see how to generate sequence number (row number) in DataGrid. To generate sequence number, we will use the DataGridItem’s ItemIndex property. But For example if we are going to second page with Pagesize 3, then sequence number should start from 4. Code snippet for implementing this method is... < asp:DataGrid id ="DataGrid1" runat ="server" PagerStyle-Mode ="NumericPages" PageSize ="10" AutoGenerateColumns ="False" AllowPaging ="True" > < Columns > < asp:templatecolumn headertext ="Row Number" > < itemtemplate > <% # (DataGrid1.PageSize*DataGrid1.CurrentPageIndex)+ Container.ItemIndex+1 %> </ itemtemplate > </ asp:templatecolumn > < asp:boundcolumn runat ="server" Da...
Read more

Polymorphism in C#

-
Polymorphism means having more than one form.   Overloading and overriding are used to implement polymorphism.  Polymorphism Examples Method Overloading Method Overriding There are two type of Polymorphism  1. Compile time polymorphism (Early binding or static binding)  Examples: overloaded methods, overloaded operators and overridden methods that are called directly by using derived objects. 2. Run time polymorphism (Late binding or dynamic binding)   Example: overridden methods that are called using base class object.  
Read more

Print Function By JavaScript

-
function PrintContent( Obj) { PrintWin=window.open('','PrintWin','directories=no, location=no, menubar=no, resizable=no, status=no, toolbar=no, scrollbars=no, width=10, height=10, left=1050, top=800'); PrintWin.document.write(" "); PrintWin.document.write(document.getElementById(Obj.id).outerHTML); PrintWin.location.reload(); PrintWin.print(); PrintWin.close(); } /************* Function call ***************/ btnPrint.Attributes.Add( "OnClick", "PrintContent(" + TblPrint.ClientID + ");");
Read more

Variable Value in Top Statement

-
i want create a Procedure that behave like this: declare @cnt int set @cnt =10 select top @cnt * from sw_user Where sw_user contains 20 records, and i want the variable to be the criteria of Top statement. so i can use this for a solution for this type of problem CREATE PROC Top10 @cnt int AS SET ROWCOUNT @cnt SELECT * FROM sw_user but some cases ROWCOUNT might not be work... so what, Note : This statement however is possible in SQL 2005, just add "()" in your variable like so: declare @cnt int set @cnt =10 select top (@cnt) * from sw_user
Read more

Auto Refresh .aspx page - ASP.NET

-
Method 1: Response.AddHeader To Refresh Web page After every 5 Seconds You can add following code, Response.AddHeader("Refresh", "5"); Cricket Sites, Stock Exchange sites use similar logic :) Method 2: In body Tag, window.setTimeout The 1000 = 1 Second... < body onload ="window.setTimeout('window.location.reload()',1000);" > Method 3: In Meta Tag Theres also a meta tag that you can define on the head, but not sure wheter it refreshes even if the content has not finished loading or if it starts counting when the head section loaded. Code would be something like that: < meta content ="600" http-equiv ="refresh" > Method 4: Timer Control : Microsoft ASP.NET 2.0 AJAX Extensions server control In following example, the timer interval is set to 10 seconds <%@ Page Language="C#" AutoEventWireup="true" %> <! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN...
Read more

Paged Data Source - Custom Paging

-
PagedDataSource , is a class that encapsulates the paging related properties for data-bound controls such as DataGrid, GridView, DataList, DetailsView and FormView that allow it to perform paging. And this article is going to combine both DataList control and PagedDataSource class to explain dynamic or custom paging methods for Asp.Net developers. protected void Page_Load( object sender, EventArgs e) { try { if (!IsPostBack) { LoadPhoto(); } } catch (Exception Exc) { HandleException = Exc; } } // LoadPhoto Function private void LoadPhoto() { PagedDataSource objPhotoPage = new PagedDataSource(); objPhotoPage.AllowPaging = true ; SOBLImageGallery _SOBLImageGallery = new SOBLImageGallery(); DataSet dsImageGallery = _SOBLImageGallery.Select6Records( new SOENTImageGallery()); if (dsImageGallery != null && dsImageGallery.Tables.Count > 0 && dsImageGallery.Tables[0]....
Read more