C#导出EXCEL如何设置单元格类型

[dreamblueli]

把数据导出EXCEL中，有一列是单晶号，比如单晶号是：200912160001这样的数据，导出到EXCEL中，数据是以科学计数法显示的，但不能以这种显示，我想把单晶号这列类型设置成文本型，如何设置单元格的类型？

Excel.ApplicationClass acExcel = new Excel.ApplicationClass();

            if (acExcel == null)
            {
                SunValley.BarCode.Com.MsgBox.PromptInfo("请确定是否安装了Excel");
                return;
            }

            acExcel.Visible = true;
            Excel.Workbook wb = acExcel.Workbooks.Add(true);
            Excel.Worksheet ws = (Excel.Worksheet)wb.ActiveSheet;

            ws.Name = "原料入库信息";

            //CellFormat cf = acExcel.FindFormat;
            //cf.NumberFormatLocal = "@";

            //导出原料入库列表中的数据 
            ws.Cells[1, 5] = "原料入库信息";

            ws.Cells[3, 1] = "单晶号";
            ws.Cells[3, 2] = "入库日期";
            ws.Cells[3, 3] = "客户编号";
            ws.Cells[3, 4] = "客户名称";
            ws.Cells[3, 5] = "数量";
            ws.Cells[3, 6] = "规格";
            ws.Cells[3, 7] = "库位编号";
            ws.Cells[3, 8] = "库位名称";
            ws.Cells[3, 9] = "操作人编号";
            ws.Cells[3, 10] = "操作人名称";
            //ws.Cells[3, 10].GetType=System.Type.

            if (this.dgvRawMaterialEnter.Rows.Count > 0)
            {
                int count = 4;
                for (int i = 0; i < this.dgvRawMaterialEnter.Rows.Count; i++)
                {
                    ws.Cells[count, 1] = this.dgvRawMaterialEnter.Rows[i].Cells[2].Value.ToString();
                    ws.Cells[count, 2] = this.dgvRawMaterialEnter.Rows[i].Cells[3].Value.ToString();
                    ws.Cells[count, 3] = this.dgvRawMaterialEnter.Rows[i].Cells[4].Value.ToString();
                    ws.Cells[count, 4] = this.dgvRawMaterialEnter.Rows[i].Cells[5].Value.ToString();
                    ws.Cells[count, 5] = this.dgvRawMaterialEnter.Rows[i].Cells[6].Value.ToString();
                    ws.Cells[count, 6] = this.dgvRawMaterialEnter.Rows[i].Cells[7].Value.ToString();
                    ws.Cells[count, 7] = this.dgvRawMaterialEnter.Rows[i].Cells[8].Value.ToString();
                    ws.Cells[count, 8] = this.dgvRawMaterialEnter.Rows[i].Cells[9].Value.ToString();
                    ws.Cells[count, 9] = this.dgvRawMaterialEnter.Rows[i].Cells[10].Value.ToString();
                    ws.Cells[count, 10] = this.dgvRawMaterialEnter.Rows[i].Cells[11].Value.ToString();
                    count++;
                }
            }

[mytimes_walker]

using System;
using System.Web;
using System.Drawing;
using Microsoft.Office.Interop.Excel;

namespace DBUtility
{
    /// <summary>
    /// Excel操作函数
    /// </summary>
    public class ExcelOperate
    {
        private object mValue = System.Reflection.Missing.Value;

        public ExcelOperate()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        public void Merge(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Merge(0);
        }

        /// <summary>
        /// 设置连续区域的字体大小
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="strStartCell">开始单元格</param>
        /// <param name="strEndCell">结束单元格</param>
        /// <param name="intFontSize">字体大小</param>
        public void SetFontSize(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, int intFontSize)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Font.Size = intFontSize.ToString();
        }

        /// <summary>
        /// 横向打印
        /// </summary>
        /// <param name="CurSheet"></param>
        public void xlLandscape(Microsoft.Office.Interop.Excel._Worksheet CurSheet)
        {
            CurSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;

        }

        /// <summary>
        /// 纵向打印
        /// </summary>
        /// <param name="CurSheet"></param>
        public void xlPortrait(Microsoft.Office.Interop.Excel._Worksheet CurSheet)
        {
            CurSheet.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlPortrait;
        }

[rpoplar]

不必设置

[mytimes_walker]

接上

        /// <summary>
        /// 在指定单元格插入指定的值
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="Cell">单元格 如Cells[1,1]</param>
        /// <param name="objValue">文本、数字等值</param>
        public void WriteCell(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, object objValue)
        {
            CurSheet.get_Range(objCell, mValue).Value2 = objValue;

        }

        /// <summary>
        /// 在指定Range中插入指定的值
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="StartCell">开始单元格</param>
        /// <param name="EndCell">结束单元格</param>
        /// <param name="objValue">文本、数字等值</param>
        public void WriteRange(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Value2 = objValue;
        }

        /// <summary>
        /// 合并单元格，并在合并后的单元格中插入指定的值
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        /// <param name="objValue">文本、数字等值</param>
        public void WriteAfterMerge(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, object objValue)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Merge(mValue);
            CurSheet.get_Range(objStartCell, mValue).Value2 = objValue;

        }

        /// <summary>
        /// 为单元格设置公式
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objCell">单元格</param>
        /// <param name="strFormula">公式</param>
        public void SetFormula(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strFormula)
        {
            CurSheet.get_Range(objCell, mValue).Formula = strFormula;
        }

        /// <summary>
        /// 单元格自动换行
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        public void AutoWrapText(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
        {
            CurSheet.get_Range(objStartCell, objEndCell).WrapText = true;
        }

        /// <summary>
        /// 设置整个连续区域的字体颜色
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        /// <param name="clrColor">颜色</param>
        public void SetColor(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Font.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
        }

        /// <summary>
        /// 设置整个连续区域的单元格背景色
        /// </summary>
        /// <param name="CurSheet"></param>
        /// <param name="objStartCell"></param>
        /// <param name="objEndCell"></param>
        /// <param name="clrColor"></param>
        public void SetBgColor(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, System.Drawing.Color clrColor)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Interior.Color = System.Drawing.ColorTranslator.ToOle(clrColor);
        }

        /// <summary>
        /// 设置连续区域的字体名称
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        /// <param name="fontname">字体名称 隶书、仿宋_GB2312等</param>
        public void SetFontName(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string fontname)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Font.Name = fontname;
        }

        /// <summary>
        /// 设置连续区域的字体为黑体
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        public void SetBold(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Font.Bold = true;
        }

        /// <summary>
        /// 设置连续区域的边框：上下左右都为黑色连续边框
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        public void SetBorderAll(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
        {
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeTop].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
            CurSheet.get_Range(objStartCell, objEndCell).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;

        }

[mytimes_walker]

再接上

/// <summary>
        /// 设置连续区域水平居中
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        public void SetHAlignCenter(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
        {
            CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
        }

        /// <summary>
        /// 设置连续区域水平居左
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        public void SetHAlignLeft(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
        {
            CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
        }

        /// <summary>
        /// 设置连续区域水平居右
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        public void SetHAlignRight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell)
        {
            CurSheet.get_Range(objStartCell, objEndCell).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;
        }

        /// <summary>
        /// 设置连续区域的显示格式
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        /// <param name="strNF">如"#,##0.00"的显示格式</param>
        public void SetNumberFormat(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, string strNF)
        {
            CurSheet.get_Range(objStartCell, objEndCell).NumberFormat = strNF;
        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="strColID">列标识，如A代表第一列</param>
        /// <param name="dblWidth">宽度</param>
        public void SetColumnWidth(Microsoft.Office.Interop.Excel._Worksheet CurSheet, string strColID, double dblWidth)
        {
            ((Microsoft.Office.Interop.Excel.Range)CurSheet.Columns.GetType().InvokeMember("Item", System.Reflection.BindingFlags.GetProperty, null, CurSheet.Columns, new object[] { (strColID + ":" + strColID).ToString() })).ColumnWidth = dblWidth;
        }

        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        /// <param name="dblWidth">宽度</param>
        public void SetColumnWidth(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblWidth)
        {
            CurSheet.get_Range(objStartCell, objEndCell).ColumnWidth = dblWidth;
        }

        /// <summary>
        /// 设置行高
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objStartCell">开始单元格</param>
        /// <param name="objEndCell">结束单元格</param>
        /// <param name="dblHeight">行高</param>
        public void SetRowHeight(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objStartCell, object objEndCell, double dblHeight)
        {
            CurSheet.get_Range(objStartCell, objEndCell).RowHeight = dblHeight;
        }

        /// <summary>
        /// 为单元格添加超级链接
        /// </summary>
        /// <param name="CurSheet">Worksheet</param>
        /// <param name="objCell">单元格</param>
        /// <param name="strAddress">链接地址</param>
        /// <param name="strTip">屏幕提示</param>
        /// <param name="strText">链接文本</param>
        public void AddHyperLink(Microsoft.Office.Interop.Excel._Worksheet CurSheet, object objCell, string strAddress, string strTip, string strText)
        {
            CurSheet.Hyperlinks.Add(CurSheet.get_Range(objCell, objCell), strAddress, mValue, strTip, strText);
        }

        /// <summary>
        /// 另存为xls文件
        /// </summary>
        /// <param name="CurBook">Workbook</param>
        /// <param name="strFilePath">文件路径</param>
        public void Save(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath)
        {
            CurBook.SaveCopyAs(strFilePath);
        }

        /// <summary>
        /// 保存文件
        /// </summary>
        /// <param name="CurBook">Workbook</param>
        /// <param name="strFilePath">文件路径</param>
        public void SaveAs(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath)
        {
            CurBook.SaveAs(strFilePath, mValue, mValue, mValue, mValue, mValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, mValue, mValue, mValue, mValue, mValue);
        }

        /// <summary>
        /// 另存为html文件
        /// </summary>
        /// <param name="CurBook">Workbook</param>
        /// <param name="strFilePath">文件路径</param>
        public void SaveHtml(Microsoft.Office.Interop.Excel._Workbook CurBook, string strFilePath)
        {
            CurBook.SaveAs(strFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlHtml, mValue, mValue, mValue, mValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, mValue, mValue, mValue, mValue, mValue);
        }

        /// <summary>
        /// 释放内存
        /// </summary>
        public void Dispose(Microsoft.Office.Interop.Excel._Worksheet CurSheet, Microsoft.Office.Interop.Excel._Workbook CurBook, Microsoft.Office.Interop.Excel._Application CurExcel)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurSheet);
                CurSheet = null;
                CurBook.Close(false, mValue, mValue);
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurBook);
                CurBook = null;

                CurExcel.Quit();
                System.Runtime.InteropServices.Marshal.ReleaseComObject(CurExcel);
                CurExcel = null;

                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
            catch (System.Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                foreach (System.Diagnostics.Process pro in System.Diagnostics.Process.GetProcessesByName("Excel"))
                    //if (pro.StartTime < DateTime.Now)
                    pro.Kill();
            }
            System.GC.SuppressFinalize(this);

        }
    }
}

[mytimes_walker]

应该够你用了。

[chwplay]

厉害，可以问你一个问题吗？怎么向Excel中添加下拉列表呢？还有怎么把Excel的单元格设置为只读呢？好期待哦！

[chwplay]

设置成文本的样子，你只需要在添加的前面增加一个“’”就可以了，显示出来的就是文本样子了，其实你在添加到单元格的时候都在前面添加一个“’”就可以了！

[kid_wang]

版本控制的，smart图形之类的，高手支个招，我也困扰好久了。用C#操作Excel生成。

[chisetiankong7668]

哥（姐）我仰慕你！

[shllshl]

ws.Cells[count, 1] = this.dgvRawMaterialEnter.Rows[i].Cells[2].Value.ToString()+ "\t";
我的是这么解决的！

[Lily_1223]

有没有设置单元格为只读的方法呢？