POI Excel and poiexcel

POI Excel and poiexcel

I. POI Overview

Apache POI is an open-source library of the Apache Software Foundation. POI provides APIs for Java programs to read and write Microsoft Office files.

Structure:

• • HSSF-provides the ability to read and write Microsoft Excel files.
  • XSSF-provides the ability to read and write files in Microsoft Excel OOXML format.
  • HWPF-read and write Microsoft Word files.
  • HSLF-supports reading and writing Microsoft PowerPoint files.
  • HDGF-read and write Microsoft Visio files.

Dependencies must be introduced

<dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.17</version></dependency>

Note: Version 3.17 is the final version that supports JDK 6.

Ii. Overview of HSSF

HSSF is the abbreviation of Horrible SpreadSheet Format. With HSSF, you can use pure Java code to read, write, and modify Excel files. HSSF provides two types of APIS for read operations: usermodel and eventusermodel, namely, "user model" and "Event-user model ".

Iii. poi excel document structure

• HSSFWorkbook excel Document Object
• Sheet of HSSFSheet excel
• HSSFRow excel row
• HSSFCell excel Cell
• HSSFFont excel font
• HSSFName
• HSSFDataFormat Date Format
• HSSFHeader sheet Header
• End of HSSFFooter sheet
• HSSFCellStyle cell style
• HSSFDateUtil date
• HSSFPrintSetup print
• HSSFErrorConstants error information table

Iv. EXCEL read/write operations

1. Read and retrieve the latest data.

1 public List <Area> importXLS () {2 3 ArrayList <Area> list = new ArrayList <> (); 4 try {5 // 1. Get the file input stream 6 InputStream inputStream = new FileInputStream ("/Users/Shared/Latest data .xls "); 7 // 2. Obtain the Excel workbook object 8 HSSFWorkbook workbook = new HSSFWorkbook (inputStream); 9 // 3. Obtain the Excel worksheet object 10 HSSFSheet sheetAt = workbook. getSheetAt (0); 11 // 4. cyclically read table data 12 for (Row row: sheetAt) {13 // The first line (that is, the header) does not read 14 if (row. getRowNum () = 0) {15 continue; 16} 17 // read the cell data in the current row. The index starts from 0 and 18 String areaNum = row. getCell (0 ). getStringCellValue (); 19 String province = row. getCell (1 ). getStringCellValue (); 20 String city = row. getCell (2 ). getStringCellValue (); 21 String district = row. getCell (3 ). getStringCellValue (); 22 String postcode = row. getCell (4 ). getStringCellValue (); 23 24 Area area = new Area (); 25 area. setCity (city); 26 area. setDistrict (district); 27 area. setProvince (province); 28 area. setPostCode (postcode); 29 list. add (area); 30} 31 // 5. Disable stream 32 workbook. close (); 33} catch (IOException e) {34 e. printStackTrace (); 35} 36 return list; 37}

2. Export the data to the ..xls file. The page data is as follows:

1 public void exportExcel () throws IOException {2 3 Page <Area> page = areaService. pageQuery (null); 4 List <Area> list = page. getContent (); 5 6 // 1. create an excel file in the memory 7 HSSFWorkbook hssfWorkbook = new HSSFWorkbook (); 8 // 2. create a workbook 9 HSSFSheet sheet = hssfWorkbook. createSheet (); 10 // 3. create a title row 11 HSSFRow titlerRow = sheet. createRow (0); 12 titlerRow. createCell (0 ). setCellValue ("Province"); 13 titlerRow. createCell (1 ). setCellValue ("city"); 14 titlerRow. createCell (2 ). setCellValue ("zone"); 15 titlerRow. createCell (3 ). setCellValue ("zip code"); 16 titlerRow. createCell (4 ). setCellValue ("Short Code"); 17 titlerRow. createCell (5 ). setCellValue ("city code"); 18 19 // 4. traverse the data and create a data Row 20 for (Area area: list) {21 // obtain the row number 22 of the last row int lastRowNum = sheet. getLastRowNum (); 23 HSSFRow dataRow = sheet. createRow (lastRowNum + 1); 24 dataRow. createCell (0 ). setCellValue (area. getProvince (); 25 dataRow. createCell (1 ). setCellValue (area. getCity (); 26 dataRow. createCell (2 ). setCellValue (area. getDistrict (); 27 dataRow. createCell (3 ). setCellValue (area. getPostcode (); 28 dataRow. createCell (4 ). setCellValue (area. getjsoncode (); 29 dataRow. createCell (5 ). setCellValue (area. getCitycode (); 30} 31 // 5. create a file named 32 String fileName = "Hangzhou data statistics .xls"; 33 // 6. get output stream object 34 HttpServletResponse response = ServletActionContext. getResponse (); 35 ServletOutputStream outputStream = response. getOutputStream (); 36 37 // 7. obtain mimeType38 ServletContext servletContext = ServletActionContext. getServletContext (); 39 String mimeType = servletContext. getMimeType (fileName); 40 // 8. obtain the browser information and recode the file name 41 HttpServletRequest request = ServletActionContext. getRequest (); 42 fileName = FileUtils. filenameEncoding (fileName, request); 43 44 // 9. set the information header 45 response. setContentType (mimeType); 46 response. setHeader ("Content-Disposition", "attachment; filename =" + fileName); 47 // 10. write the file and close the stream 48 hssfWorkbook. write (outputStream); 49 hssfWorkbook. close (); 50}

Tool

1 public class FileUtils {2 3 public static String filenameEncoding (String filename, HttpServletRequest request) throws IOException {4 String agent = request. getHeader ("User-Agent"); // obtain the browser 5 if (agent. contains ("Firefox") {6 BASE64Encoder base64Encoder = new BASE64Encoder (); 7 filename = "=? UTF-8? B? "8 + base64Encoder. encode (filename. getBytes (" UTF-8 ") 9 + "? = "; 10} else if (agent. contains ("MSIE") {11 filename = URLEncoder. encode (filename, "UTF-8"); 12} else if (agent. contains ("Safari") {13 filename = new String (filename. getBytes ("UTF-8"), "ISO8859-1"); 14} else {15 filename = URLEncoder. encode (filename, "UTF-8"); 16} 17 return filename; 18} 19}

Write the xls file:

5. Common EXCEL operations

1. obtain common Excel objects

1 POIFSFileSystem fs = newPOIFSFileSystem (new FileInputStream ("d:/test.xls"); 2 // obtain the Excel Workbook object 3 HSSFWorkbook wb = new HSSFWorkbook (fs ); 4 // obtain the Excel worksheet object 5 HSSFSheet sheet = wb. getSheetAt (0); 6 // get the rows of the Excel worksheet 7 HSSFRow row = sheet. getRow (I); 8 // get the cell 9 HSSFCell cell = row of the specified row in the Excel worksheet. getCell (short) j); 10 cellStyle = cell. getCellStyle (); // get the cell style

2. Create common Excel objects

1 HSSFWorkbook wb = new HSSFWorkbook (); // create an Excel Workbook object 2 HSSFSheet sheet = wb. createSheet ("new sheet"); // create an Excel worksheet object 3 HSSFRow row = sheet. createRow (short) 0); // create the row 4 cellStyle = wb for the Excel worksheet. createCellStyle (); // create a cell style 5 row. createCell (short) 0 ). setCellStyle (cellStyle); // create an Excel worksheet to specify the row's cell 6 row. createCell (short) 0 ). setCellValue (1); // set the Excel worksheet Value

3. Set the sheet name and cell content

1 wb. setSheetName (1, "first worksheet", HSSFCell. ENCODING_UTF_16); 2 cell. setEncoding (short) 1); 3 cell. setCellValue ("cell content ");

4. Get the number of sheet

1 wb.getNumberOfSheets()

5. Get the sheet object based on the index

1 HSSFSheet sheet = wb.getSheetAt(0);

6. Get valid rows

1 int rowcount = sheet.getLastRowNum();

7. Obtain the number of valid cells for a row

1 row.getLastCellNum();

8. read/write of the cell value type

1 cell. setCellType (HSSFCell. CELL_TYPE_STRING); // set the cell type to STRING. 2 cell. getNumericCellValue (); // read the content of a cell of the numeric type.

9. Set the column width and Row Height.

1 sheet.setColumnWidth((short)column,(short)width); 2 row.setHeight((short)height);

10. Add a region and merge cells.

1 Region region = new Region (short) rowFrom, (short) columnFrom, (short) rowTo 2, (short) columnTo ); // merge 3 sheet from columnFrom column of row rowFrom. addMergedRegion (region); // to the columnTo region of the rowTo row 4 // get all regions 5 sheet. getNumMergedRegions ()

11. Save the Excel file

1 FileOutputStream fileOut = new FileOutputStream(path); 2 wb.write(fileOut);

12. Return string values based on different cell attributes

1 public String getCellStringValue (HSSFCell cell) {2 String cellValue = ""; 3 switch (cell. getCellType () {4 case HSSFCell. CELL_TYPE_STRING: // string type 5 cellValue = cell. getStringCellValue (); 6 if (cellValue. trim (). equals ("") | cellValue. trim (). length () <= 0) 7 cellValue = ""; 8 break; 9 case HSSFCell. CELL_TYPE_NUMERIC: // value type 10 cellValue = String. valueOf (cell. getNumericCellValue (); 11 break; 12 case HSSFCell. CELL_TYPE_FORMULA: // formula 13 cell. setCellType (HSSFCell. CELL_TYPE_NUMERIC); 14 cellValue = String. valueOf (cell. getNumericCellValue (); 15 break; 16 case HSSFCell. CELL_TYPE_BLANK: 17 cellValue = ""; 18 break; 19 case HSSFCell. CELL_TYPE_BOOLEAN: 20 break; 21 case HSSFCell. CELL_TYPE_ERROR: 22 break; 23 default: 24 break; 25} 26 return cellValue; 27}

13. Common cell border formats

1 HSSFCellStyle style = wb. createCellStyle (); 2 style. setBorderBottom (HSSFCellStyle. BORDER_DOTTED); // 3 style in the lower border. setBorderLeft (HSSFCellStyle. BORDER_DOTTED); // 4 style on the left border. setBorderRight (HSSFCellStyle. BORDER_THIN); // right BORDER 5 style. setBorderTop (HSSFCellStyle. BORDER_THIN); // top border

14. Set the font and content location

1 HSSFFont f = wb. createFont (); 2 f. setFontHeightInPoints (short) 11); // font size 3 f. setBoldweight (HSSFFont. BOLDWEIGHT_NORMAL); // bold 4 style. setFont (f); 5 style. setAlignment (HSSFCellStyle. ALIGN_CENTER); // 6 styles in the left-right corner. setverticalignment (HSSFCellStyle. VERTICAL_CENTER); // top or bottom center 7 style. setRotation (short rotation); // The rotation Angle of the cell content 8 HSSFDataFormat df = wb. createDataFormat (); 9 style1.setDataFormat (df. getFormat ("0.00%"); // set the cell data format to 10 cells. setCellFormula (string); // set the formula 11 style for the cell. setRotation (short rotation); // The rotation Angle of the cell content

15. insert an image

1 // put the read image in a ByteArrayOutputStream to generate ByteArray 2 ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream (); 3 BufferedImage bufferImg = ImageIO. read (new File ("OK .jpg"); 4 ImageIO. write (bufferImg, "jpg", byteArrayOut); 5 // read an excel template 6 FileInputStream fos = new FileInputStream (filePathName + "/stencel. xlt "); 7 fs = new POIFSFileSystem (fos); 8 // create a working thin 9 HSSFWorkbook wb = new HSSFWorkbook (fs); 10 HSSFSheet sheet = wb. getSheetAt (0); 11 HSSFPatriarch patriarch = sheet. createDrawingPatriarch (); 12 HSSFClientAnchor anchor = new HSSFClientAnchor (1023,255, (short), (short) 10, 10); 13 patriarch. createPicture (anchor, wb. addPicture (byteArrayOut. toByteArray (), HSSFWorkbook. PICTURE_TYPE_JPEG ));

16. Adjust the worksheet location

1 HSSFWorkbook wb = new HSSFWorkbook(); 2 HSSFSheet sheet = wb.createSheet("format sheet"); 3 HSSFPrintSetup ps = sheet.getPrintSetup(); 4 sheet.setAutobreaks(true); 5 ps.setFitHeight((short)1); 6 ps.setFitWidth((short)1);