欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

springboot+poi导出和导入

程序员文章站 2022-03-06 22:20:30
...

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());
    }
}

 

相关标签: poi