导出excel
/**
* 导出
*/
@RequestMapping("/contractexport")
public void systemstatisticexport(Model model, Integer page,HttpServletRequest request,HttpSession session,HttpServletResponse response,MyContractDTO contract) throws Exception{
String[] headers={"contractTemplatename","contractSupplierName","relatedName","contractStatus","contractNumber","contractOwnName",
"contractTypename","contractTemplateType","createByName","createByDept","createByContact","contractOwnContact","approvalCreateDt",
"totalPrice","contractCurrency","contractInvoice","legalApprovalByName","financialApprovalByName","archiveAddr",
"serContent","contractSupplierBank","contractSupplierAccountName","contractSupplierAccount","approvalEndDt",
"contractStartDt","contractValidDt","renewFlag","frameFlag"};
String[] titles ={"合同模板种类","乙方名称","合同摘要","合同状态","合同编号","甲方名称","合同类别","条款种类","合同发起人","发起人部门",
"发起人联系方式","合同联系人姓名","审批单起草时间","合同总金额","币种","发票种类","法务审批人","财务审批人","归档位置","服务名称",
"开户银行","账户名称","帐号","审批完成时间","合同生效日期","合同有效期","是否自动续签","是否框架合同"};
List<Integer> list1 = new ArrayList<Integer>();
list1.add(1);
list1.add(2);
list1.add(3);
list1.add(4);
contract.setContractStatusMores(list1);
SimpleDateFormat dd = new SimpleDateFormat("yyyy-MM-dd");
if(contract.getApprovalCreateDt()!=null)contract.setApprovalCreateDtStr(dd.format(contract.getApprovalCreateDt()));
if(StringUtils.isNotEmpty(contract.getApprovalEndDtStr())) {
contract.setApprovalEndDt(dd.parse(contract.getApprovalEndDtStr()));
}
if(contract.getContractStartDt()!=null)contract.setContractStartDtStr(dd.format(contract.getContractStartDt()));
if(contract.getContractEndDt()!=null)contract.setContractEndDtStr(dd.format(contract.getContractEndDt()));
contract.setExportStatus(1);
ExecuteResult<DataGrid<MyContractDTO>> contractResult = contract(request, model, contract, 0);
List<MyContractDTO> list = contractResult.getResult().getRows();
ExportExcelUtil<MyContractDTO> util = new ExportExcelUtil<MyContractDTO>(titles, headers,MyContractDTO.class);
HSSFWorkbook workbook = new HSSFWorkbook();
UserVo userVo = WebUtil.getUserVo(request);
String userName = userVo.getAccount();
SimpleDateFormat df = new SimpleDateFormat("yyyyMMdd");//设置日期格式
String str = df.format(new Date());
String sheets = userName+"_"+str;
HSSFSheet sheet = workbook.createSheet("合同列表");
sheet.setDefaultColumnWidth(25);
HSSFCellStyle cellStyle = util.getCellStyle(workbook);
util.setTitleValue(sheet,cellStyle);
util.setSheetData(sheet, list);
StringBuffer fileName = new StringBuffer();
BufferedOutputStream bos = null;
try {
fileName.append(sheets);
String excelName = null ;
if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
try {
excelName = URLEncoder.encode(fileName.toString(), "UTF-8");
} catch (UnsupportedEncodingException e) {
}
} else {
try {
excelName = new String(fileName.toString().getBytes("UTF-8"), "ISO8859-1");
} catch (UnsupportedEncodingException e) {
}
}
response.setContentType("application/x-msdownload;");
response.setHeader("Content-disposition", "attachment; filename="+excelName+".xls");
bos = new BufferedOutputStream(response.getOutputStream());
workbook.write(bos);
bos.close();
}catch(Exception e){
e.printStackTrace();
}finally{
if(bos!=null){
bos.close();
}
}
/*FileOutputStream fos= new FileOutputStream("D:\\abc.xls");
workbook.write(fos);
fos.close();*/
// workbook.write(response.getOutputStream());
}
package com.camelot.util;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import com.camelot.contract.sdk.export.dto.ContractDTO;
import com.camelot.contract.sdk.export.dto.MyContractDTO;
public class ExportExcelUtil<T> {
private String[] titles;//第一行标题
private String []header;//T对象当中要导出的字段
//private List<T> datas;//数据
private Class<?> clazz;//T的类路径
public ExportExcelUtil(String[] titles, String[] header,Class<?> clazz) {
super();
this.titles = titles;
this.header = header;
//this.datas = datas;
this.clazz = clazz;
}
/**
* <p>Title: </p>
* <p>Description: </p>
*/
public ExportExcelUtil() {
}
public HSSFWorkbook createExcelSheet(String sheetName) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
// 声明一个工作薄
HSSFWorkbook workbook = new HSSFWorkbook();
// 生成一个表格
HSSFSheet sheet = workbook.createSheet(sheetName);
sheet.setDefaultColumnWidth(30);
HSSFCellStyle cellStyle = getCellStyle(workbook);
setTitleValue(sheet,cellStyle);
return workbook;
}
public HSSFCellStyle getCellStyle(HSSFWorkbook workbook){
HSSFCellStyle setBorder = workbook.createCellStyle();
// 一、设置背景色：
setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//二、设置边框:
setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// 三、设置居中:
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
// 四、设置字体:
HSSFFont font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 12);//设置字体大小
HSSFFont font2 = workbook.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);
setBorder.setFont(font);//选择需要用到的字体格式
return setBorder;
}
public void setTitleValue(HSSFSheet sheet,HSSFCellStyle cellStyle){
Row row = sheet.createRow(0);
// row.setRowStyle(cellStyle);
for(int i=0;i<titles.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(cellStyle);
}
}
public void setSheetData(HSSFSheet sheet,List<T> datas) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
List<String> getMethodNames = new ArrayList<String>();
for(String str :header){
str.substring(0,1).toUpperCase();
str.subSequence(1, str.length());
String methodName = "get"+str.substring(0,1).toUpperCase()+str.subSequence(1, str.length());
getMethodNames.add(methodName);
}
for(int r=1;r<datas.size()+1;r++){
Row row = sheet.createRow(r);
for(int c=0;c<getMethodNames.size();c++){
Cell cell = row.createCell(c);
String tempStr = getMethodNames.get(c);
Method m1 = clazz.getMethod(tempStr);
Object value = m1.invoke(datas.get(r-1));
if(null != value){
if(getMethodName("contractTemplateType").equals(tempStr)){
if(value.toString().trim().equals("0")){
value = "标准";
}else if(value.toString().trim().equals("1")){
value= "标准+选项";
}
}
if(getMethodName("contractStatus").equals(tempStr)){
if(value.toString().trim().equals("0")){
value="起草中";
}else if(value.toString().trim().equals("1")){
value="审批中";
}else if(value.toString().trim().equals("2")){
value="待用印";
}else if(value.toString().trim().equals("3")){
value="待归档";
}else if(value.toString().trim().equals("4")){
value="已归档";
}
}
if(getMethodName("contractCurrency").equals(tempStr)){
if(value.toString().trim().equals("1")){
value="人民币";
}else if(value.toString().trim().equals("2")){
value="美元";
}else if(value.toString().trim().equals("3")){
value="欧元";
}else if(value.toString().trim().equals("4")){
value="新台币";
}else if(value.toString().trim().equals("5")){
value="港币";
}
}
if(getMethodName("contractInvoice").equals(tempStr)){
value="增值税专用发票";
}
if(getMethodName("contractValidDt").equals(tempStr)){
value = dateFormat(value) ;
value += "至" + dateFormat(clazz.getMethod("getContractEndDt").invoke(datas.get(r-1)));
}else if(m1.getGenericReturnType().toString().indexOf("java.util.Date") > -1){
value = dateFormat(value) ;
}
cell.setCellValue(value.toString());
}
}
}
}
public static Object dateFormat(Object value){
SimpleDateFormat sdf1= new SimpleDateFormat("EEE MMM dd HH:mm:ss z yyyy", Locale.ENGLISH);
SimpleDateFormat sdf2= new SimpleDateFormat("yyyy-MM-dd");
try {
if(value!=null&&value!=""){
if(value.toString().length()>10){
Date parse = sdf1.parse(value.toString());
value = sdf2.format(parse);
}
}
} catch (ParseException e) {
e.printStackTrace();
}
return value ;
}
public static String getMethodName(String str){
String methodName = "get"+str.substring(0,1).toUpperCase()+str.subSequence(1, str.length());
return methodName ;
}
public void setSheetDatadatetime(HSSFSheet sheet,List<T> datas) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
List<String> getMethodNames = new ArrayList<String>();
for(String str :header){
str.substring(0,1).toUpperCase();
str.subSequence(1, str.length());
String methodName = "get"+str.substring(0,1).toUpperCase()+str.subSequence(1, str.length());
getMethodNames.add(methodName);
}
for(int r=1;r<datas.size()+1;r++){
Row row = sheet.createRow(r);
for(int c=0;c<getMethodNames.size();c++){
Cell cell = row.createCell(c);
Method m1 = clazz.getMethod(getMethodNames.get(c));
Object value = m1.invoke(datas.get(r-1));
if(value!=null){
if(value instanceof Date){
// value = DateUtil.convertDateToString((Date)value,DateUtil.TIMESTAMP_PATTERN);
}
cell.setCellValue(value.toString());
}
}
}
}
public void setSheetDatadate(HSSFSheet sheet,List<MyContractDTO> list) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException{
List<String> getMethodNames = new ArrayList<String>();
for(String str :header){
str.substring(0,1).toUpperCase();
str.subSequence(1, str.length());
String methodName = "get"+str.substring(0,1).toUpperCase()+str.subSequence(1, str.length());
getMethodNames.add(methodName);
}
for(int r=1;r<list.size()+1;r++){
Row row = sheet.createRow(r);
for(int c=0;c<getMethodNames.size();c++){
Cell cell = row.createCell(c);
Method m1 = clazz.getMethod(getMethodNames.get(c));
Object value = m1.invoke(list.get(r-1));
if(value!=null){
if(value instanceof Date){
// value = DateUtil.convertDateToString((Date)value,DateUtil.DATE_PATTERN);
}
cell.setCellValue(value.toString());
}
}
}
}
//2007-xlsx
public CellStyle getCellStyle(Workbook workbook){
CellStyle setBorder = workbook.createCellStyle();
// 一、设置背景色：
setBorder.setFillForegroundColor((short) 13);// 设置背景色
setBorder.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
//二、设置边框:
setBorder.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
setBorder.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
setBorder.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
setBorder.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
// 三、设置居中:
setBorder.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 居中
// 四、设置字体:
Font font = workbook.createFont();
font.setFontName("黑体");
font.setFontHeightInPoints((short) 12);//设置字体大小
Font font2 = workbook.createFont();
font2.setFontName("仿宋_GB2312");
font2.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//粗体显示
font2.setFontHeightInPoints((short) 12);
setBorder.setFont(font);//选择需要用到的字体格式
return setBorder;
}
public void setTitleValue(Sheet sheet,CellStyle cellStyle){
Row row = sheet.createRow(0);
// row.setRowStyle(cellStyle);
for(int i=0;i<titles.length;i++){
Cell cell = row.createCell(i);
cell.setCellValue(titles[i]);
cell.setCellStyle(cellStyle);
}
}
//导出List<Map<String,Object>> 类型的数据
public void setSheetDataForMap(Sheet sheet,List<Map<String,Object>> datas,int start) throws Exception{
for(int r=0;r<datas.size();r++){
Row row = sheet.createRow(r+1+start);
for(int c=0;c<header.length;c++){
Cell cell = row.createCell(c);
Object value = datas.get(r).get(header[c]);
if(value!=null){
cell.setCellValue(value.toString());
}
}
}
}
public void setSheetDataForMaptype(Sheet sheet,List<MyContractDTO> datas,int start) throws Exception{
for(int r=0;r<datas.size();r++){
Row row = sheet.createRow(r+1+start);
for(int c=0;c<header.length;c++){
Cell cell = row.createCell(c);
Object value = datas.get(r);
if(value!=null){
cell.setCellValue(value.toString());
}
}
}
}
//方法一:EXCEL HEARDE
public static void main(String[] args) throws NoSuchMethodException, SecurityException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException {
String[] headers={"contractId","createByName"};
String[] titles ={"用户ID","用户名"};
ContractDTO c = new ContractDTO();
c.setContractId(1L);
c.setCreateByName("张三");
List<ContractDTO> list = new ArrayList<ContractDTO>();
list.add(c);
ExportExcelUtil<ContractDTO> util = new ExportExcelUtil<ContractDTO>(titles, headers,ContractDTO.class);
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("第一页");
sheet.setDefaultColumnWidth(30);
HSSFCellStyle cellStyle = util.getCellStyle(workbook);
util.setTitleValue(sheet,cellStyle);
util.setSheetData(sheet, list);
FileOutputStream fos= new FileOutputStream("D:\\abc.xls");
workbook.write(fos);
fos.close();
}
}
