java导出/解析excel

本文有三个方法：

1，导出excel模板，只有表头

2，导出excel，将数据导出为excel

3，解析excel

package com.hebi.util;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.util.WorkbookUtil;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

/**
 * Created by rwh on 2024/1/22 19:24
 */
public class ExcelUtil {
    public static void main(String[] args) throws Exception{
        // 导出excel模板，导出的单元格为文本格式，这样用此模板时手机号就不会是数字格式了，方便解析
        exportExcelTemplate();

        // 导出excel
        exportExcelData();

        // 解析excel
        readExcel();
    }

    private static void readExcel() throws Exception{
        FileInputStream inputStream=new FileInputStream(new File("C:\\test\\带解析的数据.xls"));
        HSSFWorkbook workbook=new HSSFWorkbook(inputStream);
        // 循环工作簿
        for (int sheeetIndex = 0; sheeetIndex < workbook.getNumberOfSheets(); sheeetIndex++) {
            HSSFSheet sheet = workbook.getSheetAt(sheeetIndex);
            if (sheet==null) {
                continue;
            }
            // 循环行
            for (int rowIndex = 0; rowIndex < sheet.getLastRowNum(); rowIndex++) {
                HSSFRow row = sheet.getRow(rowIndex);
                if (row==null) {
                    continue;
                }
                // 循环列
                for (int cellIndex = 0; cellIndex < row.getLastCellNum(); cellIndex++) {
                    HSSFCell cell = row.getCell(cellIndex);
                    if (cell==null || cell.getCellType()==CellType.BLANK) {
                        continue;
                    }
                    String cellValue="";
                    // 根据不同格式做不同处理
                    if (cell.getCellType()== CellType.NUMERIC) {
                        // 数字格式
                        double numericCellValue = cell.getNumericCellValue();
                        cellValue=String.valueOf(numericCellValue);
                    }else {
                        // 其他格式
                        cellValue = cell.getStringCellValue();
                    }
                    System.out.println(cellValue);
                }
            }
        }
    }

    private static void exportExcelData() throws Exception{
        // 思路，导出的excel数据格式可能为：字符串，数字带小数位，日期，长数字（手机号），证件号码等
        // 所以在导出前把这些都转为string，这样方便一些

        HSSFWorkbook workbook=new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("工作簿"));
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 单元格水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 字体
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);
        // 表头
        List<String> excelHeadList=new ArrayList<>();
        Collections.addAll(excelHeadList,"姓名","分数","手机","证件号码","出生日期");
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < excelHeadList.size(); i++) {
            HSSFCell cell = row.createCell(i);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(excelHeadList.get(i));
        }
        // 准备数据
        List<List<String>> lists=new ArrayList<>();
        List<String> list1=new ArrayList<>();
        Collections.addAll(list1,"张三","66.66","15094638661","127837199012221112","2023-1-2 12:01:10");
        List<String> list2=new ArrayList<>();
        Collections.addAll(list2,"李四","66.67","15094638662","127837199012221113","2023-1-2 12:11:11");
        Collections.addAll(lists,list1,list2);
        // 填充数据
        for (int i = 1; i < lists.size()+1; i++) {
            HSSFRow row1 = sheet.createRow(i);
            for (int j = 0; j < lists.get(i-1).size(); j++) {
                HSSFCell cell = row1.createCell(j);
                cell.setCellValue(lists.get(i-1).get(j));
            }
        }
        // 导出
        File file =new File("C:\\test\\excel导出数据.xls");
        workbook.write(file);
    }

    private static void exportExcelTemplate() throws Exception{
        HSSFWorkbook workbook=new HSSFWorkbook();
        // 表头
        List<String> excelHeadList=new ArrayList<>();
        Collections.addAll(excelHeadList,"姓名","分数","手机","证件号码","出生日期");
        HSSFSheet sheet = workbook.createSheet(WorkbookUtil.createSafeSheetName("工作簿"));
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 单元格水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 上下左右边框加粗
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setBorderRight(BorderStyle.THIN);
        // 字体
        HSSFFont font = workbook.createFont();
        font.setBold(true);
        cellStyle.setFont(font);

        HSSFCellStyle style = workbook.createCellStyle();
        HSSFDataFormat dataFormat = workbook.createDataFormat();
        HSSFRow row = sheet.createRow(0);
        for (int i = 0; i < excelHeadList.size(); i++) {
            // 给每一列的单元格样式设置为文本样式
            style.setDataFormat(dataFormat.getFormat("@"));
            sheet.setDefaultColumnStyle(i,style);
            HSSFCell cell = row.createCell(i);
            // 给单元格应用样式
            cell.setCellStyle(cellStyle);
            // 给单元格设置值
            cell.setCellValue(excelHeadList.get(i));
        }
        // 导出
        File file =new File("C:\\test\\excel下载模板.xls");
        workbook.write(file);
    }
}

1，导出的excel模板

2，导出的excel

3，excel数据

控制台输出：