Java中采用apache poi实现生成和读取Excel文件

最新推荐文章于 2025-06-30 00:44:22 发布

小阿哲

最新推荐文章于 2025-06-30 00:44:22 发布

阅读量790

点赞数

CC 4.0 BY-SA版权

分类专栏： java

本文链接：https://blog.youkuaiyun.com/czy6538/article/details/81629240

java 专栏收录该内容

2 篇文章

订阅专栏

这篇博客详细介绍了如何在Java中利用Apache POI库来生成和读取Excel文件，包括创建工作簿、设置单元格数据、读取数据等关键步骤，为Java开发者提供了实用的Excel操作指南。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import java.io.ByteArrayOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
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.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * 
 * <p>Title: ExcelUtils</p>
 * <p>Description: Excel工具</p>
 * <p>Copyright: Copyright (c) 2018</p>
 * @author xiaoazhe
 * @version 1.0
 * @createtime 2018年08月13日 上午11:18:28
 *
 */
public class ExcelUtils {


    /**
     * @Title: export
     * @Description: 导出Excel
     * @param hashMap  每个Key为一个Sheet页对应的value 对应的是sheet页中的数据
     * @param out   生成Excel
     * @throws Exception
     * @return void
     * @author xiaoazhe
     * @version 1.0
     * @createtime 2018年08月13日 上午11:18:28
     */
    public static ByteArrayOutputStream export(Map<String, List<Object[]>> hashMap)throws Exception {

        ByteArrayOutputStream out = new ByteArrayOutputStream(1024);
        try {
            if(hashMap == null){
                throw new RuntimeException("数据参数不能为空!");
            }
            Workbook workbook = (Workbook) new HSSFWorkbook();
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(CellStyle.ALIGN_CENTER);

            CellStyle firstStyle = workbook.createCellStyle();
            firstStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
            firstStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
            firstStyle.setAlignment(CellStyle.ALIGN_CENTER);
            firstStyle.setWrapText(true);
            firstStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
            Set<String> keySet = hashMap.keySet();
            for(String sheetName : keySet){
                List<Object[]> list = hashMap.get(sheetName);
                Sheet sheet = workbook.createSheet(sheetName);
                sheet.setDefaultColumnWidth(20);
                int index = 0;
                Row row = null;
                Iterator<Object[]> its = list.iterator();
                while (its.hasNext()) {
                    row = sheet.createRow(index);
                    Object[] t = its.next();
                    for (int k = 0; k < t.length; k++) {
                        Cell cell = row.createCell((short) k);
                        if (t[k] == null || t[k].toString().trim().length() == 0) {
                            cell.setCellValue("");
                            continue;
                        }
                        cell.setCellValue(t[k].toString().length()>=501?t[k].toString().substring(0, 500):t[k].toString());
                        if(index == 0){//第一行设置背景
                            cell.setCellStyle(firstStyle);
                        }else{
                            cell.setCellStyle(style);
                        }
                    }
                    index++;
                }
            }
            workbook.write(out);
        } catch (Exception e) {
            throw new Exception("导出失败",e);
        }
        return out;
    }

    /**
     * 
     * @Title: readExcelFile
     * @Description: 读取Excel并返回
     * @param is
     * @return
     * @throws Exception
     * @return List<List<String>>[]
     * @author xiaozhe
     * @version 1.0
     * @createtime 2018年08月13日 上午11:18:28
     */
    public static Map<String,List<List<String>>> readExcelFile(InputStream is) throws Exception {

        Map<String,List<List<String>>> sheets = new LinkedHashMap<String, List<List<String>>>();
        try {
            Workbook hwb = WorkbookFactory.create(is);//根据文件流创建对应的Excel
            int num = hwb.getNumberOfSheets();//获取excel页数
            for (int sheetnum = 0; sheetnum < num; sheetnum++) {
                List<List<String>> rows = new ArrayList<List<String>>(); //表示一行 一行中包含List<String>列数据
                Sheet sheet = hwb.getSheetAt(sheetnum);
                if (sheet == null) {
                    continue;
                }
                String sheetName = sheet.getSheetName();
                // 循环行
                for (int rownum = 0; rownum <= sheet.getLastRowNum(); rownum++) {
                    // 循环列
                    List<String> cells = new ArrayList<String>();
                    Row row = sheet.getRow(rownum);
                    if(row == null){
                        continue;
                    }
                    for (int colnum = 0; colnum < row.getLastCellNum(); colnum++) {
                        Cell ce = row.getCell(colnum);
                        if(ce == null){
                            continue;
                        }
                        ce.setCellType(Cell.CELL_TYPE_STRING);//将列一律转换成文本类型
                        cells.add(ce.getStringCellValue());
                    }
                    rows.add(cells);
                }
                sheets.put(sheetName, rows);
            }
        } catch (Exception e) {
            throw new Exception(e);
        }
        return sheets;
    }
}