Apache's POI provides API

Apache POI Online api:http://tool.oschina.net/apidocs/apidoc?api=apache-poi

	I. Introduction to POI Apache POI is the Apache Software Foundation's Open Source Library, and POI provides APIs to Java programs to read and write to Microsoft Office format archives. Ii. HSSF profile HSSF is the abbreviation for horrible SpreadSheet format, and through HSSF, you can read, write, and modify Excel files with pure Java code.
	HSSF provides two types of Api:usermodel and Eventusermodel for read operations, the user model and the event-user model. Third, the POI Excel document structure class Hssfworkbook Excel Document Object hssfsheet Excel sheet Hssfrow Excel's line Hssfcell Excel's single Prompt hssffont Excel font hssfname name hssfdataformat date format Hssfheader sheet Head hssffooter sheet tail HSS Fcellstyle cell style hssfdateutil Date Hssfprintsetup print hssferrorconstants error information table Iv. Common methods of operation for Excel 1
			, get the Excel Common Object [C-sharp] View plaincopyprint?   
			Poifsfilesystem Fs=newpoifsfilesystem (New FileInputStream ("D:/test.xls"));  
			Get the Excel Workbook object Hssfworkbook WB = new Hssfworkbook (FS);   
			Get Excel Sheet Object Hssfsheet sheet = wb.getsheetat (0);  
			Gets the Excel worksheet row Hssfrow row = Sheet.getrow (i); Gets the cell of the Excel worksheet that specifies the row Hssfcell cell = Row.getcell ((shORT) (j);
			CellStyle = Cell.getcellstyle ();//Get cell style 2, set up Excel Common Object [C-sharp] View plaincopyprint?     
			Hssfworkbook wb = new Hssfworkbook ();//Create an Excel Workbook object Hssfsheet sheet = wb.createsheet ("new sheet");//Create an Excel sheet object Hssfrow row = Sheet.createrow ((short) 0); Create a row for an Excel worksheet CellStyle = Wb.createcellstyle ();//Create a cell style Row.createcell ((short) 0). Setcellstyle (CellStyle); You create an Excel worksheet that specifies the row of cells Row.createcell ((short) 0). Setcellvalue (1);
			Set the Excel worksheet value 3, set the sheet name and cell contents [C-sharp] View plaincopyprint?          
			Wb.setsheetname (1, "first sheet", hssfcell.encoding_utf_16);      
			Cell.setencoding ((short) 1);  

	Cell.setcellvalue ("cell contents");
			4. Get the number of sheet [c-sharp] View plaincopyprint?
			Wb.getnumberofsheets () 5. Get Sheet Object [C-sharp] View Plaincopyprint according to index?  

	Hssfsheet sheet = wb.getsheetat (0);
			6. Get a valid number of rows [C-sharp] View plaincopyprint?  

	int rowcount = Sheet.getlastrownum ();
			7. The number of valid cells to get a row [C-sharp] View plaincopyprint? RoW.getlastcellnum ();
			8. Cell value type read/write [c-sharp] view plaincopyprint? Cell.setcelltype (hssfcell.cell_type_string);
			Set cell to String type Cell.getnumericcellvalue ();//Read cell contents as numeric type 9, set column width, row height [c-sharp] View plaincopyprint?      
			Sheet.setcolumnwidth (short) column, (short) width);    

	Row.setheight ((short) height);
			10. Add area, merge cells [c-sharp] view plaincopyprint? Region region = new Region ((short) Rowfrom, (short) columnfrom, [short] rowto, (short) columnto);// Merge Columnfrom column sheet.addmergedregion (region) from line Rowfrom,//to Rowto line Columnto area//Get all zones sheet.getnum
			Mergedregions () 11, save Excel File [C-sharp] view plaincopyprint?   
			FileOutputStream fileout = new FileOutputStream (path);  
	 
	Wb.write (fileout);
			12. Returns the string value [C-sharp] View Plaincopyprint According to the different properties of the cell      
				Public String Getcellstringvalue (Hssfcell cell) {string cellvalue = "";   
			Switch (Cell.getcelltype ()) {case hssfcell.cell_type_string://String type	    Cellvalue = Cell.getstringcellvalue (); if (Cellvalue.trim (). Equals ("") | |      
				    Cellvalue.trim (). Length () <=0) cellvalue= "";      
				Break      
				    Case Hssfcell.cell_type_numeric://numeric type Cellvalue = String.valueof (Cell.getnumericcellvalue ());      
				Break      
				    Case Hssfcell.cell_type_formula://Formula Cell.setcelltype (Hssfcell.cell_type_numeric);      
				    Cellvalue = string.valueof (Cell.getnumericcellvalue ());      
				Break      
				    Case Hssfcell.cell_type_blank:cellvalue= "";      
				Break      
				Case HSSFCell.CELL_TYPE_BOOLEAN:break;      
				Case HSSFCell.CELL_TYPE_ERROR:break;      
				Default:break;      
			    } return cellvalue;
			} 13, commonly used cell border format [c-sharp] View plaincopyprint?      
			Hssfcellstyle style = Wb.createcellstyle (); Style.setborderbottom (hssfcellstyle.border_dotted);//Lower border sTyle.setborderleft (hssfcellstyle.border_dotted);//Left Border style.setborderright (Hssfcellstyle.border_thin);//Right Border
			Style.setbordertop (Hssfcellstyle.border_thin);//Top Border 14, setting font and content location [C-sharp] View plaincopyprint?      
			Hssffont f = wb.createfont (); F.setfontheightinpoints ((short) 11);//font f.setboldweight (hssffont.boldweight_normal);//Bold Style.setfon      
			T (f); Style.setalignment (hssfcellstyle.align_center);//center style.setverticalalignment (hssfcellstyle.vertical_center)      
			;//center style.setrotation (short rotation);//The rotation angle of the cell contents Hssfdataformat df = Wb.createdataformat (); Style1.setdataformat (Df.getformat ("0%"));//sets the cell data format Cell.setcellformula (string);//Set formula S for cells
			Tyle.setrotation (short rotation);//The rotation angle of the cell contents 15, insert Picture [c-sharp] View plaincopyprint? First put the read in a bytearrayoutputstream in order to produce bytearray bytearrayoutputstream bytearrayout = new BytearrayoUtputstream ();      
			      BufferedImage bufferimg = Imageio.read (New File ("ok.jpg"));      
			Imageio.write (bufferimg, "JPG", bytearrayout);       
			Read into an Excel template FileInputStream fos = new FileInputStream (filepathname+ "/stencil.xlt");      
			FS = new Poifsfilesystem (FOS);      
			Create a working thin hssfworkbook wb = new Hssfworkbook (FS);      
			Hssfsheet sheet = wb.getsheetat (0);      
			Hssfpatriarch Patriarch = Sheet.createdrawingpatriarch ();           
			Hssfclientanchor anchor = new Hssfclientanchor (0,0,1023,255, (short) 0,0, (short) 10,10);    
	 
	Patriarch.createpicture (anchor, Wb.addpicture (Bytearrayout.tobytearray (), hssfworkbook.picture_type_jpeg));
			16. Adjust sheet position [C-sharp] view plaincopyprint?     
			Hssfworkbook wb = new Hssfworkbook ();     
			Hssfsheet sheet = wb.createsheet ("format sheet");     
			Hssfprintsetup PS = Sheet.getprintsetup ();     
			Sheet.setautobreaks (TRUE);     
			Ps.setfitheight ((short) 1); Ps.setfitwidth ((short) 1);
			17. Set the print area [c-sharp] View plaincopyprint?     
			Hssfsheet sheet = wb.createsheet ("Sheet1");    
			
	Wb.setprintarea (0, "$A $: $C $");
			18. Annotated footnote [c-sharp] view plaincopyprint?     
			Hssfsheet sheet = wb.createsheet ("format sheet"); Hssffooter footer = Sheet.getfooter () footer.setright ("page" + hssffooter.page () + "of" + hssffooter.numpages (   
	 
	) );
			19. Empty the row data in the work order and adjust the line position [C-sharp] view plaincopyprint?     
			Hssfworkbook wb = new Hssfworkbook ();     
			Hssfsheet sheet = wb.createsheet ("row sheet");      
			Create various cells and rows for spreadsheet.     
	 
	Shift rows 6-11 on the spreadsheet to the top (rows 0-5) sheet.shiftrows (5, 10,-5);
			20. Select the specified sheet [c-sharp] View plaincopyprint?     
			Hssfsheet sheet = wb.createsheet ("row sheet"); 
	 
	Heet.setselected (TRUE);
			21. Zoom in and out of the worksheet [C-sharp] View plaincopyprint?     
			Hssfsheet Sheet1 = wb.createsheet ("new sheet");   (Sheet1.setzoom); Percent MagnificatiOn 22, head note and footnote [c-sharp] view plaincopyprint?     
			Hssfsheet sheet = wb.createsheet ("new sheet");     
			Hssfheader Header = Sheet.getheader ();     
			Header.setcenter ("Center Header");     
			Header.setleft ("left Header"); Header.setright (Hssfheader.font ("Stencil-normal", "Italic") + hssfheader.fontsize ((short) +) + "right w/stencil-  

	Normal Italic Font and size 16 ");
			23. Custom color [c-sharp] View plaincopyprint?     
			Hssfcellstyle style = Wb.createcellstyle ();     
			Style.setfillforegroundcolor (HSSFColor.LIME.index);     
			Style.setfillpattern (Hssfcellstyle.solid_foreground);     
			Hssffont font = Wb.createfont ();     
			Font.setcolor (HSSFColor.RED.index);     
			Style.setfont (font);     
			
	Cell.setcellstyle (style);
			24. Fill and color settings [C-sharp] View plaincopyprint?     
			Hssfcellstyle style = Wb.createcellstyle ();     
			Style.setfillbackgroundcolor (HSSFColor.AQUA.index);     
			Style.setfillpattern (hssfcellstyle.big_spots); Hssfcell cell = Row.createcell ((short) 1);     
			Cell.setcellvalue ("X");     
			style = Wb.createcellstyle ();     
			Style.setfillforegroundcolor (HSSFColor.ORANGE.index);     
			Style.setfillpattern (Hssfcellstyle.solid_foreground);   
	 
	Cell.setcellstyle (style);
			25. Force Refresh cell formula [C-sharp] View plaincopyprint?    
			Hssfformulaevaluator eval=new Hssfformulaevaluator ((hssfworkbook) WB);     
				private static void Updateformula (Workbook wb,sheet s,int row) {row r=s.getrow (row);     
				Cell C=null;     
				Formulaecaluator Eval=null;     
				if (WB instanceof Hssfworkbook) eval=new hssfformulaevaluator ((hssfworkbook) WB);     
				else if (WB instanceof Xssfworkbook) eval=new xssfformulaevaluator ((xssfworkbook) WB);     
				    for (int i=r.getfirstcellnum (); I c=r.getcell (i);     
				if (C.getcelltype () ==cell.cell_type_formula) Eval.evaluateformulacell (c); }} Description: Formulaevaluator provides the Evaluateformulacell (cell cell) method to calculate the formula to save the result without changing the formula.
		The Evaluateincell (cell cell) method is to calculate the formula and replace the original formula with the result of the calculation, meaning that the cell's type is not Cell.cell_type_formula but cell.cell_type_numberic. 
	 
	Hssfformulaevaluator provides a static method Evaluateallformulacells (Hssfworkbook WB) that calculates all the formulas for an Excel file, which is handy to use. Summary of--------------------------------poi method---------------------------------------------------1. Setting does not display Excel gridlines sheet . Setdisplaygridlines (false); where sheet is sheet Object 2. Set the contents of the Excel cell to wrap Cellstyle.setwraptext (true); CellStyle is workbook CellStyle object, and then sets the CellStyle to the Cell object to wrap, and finally adds "/r/n" to the object to wrap (typically a string). 

	such as Toptile.append ("/r/n" + "cellcontent"); 3. Merging of cells Sheet.addmergedregion (new cellrangeaddress (0, 4, 0, 2)); This example merges 4 rows 2 columns 4. Sets the number of pages for headers and footers Hssfheader Heade 
			    R = Sheet.getheader (); 
			    Header.setcenter ("Center Header"); 
			    Header.setleft ("left Header"); Header.setright (Hssfheader.font ("Stencil-normal", "Italic") + hssfheader.fontsize ((short) +) + "right w/stencil- 

			    Normal Italic Font and size 16 "); HssfFooter Footer = (hssffooter) sheet.getfooter () footer.setright ("page" + hssffooter.page () + "of" + Hssffooter. 

	NumPages ()); 
			
	5. Make a sheet Fit one page Sheet.setautobreaks (true);   

	6. Set the Magnification property (zoom is explicitly a fraction, for example the following 75% uses 3 as the numerator, 4 as the denominator) sheet.setzoom (3,4); 
			  7. Set printing Hssfprintsetup print = (hssfprintsetup) sheet.getprintsetup (); Print.setlandscape (TRUE);//Set Horizontal print Print.setscale ((short) 70);//Set Print zoom 70% print.setpapersize (hssfprintsetup.a4_ PAPERSIZE);//set to A4 paper Print.setlefttoright (TRUE);//Setup Print Order first column, default to Column row Print.setfitheight ((short) 10); 
			  Set Zoom to 10 page high print.setfitwidth ((short) 10), set Zoom to width height sheet.setautobreaks (false);  if (i! = 0 && I% = = 0) sheet.setrowbreak (i);//Set every 30 lines of page printing 8. Repeating rows and columns (setting print titles) Hssfworkbook WB = 
			  New Hssfworkbook (); 
			　Wb.setrepeatingrowsandcolumns (0, 0, 12, 1, 6);//Set 1 to 12 columns, rows 1 to 6 repeat for each page 9. Adjust cell width sheet.setautobreaks (true); Sheet.setcolumnwidth (short) I,coLswidth[i]); 


			 Sets the cell length Sheet.autosizecolumn ((short) i);//automatically adjusts cell length according to length