Excel Import and Export

 

 

Export:

Private void exportexcel () {If (savefiledialog1.showdialog () = dialogresult. OK) {application. doevents (); application. doevents (); this. gridcontrol1.exporttoxlsx (savefiledialog1.filename); application. doevents (); Microsoft. office. interOP. excel. application appexcel = new Microsoft. office. interOP. excel. application (); appexcel. displayalerts = false; // This warning will not occur if the displayalerts attribute is set to false. Microsoft. office. interOP. excel. workbook workbook = appexcel. workbooks. open (savefiledialog1.filename, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing, type. missing); // open Microsoft Excel. office. interOP. excel. sheets sheets = workbook. worksheets; // instance table Microsoft. office. interOP. excel. worksheet worksheet = (Microsoft. office. interOP. excel. worksheet) sheets [1]; // The first table worksheet. columns ["A", type. missing]. columnwidth = 14; worksheet. columns ["B", type. missing]. columnwidth = 5; worksheet. columns ["C", type. missing]. columnwidth = 12; worksheet. columns ["D", type. missing]. columnwidth = 10; worksheet. columns ["E", type. missing]. columnwidth = 12; worksheet. columns ["F", type. missing]. columnwidth = 16; worksheet. columns ["g", type. missing]. columnwidth = 18; worksheet. columns ["H", type. missing]. columnwidth = 7; worksheet. columns ["I", type. missing]. columnwidth = 7; worksheet. columns ["J", type. missing]. columnwidth = 7; worksheet. columns ["K", type. missing]. columnwidth = 7; worksheet. columns ["L", type. missing]. columnwidth = 10; worksheet. columns ["M", type. missing]. columnwidth = 7; worksheet. columns ["N", type. missing]. columnwidth = 7; worksheet. columns ["O", type. missing]. columnwidth = 5; worksheet. columns ["P", type. missing]. columnwidth = 5; worksheet. columns ["Q", type. missing]. columnwidth = 12; worksheet. columns ["r", type. missing]. columnwidth = 12; worksheet. columns ["S", type. missing]. columnwidth = 5; worksheet. columns ["T", type. missing]. columnwidth = 5; worksheet. columns ["u", type. missing]. columnwidth = 5; worksheet. columns ["V", type. missing]. columnwidth = 5; worksheet. columns ["W", type. missing]. columnwidth = 10; worksheet. columns ["X", type. missing]. columnwidth = 10; worksheet. columns ["Y", type. missing]. columnwidth = 10; worksheet. columns ["Z", type. missing]. columnwidth = 8; worksheet. columns ["AA", type. missing]. columnwidth = 10; worksheet. columns ["AB", type. missing]. columnwidth = 8; worksheet. columns ["AC", type. missing]. columnwidth = 10; worksheet. columns ["ad", type. missing]. columnwidth = 8; worksheet. columns ["AE", type. missing]. columnwidth = 8; worksheet. columns ["af", type. missing]. columnwidth = 12; worksheet. columns ["ag", type. missing]. columnwidth = 15; // worksheet. cells [9, "a"]. value = "ASD"; // Microsoft. office. interOP. excel. range firstcolumn = worksheet. get_range ("A1"); // range firstcolumn = (range) xlworksheet. columns [0]; // firstcolumn. entirecolumn. autofit (); workbook. save (); workbook. close (); appexcel. quit (); appexcel = NULL; GC. collect ();}}

 

2014-10-1814: 07: 48

 

Import:

Private void btn_open_click (Object sender, eventargs e) {openfiledialog DLG = new openfiledialog (); DLG. filter = "Excel file | *. XLSX ;*. xls "; if (DLG. showdialog () = dialogresult. OK) {This. txt_filename.text = DLG. filename; excel. application xlapp = new excel. application (); try {excel. workbook workbook = xlapp. workbooks. open (DLG. filename, 0, false, 5, "", "", false, Excel. xlplatform. xlwindows, "", Tru E, false, 0, true, 1, 0); int n = workbook. worksheets. count; cb_sheet.items.clear (); For (INT I = 0; I <n; I ++) {cb_sheet.items.add (Excel. worksheet) workbook. worksheets [I + 1]). name);} preqexcel (DLG. filename);} finally {xlapp. workbooks. close (); xlapp. quit (); xlapp = NULL; GC. collect (); this. overpricerowindex. clear () ;}} private void preqexcel (string path) {string strconn = "provider = micro Soft. ace. oledb.12.0; Data Source = "+ path +"; extended properties = \ "Excel 12.0; HDR = Yes \" "; oledbconnection conn = new oledbconnection (strconn); try {Conn. open ();} catch (Exception error) {MessageBox. show ("Microsoft. ace. oledb.12.0 driver lost or damaged. Please reinstall ODBC driver! "," Tip ", messageboxbuttons. OK, messageboxicon. error); return;} string strexcel = ""; oledbdataadapter mycommand = NULL; dataset DS = NULL; strexcel = string. format ("select * from [{0} $]", this. cb_sheet.text); mycommand = new oledbdataadapter (strexcel, strconn); DS = new dataset (); mycommand. fill (DS); this. gridview1.columns. clear (); this. gridcontrol1.datasource = Ds. tables [0]; Conn. close (); this. gridview1.horzscrollvisibility = devexpress. xtragrid. views. base. scrollvisibility. always; this. gridview1.vertscrollvisibility = devexpress. xtragrid. views. base. scrollvisibility. always; foreach (devexpress. xtragrid. columns. gridcolumn gcol in this. gridview1.columns) {gcol. minwidth = 40 ;}}

 

Excel Import and Export