DEV Community

Liam Anderson
Liam Anderson

Posted on

Ajouter différents types de formules et de fonctions à Excel en C# et VB.NET

Introduction

Excel est un outil puissant pour organiser, analyser et manipuler des données. L'une de ses fonctionnalités les plus puissantes est la possibilité d'ajouter des formules et des fonctions pour automatiser des calculs et des tâches complexes. Les formules sont des équations qui effectuent des opérations mathématiques sur des cellules ou des plages de cellules dans une feuille de calcul Excel. Les fonctions sont des formules pré-construites que vous pouvez utiliser pour effectuer des tâches spécifiques, telles que le calcul de moyennes, la recherche de la valeur maximale ou minimale, ou le comptage du nombre de cellules avec certaines valeurs. Dans cet article, vous apprendrez comment ajouter différents types de formules et de fonctions à Excel en C# et VB.NET à l'aide de la bibliothèque Spire.XLS for .NET.

Comment ajouter des formules et des fonctions à Excel en C# et VB.NET

Avant de commencer, vous devez inclure les fichiers DLL de la bibliothèque Spire.XLS for .NET dans votre projet. Vous pouvez le faire en installant la bibliothèque Spire.XLS for .NET via NuGet:

PM> Install-Package Spire.XLS
Enter fullscreen mode Exit fullscreen mode

Une fois la bibliothèque Spire.XLS for .NET installée, vous pouvez l'utiliser pour ajouter différents types de formules et de fonctions à un fichier Excel avec facilité.

Voici un exemple montrant comment ajouter des formules et des fonctions à Excel en C# et VB.NET:
C#

using Spire.Xls;

namespace FormulasAndFunctions
{
    internal class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            int currentRow = 1;
            string currentFormula;

            sheet.Range[currentRow, 1].Text = "Données de test:";
            sheet.Range[currentRow, 1].Style.Font.IsBold = true;
            sheet.Range[currentRow, 1].Style.FillPattern = ExcelPatternType.Solid;
            sheet.Range[currentRow, 1].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range[currentRow, 1].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            sheet.Range[++currentRow, 1].NumberValue = 7.3;
            sheet.Range[currentRow, 2].NumberValue = 5;
            sheet.Range[currentRow, 3].NumberValue = 8.2;
            sheet.Range[currentRow, 4].NumberValue = 4;
            sheet.Range[currentRow, 5].NumberValue = 3;
            sheet.Range[currentRow, 6].NumberValue = 11.3;

            currentRow++;

            sheet.Range[++currentRow, 1].Text = "Formules";
            sheet.Range[currentRow, 2].Text = "Résultats";
            sheet.Range[currentRow, 1, currentRow, 2].Style.Font.IsBold = true;
            sheet.Range[currentRow, 1, currentRow, 2].Style.KnownColor = ExcelColors.LightGreen1;
            sheet.Range[currentRow, 1, currentRow, 2].Style.FillPattern = ExcelPatternType.Solid;
            sheet.Range[currentRow, 1, currentRow, 2].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Medium;

            currentFormula = "=\"Bonjour\"";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=300";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=3389.639421";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=false";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=1+2+3+4+5-6-7+8-9";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=33*3/4-2+10";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            currentFormula = "=Sheet1!$B$2";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //AVERAGE
            currentFormula = "=AVERAGE(Sheet1!$D$2:F$2)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //COUNT
            currentFormula = "=COUNT(3,5,8,10,2,34)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;

            //NOW
            currentFormula = "=NOW()";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow, 2].Formula = currentFormula;
            sheet.Range[currentRow, 2].Style.NumberFormat = "yyyy-MM-DD";

            //SECOND
            currentFormula = "=SECOND(0.503)";
            sheet.Range[++currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MINUTE
            currentFormula = "=MINUTE(0.78125)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MONTH
            currentFormula = "=MONTH(9)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //DAY
            currentFormula = "=DAY(10)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //TIME
            currentFormula = "=TIME(4,5,7)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //DATE
            currentFormula = "=DATE(6,4,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //RAND
            currentFormula = "=RAND()";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //HOUR
            currentFormula = "=HOUR(0.5)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MOD
            currentFormula = "=MOD(5,3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //WEEKDAY
            currentFormula = "=WEEKDAY(3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //YEAR
            currentFormula = "=YEAR(23)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //NOT
            currentFormula = "=NOT(true)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //OR
            currentFormula = "=OR(true)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //AND
            currentFormula = "=AND(TRUE)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //VALUE
            currentFormula = "=VALUE(30)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //LEN
            currentFormula = "=LEN(\"world\")";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MID
            currentFormula = "=MID(\"world\",4,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //ROUND
            currentFormula = "=ROUND(7,3)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SIGN
            currentFormula = "=SIGN(4)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //INT
            currentFormula = "=INT(200)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //ABS
            currentFormula = "=ABS(-1.21)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //LN
            currentFormula = "=LN(15)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //EXP
            currentFormula = "=EXP(20)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SQRT
            currentFormula = "=SQRT(40)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //PI
            currentFormula = "=PI()";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //COS
            currentFormula = "=COS(9)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SIN
            currentFormula = "=SIN(45)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MAX
            currentFormula = "=MAX(10,30)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //MIN
            currentFormula = "=MIN(5,7)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //AVERAGE
            currentFormula = "=AVERAGE(12,45)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SUM
            currentFormula = "=SUM(18,29)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //IF
            currentFormula = "=IF(4,2,2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            //SUBTOTAL
            currentFormula = "=SUBTOTAL(3,Sheet1!A2:F2)";
            sheet.Range[currentRow, 1].Text = "'" + currentFormula;
            sheet.Range[currentRow++, 2].Formula = currentFormula;

            sheet.SetColumnWidth(1, 32);
            sheet.SetColumnWidth(2, 16);
            sheet.SetColumnWidth(3, 16);

            CellStyle style = workbook.Styles.Add("Style");
            style.HorizontalAlignment = HorizontalAlignType.Left;
            sheet.ApplyStyle(style);

            workbook.SaveToFile("résultat.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}
Enter fullscreen mode Exit fullscreen mode

VB.NET

Imports Spire.Xls

Namespace FormulasAndFunctions
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            Dim workbook As Workbook = New Workbook()
            Dim sheet As Worksheet = workbook.Worksheets(0)

            Dim currentRow = 1
            Dim currentFormula As String

            sheet.Range(currentRow, 1).Text = "Données de test:"
            sheet.Range(currentRow, 1).Style.Font.IsBold = True
            sheet.Range(currentRow, 1).Style.FillPattern = ExcelPatternType.Solid
            sheet.Range(currentRow, 1).Style.KnownColor = ExcelColors.LightGreen1
            sheet.Range(currentRow, 1).Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Medium

            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).NumberValue = 7.3
            sheet.Range(currentRow, 2).NumberValue = 5
            sheet.Range(currentRow, 3).NumberValue = 8.2
            sheet.Range(currentRow, 4).NumberValue = 4
            sheet.Range(currentRow, 5).NumberValue = 3
            sheet.Range(currentRow, 6).NumberValue = 11.3

            currentRow += 1

            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "Formules"
            sheet.Range(currentRow, 2).Text = "Résultats"
            sheet.Range(currentRow, 1, currentRow, 2).Style.Font.IsBold = True
            sheet.Range(currentRow, 1, currentRow, 2).Style.KnownColor = ExcelColors.LightGreen1
            sheet.Range(currentRow, 1, currentRow, 2).Style.FillPattern = ExcelPatternType.Solid
            sheet.Range(currentRow, 1, currentRow, 2).Style.Borders(BordersLineType.EdgeBottom).LineStyle = LineStyleType.Medium

            currentFormula = "=""Bonjour"""
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            currentFormula = "=300"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            currentFormula = "=3389.639421"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            currentFormula = "=false"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            currentFormula = "=1+2+3+4+5-6-7+8-9"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            currentFormula = "=33*3/4-2+10"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            currentFormula = "=Sheet1!$B$2"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            'AVERAGE
            currentFormula = "=AVERAGE(Sheet1!$D$2:F$2)"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            'COUNT
            currentFormula = "=COUNT(3,5,8,10,2,34)"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula

            'NOW
            currentFormula = "=NOW()"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(currentRow, 2).Formula = currentFormula
            sheet.Range(currentRow, 2).Style.NumberFormat = "yyyy-MM-DD"

            'SECOND
            currentFormula = "=SECOND(0.503)"
            sheet.Range(Threading.Interlocked.Increment(currentRow), 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'MINUTE
            currentFormula = "=MINUTE(0.78125)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'MONTH
            currentFormula = "=MONTH(9)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'DAY
            currentFormula = "=DAY(10)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'TIME
            currentFormula = "=TIME(4,5,7)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'DATE
            currentFormula = "=DATE(6,4,2)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'RAND
            currentFormula = "=RAND()"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'HOUR
            currentFormula = "=HOUR(0.5)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'MOD
            currentFormula = "=MOD(5,3)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'WEEKDAY
            currentFormula = "=WEEKDAY(3)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'YEAR
            currentFormula = "=YEAR(23)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'NOT
            currentFormula = "=NOT(true)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'OR
            currentFormula = "=OR(true)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'AND
            currentFormula = "=AND(TRUE)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'VALUE
            currentFormula = "=VALUE(30)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'LEN
            currentFormula = "=LEN(""world"")"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'MID
            currentFormula = "=MID(""world"",4,2)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'ROUND
            currentFormula = "=ROUND(7,3)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'SIGN
            currentFormula = "=SIGN(4)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'INT
            currentFormula = "=INT(200)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'ABS
            currentFormula = "=ABS(-1.21)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'LN
            currentFormula = "=LN(15)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'EXP
            currentFormula = "=EXP(20)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'SQRT
            currentFormula = "=SQRT(40)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'PI
            currentFormula = "=PI()"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'COS
            currentFormula = "=COS(9)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'SIN
            currentFormula = "=SIN(45)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'MAX
            currentFormula = "=MAX(10,30)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'MIN
            currentFormula = "=MIN(5,7)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'AVERAGE
            currentFormula = "=AVERAGE(12,45)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'SUM
            currentFormula = "=SUM(18,29)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'IF
            currentFormula = "=IF(4,2,2)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            'SUBTOTAL
            currentFormula = "=SUBTOTAL(3,Sheet1!A2:F2)"
            sheet.Range(currentRow, 1).Text = "'" & currentFormula
            sheet.Range(Math.Min(Threading.Interlocked.Increment(currentRow), currentRow - 1), 2).Formula = currentFormula

            sheet.SetColumnWidth(1, 32)
            sheet.SetColumnWidth(2, 16)
            sheet.SetColumnWidth(3, 16)

            Dim style As CellStyle = workbook.Styles.Add("Style")
            style.HorizontalAlignment = HorizontalAlignType.Left
            sheet.ApplyStyle(style)

            workbook.SaveToFile("résultat.xlsx", ExcelVersion.Version2016)
            workbook.Dispose()
        End Sub
    End Class
End Namespace
Enter fullscreen mode Exit fullscreen mode

Articles connexes

Convertir Excel en PDF en C# et VB.NET
Convertir Excel en images en C# et VB.NET
Convertir Excel en CSV et CSV en Excel en C# et VB.NET

Top comments (0)