UGG Outlet POI Operation Excel documents

POI operation 2011-01-07 11:03
an Excel document. POI Profile
Jakarta POI is the apache subproject, the goal is to deal ole2 object. It provides a set of manipulation of the Java API
Windows document now HSSF interfaces are more mature to handle MS Excel (97-2002) object. It's not like we just did not use the csv format generated by the Excel conversion thing, but really the Excel object, you can control some of the properties such as sheet, cell, and so on.
two. HSSF Profile
HSSF is Horrible SpreadSheet Format abbreviation, that HSSF may name a little funny,UGG Outlet, in essence it is a very serious, formal API. By HSSF, you can use pure Java code to read, write, and modify Excel files.
HSSF for the read operation provides two types of API: usermodel and eventusermodel, the The former is well understood, the latter is more abstract, but the operation is more efficient.
three. start coding
1.
preparation requirements: JDK 1.4 + POI development package to the latest POI can
kit
2.EXCEL structure
HSSFWorkbook excell Document Object Description
HSSFSheet excell form
HSSFRow excell line
HSSFCell excell lattice unit
HSSFFont excell font
HSSFName name
HSSFDataFormat date format in
only in poi1.7 the following two:
HSSFHeader sheet head
HSSFFooter sheet tail
and style of this style
HSSFCellStyle cell auxiliary operations including
HSSFDateUtil
date
HSSFPrintSetup print
HSSFErrorConstants error message Table
3. specific usage examples (using usermodel)
how to read Excel
read Excel files, the first object to generate a POIFSFileSystem by POIFSFileSystem object to construct an HSSFWorkbook, the HSSFWorkbook object represents the Excel document. The following code reads the generated Excel file to write the message string:
code POIFSFileSystem fs = newPOIFSFileSystem (new FileInputStream (e) {e.printStackTrace ();} HSSFSheet sheet = wb.getSheetAt (0); HSSFRow row = sheet.getRow (0); HSSFCell cell = row.getCell ((short0); String msg = cell.getStringCellValue (); POIFSFileSystem fs = newPOIFSFileSystem (new FileInputStream (HSSFRow row = sheet.getRow (0); HSSFCell cell = row.getCell ((short0); String msg = cell.getStringCellValue (); how to write excel,
to excel in the first form the first line of the first unit cell values ​​written in row = sheet.getRow (0); HSSFCell cell = row.getCell ((short0); cell.setCellValue (); wb.write (fileOut); fileOut.close (); 4. refer to the document
POI page:,
beginners how to quickly get started using POI HSSF
code examples there are many examples of code, you can very easy to get started.
POI intermediate should
1, through workbook
code / / load the source file POIFSFileSystem fs = new POIFSFileSystem (new FileInputStream (filePath)); HSSFWorkbook wb = new HSSFWorkbook (fs); forint i = 0; i wb.getNumberOfSheets (); i + +) {HSSFSheet sheet = wb.getSheetAt (i); forint i = sheet.getFirstRowNum (); i sheet.getLastRowNum (); i + +) {HSSFRow row = sheet.getRow (i); if (row! = null) {. operating }}}// target file FileOutputStream fos = new FileOutputStream (objectPath); / / write file swb.write (fos); fos.close (); 2,UGG Outlet, get columns, and cells
code HSSFRow row = sheet.getRow (i); HSSFCell cell = row.getCell ((short) j); 3, set the sheet name and cell contents for the Chinese
code wb.setSheetName ( n, cell.setCellType (HSSFCell.CELL_TYPE_NUMERIC); cell.getNumericCellValue ()
5, set the column width and row height
code sheet.setColumnWidth ((short) column, (short) width); row.setHeight ((short ) height);
6, add the area code, merged cells
Region region = new Region ((short) rowFrom, (short) columnFrom, (short) rowTo, (short) columnTo); sheet.addMergedRegion ( region); / / get all the regional sheet.getNumMergedRegions () 7, commonly used methods
different attributes of the cell returns the string value
code public String getCellStringValue (HSSFCell cell) {String cellValue = (cell.getCellType ()) {case HSSFCell.CELL_TYPE_STRING: cellValue = cell.getStringCellValue (); if (cellValue.trim (). equals (= getNumericCellValue ()); break; case HSSFCell.CELL_TYPE_BLANK: cellValue = frame format
dashed HSSFCellStyle.BORDER_DOTTED
solid line HSSFCellStyle.BORDER_THIN
code publicstatic HSSFCellStyle getCellStyle (short type) {HSSFWorkbook wb = new HSSFWorkbook (); HSSFCellStyle style = wb.createCellStyle (); style.setBorderBottom ( type); / / bottom border style.setBorderLeft (type); / / left border style.setBorderRight (type); / / right border style.setBorderTop (type); / / on the border return style;}
9, set fonts and content location
code HSSFFont f = wb.createFont (); f.setFontHeightInPoints ((short11); / / font f.setBoldweight (HSSFFont.BOLDWEIGHT_NORMAL); / / bold style.setFont (f); style. setAlignment (HSSFCellStyle.ALIGN_CENTER); / / about the center style.setVerticalAlignment (HSSFCellStyle.VERTICAL_CENTER); / / up and down the center style.setRotation (short rotation); / / contents of the cell rotation angle HSSFDataFormat df = wb.createDataFormat (); style1.setDataFormat (df.getFormat (angle cell.setCellStyle (style);
10, insert a picture to see the

Forum code / / first read in the image into a ByteArrayOutputStream in order to produce ByteArray ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream (); BufferedImage bufferImg = ImageIO.read (new File (/ stencil.xlt anchor = new HSSFClientAnchor (0,0,1023,255, (short0, 0, (short10, 10); patriarch.createPicture (anchor, wb.addPicture (byteArrayOut.toByteArray (), HSSFWorkbook.PICTURE_TYPE_JPEG)); 11, set out Wrap
HSSFCellStyle cellStyle = workbook.createCellStyle ();
cellStyle.setWrapText (true);
sheet.setDefaultColumnStyle ((short) 0,UGG Outlet, cellStyle);
set the column width
sheet . setColumnWidth ((short) 0, (short) 9000);
sheet.setDefaultColumnStyle ((short) 0, cellStyle);
and
sheet.setDefaultColumnWidth ((short) 70); conflict
line will not only set the column width
copy a cell sample: