java 生成excel文件

1）pom文件

        <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.51</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.17</version>
        </dependency>

2）java文件

import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import javax.servlet.http.HttpServletResponse;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

@Controller
@RequestMapping("/public/excel")
public class ExcelController {

    private HSSFWorkbook workBook;

    @RequestMapping(value = { "/", "/index" })
    public String index() {
        return "index";
    }

    @RequestMapping(value = { "/create" })
    public void excelCreate(HttpServletResponse response, String fileName, String sheetList) {
        try {
            response.reset(); // 清除buffer缓存
            // 指定下载的文件名
            String time = new SimpleDateFormat("-yyyyMMddHHmmss").format(new Date());
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + time + ".xls");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            OutputStream os = response.getOutputStream();
            // 创建工作薄
            WritableWorkbook workbook = Workbook.createWorkbook(os);
            // 标题样式
            WritableFont titleFont = new WritableFont(WritableFont.TIMES, 22, WritableFont.BOLD);// 设置字体种类和黑体显示,字体为Arial,字号大小为10,采用黑体显示
            WritableCellFormat titleFormate = new WritableCellFormat(titleFont);// 生成一个单元格样式控制对象
            titleFormate.setAlignment(jxl.format.Alignment.CENTRE);// 单元格中的内容水平方向居中
            titleFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 单元格的内容垂直方向居中
            // 列头部样式
            WritableFont columnFont = new WritableFont(WritableFont.TIMES, 16, WritableFont.BOLD);// 选择字体
            columnFont.setColour(Colour.BLUE);
            WritableCellFormat columnFormat = new WritableCellFormat(columnFont);
            columnFormat.setAlignment(jxl.format.Alignment.CENTRE);// 单元格中的内容水平方向居中
            columnFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 单元格的内容垂直方向居中
            // 文本内容样式
            WritableFont bodyFont = new WritableFont(WritableFont.TIMES, 13);
            WritableCellFormat bodyFormate = new WritableCellFormat(bodyFont);
            bodyFormate.setAlignment(jxl.format.Alignment.CENTRE);// 单元格中的内容水平方向居中
            bodyFormate.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 单元格的内容垂直方向居中
            JSONArray parseArray = JSON.parseArray(sheetList);
            for (int i = 0; i < parseArray.size(); i++) {
                JSONObject jo = parseArray.getJSONObject(i);
                // 获取表名，标题，列的集合，数据集合
                String sheetName = jo.getString("sheetName");
                String titleName = jo.getString("titleName");
                JSONArray columnList = jo.getJSONArray("columnList");
                JSONArray rowList = jo.getJSONArray("rowList");
                // 创建新的一页
                WritableSheet sheet = workbook.createSheet(sheetName, i);
                // 构造标题
                sheet.mergeCells(0, 0, columnList.size() - 1, 0);// 添加合并单元格，第一个参数是起始列，第二个参数是起始行，第三个参数是终止列，第四个参数是终止行
                Label title = new Label(0, 0, titleName, titleFormate);
                sheet.setRowView(0, 800, false);// 设置第一行的高度
                sheet.addCell(title);
                // 构造列头部
                Label label = null;
                for (int j = 0; j < columnList.size(); j++) {
                    JSONObject column = columnList.getJSONObject(j);
                    label = new Label(column.getIntValue("index"), 1, column.getString("title"), columnFormat);
                    sheet.addCell(label);
                }

                // 构造文本内容
                for (int j = 0; j < rowList.size(); j++) {
                    JSONObject row = rowList.getJSONObject(j);
                    for (int k = 0; k < columnList.size(); k++) {
                        JSONObject column = columnList.getJSONObject(k);
                        if (column.getString("index").equals("0") && column.getString("title").equals("序号")) {
                            label = new Label(0, j + 2, (j + 1) + "", bodyFormate);
                        } else {
                            label = new Label(Integer.valueOf(column.getIntValue("index")), j + 2,
                                    row.getString(column.getString("field")), bodyFormate);
                        }
                        sheet.addCell(label);
                    }
                }
            }

            // 把创建的内容写入到输出流中，并关闭输出流
            workbook.write();
            workbook.close();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

    @RequestMapping(value = { "/excel/create/two" })
    public void excelCreateTwo(HttpServletResponse response, String fileName, String sheetList) {
        try {
            response.reset(); // 清除buffer缓存
            // 指定下载的文件名
            String time = new SimpleDateFormat("-yyyyMMddHHmmss").format(new Date());
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + new String(fileName.getBytes("gbk"), "iso8859-1") + time + ".xls");
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            response.setHeader("Pragma", "no-cache");
            response.setHeader("Cache-Control", "no-cache");
            response.setDateHeader("Expires", 0);
            workBook = new HSSFWorkbook();
            // 表头标题样式
            HSSFFont titleFont = workBook.createFont();
            titleFont.setFontName("宋体");
            titleFont.setBold(true);
            titleFont.setFontHeightInPoints((short) 22);// 字体大小
            HSSFCellStyle titleStyle = workBook.createCellStyle();
            titleStyle.setFont(titleFont);
            titleStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            titleStyle.setLocked(true);
            // 列头部样式
            HSSFFont columnFont = workBook.createFont();
            columnFont.setFontHeightInPoints((short) 18); // 字体高度
            columnFont.setColor(HSSFFont.COLOR_RED); // 字体颜色
            columnFont.setFontName("黑体"); // 字体
            HSSFCellStyle columnStyle = workBook.createCellStyle();
            columnStyle.setFont(columnFont);
            columnStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            columnStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            // 内容样式
            HSSFFont textFont = workBook.createFont();
            textFont.setFontName("宋体");
            textFont.setFontHeightInPoints((short) 14);// 字体大小
            HSSFCellStyle textStyle = workBook.createCellStyle();
            textStyle.setFont(textFont);
            textStyle.setAlignment(HorizontalAlignment.CENTER);// 左右居中
            textStyle.setVerticalAlignment(VerticalAlignment.CENTER);// 上下居中
            textStyle.setLocked(true);
            JSONArray parseArray = JSON.parseArray(sheetList);
            for (int i = 0; i < parseArray.size(); i++) {
                JSONObject jo = parseArray.getJSONObject(i);
                // 获取表名，标题，列的集合，数据集合
                String sheetName = jo.getString("sheetName");
                String titleName = jo.getString("titleName");
                JSONArray columnList = jo.getJSONArray("columnList");
                JSONArray rowList = jo.getJSONArray("rowList");
                // 创建工作簿
                HSSFSheet sheet = workBook.createSheet();
                // 设置工作簿的名字
                workBook.setSheetName(i, sheetName);
                // 行号，从零开始，每次自动+1
                int rowNomber = -1;
                // 第一行表头标题
                CellRangeAddress cra = new CellRangeAddress(0, 0, 0, columnList.size() - 1);
                sheet.addMergedRegion(cra);
                HSSFRow titleRow = sheet.createRow(++rowNomber);
                titleRow.setHeight((short) 0x349);
                HSSFCell titleCell = titleRow.createCell(0);
                titleCell.setCellStyle(titleStyle);
                titleCell.setCellValue(titleName);
                // 构造列头部
                HSSFRow columnRow = sheet.createRow(++rowNomber);
                for (int j = 0; j < columnList.size(); j++) {
                    JSONObject column = columnList.getJSONObject(j);
                    HSSFCell cell = columnRow.createCell(column.getShort("index"));
                    cell.setCellStyle(columnStyle);
                    cell.setCellValue(column.getString("title"));
                }

                // 构造文本内容
                for (int j = 0; j < rowList.size(); j++) {
                    JSONObject data = rowList.getJSONObject(j);
                    HSSFRow row = sheet.createRow(++rowNomber);
                    for (int k = 0; k < columnList.size(); k++) {
                        JSONObject column = columnList.getJSONObject(k);
                        HSSFCell cell = row.createCell(column.getShort("index"));
                        if (column.getString("index").equals("0") && column.getString("title").equals("序号")) {
                            cell.setCellValue((j + 1) + "");
                        } else {
                            cell.setCellValue(data.getString(column.getString("field")));
                        }
                        cell.setCellStyle(textStyle);
                    }
                }

                // 列宽自适应
                for (int o = 0; o < columnList.size(); o++) {
                    sheet.autoSizeColumn(o);
                }
            }

            OutputStream os = response.getOutputStream();
            workBook.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

3）html文件

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8"/>
    <title>生成Excel文件</title>
</head>
<body>
<h2>生成Excel文件</h2>
<div>
    文件名：<span id="fileName"></span>
</div>
<div>
    <textarea id="sheetList" style="height: 200px; width: 500px;"></textarea>
</div>
<div>
    <button onclick="submitForm()">下载文件</button>
</div>

<script type="text/javascript" src="../static/js/jquery-1.8.3.min.js"></script>
<script type="text/javascript">

    var fileName = "生成的文件名";
    var sheets = [];
    var sheetName = "学生信息表";
    var titleName = "学生基本信息";
    var columnList = [
        {field: 'name', title: '姓名', index: "0"},
        {field: 'sex', title: '性别', index: "1"},
        {field: 'age', title: '年龄', index: "2"},
        {field: 'height', title: '身高', index: "3"},
        {field: 'class', title: '班级', index: "4"}
    ];

    var rowList = [
        {name: '张三', sex: '男', age: "18", height: "170", class: "一班"},
        {name: '李明', sex: '男', age: "17", height: "168", class: "三班"},
        {name: '赵雨', sex: '女', age: "18", height: "166", class: "二班"},
        {name: '天天', sex: '男', age: "19", height: "171", class: "一班"},
        {name: '小雪', sex: '女', age: "18", height: "172", class: "三班"}
    ];

    var sheet = {
        "sheetName": sheetName,
        "titleName": titleName,
        "columnList": columnList,
        "rowList": rowList
    };

    sheets.push(sheet);

    var sheetName1 = "家庭信息表";
    var titleName1 = "学生家庭信息";
    var columnList1 = [
        {field: '', title: '序号', index: "0"},
        {field: 'sname', title: '学生姓名', index: "1"},
        {field: 'fname', title: '父亲姓名', index: "2"},
        {field: 'mname', title: '母亲姓名', index: "3"}
    ];

    var rowList1 = [
        {sname: '张三', fname: '张宇', mname: "李芳"},
        {sname: '李明', fname: '李提行', mname: "王雨"},
        {sname: '赵雨', fname: '赵天明', mname: "周飞"},
        {sname: '天天', fname: '天月', mname: "地明"},
        {sname: '孙雪', fname: '孙空', mname: "白晶"}
    ];

    var sheet1 = {
        "sheetName": sheetName1,
        "titleName": titleName1,
        "columnList": columnList1,
        "rowList": rowList1
    };

    sheets.push(sheet1);
    var sheetList = JSON.stringify(sheets);
    $("#fileName").html(fileName);
    $("#sheetList").html(sheetList);
    function submitForm() {
        var forme = document.createElement("form");
        document.body.appendChild(forme);
        forme.method = 'post';
        forme.action = "/excel/create/two";
        var fname = document.createElement("input");
        fname.setAttribute("name", "fileName");
        fname.setAttribute("type", "hidden");
        fname.setAttribute("value", fileName);
        forme.appendChild(fname);
        var slist = document.createElement("input");
        slist.setAttribute("name", "sheetList");
        slist.setAttribute("type", "hidden");
        slist.setAttribute("value", sheetList);
        forme.appendChild(slist);
        forme.submit();
    }
</script>
</body>
</html>

3）新的最简单的测试文件

package com.efangtec.common.utils;

import java.io.File;
import java.io.FileOutputStream;
import java.lang.management.ManagementFactory;
import java.lang.management.MemoryUsage;
import java.util.Date;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

// 库存报表导出帮助类
public class StockExportUtil {

    public static void main(String[] args) {
        String[] data = { "73982", "1", "NH", "1", "2018122510", "2", "0", "0", "0", "12233", "0", "楼层组件优化", "0" };

        Date startTime = new Date();
        MemoryUsage startMemory = ManagementFactory.getMemoryMXBean().getHeapMemoryUsage();

        // 创建工作薄
        XSSFWorkbook workbook = new XSSFWorkbook();
        // 创建工作表
        XSSFSheet sheet = workbook.createSheet("sheet1");

        // 设置数据
        for (int row = 0; row < 50; row++) {
            XSSFRow sheetRow = sheet.createRow(row);
            for (int column = 0; column < data.length; column++) {
                sheetRow.createCell(column).setCellValue(data[column]);
            }
        }

        // 写入文件
        try {
            workbook.write(new FileOutputStream(new File("/Users/jinguohui/Desktop/book/excel.xlsx")));
            workbook.close();
        } catch (Exception ex) {
            System.out.println(ex.getMessage());
        }
        Date endTime = new Date();
        MemoryUsage endMemory = ManagementFactory.getMemoryMXBean().getHeapMemoryUsage();
        System.out.println("Cost time(ms): " + (endTime.getTime() - startTime.getTime()));
        System.out.println("Cost memory(): " + (endMemory.getUsed() - startMemory.getUsed()));

        System.out.println("Hello World!");

    }
}