/// <summary>
///
/// </summary>
/// <param name="streamToWriteOutputTo"></param>
/// <param name="sheets"></param>
/// <param name="format"></param>
public static void ExportList(Stream streamToWriteOutputTo, List <ExportPart> sheets, FileFormat format)
{
sheets = FixSheetNames(sheets);
IWorkbookSet workBook = Factory.GetWorkbookSet();
int maxRows = (format == FileFormat.OpenXMLWorkbook || format == FileFormat.OpenXMLWorkbookMacroEnabled) ? 1048575 : 65536;
string tempSheetName = null;
IWorkbook iBook = workBook.Workbooks.Add();
try {
// There's allways 1 sheet when creating a new book.
// If a new sheet has the same name, an exception will be thrown.
// So, this sheet is renamed, and in the end, deleted. (we can't delete it here since there must be at least 1 sheet in a book)
if (iBook.Sheets["Sheet1"] != null)
{
tempSheetName = Guid.NewGuid().ToString().Substring(0, 31);
iBook.Sheets["Sheet1"].Name = tempSheetName;
}
} catch {
}
IColors colorPalett = iBook.Colors;
Hashtable colorHash = new Hashtable();
if (format == FileFormat.OpenXMLWorkbook)
{
colorPalett = null;
colorHash = null;
}
int colorPalettIndex = 0;
if (colorPalett != null)
{
colorPalett[colorPalettIndex++] = Color.Black;
colorPalett[colorPalettIndex++] = Color.White;
colorHash[Color.Black.ToArgb()] = null;
colorHash[Color.White.ToArgb()] = null;
}
List <DataValidationInfo> dataValidationInfos = new List <DataValidationInfo>();
foreach (ExportPart part in sheets)
{
IWorksheet iSheet = iBook.Worksheets.Add();
iSheet.Name = part.SheetName;
IRange cells = iSheet.Cells;
int cellIndex = 0;
int rowIndex = AddExcelHeaders(cells, part, colorHash, colorPalett, ref colorPalettIndex);
int startRowIndex = rowIndex;
cellIndex = 0;
int sheetCount = 1;
int totalItemIndex = 0;
Hashtable rowSpans = new Hashtable();
if (part.DataValidationLists.Count > 0)
{
foreach (ExportPart.DataValidationList dvl in part.DataValidationLists)
{
dataValidationInfos.Add(new DataValidationInfo()
{
DVL = dvl, Sheet = iSheet, StartRow = rowIndex
});
}
}
foreach (CellDataList data in part.Data)
{
foreach (CellData value in data)
{
if (rowSpans.CheckIfRowSpanAndDecrease(cellIndex))
{
continue;
}
if (value.URL != null)
{
cells[rowIndex, cellIndex].Hyperlinks.Add(cells[rowIndex, cellIndex], value.URL, "", value.URLTip, value.Value);
}
if (value.TextFormat)
{
cells[rowIndex, cellIndex].NumberFormat = "@";
}
if (!string.IsNullOrEmpty(value.NumberFormat))
{
cells[rowIndex, cellIndex].NumberFormat = value.NumberFormat;
}
if (value.TextDirectionDegrees > 0)
{
cells[rowIndex, cellIndex].Orientation = value.TextDirectionDegrees;
}
if (value.BackgroundColor != Color.Transparent)
{
if (colorHash != null && !colorHash.ContainsKey(value.BackgroundColor.ToArgb()) && colorPalettIndex < 56)
{
colorHash[value.BackgroundColor.ToArgb()] = null;
colorPalett[colorPalettIndex++] = MakePalettValid(value.BackgroundColor);
}
cells[rowIndex, cellIndex].Interior.Color = value.BackgroundColor;
//value.Value = "Color.FromArgb( {0}, {1}, {2} )".FillBlanks( value.BackgroundColor.R, value.BackgroundColor.G, value.BackgroundColor.B );
}
if (value.FontColor != SystemColors.WindowText)
{
if (colorHash != null && !colorHash.ContainsKey(value.FontColor.ToArgb()) && colorPalettIndex < 56)
{
colorHash[value.FontColor.ToArgb()] = null;
colorPalett[colorPalettIndex++] = MakePalettValid(value.FontColor);
}
cells[rowIndex, cellIndex].Font.Color = value.FontColor;
}
if (value.Bold)
{
cells[rowIndex, cellIndex].Font.Bold = value.Bold;
}
if (value.Underline.HasValue)
{
cells[rowIndex, cellIndex].Font.Underline = value.Underline.Value ? UnderlineStyle.Single : UnderlineStyle.None;
}
if (value.Pattern)
{
cells[rowIndex, cellIndex].Interior.Pattern = Pattern.Down;
cells[rowIndex, cellIndex].Interior.PatternColor = Color.White;
}
if (value.PatternColor != Color.Empty)
{
if (colorHash != null && !colorHash.ContainsKey(value.PatternColor.ToArgb()) && colorPalettIndex < 56)
{
colorHash[value.PatternColor.ToArgb()] = null;
colorPalett[colorPalettIndex++] = MakePalettValid(value.PatternColor);
}
cells[rowIndex, cellIndex].Interior.PatternColor = value.PatternColor;
}
cells[rowIndex, cellIndex].WrapText = value.WrapText;
if (value.LeftBorder)
{
cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeLeft].Weight = BorderWeight.Thin;
}
if (value.LeftBorderColor != Color.Transparent)
{
if (colorHash != null && !colorHash.ContainsKey(value.LeftBorderColor.ToArgb()) && colorPalettIndex < 56)
{
colorHash[value.LeftBorderColor.ToArgb()] = null;
colorPalett[colorPalettIndex++] = MakePalettValid(value.LeftBorderColor);
}
cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeLeft].Color = value.LeftBorderColor;
}
if (value.RightBorder)
{
cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeRight].Weight = BorderWeight.Thin;
}
if (value.RightBorderColor != Color.Transparent)
{
if (colorHash != null && !colorHash.ContainsKey(value.RightBorderColor.ToArgb()) && colorPalettIndex < 56)
{
colorHash[value.RightBorderColor.ToArgb()] = null;
colorPalett[colorPalettIndex++] = MakePalettValid(value.RightBorderColor);
}
cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeRight].Color = value.RightBorderColor;
}
if (value.TopBorder)
{
cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeTop].Weight = BorderWeight.Thin;
}
if (value.TopBorderColor != Color.Transparent)
{
if (colorHash != null && !colorHash.ContainsKey(value.TopBorderColor.ToArgb()) && colorPalettIndex < 56)
{
colorHash[value.TopBorderColor.ToArgb()] = null;
colorPalett[colorPalettIndex++] = MakePalettValid(value.TopBorderColor);
}
cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeTop].Color = value.TopBorderColor;
}
if (value.BottomBorder)
{
cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeBottom].Weight = BorderWeight.Thin;
}
if (value.BottomBorderColor != Color.Transparent)
{
if (colorHash != null && !colorHash.ContainsKey(value.BottomBorderColor.ToArgb()) && colorPalettIndex < 56)
{
colorHash[value.BottomBorderColor.ToArgb()] = null;
colorPalett[colorPalettIndex++] = MakePalettValid(value.BottomBorderColor);
}
cells[rowIndex, cellIndex].Borders[BordersIndex.EdgeBottom].Color = value.BottomBorderColor;
}
if (!string.IsNullOrEmpty(value.Comment))
{
cells[rowIndex, cellIndex].AddComment(value.Comment).Visible = false;
}
if (value.CenterInCell)
{
cells[rowIndex, cellIndex].Style.HorizontalAlignment = HAlign.Center;
cells[rowIndex, cellIndex].Style.VerticalAlignment = VAlign.Center;
}
if (value.VerticalAlignment != VAlign.Bottom)
{
cells[rowIndex, cellIndex].Style.VerticalAlignment = value.VerticalAlignment;
}
if (value.HorizontalAlignment != HAlign.General)
{
cells[rowIndex, cellIndex].Style.HorizontalAlignment = value.HorizontalAlignment;
}
if (value.FontSize > 0f)
{
cells[rowIndex, cellIndex].Font.Size = value.FontSize;
}
try {
cells[rowIndex, cellIndex].Value = value.Value;
} catch (ArgumentException ex) {
cells[rowIndex, cellIndex].Value = "Error setting value with " + (value.Value != null ? value.Value.Length + " characters" : "null") + ": " + ex.Message + " Try exporting as text instead...";
cells[rowIndex, cellIndex].Font.Bold = true;
} catch (Exception ex) {
cells[rowIndex, cellIndex].Value = "Error setting value: " + ex.Message;
cells[rowIndex, cellIndex].Font.Bold = true;
}
//cells[ rowIndex, cellIndex ].ShrinkToFit = true; // krymper bara texten...
if (value.ColumnWidth > 0)
{
cells[rowIndex, cellIndex].ColumnWidth = value.ColumnWidth;
}
if (value.RowHeight > 0)
{
cells[rowIndex, cellIndex].RowHeight = value.RowHeight;
}
if (value.ColSpan > 1)
{
cells[rowIndex, cellIndex, rowIndex, cellIndex + value.ColSpan - 1].Merge();
//cells[ rowIndex, cellIndex + value.ColSpan - 1 ].Merge();
}
if (value.RowSpan > 1)
{
cells[rowIndex, cellIndex, rowIndex + value.RowSpan - 1, cellIndex].Merge();
rowSpans[cellIndex] = value.RowSpan;
}
if (value.ColSpan > 1)
{
cellIndex += value.ColSpan;
}
else
{
cellIndex++;
}
}
cellIndex = 0;
rowIndex++;
//if( rowIndex >= ExportPart.MaxNumberOfExcelSheetRows && (part.Data.Count > (totalItemIndex + 1)) ) {
if (rowIndex >= maxRows && (part.Data.Count > (totalItemIndex + 1)))
{
if (part.AutoFitColumnWidths)
{
try {
string colRef = "A:" + (char)('A' + (part.Headers.Count - 1));
cells[colRef].AutoFitColumns();
//cells[ 0, 1, rowIndex, 1 ].Rows.AutoFit();
//cells[ 0, 1, rowIndex, 1 ].AutoFit();
//double oldWidth = cells[ 0, 1, rowIndex, 1 ].ColumnWidth;
//try {
// cells[ 0, 1, rowIndex, 1 ].ColumnWidth += 10;
//} catch {
// cells[ 0, 1, rowIndex, 1 ].ColumnWidth = oldWidth;
//}
} catch {
}
}
sheetCount++;
iSheet = iBook.Worksheets.Add();
iSheet.Name = part.SheetName + " (" + sheetCount + ")";
cells = iSheet.Cells;
rowIndex = AddExcelHeaders(cells, part, colorHash, colorPalett, ref colorPalettIndex);
rowSpans = new Hashtable();
if (part.DataValidationLists.Count > 0)
{
foreach (ExportPart.DataValidationList dvl in part.DataValidationLists)
{
dataValidationInfos.Add(new DataValidationInfo()
{
DVL = dvl, Sheet = iSheet, StartRow = rowIndex
});
}
}
}
totalItemIndex++;
}
if (part.AutoFitColumnWidths)
{
try {
string colRef = "A:" + (char)('A' + (part.Headers.Count - 1));
cells[colRef].AutoFitColumns();
} catch {
}
}
}
AddDataValidationLists(iBook, sheets);
foreach (DataValidationInfo dvi in dataValidationInfos)
{
foreach (int dvlColumn in dvi.DVL.Columns)
{
if (dvi.DVL.Values.Count > 0)
{
dvi.Sheet.Cells[dvi.StartRow, dvlColumn, maxRows, dvlColumn].Validation.Add(ValidationType.List, ValidationAlertStyle.Warning, ValidationOperator.Equal, "=" + dvi.DVL.Name, null);
}
}
}
if (tempSheetName != null)
{
try {
if (iBook.Sheets[tempSheetName] != null)
{
iBook.Sheets[tempSheetName].Delete();
}
} catch {
}
}
if (iBook.Sheets.Count > 0)
{
iBook.Sheets[0].Select();
}
iBook.SaveToStream(streamToWriteOutputTo, format);
streamToWriteOutputTo.Flush();
iBook = null;
workBook.Dispose();
workBook = null;
GC.Collect(3);
}
//#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);
}
