封装JXL的小工具（二）

最新推荐文章于 2022-02-25 20:00:37 发布

iteye_17345

最新推荐文章于 2022-02-25 20:00:37 发布

阅读量106

点赞数

文章标签： Excel C C++ C# XSL

  编译Schema:
xjc jxl.xsd -p jxl.jaxb
生成N多文件：
 生成的Java文件全部放在jxl.jaxb包下。
  
  
 然后对Excel类编辑：

@XmlRootElement(name="excel")//添加
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "excel", propOrder = {
    "format",
    "sheet"
})
public class Excel {

    ...

}

JAXBExcel类：

构造函数：

public JAXBExcel(Source xml, Source xsl) {
	try {
		Transformer trans = TransformerFactory.newInstance()
				.newTransformer(xsl);
		JAXBContext context = JAXBContext.newInstance("jxl.jaxb");
		JAXBResult result = new JAXBResult(context);
		trans.transform(xml, result);
		table = (Excel) result.getResult();
		Marshaller marshaller = context.createMarshaller();
		marshaller.setProperty("jaxb.formatted.output", true);
		marshaller.setProperty("jaxb.encoding", "UTF-8");
		marshaller.marshal(table, System.out);
	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException(e.getMessage());
	}
}

处理XML文件：

public void process(OutputStream os) throws IOException, WriteException,
		ParseException {
	if (table == null) {
		throw new RuntimeException("The table is not init yet");
	}
	processFormat(table.getFormat());
	WritableWorkbook excel = Workbook.createWorkbook(os);
	Iterator<ExcelSheet> excelSheets = table.getSheet().iterator();
	for (int i = 0; excelSheets.hasNext(); i++) {
		ExcelSheet sheetData = excelSheets.next();
		WritableSheet sheet = excel.createSheet(sheetData.getName(), i);
		// sheet.getSettings().setProtected(true);
		// sheet.getSettings().setPassword("zhaobq");
		// 锁定单元格，设置保护密码
		int row = 0;
		if (sheetData.getHead().getRow().size() > 0) {
			this.setView(sheet, sheetData.getHead().getRow().get(
					sheetData.getHead().getRow().size() - 1));
		}

		row = processTitle(sheetData.getTitle(), sheet, row);
		row = processHead(sheetData.getHead(), sheet, row);
		row = processBody(sheetData.getBody(), sheet, row);
		row = processFoot(sheetData.getFoot(), sheet, row);
	}// TABLE
	excel.write();
	excel.close();
}

JAXBExcel类中公共的方法就这两个：

首先用XML数据文件和XSL模板文件构造JAXBExcel对象，

然后用JAXBExcel.process()得到文件的输出流

全部代码：

package jxl;

import static jxl.write.WritableFont.ARIAL;
import static jxl.write.WritableFont.BOLD;
import static jxl.write.WritableFont.DEFAULT_POINT_SIZE;
import static jxl.write.WritableFont.NO_BOLD;

import java.io.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*；
import javax.xml.bind.*;
import javax.xml.bind.util.JAXBResult;
import javax.xml.transform.*;

import jxl.format.*;
import jxl.jaxb.*;
import jxl.write.*;

/**
 * 
 * @author Allen 赵本钱
 *
 */
public class JAXBExcel {

	private Excel table = null;

	/**
	 * 默认CELLFORMAT
	 */
	public static Map<String, WritableCellFormat> CELL_FORMAT = new HashMap<String, WritableCellFormat>();

	private Map<String, WritableCellFormat> cellFormat = null;

	private String numberFormat = "####.##";

	private String dateFormat = "yyyy-MM-dd";

	private static List<String> COLUMN_TYPE = new ArrayList<String>();

	static {
		WritableFont foot = new WritableFont(ARIAL, 10);
		WritableFont body = foot;
		WritableFont head = new WritableFont(ARIAL, 10);
		WritableFont title = new WritableFont(ARIAL, 15);
		try {
			head.setBoldStyle(BOLD);

			title.setBoldStyle(BOLD);
		} catch (WriteException e) {
			e.printStackTrace();
		}
		WritableCellFormat cfoot = new WritableCellFormat();
		WritableCellFormat cbody = cfoot;
		WritableCellFormat chead = new WritableCellFormat();
		WritableCellFormat ctitle = new WritableCellFormat();
		try {
			cfoot.setAlignment(Alignment.GENERAL);
			cfoot.setWrap(false);

			chead.setAlignment(Alignment.CENTRE);
			chead.setWrap(false);
			chead.setBackground(Colour.GRAY_25);

			ctitle.setAlignment(Alignment.CENTRE);
			ctitle.setWrap(false);
		} catch (WriteException e) {
			e.printStackTrace();
		}
		cfoot.setFont(foot);
		cbody.setFont(body);
		chead.setFont(head);
		ctitle.setFont(title);
		CELL_FORMAT.put("foot", cfoot);
		CELL_FORMAT.put("body", cbody);
		CELL_FORMAT.put("head", chead);
		CELL_FORMAT.put("title", ctitle);
	}

	public JAXBExcel(Source xml, Source xsl) {
		try {
			Transformer trans = TransformerFactory.newInstance().newTransformer(xsl);
			JAXBContext context = JAXBContext.newInstance("jxl.jaxb");
			JAXBResult result = new JAXBResult(context);
			trans.transform(xml, result);
			table = (Excel) result.getResult();
			Marshaller marshaller = context.createMarshaller();
			marshaller.setProperty("jaxb.formatted.output", true);
			marshaller.setProperty("jaxb.encoding", "UTF-8");
			marshaller.marshal(table, System.out);
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage());
		}
	}

	/*
	 * (non-Javadoc)
	 * 
	 * @see jxl.Excel#process(java.io.OutputStream)
	 */
	public void process(OutputStream os) throws IOException, WriteException, ParseException {
		if (table == null) {
			throw new RuntimeException("The table is not init yet");
		}
		processFormat(table.getFormat());
		WritableWorkbook excel = Workbook.createWorkbook(os);
		Iterator<ExcelSheet> excelSheets = table.getSheet().iterator();
		for (int i = 0; excelSheets.hasNext(); i++) {
			ExcelSheet sheetData = excelSheets.next();
			WritableSheet sheet = excel.createSheet(sheetData.getName(), i);
			// sheet.getSettings().setProtected(true);
			// sheet.getSettings().setPassword("zhaobq");
			// 锁定单元格，设置保护密码
			int row = 0;
			if (sheetData.getHead().getRow().size() > 0) {
				this.setView(sheet, sheetData.getHead().getRow().get(sheetData.getHead().getRow().size() - 1));
			}

			row = processTitle(sheetData.getTitle(), sheet, row);
			row = processHead(sheetData.getHead(), sheet, row);
			row = processBody(sheetData.getBody(), sheet, row);
			row = processFoot(sheetData.getFoot(), sheet, row);
		}// TABLE
		excel.write();
		excel.close();
	}

	private void setView(WritableSheet sheet, ExcelRow view) {
		Iterator<ExcelHCell> hcell = view.getHcell().iterator();
		for (int i = 0; hcell.hasNext(); i++) {
			ExcelHCell c = hcell.next();
			if (null != c.getWidth()) {
				sheet.setColumnView(i, c.getWidth());
			}
			if (null != c.getType()) {
				COLUMN_TYPE.add(c.getType());
			} else {
				COLUMN_TYPE.add("string");
			}
		}
	}

	/**
	 * 处理Format节点
	 * 
	 * @param format
	 * @throws WriteException
	 */
	private void processFormat(Format format) throws WriteException {
		if (format == null) {
			return;
		}
		Iterator<CellFormat> cf = format.getCellFormat().iterator();

		cellFormat = new HashMap<String, WritableCellFormat>();

		while (cf.hasNext()) {
			CellFormat f = cf.next();

			String id = f.getId();
			String backColour = f.getBackColour();
			String foreColour = f.getForeColour();
			String borderColour = f.getBorderColour();
			String border = f.getBorder();
			String align = f.getAlign();
			String valign = f.getVerticalAlign();

			String fontName = f.getFontName();
			int fontSize = (null == f.getFontSize()) ? DEFAULT_POINT_SIZE : f.getFontSize();
			boolean bold = nullToFalse(f.isBold());
			boolean italic = nullToFalse(f.isItalic());
			String underLine = f.getUnderLine();

			WritableCellFormat cell = new WritableCellFormat();

			if (isNotEmptyorNull(backColour)) {
				Colour c = null;
				try {
					c = fetchStaticObject(Colour.class, backColour);
					cell.setBackground(c);
				} catch (RuntimeException e) {
					e.printStackTrace();
				}
			}// 背景
			if (isNotEmptyorNull(align)) {
				Alignment a = null;
				try {
					a = fetchStaticObject(Alignment.class, align);
					cell.setAlignment(a);
				} catch (RuntimeException e) {
					e.printStackTrace();
				}
			}// 水平对齐方式
			if (isNotEmptyorNull(valign)) {
				VerticalAlignment a = null;
				try {
					a = fetchStaticObject(VerticalAlignment.class, align);
					cell.setVerticalAlignment(a);
				} catch (RuntimeException e) {
					e.printStackTrace();
				}
			}// 垂直对齐方式

			if (isNotEmptyorNull(border)) {
				String[] b = border.split(" ");
				if (b.length == 2) {
					Border bd = fetchStaticObject(Border.class, b[0]);
					BorderLineStyle bs = fetchStaticObject(BorderLineStyle.class, b[1]);

					if (isNotEmptyorNull(borderColour)) {
						Colour c = null;
						try {
							c = fetchStaticObject(Colour.class, borderColour);
							cell.setBorder(bd, bs, c);
						} catch (RuntimeException e) {
							e.printStackTrace();
						}
					} else {
						cell.setBorder(bd, bs);
					}
				}
			}

			WritableFont fontFormat;
			if (isNotEmptyorNull(fontName)) {
				WritableFont.FontName font = null;
				try {
					font = fetchStaticObject(WritableFont.FontName.class, fontName);
				} catch (RuntimeException e) {
					e.printStackTrace();
					font = WritableFont.createFont(fontName);
				}
				fontFormat = new WritableFont(font);
			} else {
				fontFormat = new WritableFont(ARIAL);
			}// 名字
			fontFormat.setPointSize(fontSize);// 大小
			if (bold) {
				fontFormat.setBoldStyle(BOLD);
			} else {
				fontFormat.setBoldStyle(NO_BOLD);
			}// 粗体
			fontFormat.setItalic(italic);// 斜体
			if (isNotEmptyorNull(underLine)) {
				UnderlineStyle us = null;
				try {
					us = fetchStaticObject(UnderlineStyle.class, underLine);
				} catch (RuntimeException e) {
					e.printStackTrace();
					us = UnderlineStyle.NO_UNDERLINE;
				}
				fontFormat.setUnderlineStyle(us);
			}// 下划线

			if (isNotEmptyorNull(foreColour)) {
				Colour c = null;
				try {
					c = fetchStaticObject(Colour.class, foreColour);
				} catch (RuntimeException e) {
					e.printStackTrace();
					c = Colour.BLACK;
				}
				fontFormat.setColour(c);
			}// 颜色

			cell.setFont(fontFormat);
			cell.setWrap(false);
			cellFormat.put(id, cell);
		}
	}

	/**
	 * 处理Title节点
	 * 
	 * @param title
	 * @param sheet
	 * @param row
	 * @return
	 * @throws WriteException
	 */
	private int processTitle(ExcelTitle title, WritableSheet sheet, int row) throws WriteException {
		if (title == null) {
			return row;
		}
		WritableCellFormat cf = exactFormat(title.getCellFormat(), "title");
		WritableCellFeatures ft = new WritableCellFeatures();
		ft.setComment(title.getComment());
		if (null != title.getHeight()) {
			sheet.setRowView(0, title.getHeight());
		}
		int colspan = (null == title.getColspan()) ? 1 : title.getColspan();
		this.writeLabel(sheet, cf, title.getValue(), 0, row, colspan, 1, ft);
		row++;
		return row;
	}

	/**
	 * 处理Head节点
	 * 
	 * @param head
	 * @param sheet
	 * @param row
	 * @return
	 * @throws WriteException
	 * @throws ParseException
	 */
	private int processHead(ExcelHead head, WritableSheet sheet, int row) throws WriteException, ParseException {
		if (head == null) {
			return row;
		}
		List<ExcelRow> rowDate = head.getRow();
		if (null == rowDate || rowDate.isEmpty()) {
			return row;
		}
		WritableCellFormat cf = exactFormat(head.getCellFormat(), "head");
		for (int i = 0; i < rowDate.size(); i++) {
			ExcelRow r = rowDate.get(i);
			if (null != r.getHeight()) {
				sheet.setRowView(row, r.getHeight());
			}
			Iterator<ExcelHCell> c = r.getHcell().iterator();
			for (int j = 0; c.hasNext(); j++) {
				ExcelHCell hcell = c.next();
				processHCell(sheet, hcell, cf, row, j);
			}
			row++;
		}
		return row;
	}

	/**
	 * 处理Body节点
	 * 
	 * @param body
	 * @param sheet
	 * @param row
	 * @return
	 * @throws WriteException
	 * @throws ParseException
	 */
	private int processBody(ExcelBody body, WritableSheet sheet, int row) throws WriteException, ParseException {
		if (body == null) {
			return row;
		}
		List<ExcelRow> rowDate = body.getRow();
		if (rowDate == null || rowDate.isEmpty()) {
			return row;
		}
		WritableCellFormat cf = exactFormat(body.getCellFormat(), "body");
		for (int i = 0; i < rowDate.size(); i++) {
			ExcelRow r = rowDate.get(i);
			if (null != r.getHeight()) {
				sheet.setRowView(row, r.getHeight());
			}
			Iterator<ExcelCell> c = r.getCell().iterator();
			for (int j = 0; c.hasNext(); j++) {
				ExcelCell cell = c.next();
				String type = COLUMN_TYPE.get(j);
				String cfname = cell.getCellFormat();
				if (isNotEmptyorNull(cfname)) {
					processCell(sheet, cell, exactCellFormat(cell.getCellFormat()), type, row, j);
				} else {
					processCell(sheet, cell, cf, type, row, j);
				}
			}
			row++;
		}
		return row;
	}

	/**
	 * 处理Foot节点
	 * 
	 * @param foot
	 * @param sheet
	 * @param row
	 * @return
	 * @throws WriteException
	 * @throws ParseException
	 */
	private int processFoot(ExcelFoot foot, WritableSheet sheet, int row) throws WriteException, ParseException {
		if (foot == null) {
			return row;
		}
		List<ExcelRow> rowDate = foot.getRow();
		if (rowDate == null || rowDate.isEmpty()) {
			return row;
		}
		WritableCellFormat cf = exactFormat(foot.getCellFormat(), "foot");
		for (int i = 0; i < rowDate.size(); i++) {
			ExcelRow r = rowDate.get(i);
			if (null != r.getHeight()) {
				sheet.setRowView(row, r.getHeight());
			}
			Iterator<ExcelCell> c = r.getCell().iterator();
			for (int j = 0; c.hasNext(); j++) {
				ExcelCell cell = c.next();
				String type = COLUMN_TYPE.get(j);
				String cfname = cell.getCellFormat();
				if (isNotEmptyorNull(cfname)) {
					processCell(sheet, cell, exactCellFormat(cell.getCellFormat()), type, row, j);
				} else {
					processCell(sheet, cell, cf, type, row, j);
				}
			}
			row++;
		}
		return row;
	}

	/**
	 * 获取Format信息
	 * 
	 * @param cellFormatName
	 * @param font
	 * @param position
	 * @return
	 */
	private WritableCellFormat exactFormat(String cellFormatName, String position) {
		WritableCellFormat cf;
		if (isNotEmptyorNull(cellFormatName)) {
			cf = this.cellFormat.get(cellFormatName);
		} else {
			cf = CELL_FORMAT.get(position);
		}// SETTING THE CELL FORMAT
		return cf;
	}

	/**
	 * 获取Cell Format信息
	 * 
	 * @param cellFormatName
	 * @param font
	 * @param position
	 * @return
	 */
	private WritableCellFormat exactCellFormat(String cellFormatName) {
		WritableCellFormat cf = null;
		if (isNotEmptyorNull(cellFormatName)) {
			cf = this.cellFormat.get(cellFormatName);
		} // SETTING THE CELL FORMAT
		return cf;
	}

	/**
	 * 处理单元格
	 * 
	 * @param sheet
	 * @param cell
	 * @param cellFormat
	 * @param row
	 * @param j
	 * @throws WriteException
	 * @throws ParseException
	 */
	private void processCell(WritableSheet sheet, ExcelCell cell, WritableCellFormat cellFormat, String type, int row, int j) throws WriteException, ParseException {
		Integer c = (null == cell.getColspan()) ? 1 : cell.getColspan();
		Integer r = (null == cell.getRowspan()) ? 1 : cell.getRowspan();
		WritableCellFeatures features = new WritableCellFeatures();
		features.setComment(cell.getComment());
		if (!isNotEmptyorNull(type)) {
			type = "string";
		}
		if (type.equalsIgnoreCase("date")) {
			writeDate(sheet, cellFormat, cell.getValue(), j, row, c, r, features);
		} else if (type.equalsIgnoreCase("number")) {
			writeNumber(sheet, cellFormat, Double.parseDouble(cell.getValue()), j, row, c, r, features);
		} else {
			writeLabel(sheet, cellFormat, cell.getValue(), j, row, c, r, features);
		}
	}

	/**
	 * 处理单元格
	 * 
	 * @param sheet
	 * @param cell
	 * @param cellFormat
	 * @param row
	 * @param j
	 * @throws WriteException
	 * @throws ParseException
	 */
	private void processHCell(WritableSheet sheet, ExcelHCell cell, WritableCellFormat cellFormat, int row, int j) throws WriteException, ParseException {
		Integer c = (null == cell.getColspan()) ? 1 : cell.getColspan();
		Integer r = (null == cell.getRowspan()) ? 1 : cell.getRowspan();
		WritableCellFeatures features = new WritableCellFeatures();
		features.setComment(cell.getComment());
		writeLabel(sheet, cellFormat, cell.getValue(), j, row, c, r, features);
	}

	/**
	 * 写普通格式数据
	 * 
	 * @param sheet
	 * @param cellFormat
	 * @param labelName
	 * @param x
	 * @param y
	 * @param width
	 * @param height
	 * @throws WriteException
	 */
	private void writeLabel(WritableSheet sheet, WritableCellFormat cellFormat, String labelName, int x, int y, int width, int height, WritableCellFeatures ft)
			throws WriteException {
		if ((x >= 0) && (y >= 0) && (width > 0) && (height > 0)) {
			Label c = null;

			if ((width != 1) || (height != 1)) {
				sheet.mergeCells(x, y, (x + width) - 1, (y + height) - 1);
			}

			c = new Label(x, y, labelName, cellFormat);
			c.setCellFeatures(ft);
			sheet.addCell(c);
		}
	}

	/**
	 * 写数字类型数据
	 * 
	 * @param sheet
	 * @param cellFormat
	 * @param numberData
	 * @param x
	 * @param y
	 * @param width
	 * @param height
	 * @throws WriteException
	 */
	private void writeNumber(WritableSheet sheet, WritableCellFormat cellFormat, double numberData, int x, int y, int width, int height, WritableCellFeatures ft)
			throws WriteException {
		if ((x >= 0) && (y >= 0) && (width > 0) && (height > 0)) {
			jxl.write.Number c = null;
			if ((width != 1) || (height != 1)) {
				sheet.mergeCells(x, y, (x + width) - 1, (y + height) - 1);
			}
			c = new jxl.write.Number(x, y, numberData, cellFormat);
			c.setCellFeatures(ft);
			sheet.addCell(c);
		}
	}

	/**
	 * 写日期类型的数据
	 * 
	 * @param sheet
	 * @param cellFormat
	 * @param date
	 * @param x
	 * @param y
	 * @param width
	 * @param height
	 * @throws WriteException
	 * @throws ParseException
	 */
	private void writeDate(WritableSheet sheet, WritableCellFormat cellFormat, String date, int x, int y, int width, int height, WritableCellFeatures ft) throws WriteException,
			ParseException {
		if ((x >= 0) && (y >= 0) && (width > 0) && (height > 0)) {
			SimpleDateFormat simpleDateFormat = new SimpleDateFormat(dateFormat);
			DateTime c = new DateTime(x, y, simpleDateFormat.parse(date), cellFormat);
			if ((width != 1) || (height != 1)) {
				sheet.mergeCells(x, y, (x + width) - 1, (y + height) - 1);
			}
			c.setCellFeatures(ft);
			sheet.addCell(c);
		}
	}

	@SuppressWarnings("unchecked")
	private <T> T fetchStaticObject(Class<T> clazz, String name) {
		T t = null;
		try {
			t = (T) clazz.getDeclaredField(name).get(null);
		} catch (Exception e) {
			throw new RuntimeException(e.getMessage() + "不能找类：" + clazz.getName() + "的静态字段：" + name);
		}
		return t;
	}

	/**
	 * 判断字符串是否为null或者空
	 * 
	 * @param str
	 * @return
	 */
	public static boolean isNotEmptyorNull(String str) {
		if (null != str && !"".equals(str)) {
			return true;
		}
		return false;
	}

	public static boolean nullToFalse(Boolean b) {
		if (null == b) {
			return false;
		} else
			return b;
	}

	public void setNumberFormat(String nf) {
		this.numberFormat = nf;
	}

	public void setDateFormat(String df) {
		this.dateFormat = df;
	}

	public static String say() {
		return "I Said ,I'M Allen";
	}

}

使用：

String xml = "FOP.XML";
String xsl = "FOP.XSL";
JAXBExcel helper = new JAXBExcel(new StreamSource(new File(xml)), new StreamSource(new File(xsl)));
File f = new File("C:/a.xls");
OutputStream os = new FileOutputStream(f);
helper.process(os);
os.close();

其中FOP.xml FOP.xsl文件见附件：