java纯后台实现Excel导出

2023-01-01 00:00:00 java 导出 后台

通过jxl.jar实现文件导出功能,项目需要引入相应的jar包。

样例中用到了设置行高、列宽、水平和垂直对齐方式、边框样式、合并单元格、设置字体大小、加粗等操作,复制代码后右键run as java application即可查看导出效果。

import java.io.File;
import java.io.IOException;
import java.lang.Boolean;
import java.util.List;

import jxl.*;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import jxl.write.biff.RowsExceededException;

public class ExportExcel {

	public static void main(String[] args) throws WriteException {
		try {
			writeExcel("D:\\ce1.xls");
		} catch (RowsExceededException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	/**
	 * 导出报表
	 * 
	 * @param currentPage
	 * @param loan
	 * @param model
	 * @return
	 * @throws WriteException
	 */

	public static void writeExcel(String fileName) throws WriteException {
		WritableWorkbook wwb = null;
		try {
			// 首先要使用Workbook类的工厂方法创建一个可写入的工作薄(Workbook)对象
			wwb = Workbook.createWorkbook(new File(fileName));
		} catch (IOException e) {
			e.printStackTrace();
		}
		if (wwb != null) {
			// 创建一个可写入的工作表
			// Workbook的createSheet方法有两个参数,第一个是工作表的名称,第二个是工作表在工作薄中的位置
			WritableSheet ws = wwb.createSheet("sheet1", 0);

			// 下面开始添加单元格
			WritableFont wf = new WritableFont(WritableFont.TIMES, 18, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf = new WritableCellFormat(wf);
			cf.setAlignment(Alignment.CENTRE);
			cf.setBorder(Border.ALL, BorderLineStyle.NONE);

			ws.addCell(new Label(0, 0, String.valueOf("受理系统内执法样本检测联系单"), cf));
			ws.mergeCells(0, 0, 12, 0);
			ws.setRowView(0, 1200, false);// 设置第一行的高度

			WritableFont wf1 = new WritableFont(WritableFont.TIMES, 12, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf1 = new WritableCellFormat(wf1);
			cf1.setAlignment(Alignment.CENTRE);
			cf1.setBorder(Border.ALL, BorderLineStyle.NONE);
			ws.addCell(new Label(0, 1, String.valueOf("送检单位(盖章)"), cf1));
			ws.mergeCells(0, 1, 6, 1);
			ws.setRowView(1, 500, false);// 设置第2行的高度
			ws.setColumnView(0, 5);
			ws.setColumnView(1, 5);
			ws.setColumnView(2, 8);
			ws.setColumnView(3, 8);
			ws.setColumnView(4, 8);
			ws.setColumnView(5, 5);
			ws.setColumnView(6, 8);
			ws.setColumnView(7, 5);
			ws.setColumnView(8, 7);
			ws.setColumnView(9, 5);
			ws.setColumnView(10, 8);
			ws.setColumnView(11, 8);
			ws.setColumnView(12, 8);
			WritableCellFormat cf2 = new WritableCellFormat(wf1);
			cf2.setAlignment(Alignment.LEFT);
			cf2.setBorder(Border.ALL, BorderLineStyle.NONE);
			ws.addCell(new Label(7, 1, String.valueOf("抽样关员签字:"), cf2));
			ws.mergeCells(7, 1, 12, 1);

			WritableFont wf3 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf3 = new WritableCellFormat(wf3);
			cf3.setAlignment(Alignment.CENTRE);
			cf3.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf3.setBorder(Border.ALL, BorderLineStyle.THIN);
			ws.setRowView(2, 400, false);
			ws.addCell(new Label(0, 2, String.valueOf("联系人"), cf3));
			ws.mergeCells(0, 2, 1, 2);
			ws.addCell(new Label(2, 2, String.valueOf(""), cf3));
			ws.mergeCells(2, 2, 6, 2);
			ws.addCell(new Label(7, 2, String.valueOf("联系电话"), cf3));
			ws.mergeCells(7, 2, 8, 2);
			ws.addCell(new Label(9, 2, "", cf3));
			ws.mergeCells(9, 2, 12, 2);

			WritableFont wf4 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf4 = new WritableCellFormat(wf4);
			cf4.setAlignment(Alignment.CENTRE);
			cf4.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf4.setBorder(Border.ALL, BorderLineStyle.THIN);
			WritableCellFormat cf41 = new WritableCellFormat(wf4);
			cf41.setAlignment(Alignment.LEFT);
			cf41.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf41.setBorder(Border.RIGHT, BorderLineStyle.THIN);
			cf41.setBorder(Border.LEFT, BorderLineStyle.THIN);
			ws.setRowView(3, 400, false);// 设置第4行的高度
			ws.setRowView(4, 400, false);
			ws.setRowView(5, 400, false);
			ws.setRowView(6, 400, false);
			ws.setRowView(7, 400, false);
			ws.setRowView(8, 400, false);
			ws.setRowView(9, 400, false);
			ws.setRowView(10, 400, false);
			ws.setRowView(11, 400, false);
			ws.setRowView(12, 400, false);
			ws.addCell(new Label(0, 3, String.valueOf("送样日期"), cf4));
			ws.mergeCells(0, 3, 1, 4);
			ws.addCell(new Label(2, 3, String.valueOf(""), cf4));
			ws.mergeCells(2, 3, 5, 4);
			ws.addCell(new Label(6, 3, String.valueOf("送样方式"), cf4));
			ws.mergeCells(6, 3, 6, 4);
			ws.addCell(new Label(7, 3, String.valueOf("□门对门快递"), cf41));
			ws.addCell(new Label(7, 4, String.valueOf("□专人"), cf41));
			ws.mergeCells(7, 3, 8, 3);
			ws.mergeCells(7, 4, 8, 4);
			ws.addCell(new Label(9, 3, String.valueOf("送样"), cf41));
			ws.addCell(new Label(9, 4, String.valueOf("信息"), cf41));
			ws.addCell(new Label(10, 3, String.valueOf("□快递号: "), cf41));
			if (1 == 1) {
				ws.addCell(new Label(10, 4, String.valueOf("送样人: " + ""), cf41));
			} else {
				ws.addCell(new Label(10, 4, String.valueOf("□送样人: "), cf41));
			}
			ws.mergeCells(10, 3, 12, 3);
			ws.mergeCells(10, 4, 12, 4);

			WritableFont wf5 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf5 = new WritableCellFormat(wf5);
			cf5.setAlignment(Alignment.LEFT);
			cf5.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf5.setBorder(Border.ALL, BorderLineStyle.THIN);
			ws.addCell(new Label(0, 5, String.valueOf("样品"), cf41));
			ws.addCell(new Label(0, 6, String.valueOf("类型"), cf41));
			Boolean check1 = false;
			Boolean check2 = false;
			if (check1 == true) {
				ws.addCell(new Label(1, 5, String.valueOf(" 进出境监督抽样样品"), cf5));
			} else {
				ws.addCell(new Label(1, 5, String.valueOf("□ 进出境监督抽样样品"), cf5));
			}
			if (check2 == true) {
				ws.addCell(new Label(1, 6, String.valueOf(" 进出境风险监测样品"), cf5));
			} else {
				ws.addCell(new Label(1, 6, String.valueOf("□ 进出境风险监测样品"), cf5));
			}
			ws.mergeCells(1, 5, 3, 5);
			ws.mergeCells(1, 6, 3, 6);
			ws.addCell(new Label(4, 5, String.valueOf("ECIQ/BIQ报检号:" + ""), cf5));
			ws.mergeCells(4, 5, 12, 5);
			ws.addCell(new Label(4, 6, String.valueOf("监测任务类别及文件号:"), cf5));
			ws.mergeCells(4, 6, 12, 6);

			WritableFont wf6 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf6 = new WritableCellFormat(wf6);
			cf6.setAlignment(Alignment.LEFT);
			cf6.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf6.setBorder(Border.ALL, BorderLineStyle.THIN);
			ws.addCell(new Label(0, 7, String.valueOf("样品名称"), cf6));
			ws.mergeCells(0, 7, 1, 7);
			ws.addCell(new Label(2, 7, String.valueOf(""), cf6));
			ws.mergeCells(2, 7, 3, 7);
			ws.addCell(new Label(4, 7, String.valueOf("送样重/数量"), cf6));
			ws.mergeCells(4, 7, 5, 7);
			String szl = "";
			ws.addCell(new Label(6, 7, String.valueOf(szl), cf6));
			ws.mergeCells(6, 7, 7, 7);
			ws.addCell(new Label(8, 7, String.valueOf("出口企业/进口国或地区"), cf6));
			ws.mergeCells(8, 7, 10, 7);
			ws.addCell(new Label(11, 7, "", cf6));
			ws.mergeCells(11, 7, 12, 7);

			WritableFont wf7 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf7 = new WritableCellFormat(wf7);
			cf7.setAlignment(Alignment.LEFT);
			cf7.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf7.setBorder(Border.ALL, BorderLineStyle.THIN);
			ws.addCell(new Label(0, 8, String.valueOf("是否制好样"), cf7));
			ws.mergeCells(0, 8, 1, 8);
			if ("0".equals("")) {
				ws.addCell(new Label(2, 8, String.valueOf("是    □否 "), cf7));
			} else {
				ws.addCell(new Label(2, 8, String.valueOf("□是    □否 "), cf7));
			}

			ws.mergeCells(2, 8, 3, 8);
			ws.addCell(new Label(4, 8, String.valueOf("样品状态"), cf7));
			ws.mergeCells(4, 8, 5, 8);
			ws.addCell(new Label(6, 8, String.valueOf(" □固体   □液体"), cf7));
			ws.mergeCells(6, 8, 7, 8);
			ws.addCell(new Label(8, 8, String.valueOf("包装情况"), cf7));
			ws.mergeCells(8, 8, 10, 8);
			ws.addCell(new Label(11, 8, String.valueOf(" "), cf6));
			ws.mergeCells(11, 8, 12, 8);

			ws.addCell(new Label(0, 9, String.valueOf("样品标识"), cf7));
			ws.mergeCells(0, 9, 1, 9);
			ws.addCell(new Label(2, 9, String.valueOf(" "), cf7));
			ws.mergeCells(2, 9, 3, 9);
			ws.addCell(new Label(4, 9, String.valueOf("样品封识"), cf7));
			ws.mergeCells(4, 9, 5, 9);
			ws.addCell(new Label(6, 9, String.valueOf(" □样品封条"), cf7));
			ws.mergeCells(6, 9, 7, 9);
			ws.addCell(new Label(8, 9, String.valueOf("样品储存要求"), cf7));
			ws.mergeCells(8, 9, 9, 9);
			if ("1".equals("")) {
				ws.addCell(new Label(10, 9, String.valueOf("□常温 冷冻 □冷藏 □其他 "), cf6));
			} else {
				ws.addCell(new Label(10, 9, String.valueOf("□常温 □冷冻 □冷藏 □其他 "), cf6));
			}
			ws.mergeCells(10, 9, 12, 9);

			ws.addCell(new Label(0, 10, String.valueOf("送检单位是否有检测设备"), cf7));
			ws.mergeCells(0, 10, 3, 10);
			ws.addCell(new Label(4, 10, String.valueOf("□否   □是   (如有请填写外送原因):"), cf7));
			ws.mergeCells(4, 10, 12, 10);

			ws.addCell(new Label(0, 11, String.valueOf("结果单传递方式"), cf7));
			ws.mergeCells(0, 11, 3, 11);
			if ("1".equals("")) {
				ws.addCell(new Label(4, 11, String.valueOf("自取()邮寄()传真(√)邮箱()请填写传真号/邮箱:"), cf7));
			} else {
				ws.addCell(new Label(4, 11, String.valueOf("自取()邮寄()传真()邮箱()请填写传真号/邮箱:"), cf7));
			}
			ws.mergeCells(4, 11, 12, 11);

			ws.addCell(new Label(0, 12, String.valueOf("要求施检项目"), cf4));
			ws.mergeCells(0, 12, 3, 12);
			ws.addCell(new Label(4, 12, String.valueOf("检验检疫标准或依据"), cf4));
			ws.mergeCells(4, 12, 9, 12);
			ws.addCell(new Label(10, 12, String.valueOf("合同要求"), cf4));
			ws.mergeCells(10, 12, 12, 12);

			WritableFont wf42 = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf42 = new WritableCellFormat(wf42);
			cf42.setAlignment(Alignment.CENTRE);
			cf42.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf42.setBorder(Border.ALL, BorderLineStyle.THIN);
			cf42.setWrap(true);
			// 使用循环将数据读出
			int ii = 13;
			List tProjectList = null;
			if (tProjectList != null && tProjectList.size() > 0) {
				for (int i = 0; i < tProjectList.size(); i++) {
					ws.addCell(new Label(0, 13 + i, String.valueOf(""), cf42));
					ws.mergeCells(0, 13 + i, 3, 13 + i);
					ws.addCell(new Label(4, 13 + i, String.valueOf(""), cf42));
					ws.mergeCells(4, 13 + i, 9, 13 + i);
					ws.addCell(new Label(10, 13 + i, String.valueOf(" "), cf4));
					ws.mergeCells(10, 13 + i, 12, 13 + i);
					int len = 300;
					if ("".length() * 16 > len) {
						len = "".length() * 16;
					}
					ws.setRowView(13 + i, len, false);
					ii = 13 + i + 1;
				}
			}

			WritableFont wf43 = new WritableFont(WritableFont.TIMES, 12, WritableFont.BOLD, false,
					UnderlineStyle.NO_UNDERLINE);
			WritableCellFormat cf43 = new WritableCellFormat(wf43);
			cf43.setAlignment(Alignment.LEFT);
			cf43.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf43.setBorder(Border.ALL, BorderLineStyle.THIN);
			cf43.setWrap(true);
			ws.addCell(new Label(0, ii, String.valueOf("以下由技术中心填写"), cf43));
			ws.mergeCells(0, ii, 12, ii);
			ws.setRowView(ii, 600, false);

			WritableCellFormat cf44 = new WritableCellFormat(wf4);
			cf44.setAlignment(Alignment.LEFT);
			cf44.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf44.setBorder(Border.RIGHT, BorderLineStyle.THIN);
			WritableCellFormat cf45 = new WritableCellFormat(wf4);
			cf45.setVerticalAlignment(VerticalAlignment.CENTRE);
			cf45.setBorder(Border.RIGHT, BorderLineStyle.NONE);
			ws.addCell(new Label(0, ii + 1, String.valueOf("收件人"), cf7));
			ws.mergeCells(0, ii + 1, 1, ii + 1);
			ws.addCell(new Label(2, ii + 1, String.valueOf(" "), cf7));
			ws.mergeCells(2, ii + 1, 3, ii + 1);
			ws.addCell(new Label(4, ii + 1, String.valueOf("收样时间"), cf7));
			ws.mergeCells(4, ii + 1, 5, ii + 1);
			ws.addCell(new Label(6, ii + 1, String.valueOf(" "), cf7));
			ws.mergeCells(6, ii + 1, 7, ii + 1);
			ws.addCell(new Label(8, ii + 1, String.valueOf("收件人"), cf7));
			ws.mergeCells(8, ii + 1, 9, ii + 1);
			ws.addCell(new Label(10, ii + 1, String.valueOf(" "), cf45));
			ws.addCell(new Label(11, ii + 1, String.valueOf("个工作日 "), cf44));
			ws.mergeCells(11, ii + 1, 12, ii + 1);
			ws.setRowView(ii + 1, 400, false);

			ws.addCell(new Label(0, ii + 2, String.valueOf("   □状态正常  □满足检测要求  □附照片  □附其他材料"), cf7));
			ws.mergeCells(0, ii + 2, 12, ii + 2);
			ws.setRowView(ii + 2, 600, false);

			WritableCellFormat cf46 = new WritableCellFormat(wf4);
			cf46.setAlignment(Alignment.LEFT);
			cf46.setVerticalAlignment(VerticalAlignment.TOP);
			cf46.setBorder(Border.ALL, BorderLineStyle.THIN);
			ws.addCell(new Label(0, ii + 3, String.valueOf("描述记录(必要时,如微生物等特殊样品等):"), cf46));
			ws.mergeCells(0, ii + 3, 12, ii + 3);
			ws.setRowView(ii + 3, 800, false);

			ws.addCell(new Label(0, ii + 4, String.valueOf("备注:"), cf7));
			ws.mergeCells(0, ii + 4, 12, ii + 4);
			ws.setRowView(ii + 4, 600, false);

			try {
				// 从内存中写入文件中
				wwb.write();
				// 关闭资源,释放内存
				wwb.close();
			} catch (IOException e) {
				e.printStackTrace();
			} catch (WriteException e) {
				e.printStackTrace();
			}
		}
	}

}

效果如下

受理系统内执法样本检测联系单
送检单位(盖章)抽样关员签字:
联系人 联系电话 
送样日期 送样方式□门对门快递送样□快递号: 
□专人信息送样人: 
样品□ 进出境监督抽样样品ECIQ/BIQ报检号:
类型□ 进出境风险监测样品监测任务类别及文件号:
样品名称 送样重/数量 出口企业/进口国或地区 
是否制好样□是    □否 样品状态 □固体   □液体包装情况
样品标识样品封识 □样品封条样品储存要求□常温 □冷冻 □冷藏 □其他 
送检单位是否有检测设备□否   □是   (如有请填写外送原因):
结果单传递方式自取()邮寄()传真()邮箱()请填写传真号/邮箱:
要求施检项目检验检疫标准或依据合同要求
以下由技术中心填写
收件人收样时间收件人个工作日 
   □状态正常  □满足检测要求  □附照片  □附其他材料
描述记录(必要时,如微生物等特殊样品等):
备注:
    原文作者:公孙起
    原文地址: https://blog.csdn.net/xianrenyingzi/article/details/123276726
    本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系博主进行删除。

相关文章