使用POI实现Excel的导出

一.使用POI实现excel的导出

1.下载
1.下载
http://poi.apache.org/

https://mvnrepository.com/artifact/com.aliyun.openservices
https://mvnrepository.com/artifact/org.apache.poi/poi




2.excel文件说明

工作簿
	|--sheet1
		|--row   column
	|--sheet2
流程
	创建工作簿
		在工作簿里面创建sheet
			在sheet里面创建row
				在row里创建cell单元

3.导出代码样式封装[ExprotCellStyle]

package com.sxt.utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class ExprotCellStyle {

	/**
	 * 得到表头样式
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle createTableTitleStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = setRowCellCenter(workbook);//水平居中
		//设置字体
		HSSFFont font = setFontCellStyle(workbook, (short)15, HSSFColorPredefined.BLUE.getIndex(), true,false, HSSFFont.U_NONE);
		font.setFontName("华文琥珀");
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * 创建小标题的样式
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle createSecondTitleStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = setRowCellCenter(workbook);//水平居中
		//设置字体
		HSSFFont font = setFontCellStyle(workbook, (short)18, HSSFColorPredefined.GOLD.getIndex(), true,false, HSSFFont.U_NONE);
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * 创建表头的样式
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = setRowCellCenter(workbook);
		//设置字体
		HSSFFont font = setFontCellStyle(workbook, (short)30, HSSFColorPredefined.RED.getIndex(), true,false, HSSFFont.U_DOUBLE);
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * 
	 * @param workbook  工作簿
	 * @param fontSize  字体大小 
	 * @param colorIndex 字体颜色  @see HSSFColorPredefined
	 * @param bold  是否加粗
	 * @param italic  是否斜体
	 * @param undderLine   下划线风格  @see HSSFFont.U_DOUBLE
	 * @return
	 */
	public static HSSFFont setFontCellStyle(HSSFWorkbook workbook,
			short fontSize, short colorIndex, boolean bold, boolean italic,
			byte undderLine) {
		HSSFFont font=workbook.createFont();
		font.setFontHeightInPoints(fontSize);//字体大小
		font.setColor(colorIndex);//设置字体颜色
		font.setBold(bold);//加粗
		font.setItalic(italic);//设置非斜体
		font.setUnderline(undderLine);//设置下划线
		return font;
	}

	/**
	 * 创建水平和垂直居 的方法
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle setRowCellCenter(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		return cellStyle;
	}
	
	
}

4.导出代码

package com.sxt.utils;

import static com.sxt.utils.ExprotCellStyle.*;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.sxt.domain.User;
/**
 * 导出用户数据
 * @author LJH
 *
 */
public class ExportUserUtils {
	

	/**
	 * 把数据导出到path
	 * @param users
	 */
	@SuppressWarnings("deprecation")
	public static void exportUser(List<User> users,String path) {
		//创建工作簿
		HSSFWorkbook workbook=new HSSFWorkbook();
		//从工作簿里面创建sheet
//		workbook.createSheet();
		HSSFSheet sheet = workbook.createSheet("用户列表");
		//设置sheet
		//sheet.setColumnWidth(2, 100);//设置某一个下标的列宽
		//sheet.setDefaultColumnStyle(column, style);//设置某一列的默认样式
		sheet.setDefaultColumnWidth(20);//设置所有列的列宽
//		sheet.setColumnHidden(columnIndex, hidden);//设置某一列是否隐藏
		//sheet.setDefaultRowHeight((short)(30*20));//设置行高60
		//sheet.setDefaultRowHeightInPoints(30);//设置行高30
		//合并
		CellRangeAddress region1=new CellRangeAddress(0, 0, 0, 2);
		CellRangeAddress region2=new CellRangeAddress(1, 1, 0, 2);
		sheet.addMergedRegion(region1);
		sheet.addMergedRegion(region2);
		
		//的sheet上创建行
		int rownum=0;
		HSSFRow row01 = sheet.createRow(rownum);
		//在row01上创建单元格	
		HSSFCell cell_row01 = row01.createCell(0);
		//向cell_row01写东西
		cell_row01.setCellValue("用户数据");
		//设置标题样式
		HSSFCellStyle titleStyle=createTitleCellStyle(workbook);
		cell_row01.setCellStyle(titleStyle);
		
		//第二行
		rownum++;
		HSSFRow row02 = sheet.createRow(rownum);
		HSSFCell cell_row02 = row02.createCell(0);
		cell_row02.setCellValue("总数:"+users.size()+"，导出时间:"+new Date().toLocaleString());
		//设置小标题样式
		HSSFCellStyle secondTitleStyle=createSecondTitleStyle(workbook);
		cell_row02.setCellStyle(secondTitleStyle);
		
		//第三行
		rownum++;
		HSSFRow row03 = sheet.createRow(rownum);
		String[] titles={"用户ID","用户名","用户地址"};
		//得到表头的样式
		HSSFCellStyle tableTitleStyle=createTableTitleStyle(workbook);
		
		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell = row03.createCell(i);
			 cell.setCellValue(titles[i]);
			 cell.setCellStyle(tableTitleStyle);
		}
		
		HSSFCellStyle tableBodyStyle=setRowCellCenter(workbook);
		for (int i = 0; i < users.size(); i++) {
			rownum++;
			HSSFRow row = sheet.createRow(rownum);
			User user =users.get(i);
			//创建idcell
			HSSFCell idCell = row.createCell(0);
			idCell.setCellValue(user.getId());
			
			idCell.setCellStyle(tableBodyStyle);
			//创建namecell
			HSSFCell nameCell = row.createCell(1);
			nameCell.setCellValue(user.getName());
			nameCell.setCellStyle(tableBodyStyle);
			
			//创建addresscell
			HSSFCell addressCell = row.createCell(2);
			addressCell.setCellValue(user.getAddress());
			addressCell.setCellStyle(tableBodyStyle);
		}
		
		//导出数据
		try {
			workbook.write(new File(path));
			System.out.println("导出完成");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}

5.总结

1，HSSFWorkBook

|–创建方式   new HSSFWorkBook()

|–相关方法

|–workbook.createSheet()

|–workbook.createSheet(“name”)

|–workbook.write(new File(path));

2，HSSFSheet

	

|–创建方式  -workbook.createSheet()

|–相关方法
//sheet.setColumnWidth(2, 100);//设置某一个下标的列宽
//sheet.setDefaultColumnStyle(column, style);//设置某一列的默认样式

sheet.setDefaultColumnWidth(20);//设置所有列的列宽

//		sheet.setColumnHidden(columnIndex, hidden);//设置某一列是否隐藏
//sheet.setDefaultRowHeight((short)(30*20));//设置行高60
//sheet.setDefaultRowHeightInPoints(30);//设置行高30

//合并

CellRangeAddress region1=new CellRangeAddress(0, 0, 0, 2);

CellRangeAddress region2=new CellRangeAddress(1, 1, 0, 2);

sheet.addMergedRegion(region1);

sheet.addMergedRegion(region2);

3，HSSFRow

创建方式

|–sheet.creatRow(rowIndex)

相关方法

|–row01.createCell(0);  在行里面创建列

4，HSSFCell

创建方式	

|–row01.createCell(0);  在行里面创建列

相关方法

|–cell_row01.setCellStyle(titleStyle)  设置样式

|-- cell.setCellValue(titles[i]);  设置里面的内容

5，HSSFCellStyle 样式类

	创建方式
		|--workbook. createCellStyle()
	相关方法
		cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		cellStyle.setFont(font); 设置字体

6，HSSFFont 字体样式类

创建方式

|–workbook.createFont();

相关方法

font.setFontHeightInPoints(fontSize);//字体大小

font.setColor(colorIndex);//设置字体颜色

font.setBold(bold);//加粗

font.setItalic(italic);//设置非斜体

font.setUnderline(undderLine);//设置下划线

font.setFontName(“华文琥珀”);//设置字体风格

创建方式

|–new CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)

|–参数1开始行

|–参数2 结束行

|–参数3 开始列

|–参数4 结束列

代码示例:

com.sxt.domain

Tpackage com.sxt.domain;

import java.util.ArrayList;
import java.util.List;

import com.sxt.utils.ExportUserUtils;

public class TestUser {
	
	public static void main(String[] args) {
		List<User> users=new ArrayList<>();
		for (int i = 1; i <=10; i++) {
			users.add(new User(i, "小明"+i, "武汉"+i));
		}
		String path="E:/user.xls";
		//导出
		ExportUserUtils.exportUser(users,path);
	}
}

package com.sxt.domain;

public class User {
	
	private Integer id;
	private String name;
	private String address;
	public User() {
	}
	public User(Integer id, String name, String address) {
		super();
		this.id = id;
		this.name = name;
		this.address = address;
	}
	//set和get方法缺省
}

com.sxt.utils

package com.sxt.utils;

import static com.sxt.utils.ExprotCellStyle.*;

import java.io.File;
import java.io.IOException;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

import com.sxt.domain.User;
/**
 * 导出用户数据
 * @author LJH
 *
 */
public class ExportUserUtils {
	

	/**
	 * 把数据导出到path
	 * @param users
	 */
	@SuppressWarnings("deprecation")
	public static byte[] exportUser(List<User> users,String path) {
		//创建工作簿
		HSSFWorkbook workbook=new HSSFWorkbook();
		//从工作簿里面创建sheet
//		workbook.createSheet();
		HSSFSheet sheet = workbook.createSheet("用户列表");
		//设置sheet
		//sheet.setColumnWidth(2, 100);//设置某一个下标的列宽
		//sheet.setDefaultColumnStyle(column, style);//设置某一列的默认样式
		sheet.setDefaultColumnWidth(20);//设置所有列的列宽
//		sheet.setColumnHidden(columnIndex, hidden);//设置某一列是否隐藏
		//sheet.setDefaultRowHeight((short)(30*20));//设置行高60
		//sheet.setDefaultRowHeightInPoints(30);//设置行高30
		//合并
		CellRangeAddress region1=new CellRangeAddress(0, 0, 0, 2);
		CellRangeAddress region2=new CellRangeAddress(1, 1, 0, 2);
		sheet.addMergedRegion(region1);
		sheet.addMergedRegion(region2);
		
		//的sheet上创建行
		int rownum=0;
		HSSFRow row01 = sheet.createRow(rownum);
		//在row01上创建单元格	
		HSSFCell cell_row01 = row01.createCell(0);
		//向cell_row01写东西
		cell_row01.setCellValue("用户数据");
		//设置标题样式
		HSSFCellStyle titleStyle=createTitleCellStyle(workbook);
		cell_row01.setCellStyle(titleStyle);
		
		//第二行
		rownum++;
		HSSFRow row02 = sheet.createRow(rownum);
		HSSFCell cell_row02 = row02.createCell(0);
		cell_row02.setCellValue("总数:"+users.size()+"，导出时间:"+new Date().toLocaleString());
		//设置小标题样式
		HSSFCellStyle secondTitleStyle=createSecondTitleStyle(workbook);
		cell_row02.setCellStyle(secondTitleStyle);
		
		//第三行
		rownum++;
		HSSFRow row03 = sheet.createRow(rownum);
		String[] titles={"用户ID","用户名","用户地址"};
		//得到表头的样式
		HSSFCellStyle tableTitleStyle=createTableTitleStyle(workbook);
		
		for (int i = 0; i < titles.length; i++) {
			HSSFCell cell = row03.createCell(i);
			 cell.setCellValue(titles[i]);
			 cell.setCellStyle(tableTitleStyle);
		}
		
		HSSFCellStyle tableBodyStyle=setRowCellCenter(workbook);
		for (int i = 0; i < users.size(); i++) {
			rownum++;
			HSSFRow row = sheet.createRow(rownum);
			User user =users.get(i);
			//创建idcell
			HSSFCell idCell = row.createCell(0);
			idCell.setCellValue(user.getId());
			
			idCell.setCellStyle(tableBodyStyle);
			//创建namecell
			HSSFCell nameCell = row.createCell(1);
			nameCell.setCellValue(user.getName());
			nameCell.setCellStyle(tableBodyStyle);
			
			//创建addresscell
			HSSFCell addressCell = row.createCell(2);
			addressCell.setCellValue(user.getAddress());
			addressCell.setCellStyle(tableBodyStyle);
		}
		
		//导出数据
		try {
			workbook.write(new File(path));
			return workbook.getBytes();
		} catch (Exception e) {
			e.printStackTrace();
		}finally
		{
			try {
				workbook.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		
		return null;
	}
}

package com.sxt.utils;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;

public class ExprotCellStyle {

	/**
	 * 得到表头样式
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle createTableTitleStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = setRowCellCenter(workbook);//水平居中
		//设置字体
		HSSFFont font = setFontCellStyle(workbook, (short)15, HSSFColorPredefined.BLUE.getIndex(), true,false, HSSFFont.U_NONE);
		font.setFontName("华文琥珀");
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * 创建小标题的样式
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle createSecondTitleStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = setRowCellCenter(workbook);//水平居中
		//设置字体
		HSSFFont font = setFontCellStyle(workbook, (short)18, HSSFColorPredefined.GOLD.getIndex(), true,false, HSSFFont.U_NONE);
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * 创建表头的样式
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = setRowCellCenter(workbook);
		//设置字体
		HSSFFont font = setFontCellStyle(workbook, (short)30, HSSFColorPredefined.RED.getIndex(), true,false, HSSFFont.U_DOUBLE);
		cellStyle.setFont(font);
		return cellStyle;
	}

	/**
	 * 
	 * @param workbook  工作簿
	 * @param fontSize  字体大小 
	 * @param colorIndex 字体颜色  @see HSSFColorPredefined
	 * @param bold  是否加粗
	 * @param italic  是否斜体
	 * @param undderLine   下划线风格  @see HSSFFont.U_DOUBLE
	 * @return
	 */
	public static HSSFFont setFontCellStyle(HSSFWorkbook workbook,
			short fontSize, short colorIndex, boolean bold, boolean italic,
			byte undderLine) {
		HSSFFont font=workbook.createFont();
		font.setFontHeightInPoints(fontSize);//字体大小
		font.setColor(colorIndex);//设置字体颜色
		font.setBold(bold);//加粗
		font.setItalic(italic);//设置非斜体
		font.setUnderline(undderLine);//设置下划线
		return font;
	}

	/**
	 * 创建水平和垂直居 的方法
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle setRowCellCenter(HSSFWorkbook workbook) {
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		return cellStyle;
	}
	
	
}