package com.lenovo.btcp.modules.tools;
import java.io.BufferedInputStream;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.dbutils.DbUtils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import com.alibaba.druid.sql.ast.statement.SQLSelect;
import com.alibaba.druid.sql.ast.statement.SQLSelectQuery;
import com.alibaba.druid.sql.ast.statement.SQLSelectStatement;
import com.fasterxml.jackson.databind.exc.InvalidFormatException;
import com.lenovo.btcp.modules.workDay.controller.WorkDayController;
import com.lenovo.btcp.util.ConnectionTool;
import com.lenovo.btcp.util.StringUtils;
import com.lenovo.btcp.util.pdf.impl.Excel_TO_PDF;
import com.lenovo.btcp.util.pdf.pagesetting.PageSetting;
import com.lenovo.btcp.util.pdf.pdfpag.PdfPageSize;
import com.lenovo.btcp.util.readXls.GenerateTitle;
import net.sf.jxls.exception.ParsePropertyException;
import net.sf.jxls.transformer.XLSTransformer;
public class ExportExcel {
private static final Logger logger = Logger.getLogger(ExportExcel.class);
/**
*
* @param desName
* @param response
* @param beans
*/
public static void export(String desName,HttpServletResponse response,List<?> beans,Class<?> cls,Map replaceMap){
Map<Object, Object> map = new HashMap<Object, Object>();
GenerateTitle.GenerateExcel(cls,beans, map,replaceMap);
ExportExcel.export(desName, response, map);
}
public static void exportFile(String desName,OutputStream os,List<?> beans,Class<?> cls,Map replaceMap) throws IOException{
Map<Object, Object> map = new HashMap<Object, Object>();
GenerateTitle.GenerateExcel(cls,beans, map,replaceMap);
ExcelUtil.writeExcel(os, (List)map.get("listHead"), (List)map.get("listBody"));
}
public static void export(String desName,HttpServletResponse response,List<?> beans){
export(desName, response, beans,null,null);
}
public static void export(String desName,HttpServletResponse response,List<?> beans,Map replaceMap){
export(desName, response, beans,null,replaceMap);
}
/**
* @author suzy2
* @param desName 导出文件名称
* @param response
* @param beans 导出对应用的 Map对象
*/
public static void export(String desName,HttpServletResponse response,Map beans){
export(null, desName, response, beans);
}
/**
* @author suzy2
* @param temmpTlateName 模板名称
* @param desName 导出文件名称
* @param response
* @param beans 导出对应用的 Map对象
*/
public static void export(String temmpTlateName,String desName,HttpServletResponse response,Map beans){
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateStr = format.format(new Date());
response.setContentType("Application/msexcel;charset=UTF-8");
String fileName = /*"工作日历导出详情"*/ desName+dateStr+".xls";
if(StringUtils.isBlank(temmpTlateName) || "/template/common/commonTemplate.xls".equals(temmpTlateName) ){
fileName=fileName+"x";
}
try {
fileName = new String(fileName.getBytes("GBK"),"iso-8859-1");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setContentType("application/vnd.ms-excel");
if(StringUtils.isBlank(temmpTlateName) || "/template/common/commonTemplate.xls".equals(temmpTlateName) ){
try {
ExcelUtil.writeExcel(response.getOutputStream(), (List)beans.get("listHead"), (List)beans.get("listBody"));
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}else{
export_impl(temmpTlateName, desName, response, beans);
}
}
/**
* @author suzy2
* @param temmpTlateName 模板名称
* @param desName 导出文件名称
* @param response
* @param beans 导出对应用的 Map对象
*/
public static void export_impl(String temmpTlateName,String desName,HttpServletResponse response,Map beans){
String templateFileName= WorkDayController.class.getClassLoader().getResource("").getPath() +temmpTlateName;// "/template/workDayTemplate.xls";
XLSTransformer transformer = new XLSTransformer();
InputStream in=null;
OutputStream out=null;
//设置响应
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//格式化函数
beans.put("dateFormat", dateFormat);
try {
in=new BufferedInputStream(new FileInputStream(templateFileName));
Workbook workbook=transformer.transformXLS(in, beans);
out=response.getOutputStream();
workbook.write(out);
out.flush();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ParsePropertyException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (in!=null){try {in.close();} catch (IOException e) {}}
if (out!=null){try {out.close();} catch (IOException e) {}}
}
}
/**
* @author suzy2
* @param temmpTlateName 模板名称
* @param desName 导出文件名称
* @param response
* @param beans 导出对应用的 Map对象
*/
public static void export_impl(String temmpTlateName,String desName,OutputStream out,Map beans){
String templateFileName= WorkDayController.class.getClassLoader().getResource("").getPath() +temmpTlateName;// "/template/workDayTemplate.xls";
XLSTransformer transformer = new XLSTransformer();
InputStream in=null;
// OutputStream out=null;
//设置响应
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//格式化函数
beans.put("dateFormat", dateFormat);
try {
in=new BufferedInputStream(new FileInputStream(templateFileName));
Workbook workbook=transformer.transformXLS(in, beans);
//out=response.getOutputStream();
workbook.write(out);
out.flush();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ParsePropertyException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (in!=null){try {in.close();} catch (IOException e) {}}
if (out!=null){try {out.close();} catch (IOException e) {}}
}
}
/**
* @author suzy2
* @param temmpTlateName 模板名称
* @param desName 导出文件名称
* @param response
* @param beans 导出对应用的 Map对象
* @see 导出pdf 使用 目前该方法比较耗内存
*/
public static void exportStream(String temmpTlateName,String desName,HttpServletResponse response,Map beans){
String templateFileName= WorkDayController.class.getClassLoader().getResource("").getPath() +temmpTlateName;// "/template/workDayTemplate.xls";
PageSetting set=new PageSetting();
set.setPageSize(PdfPageSize.A2.rotate());
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateStr = format.format(new Date());
response.setContentType("Application/pdf;charset=UTF-8");
String fileName = /*"工作日历导出详情"*/ desName+dateStr+".pdf";
try {
fileName = new String(fileName.getBytes("GBK"),"iso-8859-1");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
XLSTransformer transformer = new XLSTransformer();
InputStream in=null;
OutputStream out=null;
//设置响应
ByteArrayOutputStream out1 = new ByteArrayOutputStream();
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
// response.setContentType("application/vnd.ms-excel");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
beans.put("dateFormat", dateFormat);
try {
in=new BufferedInputStream(new FileInputStream(templateFileName));
Workbook workbook=transformer.transformXLS(in, beans);
Excel_TO_PDF excel_TO_PDF = new Excel_TO_PDF();
workbook.write(out1);
InputStream input =new ByteArrayInputStream(out1.toByteArray());
out=response.getOutputStream();
excel_TO_PDF.conVertFormStream(input, out, set);
out.flush();
} catch (InvalidFormatException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
} catch (ParsePropertyException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (org.apache.poi.openxml4j.exceptions.InvalidFormatException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
if (in!=null){try {in.close();} catch (IOException e) {}}
if (out!=null){try {out.close();} catch (IOException e) {}}
}
}
/**
* @author suzy2
* @param temmpTlateName 模板名称
* @param desName 导出文件名称
* @param response
* @param beans 导出对应用的 Map对象
* @see 导出pdf 使用 目前该方法比较耗内存
*/
public static void exportPdf(String temmpTlateName,String desName,HttpServletResponse response,Map beans){
exportStream(temmpTlateName, desName, response, beans);
}
public static void exportExcelBySql(Connection conn,HttpServletResponse response,String desName, String sql, Class<?> rsh) throws SQLException{
exportExcelBySql( conn, response,desName, true, sql, null, rsh);
}
public static void exportExcelBySql(Connection conn,HttpServletResponse response,String desName, String sql,Map replaceMap, Class<?> rsh) throws SQLException{
exportExcelBySql( conn, response,desName, true, sql, replaceMap, rsh);
}
public static void exportExcelBySql(HttpServletResponse response,String desName, String sql,Map replaceMap, Class<?> rsh) throws SQLException{
ConnectionTool connectionTool = new ConnectionTool();
exportExcelBySql( connectionTool.getConnection(), response,desName, true, sql, replaceMap, rsh);
}
/**
*
* @author suzy2
* @param conn 数据库库连接
* @param response
* @param desName 导出文件名称
* @param closeConn 是否关闭连接
* @param sql 需要导出的sql
* @param replaceMap 替换map对象
* @param rsh 需要导出的bean类 必须使用注解才能实现标准导出
* @throws SQLException
* @see 该方法适合大数据量数据导出 使用多线程生成 提高导出时间 降低内存占用
*
*/
public static void exportExcelBySql(Connection conn,HttpServletResponse response,String desName, boolean closeConn, String sql,Map replaceMap, Class<?> rsh)
throws SQLException {
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
String dateStr = format.format(new Date());
response.setContentType("Application/msexcel;charset=UTF-8");
String fileName = /*"工作日历导出详情"*/ desName+dateStr+".xlsx";
try {
fileName = new String(fileName.getBytes("GBK"),"iso-8859-1");
} catch (UnsupportedEncodingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
response.setContentType("application/vnd.ms-excel");
if(rsh==null){
return;
}
List<Field> displayField = GenerateTitle.getDisplayField(rsh);
if(displayField==null){
return;
}
List listHead =GenerateTitle.getlistHead(displayField);
Map map =GenerateTitle.getFormatMap(displayField);
Map mapD =GenerateTitle.getDecimalFormatMap(displayField);
Workbook wb = new SXSSFWorkbook(500);
//Workbook workbook = new SXSSFWorkbook(500);//每次缓存500条到内存,其余写到磁盘。
CellStyle style = ExcelUtil.getCellStyle(wb);
Sheet sheet = wb.createSheet();
/**
* 设置Excel表的第一行即表头
*/
// BeanProcessor beanProcessor = new BeanProcessor();
Row row =sheet.createRow(0);
for(int i=0;i<listHead.size();i++){
Cell headCell = row.createCell(i);
headCell.setCellType(Cell.CELL_TYPE_STRING);
headCell.setCellStyle(style);//设置表头样式
headCell.setCellValue(String.valueOf(listHead.get(i)));
}
if (conn == null) {
throw new SQLException("Null connection");
}
if (sql == null) {
if (closeConn) {
DbUtils.close(conn);
}
throw new SQLException("Null SQL statement");
}
PreparedStatement stmt = null;
ResultSet rs = null;
Object result = null;
int i=0;
OutputStream os =null;
try {
os=response.getOutputStream();
BasicRowProcessor basicRowProcessor = new BasicRowProcessor();
stmt =conn.prepareStatement(sql);
// this.fillStatement(stmt, params);
// stmt.getMetaData().
// stmt.setMaxRows(max);
//设置结果集大小 根据结果集进行流式处理
stmt.setFetchSize(Integer.MIN_VALUE);
rs = stmt.executeQuery();
// rs.
//BeanHandler
// result = rsh.handle(rs);
if (!rs.next()) {
return ;
}
// PropertyDescriptor[] props =beanProcessor.propertyDescriptors(rsh);
// ResultSetMetaData rsmd = rs.getMetaData();
// int[] columnToProperty = beanProcessor.mapColumnsToProperties(rsmd, props);
Row rowdata=null;
List<String> mapdata ;
Cell celldata;
// rs.getMetaData().getScale(column)
ExecutorService threadPool = Executors.newFixedThreadPool(5);
// CountDownLatch countdownLatch = new CountDownLatch(5);
do {
// result =beanProcessor.createBean(rs, rsh, props, columnToProperty);
rowdata = sheet.createRow(i+1);//创建数据行
result = basicRowProcessor.toMap(rs,displayField);
ExportExcelTask exportExcelTask = new ExportExcelTask(rowdata, map, mapD, basicRowProcessor, displayField, result, replaceMap);
// result =basicRowProcessor.toMap(rs,displayField);
threadPool.execute(exportExcelTask);
rowdata = sheet.createRow(i+1);//创建数据行
// mapdata = GenerateTitle.getListRow(displayField, map, mapD, replaceMap, result);
// int j=0;
// for(String tt:mapdata){
// celldata = rowdata.createCell(j);
// celldata.setCellType(Cell.CELL_TYPE_STRING);
// if(tt==null){
// tt="";
// }
// celldata.setCellValue(tt);
// j++;
// }
i++;
// mapdata.clear();
// wb.write(os);
} while (rs.next());
// sleep(200);
threadPool.shutdown();
// Thread.currentThread().
// countdownLatch.await();
} catch (Exception e) {
e.printStackTrace();
logger.error(e.getMessage(), e);
} finally {
try {
DbUtils.close(rs);
} finally {
DbUtils.close(stmt);
if (closeConn) {
DbUtils.close(conn);
}
}
}
try {
wb.write(os);
os.flush();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}