Npoi is a powerful tool for accessing the Excel worksheet

The time has passed, and the recent cases are coming to an end. It is really a headache to say that I am doing some report and Excel work. The brother has a good example. Let me introduce it to you. In fact, it is very easy to handle the Excel template under. NET and insert rows in a row in the template. But I have never found a good solution. If anyone has a solution, I am sorry to help me!

 

In fact, things have to be done, and the project is still waiting to be finished. I can only find it myself. It's easy to get a needle in the sea. However, I have never been killed. Let me know npoi. Although it cannot insert rows (I don't know if it is true ). However, it is faster to generate an Excel file. A large file is generated in seconds. The most important thing is not to install the office. In addition, the exce operation is easier. Let's talk about it now.

 

Here, I am playing with the EXCEL function. I didn't use oledb to handle it, or I used COM components to do it;

Fortunately, with npoi, we have the highest respect (WEEPING) for those who develop such Lib ).

Not much talk, to take a fancy to culture and education and Overview: http://msdn.microsoft.com/zh-tw/ee818993.aspx

Currently, the npoi version has reached 1.2.3,

You can download

 

Examples

Reference component

 

Blank reference name

View Source

Print?

1	using NPOI.HSSF.UserModel;

 

Some Basic Applications

// Create a worksheet </P> <p> 02 This. _ workbook = new hssfworkbook (); </P> <p> 03 // ================================ ========</P> <p> 04 </P> <p> 05 // create a sheet </P> <p> 06 ohssfsheet = (hssfsheet) this. workbook. createsheet (sheetname ); </P> <p> 07 // ================================ ========</P> <p> 08 </P> <p> 09 // create a column </P> <p> 10 orow = (hssfrow) ohssfsheet. createrow (0 ); </P> <p> 11 // ================================ ========</P> <p> 12 </P> <p> 13 // create a memory cell </P> <p> 14 ohssfcell = (hssfcell) row. createcell (colnum ); </P> <p> 15 // ================================ ==========</P> <p> 16 </P> <p> 17 // set the style </P> <p> 18 hssfcellstyle ostyle = NULL; </P> <p> 19 ostyle = (hssfcellstyle) This. workbook. createcellstyle (); </P> <p> 20 </P> <p> 21 // set the background coloring </P> <p> 22 ostyle. fillforegroundcolor = npoi. hssf. util. hssfcolor. brown. index; </P> <p> 23 ostyle. fillpattern = npoi. SS. usermodel. fillpatterntype. solid_foreground; </P> <p> 24 </P> <p> 25 // set the memory storage frame limit </P> <p> 26 ostyle. borderbottom = npoi. SS. usermodel. cellbordertype. thin; </P> <p> 27 ostyle. borderleft = npoi. SS. usermodel. cellbordertype. thin; </P> <p> 28 ostyle. borderright = npoi. SS. usermodel. cellbordertype. thin; </P> <p> 29 ostyle. bordertop = npoi. SS. usermodel. cellbordertype. thin; </P> <p> 30 ostyle. bottombordercolor = npoi. hssf. util. hssfcolor. black. index; </P> <p> 31 ostyle. leftbordercolor = npoi. hssf. util. hssfcolor. black. index; </P> <p> 32 ostyle. rightbordercolor = npoi. hssf. util. hssfcolor. black. index; </P> <p> 33 ostyle. topbordercolor = npoi. hssf. util. hssfcolor. black. index; </P> <p> 34 </P> <p> 35 // set the text encoding </P> <p> 36 ostyle. alignment = npoi. SS. usermodel. horizontalalignment. center; </P> <p> 37 ostyle. verticalalignment = npoi. SS. usermodel. verticalalignment. center; </P> <p> 38 </P> <p> 39 // set </P> <p> 40 ohssfcell. cellstyle = ostyle; </P> <p> 41 // ================================ =================================</P> <p> 42 </ p> <p> 43 // set the memory size </P> <p> 44 ohssfsheet. setcolumnwidth (N1, 24*100 ); </P> <p> 45 // ================================ ========</P> <p> 46 </P> <p> 47 // specify the memory type </P> <p> 48 actcell. setcelltype (celltype. string); </P> <p> 49 actcell. setcelltype (celltype. numeric ); </P> <p> 50 // ================================ =================================</P> <p> 51 </ p> <p> 52 // set the value of the memory cell </P> <p> 53 actcell. setcellvalue ("missing partition"); </P> <p> 54 actcell. setcellvalue (0); </P> <p> 55 actcell. setcellvalue (convert. todouble (value )); </P> <p> 56 // ================================ ===============< br/>

 

Additional usage: The sample code returned in the following example can be exploited.

 

 

Maybe you can do this.

 

Note:

1,

Do not generate too many cellstyle rows (ex: ostyle = (hssfcellstyle) This. Workbook. createcellstyle ();)

The npoi component may not work properly.

As shown in the preceding figure, the style in the middle area is different from that in the upper and lower areas (box and text)

In fact, the styles of the two regions are set to be consistent. Why does this problem occur?

When I was using npoi, I accidentally set the style of each cell to two XD

When reading the response message, the response is "beyond limit" (although it cannot be Chinese, the ghost knows what this means)

Fortunately, after I review code, I changed the program history and it was OK.

2,

The maximum number of columns in a sheet is 65536.

The maximum number of bytes of a sheet is 256.

Http://tw.myblog.yahoo.com/vincent-excel/article? Mid = 988 & Prev = 989 & l = A & FID = 27

 

In the case of failure:

Lib: npoi 1.2.3 binary package.zip

Source code: npoi 1.2.3 source package.zip

Sample Code: npoi. Examples 20100802 package.zip

 

In addition, 1.2 and 1.2.3 have different areas as follows:

Border example	Corresponding static Value
	Hssfcellstyle. border_dotted
	Hssfcellstyle. border_hair
	Hssfcellstyle. border_dash_dot_dot
	Hssfcellstyle. border_dash_dot
	Hssfcellstyle. border_dashed
	Hssfcellstyle. border_thin
	Hssfcellstyle. border_medium_dash_dot_dot
	Hssfcellstyle. border_slanted_dash_dot
	Hssfcellstyle. border_medium_dash_dot
	Hssfcellstyle. border_medium_dashed
	Hssfcellstyle. border_medium
	Hssfcellstyle. border_thick
	Hssfcellstyle. border_double

Border example	Corresponding static Value
	Hssfcellstyle. border_dotted
	Hssfcellstyle. border_hair
	Hssfcellstyle. border_dash_dot_dot
	Hssfcellstyle. border_dash_dot
	Hssfcellstyle. border_dashed
	Hssfcellstyle. border_thin
	Hssfcellstyle. border_medium_dash_dot_dot
	Hssfcellstyle. border_slanted_dash_dot
	Hssfcellstyle. border_medium_dash_dot
	Hssfcellstyle. border_medium_dashed
	Hssfcellstyle. border_medium
	Hssfcellstyle. border_thick
	Hssfcellstyle. border_double