Java使用POI导出Excel文件

1.下载

http://poi.apache.org/
在这里插入图片描述
https://mvnrepository.com/artifact/com.aliyun.openservices
https://mvnrepository.com/artifact/org.apache.poi/poi
在这里插入图片描述

2.流程

工作簿
	|--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));
  1. 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);
  1. 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); 设置字体
  1. HSSFFont 字体样式类
创建方式

|–workbook.createFont();

相关方法

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

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

font.setBold(bold);//加粗

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

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

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

  1. CellRangeAddress
创建方式

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

|–参数1开始行

|–参数2 结束行

|–参数3 开始列

|–参数4 结束列

6.示例

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


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方法缺省
}

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



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


在这里插入图片描述

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

熊猫-IT

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值