current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 2 down vote favorite

Excel renders scientific notation in the engineering fashion, e.g. “4.2E-5”. That’s OK, but when you are writing a scientific paper, it’s likely that you want it to look like this: “4.2×10⁻⁵”.

I have come up with a Scala script that does the job perfectly well:

import java.awt.Toolkit
import java.awt.datatransfer.{StringSelection, DataFlavor}

/**
 * Created by IDEA on 13/10/15.
 */
object Scientific {
  val superscriptMap = {
    var tmp = collection.mutable.Map(
      '0' -> '\u2070',
      '1' -> '\u00b9',
      '2' -> '\u00b2',
      '3' -> '\u00b3'
    )
    val x = ('4' to '9').zip('\u2074' to '\u2079').toList
    for ((k, v) <- x) {
      tmp.update(k, v)
    }
    tmp.toMap
  }
  type ExponentTuple = (Int, String)

  def dealWithSign(s: String): ExponentTuple = {
    if (s.head == '+') {
      (1, s.tail.dropWhile(_ == '0'))
    } else if (s.head == '-') {
      (-1, s.tail.dropWhile(_ == '0'))
    } else {
      (1, s)
    }
  }

  def snBeautify(s: String): String = {
    val s1 = s.split("[Ee]").take(2)
    require(s1.length == 2)
    val coefficient = s1(0)
    val (sign, exponent) = dealWithSign(s1(1))
    val part1 = coefficient + '\u00d7' + "10"
    val part2 = exponent.map(superscriptMap(_))
    // if the number is negative, provide a superscript minus
    if (sign < 0) part1 + '\u207b' + part2
    else part1 + part2
  }

  def snBeautify1(s: String): String = {
    s.split("\\s").map(snBeautify(_)).mkString("\n")
  }

  def snBeautify(printOut: Boolean = true): Unit = {
    val toolkit = Toolkit.getDefaultToolkit
    val clipboard = toolkit.getSystemClipboard
    val s1 = clipboard.getData(DataFlavor.stringFlavor).asInstanceOf[String]
    val s2 = snBeautify1(s1)
    val s2Select = new StringSelection(s2)
    if (printOut) println(s2)
    clipboard.setContents(s2Select, s2Select)
  }
}

object Test1 {
  //  val s = List("4.2E+5", "4.2e5", "4.2e-5", "4.2E-5", "-4.2e-5")
  //  s.map(snBeautify(_))
  //  snBeautify1(s.mkString("\n"))
  //  val s1 = "1.74E-11\n2.72E-12\n2.20E-11\n2.20E-11\n2.20E-11\n2.20E-11\n2.20E-11\n2.20E-11\n2.20E-11\n2.20E-11\n2.20E-11\n6.42E-12\n7.13E-11\n4.02E-11\n7.84E-12\n5.87E-12\n1.55E-11\n1.07E-10\n1.04E-10\n3.81E-11\n4.46E-11\n4.46E-11\n4.46E-11\n4.46E-11\n1.23E-10\n2.45E-11\n8.24E-12"
  //  snBeautify1(s1)
  // Copy the cells you want to reformat first
  Scientific.snBeautify()
}

Not a native Excel solution, but at least Scala is much nicer than VBA.

You can copy the cells you want to reformat and then run the snBeautify function, the reformatted numbers now sits in the clipboard ready for you to paste into Excel or Word.

One advantage of this solution is that it's equally applicable to LibreOffice or OpenOffice.

You can also easily modify this into a shell script and bind it to a certain keyboard shortcut.

Any suggestion for improvement of any kind is welcome.

share|improve this question
up vote 4 down vote accepted

The code is OK, it looks quite readable, but still some things may be cleaned a bit.

Encapsulation

Members are public by default, so some of them should be hidden, since they do not seem (and do not need) to be used from outside: private val superscriptMap, private def dealWithSign.

Don't Use Mutable Collections

Scala favors immutability and using things from scala.collection.mutable should be justified. There is a simple way here to bypass the mutability by concatenation of two maps:

private val superScriptImmutable = Map(
  '0' -> '\u2070',
  '1' -> '\u00b9',
  '2' -> '\u00b2',
  '3' -> '\u00b3'
) ++ ('4' to '9').zip('\u2074' to '\u2079').toMap

Use Matchers

if.. else if.. else are too much from imperative style; matchers should be used instead:

private def dealWithSign2(s: String): ExponentTuple = {
  def dropZeros(str : String) = str.dropWhile(_ == '0')
  s.head match {
    case '+' => (1, dropZeros(s.tail))
    case '-' => (-1, dropZeros(s.tail))
    case _ => (1, s)
  }
}

Misc

There should be a general check for input data that the args contain numbers. I've tried to pass "test" string to snBeautify and it returned java.util.NoSuchElementException: key not found: s, which is not explicit from the user's point of view.

ExponentTuple type does not seem to be used elsewhere except in the optional return type declaration in dealWithSign function. The type and the declaration can be removed without any harm.

In snBeautify() functions, s1 and s2 should be renamed according to their meaning, something like original and beautified. I'd also suggest to extract clipboard operations into dedicated functions, for example:

private def getFromClipboard = {
  val clipboard = Toolkit.getDefaultToolkit.getSystemClipboard
  clipboard.getData(DataFlavor.stringFlavor).asInstanceOf[String]
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.