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);
}
}