java 导出excel 工具类使用

上代码

package com.zhuzher.common.utils;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.util.List;

/**
 * @description: 生成导出excel类
 * @author: z
 */
//@Slf4j
public class ExcelPoiUtil {
	private static final Log log = LogFactory.getLog(ExcelPoiUtil.class);
	private static HSSFCellStyle style;
	public static void main(String[] args) throws Exception {
		ExcelPoiUtil excelkit = new ExcelPoiUtil();
		String rootPath=Thread.currentThread().getContextClassLoader().getResource("").getPath();

		System.out.println(excelkit.getClass().getResource("/").getPath());
		System.out.println(excelkit.getClass().getClassLoader().getResource("").getPath());
		System.out.println(rootPath);
		//excelkit.getDefaultSheeft();
		//System.out.println(rootPath("exceldoc"+File.separator+"demo-order.xls"));

	}

	public static HSSFSheet export(String fileName, String demoPath, List list, HttpServletResponse response){
		try {
			// 设定输出文件头
			response.setContentType("application/vnd.ms-excel");// 定义输出类型
			response.reset();// 清空输出流
			response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("GB2312"), "ISO8859-1"));

			OutputStream os = response.getOutputStream();// 取得输出流
			HSSFWorkbook work = new HSSFWorkbook(new FileInputStream(demoPath));// 得到这个excel表格对象
			HSSFSheet sheet = work.getSheetAt(0); //得到第一个sheet
			style = work.createCellStyle();
			style.setBorderBottom(BorderStyle.THIN); //下边框
			style.setBorderLeft(BorderStyle.THIN);//左边框
			style.setBorderTop(BorderStyle.THIN);//上边框
			style.setBorderRight(BorderStyle.THIN);//右边框
			int rowNo = sheet.getLastRowNum();
			Field[] fields = null;
			int i = rowNo+1;
			for (Object obj : list) {
				fields = obj.getClass().getDeclaredFields();
				int j = 0;
				for (Field v : fields) {
					v.setAccessible(true);
					Object va = v.get(obj);
					if (va == null) {
						va = "";
					}
					writeToCell(va+"",sheet,i,j);
					j++;
				}
				i++;
			}
			/** **********将以上缓存中的内容写到EXCEL文件中******** */
			work.write(os);
			os.flush();
			os.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * 往sheet指定坐标单元格里面写入数据
	 *
	 * @param value
	 * @param cell
	 */
	public static void writeToCell(String value, Cell cell) {
		if (checkIntType(value)) {
			double a = Double.valueOf(value);
			cell.setCellValue(a);
		} else {
			cell.setCellValue(value);
		}
	}


	private static Boolean checkIntType(String value) {
		try {
			Integer.parseInt(value);
			return true;
		} catch (NumberFormatException e) {
			return false;
		}
	}
	/**
	 * 往sheet指定坐标单元格里面写入数据
	 *
	 * @param sheet
	 * @param row
	 * @param col
	 */
	public static void writeToCell(String value, Sheet sheet, int row, int col) {
		writeToCell(value, getCell(sheet, row, col, null));
	}
	/**
	 * 根据横纵轴坐标获取行对象
	 *
	 * @param sheet
	 * @param rowNo
	 * @param rowHeight
	 * @return
	 */
	public static Row getRow(Sheet sheet, int rowNo, Integer rowHeight) {
		Row row = sheet.getRow(rowNo);
		if (row == null) {
			row = sheet.createRow(rowNo);
			if (rowHeight != null) {
				row.setHeightInPoints(rowHeight);// 设置行高
			}
		}
		return row;
	}
	/**
	 * 根据横纵轴坐标获取单元格对象
	 *
	 * @param sheet
	 * @param rowNo
	 * @param colNo
	 * @param rowHeight
	 * @return
	 */
	public static Cell getCell(Sheet sheet, int rowNo, int colNo, Integer rowHeight) {
		Row row = getRow(sheet, rowNo, rowHeight);
		Cell cell = row.getCell(colNo);
		if (cell == null) {
			cell = row.createCell(colNo);
		}
		cell.setCellStyle(style);
		return cell;
	}
	/**
	 * 私有处理方法
	 */
	public static String rootPath(String u_path) {
		String rootPath = "";
		String classPath = Thread.currentThread().getContextClassLoader().getResource("").getPath();
		//windows下
		if ("\\".equals(File.separator)) {
			//System.out.println(classPath);
			rootPath = classPath + u_path;
			rootPath = rootPath.replaceAll("/", "\\\\");
			if (rootPath.substring(0, 1).equals("\\")) {
				rootPath = rootPath.substring(1);
			}
		}
		//linux下
		if ("/".equals(File.separator)) {
			rootPath = classPath + u_path;
			rootPath = rootPath.replaceAll("\\\\", "/");
		}
		return rootPath;
	}

}

模板路径

controller使用

 /**
     *入参 都是必传项
     * 入住时间 sdate  edate
     * 酒店编号 hotelId
     * 渠道 channel 非必填
     * orderDateCheckinDate checkin：代表按照入住时间查询 orderdate：代表按照下单时间查询）
     *orderValidType 订单有效状态：(有效订单：y,全部订单：all)
     */
    @RequestMapping("/exportHotelOrderDetailDown")
    public void exportHotelOrderDetailDown(@RequestBody ExportOrderDto exportOrderDto, HttpServletResponse response){
        if (StringUtil.isEmpty(exportOrderDto.getSdate())) {
            exportOrderDto.setSdate(DateUtil.getNextDay(DateUtil.formatDate(new Date(), "yyyy-MM-dd"), -1));
        }else {
            exportOrderDto.setSdate(exportOrderDto.getSdate()+" 00:00:00");
        }
        if (StringUtil.isEmpty(exportOrderDto.getEdate())) {
            exportOrderDto.setEdate(DateUtil.getNextDay(DateUtil.formatDate(new Date(), "yyyy-MM-dd"), -1));
        }else {
            exportOrderDto.setEdate(exportOrderDto.getEdate()+" 23:59:59");
        }
        logger.info("导出报表--------exportHotelOrderDetailDown---入参："+exportOrderDto.toString());
        List<ExportOrderDetailDto> list=null;
        String fileName="";
        try {
            list = otaOrderService.exportOrderByDate(exportOrderDto);
            if (list!=null && list.size()>0){
                fileName= list.get(0).getHotelName() + "酒店有效订单明细.xls";
            }else {
                fileName= "酒店有效订单明细.xls";
            }
            String demoPath = ExcelPoiUtil.rootPath("exceldoc" + File.separator + "demo-hotel-order-detail.xls");
            ExcelPoiUtil.export(fileName, demoPath,  invokDate(list), response);
        } catch (Exception e) {
            logger.error("导出报表--------exportHotelOrderDetailDown---异常："+exportOrderDto,e);
        }
    }