【图】Excel如何输入长位数数值

Excel如何输入长位数数值

　　在Excel中，输入超过12位的数值，系统自动将其转换为科学记数格式，如果输入超过15位的数值，系统自动将15位以后的数值转换为0。这种转换对于我们需要输入一些特殊数值(如身份证号码)时，就不能符合我们的要求了。为了能让这些长位数数值完整显示出来，就必须用下面两种方法，将单元格转换成文本格式，然后再输入。

　　方法一：在输入这些长数值时，先输入一个英文状态下的单引号(')，然后接头输入数值即可。

　　注意：这种方法适合输入少量的长数值，且输入的英文状态下的单引号不影响显示和打印。

　　方法二：选中相应的单元格区域，执行格式→单元格命令，打开单元格格式对话框，在数值标签下，选中文本选项，确定返回，再在上述单元格中输入长数值就行了。

asp.net导出Excel类库代码分享

这篇文章主要介绍了asp.net导出Excel类库代码，有需要的朋友可以参考一下

代码如下:using System;using System.Collections.Generic;using System.Reflection;using System.Web;using Excel = Microsoft.Office.Interop.Excel; /// summary///ExcelClass 的摘要说明/// /summarypublic class ExcelClass{ /// summary /// 构建ExcelClass类 /// /summary public ExcelClass() { this.m_objExcel = new Excel.Application(); } /// summary /// 构建ExcelClass类 /// /summary /// param name="objExcel"Excel.Application/param public ExcelClass(Excel.Application objExcel) { this.m_objExcel = objExcel; } /// summary /// 列标号 /// /summary private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; /// summary /// 获取描述区域的字符 /// /summary /// param name="x"/param /// param name="y"/param /// returns/returns public string GetAix(int x, int y) { char[] AChars = AList.ToCharArray(); if (x = 26) { return ""; } string s = ""; s = s + AChars[x - 1].ToString(); s = s + y.ToString(); return s; } /// summary /// 给单元格赋值1 /// /summary /// param name="x"行号/param /// param name="y"列号/param /// param name="align"对齐（CENTER、LEFT、RIGHT）/param /// param name="text"值/param public void setValue(int y, int x, string align, string text) { Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.set_Value(miss, text); if (align.ToUpper() == "CENTER") { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } if (align.ToUpper() == "LEFT") { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } if (align.ToUpper() == "RIGHT") { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; } } /// summary /// 给单元格赋值2 /// /summary /// param name="x"行号/param /// param name="y"列号/param /// param name="text"值/param public void setValue(int y, int x, string text) { Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.set_Value(miss, text); } /// summary /// 给单元格赋值3 /// /summary /// param name="x"行号/param /// param name="y"列号/param /// param name="text"值/param /// param name="font"字符格式/param /// param name="color"颜色/param public void setValue(int y, int x, string text, System.Drawing.Font font, System.Drawing.Color color) { this.setValue(x, y, text); Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.Font.Size = font.Size; range.Font.Bold = font.Bold; range.Font.Color = color; range.Font.Name = font.Name; range.Font.Italic = font.Italic; range.Font.Underline = font.Underline; } /// summary /// 插入新行 /// /summary /// param name="y"模板行号/param public void insertRow(int y) { Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y)); range.Copy(miss); range.Insert(Excel.XlDirection.xlDown, miss); range.get_Range(GetAix(1, y), GetAix(25, y)); range.Select(); sheet.Paste(miss, miss); } /// summary /// 把剪切内容粘贴到当前区域 /// /summary public void past() { string s = "a,b,c,d,e,f,g"; sheet.Paste(sheet.get_Range(this.GetAix(10, 10), miss), s); } /// summary /// 设置边框 /// /summary /// param name="x1"/param /// param name="y1"/param /// param name="x2"/param /// param name="y2"/param /// param name="Width"/param public void setBorder(int x1, int y1, int x2, int y2, int Width) { Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), miss); ((Excel.Range)range.Cells[x1, y1]).ColumnWidth = Width; } public void mergeCell(int x1, int y1, int x2, int y2) { Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); range.Merge(true); } public Excel.Range getRange(int x1, int y1, int x2, int y2) { Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); return range; } private object miss = Missing.Value; //忽略的参数OLENULL private Excel.Application m_objExcel;//Excel应用程序实例 private Excel.Workbooks m_objBooks;//工作表集合 private Excel.Workbook m_objBook;//当前操作的工作表 private Excel.Worksheet sheet;//当前操作的表格 public Excel.Worksheet CurrentSheet { get { return sheet; } set { this.sheet = value; } } public Excel.Workbooks CurrentWorkBooks { get { return this.m_objBooks; } set { &nbm.tulaoshi.comsp; this.m_objBooks = value; } } public Excel.Workbook CurrentWorkBook { get { return this.m_objBook; } set { this.m_objBook = value; } } /// summary /// 打开Excel文件 /// /summary /// param name="filename"路径/param public void OpenExcelFile(string filename) { UserControl(false); m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = m_objExcel.ActiveWorkbook; sheet = (Excel.Worksheet)m_objBook.ActiveSheet; } public void UserControl(bool usercontrol) { if (m_objExcel == null) { return; } m_objExcel.UserControl = usercontrol; m_objExcel.DisplayAlerts = usercontrol; m_objExcel.Visible = usercontrol; } public void CreateExceFile() { UserControl(false); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel.Workbook)(m_objBooks.Add(miss)); sheet = (Excel.Worksheet)m_objBook.ActiveSheet; } public void SaveAs(string FileName) { m_objBook.SaveAs(FileName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, miss, miss, miss, miss); //m_objBook.Close(false, miss, miss); } public void ReleaseExcel() { m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet); m_objExcel = null; m_objBooks = null; m_objBook = null; sheet = null; GC.Collect(); } public bool KillAllExcelApp() { try { if (m_objExcel != null) // isRunning是判断xlApp是怎么启动的flag. { m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); //释放COM组件，其实就是将其引用计数减1 //System.Diagnostics.Process theProc; foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) { //先关闭图形窗口。如果关闭失败...有的时候在状态里看不到图形窗口的excel了， //但是在进程里仍然有EXCEL.EXE的进程存在，那么就需要杀掉它:p if (theProc.CloseMainWindow() == false) { theProc.Kill(); } } m_objExcel = null; return true; } } catch { return false; } return true; }} /// summary /// 点击打印按钮事件 /// /summary /// param name="sender"/param /// param name="e"/param protected void Sendbu_Click(object sender, EventArgs e) { try { //查找部门分类用户 DataTable Duser = EduOA.DBUtility.DbHelperSQL.Query("select count(*) as count,d.Id as DId FROM OA_User u,OA_Department d where u.DepartmentID=d.Id group by d.Id").Tables[0]; ExcelClass Ec = new ExcelClass();//创建Excel操作类对象 int Ycount = 1; Ec.CreateExceFile();//创建Excel文件 Ec.setValue(Ycount, 1, "CENTER", "组织部门"); Ec.setValue(Ycount, 2, "CENTER", "姓名"); Ec.setValue(Ycount, 3, "CENTER", "性别"); Ec.setValue(Ycount, 4, "CENTER", "职位"); Ec.setValue(Ycount, 5, "CENTER", "移动电话"); Ec.setValue(Ycount, 6, "CENTER", "电话"); Ec.setValue(Ycount, 7, "CENTER", "电子邮箱"); Ec.setBorder(1, 1, 1, 1, 50); Ec.setBorder(1, 2, 2, 2, 20); Ec.setBorder(1, 5, 5, 5, 20); Ec.setBorder(1, 6, 6, 6, 20); Ec.setBorder(1, 7, 7, 7, 20); for (int i = 0; i Duser.Rows.Count; i++) { Ycount += 1; Ec.setValue(Ycount, 1, "CENTER", Common.DeleteHtml(Getdept(Duser.Rows[i]["count"], Duser.Rows[i]["DId"]))); DataTable dtuser = GetData(Duser.Rows[i]["DId"]); for (int k = 0; k dtuser.Rows.Count; k++) { Ec.setValue(Ycount, 2, "CENTER", dtuser.Rows[k]["TrueName"].ToString()); Ec.setValue(Ycount, 3, "CENTER", dtuser.Rows[k]["sex"].ToString()); Ec.setValue(Ycount, 4, "CENTER", dtuser.Rows[k]["PositionId"].ToString()); Ec.setValue(Ycount, 5, "CENTER", dtuser.Rows[k]["Telephone"].ToString()); Ec.setValue(Ycount, 6, "CENTER", dtuser.Rows[k]["Mobile"].ToString()); Ec.setValue(Ycount, 7, "CENTER", dtuser.Rows[k]["Email"].ToString()); Ycount += 1; } } string path = Server.MapPath("Contactfiles"); Ec.SaveAs(path+"通讯录.xlsx"); //*******释放Excel资源*********** Ec.ReleaseExcel(); Response.Redirect("Contactfiles/通讯录.xlsx"); } catch (Exception ex) { PageError("导出出错！"+ex.ToString(),""); } }