static void InsertPicture(Workbook workbook)
{
#region #InsertPicture
workbook.BeginUpdate();
// Set the measurement unit to Millimeter.
workbook.Unit = DevExpress.Office.DocumentUnit.Millimeter;
try
{
Worksheet worksheet = workbook.Worksheets[0];
// Insert a picture from a file so that its top left corner is in the "A1" cell.
// Default picture names are Picture 1.. Picture NN.
worksheet.Pictures.AddPicture("Pictures\\x-docserver.png", worksheet.Cells["A1"]);
// Insert a picture at 70 mm from the left, 40 mm from the top,
// resize it to a width of 85 mm and a height of 25 mm, and lock the aspect ratio.
worksheet.Pictures.AddPicture("Pictures\\x-docserver.png", 70, 40, 85, 25, true);
// Insert a picture.
worksheet.Pictures.AddPicture("Pictures\\x-docserver.png", 0, 0);
// Find the last inserted picture by its name.
Picture picShape = worksheet.Pictures.GetPicturesByName("Picture 3")[0];
// Remove the last inserted picture.
picShape.Delete();
}
finally
{
workbook.EndUpdate();
}
#endregion #InsertPicture
}
static void ModifyPicture(Workbook workbook)
{
#region #ModifyPicture
// Set the measurement unit to Millimeter.
workbook.Unit = DevExpress.Office.DocumentUnit.Millimeter;
workbook.BeginUpdate();
try
{
Worksheet worksheet = workbook.Worksheets[0];
// Insert a picture from a file.
Picture pic = worksheet.Pictures.AddPicture("Pictures\\x-docserver.png", worksheet.Cells["A1"]);
// Specify the picture name and draw a border.
pic.Name = "Logo";
pic.AlternativeText = "Spreadsheet Logo";
pic.BorderWidth = 1;
pic.BorderColor = DevExpress.Utils.DXColor.Black;
// Move a picture.
pic.Move(20, 30);
// Specify picture behavior.
pic.Placement = Placement.MoveAndSize;
worksheet.Rows[5].Height += 10;
worksheet.Columns["D"].Width += 10;
// Specify the rotation angle.
pic.Rotation = 30;
// Add a hyperlink.
pic.InsertHyperlink("https://www.devexpress.com/products/net/office-file-api/", true);
}
finally
{
workbook.EndUpdate();
}
#endregion #ModifyPicture
}
static void Main()
{
#region #main
using (Workbook workbook = new Workbook())
{
workbook.Unit = DevExpress.Office.DocumentUnit.Inch;
// Create a mail merge template.
Worksheet template = workbook.Worksheets[0];
template.Rows[1].RowHeight = 1.5;
template.Columns[1].ColumnWidth = 1.0;
template.Columns[1].Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
template.Columns[2].ColumnWidth = 2.5;
template.Columns[2].Alignment.WrapText = true;
workbook.BeginUpdate();
template.Cells["C2"].Formula = "FIELDPICTURE(\"Photo\", \"range\", C2, FALSE, 50)";
template.Cells["C3"].Formula = "=FIELD(\"FirstName\")&\" \"&FIELD(\"LastName\")";
template.Cells["B4"].Value = "Position:";
template.Cells["C4"].Formula = "FIELD(\"Title\")";
template.Cells["B5"].Value = "Birth Date:";
template.Cells["C5"].Formula = "FIELD(\"BirthDate\")";
template.Cells["C5"].NumberFormat = "M/d/yyyy";
template.Cells["B6"].Value = "Hire Date:";
template.Cells["C6"].Formula = "FIELD(\"HireDate\")";
template.Cells["C6"].NumberFormat = "dddd MMMM dd, yyyy";
template.Cells["B7"].Value = "Home Phone:";
template.Cells["C7"].Formula = "FIELD(\"HomePhone\")";
template.Cells["B8"].Value = "Address:";
template.Cells["C8"].Formula = "=FIELD(\"Address\")&\" \"&FIELD(\"City\")";
template.Cells["B9"].Value = "About:";
template.Cells["C9"].Formula = "FIELD(\"Notes\")";
workbook.EndUpdate();
// Set a detail range in the template.
CellRange detail = template.Range["C1:C9"];
detail.Name = "DETAILRANGE";
// Set a header range in the template.
CellRange header = template.Range["B1:B9"];
header.Name = "HEADERRANGE";
// Switch the mail merge mode to "Multiple Sheets".
workbook.DefinedNames.Add("MAILMERGEMODE", "=\"Worksheets\"");
// Switch the mail merge mode to "Multiple Documents".
//workbook.DefinedNames.GetDefinedName("MAILMERGEMODE").RefersTo = "\"Documents\"";
// Switch the mail merge mode to "Single Sheet".
//workbook.DefinedNames.GetDefinedName("MAILMERGEMODE").RefersTo = "\"OneWorksheet\"";
// Set vertical document orientation.
workbook.DefinedNames.Add("HORIZONTALMODE", "=TRUE");
// Perform mail merge.
workbook.MailMergeDataSource = EmployeeInfo.EmployeesInfo.GetData();
var result = workbook.GenerateMailMergeDocuments();
result[0].SaveDocument("result.xlsx");
System.Diagnostics.Process.Start("result.xlsx");
#endregion #main
}
}
void evaluator_OnBeforeCompile(object sender, EventArgs e)
{
workbook.BeginUpdate();
codeEditor.BeforeCompile();
workbook.Options.Culture = defaultCulture;
bool loaded = workbook.LoadDocument("PivotTableTemplate.xlsx");
Debug.Assert(loaded);
}
void FillInvoiceSheet()
{
if (!Workbook.Worksheets.Contains(sheet.Name))
{
return;
}
Workbook.BeginUpdate();
try {
Range usedRange = sheet.GetUsedRange();
sheet.UnMergeCells(usedRange);
sheet.Clear(usedRange);
PrepareColumns(sheet);
PrepareRows(sheet);
Cell currentCell = sheet.Cells[0, 1];
currentCell.Font.Name = defaultLightFontName;
currentCell.Font.Size = 27;
currentCell.Alignment.Vertical = SpreadsheetVerticalAlignment.Bottom;
currentCell = sheet.Cells[2, 1];
currentCell.Font.FontStyle = SpreadsheetFontStyle.BoldItalic;
currentCell.Font.Size = 13.5;
SetLeftAlignedBoldValue(sheet.Cells[3, 4], "DATE:");
SetLeftAlignedBoldValue(sheet.Cells[4, 4], "INVOICE #:");
SetLeftAlignedBoldValue(sheet.Cells[5, 4], "FOR:");
PutWatermarkStyleValue(sheet.Cells[0, 5]);
currentCell = sheet.Cells[3, 5];
currentCell.NumberFormat = dateNumberFormat;
currentCell.Alignment.Horizontal = Spreadsheet.SpreadsheetHorizontalAlignment.Left;
currentCell.Formula = "=TODAY()";
currentCell = sheet.Cells[4, 5];
currentCell.NumberFormat = "0";
currentCell.Alignment.Horizontal = Spreadsheet.SpreadsheetHorizontalAlignment.Left;
currentCell.Value = 100;
currentCell = sheet.Cells[5, 5];
currentCell.Alignment.WrapText = true;
currentCell.Font.Italic = true;
currentCell.Value = "Project or service description";
currentCell = sheet.Cells[7, 1];
currentCell.Font.Size = 13.5;
currentCell.Font.Bold = true;
currentCell.Value = "BILL TO:";
FillInvoiceCore();
}
finally {
Workbook.EndUpdate();
}
}
void FillInvoice()
{
Workbook.BeginUpdate();
try {
FillInvoiceCore();
}
finally {
Workbook.EndUpdate();
}
}
static void ChangeSeriesArguments(Workbook workbook)
{
#region #ChangeSeriesArgumentsAndValues
Worksheet worksheet = workbook.Worksheets["Sheet1"];
workbook.Worksheets.ActiveWorksheet = worksheet;
workbook.BeginUpdate();
// Create a chart.
Chart chart = worksheet.Charts.Add(ChartType.LineMarker, worksheet[0, 0]);
// Specify arguments.
chart.Series[0].Arguments = new CellValue[] { 1, 2, 3 };
// Specify values.
chart.Series[0].Values = new CellValue[] { 30, 20, 10 };
workbook.EndUpdate();
#endregion #ChangeSeriesArgumentsAndValues
}
private void XLS_Load(object sender, EventArgs e)
{
// Create a new workbook.
Workbook workbook = new Workbook();
Worksheet worksheet = workbook.Worksheets[0];
// Access the first worksheet in the workbook.
// Set the unit of measurement.
workbook.Unit = DevExpress.Office.DocumentUnit.Point;
workbook.BeginUpdate();
try
{
// Create a multiplication table.
worksheet.Cells["A1"].Value = "*";
for (int i = 1; i < 11; i++)
{
// Create the header column.
worksheet.Columns["A"][i].Value = i;
// Create the header row.
worksheet.Rows["1"][i].Value = i;
}
// Multiply values of header cells.
worksheet.Range["B2:K11"].Formula = "=B$1*$A2";
// Obtain the data range.
CellRange tableRange = worksheet.GetDataRange();
// Specify the row height and column width.
tableRange.RowHeight = 40;
tableRange.ColumnWidth = 40;
// Align the table content.
tableRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
tableRange.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
// Fill the header cells.
CellRange headerCells = worksheet.Range.Union(worksheet.Range["A1:K1"], worksheet.Range["A2:A11"]);
headerCells.FillColor = Color.FromArgb(0xf7, 0x9b, 0x77);
headerCells.Font.Bold = true;
// Fill cells that contain multiplication results.
worksheet.Range["B2:K11"].FillColor = Color.FromArgb(0xfe, 0xf2, 0xe4);
}
finally
{
workbook.EndUpdate();
}
// Calculate the workbook.
workbook.Calculate();
// Save the document file under the specified name.
workbook.SaveDocument("TestDoc.xlsx", DocumentFormat.OpenXml);
// Export the document to PDF.
workbook.ExportToPdf("TestDoc.pdf");
// Open the PDF document using the default viewer.
System.Diagnostics.Process.Start("TestDoc.pdf");
// Open the XLSX document using the default application.
System.Diagnostics.Process.Start("TestDoc.xlsx");
}
private static byte[] CreateSpreadsheetReport(IList<QueryReportDataGroup> reportData)
{
using (var workbook = new Workbook())
{
workbook.BeginUpdate();
var sheet = workbook.Worksheets.First();
sheet.Name = "Запросы";
var i = 0;
var j = 1;
foreach (var pair in reportData.First().Values) //шапка по горизонтали
{
var cell = sheet.Cells[i, j++];
cell.Value = pair.Key.title;
cell.FillColor = Colors.Accent;
cell.Font.Color = Color.White;
cell.Borders.LeftBorder.Color = Color.White;
cell.Borders.RightBorder.Color = Color.White;
cell.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
}
j = 0;
var grouped = reportData.GroupBy(t =>
{
if (t.GroupHead == t.Head)
return String.Empty;
return t.GroupHead;
});
foreach (var group in grouped) //группы по вертикали
{
j = 0;
i++;
var groupRange = sheet.Range.FromLTRB(0, i, reportData.First().Values.Count, i);
sheet.MergeCells(groupRange, MergeCellsMode.ByColumns);
groupRange.ExistingCells.First().Value = group.Key;
groupRange.Borders.BottomBorder.LineStyle = BorderLineStyle.Medium;
groupRange.Font.FontStyle = SpreadsheetFontStyle.Bold;
groupRange.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
foreach (var data in group) //данные по вертикали
{
j = 0;
i++;
sheet.Cells[i, j++].Value = data.Head;
foreach (var pair in data.Values) //запрос-значение по горизонтали
{
var cell = sheet.Cells[i, j++];
cell.Value = pair.Value.ToString();
cell.Alignment.Horizontal = SpreadsheetHorizontalAlignment.Center;
cell.NumberFormat = "@";
}
}
}
var reportRange = sheet.Range.FromLTRB(0, 0, reportData.First().Values.Count, i);
reportRange.Borders.SetAllBorders(Colors.Accent, BorderLineStyle.Thin);
reportRange.AutoFitColumns();
reportRange.ColumnWidth = reportRange.ColumnWidth * 0.5;
reportRange.Alignment.WrapText = true;
reportRange.Alignment.Vertical = SpreadsheetVerticalAlignment.Center;
reportRange.AutoFitRows();
workbook.EndUpdate();
using (var stream = new MemoryStream())
{
workbook.SaveDocument(stream, DocumentFormat.Xlsx);
return stream.ToArray();
}
}
}
