Beispiel #1
0
        private static List <detallePublicado> GenerarXlsHojaHtmZip(string hoja, string archivoDestino, string rango)
        {
            List <detallePublicado> resultado = new List <detallePublicado>();

            //copiarValoresHoja(hoja);

            Excel.Range     xlSourceRange = ((Excel.Worksheet)xlWorkBook.Application.ActiveWorkbook.Sheets[hoja]).get_Range("E1", "F1");
            Excel.Hyperlink enlace        = (Excel.Hyperlink)xlSourceRange.Hyperlinks.Add(xlSourceRange, Path.GetFileNameWithoutExtension(archivoDestino) + ".zip", Type.Missing, Type.Missing, "Descargar Archivo");

            ((Excel.Workbook)xlWorkBook.Application.ActiveWorkbook).PublishObjects.Add(Excel.XlSourceType.xlSourceRange,
                                                                                       Path.Combine(Path.GetDirectoryName(archivoDestino), Path.GetFileNameWithoutExtension(archivoDestino) + ".htm"), hoja, rango,
                                                                                       Excel.XlHtmlType.xlHtmlStatic, "1-10225", Type.Missing).Publish(true);
            if (File.Exists(Path.Combine(Path.GetDirectoryName(archivoDestino), Path.GetFileNameWithoutExtension(archivoDestino) + ".htm")))
            {
                resultado.Add(new detallePublicado(Path.GetFileNameWithoutExtension(archivoDestino) + ".htm", "Local", "OK"));
            }

            if (File.Exists(Path.Combine(Path.GetDirectoryName(archivoDestino), archivoDestino)))
            {
                UtilZip.CreaArchivoZip(Path.Combine(Path.GetDirectoryName(archivoDestino), archivoDestino), Path.Combine(Path.GetDirectoryName(archivoDestino), Path.GetFileNameWithoutExtension(archivoDestino) + ".zip"));
                if (File.Exists(Path.Combine(Path.GetDirectoryName(archivoDestino), Path.GetFileNameWithoutExtension(archivoDestino) + ".zip")))
                {
                    resultado.Add(new detallePublicado(Path.GetFileNameWithoutExtension(archivoDestino) + ".zip", "Local", "OK"));
                    //resultado.Add(new detallePublicado(Path.GetFileName(archivoDestino),"Local", "OK"));
                }
            }
            return(resultado);
        }
Beispiel #2
0
        private ICollection <FileInfo> GetHyperLinks(Excel.Hyperlink link)
        {
            List <FileInfo> attachments = new List <FileInfo>();

            if (link.Address != null)
            {
                FileInfo file = UriToFile(link.Address);
                if (file != null)
                {
                    attachments.Add(file);
                }
            }
            return(attachments);
        }
Beispiel #3
0
        private void ExcelWorkbook_SheetFollowHyperlink(object Sh, Excel.Hyperlink Target)
        {
            try
            {
                if ("Drop Down Values".Equals(Target.Range.Text))
                {
                    Excel._Worksheet ws         = (Excel._Worksheet)Sh;
                    int              rowNumber  = Target.Range.Row;
                    int              fnColIndex = ExcelHelper.ConvertColumnNameToColumnIndex("FieldName", ws);
                    Excel.Range      r          = ws.Cells[rowNumber, fnColIndex] as Excel.Range;
                    Excel.Workbook   workbook   = (Excel.Workbook)ws.Parent;
                    Excel.Sheets     oSheets    = workbook.Worksheets;
                    Excel._Worksheet oSheet     = HelperUtil.GetSheetNameFromGroupOfSheets(GlobalMembers.InstanceGlobalMembers.DropDownSheetName, oSheets);

                    int targetSheetRow = oSheet.Cells.Find("*", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, Microsoft.Office.Interop.Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlPrevious, false, System.Reflection.Missing.Value, System.Reflection.Missing.Value).Row;
                    targetSheetRow++;
                    fnColIndex = ExcelHelper.ConvertColumnNameToColumnIndex("Field Id", oSheet);
                    oSheet.Cells[targetSheetRow, fnColIndex] = r.Text;
                }
            }
            catch (Exception ex)
            { throw (ex); }
        }
        public void FillHyperLinks(string _bbid, System.Data.DataTable _mxData, System.Data.DataTable _cellData, DataSet _hmcData)
        {
            int lfw = _hmcData.Tables["COL_DEFINE"].Rows.Count;
            int hfw = _hmcData.Tables["ROW_DEFINE"].Rows.Count;

            string _url = Report_WebAppAddress + "/Dialog/ShowReportDetial.aspx";

            //处理行列表
            foreach (DataRow _dr in _mxData.Rows)
            {
                int hx = int.Parse(_dr["HX"].ToString());
                int lx = int.Parse(_dr["LX"].ToString());
                if (hx > 0 && hx <= hfw && lx > 0 && lx <= lfw)
                {
                    string _url2 = string.Format(@"{0}?HLX={1},{2}&REPORTID={3}", _url, hx, lx, _bbid);
                    Microsoft.Office.Interop.Excel.Hyperlink _link = (Microsoft.Office.Interop.Excel.Hyperlink)m_oSheet.Hyperlinks.Add(m_oSheet.Cells[hx + BaseRow - 1, lx + BaseCol], _url2, m_oMissing, m_oMissing, m_oMissing);
                    //System.Runtime.InteropServices.Marshal.ReleaseComObject (_link);
                }
            }

            //处理特别CELL表
            foreach (DataRow _dr in _cellData.Rows)
            {
                int _rownum = int.Parse(_dr["RPHX"].ToString());
                int _colnum = int.Parse(_dr["RPLX"].ToString());

                int _linkrownum = int.Parse(_dr["BBHX"].ToString());
                int _linkcolnum = int.Parse(_dr["BBLX"].ToString());

                string sfmx = _dr.IsNull("HASMX")? "0" : _dr["HASMX"].ToString();
                if (sfmx == "1")
                {
                    string _url2 = string.Format(@"{0}?HLX={1},{2}&REPORTID={3}", _url, _linkrownum, _linkcolnum, _bbid);
                    m_oSheet.Hyperlinks.Add(m_oSheet.Cells[_rownum, _colnum], _url2, m_oMissing, m_oMissing, m_oMissing);
                }
            }
        }
Beispiel #5
0
        internal void refreshChart(object sender, Excel.Hyperlink e)
        {
            string mdl = ((Excel.Worksheet) this.Application.ActiveSheet).get_Range(e.Name).Value2.ToString();

            Excel.ChartObjects col = (Excel.ChartObjects)((Excel.Worksheet) this.Application.ActiveSheet).ChartObjects();

            int tmp1 = col.Count;

            for (int i = 1; i <= col.Count; i++)
            {
                Excel.ChartObject co = (Excel.ChartObject)col.Item(i);
                if (co.Name.Substring(0, 5) == "Model")
                {
                    if (co.Name == "Model " + mdl)
                    {
                        co.Visible = true;
                    }
                    else
                    {
                        co.Visible = false;
                    }
                }
            }
        }
Beispiel #6
0
        public void WriteResultsTable(int n, bool top, bool isRead)
        {
            // put the model results data into an object array
            //object[,] amodel ;

            Excel.Range header = BottomCell().get_Offset(2, 0);
            string      start  = header.get_Address(1, 1, Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing);

            ArrayList lst   = this.Source.Models.ModelSort();
            int       first = top ? 0 : Math.Min(n, lst.Count);
            int       last  = top ? Math.Min(n, lst.Count) : lst.Count;


            if (first == last)
            {
                return;
            }

            // write the column headers
            //{ ModelNo, ModelValid, IVNames, IVCoefficients, IVses, IVpVals, R2, adjR2, pVal, RMSError, Residual, AIC, Formula };
            foreach (Utilities.Constants.ModelOutputColumns col in System.Enum.GetValues(typeof(Utilities.Constants.ModelOutputColumns)))
            {
                string label = Globals.ThisAddIn.rsc.GetString("label" + col.ToString()) ?? col.ToString();
                header.Value2 = label;
                header        = header.get_Offset(0, 1);
            }

            object[,] row;
            int rowct = 1;

            for (int j = first; j < last; j++)
            {
                Utilities.Model mdl   = this.Source.Models.Item(j);
                int             varct = mdl.VariableNames.Length;
                rowct += varct + 1;
                Excel.Range target = BottomCell().get_Offset(1, 0).get_Resize(varct + 1, 13);

                row        = new object[1, 13];
                row[0, 0]  = mdl.ModelNumber;
                row[0, 1]  = mdl.Valid();
                row[0, 6]  = mdl.R2();
                row[0, 7]  = mdl.AdjustedR2();
                row[0, 8]  = mdl.ModelPValue();
                row[0, 9]  = mdl.RMSError;
                row[0, 10] = mdl.ResidualSS();
                row[0, 11] = mdl.AICFormula();
                row[0, 12] = mdl.Formula();

                target.get_Resize(1, 13).Value2 = row;

                if (top)
                {
                    Excel.Hyperlink hl = (Excel.Hyperlink)WS.Hyperlinks.Add(target.get_Resize(1, 1), "",
                                                                            target.get_Resize(1, 1).get_Address(1, 1, Excel.XlReferenceStyle.xlA1, System.Type.Missing, System.Type.Missing), System.Type.Missing, System.Type.Missing);
                    hl.ScreenTip = "Graph model " + mdl.ModelNumber.ToString();
                }

                row = new object[varct + 1, 4];

                for (int i = 0; i < varct; i++)
                {
                    row[i, 0] = mdl.VariableNames[i];
                    row[i, 1] = mdl.Coefficients[i];
                    row[i, 2] = mdl.StandardErrors()[i];
                    row[i, 3] = mdl.PValues()[i];
                }
                //row[varct, 0] = "(Intercept)";
                row[varct, 1] = mdl.Coefficients[varct];

                target.get_Offset(0, 2).get_Resize(varct + 1, 4).Value2 = row;

                // put a box around
                target.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, System.Type.Missing);

                // highlight the best model
                if (Source.BestModel() != null)
                {
                    if (mdl.ModelNumber == Source.BestModel().ModelNumber)
                    {
                        if (mdl.Valid())
                        {
                            target.Font.Color = 0x0000AA;
                            //target.Style = Globals.ThisAddIn.rsc.GetString("bestModelStyle");
                            target.Font.Bold = true;
                        }
                        else
                        {
                            target.Font.Color = 0x0000AA;
                            //target.Style = "Bad";
                            target.Font.Bold = true;
                        }
                    }
                }
            }

            Excel.Range      tbl = WS.get_Range(start, System.Type.Missing).get_Resize(rowct, 13);
            Excel.ListObject LO  = WS.ListObjects.Add(Excel.XlListObjectSourceType.xlSrcRange, tbl, System.Type.Missing, Excel.XlYesNoGuess.xlYes, System.Type.Missing);
            LO.TableStyle = "TableStyleLight8";
            LO.ShowTableStyleRowStripes = false;

            ((Excel.Range)LO.Range[0, 4]).EntireColumn.Hidden  = true;
            ((Excel.Range)LO.Range[0, 5]).EntireColumn.Hidden  = true;
            ((Excel.Range)LO.Range[0, 10]).EntireColumn.Hidden = true;
            ((Excel.Range)LO.Range[0, 11]).EntireColumn.Hidden = true;
            ((Excel.Range)LO.Range[0, 12]).EntireColumn.Hidden = true;

            ((Excel.Range)LO.Range[0, 6]).EntireColumn.NumberFormat = "0.0000";
            ((Excel.Range)LO.Range[0, 7]).EntireColumn.NumberFormat = "0.0000";
            ((Excel.Range)LO.Range[0, 8]).EntireColumn.NumberFormat = "0.0000";
            ((Excel.Range)LO.Range[0, 9]).EntireColumn.NumberFormat = "0.0000";
        }