excel,scala,unzip , How to unzip a zip file using scala?

How to unzip a zip file using scala?


Tag: excel,scala,unzip

Basically I need to unzip a .zip file which contains a folder called modeled which in turn contains a number of excel files.

I have had some luck in finding code that was already written (ZipArchive) which is meant to unzip the zip file, but I cannot figure out why it throws an error message when I use it. The code for ZipArchive and the error message are listed below:

import java.io.{OutputStream, InputStream, File, FileOutputStream}
import java.util.zip.{ZipEntry, ZipFile}
import scala.collection.JavaConversions._

object ZipArchive {

  val BUFSIZE = 4096
  val buffer = new Array[Byte](BUFSIZE)

  def unZip(source: String, targetFolder: String) = {
    val zipFile = new ZipFile(source)

    unzipAllFile(zipFile.entries.toList, getZipEntryInputStream(zipFile)_, new File(targetFolder))

  def getZipEntryInputStream(zipFile: ZipFile)(entry: ZipEntry) = zipFile.getInputStream(entry)

  def unzipAllFile(entryList: List[ZipEntry], inputGetter: (ZipEntry) => InputStream, targetFolder: File): Boolean = {

    entryList match {
      case entry :: entries =>

        if (entry.isDirectory)
          new File(targetFolder, entry.getName).mkdirs
          saveFile(inputGetter(entry), new FileOutputStream(new File(targetFolder, entry.getName)))

        unzipAllFile(entries, inputGetter, targetFolder)
      case _ =>

  def saveFile(fis: InputStream, fos: OutputStream) = {
    writeToFile(bufferReader(fis)_, fos)

  def bufferReader(fis: InputStream)(buffer: Array[Byte]) = (fis.read(buffer), buffer)

  def writeToFile(reader: (Array[Byte]) => Tuple2[Int, Array[Byte]], fos: OutputStream): Boolean = {
    val (length, data) = reader(buffer)
    if (length >= 0) {
      fos.write(data, 0, length)
      writeToFile(reader, fos)
    } else

Error Message:

java.io.FileNotFoundException: src/test/resources/oepTemp/modeled/EQ_US_2_NULL_('CA')_ALL_ELT_IL_EQ_US.xlsx (No such file or directory), took 6.406 sec
[error]     at java.io.FileOutputStream.open(Native Method)
[error]     at java.io.FileOutputStream.<init>(FileOutputStream.java:221)
[error]     at java.io.FileOutputStream.<init>(FileOutputStream.java:171)
[error]     at com.contract.testing.ZipArchive$.unzipAllFile(ZipArchive.scala:28)
[error]     at com.contract.testing.ZipArchive$.unZip(ZipArchive.scala:15)
[error]     at com.contract.testing.OepStepDefinitions$$anonfun$1.apply$mcZ$sp(OepStepDefinitions.scala:175)
[error]     at com.contract.testing.OepStepDefinitions$$anonfun$1.apply(OepStepDefinitions.scala:150)
[error]     at com.contract.testing.OepStepDefinitions$$anonfun$1.apply(OepStepDefinitions.scala:150)
[error]     at cucumber.api.scala.ScalaDsl$StepBody$$anonfun$apply$1.applyOrElse(ScalaDsl.scala:61)
[error]     at cucumber.api.scala.ScalaDsl$StepBody$$anonfun$apply$1.applyOrElse(ScalaDsl.scala:61)
[error]     at scala.runtime.AbstractPartialFunction.apply(AbstractPartialFunction.scala:36)
[error]     at cucumber.runtime.scala.ScalaStepDefinition.execute(ScalaStepDefinition.scala:71)
[error]     at cucumber.runtime.StepDefinitionMatch.runStep(StepDefinitionMatch.java:37)
[error]     at cucumber.runtime.Runtime.runStep(Runtime.java:298)
[error]     at cucumber.runtime.model.StepContainer.runStep(StepContainer.java:44)
[error]     at cucumber.runtime.model.StepContainer.runSteps(StepContainer.java:39)
[error]     at cucumber.runtime.model.CucumberScenario.run(CucumberScenario.java:48)
[error]     at cucumber.runtime.junit.ExecutionUnitRunner.run(ExecutionUnitRunner.java:91)
[error]     at cucumber.runtime.junit.FeatureRunner.runChild(FeatureRunner.java:63)
[error]     at cucumber.runtime.junit.FeatureRunner.runChild(FeatureRunner.java:18)
[error]     ...

So based on the error message it looks like it's trying to find the exported excel file? This part completely throws me off. Any help would be greatly appreciated. I've added below how I'm calling the method, perhaps I'm doing something silly. Also I'm up for using a different way to extract my zip file if you can recommend one.

val tempDirectoryDir = "src/test/resources/oepTemp/"
ZipArchive.unZip(tempDirectoryDir + "Sub Region Input - Output.zip", tempDirectoryDir)


Well since were are using some utilities from java, here is a version basen on this, translated to scala, maybe this should be more functional, but it is useful

package zip

import java.io.{ IOException, FileOutputStream, FileInputStream, File }
import java.util.zip.{ ZipEntry, ZipInputStream }

 * Created by anquegi on 04/06/15.
object Unzip extends App {

  val INPUT_ZIP_FILE: String = "src/main/resources/my-zip.zip";
  val OUTPUT_FOLDER: String = "src/main/resources/my-zip";

  def unZipIt(zipFile: String, outputFolder: String): Unit = {

    val buffer = new Array[Byte](1024)

    try {

      //output directory
      val folder = new File(OUTPUT_FOLDER);
      if (!folder.exists()) {

      //zip file content
      val zis: ZipInputStream = new ZipInputStream(new FileInputStream(zipFile));
      //get the zipped file list entry
      var ze: ZipEntry = zis.getNextEntry();

      while (ze != null) {

        val fileName = ze.getName();
        val newFile = new File(outputFolder + File.separator + fileName);

        System.out.println("file unzip : " + newFile.getAbsoluteFile());

        //create folders
        new File(newFile.getParent()).mkdirs();

        val fos = new FileOutputStream(newFile);

        var len: Int = zis.read(buffer);

        while (len > 0) {

          fos.write(buffer, 0, len)
          len = zis.read(buffer)

        ze = zis.getNextEntry()


    } catch {
      case e: IOException => println("exception caught: " + e.getMessage)





Scala - Option Type Var Manipulation

I am working on an online exercise practicing Options and threads, both of which I have very little experience. The online exercise comes with a test suite, so right now I am trying to get my Option test cases to pass before I move on to the thread test cases....

Excel - select a cell based on adjacent cell value

I have the following excel spreadsheet and I am trying to work out how I can write a formula in order to provide the values in column D. In each row, there is a test date, I am trying to calculate the day difference from each test date to the...

Future yielding with flatMap

Given Futures fa, fb, fc, I can use f: Function1[(A,B,C), Future[D]], to return a Future[D] either by: (for { a <- fa b <- fb c <- fc } yield (a,b,c)).flatMap(f) which has the unenviable property of declaring the variables a,b,c twice. or a.zip(b).zip(c).flatMap{ case (a, (b, c)) => f(a,...

Converting ADODB Loop into DAO

Hi I've been developing a vba project with a lot of help from examples here. I'm trying to access a MS Access database from Excel VBA and import large data sets (500-100+ rows) per request. Currently, the following loop works using ADODB however, the Range("").Copyfromrecordset line is taking very long...

Scala unapplySeq extractor syntax

I (inadvertently) came across a bit of pattern matching syntax I did not expect to compile and now cannot figure out. It appears related to unapplySeq. Note the case x List(_,_) part in this simple example: val xs = List(1, 2, 3) //> xs : List[Int] = List(1, 2, 3)...

Collapse similar case statements in Scala

Is there an elegant way to do something like the following example using just one case statement? foobar match { case Node(Leaf(key, value), parent, qux) => { // Do something with parent & qux } case Node(parent, Leaf(key, value), qux) => { // Do something with parent & qux (code...

Spray microservice assembly deduplicate

I'm using this template to develop a microservice: http://www.typesafe.com/activator/template/activator-service-container-tutorial My sbt file is like this: import sbt._ import Keys._ name := "activator-service-container-tutorial" version := "1.0.1" scalaVersion := "2.11.6" crossScalaVersions := Seq("2.10.5", "2.11.6") resolvers += "Scalaz Bintray Repo" at "https://dl.bintray.com/scalaz/releases" libraryDependencies ++= { val containerVersion = "1.0.1" val configVersion = "1.2.1"...

Scala first program issue

I have just started to learn Scala after some experience with functional programming in other languages. def freq(c:Char, y:String, list:List[(Char,Int)]): List[(Char,Int)] = list match{ case _ => freq(c, y.filter(_ == c), list :: List((count(c,y),c))) case nil => list } In the above code I am getting an error when trying...

adding variables into another variable vba

Dim x As Long Dim y As Long Dim CDTotal As Double Dim CSTotal As Double Dim ETotal As Double Dim FTotal As Double Dim HTotal As Double Dim ITotal As Double Dim ITTotal As Double Dim MTotal As Double Dim TTotal As Double Dim UTotal As Double Dim TotalValue...

EXCEL VBA: How to manupulate next cell's (same row) value if cell.value=“WORD” in a range

I want to change the next cell in same row in a if cell.value="word" in a range. I have defined the range, using 'for' loop. In my code, if cell.value="FOUND THE CELL" then cell.value+1="changed the next right side cell" cell.value+2="changed the second right side cell" end if I know this...

Using VLOOKUP formula or other function to compare two columns

I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

Play Framework Form Error Handling

This is my view file containing the form that has to filled in by the user: @helper.form(call) { @helper.input(resumeForm("surname"), '_label -> "Surname") { (id, name, value, args) => <input name="@name" type="text" value="@value" placeholder="Enter your surname"> } } This is my custom field constructor: @(elements: helper.FieldElements) @if(!elements.args.isDefinedAt('showLabel) || elements.args('showLabel) == true)...

How do I do to count rows in a sheets with filters? With a suppress lines

I have a sheet with lots of columns, but when I filter and use count = Application.WorksheetFunction.CountA(Range("A:A")) It returns all the rows non Empty. Not only the rows I filtered....

How to insert excel formula to cell in Report Builder 3.0

There is RDL report template for SQL Server Reporting Services. I need to set value for cell in table in the report template which must be calculated from other values in the report. When the report is exported to Excel file I need to see the Excel formula in that...

12 Characters Including leading and following zeros

I am finding this difficult to explain, but ultimately I am wanting a cells value to be 12 characters long including +/- a decimal point and following zeroes. Examples are 1200 would become +1200.000000 -20 would become -20.00000000 99999999 would become +99999999.00 I have tried FIXED, LENGTH, and formatting rules...

NoClassDefFoundError: UnsupportedFileFormatException while using apache poi to write to an excel file

I am trying to write to an excel(.xlsx) file using Apache poi, I included the apache poi dependencies in my pom.xml file. But I am getting the following exception in execution. Exception in thread "main" java.lang.NoClassDefFoundError: org/apache/poi/UnsupportedFileFormatException at java.lang.ClassLoader.defineClass1(Native Method) at java.lang.ClassLoader.defineClass(ClassLoader.java:800) at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142) at java.net.URLClassLoader.defineClass(URLClassLoader.java:449) at...

Which is faster in Excel, an if formula giving 1 or 0 instead of true/false or --?

I've got a large spreadsheet that I'm trying to optimise as it has over 12,000 lines of data, with in excess of 28 columns. It currently takes a significant amount of time to execute and I'm therefore starting to pare it down. As part of this I've started looking at...

Comparing cell contents against string in Excel

Following is my table file:*.css file:*.csS file:*.PDF file:*.PDF file:*.ppt file:*.xls file:*.xls file:*.doc file:*.doc file:*.CFM file:*.dot file:*.cfc file:*.CFM file:*.CFC file:*.cfc file:*.DOC I need a formula to populate the H column with True or False if it finds column G in column F (exact case). I used following but nothing seems to...

Using a stored integer as a cell reference

Dim x As Integer Dim y As Integer For y = 3 To 3 For x = 600 To 1 Step -1 If Cells(x, y).Value = "CD COUNT" Then Cells(x, y).EntireRow.Select Selection.EntireRow.Hidden = True End if If Cells(x, y).Value = "CD Sector Average" Then Cells(x, y).EntireRow.Select Selection.Insert Shift:=xlDown Cells(x +...

Excel VBA Loop Delete row does not start with something

I have some data at work looks like this: 00 some data here... 00 some data here... 00 some data here... 00 some data here... Other data I want to remove Other data I want to remove Other data I want to remove Other data I want to remove 00I...

Scodec: Coproducts could not find implicit value for parameter auto: scodec.codecs.CoproductBuilderAuto

I am trying to define an Scodec coproduct codec for communicating with an EELink GPS. Here is the code: import scodec.Codec import scodec.bits.ByteVector import scodec.codecs._ trait Message object Message { implicit val discriminated: Discriminated[ Message, Int ] = Discriminated(uint8) val codec: Codec[ Message ] = Codec.coproduct[ Message ].discriminatedByIndex(uint8) } case...

How to use the Akka ask pattern without blocking

Hi I have a actor which is responsible for fetching data from a database, turning it into a list and sending it back to the sender. I am using the ask pattern to receive response from my actor, because I don't want to use await.result because this approach will block...

ZipList with Scalaz

Suppose I have a list of numbers and list of functions to apply to numbers: val xs: List[Int] = List(1, 2, 3) val fs: List[Int => Int] = List(f1, f2, f3) Now I would like to use an Applicative to apply f1 to 1, f2 to 2, etc. val ys:...

Copying sheet to last row of a sheet from another workbook

I'm stuck in this block of code that copies sheet("Newly Distributed") to the last row of sheet("Source") from another workbook. The error is runtime error 9. What's wrong with my code? Any response would be appreciated. Private Sub copylog3() Dim lRow As Long Dim NextRow As Long, a As Long...

Retrieving TriangleCount

I'm trying to retrieve the amount of triangles from a graph using graphX. As I'm new to both Scala and graphX, I'm currently quite stuck. I'm creating a graph from an edgefile: 1 2 1 3 2 3 This should be 1 triangle. Next I'm using the build in function...

VBA “Compile Error: Statement invalid outside Type Block”

I am running a VBA Macro in Excel 2010 with tons of calculations, so data types are very important, to keep macro execution time as low as possible. My optimization idea is to let the user pick what data type all numbers will be declared as (while pointing out the...

Scala running issue on eclipse

I configured everthing within eclipse for scala. I create a snippet to show you the issue, i can't see in run options run as scala application, i also tried to find my main class under build configuration option but i can't find it. How i can solve it?...

excel search engine using vba and filters?

I am using the following vba code to filter my rows in excel based on the value in my cell C5 Sub DateFilter() 'hide dialogs Application.ScreenUpdating = False 'filter for records that have June 11, 2012 in column 3 ActiveSheet.Range("C10:AS30").AutoFilter Field:=1, Criteria1:="*" & ActiveSheet.Range("C5").Value & "*" Application.ScreenUpdating = True End...

How to instantiate lexical.Scanner in a JavaTokenParsers class?

I am writing a parser which inherits from JavaTokenParsers in that I have a function as follow: import scala.util.parsing.combinator.lexical._ import scala.util.parsing._ import scala.util.parsing.combinator.RegexParsers; import scala.util.parsing.combinator.syntactical.StdTokenParsers import scala.util.parsing.combinator.token.StdTokens import scala.util.parsing.combinator.lexical.StdLexical import scala.util.parsing.combinator.lexical.Scanners import scala.util.parsing.combinator.lexical.Lexical import...

Identifying cell in Openpyxl

I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "x". I've managed to get so far, but I can't extract the location of said cell. This is the code I have come up with: from openpyxl import load_workbook wb...

Excel VBA User-Defined Function: Get Cell in Sheet Function was Called In

I have a user-defined function in Excel that I run in multiple sheets. I am trying to use Cells(i, j) to pull the value of cells by their row and column in the sheet in which my function is called. Instead, Cells(i, j) pulls the value of cell [i,j] in...

SCALA: change the separator in Array

I have an Array like this. scala> var x=Array("a","x,y","b") x: Array[String] = Array(a, x,y, b) How do I change the separator comma in array to a :. And finally convert it to string like this. String = "a:x,y:b" My aim is to change the comma(separators only) to other separator(say,:), so...

PlayFramework: value as is not a member of Array[Byte]

I want to make file download from a database using Play framework. But when I use this code I get this message: value as is not a member of Array[Byte] And if I change Ok(bytOfImage.as("image/jpg")) to Ok(bytOfImage) it works good but I get a file with a name: secondindex without...

Scala (Slick) HList splitting to case classes

currently I have a HList with more than 22 fields and now I want to split it to 2-3 case classes, is there an easy functional way to do it? Currently I use the following syntax: CaseClass1(c.head, c.tail.head, c.tail.tail.head, etc...) However that doesn't seem to be right since I have...

Scala slf4j dynamic file name

I just successfully added Grizzled-SLF4J logger to my project using this link http://alvinalexander.com/scala/how-to-log-output-file-grizzled-slf4j-scala-simplelogger.properties But using this properties, there is no option to create dynamic file name: org.slf4j.simpleLogger.logFile = /tmp/myapp.log org.slf4j.simpleLogger.defaultLogLevel = info org.slf4j.simpleLogger.showDateTime = true org.slf4j.simpleLogger.dateTimeFormat = yyyy'/'MM'/'dd' 'HH':'mm':'ss'-'S org.slf4j.simpleLogger.showThreadName = true...

If cell value starts with a specific set of numbers, replace data

My cell values are strings of numbers (always greater than 5 numbers in a cell, ie 67391853214, etc.) If a cell starts with three specific numbers (ie 673 in a cell value 67391853214) I want the data in the cell to be replaced with a different value (if 673 are...

How to effectively get indices of 1s for given binary string using Scala?

Suppose we have a binary string such as 10010010. All I want is a function returning indices of 1s for that string: indicesOfOnes("10010010") -> List(0, 3, 6) indicesOfOnes("0") -> List() And what I implemented is: def indicesOfOnes(bs: String): List[Int] = { val lb = ListBuffer[Int]() bs.zipWithIndex.foreach { case (v, i)...

Interface Controls for DoEvent in Excel

I have a macro to loop through a range and return emails to .Display based on the DoEvents element within my module. I iterate that: row_number = 1 'And Do DoEvents row_number = row_number +1 'Then a bunch of formatting requirements Loop Until row_number = 'some value I am wondering...

Operand order in Scala List.prepend (::)

Odersky has brilliantly optimized Java syntax, enabling object calls without dots and parenthesis. I.e. instead of list.prepend(item), you now simply write list :: item, which also turns language operators into simple object methods. Here, List defines :: (prepend) operator. However, you normally write it vice-verse in Scala, using item ::...

Implicit Generic.Aux missing on conversion from Shapeless HList to case class

I just recently started learning scala and today I decided I wanted to write a CSV parser that would load nicely into case classes but store the data in rows (lists) of Shapeless's HList object so that I could get some exposure to type-level programming. Here's what I have so...

Converting column from military time to standard time

I'm trying to convert a column showing the time of road traffic accidents from military time to standard time. The data looks like this: Col1 Time..24hr. 1 1404 2 322 3 1945 4 1005 5 945 I'd then like to convert to 12hr so for '322' I'd like to make...

Using a cell's number to insert that many rows (with that row's data)

I have data in excel that looks like this {name} {price} {quantity} joe // 4.99 // 1 lisa // 2.99 // 3 jose // 6.99 // 1 Would it be hard to make a macro that will take the quantity value ("lisa // 3.99 // 3") and add that many...

Is this definition of a tail recursive fibonacci function tail-recursive?

I've seen around the following F# definition of a continuation-passing-style fibonacci function, that I always assumed to be tail recursive: let fib k = let rec fib' k cont = match k with | 0 | 1 -> cont 1 | k -> fib' (k-1) (fun a -> fib' (k-2)...

Excel - Pulling data from one cell within a list

I use PowerPoint as a graphics template to type up football player names and there squad numbers. It can be a long procedure and so far following YouTube tutorials i have managed to create a form in Excel which can update the text boxes in PowerPoint at the click of...

Implementing map on a tree using fold

I am trying to implement a map using fold. I could do so in Haskell data Tree a = EmptyTree | Node a (Tree a) (Tree a) deriving (Show) foldTree :: Tree a -> b -> (b -> a -> b -> b) -> b foldTree EmptyTree d _ =...

timestamp SQL to Excel

If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

Zipping two arrays together with index in Scala?

I have two arrays populated with integers. They are the same size (val array1 and val array2). I want to fuse them together into tuples with their index as the third element. For example if we have val array1 = Array(5,2,6,2) and val array2 = Array(9,8,3,4) then I want to...

VBA - Unable to pass value from Private to Public Sub

I have a tool which I am designing to present a number of questions to a user in a set of userforms. The form will generate a score via passing an integer result from the userform to a main sub, which passes the code to a worksheet. My problem is...

How to generalize the round methods

I have the following four methods, using BigDecimal to round a number: private def round(input: Byte, scale: Int): Byte = { BigDecimal(input).setScale(scale, RoundingMode.HALF_UP).byteValue() } private def round(input: Short, scale: Int): Short = { BigDecimal(input).setScale(scale, RoundingMode.HALF_UP).shortValue() } private def round(input: Int, scale: Int): Int = { BigDecimal(input).setScale(scale, RoundingMode.HALF_UP).intValue() } private def...

Convert RDD[Map[String,Double]] to RDD[(String,Double)]

I did some calculation and returned my values in a RDD containing scala map and now I want to remove this map and want to collect all keys values in a RDD. Any help will be appreciated....