Import dataset to multiple sheet in Excel

Refer to the msdn article:

How to: use com InterOP to create an Excel spreadsheet (C # programming guide)

How to: Use a handle event of Visual C #. Net Excel

 

Code:

 

Private bool writetoexcel (string filepath, dataset DS) {excel. application oxl; excel. _ workbook owb; excel. _ worksheet osheet; excel. range orng; object omissing = system. reflection. missing. value; // start Excel and get application object. oxl = new excel. application (); oxl. visible = false; try {// get a new workbook. owb = (Excel. _ workbook) (oxl. workbooks. open (filepath, omissing, omissing); oxl. displayalerts = false; For (INT I = 0; I <Ds. tables. count; I ++) {osheet = (Excel. worksheet) owb. worksheets. add (omissing, omissing, 1, omissing); osheet. name = DS [I]. tablename. trim (); int rowindex = 1; int colindex = 0; For (Int J = 0; j <Ds. tables [I]. columns. count; j ++) {// Add Table headers going cell by cell. osheet. cells [1, J + 1] = Ds. tables [I]. columns [J]. columnname. trim (); colindex ++;} string maxheadercell = numbertochar (colindex) + "1"; // format A1: D1 as bold, vertical alignment = center. osheet. get_range ("A1", maxheadercell ). font. bold = true; osheet. get_range ("A1", maxheadercell ). verticalalignment = excel. xlvalign. xlvaligncenter; // create an array to multiple values at once. string [,] sanames = new string [Ds. tables [I]. rows. count, DS. tables [I]. columns. count]; for (INT n = 0; n <Ds. tables [I]. rows. count; n ++) {for (INT m = 0; m <Ds. tables [I]. columns. count; m ++) {sanames [n, m] = Ds. tables [I]. rows [N] [Ds. tables [I]. columns [M]. columnname. trim ()]. tostring () ;}rowindex ++;} osheet. get_range ("A2", numbertochar (colindex) + rowindex. tostring ()). value2 = sanames; orng = osheet. get_range ("A1", maxheadercell); orng. entirecolumn. autofit ();} // save and close the workbook owb. close (Excel. xlsaveaction. xlsavechanges, omissing, omissing); system. runtime. interopservices. marshal. releasecomobject (owb); // make sure Excel is visible and give the user control // of Microsoft Excel's life time. // oxl. visible = true; // oxl. usercontrol = true; return true;} catch (exception ex) {MessageBox. show (ex. message); return false;} finally {oxl. quit (); // submit the Excel file system. runtime. interopservices. marshal. releasecomobject (oxl); owb = NULL; osheet = NULL; orng = NULL; oxl = NULL; GC. collect () ;}} private string numbertochar (INT number) {if (1 <= Number & 26> = Number) {int num = Number + 64; system. text. asciiencoding = new system. text. asciiencoding (); byte [] btnumber = new byte [] {(byte) num}; return asciiencoding. getstring (btnumber);} else if (number> 26) {int newnum = Number % 26; int COUNT = Number/26; string Ss = numbertochar (count ); int num = newnum + 64; system. text. asciiencoding = new system. text. asciiencoding (); byte [] btnumber = new byte [] {(byte) num}; return SS + asciiencoding. getstring (btnumber);} return "the number is not in the conversion range ";}