首先需要下载jxl的jar包,下载地址为jxl下载
1、读取Excel的数据
Workbook readwb = null;
InputStream in = null;
try{
//创建输入流
in=new FileInputStream("f:/jxlrwtest.xls");
//获取第一个工作表
readwb = Workbook.getWorkbook(in);
Sheet readsheet = readwb.getSheet(0);
//这段可以获得表中的图片信息
int num = readsheet.getNumberOfImages();
//图片的个数
System.out.println(num);
byte[] temp;
for(int i =0 ; i<num ;i++){
if(readsheet.getDrawing(i).getImageData().length>3){
temp = readsheet.getDrawing(i).getImageData();
System.out.print(readsheet.getDrawing(i).getRow()+",");
System.out.print(readsheet.getDrawing(i).getColumn()+",");
System.out.print(readsheet.getDrawing(i).getWidth()+",");
System.out.println(readsheet.getDrawing(i).getHeight());
//生成图片并保存
FileImageOutputStream put = new FileImageOutputStream(new File("f:/img"+i+".jpg"));
put.write(temp, 0, temp.length);
put.close();
}
}
//获得表格的行数列数
int rsColumns = readsheet.getColumns();
int rsRows = readsheet.getRows();
//循环读取表格中的数据
for(int i = 0; i<rsRows; i++){
System.out.print(i+1+",");
for(int j = 0;j<rsColumns;j++){
Cell cell = readsheet.getCell(j,i);
//获取单元格的内容和类型
System.out.print(cell.getContents()+"是"+cell.getType()+"\t");
}
System.out.println();
}
}catch(Exception e){
e.printStackTrace();
}finally{
readwb.close();
try{
if(in!=null){
in.close();
}
}catch(Exception e){
e.printStackTrace();
}
}
2、向Excel写入数据
WritableWorkbook book = null;
try{
//新建工作表
book = Workbook.createWorkbook(new File("f:/writetest.xls"));
WritableSheet sheet = book.createSheet("测试", 0);
//合并单元格,设置行高列宽
sheet.mergeCells(0, 0, 2, 2);
sheet.setRowView(0, 1000);
sheet.setColumnView(1, 10);
//添加Number对象
jxl.write.Number number = new jxl.write.Number(3,0,234.354);
sheet.addCell(number);
//格式化添加Number对象
NumberFormat nf = new NumberFormat("#.##");
WritableCellFormat wcf = new WritableCellFormat(nf);
jxl.write.Number laberNF = new jxl.write.Number(3,1,234.354,wcf);
sheet.addCell(laberNF);
//添加boolean对象
jxl.write.Boolean labelB = new jxl.write.Boolean(3,2,true);
sheet.addCell(labelB);
//添加datetime 对象
sheet.addCell(new jxl.write.DateTime(4,4,new Date()));
jxl.write.DateFormat df = new jxl.write.DateFormat("yyyy-MM-dd HH:mm:ss");
WritableCellFormat wcDF = new WritableCellFormat(df);
jxl.write.DateTime labelDT = new jxl.write.DateTime(3,4,new Date(),wcDF);
sheet.addCell(labelDT);
//设置单元格样式
WritableFont font1 = new WritableFont(WritableFont.createFont("隶书"),16,WritableFont.BOLD);
WritableCellFormat format1 = new WritableCellFormat(font1);
format1.setAlignment(Alignment.RIGHT);
format1.setVerticalAlignment(VerticalAlignment.TOP);
format1.setBorder(Border.ALL,BorderLineStyle.THIN);
format1.setBackground(Colour.RED);
Label label = new Label(0,0,"test",format1);
sheet.addCell(label);
//插入图片
File imgfile = new File("f:/test.png");
WritableImage img = new WritableImage(1,5,2,5,imgfile);
sheet.addImage(img);
//添加页眉页脚
HeaderFooter hf = new HeaderFooter();
hf.getLeft().append("左");
hf.getCentre().append("中");
hf.getRight().append("右");
sheet.getSettings().setHeader(hf);
sheet.getSettings().setFooter(hf);
book.write();
}catch(Exception e){
e.printStackTrace();
}finally{
}
}