C#常规操作EXCEL

最新推荐文章于 2022-07-16 21:24:44 发布

cyb331

最新推荐文章于 2022-07-16 21:24:44 发布

阅读量717

点赞数

分类专栏： .net 文章标签： c# string excel 工作 application null

本文链接：https://blog.csdn.net/cyb331/article/details/7827104

版权

.net 专栏收录该内容

42 篇文章 0 订阅

订阅专栏

使用需要添加引用： using Microsoft.Office.Interop.Excel;

public class ExcelHelper
{

        object MissingValue = Type.Missing;
        private Microsoft.Office.Interop.Excel.Application app = new Application();
        public Microsoft.Office.Interop.Excel.Workbook Wbook { get; set; }
        public Microsoft.Office.Interop.Excel.Worksheet Wsheet { get; set; }
        public ExcelHelper()
        { }
        public ExcelHelper(string FileName)
        {
            this.Wbook = app.Workbooks.Open(FileName, MissingValue,
                                        MissingValue, MissingValue, MissingValue,
                                        MissingValue, MissingValue, MissingValue,
                                        MissingValue, MissingValue, MissingValue,
                                        MissingValue, MissingValue, MissingValue,
                                        MissingValue);
            this.Wsheet = this.Wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
        }
        public ExcelHelper(string FileName, string SheetName)
        {
            this.Wbook = app.Workbooks.Open(FileName, MissingValue,
                                        MissingValue, MissingValue, MissingValue,
                                        MissingValue, MissingValue, MissingValue,
                                        MissingValue, MissingValue, MissingValue,
                                        MissingValue, MissingValue, MissingValue,
                                        MissingValue);

            this.Wsheet = (Microsoft.Office.Interop.Excel.Worksheet)Wbook.Worksheets[SheetName];
            // this.Wsheet = this.Wbook.ActiveSheet as Microsoft.Office.Interop.Excel.Worksheet;
        }
        /// <summary>
        /// 获取一个工作薄
        /// </summary>
        /// <param name="fileName"></param>
        /// <returns></returns>
        public Microsoft.Office.Interop.Excel.Workbook SetWorkBook(string fileName)
        {
            return app.Workbooks.Open(fileName, MissingValue,
                                                    MissingValue, MissingValue, MissingValue,
                                                    MissingValue, MissingValue, MissingValue,
                                                    MissingValue, MissingValue, MissingValue,
                                                    MissingValue, MissingValue, MissingValue,
                                                    MissingValue);
        }
        /// <summary>
        /// 获取一个工作表
        /// </summary>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)Wbook.Worksheets[SheetName];
            return s;
        }
        /// <summary>
        /// 给单元格赋值
        /// </summary>
        /// <param name="workSheet">工作单</param>
        /// <param name="x">行</param>
        /// <param name="y">表</param>
        /// <param name="value">值</param>
        public void SetValue(int x, int y, string value)
        {
            Wsheet.Cells[y, x] = value;
        }
        /// <summary>
        /// 设置列宽
        /// </summary>
        /// <param name="startColumn">起始列(列对应的字母)</param>
        /// <param name="endColumn">结束列(列对应的字母)</param>
        /// <param name="width"></param>
        public void SetColumnWidth(string startColumn, string endColumn, int width)
        {
            Range range = (Range)Wsheet.Columns[startColumn + ":" + endColumn, System.Type.Missing];
            range.ColumnWidth = width;
        }
        /// <summary>
        /// 自动调整列宽
        /// </summary>
        /// <param name="columnNum">列号</param>
        public void ColumnAutoFit(string col)
        {
            string strcolumnNum = col;
            Range range = (Range)Wsheet.Columns[strcolumnNum + ":" + strcolumnNum, System.Type.Missing];
            range.EntireColumn.AutoFit();

        }
        /// <summary>
        /// 设置单元格字体和大小
        /// </summary>
        /// <param name="startLine">起始行</param>
        /// <param name="endLine">结束行</param>
        /// <param name="col">要设置的列</param>
        /// <param name="fontName">字体名称</param>
        /// <param name="fontSize">字体大小</param>
        public void FontNameSize(int startLine, int endLine, string col, string fontName, int fontSize, bool isBold)
        {
            Range range = Wsheet.get_Range(col + startLine.ToString(), col + endLine.ToString());
            range.Font.Name = fontName;
            range.Font.Size = fontSize;
            range.Font.Bold = isBold;
        }
       /// <summary>
       /// 设置字体加粗
       /// </summary>
        /// <param name="startLine">起始行</param>
        /// <param name="endLine">结束行</param>
       /// <param name="arrCol">列数组</param>
        public void SetFontBold(int startLine, int endLine, string[] arrCol)
        {
            foreach (string col in arrCol)
            {
                Range range = Wsheet.get_Range(col + startLine.ToString(), col + endLine.ToString());
                range.Font.Bold = true;
            }
        }
        /// <summary>
        /// 设置边框
        /// </summary>
        /// <param name="startLine">起始行</param>
        /// <param name="endLine">结束行</param>
        /// <param name="arrCol">列数组</param>
        public void SetBorderStyle(int startLine, int endLine, string[] arrCol,BorderStyle borderStyle)
        {
            foreach (string col in arrCol)
            {
                Range range = Wsheet.get_Range(col + startLine.ToString(), col + endLine.ToString());
                range.Borders.LineStyle = borderStyle;
            }
        }

        /// <summary>
        /// 单元格背景色及填充方式
        /// </summary>
        /// <param name="startLine">起始行</param>
        /// <param name="endLine">结束行</param>
        /// <param name="col">要设置的列</param>
        /// <param name="color">颜色索引</param>
        /// <param name="pattern">填充方式</param>
        public void CellsBackColor(int startLine, int endLine, string col, ColorIndex color, Pattern pattern)
        {
            Range range = Wsheet.get_Range(col + startLine.ToString(), col + endLine.ToString());
            range.Interior.ColorIndex = color;
            range.Interior.Pattern = pattern;
        }
       /// <summary>
       /// 设置行列背景颜色
       /// </summary>
       /// <param name="startLine">起始行</param>
       /// <param name="endLine">结束行</param>
        /// <param name="arrCol">列名数组</param>
       /// <param name="color">颜色</param>
       /// <param name="pattern">填充方式</param>
        public void SetRowsBackColor(int startLine, int endLine, string[] arrCol, ColorIndex color, Pattern pattern)
        {
            foreach (string col in arrCol)
            {
                Range range = Wsheet.get_Range(col + startLine.ToString(), col + endLine.ToString());
                range.Interior.ColorIndex = color;
                range.Interior.Pattern = pattern;
            }
        }
        /// <summary>
        /// 合并指定EXCEL的单元格
        /// </summary>
        /// <param name="mySheet">指定的EXCEL工作表</param>
        /// <param name="startLine">起始行</param>
        /// <param name="endLine">结束行</param>
        /// <param name="col">要合并的列</param>
        public void MergeCell(int startLine, int endLine, string col)
        {
            int recCount = endLine - startLine + 1;
            string qy1 = Wsheet.get_Range(col + startLine.ToString(), col + startLine.ToString()).Text.ToString();//获得起始行合并列单元格的填充内容

            Range rg1;
            string strtemp = "";
            bool endCycle = false;

            //从起始行到终止行做循环
            for (int i = startLine; i <= recCount + startLine - 1 && !endCycle; )
            {
                for (int j = i + 1; j <= recCount + startLine - 1; j++)
                {
                    rg1 = Wsheet.get_Range(col + j.ToString(), col + j.ToString());//获得下一行的填充内容
                    strtemp = rg1.Text.ToString().Trim();

                    if (strtemp.Trim() == qy1.Trim())//内容等于初始内容
                    {
                        rg1 = Wsheet.get_Range(col + i.ToString(), col + j.ToString());//选取上条合并位置和当前行的合并区域
                        rg1.ClearContents();//清空要合并的区域
                        rg1.MergeCells = true;
                        if (col == "A")
                            Wsheet.Cells[i, 1] = qy1;
                        else if (col == "B")
                            Wsheet.Cells[i, 2] = qy1;

                        if (j == recCount + startLine - 1)
                        {
                            endCycle = true;
                        }
                    }
                    else//内容不等于初始内容
                    {
                        i = j;//i获取新值
                        qy1 = Wsheet.get_Range(col + j.ToString(), col + j.ToString()).Text.ToString();
                        break;
                    }
                }
            }
        }
       /// <summary>
       /// 将datatable中数据复制到excel中，不包含列名，例：col:"A",colIndex:"1"，则数据从A1开始复制
       /// </summary>
       /// <param name="table"></param>
       /// <param name="col">EXCEL列名，例如：“A”</param>
       /// <param name="colIndex">单元格号，例如：1</param>
        public void ToExcel(System.Data.DataTable table,string col,int colIndex)
        {

            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            /* 在调用Excel应用程序，或创建Excel工作簿之前，记着加上下面的代码
            * 这是因为Excel有一个Bug，如果你的操作系统的环境不是英文的，而Excel就会在执行下面的代码时，报异常。
            */
            System.Globalization.CultureInfo CurrentCI = System.Threading.Thread.CurrentThread.CurrentCulture; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");
            //先给Range对象一个范围，Range对象可以给一个CELL的范围，也可以给例如A1到H10这样的范围
            Range rangedata = Wsheet.get_Range(col+colIndex.ToString(), miss);
            Microsoft.Office.Interop.Excel.Range xlRang = null;
            //iRowCount为实际行数，最大行
            int iRowCount = table.Rows.Count;
            int iParstedRow = 0, iCurrSize = 0;
            //iEachSize为每次写行的数值，可以自己设置，每次写1000行和每次写2000行大家可以自己测试下效率
            int iEachSize = 1000;
            //iColumnAccount为实际列数，最大列数
            int iColumnAccount = table.Columns.Count;
            //在内存中声明一个iEachSize×iColumnAccount的数组，iEachSize是每次最大存储的行数，iColumnAccount就是存储的实际列数
            object[,] objVal = new object[iEachSize, iColumnAccount];
            try
            {
                iCurrSize = iEachSize;

                while (iParstedRow < iRowCount)
                {
                    if ((iRowCount - iParstedRow) < iEachSize)
                        iCurrSize = iRowCount - iParstedRow;
                    //用FOR循环给数组赋值
                    for (int i = 0; i < iCurrSize; i++)
                    {
                        for (int j = 0; j < iColumnAccount; j++)
                            objVal[i, j] = table.Rows[i+iParstedRow][j].ToString();
                    }
                    /*
                    * 例如A1到H10的意思是从A到H，第一行到第十行
                    * 下句很关键，要保证获取Sheet中对应的Range范围
                    * 下句实际上是得到这样的一个代码语句xlRang = worksheetData.get_Range("A2","H100");
                    * 注意看实现的过程
                    * 'A' + iColumnAccount - 1这儿是获取你的最后列，A的数字码为65，大家可以仔细看下是不是得到最后列的字母
                    * iParstedRow + iCurrSize + 1获取最后行
                    * iParstedRow + colIndex要注意，每次循环这个值不一样，他取决于你每次循环RANGE取了多大，循环了几次，也就是iEachSize设置值的大小哦
                    */
                    xlRang = Wsheet.get_Range(col + ((int)(iParstedRow + colIndex)).ToString(), ((char)(Convert.ToChar(col) + iColumnAccount - 1)).ToString() + ((int)(iParstedRow + iCurrSize + colIndex-1)).ToString());
                    // 调用Range的Value2属性，把内存中的值赋给Excel
                    xlRang.Value2 = objVal;
                    iParstedRow = iParstedRow + iCurrSize;
                }

                xlRang = null;



            }
            catch (Exception ex)
            {

                return;
            }
        }
        /// <summary>
        /// 结束EXCEL.EXE进程的方法
        /// </summary>
        /// <param name="m_objExcel">EXCEL对象</param>
        [System.Runtime.InteropServices.DllImport("user32.dll", SetLastError = true)]
        static extern int GetWindowThreadProcessId(IntPtr hWnd, out int lpdwProcessId);
        public void KillSpecialExcel(Microsoft.Office.Interop.Excel.Application m_objExcel)
        {
            try
            {
                if (m_objExcel != null)
                {
                    int lpdwProcessId;
                    GetWindowThreadProcessId(new IntPtr(m_objExcel.Hwnd), out lpdwProcessId); System.Diagnostics.Process.GetProcessById(lpdwProcessId).Kill();
                }
            }
            catch (Exception ex)
            {

            }
        }

        /// <summary>
        /// 将原文件保存
        /// </summary>
        public void Save()
        {
            Wbook.Save();
            //Dispose();
        }
        /// <summary>
        /// 另存
        /// </summary>
        /// <param name="fileNamePath"></param>
        public void Save(string fileNamePath)
        {
            Wbook.SaveAs(fileNamePath, MissingValue, MissingValue, MissingValue, MissingValue, MissingValue,
                        Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, MissingValue, MissingValue, MissingValue,
                        MissingValue, MissingValue);
            //Dispose();

        }
        public void Dispose()
        {

            //this.CurrentWorkbook.Close(true, this.FileName, missing);
            //app.FinalReleaseComObject(this.Wbook);
            this.Wbook = null;
            this.app.Quit();
            this.app = null;
            //调用方法关闭EXCEL进程，大家可以试下不用的话如果程序不关闭在进程里一直会有EXCEL.EXE这个进程并锁定你的EXCEL表格
            this.KillSpecialExcel(app);
            System.GC.Collect();
            System.GC.WaitForPendingFinalizers();
        }
        public enum BorderStyle
        {
            xlContinuous=1,
            xlDash,
            xlDashDot,
            xlDashDotDot,
            xlDot,
            xlDouble,
            xlLineStyleNone,
            xlSlantDashDot
        }
        /// <summary>
        /// 水平对齐方式
        /// </summary>
        public enum ExcelHAlign
        {
            常规 = 1,
            靠左,
            居中,
            靠右,
            填充,
            两端对齐,
            跨列居中,
            分散对齐
        }
        /// <summary>
        /// 单元格填充方式
        /// </summary>
        public enum Pattern
        {
            Automatic = -4105,
            Checker = 9,
            CrissCross = 16,
            Down = -4121,
            Gray16 = 17,
            Gray25 = -4124,
            Gray50 = -4125,
            Gray75 = -4126,
            Gray8 = 18,
            Grid = 15,
            Horizontal = -4128,
            LightDown = 13,
            LightHorizontal = 11,
            LightUp = 14,
            LightVertical = 12,
            None = -4142,
            SemiGray75 = 10,
            Solid = 1,
            Up = -4162,
            Vertical = -4166
        }
        /// <summary>
        /// 常用颜色定义,对就Excel中颜色名
        /// </summary>
        public enum ColorIndex
        {
            无色 = -4142,
            自动 = -4105,
            黑色 = 1,
            褐色 = 53,
            橄榄 = 52,
            深绿 = 51,
            深青 = 49,
            深蓝 = 11,
            靛蓝 = 55,
            灰色80 = 56,
            深红 = 9,
            橙色 = 46,
            深黄 = 12,
            绿色 = 10,
            青色 = 14,
            蓝色 = 5,
            蓝灰 = 47,
            灰色50 = 16,
            红色 = 3,
            浅橙色 = 45,
            酸橙色 = 43,
            海绿 = 50,
            水绿色 = 42,
            浅蓝 = 41,
            紫罗兰 = 13,
            灰色40 = 48,
            粉红 = 7,
            金色 = 44,
            黄色 = 6,
            鲜绿 = 4,
            青绿 = 8,
            天蓝 = 33,
            梅红 = 54,
            灰色25 = 15,
            玫瑰红 = 38,
            茶色 = 40,
            浅黄 = 36,
            浅绿 = 35,
            浅青绿 = 34,
            淡蓝 = 37,
            淡紫 = 39,
            白色 = 2
        }
    }