package com.mall.common.utils;
import com.mall.common.entities.ViewTradeRecordsEntity;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.List;
import java.util.UUID;
public class ExcelUtils {
public static HSSFWorkbook createExcel(List<ViewTradeRecordsEntity> viewTradeRecordsEntities,String fileSaveFolder,HttpServletResponse response) {
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet("交易报表");
// 第三步,在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow(0);
// 第四步,创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
HSSFCell cell = row.createCell((short) 0);
cell.setCellValue("序号");
cell.setCellStyle(style);
cell = row.createCell((short) 1);
cell.setCellValue("店铺名称");
cell.setCellStyle(style);
cell = row.createCell((short) 2);
cell.setCellValue("店铺房号");
cell.setCellStyle(style);
cell = row.createCell((short) 3);
cell.setCellValue("楼层");
cell.setCellStyle(style);
cell = row.createCell((short) 4);
cell.setCellValue("手机号");
cell.setCellStyle(style);
cell = row.createCell((short) 5);
cell.setCellValue("会员名");
cell.setCellStyle(style);
cell = row.createCell((short) 6);
cell.setCellValue("成交时间");
cell.setCellStyle(style);
cell = row.createCell((short) 7);
cell.setCellValue("成交金额(元)");
cell.setCellStyle(style);
cell = row.createCell((short) 8);
cell.setCellValue("积分");
cell.setCellStyle(style);
cell = row.createCell((short) 9);
cell.setCellValue("状态");
cell.setCellStyle(style);
for (int i = 0; i < viewTradeRecordsEntities.size(); i++) {
row = sheet.createRow((int) i + 1);
//Member stu = (Member) list.get(i);
// 第四步,创建单元格,并设置值
row.createCell((short) 0).setCellValue(i);
cell.setCellStyle(style);
row.createCell((short) 1).setCellValue(viewTradeRecordsEntities.get(i).shopName);
cell.setCellStyle(style);
row.createCell((short) 2).setCellValue(viewTradeRecordsEntities.get(i).room);
cell.setCellStyle(style);
row.createCell((short) 3).setCellValue(viewTradeRecordsEntities.get(i).floor);
cell.setCellStyle(style);
row.createCell((short) 4).setCellValue(viewTradeRecordsEntities.get(i).mobile);
cell.setCellStyle(style);
if (viewTradeRecordsEntities.get(i).memberName!=null){
try {
row.createCell((short) 5).setCellValue(java.net.URLDecoder.decode(viewTradeRecordsEntities.get(i).memberName,"UTF-8"));
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
}else {
row.createCell((short) 5).setCellValue(viewTradeRecordsEntities.get(i).memberName);
}
cell.setCellStyle(style);
row.createCell((short) 6).setCellValue(viewTradeRecordsEntities.get(i).tradeTime);
cell.setCellStyle(style);
row.createCell((short) 7).setCellValue(viewTradeRecordsEntities.get(i).realAmount);
cell.setCellStyle(style);
if (viewTradeRecordsEntities.get(i).points == null) {
String k = null;
row.createCell((short) 8).setCellValue(k);
cell.setCellStyle(style);
} else {
row.createCell((short) 8).setCellValue(viewTradeRecordsEntities.get(i).points + "");
cell.setCellStyle(style);
}
if (viewTradeRecordsEntities.get(i).status == null) {
String k = null;
row.createCell((short) 8).setCellValue(k);
cell.setCellStyle(style);
} else {
row.createCell((short) 9);
cell.setCellValue(viewTradeRecordsEntities.get(i).status);
cell.setCellStyle(style);
}
}
// 第六步,将文件存到指定位置
try {
String urlname = UUID.randomUUID().toString();
FileOutputStream fout = new FileOutputStream(fileSaveFolder + "/" + urlname + ".xls");
wb.write(fout);
byte[] buffer = new byte[1024];
FileInputStream fis;
BufferedInputStream bis;
OutputStream os;
os = response.getOutputStream();
File file = new File(fileSaveFolder + "/" + urlname + ".xls");
fis = new FileInputStream(file);
bis = new BufferedInputStream(fis);
int i = bis.read(buffer);
while (i != -1) {
os.write(buffer);
i = bis.read(buffer);
}
os.close();
bis.close();
fis.close();
fout.close();
} catch (Exception e) {
e.printStackTrace();
}
return wb;
}
}
在这里由于我的mysql中member需要解码才能插入excel,所以加了
java.net.URLDecoder.decode(viewTradeRecordsEntities.get(i).memberName,"UTF-8")