Export the information to execl.

There is a small module in the project. It is to output the information in the database to execl. At first, because this part only has one or two files. It is not encapsulated. Later there were more files.

We can see that code like execl is opened in many places. It is encapsulated (this part of code is used for testing now .).

First, encapsulate the code for opening and writing information to the execl file.

Public class datafile {Public String savepath {Get; set;} Public String srcpathname {Get; set;} Public String savename {Get; set;} Public String onaction (Params action <Microsoft. office. interOP. excel. worksheet> [] Actions) {directoryinfo Path = new directoryinfo (savepath); If (path. getfiles (). where (P => P. name = savename ). count ()> 0) savename + = datetime. now. tostring ("yyyymmddhhmmss"); string savepathname = ""; if (savepath. endswith (@ "\") savepathname = savepath + savename + ". xls "; else savepathname = savepath + @" \ "+ savename + ". xls "; Microsoft. office. interOP. excel. application myexcel = new Microsoft. office. interOP. excel. application (); myexcel. workbooks. open (srcpathname, 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); Microsoft. office. interOP. excel. workbook wbook = myexcel. workbooks [1]; Microsoft. office. interOP. excel. worksheet wsheet = NULL; For (INT I = 0; I <actions. length; I ++) {wsheet = (Microsoft. office. interOP. excel. worksheet) myexcel. worksheets. get_item (I + 1); Actions [I] (wsheet);} wbook. saveas (savepathname, type. missing, type. missing, type. missing, type. missing, type. missing, Microsoft. office. interOP. excel. xlsaveasaccessmode. xlnochange, type. missing, type. missing, type. missing, type. missing, type. missing); myexcel. quit (); kill (myexcel); myexcel = NULL; GC. collect (); Return savepathname;} public void kill (Microsoft. office. interOP. excel. application Excel) {If (Excel = NULL) return; intptr T = new intptr (Excel. hwnd); int K = 0; getwindowthreadprocessid (T, out k); process P = process. getprocpolicyid (k); p. kill (); // close process k} [dllimport ("user32.dll", charset = charset. auto)] public static extern int getwindowthreadprocessid (intptr hwnd, out int ID );}

This part of the code is used to import the data set to execl. Here I will explain why C # In vs2008 does not support the default parameter !! (I don't know if I'm out .)

Public static class propertyhelper {public static list <t> datatabletolist <t> (datatable table) where T: New () {propertyinfo [] PS = typeof (t ). getproperties (); List <t> TS = new list <t> (); foreach (datarow row in table. rows) {T = new T (); // t = activator. createinstance <t> (); foreach (propertyinfo P in PS) {P. setvalue (T, row [p. name], null);} ts. add (t);} return ts;} public static datatable Li Sttodatatable <t> (this ienumerable <t> data) {propertyinfo [] PS = typeof (t ). getproperties (); datatable table = new datatable (); foreach (propertyinfo P in PS) {table. columns. add (P. name, P. propertytype);} foreach (t in data) {datarow ROW = table. newrow (); foreach (propertyinfo P in PS) {row [p. name] = P. getvalue (T, null);} table. rows. add (ROW) ;}return table ;}public static void compressfile (String sourcefile, string destinationfile, string desname) {If (file. exists (sourcefile) = false) throw new filenotfoundexception (); filestream reader = NULL; filestream writer = NULL; memorystream MS = NULL; try {// file stream reader = file. open (sourcefile, filemode. open); writer = file. create (destinationfile); // compression-related stream MS = new memorystream (); gzipstream zipstream = new gzipstream (MS, compressionmode. Compress, true); // write data to the compressed stream byte [] sourcebuffer = new byte [reader. length]; reader. read (sourcebuffer, 0, sourcebuffer. length); zipstream. write (sourcebuffer, 0, sourcebuffer. length); // You must disable zipstream before reading the memory stream. close (); zipstream. dispose (); // read data MS from the memory stream. position = 0; // Note: Do not omit this sentence byte [] header = new byte [10]; Ms. read (header, 0, 10); header [3] = 8; // contains the file name information byte [] fielcontent = new byte [Ms. length -10]; Ms. read (fielcontent, 0, fielcontent. length); byte [] filename = system. text. encoding. default. getbytes (desname); writer. write (header, 0, header. length); writer. write (filename, 0, filename. length); writer. writebyte (0); writer. write (fielcontent, 0, fielcontent. length);} catch (applicationexception ex) {new exception ("An error occurred while compressing the file! "+ Ex. message);} finally {// make sure we allways close all streams Ms. close (); Ms. dispose (); // close and release the file stream writer. close (); writer. dispose (); reader. close (); reader. dispose () ;}# region "ienumerable export execl" public static void enumtoexel <t> (this ienumerable <t> data, Microsoft. office. interOP. excel. worksheet wsheet) {data. enumtoexel <t> (wsheet, 1, 1);} public static void enumtoexel <t> (this ienumera Ble <t> data, Microsoft. office. interOP. excel. worksheet wsheet, int top, int left) {data. enumtoexel <t> (wsheet, top, left, false);} public static void enumtoexel <t> (this ienumerable <t> data, Microsoft. office. interOP. excel. worksheet wsheet, int top, int left, bool ishavecoulumn) {data. enumtoexel <t> (wsheet, top, left, ishavecoulumn, null);} public static void enumtoexel <t> (this ienumerable <t> data, Microsoft. office. interOP. excel. worksheet wsheet, int top, int left, bool ishavecoulumn, string [] removeproperty) {list <propertyinfo> PS = typeof (t ). getproperties (). tolist (); If (removeproperty! = NULL) {foreach (string pro in removeproperty) {ps. remove (PS. firstordefault (P => P. name. toupper () = pro. toupper () ;}} int ntop = top; int nleft = left; If (ishavecoulumn) {foreach (propertyinfo info in PS) {wsheet. cells [ntop, nleft] = info. name; nleft ++;} ntop ++;} foreach (t in data) {nleft = left; foreach (propertyinfo info in PS) {wsheet. cells [ntop, nleft] = info. getvalue (T, null ); Nleft ++;} ntop ++; }}# endregion "" # region "datatable export execl" public static void enumtoexel (Microsoft. office. interOP. excel. worksheet wsheet, datatable data) {enumtoexel (wsheet, Data, 1, 1, false, null);} public static void enumtoexel (Microsoft. office. interOP. excel. worksheet wsheet, datatable data, int top, int left) {enumtoexel (wsheet, Data, top, left, false, null);} public static void Enum Toexel (Microsoft. office. interOP. excel. worksheet wsheet, datatable data, int top, int left, bool ishavecoulumn) {enumtoexel (wsheet, Data, top, left, ishavecoulumn, null);} public static void enumtoexel (Microsoft. office. interOP. excel. worksheet wsheet, datatable data, int top, int left, bool ishavecoulumn, string [] removecoulumn) {If (removecoulumn! = NULL) {foreach (string pro in removecoulumn) {int ncount = data. columns. count; For (INT I = 0; I <ncount; I ++) {string coulumn = data. columns [I]. columnname. toupper (); If (coulumn = pro. toupper () data. columns. remove (data. columns [I]) ;}}int ntop = top; int nleft = left; If (ishavecoulumn) {foreach (datacolumn info in data. columns) {wsheet. cells [ntop, nleft] = info. columnname; nleft ++ ;} Ntop ++;} foreach (datarow t in data. rows) {nleft = left; foreach (datacolumn info in data. columns) {wsheet. cells [ntop, nleft] = T [info]; nleft ++;} ntop ++; }}# endregion public static void write <t> (t s) {system. reflection. propertyinfo [] MS = typeof (t ). getproperties (); foreach (propertyinfo minfo in MS) {If (minfo. getvalue (S, null )! = NULL) {string value = minfo. getvalue (S, null ). tostring (); console. foregroundcolor = consolecolor. yellow; console. write (minfo. name); console. foregroundcolor = consolecolor. white; console. write (":"); console. foregroundcolor = consolecolor. green; console. write (value); console. write ("") ;}} console. writeline ();}}

The following describes how to import data correctly.

        public void TestFun()        {            DataFile ListBomPart = new DataFile();            ListBomPart.SaveName = "TestFile";            ListBomPart.SavePath = Directory.GetCurrentDirectory();            ListBomPart.SrcPathName = ConfigurationSettings.AppSettings["TestFileName"];            string resultfilename = ListBomPart.OnAction(                p => Db.TableOne.ToList().EnumToExel(p, 2, 1, false, new string[] { "ID" })               , t => Db.TableTwo.ToList().EnumToExel(t, 2, 1, false, new string[] { "ID", "Updatedate", "Updatename" }));        }

You may find it useful to change it yourself. If you have better suggestions, please submit them. Thank you.