poi操作excel导入\导出

最新推荐文章于 2025-01-19 21:49:38 发布

原创最新推荐文章于 2025-01-19 21:49:38 发布 · 974 阅读

0 ·

CC 4.0 BY-SA版权

excel 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一种使用Java实现的Excel文件导入导出方法，包括自定义注解以简化映射过程，设置单元格样式，并提供了完整的代码示例。

package com.gkwl.util.excel;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;


/** 
 * ExcelAnnotation类主要用于-.
 * 
 * @author HuangXinyu
 * 
 * @date 2013-12-9 下午02:17:16
 *
 * @version 1.0 
 */
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface ExcelAnnotation {
     // excel导出时标题显示的名字，如果没有设置Annotation属性，将不会被导出和导入
    public String exportName();

}

package com.gkwl.util.excel;

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;

/**
 * ExcelStyle类主要用于-excel导出样式控制.
 * 
 * @author HuangXinyu
 * 
 * @date 2013-12-9 下午02:18:43
 * 
 * @version 1.0
 */
public class ExcelStyle {
	public static HSSFCellStyle setHeadStyle(HSSFWorkbook workbook,
			HSSFCellStyle style) {
		//style.setFillForegroundColor(HSSFColor.ORANGE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// 生成字体
		HSSFFont font = workbook.createFont();
		//font.setColor(HSSFColor.VIOLET.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		// 把字体应用到当前的样样式
		style.setFont(font);
		return style;

	}

	public static HSSFCellStyle setbodyStyle(HSSFWorkbook workbook,
			HSSFCellStyle style) {
		//style.setFillForegroundColor(HSSFColor.ORANGE.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 生成字体
		HSSFFont font = workbook.createFont();
		font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		// 把字体应用到当前的样样式
		style.setFont(font);
		return style;
	}
}

package com.gkwl.util.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import jxl.Workbook;
import jxl.write.WritableWorkbook;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.gkwl.util.tool.DateUtil;


/** 
 * 
 * @author HuangXinyu
 * 
 * @date 2013-12-9 下午12:09:31
 *
 * @version 1.0 
 */
public class ExecelOutputTest {

	// 设置cell编码解决中文高位字节截断
	// private static short XLS_ENCODING = HSSFCell.ENCODING_UTF_16;
	// 定制浮点数格式
	private static String NUMBER_FORMAT = "#,##0.00";
	// 定制日期格式
	private static String DATE_FORMAT = "yyyy-mm-dd hh:mm:ss"; // "m/d/yy h:mm"
	private OutputStream out = null;
	private HSSFWorkbook workbook = null;
	private HSSFSheet sheet = null;
	private HSSFRow row = null;

	public ExecelOutputTest() {
	}

	/**
	 * 初始化Excel
	 * 
	 */
	public ExecelOutputTest(OutputStream out) {
		this.out = out;
		this.workbook = new HSSFWorkbook();
		this.sheet = workbook.createSheet();
	}

	/**
	 * 导出Excel文件
	 * 
	 * @throws IOException
	 */
	public void export() throws FileNotFoundException, IOException {
		try {
			workbook.write(out);
			out.flush();
			out.close();
		} catch (FileNotFoundException e) {
			throw new IOException(" 生成导出Excel文件出错! ", e);
		} catch (IOException e) {
			throw new IOException(" 写入Excel文件出错! ", e);
		}

	}

	/**
	 * 增加一行
	 * 
	 * @param index
	 *            行号
	 */
	public void createRow(int index) {
		this.row = this.sheet.createRow(index);
	}

	/**
	 * 获取单元格的值
	 * 
	 * @param index
	 *            列号
	 */
	public String getCell(int index) {
		@SuppressWarnings("deprecation")
		HSSFCell cell = this.row.getCell((short) index);
		String strExcelCell = "";
		if (cell != null) { // add this condition
			// judge
			switch (cell.getCellType()) {
			case HSSFCell.CELL_TYPE_FORMULA:
				strExcelCell = "FORMULA ";
				break;
			case HSSFCell.CELL_TYPE_NUMERIC:
				strExcelCell = String.valueOf(cell.getNumericCellValue());
				break;
			case HSSFCell.CELL_TYPE_STRING:
				strExcelCell = cell.getStringCellValue();
				break;
			case HSSFCell.CELL_TYPE_BLANK:
				strExcelCell = "";
				break;
			default:
				strExcelCell = "";
				break;
			}
		}
		return strExcelCell;
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, int value) {
		@SuppressWarnings("deprecation")
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	@SuppressWarnings("deprecation")
	public void setCell(int index, double value) {
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
		HSSFDataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
		cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, String value) {
		@SuppressWarnings("deprecation")
		HSSFCell cell = this.row.createCell((short) index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		// cell.setEncoding(XLS_ENCODING);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, Calendar value) {
		@SuppressWarnings("deprecation")
		HSSFCell cell = this.row.createCell((short) index);
		// cell.setEncoding(XLS_ENCODING);
		cell.setCellValue(value.getTime());
		HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(DATE_FORMAT)); // 设置cell样式为定制的日期格式
		cell.setCellStyle(cellStyle); // 设置该cell日期的显示格式
	}

	public static void main(String[] args) throws IOException {
		List<PersonInfo> personInfos = new ArrayList<PersonInfo>();
		for (int i = 1; i <= 10; i++) {
			PersonInfo p = new PersonInfo();
			p.setId(i);
			p.setName("yang" + i);
			p.setAge(i + 20);
			p.setBirthday(DateUtil.createDate());
			if (i % 2 == 0) {
				p.setSex("male");
			} else {
				p.setSex("female");
			}
			personInfos.add(p);
		}
		System.out.println(" 开始导出Excel文件 ");
		WritableWorkbook book = Workbook.createWorkbook(new File("F:\\workbook1.xls"));
		book.createSheet("Sheet_1", 0);
		File f = new File("F:\\workbook1.xls");
		ExecelOutputTest e = new ExecelOutputTest();
		try {
			// 传一个输出流给构造函数
			e = new ExecelOutputTest(new FileOutputStream(f));
		} catch (FileNotFoundException e1) {
			e1.printStackTrace();
		}
		e.createRow(0);
		e.setCell(0, "编号 ");
		e.setCell(1, "姓名");
		e.setCell(2, "年龄");
		e.setCell(3, "性别");
		e.setCell(4, "出生日期");
		for (int i = 1; i <= personInfos.size(); i++) {
			e.createRow(i);
			e.setCell(0, personInfos.get(i - 1).getId());
			e.setCell(1, personInfos.get(i - 1).getName());
			e.setCell(2, personInfos.get(i - 1).getAge());
			e.setCell(3, personInfos.get(i - 1).getSex());
			SimpleDateFormat sdf = new SimpleDateFormat(DATE_FORMAT);
			String date = sdf.format(personInfos.get(i - 1).getBirthday());
			e.setCell(4, date);
		}
		try {
			e.export();
			System.out.println(" 导出Excel文件[成功] ");
		} catch (IOException ex) {
			System.out.println(" 导出Excel文件[失败] ");
			ex.printStackTrace();
		}
	}
}

package com.gkwl.util.excel;


import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;


import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;


/**
 * ImportExcel类主要用于-Excel导入（POI）
 * 
 * @author HuangXinyu
 * 
 * @date 2013-12-9 下午02:19:49
 * 
 * @version 1.0
 */
public class ImportExcel<T> {
	Class<?> clazz;


	public ImportExcel(Class<?> clazz) {
		this.clazz = clazz;
	}
	@SuppressWarnings("unchecked")
	public Collection<T> importExcel(File file, String... pattern) {
		Collection<T> dist = new ArrayList<T>();
		try {
			/**
			 * 
			 * 类反射得到调用方法
			 */
			// 得到目标目标类的所有的字段列表
			Field[] fields = clazz.getDeclaredFields();
			// 将所有标有Annotation的字段，也就是允许导入数据的字段,放入到一个map中
			Map<String, Method> fieldMap = new HashMap<String, Method>();
			// 循环读取所有字段
			for (Field field : fields) {
				// 得到单个字段上的Annotation
				ExcelAnnotation excelAnnotation = field.getAnnotation(ExcelAnnotation.class);
				// 如果标识了Annotationd
				if (excelAnnotation != null) {
					String fieldName = field.getName();
					// 构造设置了Annotation的字段的Setter方法
					String setMethodName = "set"
							+ fieldName.substring(0, 1).toUpperCase()
							+ fieldName.substring(1);
					// 构造调用的method
					Method setMethod = clazz.getMethod(setMethodName,
							new Class[] { field.getType() });
					// 将这个method以Annotaion的名字为key来存入
					System.out.println("key :"+excelAnnotation.exportName());
					System.out.println("value :"+setMethod);
					fieldMap.put(excelAnnotation.exportName(), setMethod);
				}
			}
			/**
			 * 
			 * excel的解析开始
			 */


			// 将传入的File构造为FileInputStream;
			FileInputStream inputStream = new FileInputStream(file);
			// 得到工作表
			HSSFWorkbook book = new HSSFWorkbook(inputStream);
			// 得到第一页
			HSSFSheet sheet = book.getSheetAt(0);
			// 得到第一面的所有行
			Iterator<Row> row = sheet.rowIterator();
			
			/**
			 * 
			 * 标题解析
			 */
			// 得到第一行，也就是标题行
			Row titleRow = row.next();
			// 得到第一行的所有列
			Iterator<Cell> cellTitle = titleRow.cellIterator();
			// 将标题的文字内容放入到一个map中
			Map<Integer, String> titleMap = new HashMap<Integer, String>();
			// 从标题第一列开始
			int i = 0;
			// 循环标题所有的列


			while (cellTitle.hasNext()) {
				Cell cell = (Cell) cellTitle.next();
				String value = cell.getStringCellValue();
				titleMap.put(i, value);
				i++;
			}
			/**
			 * 
			 * 解析内容行
			 */
			while (row.hasNext()) {
				// 标题下的第一行
				Row rown = row.next();
				// 行的所有列
				Iterator<Cell> cellBody = rown.cellIterator();
				// 得到传入类的实例
				T tObject = (T) clazz.newInstance();
				// 遍历一行的列
				int col = 0;
				int iy=0;
				while (cellBody.hasNext()) {
					Cell cell = (Cell) cellBody.next();
					// 这里得到此列的对应的标题
					String titleString = titleMap.get(col++);
					//{编号=public void com.gkwl.util.excel.PersonInfo.setId(java.lang.Integer), 
					//性别=public void com.gkwl.util.excel.PersonInfo.setSex(java.lang.String),
					//姓名=public void com.gkwl.util.excel.PersonInfo.setName(java.lang.String), 
					//年龄=public void com.gkwl.util.excel.PersonInfo.setAge(java.lang.Integer)}
					// 如果这一列的标题和类中的某一列的Annotation相同，那么则调用此类的的set方法，进行设值
					System.out.println("iy :"+iy);
					System.out.println("titleString"+titleString);
					if (null!=fieldMap.get(titleString)) {
						Method setMethod = fieldMap.get(titleString);
						// 得到setter方法的参数
						Type[] types = setMethod.getGenericParameterTypes();
						// 只要一个参数
						String xclass = String.valueOf(types[0]);
						System.out.println(xclass);
						// 判断参数类型
						if ("class java.lang.String".equals(xclass)) {
							System.out.println("String :"+cell.getStringCellValue());
							setMethod.invoke(tObject, cell.getStringCellValue());
						} else if ("class java.util.Date".equals(xclass)) {
							System.out.println("java.util.Date :"+cell.getStringCellValue());
							setMethod.invoke(tObject, cell.getDateCellValue());
						} else if ("class java.lang.Boolean".equals(xclass)) {
							System.out.println("java.util.Date :"+cell.getStringCellValue());
							Boolean boolName = true;
							if ("否".equals(cell.getStringCellValue())) {
								boolName = false;
							}
							setMethod.invoke(tObject, boolName);
						} else if ("class java.lang.Integer".equals(xclass)) {
							System.out.println("class java.lang.Integer :"+cell.getNumericCellValue());
							setMethod.invoke(tObject,(int)cell.getNumericCellValue());
						} else if ("class java.lang.Long".equals(xclass)) {
							setMethod.invoke(tObject, new Long(cell.getStringCellValue()));
						} else {
							
						}
					}
					iy++;
				}
				dist.add(tObject);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return dist;
	}
	public static void main(String[] args) {
		ImportExcel<PersonInfo> test = new ImportExcel<PersonInfo>(PersonInfo.class);
		File file = new File("F:\\workbook.xls");  
		List<PersonInfo> results = (List<PersonInfo>) test.importExcel(file);
		for (PersonInfo testVo : results) {
			System.out.println(testVo.getId() + "\t"+testVo.getName() + "\t" + testVo.getSex() + "\t"+
					testVo.getAge()+"\t"+testVo.getBirthday());
		}
	}
	
	/**
	 * 导入文件主入口
	 * 
	 * @param <E>
	 * @param entityClass
	 * @param url
	 * @return
	 */
	public <E> List<E> IntroductionFile(Class<?> entityClass,String url){
		ImportExcel<E> test = new ImportExcel<E>(entityClass);
		File file = new File(url);
		List<E> results = (List<E>) test.importExcel(file);
		return results;
	}
	
}

package com.gkwl.util.excel;



/** 
 * 
 * @author HuangXinyu
 * 
 * @date 2013-12-9 下午12:12:50
 *
 * @version 1.0 
 */
public class PersonInfo {
	@ExcelAnnotation(exportName = "编号")
	private Integer id;
	@ExcelAnnotation(exportName = "姓名")
	private String name;
	@ExcelAnnotation(exportName = "年龄")
	private Integer age;
	@ExcelAnnotation(exportName = "出生日期")
	private String birthday;
	@ExcelAnnotation(exportName = "性别")
	private String sex;
	
	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Integer getAge() {
		return age;
	}

	public void setAge(Integer age) {
		this.age = age;
	}
	
	public String getBirthday() {
		return birthday;
	}

	public void setBirthday(String birthday) {
		this.birthday = birthday;
	}

	public void setSex(String sex) {
		this.sex = sex;
	}

	public String getSex() {
		return sex;
	}
	
}

jar包下载