导入/导出Excel利用apache.poi

一、生成Excel

1、导入jar包

 

maven地址

 

       <dependency>  
            <groupId>org.apache.poi</groupId>  
            <artifactId>poi</artifactId>  
            <version>3.16</version>  
        </dependency>  

 

 

 

 

 

2、编写ExcelUtil工具类

 

package com.qn.yun.utils;

import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;

public class ExcelUtil {

	private Workbook wb = null;

	private Sheet sheet = null;

	public ExcelUtil(Workbook wb, Sheet sheet) {
		super();
		this.wb = wb;
		this.sheet = sheet;
	}

	/**
	 * 设置表头样式
	 * 
	 * @return
	 */
	public CellStyle getHeadStyle() {
		// 创建单元格样式
		CellStyle cellStyle = wb.createCellStyle();
		// 设置单元格的背景颜色为淡蓝色
		cellStyle.setFillForegroundColor(HSSFColorPredefined.LIGHT_YELLOW.getIndex());
                // 设置背景色-自定义灰色
                // ((XSSFCellStyle) cellStyle).setFillForegroundColor(new XSSFColor(new Color(242, 242, 242)));
		cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		// 设置单元格居中对齐
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		Font font = wb.createFont();
		// 设置字体加粗
		font.setBold(true);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 设置单元格边框为细线条
		cellStyle.setBorderLeft(BorderStyle.THIN);
		cellStyle.setBorderBottom(BorderStyle.THIN);
		cellStyle.setBorderRight(BorderStyle.THIN);
		cellStyle.setBorderTop(BorderStyle.THIN);
		return cellStyle;
	}

	public void buildTitle(Sheet sheet, String[] titles) {
		Row headRow = sheet.createRow(0);
		Cell cell = null;
		for (int i = 0; i < titles.length; i++) {
			cell = headRow.createCell(i);
			cell.setCellStyle(getHeadStyle());
			cell.setCellValue(titles[i]);
		}
	}

	/**
	 * 设置表体的单元格样式
	 * 
	 * @return
	 */
	public CellStyle getBodyStyle() {
		// 创建单元格样式
		CellStyle cellStyle = wb.createCellStyle();
		// 设置单元格居中对齐
		cellStyle.setAlignment(HorizontalAlignment.CENTER);
		// 设置单元格垂直居中对齐
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
		// 创建单元格内容显示不下时自动换行
		cellStyle.setWrapText(true);
		// 设置单元格字体样式
		Font font = wb.createFont();
		// 设置字体加粗
		font.setBold(true);
		font.setFontName("宋体");
		font.setFontHeight((short) 200);
		cellStyle.setFont(font);
		// 设置单元格边