java生成EXCEL表格

首先下载 jxl.jar， jxl.jar是通过Java操作excel表格的工具类库

public class OfficeUtils {

	public static void main(String[] args) {
		List<Map<String,String>> datas = new ArrayList<Map<String,String>>();

		for (int i = 0; i < 10; i++) {
			Map<String,String> data = new HashMap<String, String>();
			data.put("用户编号", "用户编号" + i);
		data.put("姓名", "张三" + i);
		data.put("电话", "1532645451" + i);
		datas.add(data);
	}
	System.out.println(OfficeUtils.createExcel("", "C:\\Users\\Duenan\\Desktop\\command\\test\\用户.xls", "用户编号,姓名,电话".split(","),
			datas,"总共:,1700万,这是测试的".split(",")));
}

/**
 * 创建Excel包含数据
 * 数组都按顺序对应列名和字段值
 * @param folder	  存放文件夹路径 ：download
 * @param filename	 文件名   ：统计信息.xls
 * @param columnNames 列名
 * @param dataSource  数据源
 * @param valueNames  字段名
 * @param bottom_row_str  底部自定义行    
 * @return
 */
public static synchronized WritableWorkbook createExcel(String folder,String filename,String[] columnNames,List<Map<String,String>> datas,String[] bottom_row_str){
	try {
		folder=folder+ ((folder.endsWith("/")||folder.endsWith("\\")) ? "" : File.separator);
		File fold=new File("/"+folder);
		//			File fold=new File(serverPath+folder);
		if (!fold.exists()) {
			fold.mkdirs();
		}
		String saveFileName=folder+filename;
		saveFileName=saveFileName.replaceAll("\\\\", "/").replaceAll("//", "/");
		saveFileName=saveFileName.substring(0,saveFileName.lastIndexOf("."))+"_"+new SimpleDateFormat("yyyyMMdd-HHmmss").format(new Date())+saveFileName.substring(saveFileName.lastIndexOf("."),saveFileName.length());

		File file=new File("/"+saveFileName);
		// 创建可写入的excel工作簿
		WritableWorkbook writableWorkbook = Workbook.createWorkbook(file);
		// 创建可写的工作表
		WritableSheet wtSheet = writableWorkbook.createSheet("create", 0);
		for (int i=0;i<columnNames.length;i++) {
			wtSheet.addCell(new Label(i, 0, columnNames[i].trim()));
		}
		for (int j = 1; j <= datas.size(); j++) {
			for (int i = 0; i < columnNames.length; i++) {
				Map<String,String> data = datas.get(j-1);
				wtSheet.addCell(new Label(i, j, String.valueOf(data.get(columnNames[i])==null?"":data.get(columnNames[i]))));

			}

		}
		if (bottom_row_str!=null) {
			for (int i = 0; i < bottom_row_str.length; i++) {
				wtSheet.addCell(new Label(i, datas.size()+1,bottom_row_str[i] ));
			}
		}
		writableWorkbook.write();
		writableWorkbook.close();
		return writableWorkbook;
	} catch (Exception e) {
		e.printStackTrace();
		return null;
	} 
} 

public static synchronized boolean createExcelTemplete(String serverPath,String folder,String filename,String[] columnNames){
	try {
		String savePath = serverPath+((serverPath.endsWith("/")||serverPath.endsWith("\\")) ? "" : File.separator) + folder
				+ ((folder.endsWith("/")||folder.endsWith("\\")) ? "" : File.separator);

		File fold=new File(savePath);
		if (!fold.exists()) {
			fold.mkdirs();
		}
		File file=new File(savePath+filename);
		// 创建可写入的excel工作簿
		WritableWorkbook writableWorkbook = Workbook.createWorkbook(file);
		// 创建可写的工作表
		WritableSheet wtSheet = writableWorkbook.createSheet("create", 0);
		for (int i=0;i<columnNames.length;i++) {
			wtSheet.addCell(new Label(i, 0, columnNames[i].trim()));
		}

		writableWorkbook.write();
		writableWorkbook.close();
		return true;
	} catch (Exception e) {
		e.printStackTrace();
		return false;
	} 
} 
/**
 * 解析Excel 返回List对象
 * @param <T>
 * @param path excel文件物理路径
 * @param clazz 需要返回的对象
 * @param fields 需要赋值的字段，必须和excel列的中顺序相同
 * @return
 */
public static synchronized <T>List<T> parseExcel(String path,Class<T> clazz,String fields) {
	InputStream ios =null;
	Workbook writablebook=null;
	File f=null;
	try {
		List<T> list=new ArrayList<T>();
		T obj=null;

		try {
			ios = new BufferedInputStream(new FileInputStream(path));
			f=new File(path);
		} catch (Exception e) {
			System.out.println(path+"文件不存在");
			return null;
		}
		WorkbookSettings setting=new WorkbookSettings();
		Locale local=new Locale("zh","CN");
		setting.setLocale(local);
		setting.setEncoding("ISO-8859-1");
		writablebook = Workbook.getWorkbook(ios,setting); 
		Sheet sheetArray = writablebook.getSheet(0); 
		Cell[] cells = null;
		Field field=null;
		int rows=	sheetArray.getRows();
		for (int i = 1; i < rows; i++) {
			cells= sheetArray.getRow(i);
			obj=clazz.newInstance();
			String[] fieldstr=fields.split(",");
			for (int j = 0; j < fieldstr.length; j++) {
				if(j == 3 && cells.length <= 3) {
					break;
				}
				if (fieldstr[j]!=null&&!fieldstr[j].trim().equals("")){
					field=clazz.getDeclaredField(fieldstr[j]);
					set(field, obj, cells[j].getContents().toString());
				}
			}
			list.add(obj);
		}
		return list;
	} catch (Exception e) {
		e.printStackTrace();
		return null;
	} finally{
		try {
			if(writablebook!=null)
				writablebook.close();
			if(ios!=null)
				ios.close();
			if(f!=null&&f.exists())
				f.delete();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
}
/**
 * 解析Excel为对象赋值
 * @param field
 * @param obj
 * @param context
 * @throws Exception
 */
private static  void  set(Field field,Object obj,String context) throws Exception{
	field.setAccessible(true);
	field.set(obj, context);
	field.setAccessible(false);
}
}

jxl.jar概述

• 通过Java操作excel表格的工具类库
• 支持excel 95-2000的所有版本
• 生成Excel 2000标准格式
• 支持字体、数字、日期操作
• 能够修饰单元格属性
• 支持图像和图标
  　　应该说以上功能已经能够大致满足我们的需要。最关键的是这套API是纯Java的，并不依赖Windows系统，即使运行在Linux下，它同样能够正确的处理Excel文件。另外需要说明的是，这套API对图形和图表的支持很有限，而且仅仅识别PNG格式。