c# - Excel worksheet change event is not firing -


i have created excel workbook using .net interop. excel workbook created through c# code. when user makes changes in excel, want stuff. have used excelworksheet.change event. event not firing. here code- 

using excel = microsoft.office.interop.excel;     public class xxx   {         static excel.application xlapp;       static excel.workbook xlworkbook;       static excel.worksheet xlworksheet;       static excel.worksheet xlworksheet1;     static excel.docevents_changeeventhandler eventdel_cellschange;        public static void exporttoexcel()     {                    xlapp = new excel.applicationclass();         object misvalue = system.reflection.missing.value;         xlworkbook = xlapp.workbooks.add(misvalue);          xlworksheet = (excel.worksheet)xlworkbook.worksheets.get_item(1);         xlworksheet1 = (excel.worksheet)xlworkbook.worksheets.get_item(2);           ---------------- data dumped excel here----------------           ((microsoft.office.interop.excel._worksheet)xlworksheet).activate();         xlapp.enableevents = true;         eventdel_cellschange = new excel.docevents_changeeventhandler(worksheet_change);           xlworksheet.change += eventdel_cellschange;         xlworkbook.saveas("d:\\test.xlsx", excel.xlfileformat.xlworkbookdefault, misvalue, misvalue, misvalue, misvalue, excel.xlsaveasaccessmode.xlshared, misvalue, misvalue, misvalue, misvalue, misvalue);           xlworkbook.close(true, misvalue, misvalue);         xlapp.quit();         releaseobject(xlworksheet1);         releaseobject(xlworksheet);         releaseobject(xlworkbook);         releaseobject(xlapp);            system.web.httpresponse response = system.web.httpcontext.current.response;         response.clearcontent();         response.clear();         response.contenttype = "application/vnd.ms-excel";         response.addheader("content-disposition", "attachment; filename=test.xlsx;");         response.transmitfile(("d:\\test.xlsx");         response.flush();         response.end();       }        public static void worksheet_change(excel.range target)     {         try         {             xlapp.enableevents = false;                             excel.range range = xlworksheet.get_range("y2");                             range.formula = "=a2";                         }         catch (exception ex)         {                        }                  {             xlapp.enableevents = true;         }     }   }

no change reflected in excel file when user makes changes. please me out. in advance

the worksheet_change event not global - applies particular worksheet. in code wire event handler xlsheet1.change event, close workbook , release excel objects.

edit: popped code behind form , adapted slightly. event fire , formula in cell y2 set. i'm not 100% sure of circumstances, try code , compare own. hope helps.

public partial class form1 : form {     private static excel.application xlapp;      private static excel.workbook xlworkbook;      private static excel.worksheet xlworksheet;      private static excel.worksheet xlworksheet1;      private static excel.docevents_changeeventhandler eventdel_cellschange;      public form1()     {         initializecomponent();     }      private void button1_click(object sender, eventargs e)     {         xlapp = new excel.application();         xlapp.visible = true;         object misvalue = system.reflection.missing.value;         xlworkbook = xlapp.workbooks.add(misvalue);         xlworksheet = (excel.worksheet)xlworkbook.worksheets.get_item(1);         xlworksheet1 = (excel.worksheet)xlworkbook.worksheets.get_item(2);         //---------------- data dumped excel here----------------           ((microsoft.office.interop.excel._worksheet)xlworksheet).activate();         xlapp.enableevents = true;         eventdel_cellschange = new excel.docevents_changeeventhandler(worksheet_change);         xlworksheet.change += eventdel_cellschange;        }      public static void worksheet_change(excel.range target)     {         try         {             xlapp.enableevents = false;             excel.range range = xlworksheet.get_range("y2");             range.formula = "=a2";         }         catch (exception ex)         {         }                 {             xlapp.enableevents = true;         }     } }

Comments

Post a Comment

Popular posts from this blog

python - How to create a legend for 3D bar in matplotlib? -

Image
given ax = plt.subplot() : ax.bar()[0] can passed plt.legend() . however, ax.bar3d() returns none . how create legend displayed bars? update: passing legend="stuff" ax.bar3d() , calling ax.legend() raises /usr/lib/python2.6/site-packages/matplotlib/axes.py:4368: userwarning: no labeled objects found. use label='...' kwarg on individual plots. warnings.warn("no labeled objects found. " you need use proxy artist legends not supported. this code: from mpl_toolkits.mplot3d import axes3d import matplotlib.pyplot plt import numpy np fig = plt.figure() ax = fig.add_subplot(111, projection='3d') x, y = np.random.rand(2, 100) * 4 hist, xedges, yedges = np.histogram2d(x, y, bins=4) elements = (len(xedges) - 1) * (len(yedges) - 1) xpos, ypos = np.meshgrid(xedges[:-1]+0.25, yedges[:-1]+0.25) xpos = xpos.flatten() ypos = ypos.flatten() zpos = np.zeros(elements) dx = 0.5 * np.ones_like(zpos) dy = dx.copy() dz = hist.flatten() ax...
Read more

php - Dynamic url re-writing using htaccess -

how can rewrite http://mydomain.com/test/master.php?catid=2&subcatid=8 http://mydomain.com/test/master.php/catname/subcat using .htaccess file. once rewritten, how can access these parameters in master.php? note: untested, let me know if doesn't work. rewriterule ^master.php/([a-za-z0-9_-]+)/([a-za-z0-9_-]+)$ /master.php?catid$1&subcatid=$2 [l]
Read more

java - Multi-Label Document Classification -

i have database in store data based upon following 3 fields: id, text, {labels}. note each text has been assigned more 1 label \ tag \ class. want build model (weka \ rapidminer \ mahout) able recommend \ classify bunch of labels \ tags \ classes given text. i have heard svm , naive bayes classifier, not sure whether support multi-label classification or not. guides me right direction more welcome! the basic multilabel classification method one-vs.-the-rest (ovr), called binary relevance (br). basic idea take off-the-shelf binary classifier, such naive bayes or svm, create k instances of solve k independent classification problems. in python-like pseudocode: for each class k: learner = svm(settings) # example labels = [class_of(x) == k x in samples] learner.learn(samples, labels) then @ prediction time, run each of binary classifiers on sample , collect labels predict positive. (both training , prediction can done in parallel, since problems assumed ...
Read more