Java文件处理：Excel 文档创建、写入和读取

pom.xml

		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>3.17</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>3.17</version>
		</dependency>

创建excel

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;

public class CreateExcel {

    /**
     * 2003 excel后缀
     */
    private static final String EXCEL_XLS = "xls";

    /**
     * 2007 及以上版本excel后缀
     */
    private static final String EXCEL_XLSX = "xlsx";

    public static void main(String args[]) {
        createExcel("E:\\123.xls");
        createExcel("E:\\123.xlsx");
    }

    /**
     * 功能描述:
     * <创建Excel>
     *
     * @param filePath 1
     * @return void
     * @author zhoulipu
     * @date 2019/8/2 17:04
     */
    private static void createExcel(String filePath) {
        FileOutputStream out = null;
        try {
            // 创建工作薄
            Workbook wb = null;
            if (StringUtils.substringAfterLast(filePath, ".").equals(EXCEL_XLS)) {
                wb = new HSSFWorkbook();
            }
            if (StringUtils.substringAfterLast(filePath, ".").equals(EXCEL_XLSX)) {
                wb = new XSSFWorkbook();
            }
            if (wb == null) {
                System.out.println("文件后缀不规范，无法操作excel文件，后缀示例：" + EXCEL_XLS + ", " + EXCEL_XLSX);
                return;
            }
            out = new FileOutputStream(filePath);
            // 创建工作表
            wb.createSheet();
            // 设置工作表名
            wb.setSheetName(0, "Sheet1");
            wb.write(out);
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

}

写入excel

import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import java.io.*;
import java.util.ArrayList;
import java.util.List;

public class WriteExcel {

    public static void main(String args[]) {
        List<Object> rowList = new ArrayList<>();
        for (int i = 1; i <= 10; i++) {
            List<String> cellList = new ArrayList<>();
            for (int j = 1; j <= 10; j++) {
                cellList.add(i + "行" + j + "列");
            }
            rowList.add(cellList);
        }
        // 写入数据
        writeExcel(rowList, "E:\\123.xls");
        writeExcel(rowList, "E:\\123.xlsx");
        // 删除数据
        deleteMessage(2, 5, "E:\\123.xls");
        deleteMessage(6, 9, "E:\\123.xlsx");
    }

    /**
     * 功能描述:
     * <写入Excel>
     *
     * @param rowList  1
     * @param filePath 2
     * @return void
     * @author zhoulipu
     * @date 2019/8/2 17:38
     */
    public static void writeExcel(List<Object> rowList, String filePath) {
        OutputStream out = null;
        try {
            File file = new File(filePath);
            Workbook workbook = getWorkbook(file);
            // 取第一个Sheet工作表
            Sheet sheet = workbook.getSheetAt(0);
            // 设置单元格属性
            CellStyle cellStyle = workbook.createCellStyle();
            // 水平居中
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            // 垂直居中
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 文字属性
            Font font;
            // 颜色样式
            CellStyle colorStyle = workbook.createCellStyle();
            font = workbook.createFont();
            font.setColor(IndexedColors.RED.getIndex());
            colorStyle.setFont(font);
            colorStyle.setAlignment(HorizontalAlignment.CENTER);
            colorStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 加粗样式
            CellStyle boldStyle = workbook.createCellStyle();
            font = workbook.createFont();
            font.setBold(true);
            boldStyle.setFont(font);
            boldStyle.setAlignment(HorizontalAlignment.CENTER);
            boldStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            // 写入数据
            for (int i = 0; i < rowList.size(); i++) {
                // 创建行
                Row row = sheet.createRow(i);
                List<String> cellList = (ArrayList<String>) rowList.get(i);
                for (int j = 0; j < cellList.size(); j++) {
                    // 创建列
                    Cell cell = row.createCell(j);
                    cell.setCellValue(cellList.get(j));
                    cell.setCellStyle(cellStyle);
                    if (i == 0) {
                        cell.setCellStyle(boldStyle);
                    }
                    if (i == (rowList.size() - 1)) {
                        cell.setCellStyle(colorStyle);
                    }
                }
            }
            // 第一列列宽为10
            sheet.setColumnWidth(0, (int) ((10 + 0.72) * 256));
            out = new FileOutputStream(filePath);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 功能描述:
     * <删除数据>
     *
     * @param rowMin   1 需从第几行开始删除（非下标）
     * @param rowMax   2 需从第几行结束删除
     * @param filePath 3
     * @return void
     * @author zhoulipu
     * @date 2019/8/2 17:38
     */
    public static void deleteMessage(int rowMin, int rowMax, String filePath) {
        OutputStream out = null;
        try {
            File file = new File(filePath);
            Workbook workbook = getWorkbook(file);
            // 取第一个Sheet工作表
            Sheet sheet = workbook.getSheetAt(0);
            // 原始数据总行数
            int rowNum = sheet.getLastRowNum() + 1;
            System.out.println("原始数据总行数:" + rowNum);
            if (rowNum < rowMax) {
                rowMax = rowNum;
            }
            if (rowNum < rowMin) {
                System.out.println("数据量不够");
                return;
            }
            // 删除数据
            for (int i = rowMin; i <= rowMax; i++) {
                Row row = sheet.getRow(i - 1);
                if (row != null) {
                    sheet.removeRow(row);
                }
            }
            out = new FileOutputStream(filePath);
            workbook.write(out);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (out != null) {
                    out.flush();
                    out.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 功能描述:
     * <根据Excel的版本获取Workbook>
     *
     * @param file 1
     * @return org.apache.poi.ss.usermodel.Workbook
     * @author zhoulipu
     * @date 2019/8/2 17:22
     */
    public static Workbook getWorkbook(File file) throws IOException, InvalidFormatException {
        FileInputStream in = new FileInputStream(file);
        return WorkbookFactory.create(in);
    }

}

读取excel

import org.apache.poi.ss.usermodel.*;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class ReadExcel {

    public static void main(String args[]) {
        readExcel("E:\\123.xls");
        readExcel("E:\\123.xlsx");
    }

    /**
     * 功能描述:
     * <读取Excel>
     *
     * @param filePath 1
     * @return void
     * @author zhoulipu
     * @date 2019/8/3 11:45
     */
    private static void readExcel(String filePath) {
        FileInputStream in = null;
        try {
            File file = new File(filePath); // 创建文件对象
            in = new FileInputStream(file); // 文件流
            Workbook workbook = WorkbookFactory.create(in); // 这种方式 Excel2003/2007/2010都是可以处理的
            // Sheet的数量
            int sheetCount = workbook.getNumberOfSheets(); //
            System.out.println(filePath + "文件总共有" + sheetCount + "个工作表");
            for (int n = 0; n < sheetCount; n++) {
                Sheet sheet = workbook.getSheetAt(n);
                // 总行数
                int rowCount = sheet.getLastRowNum() + 1;
                System.out.println("第" + (n + 1) + "个工作表" + sheet.getSheetName() + "总共有" + rowCount + "行数据");
                for (int i = 0; i < rowCount; i++) {
                    Row row = sheet.getRow(i);
                    if (row != null) {
                        // 总列数
                        int cellCount = row.getLastCellNum();
                        // 非空总列数 row.getPhysicalNumberOfCells()
                        System.out.print("[" + (i + 1) + "][" + cellCount + "]\t");
                        for (int j = 0; j < cellCount; j++) {
                            Cell cell = row.getCell(j);
                            if (cell != null) {
                                System.out.print(getValue(cell));
                            }
                            System.out.print("\t");
                        }
                    } else {
                        System.out.print("[" + (i + 1) + "][0]\t");
                    }
                    System.out.print("\n");
                }
            }
            in.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 功能描述:
     * <根据列属性获取参数值>
     *
     * @param cell 1
     * @return java.lang.Object
     * @author zhoulipu
     * @date 2019/8/3 11:45
     */
    private static Object getValue(Cell cell) {
        Object obj = "";
        switch (cell.getCellTypeEnum()) {
            case BOOLEAN:
                obj = cell.getBooleanCellValue();
                break;
            case ERROR:
                obj = cell.getErrorCellValue();
                break;
            case NUMERIC:
                obj = cell.getNumericCellValue();
                break;
            case STRING:
                obj = cell.getStringCellValue();
                break;
            default:
                break;
        }
        return obj;
    }

}