基于反射的Excel动态上传下载

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();        } }