使用JXL读取Excel表格.doc

使用JXL读取Excel表格,拷贝、更新Excel工作薄作者： 文章来源：发布日期：2005年01月01日 浏览次数：1次/* 读取Excel表格,拷贝、更新Excel工作薄 * Description: 可以读取Excel文件的内容,更新Excel工作薄* * Copyright: Copyright (c) Corparation 2005* 程序开发环境为eclipse* author Walker* version 1.0*/package .yitong.xls;import java.io.File;import java.io.FileInputStream;import java.io.InputStream;import java.util.Vector;import .yitong.ChartImg;import .yitong.VireObj;import .yitong.platform.log.YTLogger;import jxl.CellType;import jxl.Workbook;import jxl.format.CellFormat;import jxl.format.Colour;import jxl.format.UnderlineStyle;import jxl.write.Formula;import jxl.write.Label;import jxl.write.Number;import jxl.write.WritableCell;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableImage;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.biff.RowsExceededException;public class XLSDemo private static final int TITLE_LENGTH = 7; private static final int SHEET_WIDTH = 32; private static final int SHEET_HEIGHT = 116; /* * 创建Excel */ private void makeXls() Workbook workbook = null; try / 构建Workbook对象, 只读Workbook对象 / 直接从本地文件创建Workbook, 从输入流创建Workbook InputStream ins = new FileInputStream(D:/Workspace/testproj/source.xls); workbook = Workbook.getWorkbook(ins); / 利用已经创建的Excel工作薄创建新的可写入的Excel工作薄 File outFile = new File(D:/Workspace/testproj/test.xls); WritableWorkbook wwb = Workbook.createWorkbook(outFile, workbook); / 读取第一张工作表 WritableSheet dataSheet = wwb.getSheet(0); / 设置冻结单元格 dataSheet.getSettings().setVerticalFreeze(7); dataSheet.getSettings().setHorizontalFreeze(2); / 测试模拟数据 Vector vecData = new Vector(); for(int i = 0; i 50; i +) VireObj obj = new VireObj(); obj.setOrgNo(00 + i + 0); obj.setOrgName(机构 + (i + 1); obj.setOpenAcc(int)(100 * Math.random(); obj.setDestoryAcc(int)(10 * Math.random(); obj.setTotalAcc(int)(500 * Math.random(); obj.setMonthInCount(int)(500 * Math.random(); obj.setMonthInMoney(500 * Math.random(); obj.setMonthOutCount(int)(500 * Math.random(); obj.setMonthOutMoney(500 * Math.random(); vecData.add(obj); / 插入数据 insertData(wwb, dataSheet, vecData); / 插入模拟图像数据 Vector vecImg = new Vector(); for(int i = 0; i 3; i +) ChartImg img = new ChartImg(); img.setImgTitle(图像 + (i + 1); img.setImgName(D:/Workspace/testproj/images/barchart.png); vecImg.add(img); / 插入图表 insertImgsheet(wwb, vecImg); /写入Excel对象 wwb.write(); wwb.close(); catch (Exception e) YTLogger.logDebug(e); finally / 操作完成时，关闭对象，释放占用的内存空间 workbook.close(); /* * 插入数据 * param wwb WritableWorkbook : 工作簿 * param dataSheet WritableSheet : 工作表 * throws RowsExceededException * throws WriteException */ private void insertData(WritableWorkbook wwb, WritableSheet dataSheet, Vector vecData) throws RowsExceededException, WriteException / 获得标题单元格对象 modiStrCell(dataSheet, 2, 0, 工商银行江苏省分行 个人网上银行业务种类/开销户明细报表（2005-12）, null); / 修改数据单元格数据 for(int i = 0; i vecData.size(); i +) VireObj obj = (VireObj)vecData.get(i); modiStrCell(dataSheet, 0, TITLE_LENGTH + i, obj.getOrgNo(), null); modiStrCell(dataSheet, 1, TITLE_LENGTH + i, obj.getOrgName(), null); modiNumCell(dataSheet, 2, TITLE_LENGTH + i, obj.getOpenAcc(), null); modiNumCell(dataSheet, 3, TITLE_LENGTH + i, obj.getDestoryAcc(), null); modiNumCell(dataSheet, 4, TITLE_LENGTH + i, obj.getTotalAcc(), null); modiNumCell(dataSheet, 5, TITLE_LENGTH + i, obj.getMonthInCount(), null); modiNumCell(dataSheet, 6, TITLE_LENGTH + i, obj.getTotalInMoney(), null); modiNumCell(dataSheet, 7, TITLE_LENGTH + i, obj.getMonthOutCount(), null); modiNumCell(dataSheet, 8, TITLE_LENGTH + i, obj.getMonthOutMoney(), null); / 删除空行 for (int j = vecData.size() + TITLE_LENGTH; j SHEET_HEIGHT; j+) dataSheet.removeRow(vecData.size() + TITLE_LENGTH); / 插入公式 for(int i = 2; i SHEET_WIDTH; i +) modiFormulaCell(dataSheet, i, vecData.size() + TITLE_LENGTH, 8, vecData.size() + TITLE_LENGTH, null); /* * 修改字符单元格的值 * param dataSheet WritableSheet : 工作表 * param col int : 列 * param row int : 行 * param str String : 字符 * param format CellFormat : 单元格的样式 * throws RowsExceededException * throws WriteException */ private void modiStrCell(WritableSheet dataSheet, int col, int row, String str, CellFormat format) throws RowsExceededException, WriteException / 获得单元格对象 WritableCell cell = dataSheet.getWritableCell(col, row); / 判断单元格的类型, 做出相应的转化 if (cell.getType() = CellType.EMPTY) Label lbl = new Label(col, row, str); if(null != format) lbl.setCellFormat(format); else lbl.setCellFormat(cell.getCellFormat(); dataSheet.addCell(lbl); else if (cell.getType() = CellType.LABEL) Label lbl = (Label)cell; lbl.setString(str); else if (cell.getType() = CellType.NUMBER) / 数字单元格修改 Number n1 = (Number)cell; n1.setValue(42.05); /* * 修改数字单元格的值 * param dataSheet WritableSheet : 工作表 * param col int : 列 * param row int : 行 * param num double : 数值 * param format CellFormat : 单元格的样式 * throws RowsExceededException * throws WriteException */ private void modiNumCell(WritableSheet dataSheet, int col, int row, double num, CellFormat format) throws RowsExceededException, WriteException / 获得单元格对象 WritableCell cell = dataSheet.getWritableCell(col, row); / 判断单元格的类型, 做出相应的转化 if (cell.getType() = CellType.EMPTY) Number lbl = new Number(col, row, num); if(null != format) lbl.setCellFormat(format); else lbl.setCellFormat(cell.getCellFormat(); dataSheet.addCell(lbl); else if (cell.getType() = CellType.NUMBER) / 数字单元格修改 Number lbl = (Number)cell; lbl.setValue(num); else if (cell.getType() = CellType.LABEL) Label lbl = (Label)cell; lbl.setString(String.valueOf(num); /* * 修改公式单元格的值 * param dataSheet WritableSheet : 工作表 * param col int : 列 * param row int : 行 * param startPos int : 开始位置 * param endPos int : 结束位置 * param format * throws RowsExceededException * throws WriteException */ private void modiFormulaCell(WritableSheet dataSheet, int col, int row, int startPos, int endPos, CellFormat format) throws RowsExceededException, WriteException String f = getFormula(col, row, startPos, endPos); / 插入公式（只支持插入，不支持修改） WritableCell cell = dataSheet.getWritableCell(col, row); if (cell.getType() = CellType.EMPTY) / 公式单元格 Formula lbl = new Formula(col, row, f); if(null != format) lbl.setCellFormat(format); else lbl.setCellFormat(cell.getCellFormat(); dataSheet.addCell(lbl); else if (cell.getType() = CellType.STRING_FORMULA) YTLogger.logWarn(Formula modify not supported!); /* * 得到公式 * param col int : 列 * param row int : 行 * param startPos int : 开始位置 * param endPos int : 结束位置 * return String * throws RowsExceededException * throws WriteException */ private String getFormula(int col, int row, int startPos, int endPos) throws RowsExceededException, WriteException char base = A; char c1 = base; StringBuffer formula = new StringBuffer(128); / 组装公式 formula.append(SUM(); if (col 25) char c2 = (char) (col - 26) / 26 + base); c1 = (char) (col - 26) % 26 + base); formula.append(c2).append(c1).append(startPos).append(:) .append(c2).append(c1).append(endPos).append(); return formula.toString(); /* * 插入图表工作表 * param wwb WritableWorkbook : 工作簿 * param vecImg Vector : 图像链表 * throws RowsExceededException * throws WriteException */ private void insertImgsheet(WritableWorkbook wwb, Vector vecImg) throws RowsExceededException, WriteException / 插入图像 WritableSheet imgSheet; if(wwb.getSheets().length 2) imgSheet = wwb.createSheet(图表, 1); else imgSheet = wwb.getSheet(1); for (int i = 0; i vecImg.size(); i+) ChartImg chart = (ChartImg) vecImg.get(i); / 插入图像标题 Label lbl = new Label(0, 2 + 20 * i, chart.getImgTitle(); WritableFont font = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.DARK_BLUE2); WritableCellFormat background = new WritableCellFormat(font); background.setWrap(true