public static void ListAsFormula() { using (var stream = new MemoryStream()) { using (var xlsxWriter = new XlsxWriter(stream)) { var dataValidation = new XlsxDataValidation(validationType: XlsxDataValidation.ValidationType.List, formula1: "\"Lorem,Ipsum,Dolor\""); xlsxWriter.SetDefaultStyle(XlsxStyle.Default.With(new XlsxFill(Color.OldLace))).BeginWorksheet("Sheet 1") .BeginRow().AddDataValidation(dataValidation).Write().SkipColumns(1).AddDataValidation(1, 2, dataValidation).Write(repeatCount: 2); } using (var package = new ExcelPackage(stream)) { var dataValidation = package.Workbook.Worksheets[0].DataValidations[0] as ExcelDataValidationList; dataValidation.Should().NotBeNull(); dataValidation.ValidationType.Should().Be(ExcelDataValidationType.List); dataValidation.Address.Address.Should().Be("A1 C1:D1"); dataValidation.Formula.Values.Should().BeEquivalentTo("Lorem", "Ipsum", "Dolor"); } } }
/// <summary> /// Writes a ADODataReader to a Worksheet /// </summary> public static int WriteToWorksheet(ADOTabular.AdomdClientWrappers.AdomdDataReader reader, XlsxWriter xlsxWriter, IStatusBarMessage statusProgress, IQueryRunner runner) { int iMaxCol = reader.FieldCount - 1; int iRowCnt = 0; ADOTabularColumn daxCol; int colIdx = 0; XlsxStyle[] columnStyles = new XlsxStyle[reader.FieldCount]; var headerStyle = new XlsxStyle( new XlsxFont("Segoe UI", 9, Color.White, bold: true), new XlsxFill(Color.FromArgb(0, 0x45, 0x86)), XlsxStyle.Default.Border, XlsxStyle.Default.NumberFormat, XlsxAlignment.Default); var wrapStyle = XlsxStyle.Default.With(new XlsxAlignment(vertical: Vertical.Top, wrapText: true)); var defaultStyle = XlsxStyle.Default; // Write out Header Row xlsxWriter.SetDefaultStyle(headerStyle).BeginRow(); foreach (var colName in reader.CleanColumnNames()) { // write out the column name xlsxWriter.Write(colName); // cache the column formatstrings as Excel Styles reader.Connection.Columns.TryGetValue(reader.GetName(colIdx), out daxCol); if (daxCol != null) { columnStyles[colIdx] = GetStyle(daxCol); } else { columnStyles[colIdx] = defaultStyle; } colIdx++; } xlsxWriter.SetDefaultStyle(defaultStyle); while (reader.Read()) { // check if we have reached the limit of an xlsx file if (iRowCnt >= 999999) { runner.OutputWarning("Results truncated, reached the maximum row limit for an Excel file"); break; } // increment row count iRowCnt++; // start outputting the next row xlsxWriter.BeginRow(); for (int iCol = 0; iCol < reader.FieldCount; iCol++) { var fieldValue = reader[iCol]; switch (fieldValue) { case int i: xlsxWriter.Write(i, columnStyles[iCol]); break; case double dbl: xlsxWriter.Write(dbl, columnStyles[iCol]); break; case decimal dec: xlsxWriter.Write(dec, columnStyles[iCol]); break; case DateTime dt: xlsxWriter.Write(dt, columnStyles[iCol]); break; case string str: if (str.Contains("\n") || str.Contains("\r")) { xlsxWriter.Write(str, wrapStyle); } else { xlsxWriter.Write(str); } break; case bool b: xlsxWriter.Write(b.ToString()); // Writes out TRUE/FALSE break; case null: xlsxWriter.Write(); break; case long lng: if (lng < int.MaxValue && lng > int.MinValue) { xlsxWriter.Write(Convert.ToInt32(lng), columnStyles[iCol]); } else // TODO - should we be converting large long values to double?? { xlsxWriter.Write(lng.ToString()); // write numbers outside the size of int as strings } break; default: xlsxWriter.Write(fieldValue.ToString()); break; } } if (iRowCnt % 1000 == 0) { statusProgress.Update($"Written {iRowCnt:n0} rows to the file output"); } } return(iRowCnt); }