在把table表格中的数据导出到Excel的时候,以科学计数法显示位数多的数字时怎么解决?

时间:2021-02-01 20:22:30
    sbHtml.AppendFormat("<td>&nbsp;{0}</td>", data[i].IDcard.ToString());

sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", data[i].IDcard.ToString());

 

  public byte[] ExportToExcel(List<EnterpriseInfo> data)
        {
            var str = "A    B    C    D    E    F";
            str = str.Replace("\r\n", "");
            string[] temp = str.Split(new char[] { '\t' }, StringSplitOptions.RemoveEmptyEntries);
            var lstTitle = temp.ToList();
            var sbHtml = new System.Text.StringBuilder();
            sbHtml.Append("<table border='1' cellspacing='0' cellpadding='0'>");
            sbHtml.Append("<tr>");
            foreach (var item in lstTitle)
            {
                sbHtml.AppendFormat("<td style='font-size: 14px;text-align:center;background-color: #DCE0E2; font-weight:bold;' height='25'>{0}</td>", item);
            }
            sbHtml.Append("</tr>");

            ////读取表中数据
            for (int i = 0; i < data.Count; i++)
            {
                sbHtml.Append("<tr>");
                //sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", i);
                sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", data[i].Unitname + "");
                sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", data[i].Unitprofile + "");
                sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", data[i].Industry + "");
                sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", data[i].Contactperson + "");
                sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", data[i].Phone + "");
                sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", data[i].Emailaddress + "");
                //sbHtml.AppendFormat("<td style='mso-number-format:\"@\"';>{0}</td>", data[i].Registrationdate + "");
                sbHtml.Append("</tr>");
            }

            byte[] fileContents = System.Text.Encoding.UTF8.GetBytes(sbHtml.ToString());

            return fileContents;
        }
     var test = Request["down"];

            if (test == "Down")
            {
                return File(ExportToExcel(data), "application/ms-excel", "数据.xls");
            }


       <input type="hidden" value="" name="down" id="txttype" />

按钮事件
{

$("#txttype").val("Down");
document.getElementById('form1').action = "/Backstage/xxx/List";
document.getElementById("form1").submit();
$("#txttype").val("");

}    
 
 
 

 



 public static byte[] ExportDataTableToExcel(DataTable dt)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.Cells[0, i].PutValue(dt.Columns[i].ColumnName);
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                for (int ii = 0; ii < dt.Columns.Count; ii++)
                {
                    sheet.Cells[i+1, ii].PutValue(dt.Rows[i][ii].ToString());
                }
            }

            workbook.Save("期间模板1.xlsx");
            MemoryStream ms = workbook.SaveToStream();
            return ms.ToArray();
        }

        public static void createExcel()
        {
            Workbook wb = new Workbook();
            Worksheet sheet = wb.Worksheets[0];
            //设置样式
            //Style style = wb.CreateStyle();
            //style.ForegroundColor = System.Drawing.Color.FromArgb(199, 214, 157); //前景色
            //style.HorizontalAlignment = TextAlignmentType.Center; //水平线
            //style.Pattern = BackgroundType.Solid;
         
            ////绑定样式
            //sheet.Cells[0, 0].SetStyle(style);
            //sheet.Cells[0, 1].SetStyle(style);


            //绑定数据
            sheet.Cells[0, 0].PutValue("工号1"); //第一行,第一列
            sheet.Cells[0, 1].PutValue("姓名"); //第一行,第2列

            wb.SaveToStream();

            wb.Save("期间模板1.xlsx");
        }
 
 

 

 

 

 public static byte[] ExportDataTableToExcel(List<model> list)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];
            //string[] columns = {"A","B","C"}; 
            string[] columns = {"B", "C" };// 应为A跳过,所以去掉
            for (int i = 0; i < columns.Length; i++)
            {
                sheet.Cells[0, i].PutValue(columns[i]);
            }
            for (int i = 0; i < list.Count; i++)
            {
                int bxl = 0; //并行列
                var modeps = list[i].GetType().GetProperties(); //获得该对象所有属性名
                for (int ii = 0; ii < modeps.Length; ii++)
                {                    
                    var name = modeps[ii].Name; //获得属性名
                    if (name == "A")
                    {
                        bxl--;
                        continue;
                    }
                    bxl++;
                    var value = modeps[ii].GetValue(list[i]); //获得属性值
                    sheet.Cells[i+1, bxl].PutValue(value);
                }
            }
            workbook.Save("期间模板1.xlsx");
            MemoryStream ms = workbook.SaveToStream();
            return ms.ToArray();
        }