我想知道是否有办法使用 Apache POI (XSSF) 调整图表大小。目前我正在使用 Excel 模板,该模板具有一个图表,当使用 nameRanges 插入更多数据时,该图表会发生变化。
一切正常,我面临的唯一麻烦是:
- 图表始终保持相同的大小,因此如果有更多条目,它就会变得混乱,使图表变得毫无用处。
- 我正在使用日期,但无法在图表上将日期表示为日/月 (17/10)。基本上,它写的是 36982,而不是 04/01/2001。
该工作簿的目的是列出几项工作并检查它们在给定日期是否花费了更长的时间,该图表旨在帮助识别更长的运行时间的发生情况。作业运行时间可能从几秒到几小时不等。
这是我正在使用的代码:
package le_package.poi_tests.xssflibrary;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Name;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class POIReadFile {
public static void main(String[] args) {
try
{
String jobName = "I am a job";
String jobParent = "I am your father, Job.";
int rowNum = 40;
int deface = 4;
//Open Excel as OOXML
XSSFWorkbook currentWorkbook = new XSSFWorkbook( OPCPackage.open("include/excelTemplate.xlsx"));
//Get sheet in position 0
Sheet currentSheet = currentWorkbook.getSheetAt(0);
//Get sheet name for processing
String sheetName = currentSheet.getSheetName();
//Set values for headers
currentSheet.getRow(1).getCell(0).setCellValue(jobName);
currentSheet.getRow(1).getCell(1).setCellValue(jobParent);
for (int i=0; i<rowNum; i++)
{
//Create row in a given position
Row newRow = currentSheet.createRow(i+deface);
//Create cell within row
Cell newCell0 = newRow.createCell(0);
Cell newCell1 = newRow.createCell(1);
Cell newCell2 = newRow.createCell(2);
String cellDate = "";
/* Set CellType
* 0 - Numeric | 1 - String | 2 - Formula | 3 - Blank | 4 - Boolean | 5 - Error */
newCell0.setCellType(0);
cellDate = "3/"+(i+1)+"/2001 00:00:00";
//Convert text into date
Date currentCellDate = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse(cellDate);
//System.out.println(currentCellDate.toString()+"--"+cellDate);
//Set CellValue
newCell0.setCellValue(currentCellDate);
cellDate = "4/"+(i+1)+"/2001 00:00:00";
currentCellDate = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss").parse(cellDate);
//System.out.println(currentCellDate.toString()+"--"+cellDate);
newCell1.setCellType(0);
newCell1.setCellValue(currentCellDate);
//setCellFormula sets the formula to be evaluated by excel, it doesn't need to start with '='
newCell2.setCellFormula("A" + (i+deface+1) + "-B" + (i+deface+1));
}
//Search for named range
Name rangeCell = currentWorkbook.getName("startRange");
//Set new range for named range
String reference = sheetName + "!$A$" + ( deface+1 ) + ":$A$" + ( rowNum+deface );
//Assigns range value to named range
rangeCell.setRefersToFormula(reference);
rangeCell = currentWorkbook.getName("endRange");
reference = sheetName + "!$B$"+(deface+1) + ":$B$" + (rowNum+deface);
rangeCell.setRefersToFormula(reference);
rangeCell = currentWorkbook.getName("elapsedTime");
reference = sheetName + "!$C$"+(deface+1) + ":$C$" + (rowNum+deface);
rangeCell.setRefersToFormula(reference);
//Create a fileStream to write into a file
FileOutputStream newExcelFile = new FileOutputStream(jobName+".xlsx");
//Write Stream
currentWorkbook.write(newExcelFile);
//Close New Excel File
newExcelFile.close();
}
catch (Exception e)
{
System.out.println("AAAAARGH, I was wounded by the following exception!:");
e.printStackTrace();
System.out.println("Sorry, your program is dead :(");
}
}
}
在研究了 xlsx 的工作原理之后,我找到了如何完成它。
//Call the partiarch to start drawing
XSSFDrawing drawing = ((XSSFSheet)currentSheet).createDrawingPatriarch();
//Create CTMarket for anchor
CTMarker chartEndCoords = CTMarker.Factory.newInstance();
//The coordinates are set in columns and rows, not pixels.
chartEndCoords.setCol(column);
//Set Column offset
chartEndCoords.setColOff(0);
chartEndCoords.setRow(row);
chartEndCoords.setRowOff(0);
//drawing.getCTDrawing().getTwoCellAnchorArray(0).setFrom(chartStartCoords);
drawing.getCTDrawing().getTwoCellAnchorArray(0).setTo(chartEndCoords);
/*
This line of code allows to resize the chart:
The Patriarch is what allows to get control over the drawings, since
a chart is considered a graph in xlsx you can access it with getCTDrawing.
Each graph is stored in the tag getTwoCellAnchorArray, where the array position
is the chart you have; for example getTwoCellAnchorArray(3) would refer to the
forth graph within the sheet.
Each getTwoCellAnchorArray has several properties as FROM and TO, which define
where the existing graph starts and ends.
*/
如果您有任何意见,请告诉我。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)