Java Excel 导出技巧-优快云博客

本文介绍了使用 Java 进行 Excel 数据导出的方法，包括创建 Excel 文件、设置样式及合并单元格等操作，并提供了导出单表和主子表的具体实现。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、保存到文件

public File queryToFile() {
	List list = null;
	try {
		File file = new File("file.xls");
		WritableWorkbook wwb = Workbook.createWorkbook(file); // 此处建立路径
		WritableSheet ws = wwb.createSheet("Sheet1", 0); // 建立工作簿
		ws.setColumnView(0, 20);

		WritableFont wfc = new WritableFont(WritableFont.createFont("宋体"),
				12, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.BLACK);
		WritableCellFormat wcfFC = new WritableCellFormat(wfc);
		wcfFC.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		wcfFC.setVerticalAlignment(VerticalAlignment.CENTRE);
		wcfFC.setAlignment(Alignment.CENTRE);

		WritableFont wfcB = new WritableFont(WritableFont.createFont("宋体"),
				12, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE,
				Colour.BLACK);
		WritableCellFormat wcfFCB = new WritableCellFormat(wfcB);
		wcfFCB.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		wcfFCB.setVerticalAlignment(VerticalAlignment.CENTRE);
		wcfFCB.setAlignment(Alignment.CENTRE);

		WritableFont wfcR = new WritableFont(WritableFont.createFont("宋体"),
				12, WritableFont.NO_BOLD, false,
				UnderlineStyle.NO_UNDERLINE, Colour.BLUE);
		WritableCellFormat wcfFCR = new WritableCellFormat(wfcR);
		wcfFCR.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK);
		wcfFCR.setVerticalAlignment(VerticalAlignment.CENTRE);
		wcfFCR.setAlignment(Alignment.RIGHT);

		ws.mergeCells(0, 0, 0, 1); // 合并单元格，(行，列，行，列)
		ws.mergeCells(1, 0, 1, 1);
		ws.mergeCells(2, 0, 2, 1);
		Label t0 = new Label(0, 0, "序号", wcfFCB);
		ws.addCell(t0);
		ws.setColumnView(0, 10);

		for (int i = 0; i < 100; i++) {

			Label ts = new Label(0, (i + 2), (i + 1) + "", wcfFC);
			ws.addCell(ts);
		}

		wwb.write();
		wwb.close();
		return file;
	} catch (Exception e) {
		e.printStackTrace();
	}
	return null;
}

2、导出到文件

/**
 * 数据导出公共类
 * @author mywhile
 * @version 1.0
 */
@SuppressWarnings("unchecked")
public class ExportExcel{
	/**
	 * Excel导出
	 * 
	 * @param list 源数据
	 * @param title Excel对应列代码和注释（字段名：注释）
	 * @param filename Excel目标文件名
	 * @param response 服务器端响应
	 */
	public static void exportExcel(
			List<Map> list, 
			String title, String filename, 
			HttpServletResponse response) {
		OutputStream outputstream = null;
		jxl.write.WritableWorkbook wwb = null;
		String[] titleary = null;
		String[] titlename = null;
		String[] columnid = null;
		try {
			if (title.equals("")) {
				throw new Exception("filename不能为空！");
			}
			titleary = title.split(",");
			columnid = new String[titleary.length];
			titlename = new String[titleary.length];
			for (int j = 0; j < titleary.length; j++) {
				if (titleary[j] != null && !titleary[j].equals("")) {
					String curtitle = titleary[j];
					if (curtitle.indexOf(":") < 0) {
						throw new Exception("title格式不正确");
					}
					columnid[j] = curtitle.split(":")[0];
					titlename[j] = curtitle.split(":")[1];
				}
			}
			String filenameconvert = new String(filename.getBytes(), "iso-8859-1");
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=" + filenameconvert + ".xls");
			outputstream = response.getOutputStream();
			wwb = jxl.Workbook.createWorkbook(outputstream);
			jxl.write.WritableSheet sheet = wwb.createSheet(filename, 0);
			int index = 0;
			for (int j = 0; j < titlename.length; j++) {
				jxl.write.Label lable = new jxl.write.Label(j, index, titlename[j]);
				sheet.addCell(lable);
			}
			index = index + 1;
			for (Map map : list) {
				for (int i = 0; i < columnid.length; i++) {
					String tmp = map.get(columnid[i]) != null ? map.get(columnid[i]).toString() : "";
					jxl.write.Label lable = new jxl.write.Label(i, index, tmp);
					sheet.addCell(lable);
				}
				index++;
			}
			// 写入数据
			wwb.write();
			wwb.close();
			outputstream.close();
			wwb = null;
			outputstream = null;
		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			if (wwb != null) {
				try {
					wwb.close();
					wwb = null;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (outputstream != null) {
				try {
					outputstream.close();
					outputstream = null;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * Excel导出:主子表
	 * @param list 源数据
	 * @param sonKey 子表的Key值
	 * @param title Excel对应列代码和注释（字段名：注释,字段名：注释,字段名：注释.....）
	 * @param sonTitle Excel对应列代码与注释（字段名：注释,字段名：注释,字段名：注释.....）：子表
	 * @param filename Excel目标文件名
	 * @param response 服务器端响应
	 * 
	 */
	public static void exportExcelPS(
			List<Map> list,
			String sonKey, String title, 
			String sonTitle, String filename, 
			HttpServletResponse response) {
		OutputStream outputstream = null;
		jxl.write.WritableWorkbook wwb = null;
		String[] pTitleAry = null, sTitleAry = null;
		String[] pTitleNam = null, sTitleNam = null;
		String[] pColumnId = null, sColumnId = null;
		try {
			if (title.equals("") || sonTitle.equals("")) {
				throw new Exception("filename不能为空！");
			}
			pTitleAry = title.split(",");
			pColumnId = new String[pTitleAry.length];
			pTitleNam = new String[pTitleAry.length];
			for (int j = 0; j < pTitleAry.length; j++) {
				if (pTitleAry[j] != null && !pTitleAry[j].equals("")) {
					String curtitle = pTitleAry[j];
					if (curtitle.indexOf(":") < 0) {
						throw new Exception("title格式不正确");
					}
					pColumnId[j] = curtitle.split(":")[0];
					pTitleNam[j] = curtitle.split(":")[1];
				}
			}
			sTitleAry = sonTitle.split(",");
			sColumnId = new String[sTitleAry.length];
			sTitleNam = new String[sTitleAry.length];
			for (int j = 0; j < sTitleAry.length; j++) {
				if (sTitleAry[j] != null && !sTitleAry[j].equals("")) {
					String curtitle = sTitleAry[j];
					if (curtitle.indexOf(":") < 0) {
						throw new Exception("title格式不正确");
					}
					sColumnId[j] = curtitle.split(":")[0];
					sTitleNam[j] = curtitle.split(":")[1];
				}
			}
			String filenameconvert = new String(filename.getBytes(), "iso-8859-1");
			response.setCharacterEncoding("utf-8");
			response.setContentType("application/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=" + filenameconvert + ".xls");
			outputstream = response.getOutputStream();
			wwb = jxl.Workbook.createWorkbook(outputstream);
			jxl.write.WritableSheet sheet = wwb.createSheet(filename, 0);
			int index = 0;
			for (int j = 0; j < pTitleNam.length; j++) {
				jxl.write.Label lable = new jxl.write.Label(j, index, pTitleNam[j]);
				sheet.addCell(lable);
			}
			index = index + 1;
			for (int j = 0;j<list.size();j++) {
				Map map = (Map)list.get(j);
				for (int i = 0; i < pColumnId.length; i++) {
					String tmp = map.get(pColumnId[i]) != null ? map.get(pColumnId[i]).toString() : "";
					jxl.write.Label lable = new jxl.write.Label(i, index, tmp);
					sheet.addCell(lable);
				}
				index++;
				for (int m = 0; m < sTitleNam.length; m++) {
					jxl.write.Label lable = new jxl.write.Label(m+1, index, sTitleNam[m]);
					sheet.addCell(lable);
				}
				index++;
				if(map.get(sonKey) != null){
					List son_list = (List)map.get(sonKey);
					for(int k = 0;k<son_list.size();k++){
						Map map_son = (Map)son_list.get(k);
						for (int i = 0; i < sColumnId.length; i++) {
							String tmp = map_son.get(sColumnId[i]) != null ? map_son.get(sColumnId[i]).toString() : "";
							jxl.write.Label lable = new jxl.write.Label(i+1, index, tmp);
							sheet.addCell(lable);
						}
						index++;
					}
				}
			}
			// 写入数据
			wwb.write();
			wwb.close();
			outputstream.close();
			wwb = null;
			outputstream = null;
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			if (wwb != null) {
				try {
					wwb.close();
					wwb = null;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
			if (outputstream != null) {
				try {
					outputstream.close();
					outputstream = null;
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		}
	}
}

导入导出