Aspose.Cells导出Excel（Excel模板）

      本篇中将简单记录下Aspose.Cells这个强大的Excel操作组件。这个组件的强大之处，就不多说，对于我们的报表总是会有导出Excel的处理，如果你使用微软Excel的com组件，那么对于简单的操作还行，但是多余复杂的模板，那将是一个令人头疼的事。在Aspose.Cells之下，将是一个简单的事情。他可以导入导出excel操作，在本节将要说的就是他的际遇excel模板的导出强大功能。

    多的不说，我们先来利用Northwind做两个小demo。先说说Aspose.Cells的模板语法：

1. &=DataSource.Field，&=[DataSource].[Field]是对DataTable和几何类型的引用，将会从当前行开始竖直向下生成多行数据。
2. &=$data:是对变量或数组的引用。数组存在skip，horizontal等属性，具体参见官方网站
3. &=&=动态公式计算；{r}当前行，{c}当前列，{-n}，{n}当前行或列的偏移量前n或后n。
4. &==是动态计算，如excel，if等语句。（if（logic_test,true_value,false_value））

 

Excel模板（对象）：

 

        public void ExportActDaily(int actId, DateTime beginTime, DateTime endTime)
        {
            try
            {
                List<ActDailyDTO> data = _OrderManage.GetActDaily(actId, beginTime, endTime);
                string title = _OrderManage.GetActTitle(actId);
                string bTime = beginTime.ToString("yyyy-mm-dd");
                string eTime = endTime.ToString("yyyy-mm-dd");
                string queryTime = DateTime.Now.ToString("yyyy-mm-dd HH:MM");
                string filePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "/Data/ActDaily.xlsx";
                WorkbookDesigner designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(filePath);
                designer.SetDataSource("title", title);
                designer.SetDataSource("statisticalTnterval", string.Format("统计区间：{0}至{1}", bTime, eTime));
                designer.SetDataSource("queryTime", string.Format("查询时间：{0}", queryTime));
                designer.SetDataSource("ActDailyDTO", data);
                designer.Process();
                designer.Workbook.SaveOptions.SaveFormat = SaveFormat.Xlsx;             
                designer.Workbook.Save(System.Web.HttpContext.Current.Response, string.Format("中奖情况统计表{0}.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff")),
                    ContentDisposition.Attachment, designer.Workbook.SaveOptions);                
                designer = null;
            }
            catch (Exception ex)
            {
                WriteLog.CreateLog(ex);
            }
        }

 

Excel模板（DateTable）：

        public DataTable GetStationInfoFromDb()
        {
            try
            {
                string sql = @"select d.StationCode,d.NearestStationDistance,d.NearestSchoolDistance,
                                p.ProvinceId,p.AreaCode,p.Address,d.HouseUseRights,d.HouseArea,d.HouseRent,
                                (select StationTypeDesc from DB_Division.DicStationType st where st.StationType=d.StationType) as StationType,
                                (select Name from DB_Division.PersonServiceMan m where m.Id=d.ServiceManID)as ServiceMan,
                                d.StationStar,d.ConsigneeCode,
                                (case when d.IsScanGun=1 then '是' else '否' end) as IsScanGun,
                                (case when d.IsNetworkVideo=1 then '是' else '否' end) as IsNetworkVideo,
                                (case when d.IsAccessLine=1 then '是' else '否' end) as IsAccessLine,
                                (case when d.JoinSSQTimes>0 then '是' else '否' end) as IsJoinSSQ,
                                d.JoinSSQTimes,d.StationStandardType, d.StationAwardType,p.Longitude,p.Latitude,p.Img,'http://120.76.202.4:20144/Images/' as url
                                from DB_Division.StationInfoDetail d,DB_Division.PersonStation p
                                where d.StationID=p.Id and p.ProvinceId=15 and p.AccountState=1 ";
                DataTable dt = helper.GetDataTable(sql, CommandType.Text);
                foreach (DataRow row in dt.Rows)
                {
                    //把StationStandardType换成StationStandardTypeDesc
                    string sql1 = @"select StationStandardTypeDesc from DB_Division.DicStationStandardType where StationStandardType in
                                    ("+ row["StationStandardType"].ToString() + ")";
                    DataTable dt1 = helper.GetDataTable(sql1, CommandType.Text);
                    string StationStandardTypeDesc = string.Join(",", dt1.AsEnumerable().Select(m => m.Field<string>("StationStandardTypeDesc")).ToList());
                    row["StationStandardType"] = StationStandardTypeDesc;

                    //把StationAwardType换成StationAwardTypeDesc
                    string sql2 = @"select StationAwardTypeDesc from DB_Division.DicStationAwardType where StationAwardType in
                                    (" + row["StationAwardType"].ToString() + ")";
                    DataTable dt2 = helper.GetDataTable(sql2, CommandType.Text);
                    string StationAwardTypeDesc = string.Join(",", dt2.AsEnumerable().Select(m => m.Field<string>("StationAwardTypeDesc")).ToList());
                    row["StationAwardType"] = StationAwardTypeDesc;
                }
                dt.TableName = "Station";
                return dt;
            }
            catch (Exception e)
            {
                return null;
            }
        }

        private void GetStationInfo()
        {
            logger2.WriteLog("GetStationInfo", $"-----------------------Begin-----------------------", Level.Medium);
            DataTable dt = new ServiceDAL().GetStationInfoFromDb();
            if (dt == null)
            {
                logger2.WriteLog("GetStationInfo", $"没有站点更新数据", Level.Medium);
                return;
            }
            logger2.WriteLog("GetStationInfo", $"{dt.Rows.Count}条站点数据，开始生成csv文件", Level.Medium);
            try
            {
                WorkbookDesigner designer = new WorkbookDesigner();
                designer.Workbook = new Workbook(AppDomain.CurrentDomain.BaseDirectory + "SiteInfoTemplate.xlsx");
                //数据源 
                designer.SetDataSource(dt);
                designer.Process();
                string fileName = "elefusite_" + DateTime.Now.ToString("yyyy-MM-dd");
                designer.Workbook.Save(AppDomain.CurrentDomain.BaseDirectory + fileName + ".csv", SaveFormat.CSV);
                designer = null;

                string localFile = AppDomain.CurrentDomain.BaseDirectory + fileName + ".csv";
                string ftpFile = AppDomain.CurrentDomain.BaseDirectory + Config.ElefuSiteFolder + "\\" + fileName + ".zip";

                ZipHelper.ZipFile(localFile, ftpFile);
                File.Delete(localFile);
                FtpHelper.UpLoadFileToFtp(ftpFile, Config.ElefuSiteFolder, logger2);
                logger2.WriteLog("GetStationInfo", $"-----------------------End-----------------------", Level.Medium);
            }
            catch (Exception e)
            {
                logger1.WriteError($"生成csv文件失败：{e.Message}");
            }
        }