首页 > 代码库 > 从数据库中导出excel报表

从数据库中导出excel报表

 

通常需要将后台数据库中的数据集或者是其他列表等导出excel 报表,这里主要引用了Apose.cells dll 类库,

(1)直接上主要代码:

 

  protected void txtExport_Click(object sender, EventArgs e)         {            

              try             {               

                 // 获取测试商品报表               

                IList<ProductEntity> pList = ProductBLL.getProductList();

                // 导出到Excel中                

               Workbook workbook = new Workbook();               

               Worksheet sheet = workbook.Worksheets[0];            

                 Cells cells = sheet.Cells;//单元格

                for (int i = 0; i < 3; i++)        {      

                        cells.SetColumnWidth(i, 20);     

                                                           }

                cells[1, 0].PutValue("商品序号");        

                cells[1, 1].PutValue("商品名称");        

                cells[1, 2].PutValue("描述");

 

                for (int i = 0; i < pList.Count; i++)                 {         

                        var p = pList[i];                  

                      cells[2 + i, 0].PutValue(p.Number);         

                      cells[2 + i, 1].PutValue(p.Name);            

                     cells[2 + i, 2].PutValue(p.Description);

                    }              

          string fileName = "商品列表报表" + DateTime.Now.ToString("yyyy.MM.dd.HH.mm.ss.ms") + ".xls";             

         string saveFileName = HttpContext.Current.Server.MapPath(@"\ExportFile\" + fileName);

                workbook.Save(saveFileName);

                Label1.Text = "导出报表成功!";        

         }

            catch (Exception ex)             {            

             Label1.Text = "导出报表失败,原因:" + ex.Message;             }

        }

 

(2)获取ProductList 的方法getProductList():

        public static IList<ProductEntity> getProductList(){                   

                   IList<ProductEntity> productList = new List<ProductEntity>();

                  DataTable dt = ProductDAL.getProductList();

                   if(dt!=null && dt.Rows.Count>0)           

                      {

                       productList =DatatableToObject.ConvertToList<ProductEntity>(dt);            

                       }

                     return productList;

        }

 

(3) 顺便介绍比较实用用的DataToObject.ConvertToList<T>() 方法,可以直接将从数据库中读取的DataTable 转化成列表List<T>,

 最近才在上面写文章,不会自动对齐格式哈。

    public class DatatableToObject     {       

          public static T ConvertToObject<T>(DataRow row) where T : new()         {

                         System.Object obj = new T();         

                        if (row != null)             {    

                         DataTable dataTable = row.Table;           

                         GetObject(dataTable.Columns, row, obj);            

                        }

                 if (obj != null && obj is T)             {      

                            return (T)obj;            

                          }           

                else             {

                       return default(T);            

                                  }       

                  }

 

        private static void GetObject(DataColumnCollection cols, DataRow dr, System.Object obj)       

               {             Type type = obj.GetType();

                            PropertyInfo[] pros = type.GetProperties();   

                            foreach (PropertyInfo pro in pros)             {

                            if (cols.Contains(pro.Name))           

                                {

                             if ((pro.PropertyType).Name.ToLower() == "string")         

                                   pro.SetValue(obj, dr[pro.Name] == DBNull.Value ? "" : dr[pro.Name].ToString(), null);          

                             else                      

                                   pro.SetValue(obj, dr[pro.Name] == DBNull.Value ? null : dr[pro.Name], null);              

                                  }

                           }      

                  }

 

        public static List<T> ConvertToList<T>(DataTable dataTable) where T : new()     

                      {       List<T> list = new List<T>();            

                             foreach (DataRow row in dataTable.Rows)             {    

                                    T obj = ConvertToObject<T>(row);         

                                    list.Add(obj);             }         

                             return list;      

                         }    

                 }

 

(4) 部分运行截图:

   技术分享

 

 

  技术分享

 

从数据库中导出excel报表