java使用poi获取excel数据

昨天在csdn上看到的 当时不行 今天加了xmlbeans的jar包就可以了
博客找不到了 接下来我自己找找怎么转化给echarts用。
一个参考的类库 https://www.oschina.net/p/echarts-java

package com.example.demo.getData;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.io.IOException;

public class datacs {
    public static final String SAMPLE_XLSX_FILE_PATH = "C:/Users/83543/Desktop/data.xlsx";

    public static void main(String[] args) throws IOException, InvalidFormatException {

        // Creating a Workbook from an Excel file (.xls or .xlsx)
        Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));

        // Retrieving the number of sheets in the Workbook
        //System.out.println("Workbook has " + workbook.getNumberOfSheets() + " Sheets : ");

        /*
           =============================================================
           Iterating over all the sheets in the workbook (Multiple ways)
           =============================================================
        */

        // 1. You can obtain a sheetIterator and iterate over it
        //Iterator<Sheet> sheetIterator = workbook.sheetIterator();
        //System.out.println("Retrieving Sheets using Iterator");
        //while (sheetIterator.hasNext()) {
        //  Sheet sheet = sheetIterator.next();
        //  System.out.println("=> " + sheet.getSheetName());
        //}

        // 2. Or you can use a for-each loop
        //System.out.println("Retrieving Sheets using for-each loop");
        //for(Sheet sheet: workbook) {
        //  System.out.println("=> " + sheet.getSheetName());
        //}

        // 3. Or you can use a Java 8 forEach with lambda
        //System.out.println("Retrieving Sheets using Java 8 forEach with lambda");
        //workbook.forEach(sheet -> {
        //  System.out.println("=> " + sheet.getSheetName());
        //});

        /*
           ==================================================================
           Iterating over all the rows and columns in a Sheet (Multiple ways)
           ==================================================================
        */

        // Getting the Sheet at index zero
        Sheet sheet = workbook.getSheetAt(0);

        // Create a DataFormatter to format and get each cell's value as String
        //DataFormatter dataFormatter = new DataFormatter();

        // 1. You can obtain a rowIterator and columnIterator and iterate over them
        //System.out.println("\n\nIterating over Rows and Columns using Iterator\n");
        //Iterator<Row> rowIterator = sheet.rowIterator();
        //while (rowIterator.hasNext()) {
        //  Row row = rowIterator.next();
        //
        //  // Now let's iterate over the columns of the current row
        //  Iterator<Cell> cellIterator = row.cellIterator();
        //
        //  while (cellIterator.hasNext()) {
        //      Cell cell = cellIterator.next();
        //      String cellValue = dataFormatter.formatCellValue(cell);
        //      System.out.print(cellValue + "\t");
        //  }
        //  System.out.println();
        //}

        // 2. Or you can use a for-each loop to iterate over the rows and columns
        //System.out.println("\n\nIterating over Rows and Columns using for-each loop\n");
        //for (Row row: sheet) {
        //  for(Cell cell: row) {
        //      String cellValue = dataFormatter.formatCellValue(cell);
        //      System.out.print(cellValue + "\t");
        //  }
        //  System.out.println();
        //}

        // 3. Or you can use Java 8 forEach loop with lambda
        //System.out.println("\n\nIterating over Rows and Columns using Java 8 forEach with lambda\n");
        //sheet.forEach(row -> {
        //  row.forEach(cell -> {
        //      String cellValue = dataFormatter.formatCellValue(cell);
        //      System.out.print(cellValue + "\t");
        //  });
        //  System.out.println();
        //});

        sheet.forEach(row -> {
            row.forEach(cell -> {
                printCellValue(cell);
            });
            System.out.println();
        });
        // Closing the workbook
        workbook.close();

    }

    private static void printCellValue(Cell cell) {
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                System.out.print(cell.getBooleanCellValue());
                break;
            case STRING:
                System.out.print(cell.getRichStringCellValue().getString());
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.print(cell.getDateCellValue());
                } else {
                    System.out.print(cell.getNumericCellValue());
                }
                break;
            case FORMULA:
                System.out.print(cell.getCellFormula());
                break;
            case BLANK:
                System.out.print("");
                break;
            default:
                System.out.print("");
        }

        System.out.print("\t");
    }
}

我用数组的方式弄出来了数据。
传给echarts收到了。这里贴出来后台java的代码。

package com.example.demo.controller;

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.io.IOException;

import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.http.HttpServletRequest;
import java.lang.reflect.Array;
import java.util.HashMap;
import java.util.Map;
import java.util.Random;
import com.example.demo.getData.datacs;

@RestController
public class data_controller {
    public  static int rowNum;
    public static int columnNum;
    //    public static String[] arr;
    public static double []arrdata =new double[100];
    public static double []arrayrec ;
    public static int i;
    public static final String SAMPLE_XLSX_FILE_PATH = "C:/Users/83543/Desktop/data.xlsx";

    public static void createsheet() throws IOException, InvalidFormatException{
        Workbook workbook = WorkbookFactory.create(new File(SAMPLE_XLSX_FILE_PATH));
        // Getting the Sheet at index zero
        Sheet sheet = workbook.getSheetAt(0);
        rowNum=sheet.getLastRowNum();
        Row sheetRow=sheet.getRow(1);
        columnNum=sheetRow.getPhysicalNumberOfCells();
        System.out.println(rowNum+"??"+columnNum);
        i=0;
        sheet.forEach(row -> {
            row.forEach(cell -> {     //对每个元素都进行输出操作
                printCellValue(cell,i);
                i++;
            });
            System.out.println();
        });
        // Closing the workbook
        workbook.close();
    }

    public static void printCellValue(Cell cell,int i) {
        i-=5;
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                System.out.print(cell.getBooleanCellValue());

                break;
            case STRING: //输出了表名
                System.out.print(cell.getRichStringCellValue().getString());
                break;
            case NUMERIC://如果是数值型
                if (DateUtil.isCellDateFormatted(cell)) {
                    System.out.print(cell.getDateCellValue());
                } else {
                    //循环拿到数据,都放在一个里面，到时候循环拿,以5为单位

                    arrdata[i]=cell.getNumericCellValue();
                    System.out.print(cell.getNumericCellValue());
                }
                break;
            case FORMULA:
                System.out.print(cell.getCellFormula());
                break;
            case BLANK:
                System.out.print("");
                break;
            default:
                System.out.print("");
        }
        System.out.print("\t");

    }

    @RequestMapping("/ech_data")
    public Map<String,Object> ech_data(HttpServletRequest request)throws IOException, InvalidFormatException{
        Map<String,Object> map = new HashMap<String, Object>();
        long l = System.currentTimeMillis();
        Date date=new Date();
        SimpleDateFormat dateFormat = new SimpleDateFormat("dd日:HH:mm:ss");
        System.out.println(dateFormat.format(date));

        StringBuilder str =new StringBuilder();
        Random ra= new Random();
        String id = request.getParameter("id");//拿到前台点击的圆圈
        String title;
createsheet();
        //设置标题
        if(id.equals("c1")){ title="地区1"; }
        else if(id.equals("c2")) { title="地区2"; }else if(id.equals("c3")) { title="地区3"; }else if(id.equals("c4"))
        { title="地图4"; }else if(id.equals("c5")) { title="地区5"; }else { title="error"; }
        String x_data[ ]= new String[rowNum];
        double str1[ ]= new double[rowNum];
        double str2[ ]= new double[rowNum];
        double str3[ ]= new double[rowNum];
        double str4[ ]= new double[rowNum];
        double str5[ ]= new double[rowNum];
        for (int i=0;i<rowNum;i++)
        {
            x_data[i]=dateFormat.format(date);
        }
        int i=0;
        int j=0;
        while(i<columnNum*rowNum)
        {
            str1[j]=arrdata[i];
            i++;
            str2[j]=arrdata[i];
            i++;
            str3[j]=arrdata[i];
            i++;
            str4[j]=arrdata[i];
            i++;
            str5[j]=arrdata[i];
            i++;
            j++;
        }
//        str.append("[");
//        for (int i=1;i<11;i++)
//        {
//
//            if(i==10){
//                str.append((ra.nextInt(200)+1)+"]");
//            }else {
//                str.append((ra.nextInt(200)+1)+",");
//            }
//        }
        map.put("data1",str1);map.put("data2",str2); map.put("data3",str3); map.put("data4",str4); map.put("data5",str5);
        map.put("x_data",x_data);//legend数据
        map.put("title",title);
        return map;
    }
}