Excel表格导出的工具类

1.处理excel导出的工具类

package CommonUtil.ExcelUtil;

import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.List;

import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * 处理excel导出的工具类 jar包 3.14版本 <artifactId>poi-ooxml</artifactId>
 * <version>3.14</version>
 * 
 */
public class CreateExcelDemo {

	/**
	 * list集合,excelName 表格生成路径(自己随意命名格式如：D:\\test.xls或者D:\\demo.xls)
	 * 
	 */
	public void createExcel(List<Bill> billList, String excelPath) {
		XSSFWorkbook wb = new XSSFWorkbook();// 创建一个Excel文件
		XSSFSheet sheet = wb.createSheet("我的账单");// 创建一个工作簿
		XSSFCellStyle titleStyle = ExcelStyleUtil.getTitleStyle(wb);// 标题样式
		XSSFCellStyle attrStyle = ExcelStyleUtil.getAttrStyle(wb);// 属性样式
		XSSFCellStyle contStyle = ExcelStyleUtil.getContStyle(wb);// 列表样式
		// 创建Excel数据
		// 表格第一行
		XSSFRow titleRow = sheet.createRow((short) 0); // --->创建一行
		titleRow.setHeight((short) 400);
		XSSFCell titleCell = titleRow.createCell((short) 0); // --->创建一个单元格
		titleCell.setCellStyle(titleStyle);
		titleCell.setCellValue("我的账单记录");
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));

		short r = 1;

		XSSFRow attrRow = sheet.createRow((short) r++);

		// 账单编号
		XSSFCell attrCell_1 = attrRow.createCell((short) 0);
		attrCell_1.setCellValue("账单编号");
		attrCell_1.setCellStyle(attrStyle);
		// 消费金额
		XSSFCell attrCell_loanamount = attrRow.createCell((short) 1);
		attrCell_loanamount.setCellValue("消费金额");
		attrCell_loanamount.setCellStyle(attrStyle);
		// 支付时间
		XSSFCell attrCell_7 = attrRow.createCell((short) 2);
		attrCell_7.setCellValue("支付时间");
		attrCell_7.setCellStyle(attrStyle);

		// 往单元格里填充数据
		for (Bill bill : billList) {
			try {
				XSSFRow contRow = sheet.createRow(r++);
				// 账单编号
				XSSFCell contCell_1 = contRow.createCell((short) 0);
				contCell_1.setCellValue(bill.getApplyno());
				contCell_1.setCellStyle(contStyle);
				// 消费金额
				XSSFCell contCell_2 = contRow.createCell((short) 1);
				contCell_2.setCellValue(bill.getApplyAmount().toString());
				contCell_2.setCellStyle(contStyle);
				// 支付时间
				XSSFCell contCell_3 = contRow.createCell((short) 2);
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				contCell_3.setCellValue(sdf.format(bill.getApplyDate()));
				contCell_3.setCellStyle(contStyle);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		// 设置单元格长度
		sheet.setColumnWidth(0, 7000);
		sheet.setColumnWidth(1, 7000);
		sheet.setColumnWidth(2, 9000);

		// 新建一输出文件流
		FileOutputStream fos = null;
		// 把相应的Excel 工作簿存盘
		try {
			fos = new FileOutputStream(excelPath);
			wb.write(fos);
			fos.flush();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (fos != null) {
				try {
					// 操作结束，关闭文件
					wb.close();
					fos.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
}

2.所需的样式类如下

package CommonUtil.ExcelUtil;

import java.awt.Color;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
/**
 * 处理excel样式的工具类  
 * jar包 3.14版本 <artifactId>poi-ooxml</artifactId> <version>3.14</version>
 *           	
 */
public class ExcelStyleUtil { 
	// 设置标题样式
	public static XSSFCellStyle getTitleStyle(XSSFWorkbook wb) {
		// 标题样式
		XSSFCellStyle titleStyle = wb.createCellStyle(); // 样式对象
		titleStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直
		titleStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平

		// 设置字体样式
		XSSFFont font = wb.createFont();// 设置标题字体格式
		font.setFontHeightInPoints((short) 18); // --->设置字体大小
		font.setFontName("仿宋"); // ---》设置字体，是什么类型例如：宋体
		font.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 粗体显示
		titleStyle.setFont(font);

		// 设置背景颜色
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		titleStyle.setFillForegroundColor(new XSSFColor(new Color(242, 242, 242)));

		return titleStyle;
	}

	// 设置属性样式
	public static XSSFCellStyle getAttrStyle(XSSFWorkbook wb) {
		// 属性样式
		XSSFCellStyle attrStyle = wb.createCellStyle(); // 样式对象
		attrStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直
		attrStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平
		attrStyle.setWrapText(true);// 开启自动换行需要setWrapText（true）

		// 设置字体样式
		XSSFFont font = wb.createFont();
		font.setFontHeightInPoints((short) 12);
		font.setFontName("仿宋");
		font.setBold(true);
		attrStyle.setFont(font);

		return attrStyle;
	}

	// 设置列表样式
	public static XSSFCellStyle getContStyle(XSSFWorkbook wb) {
		// 属性样式
		XSSFCellStyle contStyle = wb.createCellStyle(); // 样式对象
		contStyle.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直
		contStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平
		contStyle.setWrapText(true);// 开启自动换行需要setWrapText（true）  
		// 设置字体样式
		XSSFFont font = wb.createFont();  
		font.setFontHeightInPoints((short) 12);
		font.setFontName("仿宋");
		contStyle.setFont(font);
		return contStyle;
	}

}

3.所需的测试实体类如下（Bill）

package CommonUtil.ExcelUtil;

import java.util.Date;

public class Bill {

	private String applyno;
	private Double applyAmount;
	private Date applyDate;

	public String getApplyno() {
		return applyno;
	}

	public void setApplyno(String applyno) {
		this.applyno = applyno;
	}

	public Double getApplyAmount() {
		return applyAmount;
	}

	public void setApplyAmount(Double applyAmount) {
		this.applyAmount = applyAmount;
	}

	public Date getApplyDate() {
		return applyDate;
	}

	public void setApplyDate(Date applyDate) {
		this.applyDate = applyDate;
	}

	@Override
	public String toString() {
		return "Bill [applyno=" + applyno + ", applyAmount=" + applyAmount + ", applyDate=" + applyDate + "]";
	}
	
}

4.以上就是表格导出的具体实现，测试Demo如下

 

package com.test;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import CommonUtil.ExcelUtil.Bill;
import CommonUtil.ExcelUtil.CreateExcelDemo;

public class CreateExcelDemoTest {

	// 获取一个4位随机数
	private static String getMathRandomData(int num) {
		String dataCode = "";
		for (int i = 0; i < num; i++) {
			dataCode += String.valueOf(Math.round(Math.random() * 9));
		}
		return dataCode;
	}

	// 创造一个list
	private static List<Bill> createList() {
		List<Bill> billList = new ArrayList<Bill>();
		for (int i = 1; i < 10; i++) {
			Bill bill = new Bill();
			bill.setApplyno("00" + i);
			bill.setApplyDate(new Date());
			bill.setApplyAmount(Double.parseDouble(getMathRandomData(4)));
			billList.add(bill);
		}
		return billList;
	}

	public static void main(String[] args) throws Exception {

		// 创造一个list
		List<Bill> billList = createList();
		//生成Excel地址
		String excelName = "D:\\test.xls";
		// 生成一个账单
		new CreateExcelDemo().createExcel(billList, excelName);
	}
}