excel导出工具类

package com.piesat.util;


import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import javax.servlet.http.HttpServletRequest;


import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
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.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


import com.piesat.uim.entity.LogUser;
import com.piesat.uim.entity.TransactionRecord;


/**
 * 导出Excel工具类
 *
 */
public class ExportExcelUtil {

public static String exportLogUser(HttpServletRequest request,LogUser param,List<LogUser> logUserList) {
String path = request.getSession().getServletContext().getRealPath("")+File.separator+"file"+File.separator;
String fileName = System.currentTimeMillis()+".xlsx";
XSSFWorkbook workbook = createXSSFWorkbook4LogUser(logUserList, param);
exportExcel4LogUser(path, fileName, workbook);//导出
return fileName;
    }

public static String exportTransationRecord(HttpServletRequest request,TransactionRecord param,List<TransactionRecord> recordList) {
String path = request.getSession().getServletContext().getRealPath("")+File.separator+"file"+File.separator;
String fileName = System.currentTimeMillis()+".xlsx";
XSSFWorkbook workbook = createXSSFWorkbook(recordList, param);
exportExcel(path, fileName, workbook);//导出
return fileName;
    }

private static XSSFWorkbook createXSSFWorkbook(List<TransactionRecord> recordList,TransactionRecord param){
StringBuffer paramName = new StringBuffer();
paramName.append("订单时间：").append(param.getBeforeOrderTime()==null?"":param.getBeforeOrderTime()).append(param.getAfterOrderTime()==null?"":param.getAfterOrderTime());
paramName.append("  ");
paramName.append("对账入库时间：").append(param.getBeforeStorageTime()==null?"":param.getBeforeStorageTime()).append(param.getAfterStorageTime()==null?"":param.getAfterStorageTime());
paramName.append("  ");
String tradeType = "";
if (StringUtils.isNotBlank(param.getTradeType())){
tradeType = "01".equals(param.getTradeType())?"银联":"微信支付宝";
}
paramName.append("交易类型：").append(tradeType);
paramName.append("  ");
String productType = "";
if (StringUtils.isNotBlank(param.getProductType())){
productType = "000202".equals(param.getProductType())?"b2b":"b2c";
} 
paramName.append("产品类型（仅银行）：").append(productType);
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet worksheet = workbook.createSheet("对账记录表");
//设置字体
XSSFCellStyle numbStyle = setNumberStyle(workbook); // 数字样式
XSSFCellStyle biaotouStyle = setTableHeaderStyle(workbook); // 汉字样式
List<String> headList = getHeadName();
setTitle(worksheet, workbook, "交易对账记录", headList.size()-1);
setParam(worksheet, workbook, paramName.toString(), headList.size()-1);
setHeadCell(worksheet, headList, biaotouStyle);
setDataCell(recordList, worksheet,numbStyle);
return workbook;
}
private static XSSFWorkbook createXSSFWorkbook4LogUser(List<LogUser> logUserList,LogUser param){
StringBuffer paramName = new StringBuffer();
paramName.append("登陆时间：").append(param.getLoginTimeOne()==null?"":param.getLoginTimeOne()).append(param.getLoginTimeTwo()==null?"":param.getLoginTimeTwo());
paramName.append("  ");
paramName.append("访问时间：").append(param.getVisitingTimeOne()==null?"":param.getVisitingTimeOne()).append(param.getVisitingTimeTwo()==null?"":param.getVisitingTimeTwo());
paramName.append("  ");
paramName.append("登出时间：").append(param.getLogoutTimeOne()==null?"":param.getLogoutTimeOne()).append(param.getLogoutTimeTwo()==null?"":param.getLogoutTimeTwo());
paramName.append("  ");
/*String tradeType = "";
if (StringUtils.isNotBlank(param.getTradeType())){
tradeType = "01".equals(param.getTradeType())?"银联":"微信支付宝";
}
paramName.append("交易类型：").append(tradeType);
paramName.append("  ");
String productType = "";
if (StringUtils.isNotBlank(param.getProductType())){
productType = "000202".equals(param.getProductType())?"b2b":"b2c";
} 
paramName.append("产品类型（仅银行）：").append(productType);*/
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet worksheet = workbook.createSheet("网站用户登录管理");
//设置字体
XSSFCellStyle numbStyle = setNumberStyle(workbook); // 数字样式
XSSFCellStyle biaotouStyle = setTableHeaderStyle(workbook); // 汉字样式
List<String> headList = getHeadName4LogUser();
setTitle(worksheet, workbook, "登录管理记录", headList.size()-1);
setParam(worksheet, workbook, paramName.toString(), headList.size()-1);
setHeadCell(worksheet, headList, biaotouStyle);
setDataCell4LogUser(logUserList, worksheet,numbStyle);
return workbook;
}

private static void exportExcel(String exportPath,String fileName,XSSFWorkbook workbook){
OutputStream fileOutput = null;
try {
File file = new File(exportPath + fileName);
if (!file.exists()) {
file.getParentFile().mkdirs();
}
fileOutput = new FileOutputStream(file);
workbook.write(fileOutput);
System.out.println("对账记录导出完成！");
} catch (FileNotFoundException e) {
System.out.println("对账记录导出失败！FileNotFoundException");
e.printStackTrace();
} catch (IOException e) {
System.out.println("对账记录导出失败！IOException");
e.printStackTrace();
} finally{
if(fileOutput != null){
try {
fileOutput.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
private static void exportExcel4LogUser(String exportPath,String fileName,XSSFWorkbook workbook){
OutputStream fileOutput = null;
try {
File file = new File(exportPath + fileName);
if (!file.exists()) {
file.getParentFile().mkdirs();
}
fileOutput = new FileOutputStream(file);
workbook.write(fileOutput);
System.out.println("数据导出完成！");
} catch (FileNotFoundException e) {
System.out.println("数据导出失败！FileNotFoundException");
e.printStackTrace();
} catch (IOException e) {
System.out.println("数据导出失败！IOException");
e.printStackTrace();
} finally{
if(fileOutput != null){
try {
fileOutput.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}

public static List<String> getHeadName4LogUser(){
List<String> headName = new ArrayList<String>();
headName.add("用户名");
headName.add("用户ip");
headName.add("登录时间");
headName.add("访问时间");
headName.add("登出时间");
// headName.add("产品类型（仅银行）");
// headName.add("银行流水号");
// headName.add("对账入库时间");
return headName;
}

public static List<String> getHeadName(){
List<String> headName = new ArrayList<String>();
headName.add("订单编号");
headName.add("商户订单时间");
headName.add("交易类型");
headName.add("交易金额");
headName.add("手续费");
headName.add("产品类型（仅银行）");
headName.add("银行流水号");
headName.add("对账入库时间");
return headName;
}

private static void setDataCell(List<TransactionRecord> recordList,XSSFSheet worksheet,XSSFCellStyle numbStyle){
TransactionRecord record = null;
//在第3行开始填写 数据
for(int i =0;i< recordList.size();i++){
record = recordList.get(i);
XSSFRow rowi = worksheet.createRow(i + 3);
Cell cellData0 = rowi.createCell(0);
cellData0.setCellValue(record.getOrderNumber());
cellData0.setCellStyle(numbStyle);
Cell cellData1 = rowi.createCell(1);
cellData1.setCellValue(record.getOrderTime());
cellData1.setCellStyle(numbStyle);
Cell cellData2 = rowi.createCell(2);
cellData2.setCellValue(record.getTradeType());
cellData2.setCellStyle(numbStyle);
Cell cellData3 = rowi.createCell(3);
cellData3.setCellValue(record.getTransactionAmount());
cellData3.setCellStyle(numbStyle);
Cell cellData4 = rowi.createCell(4);
cellData4.setCellValue(record.getServiceCharge());
cellData4.setCellStyle(numbStyle);
Cell cellData5 = rowi.createCell(5);
cellData5.setCellValue(record.getProductType());
cellData5.setCellStyle(numbStyle);
Cell cellData6 = rowi.createCell(6);
cellData6.setCellValue(record.getBankSerialNumber());
cellData6.setCellStyle(numbStyle);
Cell cellData7 = rowi.createCell(7);
cellData7.setCellValue(record.getStorageTime());
cellData7.setCellStyle(numbStyle);
}
}

private static void setDataCell4LogUser(List<LogUser> logUserList,XSSFSheet worksheet,XSSFCellStyle numbStyle){
LogUser record = null;
//在第3行开始填写 数据
for(int i =0;i< logUserList.size();i++){
record = logUserList.get(i);
XSSFRow rowi = worksheet.createRow(i + 3);
Cell cellData0 = rowi.createCell(0);
cellData0.setCellValue(record.getUserName());
cellData0.setCellStyle(numbStyle);
Cell cellData1 = rowi.createCell(1);
cellData1.setCellValue(record.getIpAddress());
cellData1.setCellStyle(numbStyle);
Cell cellData2 = rowi.createCell(2);
cellData2.setCellValue(record.getLoginTime());
cellData2.setCellStyle(numbStyle);
Cell cellData3 = rowi.createCell(3);
cellData3.setCellValue(record.getVisitingTime());
cellData3.setCellStyle(numbStyle);
Cell cellData4 = rowi.createCell(4);
cellData4.setCellValue(record.getLogoutTime());
cellData4.setCellStyle(numbStyle);
/*Cell cellData5 = rowi.createCell(5);
cellData5.setCellValue(record.getProductType());
cellData5.setCellStyle(numbStyle);
Cell cellData6 = rowi.createCell(6);
cellData6.setCellValue(record.getBankSerialNumber());
cellData6.setCellStyle(numbStyle);
Cell cellData7 = rowi.createCell(7);
cellData7.setCellValue(record.getStorageTime());
cellData7.setCellStyle(numbStyle);*/
}
}

private static void setTitle(XSSFSheet worksheet,XSSFWorkbook workbook,String titleName,int num){
//CellRangeAddress  对象的构造方法需要传入合并单元格的首行、最后一行、首列、最后一列。
        CellRangeAddress cra=new CellRangeAddress(0, 0, 0, num);
        //在sheet里增加合并单元格  
        worksheet.addMergedRegion(cra);
        XSSFFont biaotiFont = workbook.createFont();
        biaotiFont.setFontName("华文中宋");    
        biaotiFont.setFontHeightInPoints((short) 18);
        XSSFCellStyle biaotiStyle = workbook.createCellStyle(); // 汉字样式
        biaotiStyle.setWrapText(true); // 设置自动换行
        biaotiStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置居中
        biaotiStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
        biaotiStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
        biaotiStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
        biaotiStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
        biaotiStyle.setFont(biaotiFont); // 选择需要用到的字体格式
        XSSFRow title1Row = worksheet.createRow(0);
        title1Row.setHeight((short)700);
        XSSFCell cell_1 = title1Row.createCell(0);
        cell_1.setCellValue(titleName);
        cell_1.setCellStyle(biaotiStyle);
}

private static void setParam(XSSFSheet worksheet,XSSFWorkbook workbook,String paramName,int num){
CellRangeAddress cra=new CellRangeAddress(1, 1, 0, num);
        //在sheet里增加合并单元格  
        worksheet.addMergedRegion(cra);
        XSSFFont danweiFont = workbook.createFont();
        danweiFont.setFontName("宋体");    
        danweiFont.setFontHeightInPoints((short) 10);
        XSSFCellStyle paramStyle = workbook.createCellStyle(); // 汉字样式
        paramStyle.setWrapText(true); // 设置自动换行
        paramStyle.setAlignment(XSSFCellStyle.ALIGN_RIGHT); // 靠右
        paramStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
        paramStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
        paramStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
        paramStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
        paramStyle.setFont(danweiFont); // 选择需要用到的字体格式
        XSSFRow danweiRow = worksheet.createRow(1);
        XSSFCell cell_3 = danweiRow.createCell(0);
        cell_3.setCellValue(paramName);
        cell_3.setCellStyle(paramStyle);
}

/**
* 设置数字font
* @param workbook excel工作表
* @return
*/
private static XSSFCellStyle setNumberStyle(XSSFWorkbook workbook){
XSSFFont numbFont = workbook.createFont();
numbFont.setFontName("宋体");
numbFont.setFontHeightInPoints((short) 10);
XSSFCellStyle numbStyle = workbook.createCellStyle(); // 数字样式
numbStyle.setWrapText(true);
numbStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);
numbStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
numbStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
numbStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
numbStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
numbStyle.setFont(numbFont);
return numbStyle;
}

/**
* 设置表头font
* @param workbook excel工作表
* @return
*/
private static XSSFCellStyle setTableHeaderStyle(XSSFWorkbook workbook){
XSSFFont biaotouFont = workbook.createFont();
biaotouFont.setFontName("宋体");
biaotouFont.setFontHeightInPoints((short) 10);
biaotouFont.setBoldweight(XSSFFont.BOLDWEIGHT_BOLD);// 加粗
XSSFCellStyle biaotouStyle = workbook.createCellStyle(); // 汉字样式
biaotouStyle.setWrapText(false); // 设置自动换行
biaotouStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER); // 设置居中
biaotouStyle.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
biaotouStyle.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
biaotouStyle.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
biaotouStyle.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框
biaotouStyle.setFont(biaotouFont); // 选择需要用到的字体格式
return biaotouStyle;
}

private static void setHeadCell(XSSFSheet worksheet,List<String> totalList,XSSFCellStyle biaotouStyle){
XSSFRow tRow = worksheet.createRow(2);
        XSSFCell cell = null;
        for (int i = 0; i < totalList.size(); i++) {
            cell = tRow.createCell(i); // 获取每列单元格
            cell.setCellValue(totalList.get(i));
            cell.setCellStyle(biaotouStyle); // 设置样式
            //中文宽度自适应
            worksheet.autoSizeColumn(2, true);
//            sheet.setColumnWidth((short)column,(short)width);
        }
}
}