execl导出并通过浏览器下载:
转自:http://www.cnblogs.com/bmbm/archive/2011/12/08/2342261.html
代码如下:
//第一步:获取导出数据
public static byte[] export(List<RechargeCard> list,HttpServletResponse response){
// 创建一个webbook,对应一个Excel文件
HSSFWorkbook wb = new HSSFWorkbook();
// 在webbook中添加一个sheet,对应Excel文件中的sheet
HSSFSheet sheet = wb.createSheet("充值卡");
// 在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
HSSFRow row = sheet.createRow((int) 0);
// 创建单元格,并设置值表头 设置表头居中
HSSFCellStyle style = wb.createCellStyle();
style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
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);
cell = row.createCell( 8);
cell.setCellValue("状态");
cell.setCellStyle(style);
// 写入实体数据
RechargeCard map = null;
for (int i = 0; i < list.size(); i++)
{
map = list.get(i);
row = sheet.createRow((int) i + 1);
// 创建单元格,并设置值
row.createCell( 0).setCellValue(map.getAmount());
row.createCell( 1).setCellValue(map.getPassword());
row.createCell( 2).setCellValue(map.getMoney()+"");
row.createCell( 3).setCellValue(map.getCode());
row.createCell( 4).setCellValue(map.getUsername());
if(map.getUsedAt() != null)
row.createCell( 5).setCellValue(map.getUsedAt());
else
row.createCell( 5).setCellValue("");
row.createCell( 6).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(map.getCreatedAt()));
row.createCell( 7).setCellValue(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(map.getDeadline()));
row.createCell( 8).setCellValue(map.getStatus().equals("0")?"未使用":"已使用");
//cell.setCellValue(new SimpleDateFormat("yyyy-mm-dd").format());
}
// 第六步,下载文件
ByteArrayOutputStream out = null;
try
{
//方法一:指定下载路径
// FileOutputStream fout = new FileOutputStream("/Users/jalon/Downloads/充值卡.xlsx");
// wb.write(fout);
//方法二:直接通过浏览器进行默认下载
out = new ByteArrayOutputStream();
wb.write(out);
}catch (Exception e) {
e.printStackTrace();
// TODO: handle exception
}finally {
if(out!=null){
try {
out.close();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
return out.toByteArray();
}
//第二步:在控制器的请求中,调用上面的方法
//导出
@RequestMapping("export")
@ResponseBody
public void export(HttpServletRequest request,HttpServletResponse response,HttpSession session,
Model model) throws Exception{
RechargeCard fc = new RechargeCard();
fc.setRows(0);
fc.setPageSize(999);
List<RechargeCard> list = cardService.getListByMoreCondition(fc);
byte[] bytes = ExeclUtil.export(list,response);
response.setContentType("application/x-msdownload");
response.setHeader("Content-Disposition", "attachment;filename=" + "123.xls");
response.setContentLength(bytes.length);
response.getOutputStream().write(bytes);
response.getOutputStream().flush();
response.getOutputStream().close();
}
//第三步:就是前端代码,点击一个链接,get请求即可
execl导入:
jar下载地址:http://www.mvnrepository.com/artifact/org.apache.poi/poi-ooxml/3.15
- 思路:先用MultipartFile上传文件,后台获取,转换成InputStream,创建Workbook(这里要判断File是2003版的xls还是2007版的xlsx,因为这两个的实现不同),然后循环获取行和列即可;
- 代码如下:
package com.prositech.v11j.utils;
import java.io.FileInputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.web.multipart.MultipartFile;
import com.prositech.v11j.clothes.entity.Cloth;
import com.prositech.v11j.clothes.entity.ClothSize;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExeclUtil {
private static int totalRows;
private static int totalCells;
//读取execl
public static List<Cloth> readExecl(MultipartFile file){
try{
String name = file.getOriginalFilename();
InputStream in = file.getInputStream();
Workbook wb = null;
if(name.contains("xlsx"))
wb = new XSSFWorkbook(in);
else
wb = new HSSFWorkbook(in);
Sheet sheet=wb.getSheetAt(0);
totalRows=sheet.getPhysicalNumberOfRows();
if(totalRows>=1 && sheet.getRow(0) != null){
totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
}
List<Cloth> list = new ArrayList<Cloth>();
for(int i=1;i<totalRows;i++){
//获取行
Row row = sheet.getRow(i);
if(row == null)
continue;
Cloth cloth = new Cloth();
ClothSize size = new ClothSize();
cloth.setBrand(row.getCell((short) 0).getStringCellValue());
cloth.setName(row.getCell((short) 1).getStringCellValue());
cloth.setLinkurl(row.getCell((short) 2).getStringCellValue());
size.setSizecode(row.getCell((short) 3).getStringCellValue());
size.setJk((float) row.getCell((short)4).getNumericCellValue());
size.setXw((float) row.getCell((short)5).getNumericCellValue());
size.setYw((float) row.getCell((short)6).getNumericCellValue());
size.setTw((float) row.getCell((short)7).getNumericCellValue());
size.setXc((float) row.getCell((short)8).getNumericCellValue());
size.setKc((float) row.getCell((short)9).getNumericCellValue());
cloth.setClothSize(size);
list.add(cloth);
}
return list;
}catch (Exception e) {
// TODO: handle exception
e.printStackTrace();
return null;
}
}
}