使用 jxl生成excel文件

下列案例包含基本功能

maven 使用依赖

       <dependency>
            <groupId>net.sourceforge.jexcelapi</groupId>
            <artifactId>jxl</artifactId>
            <version>2.6.12</version>
        </dependency>

效果图

package com.panxg.excel;

import jxl.Cell;
import jxl.CellView;
import jxl.Sheet;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.CellFormat;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.read.biff.BiffException;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class excelExport {

    public static void main(String[] args) throws Exception {
        //读取excel文件
        //readExcel();
        //写入excel文件
       writeExcel();
    }


    /**
     * 读取excel文件
     * @throws IOException
     * @throws BiffException
     */
    private static void readExcel() throws IOException, BiffException {
        //输入流。a.xls为要读取的excel文件名,不可为xlsx后缀
        Workbook book = Workbook.getWorkbook(new File("C:\\Users\\86187\\Desktop\\excel表1.xls"));
        //获取所有sheet页
        Sheet[] sheets = book.getSheets();
        for (int i = 0; i < sheets.length; i++) {
            System.err.println("第"+i+"个sheet:"+book.getSheet(i).getName());
            //获得第一个工作表对象(ecxel中sheet的编号从0开始,0,1,2,3,....)
            Sheet sheet = book.getSheet(i);
            //获取sheet页里面所有行
            int rows = sheet.getRows();
            for (int i1 = 0; i1 < rows; i1++) {
                System.out.print("第"+i1+"行:");
                //获取sheet页里面第一行的所有列
                Cell[] cells = sheet.getRow(i1);
                for (int i2 = 0; i2 < cells.length; i2++) {
                    //输出列内容
                    System.out.print(cells[i2].getContents()+" ");
                }
                System.out.println();
            }
        }
        book.close();
    }

    /**
     * 创建一个
     * @throws IOException
     */
    private static void writeExcel() throws Exception {
        //创建一个文件
        File file = createFile("C:\\Users\\86187\\Desktop\\excel表.xls");
        //创建一个输出流，读取文件
        OutputStream outputStream = new FileOutputStream(file);
        //创建一个工作簿
        WritableWorkbook writableWorkbook = Workbook.createWorkbook(outputStream);
        //创建第一个工作表  第一个参数为：工作表名称，第二个参数：第几个工作表，从0开始
        WritableSheet writableSheet = writableWorkbook.createSheet("第一个sheet页", 0);
        //写入第一个工作表数据
        writeWorksheet1(writableSheet);
        //创建第一个工作表  第一个参数为：工作表名称，第二个参数：第几个工作表，从0开始
        writableSheet = writableWorkbook.createSheet("第二个sheet页", 1);
        writeWorksheet2(writableSheet);
        //写入第二个工作表数据
         //writableWorkbook.removeSheet(1);//删除第二个sheet页
        //关闭流
        writableWorkbook.write();
        writableWorkbook.close();
        outputStream.close();
    }

    /**
     * 第一个sheet页
     * @param writableSheet 工作表
     */
    private static void writeWorksheet1(WritableSheet writableSheet) throws Exception {
        //获取单元格格式  居中 黑色16号字体
        WritableCellFormat wcf = getWritableCellFormatCentre(getWritableFontBoldTitle());
        writableSheet.addCell(getLabel(0,0,"学习计划表",wcf));  //添加标题
        writableSheet.mergeCells(0, 0, 9, 0); //合并单元格 列，行，向右，向下
        writableSheet.setRowView(0, 600); // 设置行的高度
        //获取单元格格式  左对齐，红色9号字体
        wcf = getWritableCellFormatLeft(getWritableFontBoldRedText());
        writableSheet.addCell(getLabel(0,1,"注：①可根据当天的学习完成情况针对性指定次日的学习计划；②每天的学习计划行数可自行定义；③E列、G列、I列自动，I列直接填入数字即可",wcf));
        writableSheet.mergeCells(0, 1, 9, 0); //合并单元格 列，行，向右，向下
        //获取单元格格式 居中，蓝色背景颜色，黑色9号字体
        wcf = getWritableCellFormatCentreBackage(getWritableFontBoldBlackText());
        //添加表头
        writableSheet.addCell(getLabel(0,2,"日期",wcf));
        writableSheet.setColumnView(0,20);//单元格宽度  第几列，宽度多少
        writableSheet.addCell(getLabel(1,2,"星期",wcf));
        writableSheet.setColumnView(1,15);//单元格宽度  第几列，宽度多少
        writableSheet.addCell(getLabel(2,2,"学习科目",wcf));
        writableSheet.setColumnView(2,15);//单元格宽度  第几列，宽度多少
        writableSheet.addCell(getLabel(3,2,"计划时间",wcf));
        writableSheet.mergeCells(3, 2, 5, 0); //合并单元格 列，行，向右，向下
        writableSheet.addCell(getLabel(6,2,"时长(H)",wcf));
        writableSheet.addCell(getLabel(7,2,"计划学习内容",wcf));
        writableSheet.setColumnView(7,20);//单元格宽度  第几列，宽度多少
        writableSheet.addCell(getLabel(8,2,"完成情况（满分100分）",wcf));
        writableSheet.addCell(getLabel(9,2,"备注",wcf));
        //获取单元格格式 居中，黑色9号字体
        wcf = getWritableCellFormatCentre(getWritableFontBoldBlackText());
        //添加数据
        writableSheet.addCell(getLabel(0,3,"2020年02月08日",wcf));
        writableSheet.mergeCells(0, 3, 0, 8); //合并单元格 列，行，向右，向下
        writableSheet.addCell(getLabel(1,3,"星期六",wcf));
        writableSheet.mergeCells(1, 3, 0, 8); //合并单元格 列，行，向右，向下
        writableSheet.addCell(getLabel(2,3,"英语",wcf));
        writableSheet.addCell(getLabel(3,3,"09::00",wcf));
        writableSheet.addCell(getLabel(4,3,"-",wcf));
        writableSheet.addCell(getLabel(5,3,"10:00",wcf));
        writableSheet.addCell(getLabel(6,3,"1.00",wcf));
        writableSheet.addCell(getLabel(7,3,"单词、背诵",wcf));
        writableSheet.addCell(getLabel(8,3,"90分",wcf));
        writableSheet.addCell(getLabel(9,3,"",wcf));
        writableSheet.addCell(getLabel(2,4,"数学",wcf));
        writableSheet.addCell(getLabel(3,4,"09::00",wcf));
        writableSheet.addCell(getLabel(4,4,"-",wcf));
        writableSheet.addCell(getLabel(5,4,"10:00",wcf));
        writableSheet.addCell(getLabel(6,4,"1.00",wcf));
        writableSheet.addCell(getLabel(7,4,"单词、背诵",wcf));
        writableSheet.addCell(getLabel(8,4,"90分",wcf));
        writableSheet.addCell(getLabel(9,4,"",wcf));
        writableSheet.addCell(getLabel(2,5,"语文",wcf));
        writableSheet.addCell(getLabel(3,5,"09::00",wcf));
        writableSheet.addCell(getLabel(4,5,"-",wcf));
        writableSheet.addCell(getLabel(5,5,"10:00",wcf));
        writableSheet.addCell(getLabel(6,5,"1.00",wcf));
        writableSheet.addCell(getLabel(7,5,"单词、背诵",wcf));
        writableSheet.addCell(getLabel(8,5,"90分",wcf));
        writableSheet.addCell(getLabel(9,5,"",wcf));
        writableSheet.addCell(getLabel(2,6,"语文",wcf));
        writableSheet.addCell(getLabel(3,6,"09::00",wcf));
        writableSheet.addCell(getLabel(4,6,"-",wcf));
        writableSheet.addCell(getLabel(5,6,"10:00",wcf));
        writableSheet.addCell(getLabel(6,6,"1.00",wcf));
        writableSheet.addCell(getLabel(7,6,"单词、背诵",wcf));
        writableSheet.addCell(getLabel(8,6,"90分",wcf));
        writableSheet.addCell(getLabel(9,6,"",wcf));
        writableSheet.addCell(getLabel(2,7,"钢琴",wcf));
        writableSheet.addCell(getLabel(3,7,"09::00",wcf));
        writableSheet.addCell(getLabel(4,7,"-",wcf));
        writableSheet.addCell(getLabel(5,7,"10:00",wcf));
        writableSheet.addCell(getLabel(6,7,"1.00",wcf));
        writableSheet.addCell(getLabel(7,7,"单词、背诵",wcf));
        writableSheet.addCell(getLabel(8,7,"90分",wcf));
        writableSheet.addCell(getLabel(9,7,"",wcf));
        writableSheet.addCell(getLabel(2,8,"英语",wcf));
        writableSheet.addCell(getLabel(3,8,"09::00",wcf));
        writableSheet.addCell(getLabel(4,8,"-",wcf));
        writableSheet.addCell(getLabel(5,8,"10:00",wcf));
        writableSheet.addCell(getLabel(6,8,"1.00",wcf));
        writableSheet.addCell(getLabel(7,8,"单词、背诵",wcf));
        writableSheet.addCell(getLabel(8,8,"90分",wcf));
        writableSheet.addCell(getLabel(9,8,"",wcf));
        //添加图片
        insertImage(writableSheet);
    }


    /**
     * 添加图片
     */
    private static void insertImage(WritableSheet writableSheet) throws Exception {
        try {
            CellView cv = new CellView();
            cv.setAutosize(false);  //自动宽度关闭
            cv.setSize(270); //设置多款
            writableSheet.setRowView(10, cv);
            writableSheet.setRowView(11, cv);
            writableSheet.setRowView(12, cv);
            writableSheet.setRowView(13, cv);
            writableSheet.setRowView(14, cv);
            writableSheet.setRowView(15, cv);
            //图片就支持png格式。。。。
            File fileImage = new File(excelExport.class.getResource("/1.png").getPath());
            // x 表示列 y表示 行 width列占用几个单元格  height高占用多少个单元格
            WritableImage image = new WritableImage(5, 10, 2, 6, fileImage);//从A1开始 跨2行3个单元格
            writableSheet.addImage(image);//writableSheet 添加 图片
        } catch (Exception e) {
            throw new Exception("插入图片失败");
        }
    }


    /**
     * 获取Label
     *
     * @param col 列
     * @param row 行
     * @param cont 内容
     * @param wcf 格式
     * @return Label
     */
    private static Label getLabel(Integer col, Integer row, String cont, CellFormat wcf) {
        return new Label(col, row, cont, wcf);
    }

    /**
     * 字体格式
     * 字体，字号，粗体，斜体，下划线，字体颜色
     * 加粗 9号 黑色
     * @return WritableFont
     */
    private static WritableFont getWritableFontBoldBlackText() {
        return new WritableFont(WritableFont.createFont("宋体"), 9, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK);
    }

    /**
     * 字体格式
     * 字体，字号，粗体，斜体，下划线，字体颜色
     * 加粗 9号 红色
     *
     * @return WritableFont
     */
    private static WritableFont getWritableFontBoldRedText() {
        return new WritableFont(WritableFont.createFont("宋体"), 9, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.RED);
    }

    /**
     * 字体格式
     * 字体，字号，粗体，斜体，下划线，字体颜色
     * 加粗 16号 黑色
     *
     * @return WritableFont
     */
    private static WritableFont getWritableFontBoldTitle() {

        WritableFont wf = new WritableFont(WritableFont.createFont("宋体"), 16, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
        return wf;
    }

    /**
     * 单元格格式
     * 水平/垂直对齐 添加边框
     *
     * @return WritableCellFormat
     */
    private static WritableCellFormat getWritableCellFormatCentre(WritableFont wf) throws WriteException {
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setAlignment(jxl.format.Alignment.CENTRE);// 水平对齐
        wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //垂直对齐
        wcf.setBorder(Border.ALL, BorderLineStyle.THIN); //设置边框;
        wcf.setWrap(false);//自动换行关闭
        return wcf;
    }

    /**
     * 单元格格式
     * 水平/垂直对齐 海洋蓝背景颜色 添加边框
     *
     * @return WritableCellFormat
     */
    private static WritableCellFormat getWritableCellFormatCentreBackage(WritableFont wf) throws WriteException {
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setAlignment(jxl.format.Alignment.CENTRE);// 水平对齐
        wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); //垂直对齐
        wcf.setBackground(jxl.write.Colour.ICE_BLUE);// 设置单元格的背景颜色
        wcf.setBorder(Border.ALL, BorderLineStyle.THIN); //设置边框;
        wcf.setWrap(false);//自动换行关闭
        return wcf;
    }

    /**
     * 单元格格式
     * 左对齐 添加边框
     *
     * @return WritableCellFormat
     */
    private static WritableCellFormat getWritableCellFormatLeft(WritableFont wf) throws WriteException {
        WritableCellFormat wcf = new WritableCellFormat(wf);
        wcf.setAlignment(Alignment.LEFT);// 左对齐
        wcf.setBorder(Border.ALL, BorderLineStyle.THIN); //设置边框;
        wcf.setWrap(false);//自动换行关闭
        return wcf;
    }

    /**
     * 第二个sheet页
     * @param writableSheet 工作表
     */
    private static void writeWorksheet2(WritableSheet writableSheet) {

    }

    /**
     * 创建文件
     *
     * @param path 路径
     * @return File
     * @throws IOException 异常
     */
    private static File createFile(String path) throws IOException {
        File file = new File(path);
        //判断目录是否存在/不存在就创建
        if (!file.getParentFile().exists())
            file.getParentFile().mkdirs();
        //判断文件是否存在/存在就删除
        if (file.exists()) file.delete();
        //创建文件
        if (!file.createNewFile()) {
            throw new IOException("创建文件[" + path + "]失败!");
        }
        return file;
    }
}