예제 #1
0
        public static void Run()
        {
            //Source directory
            string sourceDir = RunExamples.Get_SourceDirectory();

            // Create an instance of Workbook and load an existing spreadsheet
            var book = new Workbook(sourceDir + "sampleDetectEmptyWorksheets.xlsx");

            // Loop over all worksheets in the workbook
            for (int i = 0; i < book.Worksheets.Count; i++)
            {
                Worksheet sheet = book.Worksheets[i];
                // Check if worksheet has populated cells
                if (sheet.Cells.MaxDataRow != -1)
                {
                    Console.WriteLine(sheet.Name + " is not Empty because one or more Cells are Populated");
                }
                // Check if worksheet has shapes
                else if (sheet.Shapes.Count > 0)
                {
                    Console.WriteLine(sheet.Name + " is not Empty because there are one or more Shapes");
                }
                // Check if worksheet has empty initialized cells
                else
                {
                    Aspose.Cells.Range range = sheet.Cells.MaxDisplayRange;
                    var rangeIterator        = range.GetEnumerator();
                    if (rangeIterator.MoveNext())
                    {
                        Console.WriteLine(sheet.Name + " is not Empty because one or more cells are Initialized");
                    }
                }
            }

            Console.WriteLine("DetectEmptyWorksheets executed successfully.");
        }
        public static void Run()
        {
            // ExStart:DetectEmptyWorksheets
            // The path to the documents directory.
            string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

            // Create an instance of Workbook and load an existing spreadsheet
            var book = new Workbook(dataDir + "sample.xlsx");

            // Loop over all worksheets in the workbook
            for (int i = 0; i < book.Worksheets.Count; i++)
            {
                Worksheet sheet = book.Worksheets[i];
                // Check if worksheet has populated cells
                if (sheet.Cells.MaxDataRow != -1)
                {
                    Console.WriteLine(sheet.Name + " is not empty because one or more cells are populated");
                }
                // Check if worksheet has shapes
                else if (sheet.Shapes.Count > 0)
                {
                    Console.WriteLine(sheet.Name + " is not empty because there are one or more shapes");
                }
                // Check if worksheet has empty initialized cells
                else
                {
                    Aspose.Cells.Range range = sheet.Cells.MaxDisplayRange;
                    var rangeIterator        = range.GetEnumerator();
                    if (rangeIterator.MoveNext())
                    {
                        Console.WriteLine(sheet.Name + " is not empty because one or more cells are initialized");
                    }
                }
            }
            // ExEnd:DetectEmptyWorksheets
        }
예제 #3
0
        public ActionResult ExportQD(string keyValue)
        {
            //获取会议基本信息
            ConferenceEntity cr = conferencebll.GetEntity(keyValue);
            ////获取会议签到人员信息
            //List<Object> data = conferenceuserbll.GetSignTable(keyValue);
            ////签到
            //List<Object> dataQD = data[0] as List<Object>;
            ////未签到
            //List<Object> dataWQD = data[1] as List<Object>;
            ////请假
            //List<Object> dataQ = data[2] as List<Object>;

            string path     = Request.PhysicalApplicationPath;
            int    index    = 0;
            string fileName = HttpUtility.UrlDecode("安全会议签到") + DateTime.Now.ToString("yyyyMMddHHmm") + ".xls";  //定义导出文件名,一定记得编码

            try
            {
                Aspose.Cells.Workbook wk = new Aspose.Cells.Workbook();
                wk.Open(Server.MapPath("~/Resource/ExcelTemplate/安全会议签到表模板.xlsx")); //加载模板
                Aspose.Cells.Worksheet sheet = wk.Worksheets[0];
                Aspose.Cells.Style     style = wk.Styles[wk.Styles.Add()];
                style.HorizontalAlignment = Aspose.Cells.TextAlignmentType.Center; //文字居中
                style.IsTextWrapped       = true;                                  //自动换行
                style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle    = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle  = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Thin;
                style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle   = Aspose.Cells.CellBorderType.Thin;
                string    sql = string.Format(" select t.USERNAME,o.DEPTNAME,t.PHOTOURL,t.issign,t.REVIEWSTATE from BIS_ConferenceUser t left join v_userinfo o on t.userid=o.userid where t.ConferenceID='{0}'", keyValue);
                DataTable dt  = specialequipmentbll.SelectData(sql);
                //参会人员集合
                var listC = dt;
                //签到人员集合
                var SdUser = listC.Select(" issign='0'");
                //未签到人员集合
                var YdUser = listC.Select(" issign<>'0' and REVIEWSTATE<>'2'");
                //请假人数
                var QUser = listC.Select(" REVIEWSTATE='2'");
                sheet.Cells[1, 2].PutValue(cr.ConferenceName); //会议名称
                sheet.Cells[1, 4].PutValue(cr.Locale);         //会议地点
                sheet.Cells[2, 2].PutValue(cr.CompereDept);    //召开部门
                sheet.Cells[2, 4].PutValue(cr.Compere);        //主持人
                sheet.Cells[3, 2].PutValue(cr.Content);        //主要议题
                if (SdUser.Length == 0)
                {
                    sheet.Cells[5, 1].PutValue("无");
                }
                else
                {
                    sheet.Cells[5 + SdUser.Length, 0].PutValue("已签到人数");
                    sheet.Cells.Merge(5, 0, SdUser.Length, 1);
                    foreach (DataRow item in SdUser)
                    {
                        index++;
                        sheet.Cells[4 + index, 1].PutValue(index);
                        sheet.Cells[4 + index, 2].PutValue(item[0].ToString()); //参会人员
                        sheet.Cells[4 + index, 4].PutValue(item[1].ToString()); //所属部门
                        if (!string.IsNullOrEmpty(item[2].ToString()))
                        {
                            string[]   u        = item[2].ToString().Split('/');
                            string     url      = path + "/" + u[1] + "/" + u[2] + "/" + u[3] + "/" + u[4] + "/" + u[5];//签名
                            FileStream fs       = new FileStream(url, FileMode.Open);
                            int        imgIndex = sheet.Pictures.Add(4 + index, 3, fs, 120, 30);
                            fs.Close();
                            sheet.Cells.SetColumnWidth(5, 20);
                            sheet.Cells.SetRowHeight(4 + index, 44);
                            sheet.Pictures[imgIndex].Width  = 139;
                            sheet.Pictures[imgIndex].Height = 50;
                            sheet.Pictures[imgIndex].Top    = 3;
                            sheet.Pictures[imgIndex].Left   = 6;
                        }
                    }
                }
                int num = SdUser.Length == 0 ? 1 : SdUser.Length;
                int s   = SdUser.Length == 0 ? 1 : SdUser.Length;
                if (index > 0)
                {
                    s = 0;
                }

                if (YdUser.Length == 0)
                {
                    sheet.Cells[5 + num, 0].PutValue("未签到人数");
                    sheet.Cells[5 + num, 1].PutValue("无");
                }
                else
                {
                    sheet.Cells[5 + num, 0].PutValue("未签到人数");
                    sheet.Cells.Merge(5 + num, 0, YdUser.Length, 1);
                    foreach (DataRow item in YdUser)
                    {
                        index++;
                        sheet.Cells[4 + s + index, 1].PutValue(index);
                        sheet.Cells[4 + s + index, 2].PutValue(item[0].ToString());
                        sheet.Cells[4 + s + index, 4].PutValue(item[1].ToString());
                        sheet.Cells[4 + s + index, 3].PutValue("");
                    }
                }
                s = YdUser.Length == 0 ? 1 : YdUser.Length;
                int qj = s + SdUser.Length == 0 ? 1 : s + SdUser.Length;
                qj = SdUser.Length == 0 ? qj + 1 : qj;
                int qjNum = 0;
                if (QUser.Length > 0)
                {
                    sheet.Cells[5 + qj, 0].PutValue("请假人数");
                    sheet.Cells.Merge(5 + qj, 0, QUser.Length, 1);
                    foreach (DataRow dr in QUser)
                    {
                        sheet.Cells[5 + qj + qjNum, 1].PutValue(index++ + 1);
                        sheet.Cells[5 + qj + qjNum, 2].PutValue(dr[0].ToString());
                        sheet.Cells[5 + qj + qjNum, 4].PutValue(dr[1].ToString());
                        sheet.Cells[5 + qj + qjNum, 3].PutValue("");
                        qjNum++;
                    }
                }
                else
                {
                    qjNum += 1;
                    sheet.Cells[5 + qj, 0].PutValue("请假人数");
                    sheet.Cells[5 + qj, 1].PutValue("无");
                }
                Aspose.Cells.Range r = sheet.Cells.CreateRange(5, 0, qj + qjNum, 5);
                r.Style = style;
                HttpResponse resp = System.Web.HttpContext.Current.Response;
                wk.Save(HttpUtility.UrlEncode(fileName), Aspose.Cells.FileFormatType.Excel2003, Aspose.Cells.SaveType.OpenInExcel, resp);

                return(ToJsonResult("0"));
            }
            catch (Exception ex)
            {
                string env = Config.GetValue("Environment");
                string msg = env == "dev" ? ex.Message : "对不起,系统出错了";
                return(ToJsonResult("" + msg + ""));
            }
        }