Export data to an Excel table in C #

Before the PM gave me an automated test case, let me crawl the data on the page to Excel table, just then another people do the system, just see can use Npoi Guide data, try, successfully exported.

Because I compare vegetables, also more lazy, afraid that they forget, today summed up, in case the next use can be consulted.

1. To use Npoi, you first need to install the Npoi package in project.

Right-click Project------>manage NuGet Packages---->search npoi-----> Click on Npoi to search and then click Wait for installation to complete.

2. After the installation is complete, you can see the following 4 assembly under project references:

Npoi;
Npoi. OOXML;
Npoi. Openxml4net;
Npoi. Openxmlformats;

3. Now you can use it in your code ...

The following namespaces are referenced first:

Using Npoi. XSSF. Usermodel;
Using Npoi. Ss. Usermodel;
Using Npoi. HSSF. Usermodel;

Note: POI read Excel has two formats, one is HSSF and the other is XSSF.

HSSF applies to versions prior to 2007, XSSF applies to version 2007 and above.

Here are the main parts of my local test code:

1   Public classXlsxhelper2     {3 4          Public Static stringcreatexlsfile (DataTable DT)5         {6Hssfworkbook Hssfworkbook =NewHssfworkbook ();7Isheet Sheet1 = Hssfworkbook. Createsheet ("table1");8Sheet1. Createfreezepane (1,1,1,1);9 Ten              for(intI=0;i<Ten; i++) One             { ASheet1. Setcolumnwidth (I, -* the); -             } -  the             intRowNum =0; -IRow Headrow =Sheet1. CreateRow (rowNum); -Headrow.heightinpoints = -; -Icellstyle Headstyle =Hssfworkbook. Createcellstyle (); +Headstyle.alignment = Npoi. Ss. UserModel.HorizontalAlignment.Center;//Center -Headstyle.verticalalignment = Verticalalignment.center;//Center Vertically +Headstyle.wraptext =true;//Wrap Line A             /**************** Write column headings *********************/ atHeadrow.createcell (0). Setcellvalue ("ID"); -Headrow.createcell (1). Setcellvalue ("Name"); -Headrow.createcell (2). Setcellvalue (" Age"); -Headrow.createcell (3). Setcellvalue ("Salary"); -  -Sheet1. GetRow (0). Getcell (0). CellStyle =Headstyle; inSheet1. GetRow (0). Getcell (1). CellStyle =Headstyle; -Sheet1. GetRow (0). Getcell (2). CellStyle =Headstyle; toSheet1. GetRow (0). Getcell (3). CellStyle =Headstyle; +  -             /********************* Write field value *******************/ theIcellstyle style =Hssfworkbook. Createcellstyle (); *Style. Fillforegroundcolor =Npoi. HSSF. Util.HSSFColor.Red.Index; $Style. FillPattern =Npoi. Ss. UserModel.FillPattern.Squares;Panax NotoginsengStyle. Fillbackgroundcolor =Npoi. HSSF. Util.HSSFColor.Red.Index; -              for(intKr=0; Kr<dt. rows.count;kr++) the             { +rownum++; AIRow row =Sheet1. CreateRow (rowNum); the                  for(intKc=0; Kc<dt. Columns.count;kc++) +                 { - row. Createcell (KC). Setcellvalue (dt. ROWS[KR][KC]. ToString ()); $                 } $             } -  -             stringFullName =GetFilePath (); the  -             using(FileStream file1 =NewSystem.IO.FileStream (FullName, FileMode.Create, fileaccess.readwrite))Wuyi             { the Hssfworkbook. Write (file1); -             } Wu             returnFullName; -         } About  $          Public Static stringGetFilePath () -         { -             stringFilePath; -             stringExcelpathfolder =configurationmanager.appsettings["folder"]; A             stringfilename ="Excel_"+ DateTime.Now.Ticks +". xls"; +  the             if(! Directory.Exists (Excelpathfolder))//If the folder does not exist, create - directory.createdirectory (excelpathfolder); $  theFilePath =System.IO.Path.Combine (excelpathfolder, filename); the  the             returnFilePath; the         } -}

View Code

Note: As I have encountered the following problem myself, all by the way, reference the namespace using System.Configuration, but use configurationmanager.appsettings["" in the Code The ConfigurationManager error occurs when the current context does not exist. Remember to add System.Configuration in the references under project.

As for the data source, you can build it yourself or get it from the database.

If there is a problem, hope to see the great God pointing. carelessly grateful.  Keep taking notes and don't be lazy. Sister will become the fattest.

Note: See Related Good blog attached address: http://www.cnblogs.com/luxiaoxun/p/3374992.html

Export data to an Excel table in C #