springboot+poi导出和导入

1.导入坐标

<!-- excel2003使用的包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.11</version>
        </dependency>
        <!-- excel2007+使用的包 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.11</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>3.11</version>
        </dependency>

2 poi 导出的controller

package com.czxy.web.reportform;

import com.czxy.common.DownloadUtil;
import com.czxy.domain.teke_delivery.WayBill;
import com.czxy.service.take_delivery.WayBillService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Date;
import java.util.List;

@RestController
@RequestMapping("/poi")
public class PoiController {

    @Autowired
    private WayBillService wayBillService;

    @GetMapping("/exportXls")
    public void exportXls(HttpServletResponse response) throws Exception{

        // 导出  运单信息
        List<WayBill> wayBillList = wayBillService.findAll();

        //1 创建工作簿 HSSFWorkbook   2003    XSSFWorkbook   2007
        Workbook wb = new XSSFWorkbook();
        //2 创建工作表
        Sheet sheet = wb.createSheet();

        // 设置列宽
        sheet.setColumnWidth(0,10*256);
        sheet.setColumnWidth(1,10*256);
        sheet.setColumnWidth(2,10*256);
        sheet.setColumnWidth(3,20*256);
        sheet.setColumnWidth(4,20*256);
        sheet.setColumnWidth(5,20*256);
        sheet.setColumnWidth(6,20*256);
        sheet.setColumnWidth(7,20*256);
        sheet.setColumnWidth(8,20*256);




        /***
         * 定义公共变量
         */
        int rowNo = 0,cellNo = 0;
        Row nRow = null;
        Cell nCell = null;

        /**************大标题*************/
        //3 创建行
        nRow = sheet.createRow(rowNo);
        // 设置行高
        nRow.setHeightInPoints(36);

        //4 创建单元格
        nCell = nRow.createCell(cellNo);
        //5 设置内容
        nCell.setCellValue("bos系统运单信息"+new Date().toLocaleString());
        //6 设置内容格式
        // 合并单元格
        //参数1：起始行 参数2：终止行 参数3：起始列 参数4：终止列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, (short) 0, (short) 9));

        // 横向居中  +   水平居中   +  红色宋体22号
        nCell.setCellStyle(bigTitleCellStyle(wb));


        /*************小标题输出**************/
        // 行号rowNo需要变化吗  列需要变化吗？
        rowNo++;

        String[] titles = {"id","运单号","订单号","寄件人姓名","寄件人电话","寄件人地址","收件人姓名","收件人电话","收件人地址"};

        //3 创建行
        nRow = sheet.createRow(rowNo);
        for (String title:titles){

            //4 创建单元格
            nCell = nRow.createCell(cellNo++);// 先创建cell单元格，然后在自增
            //5 设置内容
            nCell.setCellValue(title);
            //6 设置内容格式
            nCell.setCellStyle(titleCellStyle(wb));
        }


        /**************内容*************/
        // 行号和列号需要变化？
        rowNo++;


        for(WayBill wayBill:wayBillList){
            cellNo=0;
            //3 创建行
            nRow = sheet.createRow(rowNo++);

            //4 创建单元格
            nCell = nRow.createCell(cellNo++);
            //5 设置内容
            nCell.setCellValue(wayBill.getId()+"");
            //6 设置内容格式
            nCell.setCellStyle(contentCellStyle(wb));
            // wayBillNum
            nCell = nRow.createCell(cellNo++);
            nCell.setCellValue(wayBill.getWayBillNum());
            nCell.setCellStyle(contentCellStyle(wb));
            //订单号
            nCell = nRow.createCell(cellNo++);

            nCell.setCellStyle(contentCellStyle(wb));
            //发件人姓名
            nCell = nRow.createCell(cellNo++);
            nCell.setCellValue(wayBill.getSendName());
            nCell.setCellStyle(contentCellStyle(wb));
            //发件人电话
            nCell = nRow.createCell(cellNo++);
            nCell.setCellValue(wayBill.getSendMobile());
            nCell.setCellStyle(contentCellStyle(wb));
            //发件人地址
            nCell = nRow.createCell(cellNo++);
            nCell.setCellValue(wayBill.getSendAddress());
            nCell.setCellStyle(contentCellStyle(wb));
            //收件人姓名
            nCell = nRow.createCell(cellNo++);
            nCell.setCellValue(wayBill.getRecName());
            nCell.setCellStyle(contentCellStyle(wb));

            //收件人电话
            nCell = nRow.createCell(cellNo++);
            nCell.setCellValue(wayBill.getRecMobile());
            nCell.setCellStyle(contentCellStyle(wb));
            //收件人地址
            nCell = nRow.createCell(cellNo++);
            nCell.setCellValue(wayBill.getRecAddress());
            nCell.setCellStyle(contentCellStyle(wb));
        }




        /*************7 下载**********************/
        DownloadUtil downloadUtil = new DownloadUtil();

        ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
        // 将wb写入流
        wb.write(byteArrayOutputStream);

        /**
         byteArrayOutputStream 将文件内容写入ByteArrayOutputStream
         response HttpServletResponse	写入response
         returnName 返回的文件名

         */
        downloadUtil.download(byteArrayOutputStream,response,"bos运单表.xlsx");


    }

    public CellStyle bigTitleCellStyle(Workbook wb){
//        横向居中  +   水平居中   +  红色宋体22号
        CellStyle cellStyle = wb.createCellStyle();
        // 横向居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        Font font = wb.createFont();
        font.setFontHeight((short) 440);
        font.setColor(Font.COLOR_RED);
        font.setFontName("宋体");

        cellStyle.setFont(font);

        return cellStyle;
    }

    public CellStyle titleCellStyle(Workbook wb){
        // 宋体16号  倾斜   边框线   水平垂直居中
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setItalic(true);
        font.setBold(true);

        CellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFont(font);

        // 边框线
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 细线
        cellStyle.setBorderRight(CellStyle.BORDER_DASHED);//圆点....
        cellStyle.setBorderBottom(CellStyle.BORDER_DOTTED);// 矩形的虚线_ _ _ _ _
        cellStyle.setBorderLeft(CellStyle.BORDER_DOUBLE);// 双线
        // 横向居中
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        // 垂直居中
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);

        return cellStyle;

    }
    public CellStyle contentCellStyle(Workbook wb){
        // 边框线   水平垂直居中
        CellStyle cellStyle = wb.createCellStyle();
        // 边框线
        cellStyle.setBorderTop(CellStyle.BORDER_THIN);// 细线
        cellStyle.setBorderRight(CellStyle.BORDER_THIN);//
        cellStyle.setBorderBottom(CellStyle.BORDER_THIN);//
        cellStyle.setBorderLeft(CellStyle.BORDER_THIN);//

        return cellStyle;

    }

}

3.下载文件的utils

package com.czxy.common;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;

public class DownloadUtil {
	
	/**
	 * @param filePath 要下载的文件路径
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	protected void download(String filePath,String returnName,HttpServletResponse response,boolean delFlag){
		this.prototypeDownload(new File(filePath), returnName, response, delFlag);
	}


	/**
	 * @param file 要下载的文件
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	protected void download(File file,String returnName,HttpServletResponse response,boolean delFlag){
		this.prototypeDownload(file, returnName, response, delFlag);
	}
	
	/**
	 * @param file 要下载的文件
	 * @param returnName 返回的文件名
	 * @param response HttpServletResponse
	 * @param delFlag 是否删除文件
	 */
	public void prototypeDownload(File file,String returnName,HttpServletResponse response,boolean delFlag){
		// 下载文件
		FileInputStream inputStream = null;
		ServletOutputStream outputStream = null;
		try {
			if(!file.exists()) return;
			response.reset();
			//设置响应类型	PDF文件为"application/pdf"，WORD文件为："application/msword"， EXCEL文件为："application/vnd.ms-excel"。  
			response.setContentType("application/octet-stream;charset=utf-8");

			//设置响应的文件名称,并转换成中文编码
			//returnName = URLEncoder.encode(returnName,"UTF-8");
			returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));	//保存的文件名,必须和页面编码一致,否则乱码
			
			//attachment作为附件下载；inline客户端机器有安装匹配程序，则直接打开；注意改变配置，清除缓存，否则可能不能看到效果
			response.addHeader("Content-Disposition",   "attachment;filename="+returnName);  
			
			//将文件读入响应流
			inputStream = new FileInputStream(file);
			outputStream = response.getOutputStream();
			int length = 1024;
			int readLength=0;
			byte buf[] = new byte[1024];
			readLength = inputStream.read(buf, 0, length);
			while (readLength != -1) {
				outputStream.write(buf, 0, readLength);
				readLength = inputStream.read(buf, 0, length);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				outputStream.flush();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				outputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			try {
				inputStream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			//删除原文件
			
			if(delFlag) {				
				file.delete();
			}
		}
	}

	/**
	 * by tony 2013-10-17
	 * @param byteArrayOutputStream 将文件内容写入ByteArrayOutputStream
	 * @param response HttpServletResponse	写入response
	 * @param returnName 返回的文件名
	 */
	public void download(ByteArrayOutputStream byteArrayOutputStream, HttpServletResponse response, String returnName) throws IOException{
		response.setContentType("application/octet-stream;charset=utf-8");
		returnName = response.encodeURL(new String(returnName.getBytes(),"iso8859-1"));			//保存的文件名,必须和页面编码一致,否则乱码
		response.addHeader("Content-Disposition",   "attachment;filename=" + returnName);  
		response.setContentLength(byteArrayOutputStream.size());
		
		ServletOutputStream outputstream = response.getOutputStream();	//取得输出流
		byteArrayOutputStream.writeTo(outputstream);					//写到输出流
		byteArrayOutputStream.close();									//关闭
		outputstream.flush();											//刷数据
	}
}

4 导入Excel文件

public static void main(String[] args) throws  Exception{
    // 流读取文件
    FileInputStream is = new FileInputStream(new File("d:\\area.xls"));
    // 根据流创建文件
    Workbook wb = new HSSFWorkbook(is);
    // 获取sheet
    Sheet sheet = wb.getSheetAt(0);
    // 遍历row
    for(Row row:sheet){
        // 第一行是标题，不需要读取
        if(row.getRowNum()==0){
            continue;
        }
        // 当遇到空行，跳过
        if(row.getCell(0)==null || StringUtils.isBlank(row.getCell(1).getStringCellValue())){
            continue;
        }
        // 打印内容
        System.out.print(row.getCell(0).getStringCellValue()+":");
        System.out.print(row.getCell(1).getStringCellValue()+":");
        System.out.print(row.getCell(2).getStringCellValue()+":");
        System.out.print(row.getCell(3).getStringCellValue()+":");
        System.out.println(row.getCell(4).getStringCellValue());
    }
}