java 读写 excle 完整版

pom.xml

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>3.16</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>3.14</version>
</dependency>
<!-- 处理excel和上面功能是一样的-->
<dependency>
<groupId>net.sourceforge.jexcelapi</groupId>
<artifactId>jxl</artifactId>
<version>2.6.10</version>
</dependency>

<build>
<plugins>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-shade-plugin</artifactId>
<version>2.4.1</version>
<executions>
<execution>
<phase>package</phase>
<goals>
<goal>shade</goal>
</goals>
<configuration>
<transformers>
<transformer implementation="org.apache.maven.plugins.shade.resource.ManifestResourceTransformer">
<mainClass>com.baozun.util.DecryptAndEncrypt</mainClass>
</transformer>
</transformers>
</configuration>
</execution>
</executions>
</plugin>





<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
<plugin>
<artifactId>maven-assembly-plugin</artifactId>
<configuration>
<descriptorRefs>
<descriptorRef>jar-with-dependencies</descriptorRef>
</descriptorRefs>
<archive>
<manifest>
<mainClass></mainClass>
</manifest>
</archive>
</configuration>
<executions>
<execution>
<id>make-assembly</id>
<phase>package</phase>
<goals>
<goal>single</goal>
</goals>
</execution>
</executions>
</plugin>
<plugin>
<groupId>org.apache.maven.plugins</groupId>
<artifactId>maven-jar-plugin</artifactId>
<configuration>
<archive>
<addMavenDescriptor>false</addMavenDescriptor>
</archive>
</configuration>
</plugin>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<version>1.3.5.RELEASE</version>
<configuration>
<mainClass>com.bj.util.ExcelReaderWrite</mainClass>
</configuration>
<executions>
<execution>
<goals>
<goal>repackage</goal>
</goals>
</execution>
</executions>
</plugin>




</plugins>

</build>

部分参考：http://www.codepub.cn/2017/06/13/Maven-introduces-local-dependency-jar-to-executable-jar-packages/
　　　　　https://www.cnblogs.com/523823-wu/p/7635358.html

代码部分

package com.baozun.util;

import com.sun.scenario.effect.impl.sw.sse.SSEBlend_SRC_OUTPeer;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


public class ExcelReaderWrite {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";
    private  static String filePath=null;
    private  static String sheetName=null;
    private static Workbook workBook;
    private static Sheet sheet;
    private Object[][] results;
    private List<List<String>> listData;
    private  static FileInputStream in ;

     public ExcelReaderWrite(String filePath, String sheetName) {
        this.filePath = filePath;
        this.sheetName = sheetName;
        innit();
    }

    public void innit() {
        workBook = getWorkbok(filePath);
        sheet = workBook.getSheet(sheetName);
    }

    public Workbook getWorkbok(String filePath) {
        try {
            File file = new File(filePath);
            if (file.exists()) {
                in  = new FileInputStream(file);
                if (file.getName().endsWith(EXCEL_XLS)) {     //Excel&nbsp;2003

                    return new HSSFWorkbook(in);
                } else if (file.getName().endsWith(EXCEL_XLSX)) {    // Excel 2007/2010
                    return new XSSFWorkbook(in);
                }
            } else {
                System.out.println(filePath + "不存在 ！");
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public void setCellData(int rowNum, int colNum, String content) {
        rowNum -= 1;
        colNum -= 1;
        FileOutputStream out = null;
        if (null == sheet.getRow(rowNum)) {
            Row row = sheet.createRow(rowNum);
            if (null == row.getCell(colNum)) {
                row.createCell(colNum).setCellValue(content);
            } else {
                row.getCell(colNum).setCellValue(content);
            }
        } else {
            sheet.getRow(rowNum).createCell(colNum).setCellValue(content);
        }

        try {
            out = new FileOutputStream(filePath);
            workBook.write(out);
            out.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {

                out.flush();
                out.close();
                in.close();
              innit();

            } catch (Exception e) {
                e.printStackTrace();
            }
        }

    }
    private String getCellValue(Cell cell) {
        String cellValue = "";
        DataFormatter formatter = new DataFormatter();
        if (cell != null) {
            switch (cell.getCellType()) {
                case Cell.CELL_TYPE_NUMERIC:
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        cellValue = formatter.formatCellValue(cell);
                    } else {
                        double value = cell.getNumericCellValue();
                        int intValue = (int) value;
                        cellValue = value - intValue == 0 ? String.valueOf(intValue) : String.valueOf(value);
                    }
                    break;
                case Cell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case Cell.CELL_TYPE_BOOLEAN:
                    cellValue = String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    cellValue = String.valueOf(cell.getCellFormula());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    cellValue = "";
                    break;
                case Cell.CELL_TYPE_ERROR:
                    cellValue = "";
                    break;
                default:
                    cellValue = cell.toString().trim();
                    break;
            }
        }
        return cellValue.trim();
    }
    public String getCellData(String sheetName, int rowNum, int colNum) {
        if (rowNum <= 0 || colNum <= 0) {
            return null;
        } else if (workBook.getSheet(sheetName) == null || workBook.getSheet(sheetName).getRow(rowNum - 1) == null) {

            return filePath + " 中 " + sheetName + " 不存在，或者" + rowNum + " 行不存在 ！ ";
        } else {
            return getCellValue(workBook.getSheet(sheetName).getRow(rowNum - 1).getCell(colNum - 1));
        }

    }
    public String getCellData(int rowNum, int colNum) {
        if (rowNum <= 0 || colNum <= 0) {
            return null;
        } else if (workBook.getSheet(sheetName) == null || workBook.getSheet(sheetName).getRow(rowNum - 1) == null) {

            return filePath + " 中 " + sheetName + " 不存在，或者" + rowNum + " 行不存在 ！ ";
        } else {
            return getCellValue(workBook.getSheet(sheetName).getRow(rowNum - 1).getCell(colNum - 1));
        }

    }
    private List<List<String>> getSheetData() {
        listData = new ArrayList<List<String>>();
        int numOfRows = sheet.getLastRowNum()+1;
        System.out.println("sheet.getLastRowNum()="+sheet.getLastRowNum());
        for (int i = 0; i < numOfRows; i++) {
            Row row = sheet.getRow(i);
            Map<String, String> map = new HashMap<String, String>();
            List<String> list = new ArrayList<String>();
            if (row != null) {
                for (int j = 0; j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    list.add(this.getCellValue(cell));
                }
            }
            listData.add(list);
        }

        return listData ;
    }

    public void printSheetData() {
        // 测试数据excel数据用 ；

        List<List<String>> list   = getSheetData();
        System.out.println("总共有 "+list.size()+"   行！");
        for (int i = 0; i < list.size(); i++) {
            System.out.println("第 "+(i+1)+" 行有 "+list.get(i).size()+" 单元格有值 : "+list.get(i).toString());
        }
    }

    public static void main(String[] args) {
       // String filePath_1 = "D:/writeExcel.xlsx";
        String filePath_2 = "D:/writeExcel97.xls";
        String sheetName = "Sheet1";
     /*   int lastNum_1 = new ExcelReaderWrite(filePath_1, sheetName).getWorkbok().getSheet(sheetName).getLastrowNum();
        int lastNum_2 = new ExcelReaderWrite(filePath, sheetName).getWorkbok().getSheet(sheetName).getLastrowNum();
        System.out.println(lastNum_1);
        System.out.println(lastNum_2);
        for (int i = 0; i <10 ; i++) {
            new ExcelReaderWrite(filePath_1, sheetName).setCellData(i,3,filePath_1+"_"+String.valueOf(System.currentTimeMillis()));
            new ExcelReaderWrite(filePath_2, sheetName).setCellData(i,3,filePath_2+"_"+String.valueOf(System.currentTimeMillis()));
        }
        String dataValue=  new ExcelReaderWrite(filePath_1, sheetName).getCellData(sheetName, 1, 1);
        System.out.println(dataValue);
        new ExcelReaderWrite(filePath_1, sheetName).printSheetData();
       new ExcelReaderWrite(filePath_2, sheetName).printSheetData();
       */
        ExcelReaderWrite eh=  new ExcelReaderWrite(filePath_2, sheetName);
        eh.setCellData(1,1,"1");
        eh.setCellData(1,2,"1_2");
        eh.printSheetData();
        eh.setCellData(2,1,"22_1");
        eh.setCellData(2,2,"22_2");

        eh.printSheetData();
        System.out.println("2row2col="+eh.getCellData(2, 2));
//        eh.setCellData(1,3,String.valueOf(System.currentTimeMillis()));
//        eh.setCellData(1,4,String.valueOf(System.currentTimeMillis()));
//        eh.setCellData(1, 5, String.valueOf(System.currentTimeMillis()));
//        eh.setCellData(2,5,String.valueOf(System.currentTimeMillis()));
//        eh.setCellData(2,5,String.valueOf(System.currentTimeMillis()));
//        eh.setCellData(2,5,String.valueOf(System.currentTimeMillis()));
//        eh.setCellData(2,5,String.valueOf(System.currentTimeMillis()));




// eh.setCellData(3,2,String.valueOf(System.currentTimeMillis()));
//        eh.setCellData(3,3,String.valueOf(System.currentTimeMillis()));
//s        eh.setCellData(3,4,String.valueOf(System.currentTimeMillis()));


    }


}

 

解决了  一个对象重复写入时的错误 ；