【工具】excel输出(poi)
程序员文章站
2024-01-14 10:22:04
...
@Service
public class SaiDaOutFileService {
private static final Logger log = LoggerFactory.getLogger(SaiDaOutFileService.class);
@Autowired
private SaiDaGetInfoService saiDaGetInfoService;
/**
* @return ByteArrayOutputStream
* @Description 数据供邮箱发送,返回字节数组
* @Param
**/
public ByteArrayOutputStream outFileFromEmail() {
try {
log.info("outFileFromEmail() - write out file from email !");
SXSSFWorkbook wb = writeSaiDaExcelData();
if (wb == null) {
log.warn("outFileFromEmail() - write out file from email error ,SXSSFWorkbook is null !");
return null;
}
//输出流给发送邮件
ByteArrayOutputStream os = new ByteArrayOutputStream();
//写入表中
wb.write(os);
log.info("outFileFromEmail() - write out file from email success !");
return os;
} catch (Exception e) {
log.warn("outFileFromEmail() - write out file from email error!");
return null;
}
}
/**
* @Description 供下载的输出
* @Param HttpServletResponse
**/
public void outFileFromDownload(HttpServletResponse response) {
BufferedOutputStream bos = null;
try {
log.info("outFileFromDownload() - write out file from download start !");
// 清除buffer缓存
response.reset();
String preMonth = getPreMonth();
//生成文件名并解决中文文件名乱码问题
String filename = java.net.URLEncoder.encode(preMonth + SaiDaGlobal.STATEMENT_NAME, SaiDaGlobal.UTF8_CODE_TYPE);
response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(), SaiDaGlobal.ISO_CODE_TYPE));
response.setContentType(SaiDaGlobal.DOWNLOAD_CONTENT_TYPE + ";charset=" + SaiDaGlobal.UTF8_CODE_TYPE);
response.setHeader("Pragma", "no-cache");
response.setHeader("Cache-Control", "no-cache");
response.setDateHeader("Expires", 0);
SXSSFWorkbook wb = writeSaiDaExcelData();
if (wb == null) {
log.warn("outFileFromDownload() - write out file from download error ,SXSSFWorkbook is null !");
return;
}
OutputStream output = response.getOutputStream();
bos = new BufferedOutputStream(output);
wb.write(bos);
bos.flush();
log.info("outFileFromDownload() - write out file from download success !");
} catch (Exception e) {
log.warn("outFileFromDownload() - write out file from download error!");
} finally {
if (bos != null) {
try {
bos.close();
} catch (IOException e) {
log.warn("outFileFromDownload() - clos output stream error!");
}
}
}
}
/**
* @return SXSSFWorkbook
* @Description 得到数据,写工作表并传入SXSSFWorkbook
* @Param
**/
private SXSSFWorkbook writeSaiDaExcelData() {
try {
log.info("writeSaiDaExcelData() - write saiDa excel date start !");
//失效之前的数据
SaiDaGetInfoService.totalAccountBoList = null;
SaiDaGetInfoService.activatedAccountBoList = null;
//设置时间
String date = getPreMonth();
//输入CardTotal表的值
List<TotalAccountBo> totalAccountBoList = saiDaGetInfoService.getCardTotalInfo();
if (CollectionUtils.isEmpty(totalAccountBoList)) {
log.warn("writeSaiDaExcelData() - get total account list error ,list is null !");
return null;
}
//输入ActivatedCard表的值
List<SaiDaActivatedAccountBo> activatedAccountBoList = saiDaGetInfoService.getActivatedCardInfo();
if (CollectionUtils.isEmpty(activatedAccountBoList)) {
log.warn("writeSaiDaExcelData() - get activated account list error ,list is null !");
return null;
}
String[] activatedAccountHeader = getHeaders(SaiDaActivatedAccountBo.class);
//输入ExistType表的值
List<SaiDaExistTypeBo> existTypeBoList = saiDaGetInfoService.getExistTypeInfo();
if (CollectionUtils.isEmpty(totalAccountBoList)) {
log.warn("writeSaiDaExcelData() - get exist type list error ,list is null !");
return null;
}
String[] existTypeHeader = getHeaders(SaiDaExistTypeBo.class);
//输入CardStyleSize表的值
List<SaiDaCardStyleSizeBo> cardStyleSizeList = saiDaGetInfoService.getCardStyleSizeInfo();
if (CollectionUtils.isEmpty(totalAccountBoList)) {
log.warn("writeSaiDaExcelData() - get card style size list error ,list is null !");
return null;
}
//输入CardService表的值
List<CardServiceBo> cardServiceInfo = saiDaGetInfoService.getCardServiceInfo();
if (CollectionUtils.isEmpty(totalAccountBoList)) {
log.warn("writeSaiDaExcelData() - get card service list error ,list is null !");
return null;
}
String[] cardServiceHeader = getHeaders(CardServiceBo.class);
//输入明细表的值
List<SaiDaDetailBo> detailList = saiDaGetInfoService.getDetailList();
if (CollectionUtils.isEmpty(totalAccountBoList)) {
log.warn("writeSaiDaExcelData() - get detail list error ,list is null !");
return null;
}
String detailSheetName = date + SaiDaGlobal.CARD_DETAIL_SHEET_NAME;
//结合传入值
List[] data = {detailList, totalAccountBoList, activatedAccountBoList, existTypeBoList, cardStyleSizeList, cardServiceInfo};
String[][] headers = {SaiDaGlobal.CARD_DETAIL_SHEET_COLUMN_NAME,
SaiDaGlobal.CARD_TOTAL_SHEET_COLUMN_NAME,
activatedAccountHeader,
existTypeHeader,
SaiDaGlobal.STYLE_SIZE_SHEET_COLUMN_NAME,
cardServiceHeader};
Class[] clazzs = {SaiDaDetailBo.class,
TotalAccountBo.class,
SaiDaActivatedAccountBo.class,
SaiDaExistTypeBo.class,
SaiDaCardStyleSizeBo.class,
CardServiceBo.class};
String[] sheetNames = {detailSheetName,
SaiDaGlobal.CARD_TOTAL_SHEET_NAME,
SaiDaGlobal.ACTIVATED_CARD_SHEET_NAME,
SaiDaGlobal.EXIST_TYPE_SHEET_NAME,
SaiDaGlobal.STYLE_SIZE_SHEET_NAME,
SaiDaGlobal.CARD_SERVICE_SHEET_NAME};
//创建excel工具
SXSSFWorkbook wb = new SXSSFWorkbook(1000);
wb.setCompressTempFiles(true);
//根据数据批量创建工作表
int length = data.length;
for (int i = 0; i < length; i++) {
exportSheet(wb, data[i], headers[i], clazzs[i], sheetNames[i]);
}
log.info("writeSaiDaExcelData() - write saiDa excel date success !");
return wb;
} catch (Exception e) {
log.warn("writeSaiDaExcelData() - write saiDa excel date error, " + e);
return null;
}
}
/**
* @Description 根据传入信息输出一张工作表
* @Param [SXSSFWorkbook, List, String[], Class, String]
**/
private static void exportSheet(SXSSFWorkbook wb, List data, String[] header, Class clazz, String sheetName) {
try {
log.info("exportSheet() - export sheet start, sheetName= " + sheetName);
//创建工作表
SXSSFSheet sheet = wb.createSheet(sheetName);
String[] fieldNames = getHeaders(clazz);
//列头设置
CellStyle headerStyle = wb.createCellStyle();
Font headerFont = wb.createFont();
headerFont.setFontHeightInPoints((short) 11);
headerFont.setFontName("等线");
headerFont.setBold(true);
headerStyle.setFont(headerFont);
int rowSize = 0;
SXSSFRow headerRow = sheet.createRow(rowSize); //列头 rowIndex =0
for (int i = 0; i < header.length; i++) {
headerRow.createCell(i).setCellValue(header[i]);
headerRow.getCell(i).setCellStyle(headerStyle);
}
//处理列数据
for (int x = 0; x < data.size(); x++) {
rowSize = 1;
Row rowNew = sheet.createRow(rowSize + x);
//列的信息
for (int i = 0; i < header.length; i++) {
Object o = data.get(x);
String methodName = "get" + fieldNames[i].substring(0, 1).toUpperCase()
+ fieldNames[i].substring(1);//获取属性的get方法名
Method method = o.getClass().getMethod(methodName);
Object invoke = method.invoke(o);//获取属性值
String result;
//处理日期的格式
SimpleDateFormat sdf2 = new SimpleDateFormat(SaiDaGlobal.SHEET_DATE_FORMAT);
if (invoke instanceof Date) result = sdf2.format(invoke);
else result = invoke.toString();
rowNew.createCell(i).setCellValue(result);
//设置列宽
int count = result.getBytes().length;
int width = sheet.getColumnWidth(i);
int nowWidth = (count > SaiDaGlobal.DEFAULT_WIDTH ? count + 5 : SaiDaGlobal.DEFAULT_WIDTH) * 256;
if (width < nowWidth) sheet.setColumnWidth(i, nowWidth);
}
}
log.info("exportSheet() - export sheet success, sheetName= " + sheetName);
} catch (Exception e) {
log.warn("exportSheet() - export sheet error, sheetName= " + sheetName + ", err= " + e);
}
}
/**
* @return String[]
* @Description 根据类得到属性值
* @Param Class
**/
private static String[] getHeaders(Class clazz) {
Field[] fields = clazz.getDeclaredFields();
String[] header = new String[fields.length];
for (int i = 0; i < fields.length; i++) {
header[i] = fields[i].getName();
}
return header;
}
/**
* @return java.lang.String
* @Description 得到上个月的时间
* @Param
**/
private static String getPreMonth() {
//设置时间
Calendar cal = Calendar.getInstance();
return cal.get(Calendar.YEAR) + "年" + cal.get(Calendar.MONTH);
}
}
上一篇: 【工具】定时器(servlet)
下一篇: Spring中的事务控制