`
- 浏览:
1485977 次
- 性别:
- 来自:
深圳
-
使用JXL读取Excel表格,拷贝、更新Excel工作薄
-
-
-
-
-
-
-
-
-
- package cn.com.yitong.xls;
-
- import java.io.File;
- import java.io.FileInputStream;
- import java.io.InputStream;
- import java.util.Vector;
-
- import cn.com.yitong.ChartImg;
- import cn.com.yitong.VireObj;
- import cn.com.yitong.platform.log.YTLogger;
-
- import <SPAN class=hilite1>jxl</SPAN>.CellType;
- import <SPAN class=hilite1>jxl</SPAN>.Workbook;
- import <SPAN class=hilite1>jxl</SPAN>.format.CellFormat;
- import <SPAN class=hilite1>jxl</SPAN>.format.Colour;
- import <SPAN class=hilite1>jxl</SPAN>.format.UnderlineStyle;
- import <SPAN class=hilite1>jxl</SPAN>.write.Formula;
- import <SPAN class=hilite1>jxl</SPAN>.write.Label;
- import <SPAN class=hilite1>jxl</SPAN>.write.Number;
- import <SPAN class=hilite1>jxl</SPAN>.write.WritableCell;
- import <SPAN class=hilite1>jxl</SPAN>.write.WritableCellFormat;
- import <SPAN class=hilite1>jxl</SPAN>.write.WritableFont;
- import <SPAN class=hilite1>jxl</SPAN>.write.WritableImage;
- import <SPAN class=hilite1>jxl</SPAN>.write.WritableSheet;
- import <SPAN class=hilite1>jxl</SPAN>.write.WritableWorkbook;
- import <SPAN class=hilite1>jxl</SPAN>.write.WriteException;
- import <SPAN class=hilite1>jxl</SPAN>.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;
-
-
-
-
- private void makeXls()
- {
- Workbook workbook = null;
- try
- {
-
-
- InputStream ins = new FileInputStream("D:/Workspace/testproj/source.xls");
- workbook = Workbook.getWorkbook(ins);
-
-
- 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);
-
- wwb.write();
- wwb.close();
- } catch (Exception e)
- {
- YTLogger.logDebug(e);
- } finally
- {
-
- workbook.close();
- }
- }
-
-
-
-
-
-
-
-
- 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);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- 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);
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- 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));
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
- 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!");
- }
- }
-
-
-
-
-
-
-
-
-
-
-
- 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)
- {
- c1 = (char) (col % 26 + base);
- formula.append(c1).append(startPos).append(":")
- .append(c1).append(endPos).append(")");
- } else 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();
- }
-
-
-
-
-
-
-
-
- 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);
- background.setBackground(Colour.GRAY_25);
- imgSheet.mergeCells(0, 2 + 20 * i, 9, 2 + 20 * i);
- lbl.setCellFormat(background);
- imgSheet.addCell(lbl);
-
- insertImgCell(imgSheet, 2, 4 + 20 * i, 8, 15, chart.getImgName());
- }
- }
-
-
-
-
-
-
-
-
-
-
-
-
- private void insertImgCell(WritableSheet dataSheet, int col, int row, int width,
- int height, String imgName) throws RowsExceededException, WriteException
- {
- File imgFile = new File(imgName);
- WritableImage img = new WritableImage(col, row, width, height, imgFile);
- dataSheet.addImage(img);
- }
-
-
-
-
-
- public static void main(String[] args)
- {
- XLSDemo demo = new XLSDemo();
- demo.makeXls();
- }
- }
分享到:
Global site tag (gtag.js) - Google Analytics
相关推荐
最近刚好用到jxl来读取Excel表格数据,百度上找遍都找不到jxl的jar包,最后通过其他途径找到了,所以分享一下,让接下来的同学不要费劲找jar包了。
java利用jxl.jar包读取excel文件
java中使用jxl打印excel报表,java中使用jxl打印excel报表,java中使用jxl打印excel报表
NULL 博文链接:https://zhouyq.iteye.com/blog/272883
java读取Excel表格,拷贝、更新Excel工作薄、多个sheet合并为一个excel
jxl读取excel数据(带实例) 里面有一个自己写的实例
利用jxl读取Excel代码的完整内容,包括jar包。包括测试用例,下载后放到eclipse中即可运行,运行主类JHUserImportMgr的main函数即可看到效果。
jxl 读取2003 excel demo 使用jxl 操作excel
JAVA利用jxl读取Excel所需jar。 JAVA可以利用jxl简单快速的读取excel文件的内容。由于版本限制,只能读取97-03 xls格式的Excel。
NULL 博文链接:https://jsufly.iteye.com/blog/767602
用jxl读写excel数据,输出图片到excel
用jxl生成EXCEL表格,一个非常简单易懂的写法。
(java读取excel表格的jar包)
jsp+jspsmart上传+poi3.8读取excel2007+jxl读取excel2003
用jxl读取excel内容的java工程源码,放到eclipse中稍微建立包名即可测试
jxl JAVA Excel解析,主要用于修改、上传、下载Excel文件。
使用jxl解析excel固定模版(导入解析),本资源是以智联excel简历为例解析, 1:上传 2:临时存储,3:解析保存,4:删除临时资源关闭流
简单的介绍了JXL读写Excel java excle api
jxl 读取Excel模板并写入数据通用工具类
包含POI和JXL读取EXCEL数据的工具类(可以直接调用)和需要的工具类