Java 使用POI导出Excel文件

导出为Excel文件，所需要引入的包与导入Excel相同，如下（可能有些不太必要）：

demo的详细代码如下：

import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelExportTest {

	 /*excel column formate:column_#_width, excel中每一列的名称*/
	public static final String[] RECORES_COLUMNS = new String[]{
			"UserNumber_#_5000",
			"ExtensionName_#_5000",
			"PositionIndex_#_5000"
		};
	
	private static final String ORIGINAL_NUM = "1000";
	/*the column will display on xls files. must the same as the entity fields.对应上面的字段.*/
	public static final String[] RECORES_FIELDS = new String[]{
		"Userno","Extname","Posindex"
		};
		
	public static void main(String[] args) throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException, IOException {
		List<UserData> users = new ArrayList<UserData>();
		for(int i=0; i<10;i++){
			UserData u = new UserData();
			u.setUserno(ORIGINAL_NUM+i);
			u.setExtname(ORIGINAL_NUM+i);
			u.setPosindex(String.valueOf(i));
			users.add(u);
		}
			//实际项目中，这个list 估计是从数据库中得到的
		HSSFWorkbook workbook = new HSSFWorkbook();
		ExcelUtil<UserData> userSheet = new ExcelUtil<UserData>();
		for(int j = 0;j<3;j++){
			userSheet.creatAuditSheet(workbook, "sheet"+(j+1),
					users, RECORES_COLUMNS, RECORES_FIELDS);
		}

		FileOutputStream fileOut = new FileOutputStream("D:/test1.xls");
		workbook.write(fileOut);
		fileOut.flush();
		fileOut.close();
		}
}

ExcelUtil类如下：

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

public class ExcelUtil <UserData>{
	public HSSFCellStyle getCellStyle(HSSFWorkbook workbook,boolean isHeader){
		HSSFCellStyle style = workbook.createCellStyle();
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setLocked(true);
		if (isHeader) {
		style.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeightInPoints((short) 12);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);
		}
		return style;
	}
	public void generateHeader(HSSFWorkbook workbook,HSSFSheet sheet,String[] headerColumns){
	HSSFCellStyle style = getCellStyle(workbook,true);
	Row row = sheet.createRow(0);
	row.setHeightInPoints(30);
	for(int i=0;i<headerColumns.length;i++){
		Cell cell = row.createCell(i);
		String[] column = headerColumns[i].split("_#_");
		sheet.setColumnWidth(i, Integer.valueOf(column[1]));
		cell.setCellValue(column[0]);
		cell.setCellStyle(style);
		}
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	public HSSFSheet creatAuditSheet(HSSFWorkbook workbook,String sheetName,
		List<UserData> dataset,String[] headerColumns,String[] fieldColumns) throws NoSuchMethodException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
	
	HSSFSheet sheet = workbook.createSheet(sheetName);
//	sheet.protectSheet("1234");//设置Excel保护密码
	
	generateHeader(workbook,sheet,headerColumns);
	HSSFCellStyle style = getCellStyle(workbook,false);
	SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
	int rowNum = 0;
	for(UserData t:dataset){
		rowNum++ ;
		Row row = sheet.createRow(rowNum);
		row.setHeightInPoints(25);
		for(int i = 0; i < fieldColumns.length; i++){
			String fieldName = fieldColumns[i] ;
			String getMethodName = "get" + fieldName;
			try {
				Class clazz = t.getClass();
				Method getMethod;
				getMethod = clazz.getMethod(getMethodName, new Class[]{} );
				Object value = getMethod.invoke(t, new Object[]{});
				String cellValue = "";
				if (value instanceof Date){
						Date date = (Date)value;
						cellValue = sd.format(date);
					}else{
						cellValue = null != value ? value.toString() : "";
					}
				Cell cell = row.createCell(i);
				cell.setCellStyle(style);
				cell.setCellValue(cellValue);
				} catch (Exception e) {
					
				}
			}
		}
	return sheet;
	}

}

UserData类

public class UserData {

	private String userno;
	
	private String extname;
	
	private String posindex;

	public String getUserno() {
		return userno;
	}

	public void setUserno(String userno) {
		this.userno = userno;
	}

	public String getExtname() {
		return extname;
	}

	public void setExtname(String extname) {
		this.extname = extname;
	}

	public String getPosindex() {
		return posindex;
	}

	public void setPosindex(String posindex) {
		this.posindex = posindex;
	}
	
}

以上代码是简单导出Excel的demo，应该都能看得懂，这里不再详细解释，有问题留言。