使用POI对Excel进行操作

 要使用poi，需要在pom文件中加入以下依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.12</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.12</version>
</dependency>

然后就是 如何使用它来读取excel文件了

 InputStream is = new FileInputStream("filename"); //excel文件名
 
//xls 格式和 xlsx格式有所差异 
//xlsx 需要用 new XSSFWorkbook(is)
 Workbook Workbook = new HSSFWorkbook(is); //HSSFWorkbook 表示整个excel
 sheet hssfSheet = workbook.getSheetAt(0); //获取到第一个sheet
 int rouCount = sheet.getLastRowNum(); //获取到这一页有多少行
 for(int rowNum = 0; rowNum <= rouCount; rowNum++){ //一般第0行都是表头 
   Row row = sheet.getRow(rowNum); //获取到一个行对象
   if(row ==null) continue;
   int cellCount = row.getLastCellNum(); //获取到这一行有多少列
    for(int cellNum=0;cellNum<cellCount ;cellNum++) {
        	Cell cell = row.getCell(cellNum); //获取到每个单元格
                if(cell==null) {
                    System.out.print(" \t")
                    continue;
                }
        	cell.setCellType(Cell.CELL_TYPE_STRING);//以String类型读取 
        	System.out.print(cell.getStringCellValue()+"\t");
     }
     System.out.println();
 }
is.close(); //记得关流 需要捕获或者抛出异常

接下来就是一个很常用的场景 点击一个链接 下载excel

我这里用的是springMVC

@GetMapping("getLeaving/{startTime}/{stopTime}")
public void outLeaving(@PathVariable String startTime,@PathVariable String stopTime, HttpServletRequest request,HttpServletResponse response) throws UnsupportedEncodingException {		 
	//待导出的数据
	List<LeavingMessVo> list=leavingMessService.queryLeaving(startTime,stopTime);
	//设置编码方式 及相应头
	request.setCharacterEncoding("UTF-8");
	response.setCharacterEncoding("UTF-8");
	response.setContentType("application/x-download");
	// 设置文件信息
	String agent = request.getHeader("USER-AGENT").toLowerCase();
	response.setContentType("application/vnd.ms-excel");
	String fileName = "用户留言";
	String codedFileName = java.net.URLEncoder.encode(fileName, "UTF-8");
	if (agent.contains("firefox")) {
	 response.setCharacterEncoding("utf-8");
	 response.setHeader("content-disposition", "attachment;filename=" + new String(fileName.getBytes(), "ISO8859-1") + ".xlsx" );
	} else {
	 response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xlsx");
	}
				   
	XSSFWorkbook wb = new XSSFWorkbook();
	//创建一个 sheet页
	XSSFSheet sheet = wb.createSheet("sheetname");
	sheet.setDefaultRowHeight((short) (2 * 256));//设置行高
	sheet.setColumnWidth(0, 5000);//设置列宽
	sheet.setColumnWidth(1, 5000);//设置列宽
	sheet.setColumnWidth(2,6500);
	sheet.setColumnWidth(3,18500);
	//字体
	XSSFFont font = wb.createFont();
	font.setFontName("宋体");
	font.setFontHeightInPoints((short) 16);
					
	XSSFRow row = sheet.createRow(0);
	XSSFCell cell = row.createCell(0);
	cell.setCellValue("用户名 ");
	cell = row.createCell(1);
	cell.setCellValue("邮箱 ");
	cell = row.createCell(2);
	cell.setCellValue("留言时间 ");
	cell = row.createCell(3);
	cell.setCellValue("留言内容");
	cell = row.createCell(4);
	XSSFRow rows;
	XSSFCell cells;
	for(int i=0;i<list.size();i++) {
		rows=sheet.createRow(i+1);
		cells= rows.createCell(0);
		cells.setCellValue(list.get(i).getUsername());
		cells= rows.createCell(1);
		cells.setCellValue(list.get(i).getUserEmail());
		cells= rows.createCell(2);
                cells.setCellValue(DateUtils.getFormattedString(list.get(i).getLeaveTime(), "yyyy-MM-dd HH:mm:ss"));
		cells= rows.createCell(3);
		cells.setCellValue(list.get(i).getLeaveContent());
	}
	try {
		OutputStream out = response.getOutputStream();
		wb.write(out);
		out.close();
		wb.close();
	} catch (IOException e) {
		e.printStackTrace();
	} 
}