excel通过工具类导出

╰半橙微兮° 2021-09-26 22:54 391阅读 0赞

@ApiOperation(value="导出积分记录")  
@ResponseBody  
@RequestMapping("/point\_downLoad")  
public void DownLoad(String start\_time,String end\_time,String keyword)\{  
List<Point> point = pointRecordManager.getList(start\_time,end\_time,keyword);  
//使用excel导出流量报表  
ExcelUtil excelUtil = new ExcelUtil();   
  
  
//流量报表excel模板在类包中，转为流给excelutil  
// InputStream in = PointRecordManager.class.getClassLoader().getResourceAsStream("com/enation/app/shop/point/service/impl/point\_list.xls");  
InputStream in = FileUtil.getResourceAsStream("com/enation/app/shop/point/service/impl/point\_list.xls");  
  
  
excelUtil.openModal(in);  
int i=1;  
for(int j = 0; j<point.size();j++)\{  
excelUtil.writeStringToCell(i, 0,point.get(j).getOrder\_id()); //订单编号  
excelUtil.writeStringToCell(i, 1, point.get(j).getUname() ); //用户账号  
excelUtil.writeStringToCell(i, 2,point.get(j).getPoint()); //订单总额  
excelUtil.writeStringToCell(i, 3,point.get(j).getOld\_point()); //订单状态  
excelUtil.writeStringToCell(i, 4, DateUtil.toString(Long.valueOf(point.get(j).getCreatetime().toString()), "yyyy-MM-dd HH:mm:ss")); //下单时间  
i++;  
\}  
HttpServletResponse response=ThreadContextHolder.getHttpResponse();  
String filename=processFileName(ThreadContextHolder.getHttpRequest(), "积分统计");  
excelUtil.writeToResponse(response,filename);

//ExcelUtil工具类中的一部分方法

public void openModal(InputStream in) \{

try \{  
fs = new POIFSFileSystem(in);  
wb = new HSSFWorkbook(fs);  
\} catch (IOException e) \{  
e.printStackTrace();  
\}

public void writeStringToCell(int numRow, int numCol, String strval) \{  
try \{  
strval = StringUtil.isEmpty(strval) ? "" : strval;  
if (wb.getSheetAt(0) != null) \{  
Sheet aSheet = wb.getSheetAt(0);  
Row row = aSheet.getRow((short) numRow);  
if (row == null)  
row = aSheet.createRow(numRow);  
  
  
Cell csCell = row.getCell((short) numCol);  
if (csCell == null)  
csCell = row.createCell(numCol);  
// csCell.setEncoding(HSSFCell.ENCODING\_UTF\_16);  
csCell.setCellValue(strval);  
  
  
\}  
\} catch (Exception e) \{  
e.printStackTrace();  
// //System.out.println("insertDataToExcel" + e);  
  
  
\}  
\}

/\*\*  
 \* 提供直接导出浏览器的方法  
 \* add by jianghongyan   
 \* @param filename   
 \*/  
public void writeToResponse(HttpServletResponse response, String filename)\{  
try \{  
response.setContentType("application/vnd.ms-excel;charset=utf-8");  
  
filename=filename+".xls";  
String filename2=new String(filename.getBytes(),"iso-8859-1");  
response.setHeader("Content-Disposition", "attachment;filename=" +filename2 );  
wb.write(response.getOutputStream());  
\} catch (IOException e) \{  
e.printStackTrace();  
\}  
\}

**需要注意上面的导出方法主要通过ExcelUtil实现，ExcelUtil类这里只保存了用到的，用到的类可能需要自行到包**

**特别注意此导出方法只能用location.href去访问控制器，不能通过ajax访问，ajax有返回值，这种Excel导出方法是调起浏览器方法，所以只能用跳转方式**