XLSX: Edit XLSX content

In This Topic

VintaSoft Office .NET Plug-in has a set of classes, which allow to edit content of existing XLSX document. Classes are designed for fast and easy development of report/invoice generator based on XLSX document template.

If you want to generate XLSX report/invoice, you need to do the following steps:

Open Microsoft Excel:
- Create new XLSX document or open existing XLSX document - XLSX document will be used as a template for report/invoice.
- Change content of XLSX document. Document must contain all necessary markup except dynamic data. Places for dynamic data must be marked with unique text constants.
In the code:
- Open template document using XlsxDocumentEditor class and add dynamic data (replace text, insert table rows, replace images, etc) to the document.
- Save changed document to a XLSX file using OpenXmlDocumentEditor.Save method or export changed document using OpenXmlDocumentEditor.Export method to any format that is supported by VintaSoft Imaging SDK (PDF, PDF/A, TIFF, ...).

Report Generator Demo project contains several examples, which demonstrate how to generate reports, which are based on XLSX template.

XlsxDocumentEditor class

XlsxDocumentEditor class allows to:

open an existing XLSX document from a file or stream
get access to the sheets of XLSX document - XlsxDocumentEditor.Sheets
get access to the images of XLSX document - OpenXmlDocumentEditor.Images
get access to the charts of XLSX document - OpenXmlDocumentEditor.Charts
get access to the properties (title, creator, etc.) of XLSX document - OpenXmlDocumentEditor.DocumentProperties
save XLSX document to a file or stream - OpenXmlDocumentEditor.Save
export XLSX document to any format that is supported by VintaSoft Imaging SDK (PDF, PDF/A, TIFF, ...) - OpenXmlDocumentEditor.Export

OpenXmlDocumentElement class

OpenXmlDocumentElement represents a base class of all elements in a DOCX or XLSX document and allows to:

get or set text of element - OpenXmlDocumentElement.Text
find text in element - OpenXmlDocumentElement.FindText
replace text in element - OpenXmlDocumentElement.ReplaceText
set text properties in element - OpenXmlDocumentElement.SetTextProperties
set fill color of an element - OpenXmlDocumentElement.SetFillColor
remove this element from document - OpenXmlDocumentElement.Remove
insert copy of this element after self - OpenXmlDocumentElement.InsertCopyAfterSelf
insert copy of this element before self - OpenXmlDocumentElement.InsertCopyBeforeSelf
copy this element - OpenXmlDocumentElement.Clone
access parent element of this element - OpenXmlDocumentElement.Parent
access child elements of this element - OpenXmlDocumentElement.ChildElements

Here is an example that shows how to find and replace text in XLSX document (example uses template document FindAndReplaceText_template.xlsx):

VB.NET

' The project, which uses this code, must have references to the following assemblies:
' - Vintasoft.Imaging
' - Vintasoft.Imaging.Office.OpenXml

Public Shared Sub XlsxFindAndReplaceTextExample()
    Dim templateFilename As String = "FindAndReplaceText_template.xlsx"
    Dim outFilename As String = "FindAndReplaceText.xlsx"
    Dim outPdfFilename As String = "FindAndReplaceText.pdf"

    ' create XlsxDocumentEditor that allows to edit file "FindAndReplaceText_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet = editor.Sheets(0)

        ' replace first occurrence of text "[field1]" by text "value1"
        sheet("[field1]") = "value1"

        ' replace all occurrences of text "[field2]" by text "value2"
        sheet.ReplaceText("[field2]", "value2")

        ' find text content that corresponds to the text "[field3]"
        Dim field3Content As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextContent = sheet.FindText("[field3]")
        ' change text in found text content
        field3Content.Text = "value3"

        ' replace text "[multiline_field]" by multiline text
        sheet("[multiline_field]") = vbLf & "line1" & vbLf & "line2" & vbLf & "line3"

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

// The project, which uses this code, must have references to the following assemblies:
// - Vintasoft.Imaging
// - Vintasoft.Imaging.Office.OpenXml

public static void XlsxFindAndReplaceTextExample()
{
    string templateFilename = "FindAndReplaceText_template.xlsx";
    string outFilename = "FindAndReplaceText.xlsx";
    string outPdfFilename = "FindAndReplaceText.pdf";

    // create XlsxDocumentEditor that allows to edit file "FindAndReplaceText_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet sheet = editor.Sheets[0];

        // replace first occurrence of text "[field1]" by text "value1"
        sheet["[field1]"] = "value1";

        // replace all occurrences of text "[field2]" by text "value2"
        sheet.ReplaceText("[field2]", "value2");

        // find text content that corresponds to the text "[field3]"
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextContent field3Content = sheet.FindText("[field3]");
        // change text in found text content
        field3Content.Text = "value3";

        // replace text "[multiline_field]" by multiline text
        sheet["[multiline_field]"] = "\nline1\nline2\nline3";

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

OpenXmlTextContent class

OpenXmlTextContent represents the text content of DOCX or XLSX document and allows to:

replace text - OpenXmlTextContent.Text
set text properties - OpenXmlTextContent.SetTextProperties

Here is an example that shows how to change text properties of text content (example uses template document ChangeTextProperties_template.xlsx):

VB.NET

' The project, which uses this code, must have references to the following assemblies:
' - Vintasoft.Imaging
' - Vintasoft.Imaging.Office.OpenXml

Public Shared Sub XlsxChangeTextPropertiesExample()
    Dim templateFilename As String = "ChangeTextProperties_template.xlsx"
    Dim outFilename As String = "ChangeTextProperties.xlsx"
    Dim outPdfFilename As String = "ChangeTextProperties.pdf"

    ' create XlsxDocumentEditor that allows to edit file "ChangeTextProperties_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet = editor.Sheets(0)

        ' set text color
        sheet.FindText("COLOR").Substring(0, 2).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Red))
        sheet.FindText("COLOR").Substring(2, 1).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Green))
        sheet.FindText("COLOR").Substring(3, 2).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Blue))

        ' set text "bold text" as bold text
        sheet.FindText("bold text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.BoldText)

        ' set text "italic text" as italic text
        sheet.FindText("italic text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.ItalicText)

        ' set text "underline text" as underline text
        sheet.FindText("underline text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.UnderlineText)

        ' set text "strike text" as striked out text
        sheet.FindText("strike text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.StrikeText)

        ' change font size
        Dim setTextSize As New Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties()
        setTextSize.FontSize = 16
        sheet.FindText("text with size 16pt").Substring(0, 4).SetTextProperties(setTextSize)

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

''' <summary>
''' Creates the color text properties.
''' </summary>
''' <param name="color">The color.</param>
Private Shared Function CreateColorTextProperties(color As System.Drawing.Color) As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties
    Dim result As New Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties()
    result.Color = color
    Return result
End Function

// The project, which uses this code, must have references to the following assemblies:
// - Vintasoft.Imaging
// - Vintasoft.Imaging.Office.OpenXml

public static void XlsxChangeTextPropertiesExample()
{
    string templateFilename = "ChangeTextProperties_template.xlsx";
    string outFilename = "ChangeTextProperties.xlsx";
    string outPdfFilename = "ChangeTextProperties.pdf";

    // create XlsxDocumentEditor that allows to edit file "ChangeTextProperties_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet sheet = editor.Sheets[0];

        // set text color
        sheet.FindText("COLOR").Substring(0, 2).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Red));
        sheet.FindText("COLOR").Substring(2, 1).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Green));
        sheet.FindText("COLOR").Substring(3, 2).SetTextProperties(CreateColorTextProperties(System.Drawing.Color.Blue));

        // set text "bold text" as bold text
        sheet.FindText("bold text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.BoldText);

        // set text "italic text" as italic text
        sheet.FindText("italic text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.ItalicText);

        // set text "underline text" as underline text
        sheet.FindText("underline text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.UnderlineText);

        // set text "strike text" as striked out text
        sheet.FindText("strike text").SetTextProperties(Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties.StrikeText);

        // change font size
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties setTextSize = 
            new Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties();
        setTextSize.FontSize = 16;
        sheet.FindText("text with size 16pt").Substring(0, 4).SetTextProperties(setTextSize);

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

/// <summary>
/// Creates the color text properties.
/// </summary>
/// <param name="color">The color.</param>
private static Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties CreateColorTextProperties(System.Drawing.Color color)
{
    Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties result =
        new Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlTextProperties();
    result.Color = color;
    return result;
}

XlsxDocumentSheet class

XlsxDocumentSheet represents the sheet of XLSX document and allows to:

access rows of sheet - OpenXmlDocumentElement.ChildElements
find sheet cell by name (A1 format) - XlsxDocumentSheet.FindCellByName
find sheet cell by column and row number - XlsxDocumentSheet.FindCell
find row that contains specified text - OpenXmlDocumentTable.FindRow
find cell that contains specified text - OpenXmlDocumentTable.FindCell
set borders from sample cell - OpenXmlDocumentTable.SetOutsideBorder
get or set sheet name - XlsxDocumentSheet.Name

XlsxDocumentSheetRow class

XlsxDocumentSheetRow represents the row of XLSX sheet and allows to:

access cells of row - OpenXmlDocumentElement.ChildElements
get row number - XlsxDocumentSheetRow.RowNumber
set row height - OpenXmlDocumentTableRow.Height
find cell that contains text - XlsxDocumentSheetRow.FindCell
set row borders from sample cell - OpenXmlDocumentTableRow.SetBorder
set fill color of row - OpenXmlDocumentElement.SetFillColor
insert copy of row after self - OpenXmlDocumentElement.InsertCopyAfterSelf
insert copy of row before self - OpenXmlDocumentElement.InsertCopyBeforeSelf
copy row - XlsxDocumentSheetRow.Clone
remove row from sheet - OpenXmlDocumentElement.Remove

Here is an example that shows how to fill data table (example uses template document CopyTableRow_template.xlsx):

VB.NET

' The project, which uses this code, must have references to the following assemblies:
' - Vintasoft.Imaging
' - Vintasoft.Imaging.Office.OpenXml

Public Shared Sub XlsxCopyTableRowExample()
    Dim templateFilename As String = "CopyTableRow_template.xlsx"
    Dim outFilename As String = "CopyTableRow.xlsx"
    Dim outPdfFilename As String = "CopyTableRow.pdf"

    ' create XlsxDocumentEditor that allows to edit file "CopyTableRow_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet = editor.Sheets(0)

        ' find  row that contains text "[cell1]"
        Dim templateRow As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow = sheet.FindRow("[cell1]")

        ' create an array that contains colors
        Dim colors As System.Drawing.Color() = New System.Drawing.Color() {System.Drawing.Color.Red, System.Drawing.Color.Green, System.Drawing.Color.Blue, System.Drawing.Color.Orange, System.Drawing.Color.Yellow}
        ' for each color
        For i As Integer = 0 To colors.Length - 1
            ' insert copy of template row before template row
            Dim rowCopy As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow = DirectCast(templateRow.InsertCopyBeforeSelf(), Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow)

            ' set row data
            rowCopy(0).Text = String.Format("Copy {0} ({1})", i, colors(i))
            rowCopy("[cell1]") = String.Format("cell data {0}", i)

            ' set cell colors
            rowCopy(1).SetFillColor(colors(i))
            rowCopy(2).SetFillColor(colors(i))

            ' if color has odd index in colors array
            If i Mod 2 = 1 Then
                ' set row height to 10mm
                rowCopy.Height = Vintasoft.Imaging.Utils.UnitOfMeasureConverter.ConvertToPoints(10, Vintasoft.Imaging.UnitOfMeasure.Millimeters)
            End If
        Next

        ' remove template row
        templateRow.Remove()

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

// The project, which uses this code, must have references to the following assemblies:
// - Vintasoft.Imaging
// - Vintasoft.Imaging.Office.OpenXml

public static void XlsxCopyTableRowExample()
{
    string templateFilename = "CopyTableRow_template.xlsx";
    string outFilename = "CopyTableRow.xlsx";
    string outPdfFilename = "CopyTableRow.pdf";

    // create XlsxDocumentEditor that allows to edit file "CopyTableRow_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet sheet = editor.Sheets[0];

        // find  row that contains text "[cell1]"
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow templateRow = sheet.FindRow("[cell1]");

        // create an array that contains colors
        System.Drawing.Color[] colors = new System.Drawing.Color[] {
            System.Drawing.Color.Red,
            System.Drawing.Color.Green,
            System.Drawing.Color.Blue,
            System.Drawing.Color.Orange,
            System.Drawing.Color.Yellow
        };
        // for each color
        for (int i = 0; i < colors.Length; i++)
        {
            // insert copy of template row before template row
            Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow rowCopy = 
                (Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableRow)templateRow.InsertCopyBeforeSelf();

            // set row data
            rowCopy[0].Text = string.Format("Copy {0} ({1})", i, colors[i]);
            rowCopy["[cell1]"] = string.Format("cell data {0}", i);

            // set cell colors
            rowCopy[1].SetFillColor(colors[i]);
            rowCopy[2].SetFillColor(colors[i]);

            // if color has odd index in colors array
            if (i % 2 == 1)
            {
                // set row height to 10mm
                rowCopy.Height = Vintasoft.Imaging.Utils.UnitOfMeasureConverter.ConvertToPoints(10, Vintasoft.Imaging.UnitOfMeasure.Millimeters);
            }
        }

        // remove template row
        templateRow.Remove();

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

Here is an example that shows how to set table borders from sample table cell (example uses template document SetTableBorders_template.xlsx):

VB.NET

' The project, which uses this code, must have references to the following assemblies:
' - Vintasoft.Imaging
' - Vintasoft.Imaging.Office.OpenXml

Public Shared Sub XlsxSetTableBordersExample()
    Dim templateFilename As String = "SetTableBorders_template.xlsx"
    Dim outFilename As String = "SetTableBorders.xlsx"
    Dim outPdfFilename As String = "SetTableBorders.pdf"

    ' create XlsxDocumentEditor that allows to edit file "SetTableBorders_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet = editor.Sheets(0)

        ' get cell border templates
        Dim boldBorderTemplate As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableCell = sheet.FindCell("[bold]")
        Dim colorBorderTemplate As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableCell = sheet.FindCell("[color]")

        ' set borders from template cells
        sheet.FindCell("[bold_cell]").SetBorder(boldBorderTemplate)
        sheet.FindCell("[color_cell]").SetBorder(colorBorderTemplate)

        ' set outside border inside table
        sheet.SetOutsideBorder(sheet.FindCell("[color_first]"), sheet.FindCell("[color_last]"), colorBorderTemplate)
        sheet.SetOutsideBorder(sheet.FindCell("[bold_first]"), sheet.FindCell("[bold_last]"), boldBorderTemplate)

        ' remove border template table
        Dim removingRow As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentElement = boldBorderTemplate.Row.Prev
        For i As Integer = 0 To 4
            Dim row As Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentElement = removingRow
            removingRow = removingRow.[Next]
            row.Remove()
        Next

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

// The project, which uses this code, must have references to the following assemblies:
// - Vintasoft.Imaging
// - Vintasoft.Imaging.Office.OpenXml

public static void XlsxSetTableBordersExample()
{
    string templateFilename = "SetTableBorders_template.xlsx";
    string outFilename = "SetTableBorders.xlsx";
    string outPdfFilename = "SetTableBorders.pdf";

    // create XlsxDocumentEditor that allows to edit file "SetTableBorders_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet sheet = editor.Sheets[0];

        // get cell border templates
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableCell boldBorderTemplate = 
            sheet.FindCell("[bold]");
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentTableCell colorBorderTemplate =
            sheet.FindCell("[color]");

        // set borders from template cells
        sheet.FindCell("[bold_cell]").SetBorder(boldBorderTemplate);
        sheet.FindCell("[color_cell]").SetBorder(colorBorderTemplate);

        // set outside border inside table
        sheet.SetOutsideBorder(sheet.FindCell("[color_first]"), sheet.FindCell("[color_last]"), colorBorderTemplate);
        sheet.SetOutsideBorder(sheet.FindCell("[bold_first]"), sheet.FindCell("[bold_last]"), boldBorderTemplate);

        // remove border template table
        Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentElement removingRow = boldBorderTemplate.Row.Prev;
        for (int i = 0; i < 5; i++)
        {
            Vintasoft.Imaging.Office.OpenXml.Editor.OpenXmlDocumentElement row = removingRow;
            removingRow = removingRow.Next;
            row.Remove();
        }

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

XlsxDocumentSheetCell class

XlsxDocumentSheetCell represents the cell of XLSX sheet and allows to:

get cell name in A1 format - XlsxDocumentSheetCell.Name
get column number of cell - XlsxDocumentSheetCell.ColumnNumber
get row number of cell - XlsxDocumentSheetCell.RowNumber
get or set the text that is stored in cell - OpenXmlDocumentElement.Text
get or set the number that is stored in cell - XlsxDocumentSheetCell.Number
get or set the formula, which is used to calculate value of cell - XlsxDocumentSheetCell.Formula
get or set to style index of cell - XlsxDocumentSheetCell.StyleIndex
set cell borders from sample cell - XlsxDocumentSheetCell.SetBorder
set fill color of cell - XlsxDocumentSheetCell.SetFillColor
set text properties of cell - XlsxDocumentSheetCell.SetTextProperties

Here is an example that shows how to find cell by reference and set cell formula (example uses template document XlsxCell_template.xlsx):

VB.NET

' The project, which uses this code, must have references to the following assemblies:
' - Vintasoft.Imaging
' - Vintasoft.Imaging.Office.OpenXml

Public Shared Sub XlsxCellExample()
    Dim templateFilename As String = "XlsxCell_template.xlsx"
    Dim outFilename As String = "XlsxCell.xlsx"
    Dim outPdfFilename As String = "XlsxCell.pdf"

    ' create XlsxDocumentEditor that allows to edit file "SetBarcodeImage_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet = editor.Sheets(0)

        ' find cell by reference (A1 format) and set cell fill color
        sheet.FindCellByName("B3").SetFillColor(System.Drawing.Color.Red)
        sheet.FindCellByName("D3").SetFillColor(System.Drawing.Color.Yellow)
        sheet.FindCellByName("C4").SetFillColor(System.Drawing.Color.Green)
        sheet.FindCellByName("D5").SetFillColor(System.Drawing.Color.Blue)
        sheet.FindCellByName("B5").SetFillColor(System.Drawing.Color.Pink)

        ' set cell formula
        Dim cellD16 As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetCell = sheet.FindCellByName("D8")
        cellD16.Formula = "SUM(B8,C8)"
        cellD16.Number = sheet.FindCellByName("B8").Number + sheet.FindCellByName("C8").Number

        ' find cell that contains text "[cell_name]"
        Dim cell As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetCell = sheet.FindCell("[cell_name]")
        ' set cell text to cell name in A1 format
        cell.Text = cell.Name

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

// The project, which uses this code, must have references to the following assemblies:
// - Vintasoft.Imaging
// - Vintasoft.Imaging.Office.OpenXml

public static void XlsxCellExample()
{
    string templateFilename = "XlsxCell_template.xlsx";
    string outFilename = "XlsxCell.xlsx";
    string outPdfFilename = "XlsxCell.pdf";

    // create XlsxDocumentEditor that allows to edit file "SetBarcodeImage_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet sheet = editor.Sheets[0];

        // find cell by reference (A1 format) and set cell fill color
        sheet.FindCellByName("B3").SetFillColor(System.Drawing.Color.Red);
        sheet.FindCellByName("D3").SetFillColor(System.Drawing.Color.Yellow);
        sheet.FindCellByName("C4").SetFillColor(System.Drawing.Color.Green);
        sheet.FindCellByName("D5").SetFillColor(System.Drawing.Color.Blue);
        sheet.FindCellByName("B5").SetFillColor(System.Drawing.Color.Pink);

        // set cell formula
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetCell cellD16 = sheet.FindCellByName("D8");
        cellD16.Formula = "SUM(B8,C8)";
        cellD16.Number = sheet.FindCellByName("B8").Number + sheet.FindCellByName("C8").Number;

        // find cell that contains text "[cell_name]"
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetCell cell = sheet.FindCell("[cell_name]");
        // set cell text to cell name in A1 format
        cell.Text = cell.Name;

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

OpenXmlDocumentImage class

OpenXmlDocumentImage represents an image of DOCX or XLSX document:

get image as VintasoftImage - OpenXmlDocumentImage.GetImage
set image from VintasoftImage - OpenXmlDocumentImage.SetImage

Here is an example that shows how to set barcode image in XLSX document (example uses template document SetBarcodeImage_template.xlsx):

VB.NET

' The project, which uses this code, must have references to the following assemblies:
' - Vintasoft.Imaging
' - Vintasoft.Imaging.Office.OpenXml

Public Shared Sub XlsxSetBarcodeImageExample()
    Dim templateFilename As String = "SetBarcodeImage_template.xlsx"
    Dim outFilename As String = "SetBarcodeImage.xlsx"
    Dim outPdfFilename As String = "SetBarcodeImage.pdf"

    ' create XlsxDocumentEditor that allows to edit file "SetBarcodeImage_template.xlsx"
    Using editor As New Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename)
        ' get first sheet in XLSX document
        Dim sheet As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet = editor.Sheets(0)

        ' find cell with text "Invoice number:"
        Dim cell As Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetCell = sheet.FindCell("Invoice number:")

        ' get barcode value as text of next row after "Invoice number:" cell
        Dim barcodeValue As String = sheet.FindCell(cell.ColumnNumber, cell.RowNumber + 1).Text

        ' create and setup barcode generator
        Dim barcodeGenerator As New Vintasoft.Barcode.BarcodeWriter()
        barcodeGenerator.Settings.Barcode = Vintasoft.Barcode.BarcodeType.QR
        barcodeGenerator.Settings.SetWidth(200)
        barcodeGenerator.Settings.Value = barcodeValue

        ' generate barcode image
        Using barcodeImage As New Vintasoft.Imaging.VintasoftImage(barcodeGenerator.GetBarcodeAsBitmap(), True)
            ' crop barcode to the rectangular image
            barcodeImage.Crop(New System.Drawing.Rectangle(0, 0, barcodeImage.Width, barcodeImage.Width))

            ' set barcode image to the DOCX image at index 0
            editor.Images(0).SetImage(barcodeImage)
        End Using

        ' save changed document to a XLSX file
        editor.Save(outFilename)

        ' export changed document to a PDF document
        editor.Export(outPdfFilename)
    End Using
End Sub

// The project, which uses this code, must have references to the following assemblies:
// - Vintasoft.Imaging
// - Vintasoft.Imaging.Office.OpenXml

public static void XlsxSetBarcodeImageExample()
{
    string templateFilename = "SetBarcodeImage_template.xlsx";
    string outFilename = "SetBarcodeImage.xlsx";
    string outPdfFilename = "SetBarcodeImage.pdf";

    // create XlsxDocumentEditor that allows to edit file "SetBarcodeImage_template.xlsx"
    using (Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor editor =
        new Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentEditor(templateFilename))
    {
        // get first sheet in XLSX document
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheet sheet = editor.Sheets[0];

        // find cell with text "Invoice number:"
        Vintasoft.Imaging.Office.OpenXml.Editor.XlsxDocumentSheetCell cell = sheet.FindCell("Invoice number:");

        // get barcode value as text of next row after "Invoice number:" cell
        string barcodeValue = sheet.FindCell(cell.ColumnNumber, cell.RowNumber + 1).Text;

        // create and setup barcode generator
        Vintasoft.Barcode.BarcodeWriter barcodeGenerator = new Vintasoft.Barcode.BarcodeWriter();
        barcodeGenerator.Settings.Barcode = Vintasoft.Barcode.BarcodeType.QR;
        barcodeGenerator.Settings.SetWidth(200);
        barcodeGenerator.Settings.Value = barcodeValue;

        // generate barcode image
        using (Vintasoft.Imaging.VintasoftImage barcodeImage =
            new Vintasoft.Imaging.VintasoftImage(barcodeGenerator.GetBarcodeAsBitmap(), true))
        {
            // crop barcode to the rectangular image
            barcodeImage.Crop(new System.Drawing.Rectangle(0, 0, barcodeImage.Width, barcodeImage.Width));

            // set barcode image to the DOCX image at index 0
            editor.Images[0].SetImage(barcodeImage);
        }

        // save changed document to a XLSX file
        editor.Save(outFilename);

        // export changed document to a PDF document
        editor.Export(outPdfFilename);
    }
}

OpenXmlDocumentChart class

OpenXmlDocumentChart represents a chart of DOCX or XLSX document:

get the chart title - OpenXmlDocumentChart.Title
get the chart properties - OpenXmlDocumentChart.Properties
get the chart data - OpenXmlDocumentChart.ChartData

Send Feedback