导出功能接口:
public class exportList {
private final static Logger logger = LoggerFactory.getLogger(exportList.class);
/**
* 导出资金统计列表
* @param sumList
* @param fundRecordsList
*/
public static void fundRecordsExport(List<ProFundRecords> sumList, List<ProFundRecords> fundRecordsList, HttpServletResponse response){
OutputStream os = null;
try {
//读取的模板文件的位置(在resources下的resources文件夹里面)
String srcPath = GetResource.class.getClassLoader().getResource("/resources/资金流动统计.xls").getPath();
POIFSFileSystem fs =new POIFSFileSystem(new FileInputStream(srcPath));
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheet("资金流动统计");
//插入总支出和总到账
for(int i=0; i<sumList.size(); i++){
HSSFRow row1 = sheet.getRow(2);
if("0".equals(sumList.get(i).getType())){
row1.getCell(1).setCellValue(sumList.get(i).getCostSum().doubleValue());
}else{
row1.getCell(4).setCellValue(sumList.get(i).getCostSum().doubleValue());
}
}
//遍历插入列表数据
for(int i=0; i<fundRecordsList.size(); i++){
HSSFRow row3 = sheet.createRow(i+4);
row3.createCell(0).setCellValue(fundRecordsList.get(i).getProProject().getName());
row3.createCell(1).setCellValue(fundRecordsList.get(i).getDepartment().getName());
row3.createCell(2).setCellValue(DictUtils.getDictLabel(fundRecordsList.get(i).getProType(),"pro_type",""));
row3.createCell(3).setCellValue(DictUtils.getDictLabel(fundRecordsList.get(i).getProType(),"spending_account",""));
row3.createCell(4).setCellValue(fundRecordsList.get(i).getCost().doubleValue());
//创建千位符金额格式
CellStyle cellStyle = wb.createCellStyle();
DataFormat format= wb.createDataFormat();
cellStyle.setDataFormat(format.getFormat("#,##0.00"));
row3.getCell(4).setCellStyle(cellStyle);
row3.createCell(5).setCellValue(DictUtils.getDictLabel(fundRecordsList.get(i).getModuleType(),"fund_module_type",""));
row3.createCell(6).setCellValue(fundRecordsList.get(i).getTime());
//创建时间格式
CellStyle cellStyle1 = wb.createCellStyle();
DataFormat format1= wb.createDataFormat();
cellStyle1.setDataFormat(format1.getFormat("yyyy-MM-dd"));//设置时间格式
row3.getCell(6).setCellStyle(cellStyle1);
}
response.reset();
os =response.getOutputStream(); //获取响应的输出流
response.setContentType("text/html; charset=UTF-8"); //设置编码字符
response.setContentType("application/octet-stream"); //设置内容类型为下载类型
response.setHeader("Content-disposition", "attachment;filename="+URLEncoder.encode("资金流动统计.xls", "utf-8"));
wb.write(os);
}catch (Exception e) {
e.printStackTrace();
}finally{
if(os != null){
try{
os.flush();//释放缓存
os.close();//关闭输出流
}catch (Exception e) {
logger.info("关闭输出流失败");
e.printStackTrace();
}
}
}
}
}
=====================设置格式===================
小数格式:
cell.setCellValue(1.2);
CellStyle cellStyle = workBook.createCellStyle();
cellStyle.setDataFormat(DataFormat.getBuiltinFormat("0.00"));
cell.setCellStyle(cellStyle);
货币格式:
cell.setCellValue(20000);
CellStyle cellStyle = workBook.createCellStyle();
DataFormat format= workBook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("¥#,##0"));
cell.setCellStyle(cellStyle);
百分比格式:
cell.setCellValue(20);
CellStyle cellStyle = workBook.createCellStyle();
cellStyle.setDataFormat(DataFormat.getBuiltinFormat("0.00%"));
cell.setCellStyle(cellStyle);
中文大写格式
cell.setCellValue(20000);
CellStyle cellStyle = workBook.createCellStyle();
DataFormat format= workBook.createDataFormat();
cellStyle.setDataFormat(format.getFormat("[DbNum2][$-804]0"));
cell.setCellStyle(cellStyle);
科学计数格式
cell.setCellValue(20000);
CellStyle cellStyle = workBook.createCellStyle();
cellStyle.setDataFormat(DataFormat.getBuiltinFormat("0.00E+00"));
cell.setCellStyle(cellStyle);