Beispiel #1
0
        private void bnSendSales_Click(CommandBarButton Ctrl, ref bool CancelDefault)
        {
            // Get the Selected Rows, Create SLSend.Recipients from them, Load SLSend
            try
            {
                SLSend slSend = new SLSend(AppMode.ExcelHostedAddin);

                //Excel.Worksheet wks =
                //	(Excel.Worksheet)excelApp.ActiveWorkbook.ActiveSheet;
                //Excel.Range selectedRange = (Excel.Range)excelApp.Selection;

                // TRY-FIND
                Excel.Areas areas = ((Excel.Range)excelApp.Selection).Areas;
                for (int ai = 1; ai < areas.Count + 1; ai++)
                {
                    Excel.Range selectedRange = areas[ai];
                    Excel.Range entireRange   = selectedRange.EntireRow;
                    entireRange.Select();

                    for (int i = 1; i < entireRange.Rows.Count + 1; i++)
                    {
                        Recipient recipient = new Recipient();
                        recipient.Company = (string)((Excel.Range)entireRange.Rows.Cells.get_Item(i, 2)).Text;
                        recipient.Name    = (string)((Excel.Range)entireRange.Rows.Cells.get_Item(i, 3)).Text;

                        string tmpEmail = (string)((Excel.Range)entireRange.Rows.Cells.get_Item(i, 8)).Text;
                        if (tmpEmail != null && tmpEmail.Length > 0)
                        {
                            recipient.EmailAddress = tmpEmail;
                        }
                        slSend.AddRecipient(recipient);
                    }
                }
                // END

//				Excel.Range entireRange = selectedRange.EntireRow;
//				entireRange.Select();
//
//				for (int i = 1; i < entireRange.Rows.Count + 1; i++)
//				{
//					Recipient recipient = new Recipient();
//					recipient.Company = (string)((Excel.Range)entireRange.Rows.Cells.get_Item(i,2)).Text;
//					recipient.Name = (string)((Excel.Range)entireRange.Rows.Cells.get_Item(i,3)).Text;
//
//					slSend.AddRecipient(recipient);
//				}

                slSend.Visible = true;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Beispiel #2
0
 private void button2_Click(object sender, EventArgs e)
 {
     try
     {
         copyAlltoClipboard();
         Microsoft.Office.Interop.Excel.Application xlexcel;
         Microsoft.Office.Interop.Excel.Workbook    xlWorkBook;
         Microsoft.Office.Interop.Excel.Worksheet   xlWorkSheet;
         object misValue = System.Reflection.Missing.Value;
         xlexcel         = new Excel.Application();
         xlexcel.Visible = true;
         xlWorkBook      = xlexcel.Workbooks.Add(misValue);
         xlWorkSheet     = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
         Excel.Range CR = (Excel.Range)xlWorkSheet.Cells[1, 1];
         CR.Select();
         xlWorkSheet.PasteSpecial(CR, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, true);
     }
     catch (Exception ex)
     {
         MessageBox.Show("Install MS Excel first and then try");
     }
 }
Beispiel #3
0
        private void DoGenerateReportsPersonByPerson(
            string gardenName, string className, GrowUpReportDataAccess.ReportGroupByStudent grouping,
            Excel._Worksheet templateSheet, ReportTemplate reportTemplate, DateTime beginDate, DateTime endDate)
        {
            foreach (var groupByStudent in grouping.GetCollection())
            {
                var stuName = groupByStudent.Value.Key;
                var lastSheet = m_objSheets.get_Item(m_objSheets.Count);
                templateSheet.Copy(m_objOpt, lastSheet);
                m_objSheet = (Excel._Worksheet)m_objSheets.get_Item(m_objSheets.Count);
                m_objSheet.Name = stuName;
                m_objRange = m_objSheet.get_Range("A1", m_objOpt);
                m_objRange.Value = string.Format("{0}({1}){2} {3} \"快乐成长\"记录情况汇总", gardenName, className, groupByStudent.Key, stuName);

                int offset = 0;
                foreach (var groupByCategory in groupByStudent.Value.Value.GetCollection())
                {
                    int k = 0;
                    var category = groupByCategory.Key;
                    var reportDateList = groupByCategory.Value.GetAllReports().ToList();
                    for (int i = 0; i < reportDateList.Count; i++)
                    {
                        var report = reportDateList[i];
                        if (!string.IsNullOrEmpty(report.Item))
                        {
                            if (k >= 1)
                            {
                                offset++;
                                //向上插入一行
                                m_objSheet.get_Range((Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column], (Excel.Range)m_objSheet.Cells[m_objRange.Row + 1, m_objRange.Column]).EntireRow.Insert(Excel.XlDirection.xlDown);
                            }

                            string column = string.Empty;
                            string row = string.Empty;
                            var range = reportTemplate.GetPos(string.Format("{0}_Item", category), ReportTemplate.ReportType.Report1);
                            if (!string.IsNullOrEmpty(range))
                            {
                                column = range[0].ToString();
                                row = range.Substring(1);
                                m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
                                m_objRange.Value = report.Item;
                            }
                            range = reportTemplate.GetPos(string.Format("{0}_Time", category), ReportTemplate.ReportType.Report1);
                            if (!string.IsNullOrEmpty(range))
                            {
                                column = range[0].ToString();
                                row = range.Substring(1);
                                m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
                                m_objRange.Value = reportDateList[i].Date.ToString("yyyy年MM月");
                            }
                            range = reportTemplate.GetPos(string.Format("{0}_Desc", category), ReportTemplate.ReportType.Report1);
                            if (!string.IsNullOrEmpty(range))
                            {
                                column = range[0].ToString();
                                row = range.Substring(1);
                                m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);
                                m_objRange.Value = report.Content;
                            }
                            range = reportTemplate.GetPos(string.Format("{0}_Pic", category), ReportTemplate.ReportType.Report1);
                            if (!string.IsNullOrEmpty(range))
                            {
                                column = range[0].ToString();
                                row = range.Substring(1);
                                m_objRange = m_objSheet.get_Range(string.Format("{0}{1}", column, Convert.ToInt32(row) + offset), m_objOpt);

                                if (!string.IsNullOrEmpty(report.PicUrl) && !report.PicUrl.Contains("http"))
                                {
                                    m_objRange.Select();
                                    float PicLeft, PicTop;
                                    PicLeft = Convert.ToSingle(m_objRange.Left);
                                    PicTop = Convert.ToSingle(m_objRange.Top);

                                    var fileName = AppDomain.CurrentDomain.BaseDirectory + report.PicUrl;
                                    if (!File.Exists(fileName))
                                    {
                                        if (!string.IsNullOrEmpty(report.RawUrl) && report.RawUrl.Contains("http"))
                                        {
                                            var buffer = new WebClient().DownloadData(report.RawUrl);
                                            using (var stream = new MemoryStream(buffer))
                                            {
                                                using (var img = Image.FromStream(stream))
                                                {
                                                    img.Save(fileName);
                                                }
                                            }
                                        }
                                    }

                                    if (File.Exists(fileName))
                                    {
                                        m_objSheet.Shapes.AddPicture(fileName,
                                            Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, PicLeft, PicTop, (float)m_objRange.Width, (float)m_objRange.Height);
                                    }
                                }
                                else
                                    m_objRange.Value = report.PicUrl;
                            }

                            if (i == reportDateList.Count - 1)
                            {
                                if (k >= 1)
                                {
                                    //将各级别单元格合并
                                    var currentRow = m_objRange.Row;
                                    MergeLevel(currentRow, "C");
                                    MergeLevel(currentRow, "H");
                                    MergeLevel(currentRow, "B");
                                    MergeLevel(currentRow, "A");
                                }
                            }
                            k++;
                        }
                    }
                }
               
                m_objSheet.Range[string.Format("A2:H{0}", 38 + offset)].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
                m_objSheet.get_Range(string.Format("A{0}", 39 + offset), m_objOpt).Value = string.Format("汇总周期:{0}---{1}", beginDate.ToString("yyyy.MM.dd"), endDate.ToString("yyyy.MM.dd"));
            }
            templateSheet.Delete();
        }