How to read and write an Excel file?

How to read and write an Excel file?

About

This howto will demonstrate you how to read and write to an Excel resource.

Steps

Prerequisites

To following this howto, you should have followed the getting started guide to have knowledge of:

Create an Excel file with Random Data

The below data fill command will create a excel resource named d_date.xlsx in the temporary directory. 

tabli data fill --generator-selector date_dim--datagen.yml@howto date_dim.xlsx@temp

11:54:15 - INFO - The log level was set to INFO
11:54:15 - INFO - The following arguments were received  (data,fill,--generator-selector,date_dim--datagen.yml@howto,date_dim.xlsx@temp)
11:54:15 - INFO - The current file system path is /home/admin/code/java-mono/tabulify-cli/.
11:54:15 - INFO - The default connection was set to: `cd`
11:54:15 - INFO - No project was found.
11:54:15 - INFO - The command (data) was found
11:54:15 - INFO - The command (data.fill) was found
11:54:16 - INFO - Starting filling the tables for the data store  without the dependencies (the parent/foreign table)
11:54:16 - INFO - --buffer-size parameter NOT found. Using default : 20000
ERROR StatusLogger Could not register mbeans
 java.security.AccessControlException: access denied ("javax.management.MBeanTrustPermission" "register")
	at java.base/java.security.AccessControlContext.checkPermission(AccessControlContext.java:472)
	at java.base/java.lang.SecurityManager.checkPermission(SecurityManager.java:358)
	at java.management/com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.checkMBeanTrustPermission(DefaultMBeanServerInterceptor.java:1805)
	at java.management/com.sun.jmx.interceptor.DefaultMBeanServerInterceptor.registerMBean(DefaultMBeanServerInterceptor.java:318)
	at java.management/com.sun.jmx.mbeanserver.JmxMBeanServer.registerMBean(JmxMBeanServer.java:522)
	at org.apache.logging.log4j.core.jmx.Server.register(Server.java:400)
	at org.apache.logging.log4j.core.jmx.Server.reregisterMBeansAfterReconfigure(Server.java:168)
	at org.apache.logging.log4j.core.jmx.Server.reregisterMBeansAfterReconfigure(Server.java:141)
	at org.apache.logging.log4j.core.LoggerContext.setConfiguration(LoggerContext.java:632)
	at org.apache.logging.log4j.core.LoggerContext.reconfigure(LoggerContext.java:694)
	at org.apache.logging.log4j.core.LoggerContext.reconfigure(LoggerContext.java:711)
	at org.apache.logging.log4j.core.LoggerContext.start(LoggerContext.java:253)
	at org.apache.logging.log4j.core.impl.Log4jContextFactory.getContext(Log4jContextFactory.java:155)
	at org.apache.logging.log4j.core.impl.Log4jContextFactory.getContext(Log4jContextFactory.java:47)
	at org.apache.logging.log4j.LogManager.getContext(LogManager.java:196)
	at org.apache.logging.log4j.LogManager.getLogger(LogManager.java:599)
	at org.apache.poi.logging.PoiLogManager.getLogger(PoiLogManager.java:42)
	at org.apache.poi.ooxml.POIXMLDocumentPart.<clinit>(POIXMLDocumentPart.java:56)
	at com.tabulify.excel.ExcelSheet.<init>(ExcelSheet.java:66)
	at com.tabulify.excel.ExcelSheet$ExcelSheetConfig.build(ExcelSheet.java:259)
	at com.tabulify.excel.ExcelDataPath.getExcelSheet(ExcelDataPath.java:82)
	at com.tabulify.excel.ExcelDataPath.createFile(ExcelDataPath.java:140)
	at com.tabulify.excel.ExcelManager.create(ExcelManager.java:30)
	at com.tabulify.fs.FsDataSystem.create(FsDataSystem.java:212)
	at com.tabulify.spi.Tabulars.create(Tabulars.java:33)
	at com.tabulify.transfer.TransferSourceTarget.createTarget(TransferSourceTarget.java:475)
	at com.tabulify.transfer.TransferSourceTarget.targetPreOperationsAndCheck(TransferSourceTarget.java:302)
	at com.tabulify.transfer.TransferManager.runCrossTransfer(TransferManager.java:237)
	at com.tabulify.transfer.TransferManager.run(TransferManager.java:600)
	at com.tabulify.transfer.Transfers.transfers(Transfers.java:52)
	at com.tabulify.tabli.TabliDataTransferManager.runAndGetFeedBacks(TabliDataTransferManager.java:384)
	at com.tabulify.tabli.TabliDataFill.run(TabliDataFill.java:72)
	at com.tabulify.tabli.TabliData.run(TabliData.java:105)
	at com.tabulify.tabli.Tabli.main(Tabli.java:295)
	at javademo.run(javademo.java:3)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at net.bytle.docExec.DocExecutorUnit.eval(DocExecutorUnit.java:203)
	at net.bytle.docExec.DocExecutor.execute(DocExecutor.java:270)
	at net.bytle.docExec.DocExecutor.run(DocExecutor.java:125)
	at com.tabulify.doc.DocRun.main(DocRun.java:154)
	at com.tabulify.doc.DocRunTest.docTest(DocRunTest.java:17)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:566)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:59)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:56)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.BlockJUnit4ClassRunner$1.evaluate(BlockJUnit4ClassRunner.java:100)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:103)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:63)
	at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
	at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
	at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
	at org.junit.runner.JUnitCore.run(JUnitCore.java:115)
	at org.junit.vintage.engine.execution.RunnerExecutor.execute(RunnerExecutor.java:42)
	at org.junit.vintage.engine.execution.VintageExecutor.executeClassesAndMethodsSequentially(VintageExecutor.java:93)
	at org.junit.vintage.engine.execution.VintageExecutor.executeAllChildren(VintageExecutor.java:73)
	at org.junit.vintage.engine.VintageTestEngine.execute(VintageTestEngine.java:70)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:201)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:170)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:94)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.lambda$execute$0(EngineExecutionOrchestrator.java:59)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.withInterceptedStreams(EngineExecutionOrchestrator.java:142)
	at org.junit.platform.launcher.core.EngineExecutionOrchestrator.execute(EngineExecutionOrchestrator.java:58)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:103)
	at org.junit.platform.launcher.core.DefaultLauncher.execute(DefaultLauncher.java:85)
	at org.junit.platform.launcher.core.DelegatingLauncher.execute(DelegatingLauncher.java:47)
	at org.junit.platform.launcher.core.InterceptingLauncher.lambda$execute$1(InterceptingLauncher.java:39)
	at org.junit.platform.launcher.core.ClasspathAlignmentCheckingLauncherInterceptor.intercept(ClasspathAlignmentCheckingLauncherInterceptor.java:25)
	at org.junit.platform.launcher.core.InterceptingLauncher.execute(InterceptingLauncher.java:38)
	at org.junit.platform.launcher.core.DelegatingLauncher.execute(DelegatingLauncher.java:47)
	at org.junit.platform.launcher.core.SessionPerRequestLauncher.execute(SessionPerRequestLauncher.java:63)
	at com.intellij.junit5.JUnit5IdeaTestRunner.startRunnerWithArgs(JUnit5IdeaTestRunner.java:57)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
	at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
	at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
	at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
	at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)
11:54:16 - INFO - The target data operation (CREATE) was executed against the target (date_dim.xlsx@temp)

Source            Target               Latency (ms)   Row Count   Error   Message   
---------------   ------------------   ------------   ---------   -----   -------   
date_dim@memgen   date_dim.xlsx@temp   1126           1000                          

11:54:17 - INFO - Latency Time: 0:0:1.248 (hour:minutes:seconds:milli)
11:54:17 - INFO -        Ie (1248) milliseconds
11:54:17 - INFO - Done. Bye !
11:54:17 - INFO - To not see the `info` log, you can set the log level to `tip` with `tabli conf set --log-level tip`

Check the attributes

Check the attributes with the data info command. 

tabli data info date_dim.xlsx@temp

Read the data

What is the data of the file with the data head command? 

tabli data head --limit 30 d_date.xlsx@temp
Task Runner