java纯后台实现Excel导出

通过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报检号:
类型□ 进出境风险监测样品监测任务类别及文件号:
样品名称 送样重/数量 出口企业/进口国或地区 
是否制好样□是    □否 样品状态 □固体   □液体包装情况
样品标识样品封识 □样品封条样品储存要求□常温 □冷冻 □冷藏 □其他 
送检单位是否有检测设备□否   □是   (如有请填写外送原因):
结果单传递方式自取()邮寄()传真()邮箱()请填写传真号/邮箱:
要求施检项目检验检疫标准或依据合同要求
以下由技术中心填写
收件人收样时间收件人个工作日 
   □状态正常  □满足检测要求  □附照片  □附其他材料
描述记录(必要时,如微生物等特殊样品等):
备注:
  • 1
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
好的,下面是一份使用poi和echarts实现后端导出Excel的示例代码: ```java import org.apache.poi.ss.usermodel.*; import org.apache.poi.xssf.usermodel.*; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.ResponseBody; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import java.util.List; @Controller @RequestMapping("/export") public class ExcelExportController { @RequestMapping(value = "/excel", method = RequestMethod.GET) @ResponseBody public String exportExcel() { // 构造数据列表 List<User> userList = new ArrayList<>(); userList.add(new User("张三", 18, "男")); userList.add(new User("李四", 20, "女")); userList.add(new User("王五", 22, "男")); userList.add(new User("赵六", 24, "女")); // 初始化 Excel 文档 XSSFWorkbook workbook = new XSSFWorkbook(); XSSFSheet sheet = workbook.createSheet("用户信息"); // 创建表头 XSSFRow headerRow = sheet.createRow(0); headerRow.createCell(0).setCellValue("姓名"); headerRow.createCell(1).setCellValue("年龄"); headerRow.createCell(2).setCellValue("性别"); // 填充数据 int rowNum = 1; for (User user : userList) { XSSFRow row = sheet.createRow(rowNum++); row.createCell(0).setCellValue(user.getName()); row.createCell(1).setCellValue(user.getAge()); row.createCell(2).setCellValue(user.getGender()); } // 添加图表 XSSFDrawing drawing = sheet.createDrawingPatriarch(); XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 4, 1, 15, 20); XSSFSheet chartSheet = workbook.createSheet("统计图"); XSSFChart chart = drawing.createChart(anchor); XSSFChartLegend legend = chart.getOrCreateLegend(); legend.setPosition(LegendPosition.TOP_RIGHT); XSSFChartAxis xAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); XSSFValueAxis yAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); yAxis.setCrosses(AxisCrosses.AUTO_ZERO); List<String> xData = new ArrayList<>(); List<Double> yData = new ArrayList<>(); xData.add("张三"); xData.add("李四"); xData.add("王五"); xData.add("赵六"); yData.add(18.0); yData.add(20.0); yData.add(22.0); yData.add(24.0); XSSFChartSeries series = chart.createSeries(); series.setTitle("年龄统计"); XSSFDataSheet xDataSheet = chartSheet.createDataSheet(); XSSFChartData data = chart.getChartDataFactory().createBarChartData(); XSSFChartAxis bottomAxis = chart.getChartAxisFactory().createCategoryAxis(AxisPosition.BOTTOM); XSSFValueAxis leftAxis = chart.getChartAxisFactory().createValueAxis(AxisPosition.LEFT); ChartDataSource<String> xDataSource = DataSources.fromStringCellRange(sheet, new CellRangeAddress(1, 4, 0, 0)); ChartDataSource<Number> yDataSource = DataSources.fromNumericCellRange(sheet, new CellRangeAddress(1, 4, 1, 1)); series = data.addSeries(xDataSource, yDataSource); chart.plot(data, bottomAxis, leftAxis); // 输出 Excel 文件 try (FileOutputStream outputStream = new FileOutputStream("users.xlsx")) { workbook.write(outputStream); } catch (IOException e) { e.printStackTrace(); } return "Excel 导出成功!"; } private static class User { private String name; private int age; private String gender; public User(String name, int age, String gender) { this.name = name; this.age = age; this.gender = gender; } public String getName() { return name; } public void setName(String name) { this.name = name; } public int getAge() { return age; } public void setAge(int age) { this.age = age; } public String getGender() { return gender; } public void setGender(String gender) { this.gender = gender; } } } ``` 这份代码会在浏览器端提供一个 /export/excel 接口,访问该接口后会在后端生成一个 users.xlsx 文件,其中包含了用户信息列表和一个年龄统计图。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值