How to set invert-if-negative in charts in .NET Excel Library

17 Aug 20265 minutes to read

The following code samples demonstrate how to set the “Invert if negative” option for a chart using C# (Cross-platform and Windows-specific) and VB.NET for XLSX files. For XLS (binary) file format, see the section below.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    #region Workbook Initialization
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open(Path.GetFullPath(@"Data/InputTemplate.xlsx"));
    IWorksheet worksheet = workbook.Worksheets[0];
    #endregion

    IChart chart = worksheet.Charts[0];

    //Used to invert series color if the value is negative
    (chart.Series[0] as ChartSerieImpl).InvertIfNegative = true;

    #region Save
    //Saving the workbook
    workbook.SaveAs(Path.GetFullPath("Output/Output.xlsx"));
    #endregion
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    #region Workbook Initialization
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xlsx");
    IWorksheet worksheet = workbook.Worksheets[0];
    #endregion

    IChart chart = worksheet.Charts[0];

    //Used to invert series color if the value is negative
    (chart.Series[0] as ChartSerieImpl).InvertIfNegative = true;

    #region Save
    //Saving the workbook
    workbook.SaveAs("Output.xlsx");
    #endregion
}

VB.NET [Windows-specific]

Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Xlsx
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xlsx")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    Dim chart As IChart = worksheet.Charts(0)

    'Used to invert series color if the value is negative
    DirectCast(chart.Series(0), ChartSerieImpl).InvertIfNegative = True

    'Saving the workbook
    workbook.SaveAs("Output.xlsx")
End Using

A complete working example in C# is present on this GitHub page.

How to set invert if negative option for a chart in XLS (binary) file format?

In case of binary file format (XLS), kindly use the following code snippet.

C# [Cross-platform]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Excel97to2003;
    IWorkbook workbook = application.Workbooks.Open("InputTemplate.xls");
    IWorksheet worksheet = workbook.Worksheets[0];

    IChart chart = worksheet.Charts[0];

    // Used to invert series color if the value is negative in XLS (binary) format
    chart.Series[0].SerieFormat.Interior.SwapColorsOnNegative = true;

    // Saving the workbook
    workbook.SaveAs("Output.xls");
}

C# [Windows-specific]

using (ExcelEngine excelEngine = new ExcelEngine())
{
    #region Workbook Initialization
    IApplication application = excelEngine.Excel;
    application.DefaultVersion = ExcelVersion.Xlsx;
    IWorkbook workbook = application.Workbooks.Open("InputTemplatexlsx.xls");
    IWorksheet worksheet = workbook.Worksheets[0];
    #endregion

    IChart chart = worksheet.Charts[0];

    // Used to invert series color if the value is negative in XLS (binary) format
    chart.Series[0].SerieFormat.Interior.SwapColorsOnNegative = true;

    #region Save
    //Saving the workbook
    workbook.SaveAs("Output.xls");
    #endregion
}

VB.NET [Windows-specific]

Using excelEngine As New ExcelEngine()
    Dim application As IApplication = excelEngine.Excel
    application.DefaultVersion = ExcelVersion.Excel97to2003
    Dim workbook As IWorkbook = application.Workbooks.Open("InputTemplate.xls")
    Dim worksheet As IWorksheet = workbook.Worksheets(0)

    Dim chart As IChart = worksheet.Charts(0)

    ' Used to invert series color if the value is negative in XLS (binary) format
    chart.Series(0).SerieFormat.Interior.SwapColorsOnNegative = True

    ' Saving the workbook
    workbook.SaveAs("Output.xls")
End Using