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

• 单个Excel读取/生成
• 生成多个sheet, 自动列宽, 宋体, 标题加粗, 所有框线

maven

        <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>

代码

package com.alibaba.gts.web.util.utils;

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

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


public class ExcelUtil {

    /**
     * 读取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();
        }

    }

    /**
     * 一行一行插入
     * 多个sheet
     * 宋体,标题加粗灰色,边框,列宽自动调整
     * sheetNameContents k:sheet名称,k:内容
     *
     * @param sheetNameContents
     * @param path
     */
    public static void generateXlsByLines(Map<String, List<List<String>>> sheetNameContents, String path) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            handleByLine(sheetNameContents, workbook);
            FileOutputStream out = new FileOutputStream(path);
            //保存Excel文件
            workbook.write(out);
            //关闭文件流
            out.close();
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    private static void handleByLine(Map<String, List<List<String>>> sheetNameContents, HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = getCellStyle(workbook);
        HSSFCellStyle titleCellStyle = getTitleCellStyle(workbook);
        sheetNameContents.forEach((k, v) -> {
            if (k == null) {
                return;
            }
            HSSFSheet sheet = workbook.createSheet(k);
            List<Integer> widths = new LinkedList<>();
            List<String> title = v.get(0);
            for (int i = 0; i < title.size(); i++) {
                int max = 10;
                for (int j = 0; j < v.size(); j++) {
                    if (v.get(j) == null) {
                        continue;
                    }
                    if (v.get(j).get(i) == null) {
                        continue;
                    }
                    int length = v.get(j).get(i).getBytes().length;
                    if (length > 10) {
                        max = length;
                    }
                }
                if (max > 100) {
                    max = 100;
                }
                widths.add(max);
            }
            for (int i = 0; i < title.size(); i++) {
                sheet.setColumnWidth(i, (int) (widths.get(i) * 256 * 1.2));
            }
            for (int j = 0; j < v.size(); j++) {
                for (int i = 0; i < v.get(j).size(); i++) {
                    HSSFCell cell = getRow(sheet, j).createCell(i);
                    if (j == 0) {
                        cell.setCellStyle(titleCellStyle);
                    } else {
                        cell.setCellStyle(cellStyle);
                    }
                    cell.setCellValue(v.get(j).get(i));
                }
            }
        });
    }

    private static HSSFCellStyle getCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 处置居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 换行
        //cellStyle.setWrapText(true);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);

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

    private static HSSFCellStyle getTitleCellStyle(HSSFWorkbook workbook) {
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 水平居中
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        // 处置居中
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        // 换行
        //cellStyle.setWrapText(true);
        //下边框
        cellStyle.setBorderBottom(BorderStyle.THIN);
        //左边框
        cellStyle.setBorderLeft(BorderStyle.THIN);
        //上边框
        cellStyle.setBorderTop(BorderStyle.THIN);
        //右边框
        cellStyle.setBorderRight(BorderStyle.THIN);

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

        // 背景色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return cellStyle;
    }


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


    /**
     * 一行一行插入, 直接相应HTTP
     *
     * @param lists
     * @param outputStream
     */
    public static void generateXlsByLineResponse(List<List<String>> lists, OutputStream outputStream) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            Map<String, List<List<String>>> sheetNameContents = new HashMap<>();
            sheetNameContents.put("sheet", lists);
            handleByLine(sheetNameContents, workbook);
            workbook.write(outputStream);
            outputStream.close();
            // 关闭工作簿
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        } 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();
            Map<String, List<List<String>>> sheetNameContents = new HashMap<>();
            sheetNameContents.put("sheet", lists);
            handleByLine(sheetNameContents, workbook);
            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 generateXlsByLineNom(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, j).createCell(i).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);
    }

    public static void main(String[] args) {
        List<List<String>> lists = new LinkedList<>();
        List<String> list1 = new LinkedList<>();
        list1.add("1");
        list1.add("1");
        list1.add("1");
        lists.add(list1);
        List<String> list2 = new LinkedList<>();
        list2.add("2");
        list2.add("2");
        list2.add("2");
        list2.add("2");
        list2.add("2");
        list2.add("2");
        list2.add("2");
        lists.add(list2);
        ExcelUtil2.generateXlsByLine(lists, "/Users/leyili/Desktop/file_test/gxls2.xls");
    }


}

生成之后直接相应HTTP

    @RequestMapping("/exportXls")
    public void exportXls(HttpServletResponse response) throws Exception {
        // 设置content—type
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset:utf-8");
        // 设置标题
        String fileName = URLEncoder.encode("test"+ "_" + System.currentTimeMillis(), "UTF-8");
        //Content-disposition是MIME协议的扩展，MIME协议指示MIME用户代理如何显示附加的文件。
        response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
        ServletOutputStream outputStream = response.getOutputStream();

        List<List<String>> cs = new LinkedList<>();
        // 添加内容
        ExcelUtil.generateXlsByLineResponse(cs, outputStream);
    }