【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>