/// <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); }
//#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); }