Excel(8) : [精](poi3)读取xls、按列生成xls、按行生成xls

楽焫旒璡

已于 2023-12-20 17:09:16 修改

阅读量527

点赞数

CC 4.0 BY-SA版权

分类专栏： # Excel 文章标签：读取xls poi 按行生成xls 按列生成xls

于 2019-07-25 17:50:23 首次发布

本文链接：https://blog.youkuaiyun.com/Lxinccode/article/details/97279040

Excel 专栏收录该内容

19 篇文章

订阅专栏

本文介绍了一个使用Java进行Excel读写的实用工具类。该工具类能够读取Excel文件，并将数据转换为List集合；同时，也提供了按列或按行插入数据到Excel的功能，包括样式设置和行列宽度调整。

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


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

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;

/**
 * @Auther: liyue
 * @Date: 2019/7/25 17:44
 * @Description:
 */
public class ExcelUtil {

    public static void main(String[] args) {
        List<List<String>> lists = readXls("/Users/liyue/Desktop/test/test.xls");
        for (List<String> list : lists) {
            System.out.println("case \"" + list.get(0) + "\":\n" +
                    "                return \"" + list.get(1).replace("java.lang.", "") + "\";");
        }
        System.out.println(lists.size());
    }


    /**
     * 读取Excel
     *
     * @param path
     * @return
     */
    public static List<List<String>> readXlsTest(String path) {
        List<List<String>> list = new LinkedList<>();
        try {
            FileInputStream is = new FileInputStream(path);
            HSSFWorkbook excel = new HSSFWorkbook(is);
            //获取第一个sheet
            HSSFSheet sheet0 = excel.getSheetAt(0);
            for (Iterator rowIterator = sheet0.iterator(); rowIterator.hasNext(); ) {
                List<String> params = new LinkedList<>();
                HSSFRow row = (HSSFRow) rowIterator.next();
                for (Iterator iterator = row.cellIterator(); iterator.hasNext(); ) {
                    HSSFCell cell = (HSSFCell) iterator.next();
                    switch (cell.getCellType()) {
                        case Cell.CELL_TYPE_FORMULA:
                            params.add(cell.getCellFormula());
                            break;
                        default:
                            cell.setCellType(Cell.CELL_TYPE_STRING);
                            params.add(cell.getStringCellValue());
                            break;
                    }
                }
                list.add(params);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }

        return list;
    }

    /**
     * 读取Excel
     *
     * @param path
     * @return
     */
    public static List<List<String>> readXls(String path) {
        List<List<String>> list = new LinkedList<>();
        try {
            FileInputStream is = new FileInputStream(path);
            HSSFWorkbook excel = new HSSFWorkbook(is);
            //获取第一个sheet
            HSSFSheet sheet0 = excel.getSheetAt(0);
            for (Iterator rowIterator = sheet0.iterator(); rowIterator.hasNext(); ) {
                List<String> params = new LinkedList<>();
                HSSFRow row = (HSSFRow) rowIterator.next();
                for (Iterator iterator = row.cellIterator(); iterator.hasNext(); ) {
                    HSSFCell cell = (HSSFCell) iterator.next();
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    params.add(cell.getStringCellValue());
                }
                list.add(params);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }

        return list;
    }

    /**
     * 读取Excel
     *
     * @param inputStream
     * @return
     */
    public static List<List<String>> readXls(InputStream inputStream) {
        List<List<String>> list = new LinkedList<>();
        try {
            FileInputStream is = (FileInputStream) inputStream;
            HSSFWorkbook excel = new HSSFWorkbook(is);
            //获取第一个sheet
            HSSFSheet sheet0 = excel.getSheetAt(0);
            for (Iterator rowIterator = sheet0.iterator(); rowIterator.hasNext(); ) {
                List<String> params = new LinkedList<>();
                HSSFRow row = (HSSFRow) rowIterator.next();
                for (Iterator iterator = row.cellIterator(); iterator.hasNext(); ) {
                    HSSFCell cell = (HSSFCell) iterator.next();
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    params.add(cell.getStringCellValue());
                }
                list.add(params);
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            return null;
        }

        return list;
    }

    /**
     * 一列一列插入
     *
     * @param lists
     * @param path
     */
    public static void generateXlsByColumn(List<List<String>> lists, String path) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建工作表1(Sheet)
            HSSFSheet sheet = workbook.createSheet("sheet");
            for (int j = 0; j < lists.size(); j++) {
                for (int i = 0; i < lists.get(j).size(); i++) {
                    getRow(sheet, i).createCell(j).setCellValue(lists.get(j).get(i));
                }
            }
            FileOutputStream out = new FileOutputStream(path);
            //保存Excel文件
            workbook.write(out);
            //关闭文件流
            out.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    /**
     * 一行一行插入
     *
     * @param lists
     * @param path
     */
    public static void generateXlsByLine(List<List<String>> lists, String path) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            //创建工作表1(Sheet)
            HSSFSheet sheet = workbook.createSheet("sheet");
            HSSFCellStyle cellStyle = workbook.createCellStyle();
            // 水平居中
            cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            // 处置居中
            cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            // 换行
            //cellStyle.setWrapText(true);
            setBorder(cellStyle);

            HSSFFont font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 10);
            cellStyle.setFont(font);

            for (int i = 0; i < lists.get(0).size(); i++) {
                sheet.setColumnWidth(i, 6000);
            }


            for (int j = 0; j < lists.size(); j++) {
                for (int i = 0; i < lists.get(j).size(); i++) {
                    HSSFCell cell = getRow(sheet, j).createCell(i);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(lists.get(j).get(i));
                }
            }
            FileOutputStream out = new FileOutputStream(path);
            //保存Excel文件
            workbook.write(out);
            //关闭文件流
            out.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

    /**
     * 获取行对象,不存在则创建
     *
     * @param sheet
     * @param line
     * @return
     */
    private static HSSFRow getRow(HSSFSheet sheet, int line) {
        return sheet.getRow(line) != null ? sheet.getRow(line) : sheet.createRow(line);
    }


    // 设置边框
    private static void setBorder(HSSFCellStyle cellStyle) {
        //下边框
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        //左边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        //上边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        //右边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
    }
}

依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.14</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>

END。