How to format excel cells through excel

Question

I would like to know how I can format the cells of a spreadsheet by C#, already managed to insert the data I would like in it, but I would like to insert formatting and formulas as well.

Code: `

int i = 2;

        String answer = Interaction.InputBox("Digite o nome do arquivo de relatorio", "", null, -1, -1);

        while (answer == null)
        {
            answer = Interaction.InputBox("Digite o nome do arquivo de relatorio", "", null, -1, -1);
        }


        // Inicia o componente Excel
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        object misValue = System.Reflection.Missing.Value;

        //Cria uma planilha temporária na memória do computador
        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

        //incluindo os títulos de cada coluna
        xlWorkSheet.Cells[1, 1] = "Id Venda";
        xlWorkSheet.Cells[1, 2] = "Classificação";
        xlWorkSheet.Cells[1, 3] = "Cliente";
        xlWorkSheet.Cells[1, 4] = "Cpf/Cnpj";
        xlWorkSheet.Cells[1, 5] = "Carro";
        xlWorkSheet.Cells[1, 6] = "Placa";
        xlWorkSheet.Cells[1, 7] = "Serviço";
        xlWorkSheet.Cells[1, 8] = "Preço";
        xlWorkSheet.Cells[1, 9] = "Valor Cobrado";
        xlWorkSheet.Cells[1, 10] = "Data";
        xlWorkSheet.Cells[1, 11] = "Pagamento";
        xlWorkSheet.Cells[1, 12] = "Forma de Pagamento";

        Classes.VendasServicos vs = new Classes.VendasServicos();

        DateTime dataI = DtInicialVenda.Value;
        DateTime dataF = dtFinalVenda.Value;



        // Incluindo os dados das linhas
        con.conectar();
        SqlDataReader reader;

        if (rdbAmbos.Checked == true)
        {
            if (cmbServico.Text == "")
            {
                reader = con.exeCliente($"SELECT [VendasServicos].[idVenda], [Cliente].[frotista], [Cliente].[nome] as 'Cliente', [Cliente].[pfpj], [Vendas].[carro], [Vendas].[placa], [Servicos].[nome] as 'Serviço', [Servicos].[preco], [VendasServicos].[valorCobrado], [Vendas].[data], [Vendas].[pago], [Vendas].[formaPagamento] FROM [VendasServicos] INNER JOIN Vendas ON ([VendasServicos].[idVenda] = [Vendas].[idVenda])INNER JOIN Cliente ON Vendas.idCliente = Cliente.idCliente INNER JOIN Servicos ON [VendasServicos].idServico = Servicos.idServico WHERE [Vendas].[data] BETWEEN '{dataI}' AND '{dataF}' ");
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        xlWorkSheet.Cells[i, 1] = reader.GetInt32(0);
                        Boolean tipo = reader.GetBoolean(1);

                        if (tipo == true)
                        {
                            xlWorkSheet.Cells[i, 2] = "Frotista";
                        }
                        else
                        {
                            xlWorkSheet.Cells[i, 2] = "Particular";
                        }

                        xlWorkSheet.Cells[i, 3] = reader.GetString(2).Trim();

                        try
                        {
                            xlWorkSheet.Cells[i, 4] = reader.GetString(3).Trim();
                        }
                        catch
                        {
                            xlWorkSheet.Cells[i, 4] = "";
                        }

                        xlWorkSheet.Cells[i, 5] = reader.GetString(4);
                        xlWorkSheet.Cells[i, 6] = reader.GetString(5);
                        xlWorkSheet.Cells[i, 7] = reader.GetString(6);
                        xlWorkSheet.Cells[i, 8] = reader.GetDouble(7);
                        try
                        {
                            xlWorkSheet.Cells[i, 9] = reader.GetDouble(8);
                        }
                        catch
                        {
                            xlWorkSheet.Cells[i, 9] = reader.GetDouble(7);
                        }
                        xlWorkSheet.Cells[i, 10] = reader.GetDateTime(9);

                        Boolean pg = reader.GetBoolean(10);

                        if (pg == true)
                        {
                            xlWorkSheet.Cells[i, 11] = "PG";
                        }
                        else
                        {
                            xlWorkSheet.Cells[i, 11] = "EM ABERTO";
                        }
                        try
                        {
                            xlWorkSheet.Cells[i, 12] = reader.GetString(11);
                        }
                        catch (Exception)
                        {

                        }
                        i++;
                    }
                    int lastCell = i - 1;
                    xlWorkSheet.Cells[i, 8] = "Total:";
                    xlWorkSheet.Cells[i, 9] = "=SOMA(I2:I" + lastCell + ")";
                    reader.Close();
                    con.desconectar();
                }



                //Salva o arquivo de acordo com a documentação do Excel.
                try
                {

                    xlWorkBook.SaveAs(answer, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
                    Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
                    xlWorkBook.Close(true, misValue, misValue);
                    xlApp.Quit();
                    //o arquivo foi salvo na pasta Meus Documentos.
                    string caminho = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
                    //MessageBox.Show("Concluído. Verifique em " + caminho + "arquivo.xls");


                    try
                    {
                        FileInfo fi = new FileInfo($@"{caminho}/{answer}.xls");
                        if (fi.Exists)
                        {
                            System.Diagnostics.Process.Start($@"{caminho}/{answer}.xls");
                        }
                        else
                        {
                            MessageBox.Show("Arquivo não encontrado");
                        }
                    }
                    catch
                    {
                        MessageBox.Show("Test");
                    }
                }
                catch
                {
                    MessageBox.Show("Não foi possivel salvar");
                }
            }`

Answer 1

I managed to make some pretty cool changes to my spreadsheet I’ll leave here, maybe help someone.

 int i = 2;

        String answer = Interaction.InputBox("Digite o nome do arquivo de relatorio", "", null, -1, -1);

        while (answer == null)
        {
            answer = Interaction.InputBox("Digite o nome do arquivo de relatorio", "", null, -1, -1);
        }



        // Inicia o componente Excel
        Excel.Application xlApp;
        Excel.Workbook xlWorkBook;
        Excel.Worksheet xlWorkSheet;
        Excel.Range rangeTitulo;
        Excel.Range rangeValores;
        Excel.Range rangeTabela;
        object misValue = System.Reflection.Missing.Value;

        //Cria uma planilha temporária na memória do computador
        xlApp = new Excel.Application();
        xlWorkBook = xlApp.Workbooks.Add(misValue);
        xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);


        //Auto size columns
        xlWorkSheet.Columns.AutoFit();


        //incluindo os títulos de cada coluna
        xlWorkSheet.Cells[1, 1] = "Id Venda";
        xlWorkSheet.Cells[1, 2] = "Classificação";
        xlWorkSheet.Cells[1, 3] = "Cliente";
        xlWorkSheet.Cells[1, 4] = "Cpf/Cnpj";
        xlWorkSheet.Cells[1, 5] = "Carro";
        xlWorkSheet.Cells[1, 6] = "Placa";
        xlWorkSheet.Cells[1, 7] = "Serviço";
        xlWorkSheet.Cells[1, 8] = "Preço";
        xlWorkSheet.Cells[1, 9] = "Valor Cobrado";
        xlWorkSheet.Cells[1, 10] = "Data";
        xlWorkSheet.Cells[1, 11] = "Pagamento";
        xlWorkSheet.Cells[1, 12] = "Forma de Pagamento";


        if (reader.HasRows)
        {


            while (reader.Read())
            {
                xlWorkSheet.Cells[i, 1] = reader.GetInt32(0);
                Boolean tipo = reader.GetBoolean(1);

                if (tipo == true)
                {
                    xlWorkSheet.Cells[i, 2] = "Frotista";
                }
                else
                {
                    xlWorkSheet.Cells[i, 2] = "Particular";
                }


                xlWorkSheet.Cells[i, 3] = reader.GetString(2).Trim();


                try
                {
                    xlWorkSheet.Cells[i, 4] = reader.GetString(3).Trim();

                }
                catch
                {
                    xlWorkSheet.Cells[i, 4] = "";

                }

                xlWorkSheet.Cells[i, 5] = reader.GetString(4);

                xlWorkSheet.Cells[i, 6] = reader.GetString(5);

                xlWorkSheet.Cells[i, 7] = reader.GetString(6);

                xlWorkSheet.Cells[i, 8] = reader.GetDouble(7);

                try
                {
                    xlWorkSheet.Cells[i, 9] = reader.GetDouble(8);

                }
                catch
                {
                    xlWorkSheet.Cells[i, 9] = reader.GetDouble(7);
                }

                xlWorkSheet.Cells[i, 10] = reader.GetDateTime(9);

                Boolean pg = reader.GetBoolean(10);

                if (pg == true)
                {
                    xlWorkSheet.Cells[i, 11] = "PG";
                }
                else
                {
                    xlWorkSheet.Cells[i, 11] = "EM ABERTO";
                }
                try
                {
                    xlWorkSheet.Cells[i, 12] = reader.GetString(11);
                }
                catch (Exception)
                {

                }
                i++;
            }

            //Atribuindo valore de subtotal e total
            lastCell = i - 1;
            xlWorkSheet.Cells[i, 7] = "Total:";
            //xlWorkSheet.Cells[i, 7].Interior.Color = ColorTranslator.FromHtml("#9ea7aa");
            xlWorkSheet.Cells[i, 7].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

           // xlWorkSheet.Cells[i, 9].Interior.Color = ColorTranslator.FromHtml("#cfd8dc");
            xlWorkSheet.Cells[i, 9].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            xlWorkSheet.Cells[i, 9].Formula = "=SUM(I2:I" + lastCell + ")";
            xlWorkSheet.Calculate();
            xlWorkSheet.Cells[i, 9].NumberFormat = "R$#,##0.00";

            xlWorkSheet.Cells[i, 8].Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
         //   xlWorkSheet.Cells[i, 8].Interior.Color = ColorTranslator.FromHtml("#cfd8dc");
            xlWorkSheet.Cells[i, 8].Formula = "=SUM(H2:H" + lastCell + ")";
            xlWorkSheet.Calculate();
            xlWorkSheet.Cells[i, 8].NumberFormat = "R$#,##0.00";


            reader.Close();
            con.desconectar();
        }

        //Colocando borda 
        rangeTabela = xlWorkSheet.get_Range("A1", "L" + lastCell);
        rangeTabela.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

        // Alinha colunas
        rangeTabela.Columns.AutoFit();

        //Colocando cor nas linhas de dados
       // rangeTabela.Interior.Color = ColorTranslator.FromHtml("#cfd8dc");


        //Colocando cores nas células de identificação 
        rangeTitulo = xlWorkSheet.get_Range("A1", "L1");

        //rangeTitulo.Interior.Color = ColorTranslator.FromHtml("#9ea7aa");
        rangeTitulo.Font.Bold = true;

        // Colocando R$
        rangeValores = xlWorkSheet.get_Range("H2", "I" + lastCell);
        rangeValores.NumberFormat = "R$0.00";

        //Salva o arquivo de acordo com a documentação do Excel.
        try
        {

            xlWorkBook.SaveAs(answer, Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue,
            Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();
            //o arquivo foi salvo na pasta Meus Documentos.
            string caminho = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);

            try
            {
                FileInfo fi = new FileInfo($@"{caminho}/{answer}.xls");
                if (fi.Exists)
                {
                    System.Diagnostics.Process.Start($@"{caminho}/{answer}.xls");
                }
                else
                {
                    MessageBox.Show("Arquivo não encontrado");
                }
            }
            catch
            {
                MessageBox.Show("Test");
            }


        }
        catch
        {
            MessageBox.Show("Não foi possivel salvar");
        }