使用jxl工具包创建修改excel文件

示例代码：

package com.wlex.xxx.util;import java.io.File;import java.io.FileInputStream;import java.io.FileNotFoundException;import java.io.FileOutputStream;import java.io.IOException;import java.io.InputStream;import java.io.OutputStream;import java.math.BigDecimal;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.List;import java.util.Random;import jxl.Cell;import jxl.CellType;import jxl.DateCell;import jxl.LabelCell;import jxl.NumberCell;import jxl.Sheet;import jxl.Workbook;import jxl.biff.DisplayFormat;import jxl.biff.EmptyCell;import jxl.biff.drawing.Drawing;import jxl.format.UnderlineStyle;import jxl.read.biff.BiffException;import jxl.write.Blank;import jxl.write.DateFormat;import jxl.write.Label;import jxl.write.Number;import jxl.write.NumberFormats;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 ExcelHelper {public static void main(String[] args) {//创建excel示例testCreateExcel(new File("C:\\tmp1.xls"));System.out.println("----------------第一次读入-----------------");//读入excel例子testReadExcel(new File("C:\\tmp1.xls"));//修改excel示例testUpdateExcel(new File("C:\\tmp1.xls"));System.out.println("----------------第二次读入-----------------");//读入excel例子testReadExcel(new File("C:\\tmp1.xls"));}//读入excel文件工作表内容public static List<List<String>> readExcel(InputStream is) {List<List<String>> result = new ArrayList<List<String>>();try {Workbook rwb = Workbook.getWorkbook(is);Sheet st = rwb.getSheet(0);//Sheet st = rwb.getSheet("投资信息");int rs = st.getColumns();int rows = st.getRows();//遍历行for (int k = 0; k < rows; k++) {List<String> list = new ArrayList<String>();//遍历列for (int i = 0; i < rs; i++) {Cell cell = st.getCell(i, k);String cellValue = getCellValue(cell);list.add(cellValue);}result.add(list);}rwb.close();} catch (Exception e) {e.printStackTrace();}return result;}//根据单元格类型取值protected static String getCellValue(Cell cell) {String result = cell.getContents();CellType cellType = cell.getType();if (cellType == CellType.LABEL) {LabelCell labelc00 = (LabelCell) cell;result = labelc00.getString();} else if (cellType == CellType.DATE) {DateCell dc = (DateCell) cell;SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");result = sdf.format(dc.getDate());} else if (cell.getType() == CellType.NUMBER || cell.getType() == CellType.NUMBER_FORMULA) {NumberCell nc = (NumberCell) cell;result = new BigDecimal(nc.getValue()).toString();}return result;}//读入excel例子public static void testReadExcel(File file){InputStream is;try {is = new FileInputStream(file);List<List<String>> result = ExcelHelper.readExcel(is);for (List<String> row : result) {for (Object object : row) {System.out.print(object + "\t\t");}System.out.println();}} catch (FileNotFoundException e) {e.printStackTrace();}}//创建excel例子public static void testCreateExcel(File file) {try {OutputStream os = new FileOutputStream(file);WritableWorkbook wwb = Workbook.createWorkbook(os);WritableSheet ws = wwb.createSheet("工作表1", 0);//列头(第一行信息，行索引是0，列索引分别是0,1,2,3)ws.addCell(new Label(0, 0, "姓名"));ws.addCell(new Label(1, 0, "年龄"));ws.addCell(new Label(2, 0, "性别"));//对单元格进行字体颜色设置WritableFont wfc = new WritableFont(WritableFont.COURIER, 10, WritableFont.BOLD, false, UnderlineStyle.SINGLE, jxl.format.Colour.RED);ws.addCell(new Label(3, 0, "投资总额", new WritableCellFormat(wfc)));String[] nameArr = new String[]{"张三", "李四", "王五"};for(int i = 0; i < 3; i++) {ws.addCell(new Label(0, i+1, nameArr[i]));ws.addCell(new Number(1, i+1, new Double((new Random()).nextInt(100))));ws.addCell(new Label(2, i+1, "男"));//定义投资额单元格的显示格式DisplayFormat df = NumberFormats.FLOAT;WritableCellFormat wcf = new WritableCellFormat(df);double loanAmt = (new Random()).nextInt(10000);BigDecimal bd = new BigDecimal(loanAmt).setScale(2);Number labelNF = new Number(3, i+1, bd.doubleValue(), wcf);ws.addCell(labelNF);}//写入工作表wwb.write();wwb.close();} catch (Exception e) {e.printStackTrace();}}//修改文件例子public static void testUpdateExcel(File file) {//更改第一个工作表，将第一行第一列单元格更改为“首列”try {InputStream is = new FileInputStream(file);Workbook rbook = Workbook.getWorkbook(is);WritableWorkbook wbook = Workbook.createWorkbook(file, rbook);WritableSheet wSheet = wbook.getSheet(0);WritableCell cell = wSheet.getWritableCell(0, 0);WritableCell newCell = new Label(cell.getColumn(), cell.getRow(), "首列", cell.getCellFormat());wSheet.addCell(newCell);wbook.write();wbook.close();rbook.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();} catch (BiffException e) {e.printStackTrace();} catch (RowsExceededException e) {e.printStackTrace();} catch (WriteException e) {e.printStackTrace();}}//简单填充单元格封装public static Cell createCell(String type, int c, int r, Object value) {Cell result = new EmptyCell(c, r);if(value != null) {if ("label".equalsIgnoreCase(type)) {result = new jxl.write.Label(c, r, value.toString());} else if ("number".equalsIgnoreCase(type)) {result = new jxl.write.Number(c, r, (Double) value);} else if ("boolean".equalsIgnoreCase(type)) {result = new jxl.write.Boolean(c, r, (java.lang.Boolean) value);} else if ("date".equalsIgnoreCase(type)) {DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd HH:mm:ss");WritableCellFormat wcf = new WritableCellFormat(df);result = new jxl.write.DateTime(c, r, (Date) value, wcf);} else {result = new Blank(c, r);}}return result;}//画图例子protected static Drawing testCreateDrawing() {//jxl包只支持png格式图片File image = new File("C:\\img01.png");//从列索引为0行索引为1的地方开始，宽度为2高度为3WritableImage wrImage = new WritableImage(0, 1, 2, 3, image);return wrImage;}}