excel导入导出

引入jar包

<!--Excel导入 poi poi-ooxml-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>4.1.2</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

导入

import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * 导入excel
 *
 */
public class ExcelImport {

    public static void main(String[] args) {
        ExcelImport excelImport = new ExcelImport();
        excelImport.readExcel(new File("C:\\Users\\admin\\Desktop\\w_import.xlsx"));
    }

    public void readExcel(File file) {
        int columns = 3;
        try {
            Workbook sheets = WorkbookFactory.create(file);
//            Workbook sheets = XSSFWorkbookFactory.create(new File("C:\\Users\\admin\\Desktop\\white_import.xlsx"));
//            Workbook sheets = HSSFWorkbookFactory.create(new File("C:\\Users\\admin\\Desktop\\white_import.xlsx"));

            Sheet sheet = sheets.getSheetAt(0);

            int lastRowNum = sheet.getLastRowNum();
            System.out.println("总行数:" + lastRowNum);

            Row headRow = sheet.getRow(0);
            if (headRow.getPhysicalNumberOfCells() != columns) {
                System.out.println("列数不对");
                return;
            }
            for (int i = 0; i < columns; i++) {
                System.out.print(headRow.getCell(i));
                System.out.print(" | ");
            }
            for (int i = 1; i <= lastRowNum; i++) {
                System.out.println();
                Row row = sheet.getRow(i);
                for (int j = 0; j < columns; j++) {
                    Cell cell = row.getCell(j);
                    System.out.print(parseCell(cell));
                    System.out.print(" | ");
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private Object parseCell(Cell cell) {
        Object cellValue = "";
        if (cell != null) {
            CellType cellType = cell.getCellType();
            switch (cellType) {
                case NUMERIC:
                    if (DateUtil.isCellDateFormatted(cell)) {
                        Date dateCellValue = cell.getDateCellValue();
                        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                        cellValue = format.format(dateCellValue);
                    } else {
                        DataFormatter dataFormatter = new DataFormatter();
                        cellValue = dataFormatter.formatCellValue(cell);
                    }
                    break;
                case STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case BOOLEAN:
                    cellValue = cell.getBooleanCellValue();
                    break;
                case FORMULA:
                    cellValue = cell.getCellFormula();
                    break;
                case BLANK:
                case ERROR:
                    cellValue = "";
                    break;
                default:
                    break;
            }
        }
        return cellValue;
    }

}

导出

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
 * @author admin
 */
public class ExcelExport {

    public static void main(String[] args) {
        ExcelExport excelExport = new ExcelExport();
        try {
            excelExport.export(new FileOutputStream(new File("C:\\Users\\admin\\Desktop\\test_import.xlsx")));
            System.out.println("导出成功");
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
    }

    public void export(OutputStream outputStream) {

        Workbook workbook = createWorkBook();

        try {
            workbook.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }

    }

    private Workbook createWorkBook() {
        Workbook workbook = XSSFWorkbookFactory.createWorkbook();
        Sheet sheet = workbook.createSheet("sheet1");

        CellStyle cellStyle = setCellStyle(workbook);
        CellStyle headStyle = setHeadCellStyle(workbook);

        createRowHead(sheet, headStyle);
        createRows(sheet, cellStyle);
        return workbook;
    }

    private Font setFont(Workbook workbook) {
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 12);
        font.setBold(true);
        font.setFontName("宋体");
        return font;
    }

    private CellStyle setHeadCellStyle(Workbook workbook) {
        Font font = setFont(workbook);
        CellStyle headStyle = workbook.createCellStyle();
        headStyle.setFont(font);
        headStyle.setAlignment(HorizontalAlignment.CENTER);
        headStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        headStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        return headStyle;
    }

    private CellStyle setCellStyle(Workbook workbook) {
        Font font = setFont(workbook);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFont(font);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        return cellStyle;
    }

    private void createRows(Sheet sheet, CellStyle cellStyle) {
        for (int i = 1; i <= 5; i++) {
            Row row = sheet.createRow(i);
            for (int j = 0; j < 2; j++) {
                Cell cell = row.createCell(j);
                cell.setCellStyle(cellStyle);
                cell.setCellValue("test_" + i + "_" + j);
            }
            sheet.autoSizeColumn(i);
        }
    }

    private void createRowHead(Sheet sheet, CellStyle cellStyle) {
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellStyle(cellStyle);
        cell.setCellValue("测试名称");
        Cell cell1 = row.createCell(1);
        cell1.setCellStyle(cellStyle);
        cell1.setCellValue("测试");

        sheet.autoSizeColumn(0);
    }

    public void browserExport(HttpServletRequest request, HttpServletResponse response) {
        Workbook workbook = createWorkBook();

        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        String date = format.format(new Date());
        String filename = "test_export_" + date + ".xlsx";
        OutputStream outputStream = null;
        try {
            outputStream = response.getOutputStream();
            response.setHeader("Content-Disposition", "attachment; filename=".concat(filename));
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");

            BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outputStream);
            bufferedOutputStream.flush();

            workbook.write(bufferedOutputStream);
            outputStream.close();
            bufferedOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}