导出 项目中的报表 导出为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 控制台 打印数据
特别感谢: