POI操作Excel

一、poi导出Excel

 

1、需要jar包：

        <!-- POI，excel导入需要的 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.1</version>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>
    </dependencies>

2、思路：创建workbook 》创建sheet》创建row》创建cell

 

3、从数据库动态查询时，有以下代码

前台相关代码

//导出点击事件
 $("#exportHouse").click(function(){
            window.location.href = getRootPath()+"/myspringboot/exportHouse?name=房屋信息";
        });

getRootPath = function(){
    var curWwwPath = window.document.location.href;
    //获取主机地址之后的目录
    var pathName = window.document.location.pathname;
    var pos = curWwwPath.indexOf(pathName);
    //获取主机地址，如： http://localhost:8080
    var localhostPath = curWwwPath.substring(0, pos);
    //获取带"/"的项目名，如：/ems
    var projectName = pathName.substring(0, pathName.substr(1).indexOf('/') + 1);
    return localhostPath;
};

后台相关代码：

@RequestMapping("/exportHouse")
    public void exportHouse(HttpServletRequest request, HttpServletResponse response,Map map){
        List<Map> list=houseService.getAllHouse(map);
        //sheet名称
        String title =request.getParameter("name");
        //表头
        String[] headers={"房屋编号","用户名","房屋名称","房屋类型","房屋价格","房屋地址","描述"};
        //excel文件名
        String fileName = title + System.currentTimeMillis()+".xls";
        List<List<String>> rows = new ArrayList<List<String>>();
        List<String> row=null;
        for (int i = 0; i < list.size(); i++) {
            row = new ArrayList<String>();
            row.add(list.get(i).get("ID").toString());
            row.add(list.get(i).get("USERNAME").toString());
            row.add(list.get(i).get("TITLE").toString());
            row.add(list.get(i).get("TYPENAME").toString());
            row.add(list.get(i).get("PRICE").toString());
            row.add(list.get(i).get("ADDRESS").toString());
            row.add(list.get(i).get("DESCRIPTION").toString());
            rows.add(row);
        }


        //创建HSSFWorkbook
        HSSFWorkbook wb = Util.getHSSFWorkbook(title, headers, rows);

        //响应到客户端
        try {
            Util.setResponseHeader(response, fileName);
            OutputStream os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

package com.springboot.demo.util;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import javax.servlet.http.HttpServletResponse;
import java.io.UnsupportedEncodingException;
import java.util.List;

public class Util {

    /**
     *
     * @param title 标题
     * @param headers  表头
     * @param values  表中元素
     * @return
     */
    public static HSSFWorkbook getHSSFWorkbook(String title, String headers[], List<List<String>> values){

        //创建一个HSSFWorkbook，对应一个Excel文件
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        //在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet hssfSheet = hssfWorkbook.createSheet(title);

        //创建标题合并行
        hssfSheet.addMergedRegion(new CellRangeAddress(0,(short)0,0,(short)headers.length - 1));

        //设置标题样式
        HSSFCellStyle style = hssfWorkbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);   //设置居中样式
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置标题字体
        Font titleFont = hssfWorkbook.createFont();
        titleFont.setFontHeightInPoints((short) 14);
        style.setFont(titleFont);

        //设置值表头样式 设置表头居中
        HSSFCellStyle hssfCellStyle = hssfWorkbook.createCellStyle();
        hssfCellStyle.setAlignment(HorizontalAlignment.CENTER);   //设置居中样式
        hssfCellStyle.setBorderBottom(BorderStyle.THIN);
        hssfCellStyle.setBorderLeft(BorderStyle.THIN);
        hssfCellStyle.setBorderRight(BorderStyle.THIN);
        hssfCellStyle.setBorderTop(BorderStyle.THIN);

        //设置表内容样式
        //创建单元格，并设置值表头 设置表头居中
        HSSFCellStyle style1 = hssfWorkbook.createCellStyle();
        style1.setBorderBottom(BorderStyle.THIN);
        style1.setBorderLeft(BorderStyle.THIN);
        style1.setBorderRight(BorderStyle.THIN);
        style1.setBorderTop(BorderStyle.THIN);

        //产生标题行
        HSSFRow hssfRow = hssfSheet.createRow(0);
        HSSFCell cell = hssfRow.createCell(0);
        cell.setCellValue(title);
        cell.setCellStyle(style);



        //产生表头
        HSSFRow row1 = hssfSheet.createRow(1);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell hssfCell = row1.createCell(i);
            hssfCell.setCellValue(headers[i]);
            hssfCell.setCellStyle(hssfCellStyle);
        }

        //创建内容
        for (int i = 0; i <values.size(); i++){
            row1 = hssfSheet.createRow(i +2);
            for (int j = 0; j < values.get(i).size(); j++){
                //将内容按顺序赋给对应列对象
                HSSFCell hssfCell = row1.createCell(j);
                hssfCell.setCellValue(values.get(i).get(j).toString());
                hssfCell.setCellStyle(style1);
            }
        }
        return hssfWorkbook;
    }

    //发送响应流方法
    public static void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            try {
                fileName = new String(fileName.getBytes(),"ISO8859-1");
            } catch (UnsupportedEncodingException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            response.setContentType("application/octet-stream;charset=ISO8859-1");
            response.setHeader("Content-Disposition", "attachment;filename="+ fileName);
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

 /**
     * 随机生成long类型的id
     * @return
     */
    public static long getLongResourceId() {
        long millis = System.currentTimeMillis();
        Random random = new Random();
        int end2 = random.nextInt(99);  // 0-98的随机数
        String str = millis + String.format("%02d", end2);  //两位十进制的数  不足的补0  比如 02  03  78
        long id = new Long(str);
        return id;
    }
}

 

 

导出后的excle：

 

二、导入Excel

 

2_1导入（只是简单的实现）

注意：如果文件是Xlsx  则用 XSSFWorkbook；如果文件是Xls格式，则用HSSFWorkbook；两者方法完全相同

/**
	 * 导入
	 * @param picFile
	 * @param mobile
	 * @param request
	 * @param session
	 * @return
	 * @throws Exception 
	 */
	@SuppressWarnings("null")
	@ResponseBody
	@RequestMapping(value="/importExcel.html",method=RequestMethod.POST)
	public String add2(Mobile mobile,String type,HttpServletRequest request,HttpSession session) throws Exception{
		XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(new File("E:/测试/手机信息导出.xls")));
		//get sheet 0
		XSSFSheet sheet = wb.getSheetAt(0);
		//get columns
		XSSFRow row = sheet.getRow(0);
		int cn = row.getLastCellNum();
		
		int idCell=-1;
		int mobileTypeCell=-1;
		int mobileDescCell=-1;
		int onTimeCell=-1;
		int priceCell=-1;
		int picpathCell=-1;
		for (int i = 0; i < cn; i++) {
			XSSFCell cell = row.getCell(i);
			switch (getCellValue(cell)) {
			case "编号": idCell=i; break;
			case "手机类型": mobileTypeCell=i; break;
			case "手机详情": mobileDescCell=i; break;
			case "上架时间": onTimeCell=i; break;
			case "价格": priceCell=i; break;
			case "图片名称": picpathCell=i; break;

			}
		}
		for (int j = 1; j <= sheet.getLastRowNum(); j++) {//循环取得各行信息
			XSSFRow eachRow = sheet.getRow(j);//取得当前行
			if(idCell != -1){
				XSSFCell idCellCellValue = eachRow.getCell(idCell);
				String idCellvalue =getCellValue(idCellCellValue);
				mobile.setId(idCellvalue);
			}
			if(mobileTypeCell != -1){
				XSSFCell idCellCellValue = eachRow.getCell(mobileTypeCell);
				String idCellvalue =getCellValue(idCellCellValue);
				mobile.setMobiletype(idCellvalue);
			}
			if(mobileDescCell != -1){
				XSSFCell idCellCellValue = eachRow.getCell(mobileDescCell);
				String idCellvalue =getCellValue(idCellCellValue);
				mobile.setMobiledesc(idCellvalue);
			}
			if(onTimeCell != -1){
				XSSFCell idCellCellValue = eachRow.getCell(onTimeCell);
				Date idCellvalue =new SimpleDateFormat("yyyy-MM-dd").parse(getCellValue(idCellCellValue));
				mobile.setOntime(idCellvalue);
			}
			if(priceCell != -1){
				XSSFCell idCellCellValue = eachRow.getCell(priceCell);
				double idCellvalue =Double.parseDouble(getCellValue(idCellCellValue));
				mobile.setPrice(idCellvalue);
			}
			if(picpathCell != -1){
				XSSFCell idCellCellValue = eachRow.getCell(picpathCell);
				String idCellvalue =getCellValue(idCellCellValue);
				mobile.setPicpath(idCellvalue);
			}
			
			mobileService.insertSelective(mobile);
		}
		return null;
	}

//获取表格中的信息并格式化返回的方法
public static String getCellValue(XSSFCell cell) {  
        String cellValue = "";  
        DecimalFormat df = new DecimalFormat("#"); 
        if (cell != null) {
        switch (cell.getCellType()) {  
            case XSSFCell.CELL_TYPE_STRING:  
                cellValue = cell.getRichStringCellValue().getString().trim();  
                break;  
            case XSSFCell.CELL_TYPE_NUMERIC:  
                cellValue = df.format(cell.getNumericCellValue()).toString();  
                break;  
            case XSSFCell.CELL_TYPE_BOOLEAN:  
                cellValue = String.valueOf(cell.getBooleanCellValue()).trim();  
                break;  
            case XSSFCell.CELL_TYPE_FORMULA:  
                cellValue = cell.getCellFormula();  
                break;  
            default:  
                cellValue = "";  
            }  
        }
        return cellValue;  
    }