用java操作excel，jexcelapi

最新推荐文章于 2024-07-25 08:16:40 发布

最新推荐文章于 2024-07-25 08:16:40 发布 · 133 阅读

文章标签：

#Excel #Java #EXT #Web

Java 专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一个使用Java和jxl库创建Excel文件的实用类。该类支持从对象列表生成Excel表格，并能自动处理字符串和数值类型的数据。此外，还提供了求和功能。

这是我自己写的创建Excel的一个类，仅供参考，用的是jxl.jar（见附件）。偷懒总是有方法的，感觉像创建Ext的Grid一样。

接口定义：

public void generateExcel(List<?> entities, Column[] columns, String fileName);

实现：

package com.yingxia.trms.excel;

import java.io.File;
import java.lang.reflect.Method;
import java.util.List;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

public class ExcelGeneratorImpl implements ExcelGenerator {

	@Override
	public void generateExcel(List<?> entities, Column[] columns, String fileName) {

		try {
			
			String path = ExcelGeneratorImpl.class.getResource("/").getPath();
			path = path.replace("WEB-INF/classes/", "") + "excel/" + fileName + ".xls";
			
			File file = new File(path);
			if(file.exists()) {
				file.delete();
			}
			
			WritableWorkbook workbook = Workbook.createWorkbook(new File(path));
			WritableSheet sheet = workbook.createSheet("First sheet", 0);
			
			// header
			for (int col = 0; col < columns.length; col++) {
				Label labelHeader = new Label(col, 0, columns[col].getHeader());
				sheet.addCell(labelHeader);
			}
			
			// body
			for (int row = 1; row <= entities.size(); row++) {
				for (int col = 0; col < columns.length; col++) {
					
					String columnName = columns[col].getName();
					Object columnValue = getFieldValue(entities.get(row - 1), columnName);
					
					WritableCell cell = null;
					if(columns[col].getType().equals("string")) {
						cell = new Label(col, row, columnValue.toString());
					} else {
						cell = new jxl.write.Number(col, row, Double.valueOf(columnValue.toString()));
					}
					
					sheet.addCell(cell);
				}
			}
			
			// sum
			for (int col = 0; col < columns.length; col++) {
				
				Column column =  columns[col];
				if(column.isNeedSum() && column.getType().equals("number")) {
					
					Double columnSum = 0.0;
					for (int row = 1; row <= entities.size(); row++) {
						Object fieldValue = getFieldValue(entities.get(row - 1), column.getName());
						columnSum += Double.valueOf(fieldValue.toString());
					}
					
					WritableFont font = new WritableFont(WritableFont.ARIAL);
					font.setColour(Colour.RED);
					font.setBoldStyle(WritableFont.BOLD);
					
					jxl.write.Number number = new jxl.write.Number(col, entities.size() + 1, columnSum, new WritableCellFormat(font));
					sheet.addCell(number);
				}
			}
			
			workbook.write();
			workbook.close();
			
		} catch(Exception ex) {
			ex.printStackTrace();
		}
	}
	
	private Object getFieldValue(Object obj, String fieldName) throws Exception {
		String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
		Method getMethod = obj.getClass().getMethod(getMethodName);
		return getMethod.invoke(obj);
	}

}

这其中用到了一个自定义的Column类

package com.yingxia.trms.excel;

public class Column {

	private String name;
	private String header;
	private String type; // string, number
	private boolean needSum = false; // must be number

	public Column() {
	}

	public Column(String name, String header, String type) {
		super();
		this.name = name;
		this.header = header;
		this.type = type;
	}
	
	public Column(String name, String header, String type, boolean needSum) {
		super();
		this.name = name;
		this.header = header;
		this.type = type;
		this.needSum = needSum;
	}

	public String getName() {
		return name;
	}

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

	public String getHeader() {
		return header;
	}

	public void setHeader(String header) {
		this.header = header;
	}

	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	public boolean isNeedSum() {
		return needSum;
	}

	public void setNeedSum(boolean needSum) {
		this.needSum = needSum;
	}

}