EXCEL导出代码全套

最新推荐文章于 2024-04-20 00:48:14 发布

fzyr2008

最新推荐文章于 2024-04-20 00:48:14 发布

阅读量638

点赞数

CC 4.0 BY-SA版权

文章标签： java

本文链接：https://blog.csdn.net/fzyr2008/article/details/109046124

本文详细介绍了如何使用Java进行Excel数据导出，包括选择合适的库，设置表格样式，处理大数据量，以及导出过程中的性能优化技巧。

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

public String export() throws Exception {
   
   
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Map param = new HashMap();
		BeanHelper.beanAttributeValueTrim(this.info);
		param.put("param", this.info);
		List list = this.commonService.queryList("overSpeed", param);
		ExcelUtil excel = new ExcelUtil();
		int[] width = {
   
    80, 125, 180, 60, 60, 75, 60 };
		excel.createSheet("超速车辆记录", width);
		excel.addData(
				new String[][] {
   
    {
   
    "姓名", "0,0,1" }, {
   
    "单位", "0,0,1" }, {
   
    "车牌号", "0,0,1" }, {
   
    "超时时间", "0,0,1" }, {
   
    "超速地点", "0,0,1" }, {
   
    "行驶速度", "0,0,1" }, {
   
    "限速值", "0,0,1" }, {
   
    "超速百分比", "0,0,1" }, {
   
    "联系号码", "0,0,1" } });
		if (list != null)
			for (int i = 0; i < list.size(); ++i) {
   
   
				Map map = (Map) list.get(i);
				Object[][] str = {
   
    {
   
    map.get("Owner"), "0,0,2" }, {
   
    map.get("Department"), "0,0,2" }, {
   
    map.get("hphm"), "0,0,2" }, {
   
    (map.get("cssj") == null) ? "" : sdf.format(map.get("cssj")).toString(), "0,0,2" },
						{
   
    map.get("csdd"), "0,0,2" }, {
   
    map.get("xssd"), "0,0,2" }, {
   
    map.get("xsz"), "0,0,2" }, {
   
    map.get("csbfb") + "%", "0,0,2" }, {
   
    map.get("CellNumber"), "0,0,2" } };
				excel.addData(str);
			}
		OutputStream os = this.response.getOutputStream();
		this.response.reset();
		String fileName = new String("超速车辆记录".getBytes("GB2312"), "ISO_8859_1");
		this.response.setHeader("Content-Disposition", "attachment; filename=" + fileName + ".xls");
		this.response.setContentType("application/vnd.ms-excel;charset=utf-8");

		excel.wb.write(os);
		os.close();

		return null;
	}

package com.XXX.util.Excel;

import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.BigInteger;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExcelUtil {
	public SXSSFWorkbook wb = null;
	private Sheet sheet = null;
	private Row row = null;
	private ExcelOperate exceloperate = new ExcelOperate();
	private CellStyle title = null;
	private CellStyle header = null;
	private CellStyle text = null;
	private CellStyle top_text = null;
	private CellStyle top_label = null;
	private CellStyle lableleft_bottom = null;
	private CellStyle textmid_bottom = null;
	private CellStyle textright_bottom = null;
	private CellStyle lablemid_bottom = null;
	private CellStyle text_signature = null;
	private Cell cell = null;
	public int rownumber = 0;

	public ExcelUtil() {
		// 第一步，创建一个webbook，对应一个Excel文件
		wb = new SXSSFWorkbook(500);
		// 初始化样式
		title = exceloperate.getCellStyle(wb, "title", "");
		header = exceloperate.getCellStyle(wb, "header1", "");
		text = exceloperate.getCellStyle(wb, "text", "");
		top_text = exceloperate.getCellStyle(wb, "top_text", "");
		top_label = exceloperate.getCellStyle(wb, "top_label", "");
		lableleft_bottom = exceloperate
				.getCellStyle(wb, "lableleft_bottom", "");
		textmid_bottom = exceloperate.getCellStyle(wb, "textmid_bottom", "");
		textright_bottom = exceloperate
				.getCellStyle(wb, "textright_bottom", "");
		lablemid_bottom = exceloperate.getCellStyle(wb, "lablemid_bottom", "");
		text_signature = exceloperate.getCellStyle(wb, "text_signature", "");
	}

	public void createSheet(String sheetName, int[] width) {
		// 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet
		sheet = wb.createSheet(sheetName);
		// 初始化表格宽度
		for (int i = 0; i < width.length; i++) {
			// sheet.autoSizeColumn(i);
			sheet.setColumnWidth(i, width[i] * 36);
		}
	}

	// 添加标题
	public void addtitle(String titletext, String toptext, String toplabel,
			int width) {
		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
		rownumber = 0;
		row = sheet.createRow(rownumber);
		// 先合并列再写值
		sheet.addMergedRegion(new CellRangeAddress(0, 2, 0, width));
		cell = row.createCell(0);
		cell.setCellValue(titletext);
		cell.setCellStyle(title);
		rownumber = rownumber + 3;
		int flag = 0;
		if (!toptext.equals("")) {
			row = sheet.createRow(rownumber);
			sheet.addMergedRegion(new CellRangeAddress(rownumber, rownumber, 0,
					width / 2));
			cell = row.createCell(0);
			cell.setCellValue(toptext);
			cell.setCellStyle(top_text);
			flag = 1;
		}
		if (!toplabel.equals("")) {
			if (toptext.equals("")) {
				row = sheet.createRow(rownumber);
				sheet.addMergedRegion(new CellRangeAddress(rownumber,
						rownumber, 0, width));
				cell = row.createCell(0);
			} else {
				sheet.addMergedRegion(new CellRangeAddress(rownumber,
						rownumber, width - width / 2, width));
				cell = row.createCell(width - width / 2);

			}
			cell.setCellValue(toplabel);
			cell.setCellStyle(top_label);
			flag = 1;
		}
		if (flag == 1)
			rownumber++;
	}

	/**
	 * 添加数据和表头 str第二维存放合并行列、样式和行高（可以为空） 如1,2,1,30表示合并1行2列样式1行高30 样式 1：header
	 * 2：text 3.lableleft_bottom 没有右边框居右 4.textmid_bottom 没有左右框居左
	 * 5.textright_bottom 没有左边框居左 6.lablemid_bottom 没有左右框居右
	 * 
	 * @param str
	 */
	public void addData(Object[][] str) {
		row = sheet.createRow(rownumber);
		if (((String) str[0][1]).split(",").length == 4)
			row.setHeightInPoints(Float.parseFloat(((String) str[0][1])
					.split(",")[3]));
		else
			row.setHeightInPoints(15);
		for (int j = 0, i = 0; i < str.length; i++) {
			CellRangeAddress cellRangeAddress = null;
			String[] range = ((String) str[i][1]).split(",");
			if (Integer.parseInt(range[0]) > 0
					|| Integer.parseInt(range[1]) > 0) {
				if (str[i][0] != null) {
					cellRangeAddress = new CellRangeAddress(
							rownumber, rownumber + Integer.parseInt(range[0]),
							j, j + Integer.parseInt(range[1]));
					sheet.addMergedRegion(cellRangeAddress);
				}
			}
			cell = row.createCell(j);
			if (str[i][0] instanceof Integer)
				cell.setCellValue(((Integer) str[i][0]).intValue());
			else if (str[i][0] instanceof BigInteger)
				cell.setCellValue(((BigInteger) str[i][0]).doubleValue());
			else if (str[i][0] instanceof Double)
				cell.setCellValue(((Double) str[i][0]).doubleValue());
			else if (str[i][0] instanceof Float)
				cell.setCellValue(((Float) str[i][0]).doubleValue());
			else if (str[i][0] instanceof BigDecimal)
				cell.setCellValue(((BigDecimal) str[i][0]).doubleValue());
			else
				cell.setCellValue((String) str[i][0]);
			switch (Integer.parseInt(range[2])) {
			case 1:
				cell.setCellStyle(header);
				break;
			case 2:
				cell.setCellStyle(text);
				break;
			case 3:
				cell.setCellStyle(lableleft_bottom);
				break;
			case 4:
				cell.setCellStyle(textmid_bottom);
				break;
			case 5:
				cell.setCellStyle(textright_bottom);
				break;
			case 6:
				cell.setCellStyle(lablemid_bottom);
				break;
			case 7:
				cell.setCellStyle(text_signature);
				break;
			default:
			}
			j = j + 1 + Integer.parseInt(range[1]);
			if(cellRangeAddress!=null){
				RegionUtil.setBorderTop(1, cellRangeAddress, sheet);
				RegionUtil.setBorderBottom(1, cellRangeAddress, sheet);
				RegionUtil.setBorderLeft(1, cellRangeAddress, sheet);
				RegionUtil.setBorderRight(1, cellRangeAddress, sheet);
				cellRangeAddress = null;
			}
		}
		rownumber = rownumber + 1;
	}

	public static void main(String[] args) throws IOException {
		FileOutputStream fOut = new FileOutputStream("d:/Demo_02_05.xls");
		ExcelUtil excel = new ExcelUtil();
		int[] width = { 80, 100, 130, 80, 180, 100, 80, 80 };
		excel.createSheet("日志报表", width);
		excel.addtitle("运维部月报表", "姓名", "____年____月", width.length - 1);
		System.out.println("------------");
		excel.addData(new String[][] { { "日期", "0,0,1" }, { "星期", "0,0,1" },
				{ "客户名称", "0,0,1" }, { "处理情况", "1,2,1" }, { "工作性质", "0,0,1" },
				{ "处理结果", "0,0,1" } });
		excel.addData(new String[][] { { "日期", "0,0,1" }, { "星期", "0,0,1" },
				{ "客户名称", "0,0,1" }, { null, "-1,2,1" }, { "工作性质", "0,0,1" },
				{ "处理结果", "0,0,1" } });
		excel.addData(new String[][] { { "本月计划", "1,1,2" }, { "123", "1,5,2" } });
		excel.addData(new String[][] { { null, "0,1,2" }, { null, "0,5,2" } });
		excel.addData(new String[][] { { "下月计划", "1,1,2" }, { "", "1,5,2" } });
		excel.addData(new String[][] { { null, "0,1,2" }, { null, "0,5,2" } });
		excel.addData(new String[][] { { "部门助理：", "1,1,3" }, { "", "1,0,4" },
				{ "部门经理：", "1,0,6" }, { "", "1,1,4" }, { "人事行政部：", "1,0,6" },
				{ "", "1,0,5" } });
		excel.addData(new String[][] { { null, "0,1,3" }, { null, "0,0,4" },
				{ null, "0,0,6" }, { null, "0,1,4" }, { null, "0,0,6" },
				{ null, "0,0,5" } });
		System.out.println("ok");
		excel.wb.write(fOut);
		fOut.flush();
		fOut.close();
	}
}

package com.XXX.util.Excel;

import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

public class ExcelOperate {
   
   

	public static final String FILE_NAME = "name";
	public static final String CSS_CLASS = "clazz";
	public static final String CSS_STYLE = "style";
	public static final String COLUMN_WIDTHS = "widths";
	public static final String ROW_HEIGHT = "height";
	public static final String CELL_RANGE = "range";
	public static final String CELL_TEXT = "text";
	public static final String FIELD_DATA = "data";
	public static final String FIELD_CELL = "cell";
	
	private static final Stri