EXCEL java 上传生成 java如何生成excel表

转载

kekenai 2023-06-14 19:57:35

文章标签 EXCEL java 上传生成 excel java jar包文件名 文章分类 Java 后端开发

Java后台生成Excel表格

功能简述

需求
目标
实现

1.首先在项目中导入jxl相关jar包，并引入
2.编写代码
3.结果
后记

功能简述

由于公司业务需求，我需要实现一个完全通过Java后台导出一个特定的Excel表格功能，为此查阅了相关资料和浏览文章。最终选定使用jxl来实现此功能。

需求

EXCEL java 上传生成 java如何生成excel表_EXCEL java 上传生成

图片内黑色字体部分是写死的，黄色部分是需要数据能自由填充的。

目标

由于保密这里只实现一个demo，直接将生成的Excel文档放到指定的文件夹中。有需要的朋友，可以参考代码实现相关功能。

实现

1.首先在项目中导入jxl相关jar包，并引入

EXCEL java 上传生成 java如何生成excel表_文件名_02

需要jar包的自行在网上搜索，或者联系我vx：yangyun1719

2.编写代码

这里仅实现了一个demo，供需要的朋友参考。由于Excel相关的东西太过麻烦。需要自定义特定表格的时候，尤为麻烦。需要首先理清思路，哪里需要合并单元格，哪里需要设置单元格样式，都需要特别小心。具体实现请参考我的代码，和网上其他文字。或者去找jxl官方文档。

import jxl.CellView;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Colour;
import jxl.format.VerticalAlignment;
import jxl.write.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStream;

/**
 * 导出Excel类
 *
 * @date 2021/6/24 14:20
 */
@SuppressWarnings("ALL")
public class testExportExcel {
    /**
     * Excel文件输出路径
     */
    private static String filePath = "C:\\Users\\yangyun.LAPTOP-32A3VL6P\\Desktop\\Excel文件测试";
    /**
     * Excel文件名
     */
    private static String fileName = "测试导出Excel文件.xlsx";

    public static void main(String[] args) throws Exception {
        exportExcel(filePath, System.currentTimeMillis() + fileName);
    }

    /**
     * 导出Excel
     *
     * @param filePath 文件路径
     * @param fileName 文件名
     * @return void
     * @date 2021/6/24 14:24
     * @author yangyun
     */
    private static void exportExcel(String filePath, String fileName) throws Exception {
        File file = new File(filePath, fileName);
        OutputStream outputStream = new FileOutputStream(file);

        WritableSheet sheet = null;
        WritableWorkbook workbook = Workbook.createWorkbook(outputStream);

        CellView cellView = new CellView();
        cellView.setAutosize(true);

        // 字体样式
        WritableFont font = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD);
        WritableCellFormat wcf = new WritableCellFormat(font);
        wcf.setBackground(Colour.GRAY_25);
        wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcf.setAlignment(Alignment.CENTRE);
        wcf.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN, jxl.format.Colour.GRAY_50);

        // 加边框及居中对齐-内容（常规、居左）
        WritableFont fontBorder = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.NO_BOLD);
        WritableCellFormat wcfBorder = new WritableCellFormat(fontBorder);
        wcfBorder.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcfBorder.setAlignment(Alignment.LEFT);
        wcfBorder.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM, jxl.format.Colour.GRAY_50);

        // 加边框及居中对齐-表头（加粗、居中）
        WritableFont fontBorderBt = new WritableFont(WritableFont.createFont("宋体"), 12, WritableFont.BOLD);
        WritableCellFormat wcfBorderBt = new WritableCellFormat(fontBorderBt);
        wcfBorderBt.setVerticalAlignment(VerticalAlignment.CENTRE);
        wcfBorderBt.setAlignment(Alignment.CENTRE);
        wcfBorderBt.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.MEDIUM, jxl.format.Colour.GRAY_50);

        if (workbook != null) {
            sheet = workbook.createSheet("sheet1", 0);
            // 设置每列宽度
            sheet.setColumnView(0, 30);
            sheet.setColumnView(1, 10);
            sheet.setColumnView(2, 20);
            sheet.setColumnView(3, 20);
            sheet.setColumnView(4, 30);
            sheet.setColumnView(5, 10);
            sheet.setColumnView(6, 20);
            sheet.setColumnView(7, 20);
            // 合并单元格
            // 前两个参数 起点列、行，列和行均从左上角从0开始计数
            // 后两个参数 重点列、行
            sheet.mergeCells(0, 0, 7, 2);
            // 添加数据参数分别为：列、行、数据、格式设置
            sheet.addCell(new Label(0, 0, "资产负债表", wcf));
            sheet.addCell(new Label(0, 3, "公司名称：xxx"));
            // 表头
            sheet.addCell(new Label(0, 4, "项目", wcfBorderBt));
            sheet.addCell(new Label(1, 4, "附注", wcfBorderBt));
            sheet.addCell(new Label(2, 4, "期末余额", wcfBorderBt));
            sheet.addCell(new Label(3, 4, "期初余额", wcfBorderBt));
            sheet.addCell(new Label(4, 4, "项目", wcfBorderBt));
            sheet.addCell(new Label(5, 4, "附注", wcfBorderBt));
            sheet.addCell(new Label(6, 4, "期末余额", wcfBorderBt));
            sheet.addCell(new Label(7, 4, "期初余额", wcfBorderBt));
            // 第1行
            sheet.addCell(new Label(0, 5, "油气资产", wcfBorder));
            sheet.addCell(new Label(1, 5, "", wcfBorder));
            sheet.addCell(new Label(2, 5, "", wcfBorder));
            sheet.addCell(new Label(3, 5, "", wcfBorder));
            sheet.addCell(new Label(4, 5, "非流动负债合计", wcfBorder));
            sheet.addCell(new Label(5, 5, "", wcfBorder));
            sheet.addCell(new Label(6, 5, "", wcfBorder));
            sheet.addCell(new Label(7, 5, "", wcfBorder));
            // 第2行
            sheet.addCell(new Label(0, 6, "无形资产", wcfBorder));
            sheet.addCell(new Label(1, 6, "", wcfBorder));
            sheet.addCell(new Label(2, 6, "", wcfBorder));
            sheet.addCell(new Label(3, 6, "", wcfBorder));
            sheet.addCell(new Label(4, 6, "负债合计", wcfBorder));
            sheet.addCell(new Label(5, 6, "", wcfBorder));
            sheet.addCell(new Label(6, 6, "", wcfBorder));
            sheet.addCell(new Label(7, 6, "", wcfBorder));
            // 第3行
            sheet.addCell(new Label(0, 7, "开发支出", wcfBorder));
            sheet.addCell(new Label(1, 7, "", wcfBorder));
            sheet.addCell(new Label(2, 7, "", wcfBorder));
            sheet.addCell(new Label(3, 7, "", wcfBorder));
            sheet.addCell(new Label(4, 7, "所有者权益（或股东权益）：", wcfBorder));
            sheet.addCell(new Label(5, 7, "", wcfBorder));
            sheet.addCell(new Label(6, 7, "", wcfBorder));
            sheet.addCell(new Label(7, 7, "", wcfBorder));
            // 第4行
            sheet.addCell(new Label(0, 8, "商誉", wcfBorder));
            sheet.addCell(new Label(1, 8, "", wcfBorder));
            sheet.addCell(new Label(2, 8, "", wcfBorder));
            sheet.addCell(new Label(3, 8, "", wcfBorder));
            sheet.addCell(new Label(4, 8, "   实收资本（或股本）", wcfBorder));
            sheet.addCell(new Label(5, 8, "", wcfBorder));
            sheet.addCell(new Label(6, 8, "", wcfBorder));
            sheet.addCell(new Label(7, 8, "", wcfBorder));
            // 第5行
            sheet.addCell(new Label(0, 9, "长期待摊费用", wcfBorder));
            sheet.addCell(new Label(1, 9, "", wcfBorder));
            sheet.addCell(new Label(2, 9, "", wcfBorder));
            sheet.addCell(new Label(3, 9, "", wcfBorder));
            sheet.addCell(new Label(4, 9, "   其他权益工具", wcfBorder));
            sheet.addCell(new Label(5, 9, "", wcfBorder));
            sheet.addCell(new Label(6, 9, "", wcfBorder));
            sheet.addCell(new Label(7, 9, "", wcfBorder));
            // 第6行
            sheet.addCell(new Label(0, 10, "递延所得税资产", wcfBorder));
            sheet.addCell(new Label(1, 10, "", wcfBorder));
            sheet.addCell(new Label(2, 10, "", wcfBorder));
            sheet.addCell(new Label(3, 10, "", wcfBorder));
            sheet.addCell(new Label(4, 10, "      其中：优先股", wcfBorder));
            sheet.addCell(new Label(5, 10, "", wcfBorder));
            sheet.addCell(new Label(6, 10, "", wcfBorder));
            sheet.addCell(new Label(7, 10, "", wcfBorder));
            // 第7行
            sheet.addCell(new Label(0, 11, "其他非流动资产", wcfBorder));
            sheet.addCell(new Label(1, 11, "", wcfBorder));
            sheet.addCell(new Label(2, 11, "", wcfBorder));
            sheet.addCell(new Label(3, 11, "", wcfBorder));
            sheet.addCell(new Label(4, 11, "          永续股", wcfBorder));
            sheet.addCell(new Label(5, 11, "", wcfBorder));
            sheet.addCell(new Label(6, 11, "", wcfBorder));
            sheet.addCell(new Label(7, 11, "", wcfBorder));
            // 第8行
            sheet.addCell(new Label(0, 12, "", wcfBorder));
            sheet.addCell(new Label(1, 12, "", wcfBorder));
            sheet.addCell(new Label(2, 12, "", wcfBorder));
            sheet.addCell(new Label(3, 12, "", wcfBorder));
            sheet.addCell(new Label(4, 12, "资本公积", wcfBorder));
            sheet.addCell(new Label(5, 12, "", wcfBorder));
            sheet.addCell(new Label(6, 12, "", wcfBorder));
            sheet.addCell(new Label(7, 12, "", wcfBorder));
            // 第9行
            sheet.addCell(new Label(0, 13, "", wcfBorder));
            sheet.addCell(new Label(1, 13, "", wcfBorder));
            sheet.addCell(new Label(2, 13, "", wcfBorder));
            sheet.addCell(new Label(3, 13, "", wcfBorder));
            sheet.addCell(new Label(4, 13, "减，库存股", wcfBorder));
            sheet.addCell(new Label(5, 13, "", wcfBorder));
            sheet.addCell(new Label(6, 13, "", wcfBorder));
            sheet.addCell(new Label(7, 13, "", wcfBorder));
            // 第10行
            sheet.addCell(new Label(0, 14, "", wcfBorder));
            sheet.addCell(new Label(1, 14, "", wcfBorder));
            sheet.addCell(new Label(2, 14, "", wcfBorder));
            sheet.addCell(new Label(3, 14, "", wcfBorder));
            sheet.addCell(new Label(4, 14, "其他综合收益", wcfBorder));
            sheet.addCell(new Label(5, 14, "", wcfBorder));
            sheet.addCell(new Label(6, 14, "", wcfBorder));
            sheet.addCell(new Label(7, 14, "", wcfBorder));
            // 第11行
            sheet.addCell(new Label(0, 15, "", wcfBorder));
            sheet.addCell(new Label(1, 15, "", wcfBorder));
            sheet.addCell(new Label(2, 15, "", wcfBorder));
            sheet.addCell(new Label(3, 15, "", wcfBorder));
            sheet.addCell(new Label(4, 15, "盈余公积", wcfBorder));
            sheet.addCell(new Label(5, 15, "", wcfBorder));
            sheet.addCell(new Label(6, 15, "", wcfBorder));
            sheet.addCell(new Label(7, 15, "", wcfBorder));
            // 第12行
            sheet.addCell(new Label(0, 16, "", wcfBorder));
            sheet.addCell(new Label(1, 16, "", wcfBorder));
            sheet.addCell(new Label(2, 16, "", wcfBorder));
            sheet.addCell(new Label(3, 16, "", wcfBorder));
            sheet.addCell(new Label(4, 16, "未分配利润", wcfBorder));
            sheet.addCell(new Label(5, 16, "", wcfBorder));
            sheet.addCell(new Label(6, 16, "", wcfBorder));
            sheet.addCell(new Label(7, 16, "", wcfBorder));
            // 第13行
            sheet.addCell(new Label(0, 17, "非流动资产合计", wcfBorderBt));
            sheet.addCell(new Label(1, 17, "", wcfBorder));
            sheet.addCell(new Label(2, 17, "", wcfBorder));
            sheet.addCell(new Label(3, 17, "", wcfBorder));
            sheet.addCell(new Label(4, 17, "所有权益合计", wcfBorderBt));
            sheet.addCell(new Label(5, 17, "", wcfBorder));
            sheet.addCell(new Label(6, 17, "", wcfBorder));
            sheet.addCell(new Label(7, 17, "", wcfBorder));
            // 第14行
            sheet.addCell(new Label(0, 18, "资产总计", wcfBorderBt));
            sheet.addCell(new Label(1, 18, "", wcfBorder));
            sheet.addCell(new Label(2, 18, "", wcfBorder));
            sheet.addCell(new Label(3, 18, "", wcfBorder));
            sheet.addCell(new Label(4, 18, "负载和所有权益总计", wcfBorderBt));
            sheet.addCell(new Label(5, 18, "", wcfBorder));
            sheet.addCell(new Label(6, 18, "", wcfBorder));
            sheet.addCell(new Label(7, 18, "", wcfBorder));
            // 最后一行
            sheet.addCell(new Label(0, 18, "法定代表人："));
            sheet.addCell(new Label(1, 18, ""));
            sheet.addCell(new Label(2, 18, ""));
            sheet.addCell(new Label(3, 18, "主管会计工作负责人："));
            sheet.addCell(new Label(4, 18, ""));
            sheet.addCell(new Label(5, 18, ""));
            sheet.addCell(new Label(6, 18, "会计机构负责人："));
            sheet.addCell(new Label(7, 18, ""));

        }


        workbook.write();
        workbook.close();

    }

}

3.结果

EXCEL java 上传生成 java如何生成excel表_excel_03

EXCEL java 上传生成 java如何生成excel表_excel_04

后记

编写业务代码，是进公司以后必然面临的事情。有的人只研究技术部面向业务，有的人只钻研业务不懂技术。殊不知业务驱动技术，技术实现业务。无论你用的什么技术，对客户来说，最终实现了客户需求才是最好的技术。做我们这行的，很累，加班是常有之事，有时候会很迷茫，这也正常。新的技术不断更新迭代，心态包容，能接收新鲜事物会让我们更有动力。另外建议初入行的朋友培养点业余爱好，每天跟电脑打交道，难免会让我们在社交方面有所缺，尤其是夜深人静，如何自处是当代程序员面临的共同难题。如果有个爱好陪着自己度过无聊岁月，未尝不是一种幸运。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。