Java在Excel中进行数据分析

╰半夏微凉° 2023-10-13 15:32 23阅读 0赞

摘要：本文由葡萄城技术团队于掘金原创并首发。转载请注明出处：[葡萄城官网][Link 1]，葡萄城为开发者提供专业的开发工具、解决方案和服务，赋能开发者。

前一段时间淘宝出了一个“淘宝人生”的模块，可以看从注册淘宝账号至今的消费记录，仔细想了想，现在微信、淘宝这些APP好像都喜欢出这种记录使用者的支付、消费情况的功能。不过这个显示消费记录的功能的确让人觉得方便很多。这样大家就可以随时随地的查看以前的消费记录，有时候需要查账，翻一翻手机就能看见钱都去哪里了，而且每一笔钱的流向都可以看得非常清楚。既然这个东西这么好用，那可不可以我也搞一个类似的分析工具，这样就可以用它来记录生活中的点点滴滴。由于本人的工作性质，对Excel比较熟悉，首先想到的就是可不可以用一个表格可视化工具来实现这个功能。

说干就干，先上网找了找了一些Excel中可视化工具的样式，看了看在Excel中比较流行就是图表（柱形图、条形图等）和数据透视图了。因为图表是平时用的比较多的工具，所以在好奇心的驱使下，百度了一下“如何用代码在表格中搞一个数据透视图”! ，浏览着看了看，发现有很多种语言都可以实现（Python、Java、JavaScript、.net等）。鉴于自己对Java语言比较熟悉，所以便继续百度“如何用Java在Excel中搞一个数据透视表”。发现可以使用[Apache POI][]库来实现：

java

复制代码

`import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import java.io.FileOutputStream; import java.io.IOException; public class PivotTableExample { public static void main(String[] args) throws IOException { // 创建工作簿 Workbook workbook = new XSSFWorkbook(); // 创建工作表 Sheet sheet = workbook.createSheet("Data"); // 输入数据 Row headingRow = sheet.createRow(0); headingRow.createCell(0).setCellValue("Category"); headingRow.createCell(1).setCellValue("Value"); Row dataRow1 = sheet.createRow(1); dataRow1.createCell(0).setCellValue("A"); dataRow1.createCell(1).setCellValue(10); Row dataRow2 = sheet.createRow(2); dataRow2.createCell(0).setCellValue("B"); dataRow2.createCell(1).setCellValue(20); Row dataRow3 = sheet.createRow(3); dataRow3.createCell(0).setCellValue("A"); dataRow3.createCell(1).setCellValue(15); // 创建数据透视表 XSSFPivotTable pivotTable = ((XSSFSheet) sheet).createPivotTable(new AreaReference("A1:B3", SpreadsheetVersion.EXCEL2007), new CellReference("D5")); // 设置行标签 pivotTable.addRowLabel(0); // 设置值字段 pivotTable.addColumnLabel(DataConsolidateFunction.SUM, 1, "Sum of Value"); // 保存Excel文件 FileOutputStream fileOut = new FileOutputStream("pivotTable.xlsx"); workbook.write(fileOut); fileOut.close(); System.*out*.println("数据透视表已创建并保存到文件！"); } }`

除了使用Apache POI库，还发现了一款商业软件GcExcel，由于不太不了解这个东西，所以简单的ChatGpt了一下GcExcel，

![format_png][]

查完之后发现，和Apache POI库类似，GcExcel同样也是一个基于Java的表格操作库，于是怀着好奇的心态，又百度了一下“Java实现GcExcel数据透视表”。找到了一个[GcExcel的学习指南][GcExcel]，里面有一些源码和代码讲解，根据里面的入门教程自己写了一个小的实现数据透视表的Demo（由于完整代码太长，只截取了部分）：

想要完整代码的童鞋可以从[Gitee][]或[Github][]中下载：

java

复制代码

`public class Main { public static void main(String[] args){ Workbook workbook = new Workbook(); //创建一个WorkSheet的对象 IWorksheet worksheet = workbook.getWorksheets().get(0); //-----------------------------设置数据值------------------------------ worksheet.getRange("B3:C7").setValue(new Object[][]{ {"ITEM", "AMOUNT"}, {"Income 1", 2500}, {"Income 2", 1000}, {"Income 3", 250}, {"Other", 250}, }); worksheet.getRange("B10:C23").setValue(new Object[][]{ {"ITEM", "AMOUNT"}, {"Rent/mortgage", 800}, {"Electric", 120}, {"Gas", 50}, {"Cell phone", 45}, {"Groceries", 500}, {"Car payment", 273}, {"Auto expenses", 120}, {"Student loans", 50}, {"Credit cards", 100}, {"Auto Insurance", 78}, {"Personal care", 50}, {"Entertainment", 100}, {"Miscellaneous", 50}, }); //合并单元格 worksheet.getRange("B2:C2").merge(); worksheet.getRange("B2").setValue("MONTHLY INCOME"); worksheet.getRange("B9:C9").merge(); worksheet.getRange("B9").setValue("MONTHLY EXPENSES"); worksheet.getRange("E2:G2").merge(); worksheet.getRange("E2").setValue("PERCENTAGE OF INCOME SPENT"); worksheet.getRange("E5:G5").merge(); worksheet.getRange("E5").setValue("SUMMARY"); worksheet.getRange("E3:F3").merge(); worksheet.getRange("E9").setValue("BALANCE"); worksheet.getRange("E6").setValue("Total Monthly Income"); worksheet.getRange("E7").setValue("Total Monthly Expenses"); //--------------------------------设置形状-------------------------------- IShape shape = worksheet.getShapes().addChart(ChartType.*ColumnClustered*, 339, 247, 316.5, 346); shape.getChart().getChartArea().getFormat().getLine().setTransparency(1); shape.getChart().getColumnGroups().get(0).setOverlap(0); shape.getChart().getColumnGroups().get(0).setGapWidth(37); IAxis category_axis = shape.getChart().getAxes().item(AxisType.*Category*); category_axis.getFormat().getLine().getColor().setRGB(Color.*GetBlack*()); category_axis.getTickLabels().getFont().setSize(11); category_axis.getTickLabels().getFont().getColor().setRGB(Color.*GetBlack*()); IAxis series_axis = shape.getChart().getAxes().item(AxisType.*Value*); series_axis.getFormat().getLine().setWeight(1); series_axis.getFormat().getLine().getColor().setRGB(Color.*GetBlack*()); series_axis.getTickLabels().setNumberFormat("\$\#\#\#0"); series_axis.getTickLabels().getFont().setSize(11); series_axis.getTickLabels().getFont().getColor().setRGB(Color.*GetBlack*()); ISeries chartSeries = shape.getChart().getSeriesCollection().newSeries(); chartSeries.setFormula("=SERIES(\"Simple Budget\",{"Income\","Expenses\"},'Sheet1'!$G$6:$G$7,1)"); chartSeries.getPoints().get(0).getFormat().getFill().getColor().setRGB(Color.*FromArgb*(176, 21, 19)); chartSeries.getPoints().get(1).getFormat().getFill().getColor().setRGB(Color.*FromArgb*(234, 99, 18)); chartSeries.getDataLabels().getFont().setSize(11); chartSeries.getDataLabels().getFont().getColor().setRGB(Color.*GetBlack*()); chartSeries.getDataLabels().setShowValue(true); chartSeries.getDataLabels().setPosition(DataLabelPosition.*OutsideEnd*); workbook.save("tutorial.xlsx"); } }`

最终的Excel样式：

![format_png 1][]

通过以上的实验，使用Apache POI和GcExcel都可以在Excel中实现数据透视表，您可以根据您项目或工程的需要选择合适的方法。

**扩展链接：**

[怎样让您的系统也有“数据透视表”功能][Link 2]

[数据透视表上线！如何在纯前端实现这个强大的数据分析功能][Link 3]

[纯前端Excel数据透视表实现损益表应用][Excel]

[Link 1]: https://link.juejin.cn?target=https%3A%2F%2Fwww.grapecity.com.cn%2F
[Apache POI]: https://link.juejin.cn?target=https%3A%2F%2Fpoi.apache.org%2F
[format_png]: https://img-blog.csdnimg.cn/img_convert/0f7670134ea0e544f446e28c3ff5cbe7.webp?x-oss-process=image/format,png
[GcExcel]: https://link.juejin.cn?target=https%3A%2F%2Fwww.grapecity.com%2Fdocuments-api-excel-java%2Fdemos%2Fpivottable
[Gitee]: https://link.juejin.cn?target=https%3A%2F%2Fgitee.com%2FGrapeCity%2Fgcexcel-tutorial-gitee
[Github]: https://link.juejin.cn?target=https%3A%2F%2Fgithub.com%2FGrapeCityXA%2Fgcexcel-tutorial-github
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/c390d135ffd0badf7e076555e07056b4.webp?x-oss-process=image/format,png
[Link 2]: https://link.juejin.cn?target=https%3A%2F%2Fwww.grapecity.com.cn%2Fblogs%2Fwithpivottable
[Link 3]: https://link.juejin.cn?target=https%3A%2F%2Fwww.grapecity.com.cn%2Fblogs%2Fspreadjs-pivottable-online
[Excel]: https://link.juejin.cn?target=https%3A%2F%2Fwww.grapecity.com.cn%2Fblogs%2Fspreadjs-pivot-table-realizes-income-statement-application