java读写excel

之前写过spark读取excel的博客，必须要转csv,但是用java直接处理csv(涉及按“，”分割字符串，字符串中也有“，”),这种情况就很麻烦了，还不具有通用性，故重新采用jxl来处理excel.

<dependencies>
        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.17</version>
        </dependency>
    </dependencies>

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class Direct {
    public static List<List<String>> getCopy(File file) throws IOException, BiffException {
        List<List<String>> copy = new ArrayList<List<String>>();
        Workbook readwb = null;
        try {
            InputStream input = new FileInputStream(file);
            readwb = Workbook.getWorkbook(input);
            Sheet sheet = readwb.getSheet(0);
            int columes = sheet.getColumns();
            int line = sheet.getRows();

            for(int i = 0;i < line; i++){
                Cell[] cells = sheet.getRow(i);
                int n = cells.length;
                List<String> tmp = new ArrayList<String>();
                if(n == 19){
                    for(int j=0;j<n;j++){
                        Cell cell = sheet.getCell(j,i);
                        tmp.add(cell.getContents());
                    }
                    copy.add(tmp);
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return copy;
    }

    public static List<List<String>> getLess(File file) throws IOException, BiffException {
        List<List<String>> less = new ArrayList<List<String>>();
        Workbook readwb = null;
        try {
            InputStream input = new FileInputStream(file);
            readwb = Workbook.getWorkbook(input);
            Sheet sheet = readwb.getSheet(0);
            int columes = sheet.getColumns();
            int line = sheet.getRows();

            for(int i = 1;i < line; i++){
                Cell[] cells = sheet.getRow(i);
                int n = cells.length;
                List<String> tmp = new ArrayList<String>();
                if(n < 19){
                    for(int j=0;j<n;j++){
                        Cell cell = sheet.getCell(j,i);
                        tmp.add(cell.getContents());
                    }
                    for(int m = n; m<19;m++){
                        tmp.add("");
                    }
                    less.add(tmp);
                }
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return less;
    }
    public static void main(String[] args) throws IOException {
        String path = "/data/20180109.xls";
        File file = new File(path);
        List<List<String>> result = new ArrayList<List<String>>();
        List<List<String>> copy = null;
        List<List<String>> less = null;
        List<List<String>> head = null;
        try {
            copy = getCopy(file);
            less = getLess(file);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (BiffException e) {
            e.printStackTrace();
        }

        List<List<String>> mid = new ArrayList<List<String>>();
        Map<String, String> map = new HashMap<String, String>();

        for(List<String> list:copy){
            String lables = list.get(1)+" "+list.get(2)+" "+list.get(15)+" "+list.get(17);
            if(!map.containsKey(lables)){
                map.put(lables,"");
                mid.add(list);
            }

        }

        for(List<String> lists:copy){
            result.add(lists);
        }
        //从mid往result输出
        for(List<String> strs:less){
            String labels = strs.get(1);
            for(List<String> match:mid){
                List<String> tmps = new ArrayList<String>();
                tmps.addAll(strs);
                if(labels.equals(match.get(1))){
                    for (int i = 11; i < 19; i++) {
                        tmps.remove(i);
                        tmps.add(i,match.get(i));
                    }
                    result.add(tmps);
                }
            }
        }
        String output = "/data/test.xls";
        OutputStream os = new FileOutputStream(output);
        WritableWorkbook wb = Workbook.createWorkbook(os);
        WritableSheet sheet = wb.createSheet("sheet1", 0);
        Label label = null;
        for(int i = 0;i < result.size();i++){
            for(int j = 0;j<result.get(i).size();j++){
                label = new Label(j,i,result.get(i).get(j));
                try {
                    sheet.addCell(label);
                } catch (WriteException e) {
                    e.printStackTrace();
                }
            }
        }
        wb.write();
        try {
            wb.close();
        } catch (WriteException e) {
            e.printStackTrace();
        }
    }
}