excel Java 导出

今天写一个工具类，Excel导出，严格的说，并不是我写的，拾人牙慧罢了，首先是一个整体的java代码

package com.core.util;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * excel工具类
 */
public class ExcelUtil {

    //创建excel文档 dataList 是准备导出的数据 用list封装
    public static HSSFWorkbook makeExcelFile(List<List<String>> dataList) {
        // 创建workbook
        HSSFWorkbook workbook = new HSSFWorkbook();
        //添加样式
        HSSFCellStyle style = workbook.createCellStyle();
        //背景色
        style.setFillForegroundColor((short)10);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        //设置边框
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        //居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置自动换行
        style.setWrapText(true);
        //设置字体样式
        Font font = workbook.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short)11);
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(font);
        // 添加Worksheet
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        // 创建相关内容
        for (int i = 0; i < dataList.size(); i++) {
            Row row = sheet.createRow(i);
            List<String> subList = dataList.get(i);
            for (int j = 0; j < subList.size(); j++) {
                Cell createCell = row.createCell(j);
                createCell.setCellValue(subList.get(j));
                createCell.setCellStyle(style);
                sheet.autoSizeColumn(i);
            }
        }
        return workbook;
    }

    /**
     * 把生成的Excel流输出出去
     * @param workbook
     * @param response
     */
    public static void exportExcel(HSSFWorkbook workbook,HttpServletResponse response){
        String path = ExcelUtil.class.getResource("/").getPath();
        File filePath = new File(path.split("/classes")[0] + "/exportTemp");
        if (!filePath.exists() && !filePath.isDirectory()){
            boolean mkdir = filePath.mkdir();
        }
        Format format = new SimpleDateFormat("yyMMddHHmmss");
        String filenameTemp = path.split("/classes")[0] + "/exportTemp/"+"export" + "_" + format.format(new Date()) + ".xls";
        File filename = new File(filenameTemp);
        if (!filename.exists()) {
            try {
                boolean newFile = filename.createNewFile();
                if (newFile){
                    FileOutputStream fos = new FileOutputStream(filenameTemp);
                    workbook.write(fos);
                    fos.close();
                }
                //下载
                downFile(response, filenameTemp);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 下载文件
     * @param response
     * @param filePath 文件路径
     * @author clj
     * @date 2018/3/31
     */
    public static void downFile(HttpServletResponse response, String filePath) {
        String name = filePath.split("/")[filePath.split("/").length-1];
        //读取要下载的文件，保存到文件输入流
        try {
            // 设置头部信息
            response.setHeader(
                    "Content-disposition",
                    "attachment;filename="
                            + URLEncoder.encode(name, "UTF-8"));
            FileInputStream in = new FileInputStream(filePath);
            //创建输出流
            OutputStream out = response.getOutputStream();
            //创建缓冲区
            byte buffer[] = new byte[1024];
            int len = 0;
            //循环将输入流中的内容读取到缓冲区当中
            while((len=in.read(buffer))>0){
                //输出缓冲区的内容到浏览器，实现文件下载
                out.write(buffer, 0, len);
            }
            //关闭文件输入流
            in.close();
            //关闭输出流
            out.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

下面是导包

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

这里面主要就是一些excel有关的poi

HSSFWorkbook 是excel文件

HSSFSheet  excel表格，通过HSSFWorkbook 获取

HSSFCellStyle  excel样式，通过HSSFWorkbook 获取，可加入Font 字体样式

Font 字体样式，通过HSSFWorkbook 获取

HSSFRow，Row  表格页的行，通过HSSFSheet  获取

HSSFCell，Cell  每一格，存入数据，通过HSSFRow，Row  获取，可加入HSSFCellStyle 字体样式