package org.gyy.service.excel;
import java.io.File;
import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.List;
import org.gyy.dao.Excel;
import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
public class ExcelService {
public static void creatExcel(List<?> list,String[] heards){
try {
WritableWorkbook wwb = null;
// 创建可写入的Excel工作簿
String fileName = "F://book.xls";
File file=new File(fileName);
if (!file.exists()) {
file.createNewFile();
}
//以fileName为文件名来创建一个Workbook
wwb = Workbook.createWorkbook(file);
// 创建工作表
WritableSheet ws = wwb.createSheet("Sheet1", 0);
//要插入到的Excel表格的行号，默认从0开始
for (int i = 0; i < heards.length; i++) {
Label labelId= new Label(i, 0, heards[i]);//表示第
ws.addCell(labelId);
}
for (int i = 0; i < list.size(); i++) {
Object obj = list.get(i);
Class<? extends Object> cl = obj.getClass();
Field[] fields = cl.getDeclaredFields();
for (int j = 0; j < fields.length; j++) {
fields[j].setAccessible(true);
Label label= new Label(j, i+1, String.valueOf(fields[j].get(obj)));
ws.addCell(label);
}
}
//写进文档
wwb.write();
// 关闭Excel工作簿对象
wwb.close();
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 查询指定目录中电子表格中所有的数据
* @param file 文件完整路径
* @return
*/
public static List getAllByExcel(String file,Object obj){
List list=new ArrayList();
try {
Workbook rwb=Workbook.getWorkbook(new File(file));
Sheet rs=rwb.getSheet(0);//或者rwb.getSheet(0)
int clos=rs.getColumns();//得到所有的列
// int rows=rs.getRows();//得到所有的行
int rows = rows(file);
System.out.println(clos+" rows:"+rows);
for (int i = 1; i < rows; i++) {
for (int j = 0; j < clos; j++) {
Object instance = obj.getClass().newInstance();
Field[] fields = obj.getClass().getDeclaredFields();
for (int k = 0; k < fields.length; k++) {
String name = fields[k].getName(); // 获取属性的名字
name = name.substring(0, 1).toUpperCase() + name.substring(1); // 将属性的首字符大写，方便构造get，set方法
String type = fields[k].getGenericType().toString(); // 获取属性的类型
if (type.equals("class java.lang.String")) { // 如果type是类类型，则前面包含"class "，后面跟类名
String id=rs.getCell(j++, i).getContents();//默认最左边编号也算一列 所以这里得j++
fields[k].setAccessible(true);
fields[k].set(instance, id);//给对象设置值
}
}
list.add(instance);
//list.add(new Excel(id, name, sex, num));
break;
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return list;
}
//得到实际行数，除掉标题
private static int rows(String file){
int row=0;
try {
Workbook rwb=Workbook.getWorkbook(new File(file));
Sheet rs=rwb.getSheet(0);//或者rwb.getSheet(0)
int clos=rs.getColumns();//得到所有的列
int rows=rs.getRows();//得到所有的行
for (int i = 1; i < rows; i++) {
for (int j = 0; j < clos; j++) {
//第一个是列数，第二个是行数
Cell cell = rs.getCell(j, i);//默认最左边编号也算一列 所以这里得j++
CellType cellType = cell.getType();
if (cellType != CellType.EMPTY) {
row+=1;
break;
}
}
}
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return row;
}
public static void main(String[] args) {
List<Excel> allByExcel = getAllByExcel("F://test.xls",new Excel());
for (Excel excel : allByExcel) {
System.out.println(excel);
}
String[] heards = {"id","name","sex","num"};
creatExcel(allByExcel,heards);
}
}
heards是标题
如果公司数据库返回数据使用的List<Map<String, Object>> list格式可以使用这个代码生成excel，自动设置标题，内容
public static void creatExcel(List<Map<String, Object>> list,String fileName){ try { WritableWorkbook wwb = null; // 创建可写入的Excel工作簿 File file=new File(fileName); if (!file.exists()) { file.createNewFile(); } //以fileName为文件名来创建一个Workbook wwb = Workbook.createWorkbook(file); ArrayList<Object> arrayList = new ArrayList<>(); int k = 0; // 创建工作表 WritableSheet ws = wwb.createSheet("Sheet1", 0); if (CollectionUtils.isNotEmpty(list)) {if (MapUtils.isNotEmpty(list.get(0))) {Map<String, Object> map = list.get(0);Set<String> keySet = map.keySet();for (String string : keySet) {arrayList.add(string);Label labelId= new Label(k++, 0,string);//设置标题ws.addCell(labelId);}for (int i = 0; i < list.size(); i++) {Map<String, Object> mapdata = list.get(i);for (int j = 0; j < arrayList.size(); j++) {Label label= new Label(j, i+1,String.valueOf(mapdata.get(arrayList.get(j))) );//设置内容ws.addCell(label);}}}} //写进文档 wwb.write(); // 关闭Excel工作簿对象 wwb.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } }
