java读写excel

- 日理万妓 2022-06-02 11:26 197阅读 0赞

之前写过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();
            }
        }
    }