Format excel cells via Behind code

Asked

Viewed 479 times

2

I have a routine that generates a spreadsheet in excel, coming from an Oracle database. What happens is that in BD I have values like this: 3456.90 or 1900.00 When these values arrive in the spreadsheet, it shows like this: 3456,90 or 1900

that is, when the value has the last two digits after the point, with value 00, in the spreadsheet shows an integer number. As this is a report, the spreadsheet already needs to have the formatted values and not the user format manually. Is there any way to do it? It is exported in format .csv. Is there a way to do that? My Page Load looks like this:

protected void Page_Load(object sender, EventArgs e)
        {
            var conexao = ConfigurationManager.ConnectionStrings["TS"].ConnectionString;
            var connectionOracle = new OracleConnection(conexao);
            connectionOracle.Open();
            var cmd = new OracleCommand(sql(), connectionOracle);
            OracleDataReader odr = cmd.ExecuteReader();
            ItemMatMed itemMatMed = new ItemMatMed();

            try
            {
                Response.Write("Data de Inclusão;Tipo de Tabela;Tabela;Codigo;TUSS;Descrição;Fabricante;Referência do Fabricante;Registro ANVISA;Classificação SIMPRO;Grupo Mat/Med;Grupo Estatístico;Autorização Prévia;Última Vigência;Valor;Tabela Própria;");
                Response.Write("\r");

                while (odr.Read())
                {
                    Response.Write(odr[0].ToString() + ";" + odr[1].ToString() + ";" + odr[2].ToString() + ";" + odr[3].ToString() + ";" + odr[4].ToString() + ";" +
                                   odr[5].ToString() + ";" + odr[6].ToString() + ";" + odr[7].ToString() + ";" + odr[8].ToString() + ";" + odr[9].ToString() + ";" +
                                   odr[10].ToString() + ";" + odr[11].ToString() + ";" + odr[12].ToString() + ";" + odr[13].ToString() + ";" + odr[14].ToString() + ";");  

                    Response.Write("\r");
                }
            }
            finally
            {
                cmd.Dispose();
                connectionOracle.Close();
                connectionOracle.Dispose();
            }

            Response.ContentType = "text/plain";
            Response.AppendHeader("Content-Disposition","attachment; filename=relacao_materiais.csv");
            Response.End();
        }
  • Really what I want doesn’t work. The String.Format I passed is correct. It turns out that if the cell is not formatted, excel will always show an absolute value, if after that value is comma followed by zeros. The user will have to format the cell at his own risk.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.