《jxl读写excel常见操作搜集整理.docx》由会员分享，可在线阅读，更多相关《jxl读写excel常见操作搜集整理.docx（10页珍藏版）》请在三一文库上搜索。
1、jxl 读写 excel 常见操作搜集整理package common.excelTool;import java.io.File;import java.io.FileOutputStream;import java.io.OutputStream;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import jxl.Cell;import jxl.CellType;import jxl.DateCell;import jxl.Range;import jxl.Sheet;import jxl
2、.Workbook;import jxl.WorkbookSettings;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.CellFormat;import jxl.format.Colour;import jxl.format.UnderlineStyle;import jxl.format.VerticalAlignment;import jxl.write.Formula;import jxl.write.Label;impo
3、rt jxl.write.Number;import jxl.write.NumberFormat;import jxl.write.WritableCell;import jxl.write.WritableCellFeatures;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;import jxl.write.WriteException;import jxl.write.bi
4、ff.RowsExceededException;public class WriteExcelTest static HashMap map = new HashMap();public static void main(String args) try /copyDateFormat(new File(c:a.xls), 0, c:copy of a.xls);/writeExcelUseFormat(c:format.xls,test);/buildNewFormTemplete(new File(c:/templete.xls),newFile( c:/buildNewFormTemp
5、lete.xls);/modifyDirectly1(new File(c:/templete.xls);/modifyDirectly2(new File(c:/templete.xls);copyDateAndFormat(new File(c:/a.xls), 0, c:/a2.xls); catch (Exception e) / TODO 自动生成 catch 块 e.printStackTrace();public static void modifyDirectly2(File inputFile) throws ExceptionWorkbook w1 = Workbook.g
6、etWorkbook(inputFile);WritableWorkbook w2 = Workbook.createWorkbook(inputFile, w1);WritableSheet sheet = w2.getSheet(0);WritableCell cell = null;CellFormat cf = null;/加粗cell = sheet.getWritableCell(0,0);WritableFont bold = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, Writabl
7、eFont.BOLD);cf = new WritableCellFormat(bold);cell.setCellFormat(cf);/设置下划线cell = sheet.getWritableCell(0,1);WritableFont underline = new WritableFont(WritableFont.ARIAL, WritableFont.DEFAULT_POINT_SIZE, WritableFont.NO_BOLD, false, UnderlineStyle.SINGLE);cf = new WritableCellFormat(underline);cell.
8、setCellFormat(cf);/直截添加可以覆盖掉setCellValueDirectly(sheet,sheet.getCell(0, 2),new Double(4),CellType.NUMBER);w2.write();w2.close();public static void modifyDirectly1(File file) try/ Excel 获得文件Workbook wb = Workbook.getWorkbook(file);/ 打开一个文件的副本，并且指定数据写回到原文件WritableWorkbook book = Workbook.createWorkboo
9、k(file, wb);WritableSheet sheet0 = book.getSheet(0);sheet0.addCell(new Label(0, 1, 陈小稳 );/ 添加一个工作表WritableSheet sheet = book.createSheet( 第二页 , 1 );sheet.addCell( new Label( 0 , 0 , 第二页的测试数据 );book.write();book.close(); catch (Exception e) System.out.println(e);public static void buildNewFormTemplet
10、e(File inputFile, File outputFile) try/ Excel 获得文件Workbook wb = Workbook.getWorkbook(inputFile);/ 打开一个文件的副本，并且指定数据写回到原文件WritableWorkbook book = Workbook.createWorkbook(outputFile, wb);WritableSheet sheet0 = book.getSheet(0);sheet0.addCell(new Label(0, 1, 陈小稳 );/ 添加一个工作表WritableSheet sheet = book.cre
11、ateSheet( 第二页 , 1 ); sheet.addCell( new Label( 0 , 0 , 第二页的测试数据 );book.write();book.close(); catch (Exception e) System.out.println(e); public static void copyDateAndFormat(File inputFile, int inputFileSheetIndex, String outputFilePath) throws Exception Workbook book = null;Cell cell = null;/1. 避免乱码
12、的设置WorkbookSettings setting = new WorkbookSettings();java.util.Locale locale = new java.util.Locale(zh,CN);setting.setLocale(locale);setting.setEncoding(ISO-8859-1);book = Workbook.getWorkbook(inputFile, setting);Sheet readonlySheet = book.getSheet(inputFileSheetIndex);OutputStream os=new FileOutput
13、Stream(outputFilePath);/ 输出的 Excel 文件 URLWritableWorkbook wwb = Workbook.createWorkbook(os);/ 创建可写工作薄WritableSheet writableSheet = wwb.createSheet(readonlySheet.getName(), 0);/ 创建可写工作 表/2. 誊写不同数据格式的数据for (int rowIndex = 0; rowIndex readonlySheet.getRows(); rowIndex+) for (int colIndex = 0; colIndex
14、readonlySheet.getColumns(); colIndex+) cell = readonlySheet.getCell(colIndex, rowIndex);/A2B2 为合并的单元格， A2 有内容， B2 为空/ if(colIndex = 0 & rowIndex = 1)/ System.out.println(colIndex + , + rowIndex + type: + cell.getType() + : + cell.getContents();/【有各种设置格式】if(cell.getType() = CellType.DA TE | cell.getT
15、ype() = CellType.DATE_FORMULA) writableSheet.addCell(new jxl.write.DateTime(colIndex, rowIndex, (DateCell)cell).getDate(),new jxl.write.WritableCellFormat(cell.getCellFormat();else if(cell.getType() = CellType.NUMBER | cell.getType() =CellType.NUMBER_FORMULA)writableSheet.addCell(new jxl.write.Numbe
16、r(colIndex, rowIndex, (jxl.NumberCell)cell).getValue(),new jxl.write.WritableCellFormat( cell.getCellFormat();else if(cell.getType() = CellType.EMPTY)/空的以及合并单元格中第一列外的/ System.out.println(EMPTY:+cell.getContents();/ System.err.println( 空单元格 at + colIndex + , + rowIndex + content: + cell.getContents()
17、;else if(cell.getType() = CellType.LABEL | cell.getType() =CellType.STRING_FORMULA)writableSheet.addCell(new Label( colIndex , rowIndex , cell.getContents() , new jxl.write.WritableCellFormat( cell.getCellFormat();elseSystem.err.println( 其它单元格类型： + cell.getType() + at + colIndex + , + rowIndex + con
18、tent: + cell.getContents();/ if(cell.getType() = CellType.STRING_FORMULA)/ System.err.println(colIndex + , + rowIndex +: + cell.getContents() + type: + cell.getType();/3. 处理合并单元格的事情(复制合并单元格格式)Range range = readonlySheet.getMergedCells();for (int i = 0; i range.length; i+) /System.out.println(第+i+”处合
19、并的单元格：/+,getTopLeft=+rangei.getTopLeft().getColumn()/+,+rangei.getTopLeft().getRow()/+,getBottomRight=+rangei.getBottomRight().getColumn()/+,+rangei.getBottomRight().getRow()/);/topleftXIndex, topleftYIndex, bottomRightXIndex, bottomRightYIndexwritableSheet.mergeCells(rangei.getTopLeft().getColumn()
20、,rangei.getTopLeft().getRow(),rangei.getBottomRight().getColumn(), rangei.getBottomRight().getRow();/4. 设置行列高宽for (int colIndex = 0; colIndex readonlySheet.getColumns(); colIndex+) writableSheet.setColumnView(colIndex,readonlySheet.getColumnView(colIndex);for (int rowIndex = 0; rowIndex readonlyShee
21、t.getRows(); rowIndex+) writableSheet.setRowView(rowIndex,readonlySheet.getRowView(rowIndex);wwb.write();wwb.close();os.close();public static void writeExcelUseFormat(String outputFilePath,String outputFileSheetName) throws ExceptionOutputStream os=new FileOutputStream(outputFilePath);/ 输出的 Excel 文件
22、 URL WritableWorkbook wwb = Workbook.createWorkbook(os);/ 创建可写工作薄WritableSheet sheet = wwb.createSheet(outputFileSheetName, 0);/ 创建可写工作表sheet.addCell(new Label(0, 0, 号码 );sheet.addCell(new Label(1, 0, 有效期 );/1. 写入时间的数据格式jxl.write.DateFormat df = new jxl.write.DateFormat(yyyy-MM-dd);jxl.write.Writabl
23、eCellFormat wcfDF = new jxl.write.WritableCellFormat(df);jxl.write.DateTime labelDTF = new jxl.write.DateTime(1, 1, new Date(), wcfDF); / 自定义格 式sheet.addCell(labelDTF);/2. 字体样式/ WritableFont() 方法里参数说明：/ 这个方法算是一个容器，可以放进去好多属性/ 第一个 : TIMES 是字体大小，他写的是18/ 第二个 : BOLD 是判断是否为斜体,选择 true 时为斜体/第三个: ARIAL/第四个:
24、UnderlineStyle.NO_UNDERLINE下划线/ 第五个 : jxl.format.Colour.RED 字体颜色是红色的 jxl.write.WritableFont wf = new jxl.write.WritableFont(WritableFont.TIMES, 18,WritableFont.BOLD, true);jxl.write.WritableCellFormat wcfF = new jxl.write.WritableCellFormat(wf);wcfF.setWrap(true);/ 自动换行wcfF.setAlignment(jxl.format.A
25、lignment.CENTRE);/ 把水平对齐方式指定为居中wcfF.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);/ 把垂直对齐方式指定为居中jxl.write.Label labelC = new jxl.write.Label(0, 1, This is a Label cell, wcfF); sheet.addCell(labelC);/3. 添加带有 formatting 的 Number 对象jxl.write.NumberFormat nf = new jxl.write.NumberFormat(#.#)
26、;jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf);jxl.write.Number labelNF = new jxl.write.Number(0, 2, 3.1415926, wcfN); sheet.addCell(labelNF);/4. 添加 Boolean 对象jxl.write.Boolean labelB = new jxl.write.Boolean(0, 3, false); sheet.addCell(labelB);/5. 设置一个注解WritableCellFeature
27、s cellFeatures = new WritableCellFeatures();cellFeatures.setComment(添力口 Boolean 对象)labelB.setCellFeatures(cellFeatures);/6. 单元格内换行WritableCellFormat wrappedText = new WritableCellFormat(WritableWorkbook.ARIAL_10_PT);wrappedText.setWrap(true);/ 可换行的 label 样式Label label = new Label(4, 0, 测试 ,012 测试。 。
28、 。 ,wrappedText); / 012 强制换行 sheet.addCell(label);/7. 数字的公式计算Number n = new jxl.write.Number(0,9,4.5);/A10 sheet.addCell(n);n = new Number(1,9, 8);/B10sheet.addCell(n);NumberFormat dp3 = new NumberFormat(#.#); / 设置单元格里面的数字格式WritableCellFormat dp3cell = new WritableCellFormat(dp3);dp3cell.setWrap(tru
29、e);Formula f = new Formula(2, 9, (a10+b10)/2, dp3cell); / 设置 C10 公式 sheet.addCell(f);f = new Formula(3, 9, SUM(A10:B10), dp3cell);/ 设置 D10 公式sheet.addCell(f);/8. 设置 sheet 的样式sheet.getSettings().setProtected(true); /设置 xls 的保护，单元格为只读的sheet.getSettings().setPassword(123); / 设置 xls 的密码sheet.getSettings
30、().setDefaultColumnWidth(10); / 设置列的默认宽度,2cm 左右sheet.setRowView(3,200);/ 设置第 4 行高度sheet.setRowView(2,false);/ 这样可以自动把行高扩展sheet.setColumnView(0 , 300);/ 设置第 1 列宽度， 6cm 左右sheet.mergeCells(0 , 5, 1, 7);/合并单元格： 合并 A6B8 也就是 1 列 6 行 与 2 列 7 行 之间 的矩形/9. 设置边框drawRect(sheet, 5, 6, 7, 6,BorderLineStyle.THICK,
31、 Colour.BLACK, null);wwb.write();wwb.close();os.close();public static void drawRect(WritableSheet sheet, int x, int y, int width, int height,BorderLineStyle style,Colour BorderColor, Colour bgColor) throws WriteException for(int w = 0; w width; w+)for(int h = 0; h height; h +) WritableCellFormat ali
32、gnStyle = new WritableCellFormat(); / 单元格样式alignStyle.setAlignment(Alignment.CENTRE); /设置对齐方式alignStyle.setVerticalAlignment(VerticalAlignment.CENTRE);/ 设置对齐方式if(h = 0)/ 画上alignStyle.setBorder(Border.TOP, style, BorderColor);/ 设置边框的颜色和样式if(w = 0)/ 画左alignStyle.setBorder(Border.LEFT, style, BorderCol
33、or);/ 设置边框的颜色和样式if(w = width - 1)/ 画右alignStyle.setBorder(Border.RIGHT, style, BorderColor);/ 设置边框的颜色和样式if(h = height -1)/ 画下alignStyle.setBorder(Border.BOTTOM, style, BorderColor);/ 设置边框的颜色和样式/drawLine(sheet, x, y, Border.BOTTOM);if(bgColor != null)alignStyle.setBackground(bgColor); / 背静色Label merg
34、elabel = new Label(x, y, , alignStyle);/topleftXIndex, topleftYIndex, bottomRightXIndex, bottomRightYIndex/sheet.mergeCells(2, 5, 10, 10);sheet.addCell(mergelabel);y+;y -= height;x+;public static ArrayList sampleReadExcel(File inputFile, int inputFileSheetIndex) throws Exception ArrayList list = new
35、 ArrayList();Workbook book = null;Cell cell = null;/避免乱码的设置WorkbookSettings setting = new WorkbookSettings();java.util.Locale locale = new java.util.Locale(zh,CN);setting.setLocale(locale);setting.setEncoding(ISO-8859-1);book = Workbook.getWorkbook(inputFile, setting);Sheet sheet = book.getSheet(inp
36、utFileSheetIndex);for (int rowIndex = 0; rowIndex sheet.getRows(); rowIndex+) / Excel 第一行为表头, 因此 J 初值设为 1for (int colIndex = 0; colIndex sheet.getColumns(); colIndex+) / 只需从 Excel 中取出 2 列cell = sheet.getCell(colIndex, rowIndex);list.add(cell.getContents();/【 问题：如果在实际部署的时候没有写下面这句是否会导致不断消耗掉服务器的内存？jxl
37、里面有个ReadWrite.java 没有关闭读的，只关闭了写的】book.close();return list;public static void setCellValueDirectly(WritableSheet sheet, Cell cell, Object newValue, CellType type) throws Exceptionif(type = CellType.DATE | type = CellType.DATE_FORMULA)sheet.addCell(new jxl.write.DateTime(cell.getColumn(), cell.getRow(
38、), (Date)newV alue,new jxl.write.WritableCellFormat( cell.getCellFormat();else if(type = CellType.NUMBER | type = CellType.NUMBER_FORMULA) sheet.addCell(new jxl.write.Number( cell.getColumn() , cell.getRow(), (Double)newValue).doubleValue(),new jxl.write.WritableCellFormat( cell.getCellFormat();else
39、 if(type = CellType.LABEL | type = CellType.STRING_FORMULA) sheet.addCell(new Label( cell.getColumn() , cell.getRow() , (String)newValue , new jxl.write.WritableCellFormat(cell.getCellFormat();elsethrow new Exception( 不支持的其它单元格类型： +type);/ System.err.println( 不 支 持 的 其 它 单 元 格 类 型 ： + cell.getType() + at + cell.getColumn() + , + cell.getRow() + current content: + cell.getContents();