封装JXL的小工具（二）
编译Schema:xjc jxl.xsd -p jxl.jaxb
生成N多文件：
http://www.agoit.com/upload/attachment/41943/045944ae-4e26-3843-831d-9b5608baf126.jpg
生成的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);}// TABLEexcel.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);}// TABLEexcel.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);BorderLineStyle bs = fetchStaticObject(BorderLineStyle.class, b);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 FORMATreturn 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 FORMATreturn 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;} elsereturn 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文件见附件：
页:
[1]