c# - append to excel file that is already created -
i have excel file writes contents c# application cell contents:
private void button8_click(object sender, eventargs e) { excel.application xlapp = new microsoft.office.interop.excel.application(); if (xlapp == null) { messagebox.show("excel not installed!!"); return; } excel.workbook xlworkbook; excel.worksheet xlworksheet; object misvalue = system.reflection.missing.value; xlworkbook = xlapp.workbooks.add(misvalue); xlworksheet = (excel.worksheet)xlworkbook.worksheets.get_item(1); xlworksheet.cells[1, 1] = combobox2.text; xlworksheet.cells[1, 2] = textbox5.text; xlworksheet.cells[1, 3] = textbox2.text; xlworksheet.cells[1, 4] = combobox3.text; xlworksheet.cells[1, 5] = textbox3.text; xlworksheet.cells[1, 6] = combobox1.text; xlworkbook.saveas(@"cross_check.xls", excel.xlfileformat.xlworkbooknormal, misvalue, misvalue, misvalue, misvalue, excel.xlsaveasaccessmode.xlexclusive, misvalue, misvalue, misvalue, misvalue, misvalue); xlworkbook.close(true, misvalue, misvalue); xlapp.quit(); marshal.releasecomobject(xlworksheet); marshal.releasecomobject(xlworkbook); marshal.releasecomobject(xlapp); messagebox.show("excel file created succcessfully"); } } how append same file has been created?to expand further, have specify cells values have added to. how increment in fashion no matter how many times user hits add file button should increment previous pattern. eg. have :
xlworksheet.cells[1, 1] = combobox2.text; xlworksheet.cells[1, 2] = textbox5.text; xlworksheet.cells[1, 3] = textbox2.text; xlworksheet.cells[1, 4] = combobox3.text; xlworksheet.cells[1, 5] = textbox3.text; xlworksheet.cells[1, 6] = combobox1.text; upon clicking button, how make follow pattern:
xlworksheet.cells[2, 1] = combobox2.text; xlworksheet.cells[2, 2] = textbox5.text; xlworksheet.cells[2, 3] = textbox2.text; xlworksheet.cells[2, 4] = combobox3.text; xlworksheet.cells[2, 5] = textbox3.text; xlworksheet.cells[2, 6] = combobox1.text;
i suppose use excel object through microsoft.office.interop.excel reference. have modify code follows
private void button8_click(object sender, eventargs e) { microsoft.office.interop.excel.application xlapp; //declare //excel object try { xlapp = (microsoft.office.interop.excel.application)system.runtime.interopservices.marshal.getactiveobject("excel.application"); } catch (exception ee) { xlapp = new microsoft.office.interop.excel.application(); if (xlapp == null) { messagebox.show("excel not installed!!"); return; } } if (xlapp == null) { messagebox.show("excel not installed!!"); return; } object misvalue = system.reflection.missing.value; microsoft.office.interop.excel.workbook xlworkbook=xlapp.workbooks.add(misvalue); try { xlworkbook = xlapp.workbooks.open(@"cross_check.xls");//, } catch (exception ex) { ;// } microsoft.office.interop.excel.range range; microsoft.office.interop.excel.worksheet xlworksheet = (microsoft.office.interop.excel.worksheet)xlworkbook.worksheets.get_item(1); int rownum = 1; int max_rows=30000; //you may define own limit bool written = true; range = xlworksheet.usedrange; while ((written) && (rownum<max_rows)) { var test = (range.cells[rownum, 1] microsoft.office.interop.excel.range).value2; if (test != null) { rownum++; } else { written = false; } } if (written == false) { xlworksheet.cells[rownum, 1] = combobox2.text; xlworksheet.cells[rownum, 2] = textbox5.text; xlworksheet.cells[rownum, 3] = textbox2.text; xlworksheet.cells[rownum, 4] = combobox3.text; xlworksheet.cells[rownum, 5] = textbox3.text; xlworksheet.cells[rownum, 6] = combobox1.text; } xlapp.displayalerts = false; //disables prompts xlworkbook.saveas(@"cross_check.xls", microsoft.office.interop.excel.xlfileformat.xlworkbooknormal, misvalue, misvalue, misvalue, misvalue, microsoft.office.interop.excel.xlsaveasaccessmode.xlshared, misvalue, misvalue, misvalue, misvalue, misvalue); xlapp.displayalerts = true; // xlworkbook.close(true, misvalue, misvalue); xlapp.quit(); marshal.releasecomobject(xlworksheet); marshal.releasecomobject(xlworkbook); marshal.releasecomobject(xlapp); messagebox.show("excel file created/updated succcessfully"); } at first steps of code check excel object, if running, performed. if so, not create new excel object use 1 running in system. workbook created or updated. when saving it, must saved with
microsoft.office.interop.excel.xlsaveasaccessmode.xlshared in order able reopen , update it.
to speed data entering modified code may use quoted.
you should add additional button e.g. button9 , use quoted code in click event necessary modifications made button8 enter data. also, have declare variables xlapp,xlworkbook,xlworksheet etc global , public in following code,
............ public bool startd = false; public int rownum; public int max_rows = 30000;//you may define own limit here public bool isfirsttime = true; public string oldbtnfiletext; public string oldbtndatatext; public microsoft.office.interop.excel.application xlapp; public microsoft.office.interop.excel.workbook xlworkbook; public microsoft.office.interop.excel.worksheet xlworksheet; public microsoft.office.interop.excel.range range; public object misvalue; private void button8_click(object sender, eventargs e) { if (startd == false) { return; } if (isfirsttime == true) { bool written = true; int rnum = 1; if (xlworksheet!=null) { range=xlworksheet.usedrange; while ((written) && (rnum < max_rows)) { var test = (range.cells[rnum, 1] microsoft.office.interop.excel.range).value2; if (test != null) { rnum++; } else { written = false; } } if (written == false) { rownum = rnum; isfirsttime = false; } else { messagebox.show("the current worksheet full"); return; } } } if (xlworksheet!=null) { xlworksheet.cells[rownum, 1] = combobox2.text; xlworksheet.cells[rownum, 2] = textbox5.text; xlworksheet.cells[rownum, 3] = textbox2.text; xlworksheet.cells[rownum, 4] = combobox3.text; xlworksheet.cells[rownum, 5] = textbox3.text; xlworksheet.cells[rownum, 6] = combobox1.text; rownum++; } } private void button9_click(object sender, eventargs e) { if (startd == false) { try { xlapp = (microsoft.office.interop.excel.application)system.runtime.interopservices.marshal.getactiveobject("excel.application"); } catch (exception ee) { xlapp = new microsoft.office.interop.excel.application(); if (xlapp == null) { messagebox.show("excel not installed!!"); return; } } if (xlapp == null) { messagebox.show("excel not installed!!"); return; } misvalue = system.reflection.missing.value; xlworkbook = xlapp.workbooks.add(misvalue); try { xlworkbook = xlapp.workbooks.open(@"cross_check.xls");//, } catch (exception ex) { ;// } xlworksheet = (microsoft.office.interop.excel.worksheet)xlworkbook.worksheets.get_item(1); oldbtnfiletext = button9.text.tostring(); button9.text = "file ready accept data"; oldbtndatatext = button1.text.tostring(); button8.text = "enter data"; startd = true; } else { xlapp.displayalerts = false; xlworkbook.saveas(@"cross_check.xls", microsoft.office.interop.excel.xlfileformat.xlworkbooknormal, misvalue, misvalue, misvalue, misvalue, microsoft.office.interop.excel.xlsaveasaccessmode.xlshared, misvalue, misvalue, misvalue, misvalue, misvalue); xlapp.displayalerts = true; xlworkbook.close(true, misvalue, misvalue); xlapp.quit(); marshal.releasecomobject(xlworksheet); marshal.releasecomobject(xlworkbook); marshal.releasecomobject(xlapp); messagebox.show("excel file created/updated succcessfully"); startd = false; button9.text = oldbtnfiletext; //restore initial captions button8.text = oldbtndatatext;//... } } // hope these can useful.
Comments
Post a Comment