Esempio n. 1
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="streamToWriteOutputTo"></param>
        /// <param name="sheet"></param>
        public static void ExportList(Stream streamToWriteOutputTo, ExportPart sheet)
        {
            List <ExportPart> tmp = new List <ExportPart>(1);

            tmp.Add(sheet);
            ExportList(streamToWriteOutputTo, tmp);
        }
Esempio n. 2
0
        //#region public static void ExportSearchResult( Stream streamToWriteOutputTo, StationList stations, List<IStationProgramParameterSearchFilter> searchConditions )
        ///// <summary>
        /////
        ///// </summary>
        ///// <param name="streamToWriteOutputTo"></param>
        ///// <param name="stations"></param>
        ///// <param name="searchConditions"></param>
        //public static void ExportSearchResult( Stream streamToWriteOutputTo, StationList stations, List<IStationProgramParameterSearchFilter> searchConditions ) {
        //    string serverURL = ServerURL;
        //    IWorkbookSet workBook = Factory.GetWorkbookSet();
        //    IWorkbook iBook = workBook.Workbooks.Add();
        //    IWorksheet iSheet = iBook.Worksheets.Add();
        //    iSheet.Name = "Station-Program-Parameter";
        //    IRange cells = iSheet.Cells;
        //    int cellIndex = 0;
        //    cells[ 0, cellIndex++ ].Value = "Namn";

        //    cells[0, cellIndex++].Value = "EU_CD";
        //    cells[ 0, cellIndex++ ].Value = "Vattenkategori";
        //    cells[ 0, cellIndex++ ].Value = "Distrikt";
        //    cells[ 0, cellIndex++ ].Value = "Huvudavrinningsområde";
        //    cells[ 0, cellIndex++ ].Value = "Län";
        //    cells[ 0, cellIndex++ ].Value = "Kommun";

        //    cells[ 0, cellIndex++ ].Value = "Program";
        //    cells[ 0, cellIndex++ ].Value = "Datum";
        //    cells[ 0, cellIndex++ ].Value = "Frekvens";
        //    cells[ 0, cellIndex++ ].Value = "Motivering";
        //    cells[ 0, 0, 0, cellIndex ].Font.Bold = true;
        //    int rowIndex = 1;
        //    if( stations != null ) {
        //        for( int i = 0; i < stations.Count; i++ ) {
        //            Station station = stations[ i ];
        //            StationProgramParameterClassificationList sppcl = SearchControl.GetLatestStationProgramParameterClassificaitons( station, searchConditions );
        //            //cells[ rowIndex, 0 ].Value = station.DisplayName;
        //            cellIndex = 0;
        //            if( serverURL != null ) {
        //                cells[ rowIndex, cellIndex++ ].Hyperlinks.Add( cells[ rowIndex, 0 ], serverURL + "Stations.aspx?" + Constants.STATION_EUID + "=" + station.EUID, "", "Klicka här för att öppna stationen i VISS", station.DisplayName );
        //            } else {
        //                cells[ rowIndex, cellIndex++ ].Value = station.DisplayName;
        //            }
        //            cells[ rowIndex, cellIndex++ ].Value = station.EUID;
        //            cells[ rowIndex, cellIndex++ ].Value = station.WaterType != null ? station.WaterType.TranslatedName : "";
        //            cells[ rowIndex, cellIndex++ ].Value = station.Area != null ? station.Area.TranslatedName : "";
        //            cells[ rowIndex, cellIndex++ ].Value = station.Basin != null ? station.Basin.TranslatedName : "";
        //            cells[ rowIndex, cellIndex++ ].Value = station.Counties != null ? station.Counties.ToString( ", " ) : "";
        //            cells[ rowIndex, cellIndex++ ].Value = station.Municipality != null ? station.Municipality.Name : "";
        //            if( sppcl.Count > 1 ) {
        //                cells[ rowIndex, 0, rowIndex + sppcl.Count - 1, 0 ].Merge();
        //                cells[ rowIndex, 0, rowIndex + sppcl.Count - 1, 0 ].VerticalAlignment = VAlign.Center;
        //            }
        //            for( int j = 0; j < sppcl.Count; j++ ) {
        //                if( sppcl[ j ].Program != null ) {
        //                    if( serverURL != null ) {
        //                        cells[ rowIndex, cellIndex + 1 ].Hyperlinks.Add( cells[ rowIndex, 1 ], serverURL + "Programs.aspx?" + Constants.PROGRAM_ID + "=" + sppcl[ j ].ProgramID, "", "Klicka här för att öppna programmet i VISS", sppcl[ j ].Program.TranslatedName );
        //                    } else {
        //                        cells[ rowIndex, cellIndex + 1 ].Value = sppcl[ j ].Program.TranslatedName;
        //                    }
        //                } else {
        //                    cells[ rowIndex, cellIndex + 1 ].Value = "";
        //                }
        //                cells[ rowIndex, cellIndex + 2 ].Value = DataFactory.Instance.FormatDate( sppcl[ j ].ClassificationDate );
        //                cells[ rowIndex, cellIndex + 3 ].Value = sppcl[ j ].Frequency != null ? sppcl[ j ].Frequency.TranslatedName : "";
        //                cells[ rowIndex, cellIndex + 4 ].Value = sppcl[ j ].ClassificationParameterText ?? "";
        //                rowIndex++;
        //            }
        //        }
        //    }
        //    try {
        //        if( iBook.Sheets[ "Sheet1" ] != null ) {
        //            iBook.Sheets[ "Sheet1" ].Delete();
        //        }
        //    } catch {
        //    }
        //    iBook.SaveToStream( streamToWriteOutputTo, FileFormat.Excel8 );
        //}
        //#endregion

        #region private static int AddExcelHeaders( IRange cells, ExportPart part, Hashtable colorHash, IColors colorPalett, ref int colorPalettIndex )
        /// <summary>
        ///
        /// </summary>
        /// <param name="cells"></param>
        /// <param name="part"></param>
        /// <param name="colorHash"></param>
        /// <param name="colorPalett"></param>
        /// <param name="colorPalettIndex"></param>
        /// <returns>Rowindex for the next row that does not contain data</returns>
        private static int AddExcelHeaders(IRange cells, ExportPart part, Hashtable colorHash, IColors colorPalett, ref int colorPalettIndex)
        {
            int rowIndex = 0;
            List <CellDataList> headers = new List <CellDataList>();

            headers.Add(part.Headers);
            if (part.AdditionalHeaders != null && part.AdditionalHeaders.Count > 0)
            {
                foreach (CellDataList list in part.AdditionalHeaders)
                {
                    headers.Add(list);
                }
            }
            Hashtable rowSpans = new Hashtable();

            foreach (CellDataList headerList in headers)
            {
                int cellIndex = 0;
                foreach (CellData data in headerList)
                {
                    if (rowSpans.CheckIfRowSpanAndDecrease(cellIndex))
                    {
                        continue;
                    }
                    if (data.URL != null)
                    {
                        cells[rowIndex, cellIndex].Hyperlinks.Add(cells[rowIndex, cellIndex], data.URL, "", data.URLTip, data.Value);
                    }
                    if (data.TextFormat)
                    {
                        cells[rowIndex, cellIndex].NumberFormat = "@";
                    }
                    if (data.TextDirectionDegrees > 0)
                    {
                        cells[rowIndex, cellIndex].Orientation = data.TextDirectionDegrees;
                    }
                    if (data.BackgroundColor != Color.Transparent)
                    {
                        //SpreadsheetGear.Drawing.Color c = SpreadsheetGear.Drawing.Color.FromArgb( data.BackgroundColor.A, data.BackgroundColor.R, data.BackgroundColor.G, data.BackgroundColor.B );
                        //cells[ rowIndex, cellIndex ].Interior.Color = c;
                        if (colorHash != null && !colorHash.ContainsKey(data.BackgroundColor.ToArgb()) && colorPalettIndex < 56)
                        {
                            colorHash[data.BackgroundColor.ToArgb()] = null;
                            colorPalett[colorPalettIndex++]          = MakePalettValid(data.BackgroundColor);
                        }
                        cells[rowIndex, cellIndex].Interior.Color = data.BackgroundColor;
                        //data.Value = "Color.FromArgb( {0}, {1}, {2} )".FillBlanks( data.BackgroundColor.R, data.BackgroundColor.G, data.BackgroundColor.B );
                    }
                    if (data.FontColor != SystemColors.WindowText)
                    {
                        if (colorHash != null && !colorHash.ContainsKey(data.FontColor.ToArgb()) && colorPalettIndex < 56)
                        {
                            colorHash[data.FontColor.ToArgb()] = null;
                            colorPalett[colorPalettIndex++]    = MakePalettValid(data.FontColor);
                        }
                        cells[rowIndex, cellIndex].Font.Color = data.FontColor;
                    }
                    if (rowIndex == 0 || data.Bold)
                    {
                        cells[rowIndex, cellIndex].Font.Bold = true;
                    }
                    if (data.Underline.HasValue)
                    {
                        cells[rowIndex, cellIndex].Font.Underline = data.Underline.Value ? UnderlineStyle.Single :UnderlineStyle.None;
                    }
                    if (data.CenterInCell)
                    {
                        cells[rowIndex, cellIndex].Style.HorizontalAlignment = HAlign.Center;
                        cells[rowIndex, cellIndex].Style.VerticalAlignment   = VAlign.Center;
                    }
                    if (data.FontSize > 0f)
                    {
                        cells[rowIndex, cellIndex].Font.Size = data.FontSize;
                    }
                    if (data.LeftBorder)
                    {
                        cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeLeft].Weight = BorderWeight.Thin;
                    }
                    if (data.LeftBorderColor != Color.Transparent)
                    {
                        if (colorHash != null && !colorHash.ContainsKey(data.LeftBorderColor.ToArgb()) && colorPalettIndex < 56)
                        {
                            colorHash[data.LeftBorderColor.ToArgb()] = null;
                            colorPalett[colorPalettIndex++]          = MakePalettValid(data.LeftBorderColor);
                        }
                        cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeLeft].Color = data.LeftBorderColor;
                    }
                    if (data.RightBorder)
                    {
                        cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeRight].Weight = BorderWeight.Thin;
                    }
                    if (data.RightBorderColor != Color.Transparent)
                    {
                        if (colorHash != null && !colorHash.ContainsKey(data.RightBorderColor.ToArgb()) && colorPalettIndex < 56)
                        {
                            colorHash[data.RightBorderColor.ToArgb()] = null;
                            colorPalett[colorPalettIndex++]           = MakePalettValid(data.RightBorderColor);
                        }
                        cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeRight].Color = data.RightBorderColor;
                    }
                    if (data.TopBorder)
                    {
                        cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeTop].Weight = BorderWeight.Thin;
                    }
                    if (data.TopBorderColor != Color.Transparent)
                    {
                        if (colorHash != null && !colorHash.ContainsKey(data.TopBorderColor.ToArgb()) && colorPalettIndex < 56)
                        {
                            colorHash[data.TopBorderColor.ToArgb()] = null;
                            colorPalett[colorPalettIndex++]         = MakePalettValid(data.TopBorderColor);
                        }
                        cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeTop].Color = data.TopBorderColor;
                    }
                    if (data.BottomBorder)
                    {
                        cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeBottom].Weight = BorderWeight.Thin;
                    }
                    if (data.BottomBorderColor != Color.Transparent)
                    {
                        if (colorHash != null && !colorHash.ContainsKey(data.BottomBorderColor.ToArgb()) && colorPalettIndex < 56)
                        {
                            colorHash[data.BottomBorderColor.ToArgb()] = null;
                            colorPalett[colorPalettIndex++]            = MakePalettValid(data.BottomBorderColor);
                        }
                        cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeBottom].Color = data.BottomBorderColor;
                    }
                    if (!string.IsNullOrEmpty(data.Comment))
                    {
                        cells[rowIndex, cellIndex].AddComment(data.Comment).Visible = false;
                    }
                    cells[rowIndex, cellIndex].WrapText = data.WrapText;
                    cells[rowIndex, cellIndex].Value    = data.Value;
                    if (data.ColumnWidth > 0)
                    {
                        cells[rowIndex, cellIndex].ColumnWidth = data.ColumnWidth;
                    }
                    if (data.RowHeight > 0)
                    {
                        cells[rowIndex, cellIndex].RowHeight = data.RowHeight;
                    }
                    if (data.ColSpan > 1)
                    {
                        cells[rowIndex, cellIndex, rowIndex, cellIndex + data.ColSpan - 1].Merge();
                    }
                    if (data.RowSpan > 1)
                    {
                        cells[rowIndex, cellIndex, rowIndex + data.RowSpan - 1, cellIndex].Merge();
                        rowSpans[cellIndex] = data.RowSpan;
                    }
                    if (data.ColSpan > 1)
                    {
                        cellIndex += data.ColSpan;
                    }
                    else
                    {
                        cellIndex++;
                    }
                }
                rowIndex++;
            }
            //cells[ 0, 0, 0, cellIndex ].Font.Bold = true;
            return(rowIndex);
        }