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

﹏ヽ暗。殇╰゛Y 2022-06-11 09:27 129阅读 0赞

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