一、下载jxl.jar包导入到工程中
https://download.csdn.net/download/xiaocha1994/10341577
二、新建Book.java
package com.cha.model;
public class Book {
private int id;
private String name;
private String type;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getType() {
return type;
}
public void setType(String type) {
this.type = type;
}
}
三、新建ExcelBook
package com.cha.excel;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import com.cha.model.Book;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
public class ExcelBook {
//将数据导出到Excel
public void excelOut(ArrayList<Book> arrayList) {
WritableWorkbook bWorkbook = null;
try {
// 创建Excel对象
bWorkbook = Workbook.createWorkbook(new File("D:/book.xls"));
// 通过Excel对象创建一个选项卡对象
// 生成名为“第一页”的工作表,参数0表示这是第一页
// 在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
WritableSheet sheet = bWorkbook.createSheet("第一页", 0);
WritableSheet sheet2 = bWorkbook.createSheet("第2页", 1);
//第一个参数向左【行】。第二个参数向下【列】,第三个内容
Label labe0=new Label(0,0,"标题");
Label labe00=new Label(1,0,"标题2");
Label labe000=new Label(2,0,"标题3");
sheet.addCell(labe0);
sheet.addCell(labe00);
sheet.addCell(labe000);
/*
* 生成一个保存数字的单元格 必须使用Number的完整包路径,否则有语法歧义 单元格位置是第二列,第一行,值为789.123
*/
jxl.write.Number number = new jxl.write.Number( 5 , 0 , 555.12541 );
sheet.addCell(number);
//设置字体规格等
WritableFont font1 = new WritableFont(WritableFont.TIMES, 16 ,WritableFont.BOLD);
WritableCellFormat format1 = new WritableCellFormat(font1);
// 把水平对齐方式指定为居中
format1.setAlignment(jxl.format.Alignment.CENTRE);
// 把垂直对齐方式指定为居中
format1.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
Label labelx = new Label( 6 , 0 ,"data 4 test",format1);
sheet.addCell(labelx);
//合并单元格
//WritableSheet.mergeCells( int a, int n, int p, int q);
// 作用是从(m,n)到(p,q)的单元格全部合并,比如:
WritableSheet sheet3 = bWorkbook.createSheet("第3页", 2 );
//合并既可以是横向的,也可以是纵向的。合并后的单元格不能再次进行合并,否则会触发异常。
// 合并第一列第一行到第六列第一行的所有单元格
sheet3.mergeCells( 0 , 0 , 5 , 0 );
//设置行高和列宽
//WritableSheet.setRowView( int i, int height);
// 作用是指定第i+1行的高度,比如:
// 将第一行的高度设为200
sheet3.setRowView( 0 , 200 );
//WritableSheet.setColumnView( int i, int width);
// 作用是指定第i+1列的宽度,比如:
// 将第一列的宽度设为30
sheet3.setColumnView( 0 , 30 );
//使用循环将数据读出
for (int i = 0; i < arrayList.size(); i++) {
Book book=arrayList.get(i);
Label label=new Label(0,i+1,String.valueOf(book.getId()));
Label label1=new Label(1,i+1,String.valueOf(book.getName()));
Label label2=new Label(2,i+1,String.valueOf(book.getType()));
sheet.addCell(label);
sheet.addCell(label1);
sheet.addCell(label2);
}
// 写如目标路径
bWorkbook.write();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
bWorkbook.close();
} catch (WriteException | IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
//将Excel中的数据导入
public ArrayList<Book> ExcelIn(){
ArrayList<Book>arrayList=new ArrayList<Book>();
Workbook bWorkbook=null;
try {
bWorkbook=Workbook.getWorkbook(new File("D:/book.xls"));
Sheet sheet=bWorkbook.getSheet(0);
for (int i = 0; i < sheet.getRows(); i++) {
Book book=new Book();
//获取单元格对象
Cell cell =sheet.getCell(0,i);
//获取单元格的值
book.setId(Integer.valueOf(cell.getContents()));
book.setName(sheet.getCell(1,i).getContents());
book.setType(sheet.getCell(2, i).getContents() );
arrayList.add(book);
}
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
bWorkbook.close();
}
return arrayList;
}
public static void main(String[] args) {
//将数据导出到Excel中
ExcelBook book = new ExcelBook();
ArrayList<Book> arrayList = new ArrayList<Book>();
Book bo = new Book();
bo.setId(1);
bo.setName("酒馆");
bo.setType("生活");
Book bo1 = new Book();
bo1.setId(2);
bo1.setName("酒馆1");
bo1.setType("生活1");
arrayList.add(bo);
arrayList.add(bo1);
arrayList.add(bo);
arrayList.add(bo1);
arrayList.add(bo);
arrayList.add(bo1);
book.excelOut(arrayList);
/*
* 如果在web端如何实现呢,就是在文件上传的时候获取到这个文件名和绝对路径就直接可以new File("xxxx")啦
*
*/
//类似于导入excel到数据库的感觉
// //将数据从Excel中导入
// ArrayList<Book> arrayList1 = book.ExcelIn();
// for(Book bo2:arrayList1){
// System.out.println(bo2.getName()+bo2.getType());
// }
}
}
四.原文地址: https://blog.csdn.net/lrici/article/details/56012054