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