java || poi操作excel文件【xlsx,xls】

导入依赖包，poi和poi-ooxml  jar版本最好一样 

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

------------------------------------------------------------------------------------------------------------------ 

操作excel文件

//xls

public static  void showXLSExcel() throws Exception {
        HSSFWorkbook workbook=new HSSFWorkbook(new FileInputStream(new File("F:\111.xls")));
        HSSFSheet sheet=null;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            sheet=workbook.getSheetAt(i);
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//获取每行
                HSSFRow row=sheet.getRow(j);
                for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//获取每个单元格
                    HSSFCell cell = row.getCell(k);
                    System.out.print(row.getCell(k)+"\t");
                }
                System.out.println("---Sheet表"+i+"处理完毕---");
            }
        }
    }



//xlsx

public static  void showXLSXExcel() throws Exception {
        XSSFWorkbook workbook=new XSSFWorkbook(new FileInputStream(new File("F:\111.xlsx")));
        XSSFSheet sheet=null;
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {//获取每个Sheet表
            sheet=workbook.getSheetAt(i);
            for (int j = 0; j < sheet.getPhysicalNumberOfRows(); j++) {//获取每行
                XSSFRow row=sheet.getRow(j);
                for (int k = 0; k < row.getPhysicalNumberOfCells(); k++) {//获取每个单元格
                    XSSFCell cell = row.getCell(k);
                    System.out.print(row.getCell(k)+"\t");
                }
                System.out.println("---Sheet表"+i+"处理完毕---");
            }
        }
    }

执行结果：

------------------------------------------------------------------------------------------------------------------

excel读取时兼容处理 

public R importRegion(MultipartFile file,HttpServletResponse response) throws IOException {
		String fileName = file.getOriginalFilename();
		String cell1= "";
		String cell2="";

		if(fileName.indexOf(".xlsx") != -1){
			XSSFWorkbook workbook=new XSSFWorkbook(file.getInputStream());
			XSSFSheet sheet=workbook.getSheetAt(0);
			cell1= sheet.getRow(0).getCell(0).toString();
			cell2= sheet.getRow(1).getCell(0).toString();
		}else{
			HSSFWorkbook workbook=new HSSFWorkbook(file.getInputStream());
			HSSFSheet sheet=workbook.getSheetAt(0);
			cell1= sheet.getRow(0).getCell(0).toString();
			cell2= sheet.getRow(1).getCell(0).toString();
		}
}

------------------------------------------------------------------------------------------------------------------

做导出时poi操作 多个sheet页

	@RequestMapping("etcFXExport")
	@ApiOperationSupport(order = 10)
	@ApiOperation(value = "导出列表")
	public void etcFXExport(HttpServletResponse response, EtcPassRateVO etcPassRate) throws Exception{
		List<EtcPassRateVO> list1 ;
		String time = LocalDate.now().getYear()+""+LocalDate.now().getMonth()+""+LocalDate.now().getDayOfYear();
		String titleName = "****文件"+time;
		ServletOutputStream outputStream = response.getOutputStream();
		response.setContentType("application/octet-stream; charset=utf-8");
		response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(titleName+".xls","UTF-8"));

		HSSFWorkbook workbook=new HSSFWorkbook();
		//合并单元格
		CellRangeAddress region=null;
		//设置样式
		HSSFCellStyle style = workbook.createCellStyle(); //标题区样式
		HSSFCellStyle style2 = workbook.createCellStyle();//填充值区样式
		style.setAlignment(HorizontalAlignment.CENTER);//水平靠左
		style.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

		style2.setAlignment(HorizontalAlignment.LEFT);//水平靠左
		style2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

		// 自动换行
		style.setWrapText(true);
		style2.setWrapText(true);

		style.setBorderBottom(BorderStyle.THIN); //下边框
		style.setBorderLeft(BorderStyle.THIN);//左边框
		style.setBorderTop(BorderStyle.THIN);//上边框
		style.setBorderRight(BorderStyle.THIN);//右边框

		style2.setBorderBottom(BorderStyle.THIN); //下边框
		style2.setBorderLeft(BorderStyle.THIN);//左边框
		style2.setBorderTop(BorderStyle.THIN);//上边框
		style2.setBorderRight(BorderStyle.THIN);//右边框
		// 生成一个字体
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 14);
		font.setBold(true);
		font.setColor(HSSFColor.BLACK.index);
		font.setFontName("宋体");
		HSSFFont font2 = workbook.createFont();
		font2.setFontHeightInPoints((short) 12);
		font2.setColor(HSSFColor.BLACK.index);
		font2.setFontName("宋体");
		// 把字体 应用到当前样式
		style.setFont(font);
		style2.setFont(font2);


		HSSFSheet sheet=workbook.createSheet("封闭式出入口");
		HSSFRow row = sheet.createRow(0);
		HSSFCell cell = null;
		cell = row.createCell(0);
		cell.setCellValue("封闭式高速出入口车道ETC通过率排名");
		cell.setCellStyle(style);
		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
		etcPassRate.setVitrualFlag("1");
		etcPassRate.setDirecton("0,1");
		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);



		sheet=workbook.createSheet("封闭式入口");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		cell.setCellValue("封闭式高速入口车道ETC通过率排名");
		cell.setCellStyle(style);
		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
		etcPassRate.setVitrualFlag("1");
		etcPassRate.setDirecton("0");
		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);

		sheet=workbook.createSheet("封闭式出口");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		cell.setCellValue("封闭式高速出口车道ETC通过率排名");
		cell.setCellStyle(style);
		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
		etcPassRate.setVitrualFlag("1");
		etcPassRate.setDirecton("1");
		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);

		sheet=workbook.createSheet("开放式出入口");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		cell.setCellValue("开放式高速出入口车道ETC通过率排名");
		cell.setCellStyle(style);
		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
		etcPassRate.setVitrualFlag("2");
		etcPassRate.setDirecton("0,1");
		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);

		sheet=workbook.createSheet("委托管理出入口");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		cell.setCellValue("委托管理高速出入口车道ETC通过率排名");
		cell.setCellStyle(style);
		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
		etcPassRate.setVitrualFlag("3");
		etcPassRate.setDirecton("0,1");
		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);

		sheet=workbook.createSheet("委托管理入口");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		cell.setCellValue("委托管理高速入口车道ETC通过率排名");
		cell.setCellStyle(style);
		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
		etcPassRate.setVitrualFlag("3");
		etcPassRate.setDirecton("0");
		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);

		sheet=workbook.createSheet("委托管理出口");
		row = sheet.createRow(0);
		cell = row.createCell(0);
		cell.setCellValue("委托管理高速出口车道ETC通过率排名");
		cell.setCellStyle(style);
		sheet = getTitle(region,sheet,row,cell,etcPassRate,style);
		etcPassRate.setVitrualFlag("3");
		etcPassRate.setDirecton("1");
		list1 = etcPassRateService.selectEtcPassRatePage(etcPassRate);
		sheet = returnSheet(sheet,workbook,list1,row,cell,style2);

		//导出
		workbook.write(outputStream);
		outputStream.close();
	}
	//表格前三行
	public HSSFSheet getTitle(CellRangeAddress region,HSSFSheet sheet,HSSFRow row,HSSFCell cell,EtcPassRateVO etcPassRate,HSSFCellStyle style){
		sheet.setColumnWidth(1, 25 * 256);
		sheet.setColumnWidth(2, 25 * 256);
		sheet.setColumnWidth(3, 25 * 256);
		sheet.setColumnWidth(4, 25 * 256);
		sheet.setColumnWidth(5, 25 * 256);
		//合并单元格
		region=new CellRangeAddress(0, 0, 0, 8);
		sheet.addMergedRegion(region);
		//添加样式
		row.setHeightInPoints(35);

		row = sheet.createRow(1);
		cell = row.createCell(0);
		cell.setCellValue("汇总时间："+etcPassRate.getStartTime()+"至"+etcPassRate.getEndTime());
		cell.setCellStyle(style);
		region=new CellRangeAddress(1, 1, 0, 8);
		sheet.addMergedRegion(region);

		row = sheet.createRow(2);
		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);
		return sheet;
	}
	//数据填充
	public HSSFSheet returnSheet(HSSFSheet sheet,HSSFWorkbook workbook,List<EtcPassRateVO> list1,HSSFRow row,HSSFCell cell,HSSFCellStyle style2){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HorizontalAlignment.CENTER);
		int y=3,i=1;
		for(EtcPassRateVO etc:list1){
			row = sheet.createRow(y);
			row.setHeightInPoints(18);
				cell = row.createCell(0);
				cell.setCellStyle(style);
				cell.setCellValue(i);
				cell.setCellStyle(style2);

				cell = row.createCell(1);
				cell.setCellStyle(style);
				cell.setCellValue(etc.getDeptName2());
			cell.setCellStyle(style2);

				cell = row.createCell(2);
				cell.setCellStyle(style);
				cell.setCellValue(etc.getDeptName());
			cell.setCellStyle(style2);

				cell = row.createCell(3);
				cell.setCellStyle(style);
				cell.setCellValue(etc.getRoadName());
			cell.setCellStyle(style2);

				cell = row.createCell(4);
				cell.setCellStyle(style);
				cell.setCellValue(etc.getStationName());
			cell.setCellStyle(style2);

				cell = row.createCell(5);
				cell.setCellStyle(style);
				cell.setCellValue(etc.getPlazaName());
			cell.setCellStyle(style2);

				cell = row.createCell(6);
				cell.setCellStyle(style);
				cell.setCellValue(etc.getLane());
			cell.setCellStyle(style2);

				cell = row.createCell(7);
				cell.setCellStyle(style);
				cell.setCellValue(etc.getSuccessRate());
			cell.setCellStyle(style2);

				cell = row.createCell(8);
				cell.setCellStyle(style);
				cell.setCellValue(etc.getDealPassRate());
			cell.setCellStyle(style2);
			y=y+1;
			i=i+1;
		}
		return sheet;
	}