apache poi 解析excel

標籤：java   apache   poi   excel   

maven 依賴

<dependency>    <groupId>com.fasterxml.jackson.core</groupId>    <artifactId>jackson-core</artifactId>    <version>2.0.0</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi</artifactId>    <version>3.15</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml</artifactId>    <version>3.15</version></dependency><dependency>    <groupId>org.apache.poi</groupId>    <artifactId>poi-ooxml-schemas</artifactId>    <version>3.15</version></dependency><dependency>    <groupId>org.apache.xmlbeans</groupId>    <artifactId>xmlbeans</artifactId>    <version>2.6.0</version></dependency><dependency>    <groupId>com.fasterxml.jackson.dataformat</groupId>    <artifactId>jackson-dataformat-xml</artifactId>    <version>2.0.0</version></dependency>

package excel;import org.apache.poi.ss.usermodel.CellType;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook;import java.io.InputStream;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;/** * Created by xiaominzh on 2016/11/14. */public class ExcelExportNew {    private static String getCellValue(XSSFCell cell,String columnType)throws Exception{        if("i".equals(columnType)){            return String.valueOf(cell.getNumericCellValue());        }        if("d".equals(columnType)){            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");            return sdf.format(cell.getDateCellValue());        }        cell.setCellType(CellType.STRING);        return cell.getRichStringCellValue().toString();    }    private static List<String> loadColumnNames(XSSFRow row){        List<String> array = new ArrayList<String>();        int maxCellNum = row.getLastCellNum();        for(int i=0;i<maxCellNum;i++){            XSSFCell cell = row.getCell(i);            String name = cell.getStringCellValue();            array.add(name);        }        return array;    }    private static List<String> loadColumnTypes(XSSFRow row){        List<String> array = new ArrayList<String>();        int maxCellNum = row.getLastCellNum();        for(int i=0;i<maxCellNum;i++){            XSSFCell cell = row.getCell(i);            String name = cell.getStringCellValue();            array.add(name);        }        return array;    }    private static void convertExcelToJSON(String fileName,String sheetName)throws Exception {        InputStream is = ExcelExportNew.class.getResourceAsStream(fileName);        XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);        XSSFSheet sheet = hssfWorkbook.getSheet(sheetName);        int rows = sheet.getLastRowNum();        List<Map<String, Object>> result = new ArrayList<Map<String, Object>>();        List<String> columnTypes = loadColumnTypes(sheet.getRow(0));        List<String> columnNames = loadColumnNames(sheet.getRow(1));        for (int i = 3; i <= rows; i++) {            XSSFRow row = sheet.getRow(i);            int maxCellNum = row.getLastCellNum();            Map<String, Object> item = new HashMap<String, Object>();            for (int cellIndex = 0; cellIndex < maxCellNum; cellIndex++) {                XSSFCell cell = row.getCell(cellIndex);                String columnName = columnNames.get(cellIndex);                String columnType = columnTypes.get(cellIndex);                String value = null;                try {//                    cell.setCellType(CellType.STRING);                    value = getCellValue(cell,columnType);                } catch (Exception e) {                    System.err.println("row:"+i+",column:"+cellIndex);                    System.err.println(e.getMessage());                    break;                }                item.put(columnName, value);            }            result.add(item);        }        System.out.println(JSONUtil.getJSONString(result));    }    public static void main(String[] args) throws Exception {        convertExcelToJSON("shop_sale_type_list.xlsx","shop_sale_type_list");    }}

測試excel檔案

650) this.width=650;" src="http://s4.51cto.com/wyfs02/M02/8A/7D/wKiom1gyU_Lyi-spAAeHuONXvwE111.jpg" title="e.jpg" alt="wKiom1gyU_Lyi-spAAeHuONXvwE111.jpg" />

輸出

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M01/8A/79/wKioL1gyVIjRXTSLAAq81D76qTI029.jpg" title="c.jpg" alt="wKioL1gyVIjRXTSLAAq81D76qTI029.jpg" />

apache poi 解析excel