Java 导出Excel

导出 项目中的报表  导出为Excel 格式

代码：

@RequiresPermissions("tradelog:tradeLog:view")
	@RequestMapping(value = "export")
	public void export(TradeLog tradeLog, HttpServletRequest request, HttpServletResponse response, Model model){
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		Date startDate = tradeLog.getStartPayTime();
		if(startDate != null) {
			String startDate_ = format.format(startDate);
			startDate_ = startDate_.substring(0,10) +" "+"00:00:00";
			System.out.println("开始时间="+startDate_);
			try {
				tradeLog.setStartPayTime(format.parse(startDate_));
			} catch (ParseException e) {
				System.out.println("开始时间转化异常");
				e.printStackTrace();
			}
		}
		Date endDate = tradeLog.getEndPayTime();
		if(endDate != null) {
			String endDate_ = format.format(endDate);
			endDate_ = endDate_.substring(0, 10) +" "+"23:59:59";
			System.out.println("结束时间="+endDate_);
			try {
				tradeLog.setEndPayTime(format.parse(endDate_));
			} catch (ParseException e) {
				System.out.println("结束时间转换异常");
				e.printStackTrace();
			}
		}
		List<TradeLog> list = tradeLogService.findList(tradeLog);
		createExcel(list,response);
	}
	
	/**
	 * 生成excel文件
	 * @param tradeLogList
	 */
	public void createExcel(List<TradeLog> tradeLogList,HttpServletResponse response) {
		//获取OutputStream输出流
		OutputStream out = null;
		try {
			out = response.getOutputStream();
		} catch (IOException e1) {
			e1.printStackTrace();
		}
		String excelName = "交易记录.xls";
		try {
			response.setHeader("Content-Disposition", "attachment; filename=" +new String(excelName.getBytes("UTF-8"), "ISO8859-1" ));
		} catch (UnsupportedEncodingException e1) {
			e1.printStackTrace();
		}
		response.setContentType("application/vnd.ms-excel"); // 设置下载类型
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet("交易记录");//文件名
		sheet.setDefaultColumnWidth(20);//列宽
		HSSFRow row = sheet.createRow((int) 0);
		HSSFCellStyle style = wb.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);//表头的对齐方式
		// 设置表头
		HSSFCell cell = row.createCell(0);
		cell.setCellValue("序号");
		cell.setCellStyle(style);

		cell = row.createCell(1);
		cell.setCellValue("订单号");
		cell.setCellStyle(style);

		cell = row.createCell(2);
		cell.setCellValue("用户名");
		cell.setCellStyle(style);

		cell = row.createCell(3);
		cell.setCellValue("商品名称");
		cell.setCellStyle(style);

		cell = row.createCell(4);
		cell.setCellValue("金额￥");
		cell.setCellStyle(style);

		cell = row.createCell(5);
		cell.setCellValue("下单时间");
		cell.setCellStyle(style);

		cell = row.createCell(6);
		cell.setCellValue("支付方式");
		cell.setCellStyle(style);

		cell = row.createCell(7);
		cell.setCellValue("订单状态");
		cell.setCellStyle(style);

		// 循环将数据写入Excel
		for (int i = 0; i < tradeLogList.size(); i++) {
			HSSFRow row_ = sheet.createRow(i+1);//数据
			TradeLog tradeLog = tradeLogList.get(i);
			
			HSSFCell cell_ = row_.createCell(0);
			cell_.setCellValue(i + 1);// 序号
			cell_.setCellStyle(style);
			
			cell_ = row_.createCell(1);
			cell_.setCellValue(tradeLog.getOrderNo());// 订单号
			cell_.setCellStyle(style);
			
			cell_ = row_.createCell(2);
			User user = UserUtils.get(tradeLog.getUsername().getId());
			if(user == null) {
				cell_.setCellValue("");// 用户名
			}else {
				cell_.setCellValue(user.getName());// 用户名
			}
			
			cell_.setCellStyle(style);
			
			
			cell_ = row_.createCell(3);
			cell_.setCellValue(tradeLog.getCommodityName()); // 商品名称
			cell_.setCellStyle(style);
			
			cell_ = row_.createCell(4);
			cell_.setCellValue(tradeLog.getPayAmount().toString()); // 金额
			cell_.setCellStyle(style);
			
			cell_ = row_.createCell(5);
			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
			if(tradeLog.getPayTime() == null) {
				cell_.setCellValue("");// 下单时间
				cell_.setCellStyle(style);
			}else {
				String pay_time = format.format(tradeLog.getPayTime());
				cell_.setCellValue(pay_time);// 下单时间
				cell_.setCellStyle(style);
			}
			
			cell_ = row_.createCell(6);
			if(tradeLog.getPayMethod() == null) {
				cell_.setCellValue("");// 支付方式
				cell_.setCellStyle(style);
			}else {
				String pay_method = tradeLog.getPayMethod();
				pay_method = PayMethodEnum.getName(pay_method);
				cell_.setCellValue(pay_method);// 支付方式
				cell_.setCellStyle(style);
			}
			
			
			
			cell_ = row_.createCell(7);
			String order_status = tradeLog.getOrderStatus();
			order_status = OrderStatusEnum.getName(order_status);
			cell_.setCellValue(order_status);// 订单状态
			cell_.setCellStyle(style);
		}
		//不弹出下载框
		try {
			wb.write(out);
		} catch (IOException e) {
			e.printStackTrace();
		} 
		try {
			out.flush();
			out.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	

 效果图：

 

 

看看别人的代码  自己研究研究 就好啦

java 导入 Excel 文件

import java.io.File;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class Test {
	/**
	 * 解析Excel文件
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		try {
			//创建workbook
			Workbook workbook=Workbook.getWorkbook(new File("C:\\Users\\sdm\\Desktop\\新建文件夹\\小王子新版荣获法国政府大奖诗人译本小王子.xls"));
			//获取第一个工作表sheet
			Sheet sheet=workbook.getSheet(0);
			//获取数据
			for (int i = 0; i < sheet.getRows(); i++) {
				for (int j = 0; j < sheet.getColumns(); j++) {
					Cell cell=sheet.getCell(j,i);
					System.out.print(cell.getContents()+"\t");
				}
				System.out.println();
			}
			//关闭流
			workbook.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}

 

Excel  文件：

 

IDAE 控制台 打印数据

 

特别感谢：

@（Jansens）https://home.cnblogs.com/u/Jansens520/