Spread Service For JAVA 如何实现下图所示的Excel?

葡萄城技术团队
  • 1.4k

Spread Service For JAVA 如何实现下图所示的Excel?
图片描述

回复
阅读 1.2k
1 个回答

1.初始化workbook
(初始化workbook会默认初始化一个worksheet),获取worksheet对象,之后更改sheet标签名称为Tables

Workbook workbook = new Workbook();
IWorksheet worksheet = workbook.getWorksheets().get(0);
worksheet.setName("Table");

2.插入数据:
Spread Service中支持插入二位数组形式的插入方式,例如下面所示:

    Object[][] sourceData = new Object[][]{
                {"ITEM", "AMOUNT"},
                {"Income 1", 2500},
                {"Income 2", 1000},
                {"Income 3", 250},
                {"Other", 250},
            };
            Object[][] sourceData1 = new Object[][]{
                {"ITEM", "AMOUNT"},
                {"Rent/mortgage", 800},
                {"Electricity", 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("B3:C7").setValue(sourceData);
        worksheet.getRange("B10:C23").setValue(sourceData1);

3.设置单元格合并:

    worksheet.getRange("B3:C7").setValue(sourceData);
    worksheet.getRange("B10:C23").setValue(sourceData1);
    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");

4.创建表格,设置表名,设置表样式

    //Create the first table to show Income.
    ITable incomeTable = worksheet.getTables().add(worksheet.getRange("B3:C7"), true);
        incomeTable.setName("tblIncome");
    incomeTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4"));
            //Create the second table to show Expenses.
    ITable expensesTable = worksheet.getTables().add(worksheet.getRange("B10:C23"), true);
        expensesTable.setName("tblExpenses");
    expensesTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4"));incomeTable.setTableStyle(workbook.getTableStyles().get("TableStyleMedium4"));

5.设置表格公式
因为合计公式需要重复使用,可以定义名称将其以别名命名

    worksheet.getNames().add("TotalMonthlyIncome", "=SUM(tblIncome[AMOUNT])");
    worksheet.getNames().add("TotalMonthlyExpenses", "=SUM(tblExpenses[AMOUNT])");
    之后我们就可以通过别名来快捷调用这些公式了
    worksheet.getRange("E3").setFormula("=TotalMonthlyExpenses");
    worksheet.getRange("G3").setFormula("=TotalMonthlyExpenses/TotalMonthlyIncome");
    worksheet.getRange("G6").setFormula("=TotalMonthlyIncome");
    worksheet.getRange("G7").setFormula("=TotalMonthlyExpenses");
    worksheet.getRange("G9").setFormula("=TotalMonthlyIncome-TotalMonthlyExpenses");

6.最后以文件流形式导出生成Excel文件

    File f= new File("d:" + File.separator + "outputExcel.xlsx");
            OutputStream out = null;
            out = new FileOutputStream(f);
            workbook.save(out);
    out.close();

大功告成,让我们打开导出的Excel看一下效果:
图片描述

以上就是 Spread Service 在java平台表格相关的功能示例,相信看了之后大家对 Spread Service 的表格应用会有一些收获,除此之外,Spread表格组件还有许多强大的功能,有兴趣的朋友可以免费试用本产品(发送邮件至marketing.xa@grapecity.com),或者关注我们葡萄城官方团队博客:Spread 技术博客

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
你知道吗?

logo
葡萄城
子站问答

葡萄城技术问答

访问社区
宣传栏