Java 与 Excel，支持 xls 和 xlsx

最新推荐文章于 2024-07-25 08:16:40 发布

hresh

最新推荐文章于 2024-07-25 08:16:40 发布

阅读量731

点赞数

分类专栏： Java 文章标签： Java

本文链接：https://blog.csdn.net/Herishwater/article/details/99744046

版权

Java 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

引入 Jar 包

前往 apache 官网进行下载，如图：
在这里插入图片描述
Jar 包建议选择最新的稳定版，这样功能更加齐全，使用起来体验感好。

然后点击下载，
在这里插入图片描述
对下载的压缩包进行解压，打开后如下：

这里显示的 Jar 包还不够，如果要实现对 Excel（支持 xls 和 xlsx）的导入导出功能，需要增加额外的 Jar 包，就在 lib 文件夹和 ooxml-lib 文件夹中可以找到。复制出来结果如下：
在这里插入图片描述

Excel 导入导出

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.*;


public class ExportExcelPoi {
    private static final String EXCEL_XLS = "xls";
    private static final String EXCEL_XLSX = "xlsx";

    /**
     * excel建立单个sheet表导出.
     */
    public static void exportExcel(String title, String[] headers, List<List<String>> dataset, OutputStream out) {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格
        HSSFSheet sheet = workbook.createSheet(title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        HSSFCellStyle style = workbook.createCellStyle();
        // 生成一个字体
        HSSFFont font = workbook.createFont();
//		font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);

        // 产生表格标题行
        HSSFRow row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {// i是headers的索引
            HSSFCell cell = row.createCell(i);
            HSSFRichTextString text = null;
            text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        for (int i = 0, index = 1; i < dataset.size(); i++, index++) {
            row = sheet.createRow(index);
            for (int j = 0; j < ((ArrayList) dataset.get(i)).size(); j++) {
                HSSFCell cell = row.createCell((short) j);
                HSSFRichTextString richString = new HSSFRichTextString(dataset.get(i).get(j));
                cell.setCellValue(richString);
            }
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * excel建立多个sheet表导出.
     */
    public static OutputStream exportExcel(String title, String[] headers, List<List<String>> dataset, OutputStream out, Workbook workbook, int sheetNum) {
        // 声明一个工作薄
//		HSSFWorkbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        workbook.setSheetName(sheetNum, title);
        // 设置表格默认列宽度为15个字节
        sheet.setDefaultColumnWidth((short) 15);
        // 生成一个样式
        CellStyle style = workbook.createCellStyle();
        // 生成一个字体
        Font font = workbook.createFont();
//		font.setColor(HSSFColor.VIOLET.index);
        font.setFontHeightInPoints((short) 12);
        // 把字体应用到当前的样式
        style.setFont(font);

        // 产生表格标题行
        Row row = sheet.createRow(0);
        for (short i = 0; i < headers.length; i++) {// i是headers的索引
            Cell cell = row.createCell(i);
            RichTextString text = null;
            if (workbook instanceof HSSFWorkbook){
                text = new HSSFRichTextString(headers[i]);
            }else{
                text = new XSSFRichTextString(headers[i]);
            }
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        for (int i = 0, index = 1; i < dataset.size(); i++, index++) {
            row = sheet.createRow(index);
            for (int j = 0; j < ((ArrayList) dataset.get(i)).size(); j++) {
                Cell cell = row.createCell((short) j);
                RichTextString text = null;
                if (workbook instanceof HSSFWorkbook){
                    text = new HSSFRichTextString(dataset.get(i).get(j));
                }else{
                    text = new XSSFRichTextString(dataset.get(i).get(j));
                }
                cell.setCellValue(text);
            }
        }

        return out;
    }

	//Excel数据导入
    public static List importExcel(File filePath) throws IOException {
        Workbook wb = null;
        FileInputStream in = new FileInputStream(filePath);
        if (filePath.getName().endsWith(EXCEL_XLS)) { //Excel 2003  
            wb = new HSSFWorkbook(in);
        } else if (filePath.getName().endsWith(EXCEL_XLSX)) { // Excel 2007 
            wb = new XSSFWorkbook(in);
        }

        return getExcelToList(wb);
    }
    /**
     * excel转成List.
     * 当读取的列为数字时，一旦转为文本模式，必须点击“数据”，“分列”按钮将该列完全转换为文本格式
     * 同时将所有列置为空格
     */
    public static List getExcelToList(Workbook workbook) throws IOException {
        List list = new ArrayList();

        Sheet sheet = workbook.getSheetAt(0);
//        HSSFSheet sheet = workbook.getSheet("Sheet1");        //sheet从0开始，本excel仅有一个sheet且名字为“result"
        int nRows = sheet.getLastRowNum();            //取出行数
        if (nRows < 1) {
            return list;
        }

        Cell cell;
        Map<String, String> map = null;
        // 取excel中有多少列----start---
        Row rows = sheet.getRow(0);        //取首行
        int tempRowSize = rows.getLastCellNum();        //取出列数
        String columnName[] = new String[tempRowSize];
        nRows += 1;        //取出最后一行的行号

        for (int i = 0; i < nRows; i++) {
            map = new HashMap();
            Row row = sheet.getRow(i);
            if (i == 0) {
                for (int m = 0; m < tempRowSize; m++) {
                    cell = row.getCell((short) m);
                    columnName[m] = cell.toString();
                }
                continue;
            } else if (i >= 1) {
                for (int n = 0; n < tempRowSize; n++) {
                    cell = row.getCell((short) n);
                    // map.put(columnName[n],this.parseCell(cell));
                    map.put(columnName[n], cell.toString());
                }
                list.add(map);
            }
        }
        return list;
    }

    /**
     * 转换成Map.
     */
    public static Map trimMapStr(Map map) {
        Map resultMap = new HashMap();
        Iterator it = map.entrySet().iterator();
        while (it.hasNext()) {
            Map.Entry entry = (Map.Entry) it.next();
            Object val = entry.getValue();
            if (val instanceof String) {
                String strVal = ((String) val).trim();
                resultMap.put(entry.getKey(), strVal);
            } else {
                resultMap.put(entry.getKey(), val);
            }
        }
        return resultMap;
    }
}

public static void main(String[] args) {
	Workbook workbook = null;

    String outfilePath = "D:" + File.separator + "curRule.xlsx";
    if (outfilePath.endsWith("xls")) {
        workbook = new HSSFWorkbook();
    } else if (outfilePath.endsWith("xlsx")) {
        workbook = new XSSFWorkbook();
    } else {
        System.out.println("您的文档格式不正确！");
    }

    List<List<String>> data = new ArrayList<List<String>>();
    String sheetName = "RULE_PROJECT";
    String[] ruleProjectTitles = {"ID", "PROJECT_ENAME", "PROJECT_DESC", "PROJECT_TYPE", "ENV_TYPE", "REC_CREATOR", "REC_CREATE_TIME", "REC_REVISOR", "REC_REVISE_TIME",
            "ARCHIVE_FLAG", "VERSION"};

    OutputStream out = null;
    try {
        out = new FileOutputStream(outfilePath);
        exportExcel(sheetName, ruleProjectTitles, data, out, workbook,0);

    } catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }

    data.clear();
    //这些数据都是测试用的，可自行更改
    for (RuleGroup ruleGroup : listRuleGroup) {
        List<String> rowData = new ArrayList<String>();

        rowData.add(" ");//存放id信息
        rowData.add(ruleGroup.getRecCreator().toString());
        rowData.add(ruleGroup.getRecCreateTime().toString());
        rowData.add(ruleGroup.getRecRevisor().toString());
        rowData.add(ruleGroup.getRecReviseTime().toString());
        rowData.add(ruleGroup.getArchiveFlag().toString());//存放归档标记

        rowData.add(ruleGroup.getProjectEname().toString());
        rowData.add(ruleGroup.getEnvType().toString());
        rowData.add(ruleGroup.getRuleGroup().toString());
        rowData.add(ruleGroup.getRuleGroupDesc().toString());
        rowData.add(ruleGroup.getRuleGroupSeq().toString());
        rowData.add(" ");
        rowData.add(ruleGroup.getEnabled().toString());
        rowData.add(ruleGroup.getVersion().toString());
        rowData.add(ruleGroup.getPriority().toString());

        data.add(rowData);
    }

    sheetName = "RULE_GROUP";
    String[] ruleGroupTitles = {"ID", "REC_CREATOR", "REC_CREATE_TIME", "REC_REVISOR", "REC_REVISE_TIME", "ARCHIVE_FLAG", "PROJECT_ENAME", "ENV_TYPE", "RULE_GROUP",
            "RULE_GROUP_DESC", "RULE_GROUP_SEQ", "PRE_RULE_GROUP", "ENABLED", "VERSION", "PRIORITY"};

    exportExcel(sheetName, ruleGroupTitles, data, out, workbook,1);
    try {
        workbook.write(out);
        out.close();
        System.out.println("导出curRule表成功！" + outfilePath);
    } catch (IOException e) {
        e.printStackTrace();
    }
}