POI allows you to read and write Excel files.

POI allows you to read and write Excel files.
1. Apache POI Overview

Apache POI is an open source library of the Apache Software Foundation.

Provides APIs for Java applications to read and write Microsoft Office files.

The name of a foreigner is always modest. POI is the abbreviation of (Poor Obfuscation Implementation), meaning "Poor fuzzy Implementation ".

If you have checked the source code of any project in the Apache open source library, you will find that the proper design mode, high cohesion and low coupling between modules, the interface abstraction is quite in place, and the implementation method is also very elegant, such a specific.

O (too many) o alas, how much rigorous thinking, how much code is required, and how many projects have been designed to reach that level?

POI provides the following functions to read and write Microsoft Office:

• 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-provides the ability to read and write Microsoft Word files.
• HSLF-provides the ability to read and write Microsoft PowerPoint files.
• HDGF-provides the ability to read and write Microsoft Visio files.

This document describes how to use POI to read and write Excel.

The above sentence is the main sentence of this article. If you can solve your problem, please take a closer look.

The project uses the latest poi-3.14-20160307.jar, Baidu cloud address: http://pan.baidu.com/s/1bnWFWg3 password: kame

2. design before implementation

Because the project is relatively large, the following is an example after the project is created separately.

3. POI write Excel

Use Case chart on the left to write Excel for POI

A. getExcelData: Obtain the Excel Data to be output. The data here can be obtained from the persistence layer and the page display layer ......

(The data in this example is the help_categroy table record in Mysql's own database.) The process of establishing a JavaBean and obtaining a database is not mentioned here.

The data type of the retrieved data is List <HelpCategory>

B. POI Write Workbook: this is the key point. Use POI to instantiate the data as HSSFWorkbook. The core code is as follows:

Public HSSFWorkbook expExcel (List <HelpCategory> helpCategories) {HSSFWorkbook workbook = new HSSFWorkbook (); HSSFSheet sheet = workbook. createSheet (); createSheetStyle (workbook, sheet); HSSFRow row = sheet. createRow (0); HSSFCell cell; HelpCategory category = new HelpCategory (); Field [] fields = category. getClass (). getDeclaredFields (); for (int j = 0; j <fields. length; j ++) {cell = row. createCell (J); cell. setCellValue (fields [j]. getName (); cell. setCellStyle (this. textAlignCenter) ;}for (int I = 0; I Obtain the value of the JavaBean field as the header and set the style of the header:

        HSSFRow row = sheet.createRow(0);        HSSFCell cell;        HelpCategory category = new HelpCategory();        Field[] fields = category.getClass().getDeclaredFields();        for (int j = 0; j < fields.length; j++) {            cell = row.createCell(j);            cell.setCellValue(fields[j].getName());            cell.setCellStyle(this.textAlignCenter);        }

Obtain the field value in JavaBean HelpCategory and fill it in the corresponding table, and set the style:

     for (int i = 0; i < helpCategories.size(); i++) {            category = helpCategories.get(i);            row = sheet.createRow(i + 1);            for (int k = 0; k < fields.length; k++) {                Field field = fields[k];                Object o = invokeGet(category, field.getName());                cell = row.createCell(k);                cell.setCellValue(o != null ? o.toString() : "");                cell.setCellStyle((k == 0 || k == 1) ? this.textAlignCenter : this.textAlignLeft);            }        }

A reflection is used to obtain the value of the javaBean attribute.

If you think the reflection efficiency is a little poor, you can construct a Map data set by yourself to achieve the same effect.

  private Object invokeGet(Object o, String fieldName) {        Method method = getGetMethod(o.getClass(), fieldName);        try {            return method.invoke(o, new Object[0]);        } catch (Exception e) {            e.printStackTrace();        }        return null;    }    private Method getGetMethod(Class objectClass, String fieldName) {        StringBuilder sb = new StringBuilder();        sb.append("get");        sb.append(fieldName.substring(0, 1).toUpperCase());        sb.append(fieldName.substring(1));        try {            return objectClass.getMethod(sb.toString());        } catch (Exception e) {            e.printStackTrace();        }        return null;    }

You can also set the table style and border style:

Private void createSheetStyle (HSSFWorkbook _ workbook, HSSFSheet _ sheet) {// set the table font HSSFFont font10 = _ workbook. createFont (); font10.setFontHeightInPoints (short) 12); font10.setFontName (""); // set the table sample this. textAlignCenter = getCellStyle (_ workbook, font10, HSSFCellStyle. ALIGN_CENTER); this. textAlignLeft = getCellStyle (_ workbook, font10, HSSFCellStyle. ALIGN_LEFT); // sets the column width _ sheet. setColumnWidth (0, 4000); _ sheet. setColumnWidth (1, 4000); _ sheet. setColumnWidth (2, 10000); _ sheet. setColumnWidth (3, 10000 );}

The above style settings, if you still can not meet your functional needs, more settings please refer to blog: http://langhua9527.iteye.com/blog/388005

4. POI read Excel

Use Case chart on the left to write Excel for POI

A. getExcleFile: Get the Excel file to be read. (Note: Determine the suffix of the obtained file)

String fileName = excel. getName (); int iIndex = fileName. lastIndexOf ("."); String ext = (iIndex <0 )? "": FileName. substring (iIndex + 1). toLowerCase (); if (! "Xls, xlsx". contains (ext) | ". contains (ext) {System. out. println (" the file type is not EXCEL! ");}

B. POI Read Workbook: Here is the focus. Use POI to Read the Excel file, parse and save it to your own data type.

           ArrayList<HelpCategory> categories = new ArrayList<>();        try {            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(new FileInputStream(excel));            HSSFWorkbook workbook = new HSSFWorkbook(poifsFileSystem);            HSSFSheet sheet = workbook.getSheetAt(0);            HelpCategory category = new HelpCategory();            Field[] declaredField = category.getClass().getDeclaredFields();            for (int k = 1; k <= sheet.getLastRowNum(); k++) {                HSSFRow row = sheet.getRow(k);                category = new HelpCategory();                for (int j = 0; j < declaredField.length; j++) {                    HSSFCell cell = row.getCell(j);                    invokeSet(category, declaredField[j].getName(), cell.getStringCellValue());                }                categories.add(category);            }        } catch (Exception e) {            e.printStackTrace();        }

The above also uses some reflection items. Here we get the set method of JavaBean to set specific attributes.

   public void invokeSet(Object o, String fieldName, Object value) {        Method method = getSetMethod(o.getClass(), fieldName);        try {            method.invoke(o, new Object[]{value});        } catch (Exception e) {            e.printStackTrace();        }    }    public Method getSetMethod(Class objectClass, String fieldName) {        try {            Class[] parameterTypes = new Class[1];            Field field = objectClass.getDeclaredField(fieldName);            parameterTypes[0] = field.getType();            StringBuffer sb = new StringBuffer();            sb.append("set");            sb.append(fieldName.substring(0, 1).toUpperCase());            sb.append(fieldName.substring(1));            Method method = objectClass.getMethod(sb.toString(), parameterTypes);            return method;        } catch (Exception e) {            e.printStackTrace();        }        return null;    }

The above example is only used as an example, and the specific requirements of the project are changed at any time.

For example, the way to obtain data may be multi-table joint query or display layer. Other operations are required for the results, and the style changes of the Excel table are required ........

However, if you are familiar with the specific core read/write implementation, you must renew it.