private void ex(List<InvoiceApply> lst) {
XSSFWorkbook wb = new XSSFWorkbook();// 创建一个Excel文件
XSSFSheet sheet = wb.createSheet("发票受理");// 创建一个工作簿
XSSFCellStyle titleStyle = ExcelUtil.getTitleStyle(wb);// 标题样式(样式工具类调用)
XSSFCellStyle attrStyle = ExcelUtil.getAttrStyle(wb);// 属性样式(样式工具类调用)
XSSFCellStyle contStyle = ExcelUtil.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, 6));
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_2 = attrRow.createCell((short) 1);
attrCell_2.setCellValue("发票抬头");
attrCell_2.setCellStyle(attrStyle);
// 第三个单元格
XSSFCell attrCell_3 = attrRow.createCell((short) 2);
attrCell_3.setCellValue("发票类型");
attrCell_3.setCellStyle(attrStyle);
// 第四个单元格
XSSFCell attrCell_4 = attrRow.createCell((short) 3);
attrCell_4.setCellValue("开票总金额(元)");
attrCell_4.setCellStyle(attrStyle);
// 第五个单元格
XSSFCell attrCell_5 = attrRow.createCell((short) 4);
attrCell_5.setCellValue("申请日期");
attrCell_5.setCellStyle(attrStyle);
// 第六个单元格
XSSFCell attrCell_6 = attrRow.createCell((short) 5);
attrCell_6.setCellValue("受理日期");
attrCell_6.setCellStyle(attrStyle);
// 第七个单元格
XSSFCell attrCell_7 = attrRow.createCell((short) 6);
attrCell_7.setCellValue("状态");
attrCell_7.setCellStyle(attrStyle);
for (InvoiceApply InvoiceApply : lst) {
XSSFRow contRow = sheet.createRow(r++);
//第一格
XSSFCell contCell_1 = contRow.createCell((short) 0);
contCell_1.setCellValue(InvoiceApply.getInvoiceno());
contCell_1.setCellStyle(contStyle);
//第二格
XSSFCell contCell_2 = contRow.createCell((short) 1);
contCell_2.setCellValue(InvoiceApply.getLetterhead());
contCell_2.setCellStyle(contStyle);
//第三格
XSSFCell contCell_3 = contRow.createCell((short) 2);
contCell_3.setCellValue(InvoiceApply.getInvoicetype());
contCell_3.setCellStyle(contStyle);
//第四格
XSSFCell contCell_4 = contRow.createCell((short) 3);
//contCell_4.setCellValue(EpointDateUtil.convertDate2String(bill.getBilldate(), "yyyy-MM-dd HH:mm:ss"));
contCell_4.setCellValue(InvoiceApply.getTotalamount());
contCell_4.setCellStyle(contStyle);
//第五格
XSSFCell contCell_5 = contRow.createCell((short) 4);
contCell_5.setCellValue(ExcelUtil.getStringDate(InvoiceApply.getInvoicedate()));
contCell_5.setCellStyle(contStyle);
//第六格
XSSFCell contCell_6 = contRow.createCell((short) 5);
contCell_6.setCellValue(
EpointDateUtil.convertDate2String(InvoiceApply.getAcceptdate(), "yyyy-MM-dd HH:mm:ss"));
contCell_6.setCellStyle(contStyle);
//第七格
XSSFCell contCell_7 = contRow.createCell((short) 6);
DB_CodeItem DB_CodeItem = new DB_CodeItem();
contCell_7.setCellValue(DB_CodeItem.getCodeItemText("发票状态", InvoiceApply.getState() + ""));
contCell_7.setCellStyle(contStyle);
}
//设置单元格长度
sheet.setColumnWidth(0, 4000);
sheet.setColumnWidth(1, 6000);
sheet.setColumnWidth(2, 8000);
sheet.setColumnWidth(3, 4500);
sheet.setColumnWidth(4, 8000);
sheet.setColumnWidth(5, 8000);
sheet.setColumnWidth(6, 4000);
OutputStream output = null;
try {
HttpServletResponse response = getRequestContext().getRes();
response.setContentType("application/vnd.ms-excel");
response.setHeader("Expires", "0");
response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0");
response.setHeader("Pragma", "public");
// 设置头时如果有中文需要用UrlEncode编码一下
String fileName = URLEncoder.encode("发票受理记录.xlsx", "UTF-8");
response.setHeader("Content-disposition",
"attachment;filename=" + StringUtil.getFileNameFromPath(fileName));
output = response.getOutputStream();
wb.write(output);
output.flush();
requestContext.setRequestComplete(true);
}
catch (Exception e) {
e.printStackTrace();
//AlertAjaxMessage(e.getMessage());
}
finally {
if (output != null) {
try {
wb.close();
output.close();
}
catch (IOException e) {
e.printStackTrace();
}
}
}
}
样式工具类
package com.epoint.baohan.bizlogic.util;
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;
import org.apache.poi2.hssf.usermodel.HSSFFont;
public class ExcelUtil {
// 设置标题样式
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(HSSFFont.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;
}
//转换字符串日期
public static String getStringDate(Date date) {
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
if (date != null) {
return sdf.format(date);
}
return "";
}
}