void ExportCell(IXlRow row, int gridRowHandle, GridColumn gridColumn)
{
using (IXlCell cell = row.CreateCell()) {
// Set cell value
cell.Value = XlVariantValue.FromObject(this.view.GetRowCellValue(gridRowHandle, gridColumn));
// Get cell appearance
AppearanceObject appearance = GetCellAppearance(gridRowHandle, gridColumn);
// Apply alignment
XlCellAlignment alignment = new XlCellAlignment()
{
WrapText = appearance.TextOptions.WordWrap.HasFlag(WordWrap.Wrap),
VerticalAlignment = ConvertAlignment(appearance.TextOptions.VAlignment),
HorizontalAlignment = ConvertAlignment(appearance.TextOptions.HAlignment)
};
cell.ApplyFormatting(alignment);
// Apply borders
Color borderColor = appearance.GetBorderColor();
if (!DXColor.IsTransparentOrEmpty(borderColor))
{
cell.ApplyFormatting(XlBorder.OutlineBorders(borderColor));
}
// Apply fill
if (appearance.Options.UseBackColor)
{
cell.ApplyFormatting(XlFill.SolidFill(appearance.BackColor));
}
// Apply font
Font appearanceFont = appearance.Font;
XlFont font = XlFont.CustomFont(appearanceFont.Name);
font.Size = appearanceFont.SizeInPoints;
font.Bold = appearanceFont.Bold;
font.Italic = appearanceFont.Italic;
font.StrikeThrough = appearanceFont.Strikeout;
font.Underline = appearanceFont.Underline ? XlUnderlineType.Single : XlUnderlineType.None;
if (appearance.Options.UseForeColor)
{
font.Color = appearance.ForeColor;
}
cell.ApplyFormatting(font);
}
}
void GenerateInvoiceBillTo(IXlSheet sheet)
{
// Set the top border for the first row in the "Bill To" panel.
XlBorder border = new XlBorder();
// Set the top border color to white.
border.TopColor = Color.White;
// Specify the medium border line style.
border.TopLineStyle = XlBorderLineStyle.Medium;
// Generate worksheet rows containing buyer's contact information.
GenerateBillToRow(sheet, null, null, null, null, border);
GenerateBillToRow(sheet, "BILL TO:", invoice.Customer, "PHONE:", invoice.Phone, null);
GenerateBillToRow(sheet, null, invoice.Company, null, null, null);
GenerateBillToRow(sheet, "ADDRESS:", invoice.Address, "FAX:", invoice.Fax, null);
GenerateBillToRow(sheet, null, invoice.Address2, null, null, null);
GenerateBillToRow(sheet, null, null, null, null, null);
}
static void Borders(Stream stream, XlDocumentFormat documentFormat)
{
#region #Borders
// Specify a two-dimensional array that stores possible line styles for a border.
XlBorderLineStyle[,] lineStyles = new XlBorderLineStyle[, ] {
{ XlBorderLineStyle.Thin, XlBorderLineStyle.Medium, XlBorderLineStyle.Thick, XlBorderLineStyle.Double },
{ XlBorderLineStyle.Dotted, XlBorderLineStyle.Dashed, XlBorderLineStyle.DashDot, XlBorderLineStyle.DashDotDot },
{ XlBorderLineStyle.SlantDashDot, XlBorderLineStyle.MediumDashed, XlBorderLineStyle.MediumDashDot, XlBorderLineStyle.MediumDashDotDot }
};
// Create an exporter instance.
IXlExporter exporter = XlExport.CreateExporter(documentFormat);
// Create a new document.
using (IXlDocument document = exporter.CreateDocument(stream)) {
document.Options.Culture = CultureInfo.CurrentCulture;
// Create a worksheet.
using (IXlSheet sheet = document.CreateSheet()) {
for (int i = 0; i < 3; i++)
{
sheet.SkipRows(1);
// Create a worksheet row.
using (IXlRow row = sheet.CreateRow()) {
for (int j = 0; j < 4; j++)
{
row.SkipCells(1);
// Create a new cell in the row.
using (IXlCell cell = row.CreateCell()) {
// Set outside borders for the created cell using a particular line style from the lineStyles array.
cell.ApplyFormatting(XlBorder.OutlineBorders(Color.SeaGreen, lineStyles[i, j]));
}
}
}
}
}
}
#endregion #Borders
}
void GenerateBillToRow(IXlSheet sheet, string name1, object value1, string name2, object value2, XlBorder specificBorder)
{
using (IXlRow row = sheet.CreateRow()) {
// Set the cell font.
row.ApplyFormatting(infoFont);
// Skip the first cell in the row.
row.SkipCells(1);
// Create the empty cell with the specified formatting settings.
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name1;
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
cell.Formatting.Font.Bold = true;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value1);
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create the cell, assign its value and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = name2;
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
cell.Formatting.Font.Bold = true;
}
// Create the cell, assign its value converted from the custom object and apply specific formatting settings to it.
using (IXlCell cell = row.CreateCell()) {
cell.Value = XlVariantValue.FromObject(value2);
cell.ApplyFormatting(infoFormatting);
// Set the cell border.
cell.ApplyFormatting(specificBorder);
}
// Create three successive cells, apply specific formatting settings to them and set the cell borders.
for (int i = 0; i < 3; i++)
{
using (IXlCell cell = row.CreateCell()) {
cell.ApplyFormatting(infoFormatting);
cell.ApplyFormatting(specificBorder);
}
}
}
}
void InitializeFormatting()
{
// Specify formatting settings for the even rows.
evenRowFormatting = new XlCellFormatting();
evenRowFormatting.Font = new XlFont();
evenRowFormatting.Font.Name = "Century Gothic";
evenRowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
evenRowFormatting.Fill = XlFill.SolidFill(Color.White);
// Specify formatting settings for the odd rows.
oddRowFormatting = new XlCellFormatting();
oddRowFormatting.CopyFrom(evenRowFormatting);
oddRowFormatting.Fill = XlFill.SolidFill(Color.FromArgb(242, 242, 242));
// Specify formatting settings for the header row.
headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(evenRowFormatting);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = Color.White;
headerRowFormatting.Fill = XlFill.SolidFill(Color.FromArgb(192, 0, 0));
// Set borders for the header row.
headerRowFormatting.Border = new XlBorder();
// Specify the top border and set its color to white.
headerRowFormatting.Border.TopColor = Color.White;
// Specify the medium border line style.
headerRowFormatting.Border.TopLineStyle = XlBorderLineStyle.Medium;
// Specify the bottom border for the header row.
// Set the bottom border color to dark gray.
headerRowFormatting.Border.BottomColor = Color.FromArgb(89, 89, 89);
// Specify the medium border line style.
headerRowFormatting.Border.BottomLineStyle = XlBorderLineStyle.Medium;
// Specify formatting settings for the invoice header.
panelFont = new XlFont();
panelFont.Name = "Century Gothic";
panelFont.SchemeStyle = XlFontSchemeStyles.None;
panelFont.Color = Color.White;
// Set font attributes for the row displaying the invoice label and company name.
titleFont = panelFont.Clone();
titleFont.Size = 26;
// Specify formatting settings for the worksheet range containing the name and contact details of the seller (the "Vader Enterprises" panel).
leftPanelFormatting = new XlCellFormatting();
// Set the cell background color to dark gray.
leftPanelFormatting.Fill = XlFill.SolidFill(Color.FromArgb(89, 89, 89));
leftPanelFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
leftPanelFormatting.NumberFormat = XlNumberFormat.General;
// Set the right border for this range.
leftPanelBorder = new XlBorder();
// Set the right border color to white.
leftPanelBorder.RightColor = Color.White;
// Specify the medium border line style.
leftPanelBorder.RightLineStyle = XlBorderLineStyle.Medium;
// Specify formatting settings for the worksheet range containing general information about the invoice:
// its date, reference number and service description (the "Invoice" panel).
rightPanelFormatting = new XlCellFormatting();
// Set the cell background color to dark red.
rightPanelFormatting.Fill = XlFill.SolidFill(Color.FromArgb(192, 0, 0));
rightPanelFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
rightPanelFormatting.NumberFormat = XlNumberFormat.General;
// Specify formatting settings and font attributes for the worksheet range containing buyer's contact information (the "Bill To" panel).
infoFormatting = new XlCellFormatting();
// Set the cell background color to light gray.
infoFormatting.Fill = XlFill.SolidFill(Color.FromArgb(217, 217, 217));
infoFormatting.Alignment = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
infoFormatting.NumberFormat = XlNumberFormat.General;
infoFont = panelFont.Clone();
infoFont.Color = Color.Black;
}
void op_CustomizeCell(CustomizeCellEventArgs e)
{
long _mark = long.Parse((GVG.GetRowCellValue(e.RowHandle, "ID") ?? 0).ToString());
XlCellFormatting formatting = new XlCellFormatting();
formatting.Font = new XlFont();
formatting.Font.Bold = true;
formatting.Font.Name = "Times New Roman";
if (_mark == 0)
{
formatting.Border = XlBorder.OutlineBorders(Color.FromArgb(216, 228, 188));
if (e.ColumnFieldName == "CongTy")
{
formatting.Border.RightColor = Color.Black;
}
formatting.Fill = XlFill.SolidFill(Color.FromArgb(216, 228, 188));
e.Formatting.FormatType = FormatType.None;
e.Value = string.Empty;
}
else
{
XlCellAlignment alignment = new XlCellAlignment();
alignment.HorizontalAlignment = XlHorizontalAlignment.Center;
alignment.VerticalAlignment = XlVerticalAlignment.Center;
if (e.AreaType == SheetAreaType.Header)
{
e.Formatting.Alignment = alignment;
formatting.Fill = XlFill.SolidFill(Color.DarkSeaGreen);
formatting.Font.Size = 15;
}
else if (_mark == -1)
{
formatting.Border = XlBorder.OutlineBorders(Color.DarkSeaGreen);
if (!"MaCho HanhTrinhDi MaHD SoVe".Contains(e.ColumnFieldName))
{
formatting.Border.RightColor = Color.Black;
}
formatting.Border.BottomColor = Color.Black;
formatting.Fill = XlFill.SolidFill(Color.DarkSeaGreen);
formatting.Font.Size = 12;
if (e.ColumnFieldName == "HanhTrinhVe")
{
e.Value = "Tổng";
}
if ((e.Value ?? string.Empty).ToString() == "0")
{
e.Value = string.Empty;
}
}
else
{
formatting.Fill = XlFill.SolidFill(Color.White);
if (e.Value is string)
{
e.Formatting.Alignment = alignment;
}
}
}
e.Formatting.CopyFrom(formatting, FormatType.None);
e.Handled = true;
}