C # export Excel

Using system; using system. collections. generic; using system. LINQ; using system. text; using system. data; using system. data. sqlclient; using system. windows. forms; using Excel = Microsoft. office. interOP. excel; using system. reflection; using system. io; namespace mypracticeconsole {public class rollrealexcel {static string rowtocolumnsql = "declare @ s varchar (8000) \ r \ n" + "set @ s = 'select cast (date as datetime) As date '\ r \ n "+" select "+" @ s = @ s +', ['+. username + '] = max (case when username = ''+. username + ''then attendancestate else 0 end) '"+" from (select username from userinfo where firm = 0) A \ r \ n "+" set @ s = @ s + 'from record where date like' % {0}-{1} % 'group by date order by date '\ r \ n "+" Exec (@ s) "; // static string sqlconnectionstring =" Data Source = samewaydev02; database = attendanceinfo; user id = sa; PA Ssword = sa; "; static string sqlconnectionstring =" Data Source = 192.168.1.111; database = attendanceinfo; user id = sa; Password = sa; "; Private Static datatable getdata () {string SQL = string. format (rowtocolumnsql, datetime. today. addmonths (-1 ). year, datetime. today. addmonths (-1 ). month); sqlconnection connection = new sqlconnection (sqlconnectionstring); connection. open (); sqldataadapter adapter = new sqldata Adapter (SQL, connection); adapter. selectcommand. executenonquery (); datatable table = new datatable (); adapter. fill (table); connection. close (); datacolumn [] keys = new datacolumn [1]; keys [0] = table. columns [0]; table. primarykey = keys; return table;} Private Static double getmark (string state) {int tempmark = int. parse (State); double acturalmark =-5.00; Switch (tempmark) {Case 0: acturalmark = -1.00; break; Case 1: acturalmark = 1.00; break; Case 21: acturalmark = 0.85; break; Case 22: acturalmark = 0.70; break; Case 23: acturalmark = 0.50; break; Case 41: acturalmark = 0.85; break; Case 42: acturalmark = 0.70; break; Case 43: acturalmark = 0.50; break; Case 51: acturalmark = 0.70; break; case 52: acturalmark = 0.55; break; Case 53: acturalmark = 0.35; break; Case 54: acturalmark = 0.55; Break; Case 55: acturalmark = 0.40; break; Case 56: acturalmark = 0.20; break; Case 57: acturalmark = 0.35; break; Case 58: acturalmark = 0.20; break; default: acturalmark = 0.00; break;} return acturalmark;} public static void exportexcel () {datatable table = getdata (); savefiledialog = new savefiledialog (); savefiledialog. filter = "execl files (*. XLS) | *. xls "; savefiledialog. f Ilterindex = 0; savefiledialog. restoredirectory = true; savefiledialog. title = "Export an Excel file to"; if (savefiledialog. showdialog () = dialogresult. OK) {stream mystream; mystream = savefiledialog. openfile (); streamwriter Sw = new streamwriter (mystream, system. text. encoding. getencoding ("gb2312"); datetime lastmonth = datetime. today. addmonths (-1); int daycount = datetime. daysinmonth (lastmonth. year, lastm Onth. month); int personcount = table. columns. count-1; try {sqlconnection connection = new sqlconnection (sqlconnectionstring); connection. open (); // Total Number of days double [] summarks = new double [personcount]; String title = string. format ("{0} year {1} month attendance statistics", lastmonth. year, lastmonth. month); Sw. writeline (title); string secondtitle = string. format ("actual attendance days: {0}", table. rows. count); Sw. writeline (secondtitle); // write the title For (INT I = 0; I <table. columns. count; I ++) {if (I = 0) {SW. write ("date");} else {SW. write (table. columns [I]. columnname);} SW. write ("\ t");} SW. writeline (); // write content int rowcount = 1; for (Int J = 1; j <daycount + 1; j ++) {datetime time = datetime. parse (string. format ("{0}-{1}-{2}", lastmonth. year, lastmonth. month, j); datarow ROW = table. rows. find (time); If (row! = NULL) {for (int K = 0; k <table. columns. count; k ++) {If (k = 0) {SW. write (j);} else {double mark = getmark (table. rows [rowcount-1] [K]. tostring (); string markstring = mark. tostring ("F2"); If (markstring = "1.00") {SW. write (markstring); summarks [k-1] + = mark;} else {string SQL = string. format ("select missreason from record where date = '{0}-{1}-{2}' and username = '{3}'", lastmonth. year, Lastmonth. month, J, table. columns [K]. columnname); sqlcommand command = new sqlcommand (SQL, connection); object A = command. executescalar (); string missreason = A = NULL? String. empty:. tostring (); If (missreason = string. empty) {SW. write ("0.00");} else {SW. write (markstring + missreason); summarks [k-1] + = mark ;}} SW. write ("\ t");} rowcount ++;} else {for (int K = 0; k <table. columns. count; k ++) {If (k = 0) {SW. write (j);} else {SW. writes ("0.00");} SW. write ("\ t") ;}} SW. writeline ();} SW. write ("summary \ t"); For (INT I = 0; I <personcount; I ++) {SW. write (summarks [I]. tostring ("F2"); Sw. write ("\ t");} SW. writeline (); Sw. write ("personal leave plus 1.0, sick leave plus 1.5 wedding and funeral leave within the specified time as well as Field Service, relocation, travel plus 2.0 \ t"); Sw. writeline (); Sw. close (); mystream. close (); connection. close ();} catch (exception ee) {MessageBox. show (EE. message); return;} finally {SW. close (); mystream. close ();}}}}}