C # Implementation reads data from the database to Excel

Using third-party components: Npoi to achieve

Go to the official website: http://npoi.codeplex.com/The download requires the introduction of a DLL (you can select the. net2.0 or. net4.0 dll), and then add references to the Web site. With Npoi, you can read and write Word/excel documents on a machine that does not have Office installed or the appropriate environment.

Create an entity class:

[Table ("Customer") ]     Public classCustomer {[Key] Public intId {Get;Set; }  Public stringFirstName {Get;Set; }  Public stringLastName {Get;Set; }  Public intAge {Get;Set; }  Public intGender {Get;Set; } }

Create a new class of customer

Create a class to implement reading data from list<customer> to Excel

 Public classexportToExcel { Public voidExportcustomertoexcel (Stream stream, ilist<customer>customerlist) {Xssfworkbook WorkBook=NewXssfworkbook (); Isheet WorkSheet= Workbook.createsheet ("Customer");            IRow Currrow;            Icell Currcell; Worksheet.createfreezepane (0,1,0,1); //Excel Header            varProperties =New string[] {"Id","FirstName","LastName"," Age","Gender" }; Icellstyle Styleheader=Getnpoiexcelheaderstyle (WorkBook); Currrow= Worksheet.createrow (0);  for(intI=0; I < properties. Length; i++) {Currcell=Currrow.createcell (i);                Currcell.setcellvalue (Properties[i]); Currcell.cellstyle=Styleheader; }            //the body of Excel            introw =1; intCol =0; foreach(varCustomerinchcustomerlist) {Col=0; Currrow=worksheet.createrow (row); Currrow.createcell (COL). Setcellvalue (Customer.                ID); Col++; Currrow.createcell (COL). Setcellvalue (Customer.                FirstName); Col++; Currrow.createcell (COL). Setcellvalue (Customer.                LastName); Col++; Currrow.createcell (COL). Setcellvalue (Customer.                Age); Col++; Currrow.createcell (COL). Setcellvalue (Customer.                Gender); Col++; Row++;        } workbook.write (stream); }        Privateicellstyle Getnpoiexcelheaderstyle (Iworkbook workbook) {Icellstyle Styleheader=workbook.            Createcellstyle (); IFont Fontheader=workbook.            CreateFont (); Fontheader.boldweight= ( Short) Fontboldweight.bold;            Styleheader.setfont (Fontheader); returnStyleheader; }    }

reading data from list to Excel

Implement read data from the database in the main function method, and call the Exportcustomertoexcel method in the exportToExcel class to write data to Excel

classProgram {Static voidMain (string[] args) {            stringFilePath =@"E:\Customer_Test.xlsx"; #regionReading data from a databaseIList<Customer> customerlist =NewList<customer>(); Codefirstdbcontext Context=NewCodefirstdbcontext (); varCustomer =context.            Customer.tolist (); #endregionexportToExcel Export=NewexportToExcel (); MemoryStream Ms=NewMemoryStream (); Export.                       Exportcustomertoexcel (MS, customer); using(FileStream fs =NewFileStream (@"E:\Customer.xlsx", Filemode.create,fileaccess.write)) {                byte[] bytes =Ms.                ToArray (); Fs. Write (Bytes,0, Bytes.                Length); Fs.            Flush ();        } console.readkey (); }    }

Main function

Read data from the database into Excel, already implemented

C # Implementation reads data from the database to Excel