导入导出为excel

最新推荐文章于 2024-09-25 11:09:10 发布

大鸡腿同学

最新推荐文章于 2024-09-25 11:09:10 发布

阅读量200

点赞数

文章标签： excel java php

本文链接：https://blog.csdn.net/weixin_38336658/article/details/75949586

版权

一·导入excel：类似于文件上传

二·导出excel 基于spring mvc

1.简单的html来表示excel(java,php都可以使用)

@RequestMapping(value=" ",method=RequestMethod.GET)

@ResponseBody
public String index2(HttpServletResponse response) throws IOException{
String filename="haha.xls";
String str = "<html xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\nxmlns:x=\"urn:schemas-microsoft-com:office:excel\"\r\nxmlns=\"http://www.w3.org/TR/REC-html40\">\r\n<head>\r\n<meta http-equiv=Content-Type content=\"text/html; charset=utf-8\">\r\n</head>\r\n<body>";
str +="<table border=1><tr>";
str+="<td style='vnd.ms-excel.numberformat:@'>"+"id"+"</td>";
str+="<td style='vnd.ms-excel.numberformat:@'>"+"name"+"</td>";
str+="<td style='vnd.ms-excel.numberformat:@'>"+"age"+"</td>";
str+="</tr>";

for(int i=0;i<3;i++){
str+="<tr>";
str+="<td style='vnd.ms-excel.numberformat:@'>"+1+"</td>";
str+="<td style='vnd.ms-excel.numberformat:@'>"+"ys"+"</td>";
str+="<td style='vnd.ms-excel.numberformat:@'>"+21+"</td>"+"</tr>";
}

str += "</table></body></html>";
response.addHeader( "Content-Type"," application/vnd.ms-excel; name='excel'" );
response.addHeader( "Content-type"," application/octet-stream" );
response.addHeader( "Content-Disposition"," attachment; filename="+filename );
response.addHeader( "Cache-Control"," must-revalidate, post-check=0, pre-check=0" );
response.addHeader( "Pragma"," no-cache" );
response.addHeader( "Expires","0" );
//PrintWriter outputStream = response.getWriter();
//outputStream.write(str);

return str;

}

2.java jxl,poi导出excel

下面为poi方式，XSSF...兼容高版本excel

@RequestMapping(value=" ",method=RequestMethod.GET)
@ResponseBody
public void xls(HttpServletResponse response) throws IOException, RowsExceededException, WriteException{
String[] title={"id","name","sex"};
XSSFWorkbook workbook=new XSSFWorkbook();
Sheet sheet=workbook.createSheet();
Row row=sheet.createRow(0);
Cell cell=null;
for(int i=0;i<title.length;i++){
cell=row.createCell(i);
cell.setCellValue(title[i]);
}
for(int i=1;i<10;i++){
Row neRow=sheet.createRow(i);
Cell cell2=neRow.createCell(0);
cell2.setCellValue("a"+i);
cell2=neRow.createCell(1);
cell2.setCellValue("user"+i);
cell2=neRow.createCell(2);
cell2.setCellValue("男");
}
response.addHeader( "Content-Type"," application/vnd.ms-excel; name='excel'" );
response.addHeader( "Content-type"," application/octet-stream" );
response.addHeader( "Content-Disposition"," attachment; filename="+"h.xls" );
response.addHeader( "Cache-Control"," must-revalidate, post-check=0, pre-check=0" );
response.addHeader( "Pragma"," no-cache" );
response.addHeader( "Expires","0" );
OutputStream outputStream = response.getOutputStream();
workbook.write(outputStream);
outputStream.close();

}

总结，第一种会简单些，直接用html转excel,第二种有java的封装类，这两种相对简单实现，网上有些是由很多对象，还有封装方法来实现导出excel的，比较复杂就没深入学习。但是上面提供这两种方法，会出现点开后excel报格式错误，具体也没看什么鬼情况，但是数据都是正确的。over~