目录
一:先到http://poi.apache.org/官网下载poi相关jar包
一:先到http://poi.apache.org/官网下载poi相关jar包
我这里使用的版本是4.0.1
解压后选取需要的jar包
接下来就是熟悉的构建工程了,将jar包文件放到lib目录下
二:先来做一个简单的demo
public class Demo {
public static void main(String [] args){
//1.创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
//2.创建sheet
HSSFSheet sheet = workbook.createSheet("学生信息");
//3.创建行(下标从0开始)
HSSFRow row = sheet.createRow(0);
//4.创建列
HSSFCell cell = row.createCell(0);
//5.填充列
cell.setCellValue("test");
//6.文件输出
try {
OutputStream out = new FileOutputStream("D:/student.xls");
workbook.write(out);
out.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
三:书写通用工具类
/**
* Excel常用工具类
* @author liuxiang
*
*/
public class ExcelUtil {
/**
* 将Excel数据导出到excel
* @param data
* @param out
* @param field
*/
//每页显示条数
private static int sheetSize = 5000;
//导出
public static <T> void listToExcel(List<T> data,
OutputStream out,Map<String, String> fields) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException, IOException{
if(data==null||data.size()==0){
throw new RuntimeException("传入的数据源中无数据!");
}
//xls格式的excel不能存储太多的数据,最大65536
HSSFWorkbook workbook = new HSSFWorkbook();
//1.计算一共有多少sheet(页)
int sheetNum = data.size()/sheetSize;
if(data.size()%sheetSize!=0){
sheetNum+=1;
}
//字段名和中文名分别存储在两个数组
String[] fieldNames = new String[fields.size()];
String[] chinaNames = new String[fields.size()];
int count = 0;
for(Entry<String,String> entry:fields.entrySet()){
String fieldName = entry.getKey();
String chinaName = entry.getValue();
fieldNames[count] = fieldName;
chinaNames[count] = chinaName;
count++;
}
//填充属性
for(int i=0;i<sheetNum;i++){
int rowCount = 0;
HSSFSheet sheet = workbook.createSheet();
int startIndex = i*sheetSize;//0 5000
int endIndex;
if(i==sheetNum-1){
if(data.size()%sheetSize!=0){
endIndex = startIndex + data.size()%sheetSize -1;
}else{
endIndex = (i+1)*sheetSize - 1;
}
}else{
endIndex = (i+1)*sheetSize - 1;
}
//endIndex = (i+1)*sheetSize - 1>=data.size()?data.size()-1:(i+1)*sheetSize - 1;//4999 9999
//创建行10 0-2 3-5 6-8 9
HSSFRow row = sheet.createRow(rowCount);
for(int j=0;j<chinaNames.length;j++){
HSSFCell cell = row.createCell(j);
cell.setCellValue(chinaNames[j]);//设置中文标题
}
rowCount++;
for(int index=startIndex;index<=endIndex;index++){
T item = data.get(index);
row = sheet.createRow(rowCount);
//通过反射取值
for(int j=0;j<chinaNames.length;j++){
Field field = item.getClass().getDeclaredField(fieldNames[j]);
field.setAccessible(true);
Object o = field.get(item);
String value = o==null?"":o.toString();
HSSFCell cell = row.createCell(j);
cell.setCellValue(value);//设置单元格内容
}
rowCount++;
}
}
workbook.write(out);
out.close();
}
/**
* 导入
* @param in
* @return
* @throws FileNotFoundException
* @throws IOException
*/
public static <T> List<T> excelToList(InputStream in) throws FileNotFoundException, IOException{
List<Student> data = new ArrayList<Student>();
HSSFWorkbook workbook = new HSSFWorkbook(in);
//迭代每一个选项卡
for(int i=0;i<workbook.getNumberOfSheets();i++){
HSSFSheet sheet = workbook.getSheetAt(i);
if(sheet==null){
continue;
}
//循环行
for(int j=1;j<=sheet.getLastRowNum();j++){
HSSFRow row = sheet.getRow(j);
if(row==null){
continue;
}
Student stu = new Student();
stu.setNo(row.getCell(0).getStringCellValue());
stu.setName(row.getCell(1).getStringCellValue());
stu.setAge(Integer.valueOf(row.getCell(2).getStringCellValue()));
stu.setSex(row.getCell(3).getStringCellValue());
data.add(stu);
}
}
return (List<T>) data;
}
}
四:测试程序
程序中这段代码原来使用的是HashMap ,这样会导致生成的EXCEL 数据列随机排列,换成LinkedHashMap 可以有序排列
Map<String,String> fields = new LinkedHashMap<String,String>();
public class Test {
public static void main(String[] args) throws NoSuchFieldException, SecurityException, IllegalArgumentException, IllegalAccessException, IOException {
List<Student> data = new ArrayList<Student>();
Student stu;
stu = new Student();
stu.setName("one");
stu.setNo("1");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("two");
stu.setNo("2");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("three");
stu.setNo("3");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("four");
stu.setNo("4");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("five");
stu.setNo("5");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("six");
stu.setNo("6");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("seven");
stu.setNo("7");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("eight");
stu.setNo("8");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("nine");
stu.setNo("9");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
stu = new Student();
stu.setName("ten");
stu.setNo("10");
stu.setSex("男");
stu.setAge(0);
data.add(stu);
OutputStream out = new FileOutputStream("D:/student.xls");
Map<String,String> fields = new LinkedHashMap<String,String>();
fields.put("no", "学号");
fields.put("name", "姓名");
fields.put("age", "年龄");
fields.put("sex", "性别");
ExcelUtil.listToExcel(data, out, fields);
FileInputStream in = new FileInputStream("D:/student.xls");
List<Student> list = ExcelUtil.excelToList(in);
for(Student student:list){
System.out.println(student.toString());
}
}
}
五:测试结果
修改sheetSize 测试分页情况
sheetSize = 5000;
sheetSize = 3;
第一页
第二页
第三页
第四页
sheetSize = 10;
测试导入(这里没有对EXCEL数据类型进行区分判断)