POI Excel Chart 예제

코파일럿 참 잘짜주넹

 

 

package excel.MavenProject;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFChart;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;
import org.apache.poi.xddf.usermodel.chart.*;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

public class App {
    public static void main(String[] args) {
    	 String excelFilePath = "C:\\Users\\rhkdr\\OneDrive\\바탕 화면\\업무\\excel\\test.xlsx";
    	 
    	 try (FileInputStream fis = new FileInputStream(excelFilePath);
    			 Workbook workbook = new XSSFWorkbook(fis)) {

             XSSFSheet sheet = (XSSFSheet) workbook.getSheetAt(0);

             // 데이터의 마지막 행과 열을 동적으로 계산
             int lastRowNum = sheet.getLastRowNum();
             int lastCellNum = sheet.getRow(0).getLastCellNum();

             // 차트를 그릴 위치 설정
             XSSFDrawing drawing = sheet.createDrawingPatriarch();
             int chartIndex = 0;

             for (int colStart = 1; colStart < lastCellNum; colStart += 5) {
                 int colEnd = Math.min(colStart + 4, lastCellNum - 1);

                 XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 0, lastRowNum + 2 + chartIndex * 15, 10, lastRowNum + 17 + chartIndex * 15);
                 XSSFChart chart = drawing.createChart(anchor);
                 chart.setTitleText("Application Usage Over Time (Part " + (chartIndex + 1) + ")");
                 chart.setTitleOverlay(false);

                 XDDFCategoryAxis bottomAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
                 bottomAxis.setTitle("Time");
                 XDDFValueAxis leftAxis = chart.createValueAxis(AxisPosition.LEFT);
                 leftAxis.setTitle("Value");

                 XDDFDataSource<String> times = XDDFDataSourcesFactory.fromStringCellRange(sheet,
                         new CellRangeAddress(1, lastRowNum, 0, 0));

                 XDDFLineChartData data = (XDDFLineChartData) chart.createData(ChartTypes.LINE, bottomAxis, leftAxis);

                 for (int i = colStart; i <= colEnd; i++) {
                     XDDFNumericalDataSource<Double> appData = XDDFDataSourcesFactory.fromNumericCellRange(sheet,
                             new CellRangeAddress(1, lastRowNum, i, i));
                     String seriesName = sheet.getRow(0).getCell(i).getStringCellValue();
                     XDDFLineChartData.Series series = (XDDFLineChartData.Series) data.addSeries(times, appData);
                     series.setTitle(seriesName, null);
                 }

                 chart.plot(data);
                 // 범례 추가
                 XDDFChartLegend legend = chart.getOrAddLegend();
                 legend.setPosition(LegendPosition.BOTTOM);

                 chartIndex++;
             }
                // 파일 저장
                try (FileOutputStream fos = new FileOutputStream("C:\\Users\\rhkdr\\OneDrive\\바탕 화면\\업무\\excel\\data_with_chart.xlsx")) {
                    workbook.write(fos);
                }

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

 

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>excel</groupId>
  <artifactId>MavenProject</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <name>MavenProject</name>
  <!-- FIXME change it to the project's website -->
  <url>http://www.example.com</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <maven.compiler.release>17</maven.compiler.release>
  </properties>

  <dependencyManagement>
    <dependencies>
      <dependency>
        <groupId>org.junit</groupId>
        <artifactId>junit-bom</artifactId>
        <version>5.11.0</version>
        <type>pom</type>
        <scope>import</scope>
      </dependency>
    </dependencies>
  </dependencyManagement>

  <dependencies>
    <dependency>
      <groupId>org.junit.jupiter</groupId>
      <artifactId>junit-jupiter-api</artifactId>
      <scope>test</scope>
    </dependency>
    <!-- Optionally: parameterized tests support -->
    <dependency>
      <groupId>org.junit.jupiter</groupId>
      <artifactId>junit-jupiter-params</artifactId>
      <scope>test</scope>
    </dependency>
    <!-- Apache POI -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>

        <!-- JFreeChart -->
        <dependency>
            <groupId>org.jfree</groupId>
            <artifactId>jfreechart</artifactId>
            <version>1.5.3</version>
        </dependency>

        <!-- JCommon (required by JFreeChart) -->
        <dependency>
            <groupId>org.jfree</groupId>
            <artifactId>jcommon</artifactId>
            <version>1.0.24</version>
        </dependency>
        <dependency>
	        <groupId>org.apache.poi</groupId>
	        <artifactId>poi-ooxml</artifactId>
	        <version>5.2.3</version>
    	</dependency>
    	<dependency>
	        <groupId>org.apache.logging.log4j</groupId>
	        <artifactId>log4j-core</artifactId>
	        <version>2.17.1</version>
    	</dependency>
	    <!-- Log4j2 API -->
	    <dependency>
	        <groupId>org.apache.logging.log4j</groupId>
	        <artifactId>log4j-api</artifactId>
	        <version>2.17.1</version>
	    </dependency>
  </dependencies>
  
  <build>
    <pluginManagement><!-- lock down plugins versions to avoid using Maven defaults (may be moved to parent pom) -->
      <plugins>
        <!-- clean lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#clean_Lifecycle -->
        <plugin>
          <artifactId>maven-clean-plugin</artifactId>
          <version>3.4.0</version>
        </plugin>
        <!-- default lifecycle, jar packaging: see https://maven.apache.org/ref/current/maven-core/default-bindings.html#Plugin_bindings_for_jar_packaging -->
        <plugin>
          <artifactId>maven-resources-plugin</artifactId>
          <version>3.3.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-compiler-plugin</artifactId>
          <version>3.13.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-surefire-plugin</artifactId>
          <version>3.3.0</version>
        </plugin>
        <plugin>
          <artifactId>maven-jar-plugin</artifactId>
          <version>3.4.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-install-plugin</artifactId>
          <version>3.1.2</version>
        </plugin>
        <plugin>
          <artifactId>maven-deploy-plugin</artifactId>
          <version>3.1.2</version>
        </plugin>
        <!-- site lifecycle, see https://maven.apache.org/ref/current/maven-core/lifecycles.html#site_Lifecycle -->
        <plugin>
          <artifactId>maven-site-plugin</artifactId>
          <version>3.12.1</version>
        </plugin>
        <plugin>
          <artifactId>maven-project-info-reports-plugin</artifactId>
          <version>3.6.1</version>
        </plugin>
      </plugins>
    </pluginManagement>
  </build>
</project>