未使用Apache poi获取行或散点图中的单个列的所有值

In a excel file named "chart.xlsx" i have these data as follows-

在名为“chart.xlsx”的excel文件中,我将这些数据如下 -

name age bloodgrp

名字年龄bloodgrp

ad 14 a+

广告14 a +

as 42 o+

为42 o +

sd 21 o-

df 55 ab-

fg 44 a-

gh 87 b-

hj 26 b+

hj 26 b +

jk 24 ab+

jk 24 ab +

kl 28 b-

i read these data using apache poi and i want to plot a line chart/ scatter chart in a excel file .

我使用apache poi读取这些数据,我想在excel文件中绘制折线图/散点图。

package tkl;
import java.io.*;
import java.util.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.charts.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;


public class DemoTrendTest {


public static void main(String[] args) {
    // TODO Auto-generated method stub
    try{
    FileInputStream fis= new FileInputStream(new File("D:\\Chart.xlsx"));
    XSSFWorkbook wb= new XSSFWorkbook(fis);

    XSSFSheet s= wb.getSheet("a");
    Iterator <Row> rowIterator = s.iterator();
    while (rowIterator.hasNext())
    {
        Row row = rowIterator.next();
        //For each row, iterate through all the columns
        Iterator<Cell> cellIterator = row.cellIterator();

        while (cellIterator.hasNext())
        {
            Cell cell = cellIterator.next();
            if(cell.getCellType()== Cell.CELL_TYPE_NUMERIC)
            {
                System.out.print(cell.getNumericCellValue() + "\t");
            }

        }

    }

    Drawing drawing = s.createDrawingPatriarch();
    ClientAnchor anchor= drawing.createAnchor(0, 0, 0, 0, 5, 4, 14, 20);
    Chart chart = drawing.createChart(anchor);
    ChartLegend legend = chart.getOrCreateLegend();
    legend.setPosition(LegendPosition.BOTTOM);
    ScatterChartData data = chart.getChartDataFactory().createScatterChartData();
    ValueAxis bottomAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.BOTTOM);
    ValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT);
    leftAxis.setCrosses(AxisCrosses.AUTO_ZERO);
    ChartDataSource<Number> xs = DataSources.fromNumericCellRange(s, new CellRangeAddress(0, 0, 0, 2));
    ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(s, new CellRangeAddress(1, 10, 1,1));

//actually should get =SERIES(a!$B$1,,a!$B$2:$B$10,1) but getting =SERIES(,a!$A$1:$J$1,a!$B$2:$B$11,1)

    data.addSerie(xs, ys2);
    chart.plot(data, bottomAxis, leftAxis);
    FileOutputStream fileOut = new FileOutputStream("D://1chart.xlsx");
    wb.write(fileOut);
    fileOut.close();

    }
    catch(Exception e)
    {
        e.printStackTrace();
    }
}

}

i want to plot 14,42,21, 55,44,87,26,24,28 data in the chart. the problem i'm facing that only first 3 values are taking in the chart. i'm getting =SERIES(,a!$A$1:$J$1,a!$B$2:$B$11,1) but i want =SERIES(a!$B$1,,a!$B$2:$B$10,1). How can i get all the values in the chart? Can anyone help me out.

我想在图表中绘制14,42,21,55,44,87,26,24,28数据。我面临的问题是图表中只有前三个值。我得到= SERIES(,一个!$ A $ 1:$ J $ 1,一个!$ B $ 2:$ B $ 11,1)但我想要= SERIES(一个!$ B $ 1,一个!$ B $ 2:$ B $ 10,1)。我怎样才能获得图表中的所有值?谁能帮我吗。

2 个解决方案

#1

You are only supplying 3 x axis values:

您只提供3个x轴值:

ChartDataSource xs = DataSources.fromNumericCellRange(s, new CellRangeAddress(0, 0, 0, 2));

ChartDataSource xs = DataSources.fromNumericCellRange(s,new CellRangeAddress(0,0,0,2));

If you change that line to:

如果您将该行更改为:

ChartDataSource xs = DataSources.fromNumericCellRange(s, new CellRangeAddress(1, 10, 0, 1));

ChartDataSource xs = DataSources.fromNumericCellRange(s,new CellRangeAddress(1,10,0,1));

then you will see all of your data points.

然后你会看到你所有的数据点。

#2

I just changed 2 lines

我刚换了2行

ChartDataSource<Number> xs = DataSources.fromNumericCellRange(s, new CellRangeAddress(0, 9, 1, 1));
ChartDataSource<Number> ys2 = DataSources.fromNumericCellRange(s, new CellRangeAddress(1, 9, 1,1));

now i can see my output.

现在我可以看到我的输出。

#1