/// <summary>
/// Return the Excel type formatting string for the current options (null if not defined)
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="acceptsNativeType">True if the type converter will pass the native type through to Excel on writing, false if conversion is required.</param>
/// <param name="convertedType">The type that we are converting from</param>
/// <returns>The Excel formatting string for the object, null to use default formatting.</returns>
public static string DefaultFormatString(
TypeConverterOptions options,
bool acceptsNativeType,
Type convertedType)
{
if (acceptsNativeType)
{
if (options.NumberFormat != null)
{
var format = XLStyle.FormatDotNetToXL(options.NumberFormat, convertedType, options.CultureInfo);
if (!string.IsNullOrEmpty(format))
{
return(format);
}
}
else if (options.DateFormat != null)
{
var format = XLStyle.FormatDotNetToXL(options.DateFormat, convertedType, options.CultureInfo);
if (!string.IsNullOrEmpty(format))
{
return(format);
}
}
}
return(null);
}
private void btnFormulas_Click(object sender, RoutedEventArgs e)
{
SaveBook(book =>
{
// first sheet
var sheet = book.Sheets[0];
// column width in twips
sheet.Columns[0].Width = 2000;
sheet.Columns[1].Width = 2200;
// string formulas
string s = "String:";
sheet[0, 0].Value = s;
sheet[1, 0].Value = s;
sheet[2, 0].Value = s;
sheet[0, 1].Value = "apples";
sheet[1, 1].Value = "and";
sheet[2, 1].Value = "oranges";
s = "String formula:";
sheet[4, 0].Value = s;
sheet[5, 0].Value = s;
sheet[4, 1].Value = "apples and oranges";
sheet[5, 1].Value = "apples an";
sheet[4, 1].Formula = "CONCATENATE(B1,\" \",B2, \" \",B3)";
sheet[5, 1].Formula = "LEFT(B5,9)";
// simple formulas
sheet[7, 0].Value = "Formula: 5!";
sheet[7, 1].Value = 120;
sheet[7, 1].Formula = "1*2*3*4*5";
sheet[8, 0].Value = "Formula: 12/0";
sheet[8, 1].Value = 0;
sheet[8, 1].Formula = "12/0";
sheet[9, 0].Value = "Formula: 1 = 1";
sheet[9, 1].Value = true;
sheet[9, 1].Formula = "1=1";
sheet[10, 0].Value = "Formula: 1 = 2";
sheet[10, 1].Value = false;
sheet[10, 1].Formula = "1 = 2";
// now function
sheet[12, 0].Value = "Formula: Now()";
sheet[12, 1].Value = DateTime.Now;
sheet[12, 1].Formula = "Now()";
var style = new XLStyle(book);
var dtfi = CultureInfo.CurrentCulture.DateTimeFormat;
style.Format = XLStyle.FormatDotNetToXL(dtfi.ShortDatePattern + " " + dtfi.ShortTimePattern);
sheet[12, 1].Style = style;
});
}
/// <summary>
/// Set an entire row to a specific format. By default Excel defines the
/// cell style in the following order; cell, row, column, worksheet default
/// </summary>
/// <param name="row">Row to set the format for</param>
/// <param name="numberFormat">Optional number formatting string for the cell</param>
/// <param name="dateFormat">Optional DateTime formatting string for the cell</param>
/// <param name="fontStyle">Optional font style for the cell</param>
/// <param name="fontSize">Optional font size for the cell</param>
/// <param name="fontName">Optional font name for the cell</param>
public void SetRowFormat(
int row,
string numberFormat = null,
string dateFormat = null,
FontStyle?fontStyle = null,
float?fontSize = null,
string fontName = null)
{
var format = XLStyle.FormatDotNetToXL(numberFormat ?? dateFormat);
XLStyle style = null;
UpdateStyle(ref style, format, fontStyle, fontSize, fontName);
_sheet.Rows[row].Style = style;
}
/// <summary>
/// Set an entire column to a specific format. By default Excel defines the
/// cell style in the following order; cell, row, column, worksheet default
/// </summary>
/// <param name="col">Column to set the format for</param>
/// <param name="numberFormat">Optional number formatting string for the cell</param>
/// <param name="dateFormat">Optional DateTime formatting string for the cell</param>
/// <param name="fontStyle">Optional font style for the cell</param>
/// <param name="fontSize">Optional font size for the cell</param>
/// <param name="fontName">Optional font name for the cell</param>
public void SetColumnFormat(
int col,
string numberFormat = null,
string dateFormat = null,
FontStyle?fontStyle = null,
float?fontSize = null,
string fontName = null)
{
var format = XLStyle.FormatDotNetToXL(numberFormat ?? dateFormat);
XLStyle style = null;
UpdateStyle(ref style, format, fontStyle, fontSize, fontName);
_sheet.Columns[col].Style = style;
}
private string CreateExcelFile()
{
//clear Excel book, remove the single blank sheet
_c1xl.Clear();
_c1xl.Sheets.Clear();
_c1xl.DefaultFont = new Font("Tahoma", 8);
//create Excel styles
_styTitle = new XLStyle(_c1xl);
_styHeader = new XLStyle(_c1xl);
_styMoney = new XLStyle(_c1xl);
_styOrder = new XLStyle(_c1xl);
//set up styles
_styTitle.Font = new Font(_c1xl.DefaultFont.Name, 15, FontStyle.Bold);
_styTitle.ForeColor = Color.Blue;
_styHeader.Font = new Font(_c1xl.DefaultFont, FontStyle.Bold);
_styHeader.ForeColor = Color.White;
_styHeader.BackColor = Color.DarkGray;
_styMoney.Format = XLStyle.FormatDotNetToXL("c");
_styOrder.Font = _styHeader.Font;
_styOrder.ForeColor = Color.Red;
//create report with one sheet per category
DataTable dt = GetCategories();
foreach (DataRow dr in dt.Rows)
{
CreateSheet(dr);
}
//save xls file
string filename = GetTempFileName(".xls");
_c1xl.Save(filename);
return(filename);
}
/// <summary>
/// Return the Excel type formatting string for the current options (null if not defined)
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <returns>The Excel formatting string for the object, null to use default formatting.</returns>
public virtual string ExcelFormatString(
TypeConverterOptions options)
{
if (AcceptsNativeType)
{
if (options.NumberFormat != null)
{
var format = XLStyle.FormatDotNetToXL(options.NumberFormat, _convertedType, options.CultureInfo);
if (!string.IsNullOrEmpty(format))
{
return(format);
}
}
else if (options.DateFormat != null)
{
var format = XLStyle.FormatDotNetToXL(options.DateFormat, _convertedType, options.CultureInfo);
if (!string.IsNullOrEmpty(format))
{
return(format);
}
}
}
return(null);
}
static public void ToExcel(C1TrueDBGrid _Dgd)
{
if (_Dgd.RowCount == 0)
{
return;
}
string filename = "";
var book = new C1XLBook();
try
{
SaveFileDialog dialog = new SaveFileDialog
{
Filter = "*.xls|*.xls",
FileName = "DgdExcel.xls"
};
if (dialog.ShowDialog() == DialogResult.OK)
{
filename = dialog.FileName;
var sheet = book.Sheets[0];
sheet.Name = "Page1";
XLStyle style = new XLStyle(book);
DateTimeFormatInfo dtfi = CultureInfo.CurrentCulture.DateTimeFormat;
style.Format = XLStyle.FormatDotNetToXL(dtfi.ShortDatePattern);
var k = 0;
for (int i = 0; i < _Dgd.Columns.Count; i++)
{
if (_Dgd.Splits[0].DisplayColumns[i] == null)
{
break;
}
if (_Dgd.Splits[0].DisplayColumns[i].Visible)
{
sheet[0, k].Value = _Dgd.Columns[i].Caption;
k++;
}
}
for (int i = 0; i < _Dgd.RowCount; i++)
{
k = 0;
for (int j = 0; j < _Dgd.Columns.Count; j++)
{
if (_Dgd.Splits[0].DisplayColumns[j] == null)
{
break;
}
if (_Dgd.Splits[0].DisplayColumns[j].Visible)
{
var obj = _Dgd[i, j];
if (obj != null)
{
if (obj.GetType() == typeof(DateTime))
{
sheet[i + 1, k].Style = style;
}
sheet[i + 1, k].Value = _Dgd[i, j];
}
else
{
sheet[i + 1, k].Value = "";
}
k++;
}
}
}
book.Save(filename);
}
}
catch (Exception ex)
{
MBox.ShowErr(ex.Message);
return;
}
finally
{
book.Dispose();
}
if (MBox.ShowAsk(string.Format("数据导出成功。保存路径：{0}\r\n是否打开？", filename)))
{
System.Diagnostics.Process.Start(filename);
}
}
/// <summary>
/// Writes a cell to the Excel file.
/// </summary>
/// <typeparam name="T">The type of the field.</typeparam>
/// <param name="row">Row to write the field to.</param>
/// <param name="col">Column to write the field to.</param>
/// <param name="field">The field to write.</param>
/// <param name="numberFormat">Optional number formatting string for the cell</param>
/// <param name="dateFormat">Optional DateTime formatting string for the cell</param>
/// <param name="fontStyle">Optional font style for the cell</param>
/// <param name="fontSize">Optional font size for the cell</param>
/// <param name="fontName">Optional font name for the cell</param>
public void WriteCell <T>(
int row,
int col,
T field,
string numberFormat = null,
string dateFormat = null,
FontStyle?fontStyle = null,
float?fontSize = null,
string fontName = null)
{
// Find the type conversion options
var type = typeof(T);
var converter = TypeConverterFactory.GetConverter(type);
var options = TypeConverterOptions.Merge(TypeConverterOptionsFactory.GetOptions(type, _configuration.CultureInfo));
// Set the formatting options to override the defaults
var format = numberFormat ?? dateFormat;
if (converter.AcceptsNativeType)
{
// Convert the options to Excel format
if (format != null)
{
format = XLStyle.FormatDotNetToXL(format, converter.ConvertedType, options.CultureInfo);
}
else
{
// If no formatting is provided, see if the native type requires it (mostly for DateTime)
format = converter.ExcelFormatString(options);
}
}
else
{
// Override the formatting for the formatter, and do not format the Excel cell
if (numberFormat != null)
{
options.NumberFormat = format;
}
else if (dateFormat != null)
{
options.DateFormat = format;
}
format = null;
}
// Find the default style to use for this cell based on the row and column styles
var cellStyle = _sheet.Rows[row].Style ?? _sheet.Columns[col].Style;
// Clone the style so it does not modify the entire row or column
cellStyle = cellStyle?.Clone();
// Set up cell formatting for this cell
UpdateStyle(ref cellStyle, format, fontStyle, fontSize, fontName);
// Apply the style to this cell if defined
if (cellStyle != null)
{
_sheet[row, col].Style = cellStyle;
}
// Now write the cell contents
object value;
if (converter.AcceptsNativeType)
{
value = field;
}
else
{
value = converter.ConvertToExcel(options, field);
}
var s = value as string;
if (s != null && s.StartsWith("="))
{
// Write as a formula if it starts with an equals sign
_sheet[row, col].Value = "";
_sheet[row, col].Formula = s;
}
else
{
_sheet[row, col].Value = value;
}
}
// convert grid styles into excel styles
private static XLStyle GetXLStyle(C1FlexGrid flex, XLSheet sheet, ExcelCellStyle s)
{
// look it up in the cache
XLStyle x = default(XLStyle);
if (_excelStyles.TryGetValue(s, out x))
{
return(x);
}
// not found, create style now
x = new XLStyle(sheet.Book);
// alignment
if (s.HorizontalAlignment.HasValue)
{
switch (s.HorizontalAlignment.Value)
{
case HorizontalAlignment.Left:
x.AlignHorz = XLAlignHorzEnum.Left;
break;
case HorizontalAlignment.Center:
x.AlignHorz = XLAlignHorzEnum.Center;
break;
case HorizontalAlignment.Right:
x.AlignHorz = XLAlignHorzEnum.Right;
break;
}
}
if (s.VerticalAlignment.HasValue)
{
switch (s.VerticalAlignment.Value)
{
case VerticalAlignment.Top:
x.AlignVert = XLAlignVertEnum.Top;
break;
case VerticalAlignment.Center:
x.AlignVert = XLAlignVertEnum.Center;
break;
case VerticalAlignment.Bottom:
x.AlignVert = XLAlignVertEnum.Bottom;
break;
}
}
if (s.TextWrapping.HasValue)
{
x.WordWrap = s.TextWrapping.Value;
}
// colors
if (s.Background is SolidColorBrush)
{
x.BackColor = ((SolidColorBrush)s.Background).Color;
x.BackPattern = XLPatternEnum.Solid;
}
if (s.Foreground is SolidColorBrush)
{
x.ForeColor = ((SolidColorBrush)s.Foreground).Color;
}
// font
dynamic fontName = flex.FontFamily.Source;
dynamic fontSize = flex.FontSize;
dynamic bold = false;
dynamic italic = false;
bool underline = false;
bool hasFont = false;
if (s.FontFamily != null)
{
fontName = s.FontFamily.Source;
hasFont = true;
}
if (s.FontSize.HasValue)
{
fontSize = s.FontSize.Value;
hasFont = true;
}
if (s.FontWeight.HasValue)
{
bold = s.FontWeight.Value == FontWeights.Bold || s.FontWeight.Value == FontWeights.ExtraBold || s.FontWeight.Value == FontWeights.SemiBold;
hasFont = true;
}
if (s.FontStyle.HasValue)
{
italic = s.FontStyle.Value == FontStyles.Italic;
hasFont = true;
}
if (s.TextDecorations != null)
{
underline = true;
hasFont = true;
}
if (hasFont)
{
fontSize = PixelsToPoints(fontSize);
if (underline)
{
dynamic color = Colors.Black;
if (flex.Foreground is SolidColorBrush)
{
color = ((SolidColorBrush)flex.Foreground).Color;
}
if (s.Foreground is SolidColorBrush)
{
color = ((SolidColorBrush)s.Foreground).Color;
}
x.Font = new XLFont(fontName, Convert.ToSingle(fontSize), bold, italic, false, XLFontScript.None, XLUnderlineStyle.Single, color);
}
else
{
x.Font = new XLFont(fontName, Convert.ToSingle(fontSize), bold, italic);
}
}
// format
if (!string.IsNullOrEmpty(s.Format))
{
x.Format = XLStyle.FormatDotNetToXL(s.Format);
}
// borders
if (s.CellBorderThickness.Left > 0 && s.CellBorderBrushLeft is SolidColorBrush)
{
x.BorderLeft = GetBorderLineStyle(s.CellBorderThickness.Left);
x.BorderColorLeft = ((SolidColorBrush)s.CellBorderBrushLeft).Color;
}
if (s.CellBorderThickness.Top > 0 && s.CellBorderBrushTop is SolidColorBrush)
{
x.BorderTop = GetBorderLineStyle(s.CellBorderThickness.Top);
x.BorderColorTop = ((SolidColorBrush)s.CellBorderBrushTop).Color;
}
if (s.CellBorderThickness.Right > 0 && s.CellBorderBrushRight is SolidColorBrush)
{
x.BorderRight = GetBorderLineStyle(s.CellBorderThickness.Right);
x.BorderColorRight = ((SolidColorBrush)s.CellBorderBrushRight).Color;
}
if (s.CellBorderThickness.Bottom > 0 && s.CellBorderBrushBottom is SolidColorBrush)
{
x.BorderBottom = GetBorderLineStyle(s.CellBorderThickness.Bottom);
x.BorderColorBottom = ((SolidColorBrush)s.CellBorderBrushBottom).Color;
}
// save in cache and return
_excelStyles[s] = x;
return(x);
}
/// <summary>
/// Return the Excel type formatting string for the current options (null if not defined)
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <returns>The Excel formatting string for the object, null to use default formatting.</returns>
public static string DateTimeFormatString(
TypeConverterOptions options)
{
// Always use the general date format for storing dates in Excel, so they don't look like doubles to the user
return(XLStyle.FormatDotNetToXL(options.DateFormat ?? "G", typeof(DateTime), options.CultureInfo));
}
// convert FlexGrid style into Excel style
private XLStyle StyleFromFlex(CellStyle style)
{
// sanity
if (style == null)
{
return(null);
}
// look it up on list
if (_styles.Contains(style))
{
return(_styles[style] as XLStyle);
}
// create new Excel style
XLStyle xs = new XLStyle(_book);
// set up new style
xs.Font = style.Font;
if (style.BackColor.ToArgb() != SystemColors.Window.ToArgb())
{
xs.BackColor = style.BackColor;
}
xs.WordWrap = style.WordWrap;
xs.Format = XLStyle.FormatDotNetToXL(style.Format);
switch (style.TextDirection)
{
case TextDirectionEnum.Up:
xs.Rotation = 90;
break;
case TextDirectionEnum.Down:
xs.Rotation = 180;
break;
}
switch (style.TextAlign)
{
case TextAlignEnum.CenterBottom:
xs.AlignHorz = XLAlignHorzEnum.Center;
xs.AlignVert = XLAlignVertEnum.Bottom;
break;
case TextAlignEnum.CenterCenter:
xs.AlignHorz = XLAlignHorzEnum.Center;
xs.AlignVert = XLAlignVertEnum.Center;
break;
case TextAlignEnum.CenterTop:
xs.AlignHorz = XLAlignHorzEnum.Center;
xs.AlignVert = XLAlignVertEnum.Top;
break;
case TextAlignEnum.GeneralBottom:
xs.AlignHorz = XLAlignHorzEnum.General;
xs.AlignVert = XLAlignVertEnum.Bottom;
break;
case TextAlignEnum.GeneralCenter:
xs.AlignHorz = XLAlignHorzEnum.General;
xs.AlignVert = XLAlignVertEnum.Center;
break;
case TextAlignEnum.GeneralTop:
xs.AlignHorz = XLAlignHorzEnum.General;
xs.AlignVert = XLAlignVertEnum.Top;
break;
case TextAlignEnum.LeftBottom:
xs.AlignHorz = XLAlignHorzEnum.Left;
xs.AlignVert = XLAlignVertEnum.Bottom;
break;
case TextAlignEnum.LeftCenter:
xs.AlignHorz = XLAlignHorzEnum.Left;
xs.AlignVert = XLAlignVertEnum.Center;
break;
case TextAlignEnum.LeftTop:
xs.AlignHorz = XLAlignHorzEnum.Left;
xs.AlignVert = XLAlignVertEnum.Top;
break;
case TextAlignEnum.RightBottom:
xs.AlignHorz = XLAlignHorzEnum.Right;
xs.AlignVert = XLAlignVertEnum.Bottom;
break;
case TextAlignEnum.RightCenter:
xs.AlignHorz = XLAlignHorzEnum.Right;
xs.AlignVert = XLAlignVertEnum.Center;
break;
case TextAlignEnum.RightTop:
xs.AlignHorz = XLAlignHorzEnum.Right;
xs.AlignVert = XLAlignVertEnum.Top;
break;
default:
Debug.Assert(false);
break;
}
// save it
_styles.Add(style, xs);
// return it
return(xs);
}
public C1XLBook CreateSample()
{
// create C1XLBook
C1XLBook wb = new C1XLBook();
XLSheet ws = wb.Sheets[0];
// column width in twips
ws.Columns[0].Width = 2000;
ws.Columns[1].Width = 2200;
// string formulas
string s = "String:";
ws[0, 0].Value = s;
ws[1, 0].Value = s;
ws[2, 0].Value = s;
ws[0, 1].Value = "apples";
ws[1, 1].Value = "and";
ws[2, 1].Value = "oranges";
s = "String formula:";
ws[4, 0].Value = s;
ws[5, 0].Value = s;
ws[4, 1].Value = "apples and oranges";
ws[5, 1].Value = "apples an";
ws[4, 1].Formula = "CONCATENATE(B1,\" \",B2, \" \",B3)";
ws[5, 1].Formula = "LEFT(B5,9)";
// simple formulas
ws[7, 0].Value = "Formula: 5!";
ws[7, 1].Value = 120;
ws[7, 1].Formula = "1*2*3*4*5";
ws[8, 0].Value = "Formula: 12/0";
ws[8, 1].Value = 0;
ws[8, 1].Formula = "12/0";
ws[9, 0].Value = "Formula: 1 = 1";
ws[9, 1].Value = true;
ws[9, 1].Formula = "1=1";
ws[10, 0].Value = "Formula: 1 = 2";
ws[10, 1].Value = false;
ws[10, 1].Formula = "1 = 2";
// now function
ws[12, 0].Value = "Formula: Now()";
ws[12, 1].Value = DateTime.Now;
ws[12, 1].Formula = "Now()";
XLStyle style = new XLStyle(wb);
DateTimeFormatInfo dtfi = CultureInfo.CurrentCulture.DateTimeFormat;
style.Format = XLStyle.FormatDotNetToXL(dtfi.ShortDatePattern + " " + dtfi.ShortTimePattern);
ws[12, 1].Style = style;
// done
return(wb);
}
private void _btCreate_Click(object sender, RoutedEventArgs e)
{
// create new workbook
if (_book == null)
{
_book = new C1XLBook();
}
// clear the book
_book.Clear();
// first sheet
XLSheet sheet = _book.Sheets[0];
// column width in twips
sheet.Columns[0].Width = 2000;
sheet.Columns[1].Width = 2200;
// string formulas
string s = "String:";
sheet[0, 0].Value = s;
sheet[1, 0].Value = s;
sheet[2, 0].Value = s;
sheet[0, 1].Value = "apples";
sheet[1, 1].Value = "and";
sheet[2, 1].Value = "oranges";
s = "String formula:";
sheet[4, 0].Value = s;
sheet[5, 0].Value = s;
sheet[4, 1].Value = "apples and oranges";
sheet[5, 1].Value = "apples an";
sheet[4, 1].Formula = "CONCATENATE(B1,\" \",B2, \" \",B3)";
sheet[5, 1].Formula = "LEFT(B5,9)";
// simple formulas
sheet[7, 0].Value = "Formula: 5!";
sheet[7, 1].Value = 120;
sheet[7, 1].Formula = "1*2*3*4*5";
sheet[8, 0].Value = "Formula: 12/0";
sheet[8, 1].Value = 0;
sheet[8, 1].Formula = "12/0";
sheet[9, 0].Value = "Formula: 1 = 1";
sheet[9, 1].Value = true;
sheet[9, 1].Formula = "1=1";
sheet[10, 0].Value = "Formula: 1 = 2";
sheet[10, 1].Value = false;
sheet[10, 1].Formula = "1 = 2";
// now function
sheet[12, 0].Value = "Formula: Now()";
sheet[12, 1].Value = DateTime.Now;
sheet[12, 1].Formula = "Now()";
XLStyle style = new XLStyle(_book);
DateTimeFormatInfo dtfi = CultureInfo.CurrentCulture.DateTimeFormat;
style.Format = XLStyle.FormatDotNetToXL(dtfi.ShortDatePattern + " " + dtfi.ShortTimePattern);
sheet[12, 1].Style = style;
// allow save the file
_lblStatus.Text = "You can save workbook";
_btnSave.IsEnabled = true;
}
