java 读写 excle 完整版

2023-08-18 10:02:52

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>

<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()));

    }

}

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

码农公寓

相关文章