Go to Qiita Advent Calendar 2024 Top
LoginSignup
7
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@yaz(Yas)

xlsファイルをcsvにしたいだけなのに

Last updated at Posted at 2015-01-12

###はじめに###
大量のxlsファイルを処理する必要が生じました。
Common Lispで処理したいので，適当にxlsファイルを処理してやって読ませる必要があります。

xlsファイル自体は，テンプレのxlsに不特定多数のヒトが何某か記載したものです。
xlsxで提出されているものも散見されて嫌な感じです。

  1. Common Lisp
    （cl-olefsとか試したものの，ほぼ全てのファイルが読めず）
  2. Perl
    （８割のファイルが読めず，読めたファイルと読めないファイルの違いもわからず）
  3. R（xlsxライブラリで読めたものの，rJava経由でApache POIを使ってるので遅すぎる）
  4. Java (それなら直にJavaでApache POI使えば良い？）

ということでJavaでcsvにする事にしました。

ちなみにJava触ったのは大学の講義以来です。何やったかも覚えてません。

##準備##
###Java###
Mac OSX側は8でした。

$ screenfetch

                 -/+:.          
                :++++.          OS: 64bit Mac OS X 10.10.1 14B25
               /+++/.           Kernel: x86_64 Darwin 14.0.0
       .:-::- .+/:-``.::-       Uptime: 1d 18h 27m
    .:/++++++/::::/++++++/:`    Packages: 84
  .:///////////////////////:`   Shell: bash 3.2.53
  ////////////////////////`     Resolution: 1440x900
 -+++++++++++++++++++++++`      DE: Aqua
 /++++++++++++++++++++++/       WM: Quartz Compositor
 /sssssssssssssssssssssss.      WM Theme: Blue
 :ssssssssssssssssssssssss-     Font: Not Found
  osssssssssssssssssssssssso/`  CPU: Intel Core i5-4250U CPU @ 1.30GHz
  `syyyyyyyyyyyyyyyyyyyyyyyy+`  GPU: Intel HD Graphics 5000 
   `ossssssssssssssssssssss/    RAM: 2721MB / 4096MB
     :ooooooooooooooooooo+.    
      `:+oo+/:-..-:/+o+/-      

$ java -version
java version "1.8.0_25"
Java(TM) SE Runtime Environment (build 1.8.0_25-b17)
Java HotSpot(TM) 64-Bit Server VM (build 25.25-b02, mixed mode)

Macで作ったrunnable jarがUbuntuで動かなかったりしたので
Ubuntu側も8に合わせました。

$ bash screenfetch-dev 
                          ./+o+-       
                  yyyyy- -yyyyyy+      OS: Ubuntu 14.04 trusty
               ://+//////-yyyyyyo      Kernel: x86_64 Linux 3.13.0-43-generic
           .++ .:/++++++/-.+sss/`      Uptime: 20h 20m
         .:++o:  /++++++++/:--:/-      Packages: 2821
        o:+o+:++.`..```.-/oo+++++/     Shell: bash 4.3.11
       .:+o:+o/.          `+sssoo+/    Resolution: 1366x768
  .++/+:+oo+o:`             /sssooo.   WM: Compiz
 /+++//+:`oo+o               /::--:.   WM Theme: Ambiance
 \+/+o+++`o++o               ++////.   GTK Theme: Not Found [GTK2/3]
  .++.o+++oo+:`             /dddhhh.   Icon Theme: Not Found
       .+.o+oo:.          `oddhhhh+    Font: Not Found
        \+.++o+o``-````.:ohdhhhhh+     CPU: Intel Core i7-3820 CPU @ 3.601GHz
         `:o+++ `ohhhhhhhhyo++os:      GPU: GeForce GTX 650
           .o:`.syhhhhhhh/.oo++o`      RAM: 3532MB / 64386MB
               /osyyyyyyo++ooo+++/    
                   ````` +oo+++o\:    
                          `oo++.      

$ sudo apt-get purge openjdk*
$ sudo add-apt-repository ppa:webupd8team/java
$ sudo apt-get update
$ sudo apt-get install oracle-java8-installer
$ sudo apt-get install oracle-java8-set-default

$ cd /usr/lib/jvm/
$ sudo ln -s java-8-oracle/ default-java

$ java -version
java version "1.8.0_25"
Java(TM) SE Runtime Environment (build 1.8.0_25-b17)
Java HotSpot(TM) 64-Bit Server VM (build 25.25-b02, mixed mode)

$ javac -version
javac 1.8.0_25

###Eclipse###
Eclipse Luna (4.4.1) のEclipse IDE for Java Developersをダウンロードして展開したものを使いました。

###Apache POI###
JavaでMicrosof Office系のファイルを扱う時はApache POIを使うのが一般的らしいのでPOIを使います。
poi-bin-3.11をダウンロードしてEclipseに中のjarファイルを適当に放り込みました。

##xls to csv##
試行錯誤（途中の記憶が曖昧…）の末にこんな感じになりました。

xls2csv
package xlsNxlsx2csv;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory; 
import org.apache.poi.ss.usermodel.Cell;

public class xls2csv {
public static void main(final String[] args) {
	InputStream inp = null;
	String regex1 = "\"";
	Pattern p1 = Pattern.compile(regex1);
	String regex2 = ",";
	Pattern p2 = Pattern.compile(regex2);

try {inp = new FileInputStream(args[0]);
	Workbook wb = WorkbookFactory.create(inp);
	Sheet sheet = wb.getSheetAt(0);
		for(Row row : sheet) {
			for(Cell cell : row) {
				Matcher m1 = p1.matcher(cell.toString());
				String result1 = m1.replaceAll("”"); // " -> ”
				Matcher m2 = p2.matcher(result1);
				String result2 = m2.replaceAll("，"); // , -> ，
				System.out.print("\"" + result2 + "\",");} // cell -> result2
		System.out.println();}
} catch (InvalidFormatException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} catch (FileNotFoundException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} catch (IOException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} finally {try {inp.close();} catch (IOException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);}}}}

これで処理すると，
行の先頭に2つ空のセルが続いた時に，1つの空のセルとして扱われてしまい，そこから先のセルが全て左に１つシフトした扱いになってしまいます。
(例：E4セルの値がD4セルとして扱われる）

これで列の値を使って計算していたものが殆ど使えなくなってしまいました。

xlsファイル提出者が打ち込んだ数値や文字列は取得出来ているので，これを元にExcelで計算していたものを再計算することは出来ます。
このままでも使えなくはないのですが，今後もjavaでcsvに変換することがありそうなので原因と対処法を探ってます。

何かわかったら追記します。

##追記2015-01-13：空白セルの問題が解決しました##
コメントでyazawa_logさんにご指摘いただき，Iterate over cells, with control of missing / blank cellsを参考にして書き換えました。

ついでにセルの数式も計算するようにしました。

xls2csv
package xlsNxlsx2csv;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory; 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;

public class xls2csv {
public static void main(final String[] args) {
	InputStream inp = null;
	String regex1 = "\"";
	Pattern p1 = Pattern.compile(regex1);
	String regex2 = ",";
	Pattern p2 = Pattern.compile(regex2);

try {
    inp = new FileInputStream(args[0]);
	Workbook wb = WorkbookFactory.create(inp);		
	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
	Sheet sheet = wb.getSheetAt(0);
    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());

    for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
       Row row = sheet.getRow(rowNum);
       int lastColumn = Math.max(row.getLastCellNum(), 0);
       for (int columnnumber = 0; columnnumber < lastColumn; columnnumber++) {
          Cell cell = row.getCell(columnnumber, Row.RETURN_BLANK_AS_NULL);
          if (cell == null) {
        	  System.out.print("\"\",");
          } else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
        	  System.out.print("\"" + evaluator.evaluateInCell(cell) + "\",");
          } else {
				Matcher m1 = p1.matcher(cell.toString());
				String result1 = m1.replaceAll("”"); // " -> ”
				Matcher m2 = p2.matcher(result1);
				String result2 = m2.replaceAll("，"); // , -> ，
				System.out.print("\"" + result2 + "\",");}} // cell -> result2
          System.out.println();}
} catch (InvalidFormatException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} catch (FileNotFoundException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} catch (IOException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} finally {try {inp.close();} catch (IOException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);}}}}

これで当初目標としていた挙動になりました。
以下のエラーがファイルの末尾に吐かれていたので再調整中。

Exception in thread "main" java.lang.NullPointerException
	at xlsNxlsx2csv.xls2csv.main(xls2csv.java:51)

これ消せるだけでも良いのだけど…

##追記：2015-01-14 NullPointerException対処##

xls2csv
package xlsNxlsx2csv;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory; 
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;

public class xls2csv {
public static void main(final String[] args) {
	InputStream inp = null;
	String regex1 = "\"";
	Pattern p1 = Pattern.compile(regex1);
	String regex2 = ",";
	Pattern p2 = Pattern.compile(regex2);
	String regex3 = "#VALUE!";
	Pattern p3 = Pattern.compile(regex3);

try {
    inp = new FileInputStream(args[0]);
	Workbook wb = WorkbookFactory.create(inp);		
	FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
	Sheet sheet = wb.getSheetAt(0);
    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());

    for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
       Row row = sheet.getRow(rowNum);
       if (row != null){
    	   int lastColumn = Math.max(row.getLastCellNum(), 0); 
    	   for (int columnnumber = 0; columnnumber < lastColumn; columnnumber++) {
    		   Cell cell = row.getCell(columnnumber, Row.RETURN_BLANK_AS_NULL);
    		   if (cell == null) {
    			   System.out.print("\"\",");
    			   } else if(cell.getCellType() == Cell.CELL_TYPE_FORMULA) {
    				   cell = evaluator.evaluateInCell(cell);
    				   Matcher m3 = p3.matcher(cell.toString());
    				   String result3 = m3.replaceAll("");
    				   System.out.print("\"" + result3 + "\","); 
    				   } else if(cell.getCellType() == Cell.CELL_TYPE_STRING) {
    					   Matcher m1 = p1.matcher(cell.toString());
    					   String result1 = m1.replaceAll("”"); // " -> ”
    					   Matcher m2 = p2.matcher(result1);
    					   String result2 = m2.replaceAll("，"); // , -> ，
    					   System.out.print("\"" + result2 + "\","); // cell -> result2
    					   }}
    	   System.out.println();}}
} catch (InvalidFormatException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} catch (FileNotFoundException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} catch (IOException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} finally {try {inp.close();} catch (IOException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);}}}}

問題点：他のファイルへの自動リンクが設定されているファイルが扱えない。
数式の評価を無効にする以外の対処法が思いつかない。

##追記:2014-02-01##
1月末に2014年分のデータが届きました。
大量の空白が入ったデータ（スペースキー押しっぱ？）があったので正規表現で不要な空白を取り除く事にしました。

xls2csv
package xlsNxlsx2csv;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Pattern;
import java.util.regex.Matcher;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory; 
import org.apache.poi.ss.usermodel.Cell;

public class xls2csv {
public static void main(final String[] args) {
	InputStream inp = null;
	String regex1 = "\"";
	Pattern p1 = Pattern.compile(regex1);
	String regex2 = ",";
	Pattern p2 = Pattern.compile(regex2);
	
	String regex4 = "[ |　]+";  // 全角半角の空白は１つの半角空白へ置換
	Pattern p4 = Pattern.compile(regex4);
	String regex5 = "( |　)+$"; // 末尾の空白は取り除く
	Pattern p5 = Pattern.compile(regex5);

try {
    inp = new FileInputStream(args[0]);
	Workbook wb = WorkbookFactory.create(inp);		
	Sheet sheet = wb.getSheetAt(0);
    int rowStart = Math.min(15, sheet.getFirstRowNum());
    int rowEnd = Math.max(1400, sheet.getLastRowNum());

    for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
       Row row = sheet.getRow(rowNum);
       if (row != null){
    	   int lastColumn = Math.max(row.getLastCellNum(), 0); 
    	   for (int columnnumber = 0; columnnumber < lastColumn; columnnumber++) {
    		   Cell cell = row.getCell(columnnumber, Row.RETURN_BLANK_AS_NULL);
    		   if (cell == null) {
    			   System.out.print("\"\",");
    			   } 
    		   else {
    					   Matcher m1 = p1.matcher(cell.toString());
    					   String result1 = m1.replaceAll("”"); // " -> ”
    					   Matcher m2 = p2.matcher(result1);
    					   String result2 = m2.replaceAll("，"); // , -> ，
    					   
    					   Matcher m4 = p4.matcher(result2);
    					   String result4 = m4.replaceAll(" ");
    					   Matcher m5 = p5.matcher(result4);
    					   String result5 = m5.replaceAll("");
    					   
    					   System.out.print("\"" + result5 + "\","); // cell -> result2
    					   }}
    	   System.out.println();}}
} catch (InvalidFormatException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} catch (FileNotFoundException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} catch (IOException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);
} finally {try {inp.close();} catch (IOException ex) {Logger.getLogger(xls2csv.class.getName()).log(Level.SEVERE, "\"\"", ex);}}}}
7
5
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
7
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?