/**
* 基类基于子类提供的相关参数数据, 生成JXLS报表
*
* @see #exportXlsForGrid(List, Sort, GroupPropertyFilter) 此方法中基于参数组装好相关的data数据后，调用此方法生成Excel响应
* @param dataMap
*/
protected void exportExcel(
String templateFileName, String exportFileName, Map<String, Object> dataMap) {
// 日期格式定义
dataMap.put("dateFormatter", new SimpleDateFormat(DateUtils.DEFAULT_DATE_FORMAT));
dataMap.put("timeFormatter", new SimpleDateFormat(DateUtils.DEFAULT_TIME_FORMAT));
HttpServletResponse response = ServletActionContext.getResponse();
InputStream fis = null;
OutputStream fos = null;
try {
Resource resource = new ClassPathResource("/template/xls/" + templateFileName);
logger.debug("Open template file inputstream: {}", resource.getURL());
fis = resource.getInputStream();
XLSTransformer transformer = new XLSTransformer();
// generate the excel workbook according to the template and
// parameters
Workbook workbook = transformer.transformXLS(fis, dataMap);
String filename = exportFileName;
filename = new String(filename.getBytes("GBK"), "ISO-8859-1");
response.setHeader("Content-Disposition", "attachment;filename=" + filename);
response.setContentType("application/vnd.ms-excel;charset=utf-8");
fos = response.getOutputStream();
// output the generated excel file
workbook.write(fos);
} catch (Exception e) {
throw new WebException(e.getMessage(), e);
} finally {
IOUtils.closeQuietly(fis);
IOUtils.closeQuietly(fos);
}
}
public void export(OutputStream outputStream) {
XLSTransformer transformer = new XLSTransformer();
InputStream is = null;
try {
is = new BufferedInputStream(new FileInputStream(template));
HSSFWorkbook workbook = transformer.transformXLS(is, dataMap);
workbook.write(outputStream);
outputStream.flush();
} catch (FileNotFoundException e) {
throw new RuntimeException("模版文件没有找到!", e);
} catch (IOException e) {
e
.printStackTrace(); // To change body of catch statement use File | Settings | File
// Templates.
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
throw new RuntimeException("输入流关闭失败!", e);
}
}
if (outputStream != null) {
try {
outputStream.close();
} catch (IOException e) {
throw new RuntimeException("输出流关闭失败!", e);
}
}
}
}
public void exportXLS(Map<String, Object> map, String path, String fileName) {
try {
File templateResource = ResourceUtil.getResourceFile(path);
String templateFileName = templateResource.getAbsolutePath();
String destFileName = Constant.getTempDir() + "/" + fileName;
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS(templateFileName, map, destFileName);
HttpServletResponse response = getResponse();
if (fileName.indexOf(".xlsx") != -1) {
response.setContentType(
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
} else {
response.setContentType("application/vnd.ms-excel");
}
response.setHeader("Content-disposition", "attachment;filename=" + fileName);
File file = new File(destFileName);
InputStream inputStream = new FileInputStream(destFileName);
if (file != null && file.exists()) {
OutputStream os = response.getOutputStream();
byte[] b = new byte[1024];
int length;
while ((length = inputStream.read(b)) > 0) {
os.write(b, 0, length);
}
inputStream.close();
} else {
IOUtils.closeQuietly(inputStream);
throw new RuntimeException("下载失败");
}
} catch (Exception e) {
throw new RuntimeException(e);
}
}
/**
* 单个导出
*
* @param template
* @param data
* @param dest
* @throws IOException
*/
public void transform(String template, Map<String, Object> data, String dest) throws IOException {
try {
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS(template, data, dest);
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
}
}
public void testSelectTimestamp() throws IOException, InvalidFormatException {
Map beans = new HashMap();
ReportManager rm = new ReportManagerImpl(conn, beans);
beans.put("rm", rm);
InputStream is = new BufferedInputStream(getClass().getResourceAsStream(reportTimestamp));
XLSTransformer transformer = new XLSTransformer();
// transformer.setJexlInnerCollectionsAccess( true );
Workbook resultWorkbook = transformer.transformXLS(is, beans);
is.close();
// todo
saveWorkbook(resultWorkbook, reportTimestampDest);
}
/**
* 批量导出
*
* @param data
* @throws FileNotFoundException
*/
public void transform(Map<String, Object> data) throws FileNotFoundException {
FileInputStream templateIs = new FileInputStream(template);
try {
XSSFWorkbook bookTmp = (XSSFWorkbook) transformer.transformXLS(templateIs, data);
bookTmp.setSheetName(0, "第" + count + "页");
if (count == 1) {
book = new SXSSFWorkbook(bookTmp);
} else {
Sheet newSheet = book.createSheet(bookTmp.getSheetName(0));
Sheet sheet = bookTmp.getSheetAt(0);
Util.copySheets(newSheet, sheet);
}
count++;
} catch (ParsePropertyException e) {
e.printStackTrace();
} catch (InvalidFormatException e) {
e.printStackTrace();
} finally {
try {
templateIs.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
@ResponseBody
@RequestMapping(value = "/admin/orders/export.html", method = RequestMethod.GET)
public ResponseEntity<byte[]> export() throws Exception {
Map<String, Object> model = new HashMap<String, Object>();
List<Order> orders = orderRepository.export();
model.put("orders", orders);
XLSTransformer transformer = new XLSTransformer();
Resource resource = new ClassPathResource("/template/OrderExport.xlsx");
Workbook workbook = transformer.transformXLS(resource.getInputStream(), model);
ByteArrayOutputStream baos = new ByteArrayOutputStream();
workbook.write(baos);
final HttpHeaders headers = new HttpHeaders();
headers.setContentType(
MediaType.valueOf("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
headers.set("Content-Disposition", "attachment;Filename=OrderExport.xlsx");
headers.setCacheControl("no-cache");
return new ResponseEntity<byte[]>(baos.toByteArray(), headers, HttpStatus.OK);
}
@Override
public Blob renderTemplate(TemplateBasedDocument templateBasedDocument, String templateName)
throws Exception {
Blob sourceTemplateBlob = getSourceTemplateBlob(templateBasedDocument, templateName);
List<TemplateInput> params = templateBasedDocument.getParams(templateName);
DocumentModel doc = templateBasedDocument.getAdaptedDoc();
Map<String, Object> ctx = FMContextBuilder.build(doc, false);
JXLSBindingResolver resolver = new JXLSBindingResolver();
resolver.resolve(params, ctx, templateBasedDocument);
File workingDir = getWorkingDir();
File generated = new File(workingDir, "JXLSresult-" + System.currentTimeMillis());
generated.createNewFile();
File input = new File(workingDir, "JXLSInput-" + System.currentTimeMillis());
input.createNewFile();
sourceTemplateBlob.transferTo(input);
XLSTransformer transformer = new XLSTransformer();
transformer.transformXLS(input.getAbsolutePath(), ctx, generated.getAbsolutePath());
input.delete();
Blob newBlob = new FileBlob(generated);
String templateFileName = sourceTemplateBlob.getFilename();
// set the output file name
String targetFileExt = FileUtils.getFileExtension(templateFileName);
String targetFileName =
FileUtils.getFileNameNoExt(templateBasedDocument.getAdaptedDoc().getTitle());
targetFileName = targetFileName + "." + targetFileExt;
newBlob.setFilename(targetFileName);
// mark the file for automatic deletion on GC
Framework.trackFile(generated, newBlob);
return newBlob;
}
public void testFormatting() throws IOException, ParsePropertyException, InvalidFormatException {
Map<String, Object> beans = new HashMap<String, Object>();
beans.put("beans", beanList);
beans.put("ii", ii);
beans.put("fmt", new Formatter());
InputStream is = new BufferedInputStream(getClass().getResourceAsStream(formatterBeanXLS));
XLSTransformer transformer = new XLSTransformer();
Workbook resultWorkbook = transformer.transformXLS(is, beans);
is.close();
is = new BufferedInputStream(getClass().getResourceAsStream(formatterBeanXLS));
Workbook sourceWorkbook = WorkbookFactory.create(is);
Sheet sourceSheet = sourceWorkbook.getSheetAt(0);
Sheet resultSheet = resultWorkbook.getSheetAt(0);
// assert and formatting and styles
is.close();
saveWorkbook(resultWorkbook, formatterBeanDestXLS);
}
/**
* @param request
* @param response
* @param exportName 生成的下载文件名
* @param templateName 模版文件名
* @param dataMap 要导出的数据
* @throws Exception
*/
public void export(
HttpServletRequest request,
HttpServletResponse response,
String exportName,
String templateName,
Map<String, Object> dataMap)
throws Exception {
response.setContentType("application/vnd.ms-excel");
response.addHeader("Content-Disposition", "attachment;filename=\"" + exportName + "\"");
String templateFullPath =
request.getSession().getServletContext().getRealPath("/") + templateName;
XLSTransformer transformer = new XLSTransformer();
InputStream is = null;
OutputStream os = null;
try {
is = new BufferedInputStream(new FileInputStream(templateFullPath));
HSSFWorkbook workbook = transformer.transformXLS(is, dataMap);
os = response.getOutputStream();
workbook.write(os);
os.flush();
} finally {
if (is != null) {
try {
is.close();
} catch (IOException e) {
throw new RuntimeException("输入流关闭失败!", e);
}
}
if (os != null) {
try {
os.close();
} catch (IOException e) {
throw new RuntimeException("输出流关闭失败!", e);
}
}
}
}
/**
* 根据模板生成Excel文件.
*
* @param templateFileName 模板文件.
* @param list 模板中存放的数据.
* @param report 存放打印信息，如果打印时间等
* @param resultFileName 生成的文件.
*/
public void createExcel(
String templateFileName, List<?> list, ReportEntity report, String resultFileName) {
try {
// 创建XLSTransformer对象
XLSTransformer transformer = new XLSTransformer();
// 获取java项目编译后根路径
URL url = this.getClass().getClassLoader().getResource("");
File file = new File(url.getFile());
String path = file.getPath();
// 得到模板文件路径
String srcFilePath = path + File.separator + "template" + File.separator + templateFileName;
Map<String, Object> map = new HashMap<String, Object>();
map.put("list", list);
map.put("report", report);
String destFilePath = path + File.separator + "download" + File.separator + resultFileName;
// 生成Excel文件
transformer.transformXLS(srcFilePath, map, destFilePath);
} catch (Exception e) {
throw new RuntimeException("error happens...", e);
}
}
@SuppressWarnings("unchecked")
public String makeReport() {
String sql = "";
if (getItemUids() != null
&& (!getItemUids().trim().equals(""))
&& (!getItemUids().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getItemUids().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.itemUid like '" + getItemUids().trim() + "%'";
}
Organization orgSelected = null;
if (getOrgCode() != null && !getOrgCode().trim().equals("")) {
orgSelected = orgDao.findByOrgId(getOrgCode());
String innercodeSelected = (String) orgDao.getOrgInnerInfo(orgSelected)[0];
if (orgSelected.getOrgType().trim().equals("00"))
sql += " and v.orgInnerCode01 like '" + innercodeSelected + "%'";
else if (orgSelected.getOrgType().trim().equals("01"))
sql += " and v.orgInnerCode02 like '" + innercodeSelected + "%'";
else if (orgSelected.getOrgType().trim().equals("02"))
sql += " and v.orgInnerCode03 like '" + innercodeSelected + "%'";
}
if (getTermSeqs() != null
&& (!getTermSeqs().trim().equals(""))
&& (!getTermSeqs().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getTermSeqs().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.termseq = '" + getTermSeqs().trim() + "'";
}
if (getTermnos() != null
&& (!getTermnos().trim().equals(""))
&& (!getTermnos().trim().equals(AppCommonVarRegiter.ALL_VALUE_TAG))
&& (!getTermnos().trim().equals(AppCommonVarRegiter.NULL_VALUE_TAG))) {
sql += " and v.termno = '" + getTermnos().trim() + "'";
}
if (this.getSearchStartDate() != null && !getSearchStartDate().equals("")) {
sql =
sql
+ " and v.costDate >='"
+ DateConvert.convertAtoB(getSearchStartDate(), "yyyy-MM", "yyyyMM")
+ "'";
}
if (this.getSearchEndDate() != null && !getSearchEndDate().equals("")) {
sql =
sql
+ " and v.costDate <='"
+ DateConvert.convertAtoB(getSearchEndDate(), "yyyy-MM", "yyyyMM")
+ "'";
}
String state = this.getSearchPayState();
if (state != null && !state.equals("")) {
if (state != "0" && !state.equals("0")) {
sql = sql + " and v.payState ='" + state + "'";
}
}
// 构造查询
String queryStr =
" select v from ComsPayCommunication v where 1=1 " + sql + " order by v.termno,v.costDate";
List<ComsPayCommunication> dataList = new ArrayList<ComsPayCommunication>();
dataList.addAll(comsPayCommunicationDao.getAllDataByQuery(queryStr));
if (dataList != null && dataList.size() > 0) {
// 翻译
for (ComsPayCommunication pay : dataList) {
pay.setCostDate(
AppCommonVarRegiter.changeTimeStyle("yyyyMM", pay.getCostDate(), "yyyy-MM"));
// 运营机构
pay.setOrgInnerCode01(orgDao.findByInnerCode(pay.getOrgInnerCode01()).getOrgNameCn());
// 项目
pay.setItemUid(comsProjectDao.findById(pay.getItemUid()).getItemName());
}
Authuser authuser = (Authuser) request.getSession().getAttribute("currentUser");
filename = "通讯费支付历史明细报表.xls";
Map beans = new HashMap();
beans.put("orgName", orgSelected.getOrgNameCn());
beans.put("authorUser", authuser.getRealName());
Date createDate = new Date();
beans.put("createDate", createDate);
beans.put("dataList", dataList);
HSSFWorkbook wb = new HSSFWorkbook();
Configuration config = new Configuration();
XLSTransformer transformer = new XLSTransformer(config);
InputStream is = null;
try {
is = new FileInputStream(request.getRealPath("/WEB-INF") + filepath);
wb = transformer.transformXLS(is, beans);
// 产生POI输出流
ByteArrayOutputStream os = new ByteArrayOutputStream();
wb.write(os);
excelStream = new ByteArrayInputStream(os.toByteArray());
} catch (IOException e) {
e.printStackTrace();
}
message = "生成报表：" + filename;
new LogAssistant(
authuser.getUsername().trim(),
request.getRemoteAddr() + ":" + TrackOpterationEnum.MAKEREPORT.getOperation(),
message)
.logUserOpt();
return "excel";
} else {
message = "没有通讯费支付记录!";
}
return SUCCESS;
}
