Usually a price-list is a table containing information about goods or services (description, photo, quantity, price).
For representation of such table suits well the XLSX format.
Since version 10.1 of VintaSoft Imaging .NET SDK became possible to edit existing DOCX and XLSX documents programmatically.
One can use this feature to create a simple and easily customizable generator of price-lists in XLSX document format.
More details about the editing of XLSX documents programmatically one can read
here.
It is necessary to perform the following steps to create the price-list generator in XLSX document format:
1. Create a document template (Pricelist_template.xlsx) using MS Excel.
2. Write the programming code that will fill in the template with dynamic data.
Here is the code, which shows how to create a price-list and save it to XLSX document:
// The project, which uses this code, must have references to the following assemblies:
// - Vintasoft.Imaging
// - Vintasoft.Imaging.Office.OpenXml
/// <summary>
/// Generates XLSX pricelist, which is based on XLSX document template.
/// </summary>
public static void GenerateXlsxPricelist()
{
// create XLSX document editor and use file "Pricelist_template.xlsx" as document template
using (Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor editor =
new Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor("Pricelist_template.xlsx"))
{
// generate test pricelist data
PriceListItem[] testData = GetTestData();
// fill pricelist data
FillPricelistData(editor, testData);
// save pricelist to a XLSX document
editor.Save("Pricelist.xlsx");
}
}
/// <summary>
/// Fills the pricelist data using XLSX document editor.
/// </summary>
/// <param name="documentEditor">The DOCX document editor.</param>
/// <param name="pricelistData">The pricelist data.</param>
private static void FillPricelistData(
Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor documentEditor,
PriceListItem[] pricelistData)
{
// set current date in document
documentEditor.Body["[date]"] = System.DateTime.Now.ToShortDateString();
// get the first sheet in document
Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet sheet = documentEditor.Sheets[0];
// get template row in document
Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetRow templateRow = sheet.FindRow("[n]");
int itemNumber = 1;
// for each item in price list
foreach (PriceListItem item in pricelistData)
{
// copy template row and insert copy after template row
Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetRow currentRow = templateRow;
templateRow = (Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetRow)templateRow.InsertCopyAfterSelf();
// fill data in current row
currentRow.FindCell("[n]").Number = itemNumber;
currentRow.FindCell("[product]").Text = item.Product;
currentRow.FindCell("[price]").Number = item.Price;
// get image object, which stores product image, in current row
Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentImage image = sheet.FindImages(currentRow)[0];
// if product has image
if (!string.IsNullOrEmpty(item.ImageId))
{
// set product image in image object
using (Vintasoft.Imaging.VintasoftImage productImage = new Vintasoft.Imaging.VintasoftImage(item.ImageId))
image.SetImage(productImage, true);
}
else
{
// remove image object from current row
image.Remove();
}
itemNumber++;
}
// remove template row from result document
templateRow.Remove();
}
/// <summary>
/// Returns the price list test data.
/// </summary>
/// <returns>The price list test data.</returns>
public static PriceListItem[] GetTestData()
{
return new PriceListItem[] {
new PriceListItem("VintaSoft Imaging .NET SDK, Developer license for Desktop PCs", "vsimaging-icon.png", 219.95f),
new PriceListItem("VintaSoft Imaging .NET SDK, Developer license for Servers", "vsimaging-icon.png", 549.95f),
new PriceListItem("VintaSoft Imaging .NET SDK, Site license for Desktop PCs", "vsimaging-icon.png", 659.95f),
new PriceListItem("VintaSoft Imaging .NET SDK, Site license for Servers", "vsimaging-icon.png", 1649.95f),
new PriceListItem("VintaSoft Imaging .NET SDK, Single Server license", "vsimaging-icon.png", 164.95f),
new PriceListItem("VintaSoft Annotation .NET Plug-in, Site license for Desktop PCs", "vsannotation-icon.png", 449.95f),
new PriceListItem("VintaSoft Office .NET Plug-in, Site license for Desktop PCs", "vsoffice-icon.png", 569.95f),
new PriceListItem("VintaSoft PDF .NET Plug-in (Reader+Writer), Site license for Desktop PCs", "vspdf-icon.png", 1499.95f),
new PriceListItem("VintaSoft PDF .NET Plug-in (Reader+Writer+VisualEditor), Site license for Desktop PCs", "vspdf-icon.png",2999.95f),
new PriceListItem("VintaSoft JBIG2 .NET Plug-in, Site license for Desktop PCs", "vsjbig2-icon.png",1139.95f),
new PriceListItem("VintaSoft JPEG2000 .NET Plug-in, Site license for Desktop PCs", "vsjpeg2000-icon.png", 689.95f),
new PriceListItem("VintaSoft Document Cleaup .NET Plug-in, Site license for Desktop PCs", "vsdoccleanup-icon.png", 569.95f),
new PriceListItem("VintaSoft OCR .NET Plug-in, Site license for Desktop PCs", "vsocr-icon.png", 509.95f),
new PriceListItem("VintaSoft DICOM .NET Plug-in (Codec+MPR), Site license for Desktop PCs", "vsdicom-icon.png", 1199.95f),
new PriceListItem("VintaSoft Forms Processing .NET Plug-in, Site license for Desktop PCs", "vsformsprocessing-icon.png", 509.95f),
new PriceListItem("VintaSoft Barcode .NET SDK (1D+2D Reader+Writer), Site license for Desktop PCs", "vsbarcode-icon.png", 1379.95f),
new PriceListItem("VintaSoft Twain .NET SDK, Developer license", "vstwain-icon.png", 179.95f),
new PriceListItem("VintaSoft Twain .NET SDK, Site license", "vstwain-icon.png", 539.95f),
new PriceListItem("VintaSoft Twain .NET SDK, Single URL license", "vstwain-icon.png", 149.95f),
new PriceListItem("VintaSoft Twain ActiveX, Developer license", "vstwain-icon.png", 99.95f),
new PriceListItem("VintaSoft Twain ActiveX, Site license", "vstwain-icon.png", 299.95f),
new PriceListItem("VintaSoft Twain ActiveX, Single URL license", "vstwain-icon.png", 119.95f)
};
}
/// <summary>
/// Represents a price list item.
/// </summary>
public class PriceListItem
{
/// <summary>
/// Initializes a new instance of the <see cref="PriceListItem"/> class.
/// </summary>
/// <param name="product">The product name.</param>
/// <param name="imageId">Th image ID.</param>
/// <param name="price">The product price.</param>
public PriceListItem(string product, string imageId, float price)
{
Product = product;
ImageId = imageId;
Price = price;
}
/// <summary>
/// Gets the product name.
/// </summary>
public string Product;
/// <summary>
/// Gets the image Id.
/// </summary>
public string ImageId;
/// <summary>
/// Gets the product price.
/// </summary>
public float Price;
}
