{"id":10008420,"date":"2024-03-04T13:21:28","date_gmt":"2024-03-04T05:21:28","guid":{"rendered":"https:\/\/blog.itmax.cn\/?p=10008420"},"modified":"2024-03-04T13:21:28","modified_gmt":"2024-03-04T05:21:28","slug":"%e5%a6%82%e4%bd%95%e5%9c%a8-java-%e4%b8%ad%e4%bb%a5%e7%bc%96%e7%a8%8b%e7%9a%84%e6%96%b9%e5%bc%8f%e5%b0%86-csv-%e8%bd%ac%e4%b8%ba-excel-xlsx-%e6%96%87%e4%bb%b6","status":"publish","type":"post","link":"https:\/\/itmax.cn\/?p=10008420","title":{"rendered":"\u5982\u4f55\u5728 Java \u4e2d\u4ee5\u7f16\u7a0b\u7684\u65b9\u5f0f\u5c06 CSV \u8f6c\u4e3a Excel XLSX \u6587\u4ef6"},"content":{"rendered":"

\u524d\u8a00<\/strong><\/p>\n

Microsoft Excel\u7684XLSX\u683c\u5f0f\u4ee5\u53ca\u57fa\u4e8e\u6587\u672c\u7684CSV\uff08\u9017\u53f7\u5206\u9694\u503c\uff09\u683c\u5f0f\uff0c\u662f\u6570\u636e\u4ea4\u6362\u4e2d\u5e38\u89c1\u7684\u6587\u4ef6\u683c\u5f0f\u3002\u5e94\u7528\u7a0b\u5e8f\u901a\u8fc7\u5b9e\u73b0\u5bf9\u8fd9\u4e9b\u683c\u5f0f\u7684\u8bfb\u5199\u652f\u6301\uff0c\u53ef\u4ee5\u663e\u8457\u63d0\u5347\u6027\u80fd\u3002\u5728\u672c\u6587\u4e2d\uff0c\u5c0f\u7f16\u5c06\u4e3a\u5927\u5bb6\u4ecb\u7ecd\u5982\u4f55\u501f\u52a9\u8461\u8404\u57ce\u516c\u53f8\u5730Java API\u7ec4\u4ef6GrapeCity Documents for Excel<\/a>\uff08\u4ee5\u4e0b\u7b80\u79f0GcExcel\uff09\u5c06CSV\u6587\u4ef6\u8f6c\u5316\u4e3aXLSX \u6587\u4ef6\u3002<\/p>\n

\u5177\u4f53\u64cd\u4f5c\u6b65\u9aa4\u5982\u4e0b\uff1a<\/p>\n

    \n
  1. \u521b\u5efa\u9879\u76ee\uff08\u4f7f\u7528intelliJ IDEA\u521b\u5efa\u4e00\u4e2a\u65b0\u7684Maven\u9879\u76ee\uff09<\/li>\n
  2. \u67e5\u8be2\u6570\u636e\uff08\u4f7f\u7528AlphaVantage Web\u670d\u52a1\u83b7\u53d6CSV\u683c\u5f0f\u7684\u6708\u5ea6BTC-USD\u6570\u636e\uff09<\/li>\n
  3. \u52a0\u8f7dCSV<\/li>\n
  4. \u5904\u7406CSV\uff08\u91cd\u65b0\u6392\u5217\u3001\u521b\u5efa\u8868\u683c\u5e76\u521b\u5efa\u5e26\u6709\u8d8b\u52bf\u7ebf\u7684\u56fe\u8868\uff09<\/li>\n
  5. \u8fd4\u56deXLSX<\/li>\n<\/ol>\n

    1\uff09\u521b\u5efa\u9879\u76ee<\/strong><\/p>\n

    \uff081\uff09\u4f7f\u7528intelliJ IDEA\uff0c\u521b\u5efa\u4e00\u4e2a\u65b0\u9879\u76ee\u3002
    \"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"<\/p>\n

    \uff082\uff09\u4e3a\u9879\u76ee\u8f93\u5165\u9879\u76ee\u540d\u79f0\u201cBTC_Chart_WebService\u201d\uff0c\u5e76\u5728\u5de6\u4fa7\u83dc\u5355\u4e2d\u9009\u62e9Maven Archetype\u540e\u9009\u62e9\u4e00\u4e2a\u9879\u76ee\u5b58\u653e\u4f4d\u7f6e\uff0c\u7136\u540e\u70b9\u51fb\u4e0b\u4e00\u6b65\u3002
    \"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"<\/p>\n

    \uff083\uff09\u6253\u5f00pom.xml\u6587\u4ef6\u5e76\u6dfb\u52a0GcExcel\u4f9d\u8d56\u9879<\/p>\n

    <dependency>\n  <groupId>com.grapecitysoft.documents<\/groupId>\n  <artifactId>gcexcel<\/artifactId>\n  <version>7.0.1<\/version>\n<\/dependency><\/code><\/pre>\n
    \"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"<\/p>\n
    2\uff09\u67e5\u8be2\u6570\u636e<\/strong><\/p>\n
    \u521b\u5efa\u4e00\u4e2aCSV\u7c7b\uff0c\u7528\u4e8e\u4eceAlphaVantage Web\u670d\u52a1\u67e5\u8be2CSV\u683c\u5f0f\u7684\u6708\u5ea6BTC-USD\u6570\u636e\u3002<\/p>\n
    public static String getCsvData() {\n    String csv = null;\n    String apiKey = "YOUR_API_KEY";\n\n    String queryUrl = String.format(\n            "https:\/\/www.alphavantage.co\/query?function=DIGITAL_CURRENCY_MONTHLY&symbol=BTC&market=USD&apikey=%s&datatype=csv",\n            apiKey);\n\n    HttpClient client = HttpClient.newHttpClient();\n    HttpRequest request = HttpRequest.newBuilder()\n            .uri(URI.create(queryUrl))\n            .GET()\n            .build();\n    try {\n        HttpResponse<String> response = client.send(request, HttpResponse.BodyHandlers.ofString());\n        csv = response.body();\n    } catch (IOException | InterruptedException e) {\n        e.printStackTrace();\n    }\n\n    return csv;\n}<\/code><\/pre>\n
    3\uff09\u52a0\u8f7dCSV<\/strong><\/p>\n
    \u63a5\u4e0b\u6765\uff0c\u521b\u5efa\u4e00\u4e2aXLSX\u7c7b\uff0c\u5e76\u6dfb\u52a0\u65b9\u6cd5AnalysisCSV\uff0c\u5165\u53c2\u4e3acsv\u5b57\u7b26\u4e32\uff0c\u8be5\u65b9\u6cd5\u4f1a\u8fd4\u56de\u4e00\u4e2aWorkbook\u7c7b\u578b\u7684\u5de5\u4f5c\u7c3f\u5bf9\u8c61\u3002<\/p>\n
    public static Workbook AnalysisCSV(String csvStr) {\n    Workbook workbook = new Workbook();\n    byte[] arr = csvStr.getBytes(StandardCharsets.UTF_8);\n    try (InputStream s = new ByteArrayInputStream(arr)) {\n        workbook.open(s, OpenFileFormat.Csv);\n    } catch (IOException e) {\n        e.printStackTrace();\n    }\n    return workbook;\n}<\/code><\/pre>\n
    4\uff09\u5904\u7406CSV<\/strong><\/p>\n
    \u9996\u5148\uff0c\u7528\u4ee3\u7801\u83b7\u53d6\u5e26\u6709CSV\u6570\u636e\u7684IWorksheet\uff0c\u5e76\u91cd\u6392\u5217\u5217\u7684\u987a\u5e8f\uff0c\u5c06Volume\u5217\u653e\u5728Date\u548cOpen\u5217\u4e4b\u95f4\u3002\u7136\u540e\uff0c\u521b\u5efa\u4e00\u4e2a\u540d\u4e3a\u201c\u6bcf\u6708\u6bd4\u7279\u5e01\u6570\u636e\u201d\u7684\u8868\u683c\uff0c\u5305\u542bCSV\u6570\u636e\uff0c\u5e76\u81ea\u52a8\u9002\u5e94\u8868\u4e2d\u7684\u5217\u3002<\/p>\n
    \u63a5\u7740\uff0c\u5728\u5de5\u4f5c\u8868\u4e2d\u6dfb\u52a0\u4e00\u4e2a\u65b0\u7684StockVOHLC\uff08\u6210\u4ea4\u91cf\u3001\u5f00\u76d8\u3001\u6700\u9ad8\u3001\u6700\u4f4e\u3001\u6536\u76d8\uff09\u7c7b\u578b\u7684\u56fe\u6807\uff0c\u5e76\u8986\u76d6\u6574\u4e2a\u8868\u683c\u8303\u56f4\uff1b\u8bbe\u7f6e\u56fe\u6807\u7684\u6807\u9898\uff0c\u6dfb\u52a0\u7cfb\u5217\u5230\u56fe\u6807\uff0c\u6539\u53d8\u7c7b\u522b\u8f74\u7684\u5355\u4f4d\u4e3a\u6708\u4efd\uff0c\u66f4\u65b0\u7c7b\u522b\u8f74\u523b\u5ea6\u6807\u7b7e\u7684\u65b9\u5411\u548c\u6570\u5b57\u683c\u5f0f\uff1b\u7136\u540e\u521b\u5efa\u4e09\u6761\u8d8b\u52bf\u7ebf\u3002\u8d8b\u52bf\u7ebf\u5206\u522b\u4ee5\u84dd\u8272\u3001\u7eff\u8272\u548c\u7ea2\u8272\u663e\u793a\u6210\u4ea4\u91cf\u3001\u6700\u9ad8\u4ef7\u548c\u6700\u4f4e\u4ef7\u7684\u4e09\u4e2a\u6708\u79fb\u52a8\u5e73\u5747\u7ebf\u3002<\/p>\n
    public static void processWorkbook(Workbook workbook){\n    IWorksheet worksheet = workbook.getWorksheets().get(0);\n    \/\/ \u628a\u7b2cK\u5217\u6570\u636e\u79fb\u52a8\u5230B\u5217\u4f4d\u7f6e\n    worksheet.getRange("B:B").insert();\n    worksheet.getRange("K:K").copy(worksheet.getRange("B:B"));\n    worksheet.getRange("K:K").delete();\n\n    \/\/ \u83b7\u53d6\u6570\u636e\u8303\u56f4\n    IRange usedRange = worksheet.getUsedRange();\n\n    \/\/ \u521b\u5efa\u8868\u683c\n    ITable addTable = worksheet.getTables().add(usedRange, true);\n    addTable.setName("\u6bcf\u6708\u6bd4\u7279\u5e01\u6570\u636e");\n    usedRange.autoFit();\n\n    \/\/ \u521b\u5efa\u56fe\u8868\n    IShape iShape = worksheet.getShapes().addChart(ChartType.StockVOHLC, 0, 0, usedRange.getWidth(), usedRange.getHeight());\n    IChart chart = iShape.getChart();\n    chart.getChartTitle().setText("\u6bd4\u7279\u5e01\u6bcf\u6708\u5f00\u76d8-\u6700\u9ad8-\u6700\u4f4e-\u6536\u76d8-\u6210\u4ea4\u91cf");\n    chart.getSeriesCollection().add(worksheet.getRange(0,0,usedRange.getRowCount(),6), RowCol.Columns,true,true);\n    IAxis categoryAxis  = chart.getAxes().item(AxisType.Category);\n    categoryAxis.setBaseUnit(TimeUnit.Months);\n    categoryAxis.getTickLabels().setOrientation(45);\n    categoryAxis.getTickLabels().setNumberFormat("d\/m\/yyyy");\n\n    ITrendline voltrend  = chart.getSeriesCollection().get(0).getTrendlines().add();\n    voltrend.setName("3\u4e2a\u6708\u79fb\u52a8\u5e73\u5747\u6210\u4ea4\u91cf");\n    voltrend.setType(TrendlineType.MovingAvg);\n    voltrend.setPeriod(3);\n    voltrend.getFormat().getLine().getColor().setRGB(Color.GetBlue());\n    voltrend.getFormat().getLine().setDashStyle(LineDashStyle.RoundDot);\n\n    ITrendline hightrend  = chart.getSeriesCollection().get(2).getTrendlines().add();\n    hightrend.setName("3\u4e2a\u6708\u79fb\u52a8\u5e73\u5747\u6700\u9ad8\u4ef7");\n    hightrend.setType(TrendlineType.MovingAvg);\n    hightrend.setPeriod(3);\n    hightrend.getFormat().getLine().getColor().setRGB(Color.GetGreen());\n    hightrend.getFormat().getLine().setDashStyle(LineDashStyle.RoundDot);\n\n    ITrendline lowtrend  = chart.getSeriesCollection().get(3).getTrendlines().add();\n    lowtrend.setName("3\u4e2a\u6708\u79fb\u52a8\u5e73\u5747\u6700\u4f4e\u4ef7");\n    lowtrend.setType(TrendlineType.MovingAvg);\n    lowtrend.setPeriod(3);\n    lowtrend.getFormat().getLine().getColor().setRGB(Color.GetRed());\n    lowtrend.getFormat().getLine().setDashStyle(LineDashStyle.RoundDot);\n}<\/code><\/pre>\n
    5\uff09\u8fd4\u56deXLSX<\/strong><\/p>\n
    \u6700\u540e\uff0c\u521b\u5efa\u4e00\u4e2aMain\u7c7b\uff0c\u5e76\u6dfb\u52a0\u76f8\u5173\u65b9\u6cd5\u4f5c\u4e3a\u6574\u4e2a\u7a0b\u5e8f\u7684\u5165\u53e3\uff0c\u53f3\u952e\u6267\u884c\u7a0b\u5e8f\u540e\u5c31\u53ef\u4ee5\u83b7\u5f97\u6700\u7ec8\u7684Excel XLSX\u6587\u4ef6\u3002<\/p>\n
    public static void main(String[] args) {\n    Workbook workbook = AnalysisCSV(CSV.getCsvData());\n    processWorkbook(workbook);\n    workbook.save("\u6bd4\u7279\u5e01\u6708\u5ea6\u6570\u636e.xlsx");\n}<\/code><\/pre>\n
    \"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"<\/p>\n
    \u8fd0\u884c\u7ed3\u679c\u5982\u4e0b\u6240\u793a\uff1a
    \"\u5728\u8fd9\u91cc\u63d2\u5165\u56fe\u7247\u63cf\u8ff0\"<\/p>\n
    \u6700\u540e\u9644\u4e0a\u5b8c\u6574\u7684\u4ee3\u7801\u5de5\u7a0b\u6587\u4ef6\uff1ahttps:\/\/gitee.com\/GrapeCity\/btc_-chart_-web-service<\/a><\/p>\n
    \u603b\u7ed3<\/strong><\/p>\n
    \u4ee5\u4e0a\u5c31\u662f\u5728Java \u4e2d\u4ee5\u7f16\u7a0b\u7684\u65b9\u5f0f\u5c06 CSV\u8f6c\u5316\u4e3a Excel XLSX \u6587\u4ef6\u7684\u5168\u8fc7\u7a0b\uff0c\u5982\u679c\u60a8\u60f3\u4e86\u89e3\u66f4\u591a\u4fe1\u606f\uff0c\u6b22\u8fce\u70b9\u51fb\u8fd9\u7bc7\u53c2\u8003\u8d44\u6599<\/a>\u8bbf\u95ee\u3002<\/p>\n
    \u6269\u5c55\u94fe\u63a5\uff1a<\/strong><\/p>\n
    \u8f7b\u677e\u6784\u5efa\u4f4e\u4ee3\u7801\u5de5\u4f5c\u6d41\u7a0b\uff1a\u7b80\u5316\u7e41\u7410\u4efb\u52a1\u7684\u5229\u5668<\/a><\/p>\n
     \u4f18\u5316\u9884\u7b97\u7ba1\u7406\u6d41\u7a0b\uff1aWeb\u7aef\u5b9e\u73b0\u9884\u7b97\u7f16\u5236\u7684\u5229\u5668<\/a><\/p>\n
    \u5982\u4f55\u5728.NET\u7535\u5b50\u8868\u683c\u5e94\u7528\u7a0b\u5e8f\u4e2d\u521b\u5efa\u6d41\u7a0b\u56fe<\/a><\/p>\n
    https:\/\/segmentfault.com\/a\/1190000044679327<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
    \u524d\u8a00 Microsoft Excel\u7684XLSX\u683c\u5f0f\u4ee5\u53ca\u57fa\u4e8e\u6587\u672c\u7684CSV\uff08\u9017\u53f7\u5206\u9694\u503c\uff09\u683c\u5f0f\uff0c\u662f\u6570\u636e\u4ea4\u6362\u4e2d\u5e38\u89c1\u7684\u6587 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[],"class_list":["post-10008420","post","type-post","status-publish","format-standard","hentry","category-share"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10008420","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10008420"}],"version-history":[{"count":0,"href":"https:\/\/itmax.cn\/index.php?rest_route=\/wp\/v2\/posts\/10008420\/revisions"}],"wp:attachment":[{"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10008420"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10008420"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/itmax.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10008420"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}