static void Main()
{
const string ID = "ID";
const string FRUIT = "Fruit";
const string FAVORITE = "Favorite";
const string DESC = "Description";
var table = new DataTable();
table.Columns.Add(ID);
table.Columns.Add(FRUIT);
table.Columns.Add(FAVORITE);
table.Columns.Add(DESC);
string[] fruits = { "Apple", "Orange", "Pineapple", "Grapes", "Peach", "Pear", "Strawberry", "Raspberry", "BlueBerry", "Lemon" };
for (var i = 0; i < 100; i++)
{
var row = table.NewRow();
row[ID] = (i + 1).ToString();
row[FRUIT] = fruits[i % fruits.Length];
row[FAVORITE] = (i * (i + 2)) % 7 == 0 ? "X" : "";
row[DESC] = fruits[i % fruits.Length] + Environment.NewLine + fruits[i % (fruits.Length / 2)];
table.Rows.Add(row);
}
var parms = new ExcelExportParm
{
HeaderFormat = new ExcelFormat
{
Bold = true,
BackColor = Color.Aqua,
ForeColor = Color.Black,
Border = new ExcelBorder()
{
BorderStyle = XlLineStyle.xlContinuous,
BorderThickness = 4
}
},
DefaultCellFormat = new ExcelFormat()
{
ForeColor = Color.BlueViolet,
Border = new ExcelBorder()
{
BorderColor = Color.Red,
BorderStyle = XlLineStyle.xlContinuous,
BorderThickness = 2d
}
}
};
var favColPArms = new ColumnExportParm
{
HorizontalAlignment = XlHAlign.xlHAlignCenter,
FormatConditions = new []
{
new ExcelConditionnalFormating
{
Type = XlFormatConditionType.xlCellValue,
Operator = XlFormatConditionOperator.xlEqual,
Formula = "X",
Format = new ExcelFormat
{
ForeColor = Color.Red,
BackColor = Color.Bisque,
Bold = true
}
}
}
};
parms.ColumnParms.Add(FAVORITE, favColPArms);
var fruitColPArms = new ColumnExportParm
{
EnumValues = fruits
};
parms.ColumnParms.Add(FRUIT, fruitColPArms);
new ExcelExporter().ExportDataTable(table, parms);
}
public void ExportDataTable(DataTable table, ExcelExportParm parms)
{
int totalCount = parms.UseView ? table.DefaultView.Count : table.Rows.Count;
ExportationStarted(this, new EventArgs <int>(table.Rows.Count));
new Thread(new ThreadStart(delegate
{
try
{
var excelApp = new ExcelApp.Application();
var excelWorkbook = excelApp.Workbooks.Add();
ExcelApp.Worksheet sheet = excelWorkbook.Worksheets.Add();
foreach (var sh in excelWorkbook.Worksheets.Cast <ExcelApp.Worksheet>().Where(sh => sh != sheet))
{
sh.Delete();
}
sheet.Name = string.IsNullOrWhiteSpace(table.TableName) ? "Table" : table.TableName;
var cols = table.Columns.OfType <DataColumn>().Select(dc => dc.ColumnName).ToList();
var excelHeaders = sheet.Range[sheet.Cells[1, 1], sheet.Cells[1, cols.Count]];
excelHeaders.Value2 = cols.ToArray();
ApplyStyle(excelHeaders, parms.DefaultCellFormat);
if (parms.HeaderFormat != null)
{
ApplyStyle(excelHeaders, parms.HeaderFormat);
}
var i = 2;
foreach (DataRow dr in parms.UseView ? table.DefaultView.OfType <DataRowView>().Select(x => x.Row) : table.Rows.OfType <DataRow>())
{
ProgressUpdated(this, new EventArgs <int>(i - 1));
var data = new string[cols.Count];
for (var j = 0; j < data.Length; ++j)
{
data[j] = dr[j].ToString();
}
var excelRow = sheet.Range[sheet.Cells[i, 1], sheet.Cells[i, cols.Count]];
ApplyStyle(excelRow, parms.DefaultCellFormat);
excelRow.Value2 = data;
excelRow.Rows.AutoFit();
i++;
}
foreach (var colName in parms.ColumnParms.Keys)
{
var col = parms.ColumnParms[colName];
var colId = cols.IndexOf(colName) + 1;
var xlCol = sheet.Range[sheet.Cells[2, colId], sheet.Cells[totalCount + 1, colId]];
if (col.EnumValues != null)
{
xlCol.Validation.Add(ExcelApp.XlDVType.xlValidateList
, ExcelApp.XlDVAlertStyle.xlValidAlertInformation
, ExcelApp.XlFormatConditionOperator.xlBetween
, string.Join(";", col.EnumValues)
, Type.Missing);
xlCol.Validation.InCellDropdown = true;
xlCol.Validation.ShowError = false;
}
var style = excelWorkbook.Styles.Add("col#" + colId);
style.HorizontalAlignment = col.HorizontalAlignment;
style.VerticalAlignment = col.VerticalAlignment;
xlCol.Style = style;
foreach (var fcond in col.FormatConditions)
{
ExcelApp.FormatCondition cond = xlCol.FormatConditions.Add(fcond.Type, fcond.Operator, fcond.Formula);
ApplyBorderStyle(parms.DefaultCellFormat.Border, cond.Borders, true);
ApplyFontStyle(parms.DefaultCellFormat, cond.Font);
ApplyInteriorStyle(parms.DefaultCellFormat, cond.Interior);
ApplyBorderStyle(fcond.Format.Border, cond.Borders, true);
ApplyFontStyle(fcond.Format, cond.Font);
ApplyInteriorStyle(fcond.Format, cond.Interior);
}
ExcelApp.FormatCondition dummyCond = xlCol.FormatConditions.Add(ExcelApp.XlFormatConditionType.xlCellValue, ExcelApp.XlFormatConditionOperator.xlNotEqual, "I AM DUMB FOR DEFINING DEFAULT FORMAT");
ApplyBorderStyle(parms.DefaultCellFormat.Border, dummyCond.Borders, true);
ApplyFontStyle(parms.DefaultCellFormat, dummyCond.Font);
ApplyInteriorStyle(parms.DefaultCellFormat, dummyCond.Interior);
}
sheet.Application.ActiveWindow.SplitRow = 1;
sheet.Application.ActiveWindow.FreezePanes = true;
var firstRow = (ExcelApp.Range)sheet.Rows[1];
firstRow.AutoFilter(1, Type.Missing, ExcelApp.XlAutoFilterOperator.xlAnd, Type.Missing, true);
firstRow.EntireColumn.AutoFit();
var firstcol = (ExcelApp.Range)sheet.Columns[1];
firstcol.EntireRow.AutoFit();
excelApp.Visible = true;
ExportationEnded(this, new EventArgs <bool>(true));
}
catch
{
ExportationEnded(this, new EventArgs <bool>(false));
}
})).Start();
}
