【Java】POI对于Excel 2007处理的案例

Java使用POI操作Excel，包含读写Excel的操作，maven工程，用于后续的样例代码查找。

package com.kingdz;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadFile {

    public static void main(String[] args) {
        List<String> companyList = readCompanyFile();
        for (int i = 5; i < companyList.size(); i++) {
            processSheet(i, companyList.get(i));
        }
    }

    private static void processSheet(int number, String company) {
        number = number + 1;
        File[] files = new File("src/main/resources/sheet").listFiles();
        System.out.println(files[0].getAbsolutePath());

        List<String[]> list = new ArrayList<String[]>();
        for (File file : files) {
            System.out.println(number + "\t" + file.getName());
            list.add(new String[] { file.getName().replace(".xlsx", "") });
            XSSFWorkbook hssfWorkbook = null;
            try {
                hssfWorkbook = new XSSFWorkbook(new FileInputStream(file));
                XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
                int totalRows = hssfSheet.getLastRowNum() + 1;
                int totalCols = hssfSheet.getRow(0).getPhysicalNumberOfCells();
                String[] array = new String[totalCols];
                XSSFRow row0 = hssfSheet.getRow(0);
                for (int i = 0; i < totalCols; i++) {
                    XSSFCell cell = row0.getCell(i);
                    if (cell != null) {
                        array[i] = "" + cell.getStringCellValue();
                    }
                }
                list.add(array);

                for (int i = 0; i < totalRows; i++) {
                    array = new String[totalCols];

                    XSSFRow row = hssfSheet.getRow(i);
                    int cols = row.getPhysicalNumberOfCells();
                    String name = row.getCell(0).getStringCellValue();
                    if (company.equals(name)) {
                        for (int j = 0; j < cols; j++) {
                            XSSFCell cell = row.getCell(j);
                            if (cell != null) {
                                array[j] = "" + cell.getStringCellValue();
                            }
                        }
                        list.add(array);
                    }
                }
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (hssfWorkbook != null) {
                    try {
                        hssfWorkbook.close();
                    } catch (IOException e) {

                    }
                }
            }
            list.add(new String[] { "" });
        }

        {
            XSSFWorkbook workBook = new XSSFWorkbook();
            XSSFSheet sheet = workBook.createSheet();

            for (int i = 0; i < list.size(); i++) {
                XSSFRow row = sheet.createRow(i);
                String[] array = list.get(i);
                for (int j = 0; j < array.length; j++) {
                    XSSFCell cell = row.createCell(j);
                    cell.setCellValue(array[j]);
                }
            }

            FileOutputStream os = null;
            try {
                os = new FileOutputStream("src/main/resources/result/" + number + "、" + company + ".xlsx");
                workBook.write(os);
            } catch (FileNotFoundException e) {
                e.printStackTrace();
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                if (os != null) {
                    try {
                        os.close();
                    } catch (IOException e) {
                        e.printStackTrace();
                    }
                }
                if (workBook != null) {
                    try {
                        workBook.close();
                    } catch (IOException e) {

                    }
                }
            }

            System.out.println("创建文件成功 office 2007 excel");
        }
    }

    private static List<String> readCompanyFile() {
        List<String> list = new ArrayList<String>();

        BufferedReader br = null;
        try {
            br = new BufferedReader(new FileReader("src/main/resources/company"));
            String str = br.readLine();
            while (str != null) {
                list.add(str.trim());
                str = br.readLine();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {

                }
            }
        }

        return list;
    }

}

POM文件依赖

	<dependencies>
		<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.16</version>
		</dependency>

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