static void FitPicture(Stream stream, XlDocumentFormat documentFormat)
{
// 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())
{
sheet.SkipColumns(1);
// Create the column "B" and set its width.
using (IXlColumn column = sheet.CreateColumn())
{
column.WidthInPixels = 300;
}
sheet.SkipRows(1);
// Create the second row and set its height.
using (IXlRow row = sheet.CreateRow())
{
row.HeightInPixels = 154;
}
#region #FitPicture
// Insert a picture from a file to fit in the cell B2.
using (IXlPicture picture = sheet.CreatePicture())
{
picture.Image = Image.FromFile(Path.Combine(imagesPath, "image1.jpg"));
picture.FitToCell(new XlCellPosition(1, 1), 300, 154, true);
}
}
#endregion #FitPicture
}
}
static void PrintArea(Stream stream, XlDocumentFormat documentFormat)
{
// 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()) {
#region #PrintArea
// Set the print area to cells A1:E5.
sheet.PrintArea = XlCellRange.FromLTRB(0, 0, 4, 4);
#endregion #PrintArea
// Create worksheet columns and set their widths.
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 110;
column.Formatting = XlCellAlignment.FromHV(XlHorizontalAlignment.Left, XlVerticalAlignment.Bottom);
}
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 190;
}
for (int i = 0; i < 2; i++)
{
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 90;
column.Formatting = new XlCellFormatting();
column.Formatting.NumberFormat = @"_([$$-409]* #,##0.00_);_([$$-409]* \(#,##0.00\);_([$$-409]* ""-""??_);_(@_)";
}
}
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 130;
}
sheet.SkipColumns(1);
using (IXlColumn column = sheet.CreateColumn()) {
column.WidthInPixels = 130;
}
// Specify formatting settings for cells containing data.
XlCellFormatting rowFormatting = new XlCellFormatting();
rowFormatting.Font = new XlFont();
rowFormatting.Font.Name = "Century Gothic";
rowFormatting.Font.SchemeStyle = XlFontSchemeStyles.None;
// Specify formatting settings for the header row.
XlCellFormatting headerRowFormatting = new XlCellFormatting();
headerRowFormatting.CopyFrom(rowFormatting);
headerRowFormatting.Font.Bold = true;
headerRowFormatting.Font.Color = XlColor.FromTheme(XlThemeColor.Light1, 0.0);
headerRowFormatting.Fill = XlFill.SolidFill(XlColor.FromTheme(XlThemeColor.Accent2, 0.0));
// Generate the header row.
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Employee ID";
cell.ApplyFormatting(headerRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Employee name";
cell.ApplyFormatting(headerRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Salary";
cell.ApplyFormatting(headerRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Bonus";
cell.ApplyFormatting(headerRowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Department";
cell.ApplyFormatting(headerRowFormatting);
}
row.SkipCells(1);
using (IXlCell cell = row.CreateCell()) {
cell.Value = "Departments";
cell.ApplyFormatting(headerRowFormatting);
}
}
// Generate data for the document.
int[] id = new int[] { 10115, 10709, 10401, 10204 };
string[] name = new string[] { "Augusta Delono", "Chris Cadwell", "Frank Diamond", "Simon Newman" };
int[] salary = new int[] { 1100, 2000, 1750, 1250 };
int[] bonus = new int[] { 50, 180, 100, 80 };
int[] deptid = new int[] { 0, 2, 3, 3 };
string[] department = new string[] { "Accounting", "IT", "Management", "Manufacturing" };
for (int i = 0; i < 4; i++)
{
using (IXlRow row = sheet.CreateRow()) {
using (IXlCell cell = row.CreateCell()) {
cell.Value = id[i];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = name[i];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = salary[i];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = bonus[i];
cell.ApplyFormatting(rowFormatting);
}
using (IXlCell cell = row.CreateCell()) {
cell.Value = department[deptid[i]];
cell.ApplyFormatting(rowFormatting);
}
row.SkipCells(1);
using (IXlCell cell = row.CreateCell()) {
cell.Value = department[i];
cell.ApplyFormatting(rowFormatting);
}
}
}
// Restrict data entry in the cell range E2:E5 to values in a drop-down list obtained from the cells G2:G5.
XlDataValidation validation = new XlDataValidation();
validation.Ranges.Add(XlCellRange.FromLTRB(4, 1, 4, 4));
validation.Type = XlDataValidationType.List;
validation.Criteria1 = XlCellRange.FromLTRB(6, 1, 6, 4).AsAbsolute();
sheet.DataValidations.Add(validation);
}
}
}