Java导出主表和明细Excel

最新推荐文章于 2023-08-28 14:29:32 发布

Mr.ZQ

最新推荐文章于 2023-08-28 14:29:32 发布

阅读量2.3k

点赞数

分类专栏：技能分享文章标签： Excel导出

原文链接：https://www.cnblogs.com/gudongcheng/p/8268909.html

版权

技能分享专栏收录该内容

2 篇文章 0 订阅

订阅专栏

Java导出主表和明细Excel

使用POI导出数据库主表和明细数据,原文https://www.cnblogs.com/gudongcheng/p/8268909.html

业务代码

package com.sto.export;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;
import org.springframework.stereotype.Service;

@Service
public class ExcelExportService {

	/**
	 * 自定义导出Workbook
	 * @param sheetName sheet名称
	 * @param title 标题
	 * @param values 主单表头名称数据
	 * @param title2  主单导出数据
	 * @param values2 明细表头名称数据
	 * @param wb
	 * @return
	 */
	public HSSFWorkbook getHSSFWorkbook(String sheetName, String[] title, String[][] values, String[] title2,
			String[][] values2, HSSFWorkbook wb) {
		// 第一步，创建一个HSSFWorkbook，对应一个Excel文件
		if (wb == null) {
			wb = new HSSFWorkbook();
		}
		// 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
		HSSFSheet sheet = wb.createSheet(sheetName);
		
		for (int i = 0; i < title2.length; i++) {
			sheet.setColumnWidth(i, 5000);
		}

		// 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
		HSSFRow headRow = sheet.createRow(0);
		HSSFRow detailRow = sheet.createRow(2);

		// 第四步，创建单元格，并设置值表头 设置表头居中
		HSSFCellStyle style = wb.createCellStyle();
		// style.setAlignment(HorizontalAlignment.CENTER); // 创建一个居中格式
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		Font ztFont = wb.createFont();
		ztFont.setItalic(false); // 设置字体为斜体字
		ztFont.setColor(Font.COLOR_NORMAL); // 将字体设置为“红色”
		ztFont.setFontHeightInPoints((short) 13); // 将字体大小设置为18px
		ztFont.setFontName("宋体"); // 将“宋体”字体应用到当前单元格上
		ztFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); // 加粗
		style.setFont(ztFont);
		
		//设置内容格式
		HSSFCellStyle dataStyle = wb.createCellStyle();
		dataStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式

		// 声明列对象
		HSSFCell cell = null;
		HSSFCell detialCell = null;

		// 创建标题
		for (int i = 0; i < title.length; i++) {
			cell = headRow.createCell(i);
			cell.setCellValue(title[i]);
			cell.setCellStyle(style);
		}
		for (int i = 0; i < title2.length; i++) {
			detialCell = detailRow.createCell(i);
			detialCell.setCellValue(title2[i]);
			detialCell.setCellStyle(style);
		}

		// 创建内容
		for (int i = 0; i < values.length; i++) {
			headRow = sheet.createRow(i + 1);
			for (int j = 0; j < values[i].length; j++) {
				// 将内容按顺序赋给对应的列对象
				HSSFCell headCell = headRow.createCell(j);
				headCell.setCellValue(values[i][j]);
				headCell.setCellStyle(dataStyle);
			}
		}
		for (int i = 0; i < values2.length; i++) {
			detailRow = sheet.createRow(i + 3);
			for (int j = 0; j < values2[i].length; j++) {
				// 将内容按顺序赋给对应的列对象
				HSSFCell detCell = detailRow.createCell(j);
				detCell.setCellValue(values2[i][j]);
				detCell.setCellStyle(dataStyle);
			}
		}
		return wb;
	}
}

控制层代码

package com.sto.controller;

import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.sto.export.ExcelExportService;
import com.sto.pojo.Order;
import com.sto.pojo.OrderDetial;
@Controller
@RequestMapping(value = "/report")
public class TestController {

	@Autowired
	private ExcelExportService excelExportService;
	
	/**
     * 导出报表
     * @return
     */
    @RequestMapping(value = "/export")
    @ResponseBody
    public void export(HttpServletRequest request,HttpServletResponse response) throws Exception {
        //获取数据
        List<Order> list = new ArrayList<>();
        Order order = new Order();
        order.setOrderId(123456L);
        order.setOrderCode("OC123456");
        order.setOrderSource("STO");
        order.setOrderPrice(10D);
        List<OrderDetial> arrayList = new ArrayList<>();
        OrderDetial orderDetial = new OrderDetial();
        orderDetial.setProductId(10001L);
        orderDetial.setProductCode("IF10001");
        orderDetial.setProductName("咖啡IE");
        orderDetial.setProductWeight("150g");
        orderDetial.setProductDec("测试数据");
        arrayList.add(orderDetial);
        OrderDetial orderDetial2 = new OrderDetial();
        orderDetial2.setProductId(10002L);
        orderDetial2.setProductCode("IF10002");
        orderDetial2.setProductName("咖啡IE_2");
        orderDetial2.setProductWeight("150g");
        orderDetial2.setProductDec("测试数据02");
        arrayList.add(orderDetial2);
        order.setOrderDetails(arrayList);
        list.add(order);

        //excel标题
        String[] title = {"主单","订单ID","订单编码","订单来源","订单价值"};
        String[] title2 = {"明细","货品ID","货品编码","货品名称","货品重量","货品描述"};

        //excel文件名
        String fileName = "订单信息表"+System.currentTimeMillis()+".xls";

        //sheet名
        String sheetName = "订单信息表";
        String[][] content = new String[list.size()][];
        for (int i = 0; i < list.size(); i++) {
            content[i] = new String[title.length];
            Order obj = list.get(i);
            content[i][0] = "";
            content[i][1] = obj.getOrderId().toString();
            content[i][2] = obj.getOrderCode();
            content[i][3] = obj.getOrderSource();
            content[i][4] = obj.getOrderPrice().toString();
        }
        String[][] content2 = new String[list.get(0).getOrderDetails().size()][];;
        for (int i = 0; i < list.get(0).getOrderDetails().size(); i++) {
        	content2[i] = new String[title2.length];
            OrderDetial obj = list.get(0).getOrderDetails().get(i);
            content2[i][0] = "";
            content2[i][1] = obj.getProductId().toString();
            content2[i][2] = obj.getProductCode();
            content2[i][3] = obj.getProductName();
            content2[i][4] = obj.getProductWeight();
            content2[i][5] = obj.getProductDec();
        }

        //创建HSSFWorkbook 
        HSSFWorkbook wb = excelExportService.getHSSFWorkbook(sheetName, title, content,title2,content2, null);

        //响应到客户端
        try {
        	this.setResponseHeader(response, fileName);
        	OutputStream os = response.getOutputStream();
        	wb.write(os);
        	os.flush();
        	os.close();
        } catch (Exception e) {
        	e.printStackTrace();
        	}
        }
        	

	// 发送响应流方法
	public void setResponseHeader(HttpServletResponse response, String fileName) {
		try {
			try {
				fileName = new String(fileName.getBytes(), "ISO8859-1");
			} catch (UnsupportedEncodingException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			response.setContentType("application/octet-stream;charset=ISO8859-1");
			response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
			response.addHeader("Pargam", "no-cache");
			response.addHeader("Cache-Control", "no-cache");
		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
}

实体类Order

package com.sto.pojo;

import java.util.List;

import lombok.Data;

@Data
public class Order {

	/**
	 * 订单id
	 */
	private Long orderId;
	/**
	 * 订单编码
	 */
	private String orderCode;
	/**
	 * 订单来源
	 */
	private String orderSource;
	/**
	 * 订单价值
	 */
	private Double orderPrice;
	/**
	 * 订单明细
	 */
	private List<OrderDetial> orderDetails;
}

实体类OrderDetail

package com.sto.pojo;

import lombok.Data;

@Data
public class OrderDetial {
	/**
	 * 商品id
	 */
	private Long productId;
	/**
	 * 商品名称
	 */
	private String productName;
	/**
	 * 商品编码
	 */
	private String productCode;
	/**
	 * 商品描述
	 */
	private String productDec;
	/**
	 * 商品重量
	 */
	private String productWeight;
}