JAVA读写xls格式的Excel表格实例

曾经终败给现在 2022-01-27 18:55 261阅读 0赞

# 工程目录 #

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]

# 文件 #

### pom.xml ###

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <parent>
            <artifactId>helloworld</artifactId>
            <groupId>com.ydfind</groupId>
            <version>1.0-SNAPSHOT</version>
        </parent>
        <modelVersion>4.0.0</modelVersion>
    
        <artifactId>excel-demo</artifactId>
    
        <build>
            <resources>
                <resource>
                    <directory>src/main/resources</directory>
                    <filtering>true</filtering>
                </resource>
            </resources>
        </build>
    
        <dependencies>
            
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.16.20</version>
            </dependency>
            <dependency>
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-simple</artifactId>
                <version>1.7.25</version>
            </dependency>
            
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>3.8</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>3.8</version>
            </dependency>
            
            <dependency>
                <groupId>junit</groupId>
                <artifactId>junit</artifactId>
                <version>4.12</version>
                <scope>test</scope>
            </dependency>
        </dependencies>
    
    </project>

### ExcelReadWrite.java ###

package com.ydfind.excel;
    
    import lombok.extern.slf4j.Slf4j;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    
    import java.io.*;
    import java.util.Objects;
    
    @Slf4j
    public class ExcelReadWrite {
    
        /**
         * 通过复制格子的形式，作为读写的例子
         * 将excel文件sourceFilename的内容，复制到targetFilename
         * @param sourceFilename
         * @param targetFilename
         */
        public static void copyExcel(String sourceFilename, String targetFilename) throws IOException {
            FileInputStream inputStream = null;
            FileOutputStream outputStream = null;
            try{
                log.info("start to copy excel from {} to {}", sourceFilename, targetFilename);
                // 源文件
                File inFile = new File(sourceFilename);
                inputStream = new FileInputStream(inFile);
                Workbook inWork = new HSSFWorkbook(inputStream);
                // 目标
                File outFile = new File(targetFilename);
                outputStream = new FileOutputStream(outFile);
                Workbook outWork = new HSSFWorkbook();
                // 复制
                copyExcel(inWork, outWork);
                // 保存到目标文件
                outWork.write(outputStream);
                log.info("copy success");
            }finally {
                if(outputStream != null){
                    try {
                        outputStream.close();
                    }catch (Exception e){
                        log.warn("outputStream close error!");
                    }
                }
                if(inputStream != null) {
                    try {
                        inputStream.close();
                    } catch (Exception e) {
                        log.warn("inputStream close error!");
                    }
                }
            }
        }
    
        private static void copyExcel(Workbook source, Workbook target){
            // 遍历复制
            int sheetCount = source.getNumberOfSheets();
            for(int i = 0; i < sheetCount; i++){
                Sheet inSheet = source.getSheetAt(i);
                Sheet outSheet = target.createSheet(inSheet.getSheetName());
                int rowCount = inSheet.getLastRowNum() + 1;
                for(int j = 0; j < rowCount; j++){
                    Row inRow = inSheet.getRow(j);
                    Row outRow = outSheet.createRow(j);
                    if(Objects.isNull(inRow)){
                        continue;
                    }
                    int colNum = inRow.getLastCellNum() + 1;
                    for(int coli = 0; coli < colNum; coli++){
                        Cell inCell = inRow.getCell(coli);
                        Cell outCell = outRow.createCell(coli);
                        if(Objects.isNull(inCell)){
                            continue;
                        }
                        String text = inCell.toString();
                        if(Objects.isNull(text)){
                            continue;
                        }
                        outCell.setCellValue(text.trim());
                    }
                }
            }
        }
    }

### FileUtil.java ###

package com.ydfind.util;
    
    import lombok.extern.slf4j.Slf4j;
    
    import java.io.*;
    
    @Slf4j
    public class FileUtil {
    
        public static void writeFile(String path, String str) throws IOException {
            try {
                File file = new File(path);
                if(!file.exists()) {
                    file.createNewFile();
                }
                FileOutputStream out = new FileOutputStream(file);
                StringBuffer sb = new StringBuffer();
                sb.append(str + "\r\n");
                out.write(sb.toString().getBytes("utf-8"));
                out.close();
            } catch(IOException ex) {
                throw new IOException("writeFile " + path + " error", ex);
            }
        }
    
        public static String readFile(String path) throws IOException {
            return readFile(path, "utf-8");
        }
    
        public static String readFile(String path, String encode) throws IOException {
            StringBuffer sb=new StringBuffer();
            String tempstr=null;
            try {
                File file=new File(path);
                if(!file.exists()) {
                    throw new FileNotFoundException();
                }
                FileInputStream fis=new FileInputStream(file);
                BufferedReader br=new BufferedReader(new InputStreamReader(fis, encode));
                while((tempstr=br.readLine())!=null) {
                    sb.append(tempstr + "\r\n");
                }
            } catch(IOException ex) {
                throw new IOException("readFile " + path + " error", ex);
            }
            return sb.toString();
        }
    
    }

### ExcelReadWriteTest.java ###

import com.ydfind.excel.ExcelReadWrite;
    import lombok.extern.slf4j.Slf4j;
    import org.junit.Test;
    
    import java.io.IOException;
    
    @Slf4j
    public class ExcelReadWriteTest {
    
        @Test
        public void testCopyExcel() throws IOException {
            String source = ExcelReadWriteTest.class.getClassLoader().getResource("source.xls").getFile();
            String target = ExcelReadWriteTest.class.getClassLoader().getResource("target.xls").getFile();
            ExcelReadWrite.copyExcel(source, target);
            log.info("打开 \".\\excel-demo\\target\\test-classes\\target.xls\", 查看是否复制成功！");
        }
    }

# 测试 #

新建两个excel文件，放到test的resource文件夹里（也可以放到别的地方，用绝对路径），运行test，就可以把source.xls的内容复制到target.xls里  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70]: /images/20220126/25653061537b476d91d8e6adc85dd37c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NuZGF5WVU_size_16_color_FFFFFF_t_70 1]: /images/20220126/124355f39ebe4f47b5a8c9b5874f601c.png