java——java使用poi操作excel，java excel读写

maven依赖：

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
		    <groupId>org.apache.poi</groupId>
		    <artifactId>poi</artifactId>
		    <version>4.1.0</version>
		</dependency>
		<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-scratchpad</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.1.0</version>
        </dependency>

工具类：

1.代码都是活的可以根据自己的业务修改，比如读取返还的数据格式不爽可以修改。

2.catch部分建议使用工程的日志系统 把异常记录。

package hoperun.pagoda.dxlAnalyse.utils;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * Use poi Operate the Excel.
 * @author zy
 */
public final class PoiUtils {
    /**
     * writeWorkbook.
     */
    private static Workbook writeWorkbook;
    /**
     * readworkbook.
     */
    private static Workbook readworkbook;
    /**
     * outFilepath.
     */
    private static String writeFilePath;
    /**
     * readFilepath.
     */
    private static String readFilePath;
    /**
     * excel xls.
     */
    public static final String OFFICE_EXCEL_XLS = "xls";
    /**
     * excel xlsx.
     */
    public static final String OFFICE_EXCEL_XLSX = "xlsx";
    /**
     * font size 12.
     */
    public static final int FONT_SIZE_12 = 12;
    /**
     * cell width 15.
     */
    public static final int CELL_DEFAULT_WIDTH = 20;

    /**
     * font size 16.
     */
    public static final int FONT_SIZE_16 = 16;
    /**
     * construction.
     */
    private PoiUtils() {

    }

    /**
     * Gets the Workbook object based on the file path.
     * @param filepath1 filepath
     */
    public static void getWorkbook(final String filepath1) {
        readFilePath = filepath1;
        InputStream is = null;
        if (readFilePath.isEmpty()) {
            throw new IllegalArgumentException("The file path cannot be empty");
        } else {
            String suffiex = getSuffiex(readFilePath);
            if (suffiex.isEmpty()) {
                throw new IllegalArgumentException("The file suffix cannot be empty");
            }
            if (OFFICE_EXCEL_XLS.equals(suffiex) || OFFICE_EXCEL_XLSX.equals(suffiex)) {
                try {
                    is = new FileInputStream(readFilePath);
                    readworkbook = WorkbookFactory.create(is);
                } catch (FileNotFoundException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (EncryptedDocumentException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                } catch (IOException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }finally {
                    try {
                        if (is != null) {
                            is.close();
                        }
                        if (readworkbook != null) {
                            readworkbook.close();
                        }
                    } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }
                }
            } else {
                throw new IllegalArgumentException("This file is not an Excel");
            }
        }
    }

    /**
     * Also read the contents of the specified Sheet.
     * @param sheetNo The sheet number, starting at 0, is set to null if sheetNo reads the full text.
     * @return excel content.
     */
    public static String readExcel(final Integer sheetNo) {
        StringBuilder sb = new StringBuilder();
        try {
            getWorkbook(readFilePath);
            if (readworkbook != null) {
                if (sheetNo == null) {
                    int numberOfSheets = readworkbook.getNumberOfSheets();
                    for (int i = 0; i < numberOfSheets; i++) {
                        Sheet sheet = readworkbook.getSheetAt(i);
                        if (sheet == null) {
                            continue;
                        }
                        sb.append(readExcelSheet(sheet));
                    }
                } else {
                    Sheet sheet = readworkbook.getSheetAt(sheetNo);
                    if (sheet != null) {
                        sb.append(readExcelSheet(sheet));
                    }
                }
            }
        } catch (EncryptedDocumentException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return sb.toString();
    }

    /**
     * read Excel Sheet.
     * @param sheet sheet
     * @return String
     */
    private static String readExcelSheet(final Sheet sheet) {
        StringBuilder sb = new StringBuilder();
        if (sheet != null) {
            int rowNos = sheet.getLastRowNum();// Get the total number of records in excel
            for (int i = 0; i <= rowNos; i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    int columNos = row.getLastCellNum();
                    for (int j = 0; j < columNos; j++) {
                        Cell cell = row.getCell(j);
                        if (cell != null) {
                            cell.setCellType(CellType.STRING);
                            sb.append(cell.getStringCellValue() + " ");
                        }
                    }
                }
            }
        }
        return sb.toString();
    }

    /**
     * Create Workbook.
     * @param filepath1 filepath.
     */
    public static void createWorkbook(final String filepath1) {
        writeFilePath = filepath1;
        if (writeFilePath.isEmpty()) {
            throw new IllegalArgumentException("文件路径不能为空");
        } else {
            String suffiex = getSuffiex(writeFilePath);
            if (suffiex.isEmpty()) {
                throw new IllegalArgumentException("文件后缀不能为空");
            }
            if (OFFICE_EXCEL_XLS.equals(suffiex.toLowerCase())) {
                writeWorkbook = new HSSFWorkbook();
            } else {
                writeWorkbook = new XSSFWorkbook();
            }
        }
    }

    /**
     * creat Excel file.
     * @param sheetName new sheet name.
     * @param allData all data.
     */
    public static void creatSheet(final String sheetName, final List<List<Object>> allData) {
        //creat sheet.
        Sheet sheet;
        if (sheetName.isEmpty()) {
            // use default sheet name.
            sheet = writeWorkbook.createSheet();
        } else {
            sheet = writeWorkbook.createSheet(sheetName);
        }
        //set column width to default.
        sheet.setDefaultColumnWidth((short) CELL_DEFAULT_WIDTH);
        //creat style.
        Map<String, CellStyle> styles = createStyles(writeWorkbook);
        for(int rowIndex = 0; rowIndex<allData.size();rowIndex++) {

            Row row = sheet.createRow(rowIndex);
            List<Object> cellsData = allData.get(rowIndex);
            for(int cellIndex = 0;cellIndex<cellsData.size();cellIndex++) {
                Cell cell = row.createCell(cellIndex);
                if(rowIndex == 0) {
                    cell.setCellStyle(styles.get("header"));
                }else if ((rowIndex & 1)==1) {
                    //w 设置cell的样式
                    cell.setCellStyle(styles.get("cellA"));
                } else {
                    cell.setCellStyle(styles.get("cellB"));
                }
                Object cellData = cellsData.get(cellIndex);
                //Determine the type of object.
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                if (cellData instanceof Double) {
                    cell.setCellValue((Double) cellData);
                } else if (cellData instanceof Date) {
                    String time = simpleDateFormat.format(cellData);
                    cell.setCellValue(time);
                } else if (cellData instanceof Calendar) {
                    Calendar calendar = (Calendar) cellData;
                    String time = simpleDateFormat.format(calendar.getTime());
                    cell.setCellValue(time);
                } else if (cellData instanceof Boolean) {
                    cell.setCellValue((Boolean) cellData);
                } else {
                    if (cellData != null) {
                        cell.setCellValue(cellData.toString());
                    }
                }
            }
        }
    }

    /**
     * write Execute.
     * @return result
     */
    public static boolean writeExcel() {
        boolean result = false;
        OutputStream outputStream = null;
        try {
            outputStream = new FileOutputStream(writeFilePath);
            writeWorkbook.write(outputStream);
            result = true;
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();
                }
                if (writeWorkbook != null) {
                    writeWorkbook.close();
                }
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return result;
    }

    /**
     * set style.
     * @param wb Workbook.
     * @return style Map.
     */
    private static Map<String, CellStyle> createStyles(final Workbook wb) {
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        //title style.
        CellStyle titleStyle =  wb.createCellStyle();
        titleStyle.setAlignment(HorizontalAlignment.CENTER); // 水平对齐
        titleStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直对齐
        titleStyle.setLocked(true); // 样式锁定
        titleStyle.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        Font titleFont = wb.createFont();
        titleFont.setFontHeightInPoints((short) FONT_SIZE_16);
        titleFont.setBold(true);
        titleFont.setFontName("微软雅黑");
        titleStyle.setFont(titleFont);
        styles.put("title", titleStyle);

        //header style.
        CellStyle headerStyle = wb.createCellStyle();
        headerStyle.setAlignment(HorizontalAlignment.CENTER);
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        headerStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex()); // 前景色
        headerStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 颜色填充方式
        headerStyle.setWrapText(true);
        headerStyle.setBorderRight(BorderStyle.THIN); // 设置边界
        headerStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderLeft(BorderStyle.THIN);
        headerStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderTop(BorderStyle.THIN);
        headerStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        headerStyle.setBorderBottom(BorderStyle.THIN);
        headerStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        Font headerFont = wb.createFont();
        headerFont.setFontHeightInPoints((short) FONT_SIZE_12);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        titleFont.setFontName("微软雅黑");
        headerStyle.setFont(headerFont);
        styles.put("header", headerStyle);

        Font cellStyleFont = wb.createFont();
        cellStyleFont.setFontHeightInPoints((short) FONT_SIZE_12);
        cellStyleFont.setColor(IndexedColors.BLUE_GREY.getIndex());
        cellStyleFont.setFontName("微软雅黑");

        //content style A.
        CellStyle cellStyleA =  wb.createCellStyle();
        cellStyleA.setAlignment(HorizontalAlignment.CENTER); // 居中设置
        cellStyleA.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleA.setWrapText(true);
        cellStyleA.setBorderRight(BorderStyle.THIN);
        cellStyleA.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderLeft(BorderStyle.THIN);
        cellStyleA.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderTop(BorderStyle.THIN);
        cellStyleA.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setBorderBottom(BorderStyle.THIN);
        cellStyleA.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleA.setFont(cellStyleFont);
        styles.put("cellA", cellStyleA);
        //content style A:The foreground color is light yellow.
        CellStyle cellStyleB =  wb.createCellStyle();
        cellStyleB.setAlignment(HorizontalAlignment.CENTER);
        cellStyleB.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleB.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        cellStyleB.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleB.setWrapText(true);
        cellStyleB.setBorderRight(BorderStyle.THIN);
        cellStyleB.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderLeft(BorderStyle.THIN);
        cellStyleB.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderTop(BorderStyle.THIN);
        cellStyleB.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setBorderBottom(BorderStyle.THIN);
        cellStyleB.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyleB.setFont(cellStyleFont);
        styles.put("cellB", cellStyleB);
        return styles;
    }


    /**
     * get suffix.
     * @param path path.
     * @return suffix
     */
    private static String getSuffiex(final String path) {
        if ( path.isEmpty()) {
            return "";
        }
        int index =  path.lastIndexOf(".");
        if (index == -1) {
            return "";
        }
        return  path.substring(index + 1,  path.length());
    }
}

写入调用：

// 创建workbook
String outFilePath = "c://test.xls";
PoiUtils.createWorkbook(outFilePath);
//创建第一个sheet并写入数据
List<List<Object>> formDate = new  ArrayList<List<Object>>();
List<Object> formTitle = Arrays.asList("Form一览","form名","Filed数","Button数","Code量");
formDate.add(....);//添加行的内容
disposalFormData(formCountMap, formDate);
String sheetName1 = "sheet名字";
PoiUtils.creatSheet(sheetName1, formDate);
//创建第二个sheet并写入数据
//......
//把workboot写入文件
PoiUtils.writeExcel()

读取调用：

String filePath = "c://test.xls";
PoiUtils.getWorkbook(filePath);
//读第一个sheet内容
String content = PoiUtils.readExcel(0);
//读所有sheet内容
String content1 = PoiUtils.readExcel(null);

poi 设置超链接

public void testSetHyperLink() {
    XSSFWorkbook workbook = new XSSFWorkbook();
    try {
        FileOutputStream out = new FileOutputStream("d:\\testSetHyperLink.xlsx");
        XSSFSheet sheet = workbook.createSheet("1");
        XSSFCellStyle style = workbook.createCellStyle();
        XSSFFont font = workbook.createFont();
        font.setUnderline(XSSFFont.U_DOUBLE);
        font.setColor(IndexedColors.RED.getIndex());
        style.setFont(font);


        CreationHelper createHelper = workbook.getCreationHelper();

        XSSFCellStyle cellStyle = workbook.createCellStyle();
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue("test.xlsx");
        XSSFFont font1 = workbook.createFont();
        font1.setColor(HSSFColor.GREEN.index);
        cellStyle.setFont(font1);
        cell.setCellStyle(cellStyle);

        Hyperlink hyperlink = createHelper.createHyperlink(Hyperlink.LINK_FILE);
        hyperlink.setAddress("C://Users//Administrator//Desktop//test.xlsx");
        cell.setHyperlink(hyperlink);
        workbook.write(out);
        out.close();

    } catch (IOException e) {
        e.printStackTrace();
    }

}