更新下Excel操作类

最新推荐文章于 2024-09-28 12:06:29 发布
最新推荐文章于 2024-09-28 12:06:29 发布
阅读量1.5k 收藏 6
点赞数
文章标签： excel string object 工作 null 活动
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lanwilliam/article/details/2552457
版权 
/**

 * Excel控制类

 * 作者：王文斌

 * 版本：bate2  2008-6-6

 * 备注：部分方法未经测试，使用时请注意。新增复制模板方法。支持26列以上表格。

 * 使用方法：确保已安装office03，并安装Excel的.net开发支持

 * 引用com口dll

 * */

using System;

using System.Reflection;

using Microsoft.Office.Interop.Excel;

using Microsoft.Office.Core;

using System.Drawing;



namespace WWBClassLib.Excel

{

    /// <summary>

    /// ExcelClass 的摘要说明。

    /// </summary>

    public class ExcelCtrl

    {

        /// <summary>

        /// 构建ExcelClass类

        /// </summary>

        public ExcelCtrl()

        {

            this.m_objExcel = new Microsoft.Office.Interop.Excel.Application();

        }

        /// <summary>

        /// 构建ExcelClass类

        /// </summary>

        /// <param name="objExcel">Excel.Application</param>

        public ExcelCtrl(Microsoft.Office.Interop.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();

            int i = x % 26;

            int j = x / 26;



            //if (x >= 26) { return ""; }

            string s = "";

            if (j > 0)

            {



                s = s + AChars[j - 1].ToString() + AChars[i].ToString();





            }

            else

            {

                s = s + AChars[x - 1].ToString();

            }

            //s = s + AChars[x - 1].ToString();

            s = s + y.ToString();

            return s;

        }



        /// <summary>

        /// 获取当前活动Sheet的Range的值

        /// </summary>

        /// <param name="y"></param>

        /// <param name="x"></param>

        /// <returns></returns>

        public string GetRangeText(int y, int x)

        {

            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);

            return range.Text.ToString();

        }



        /// <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)

        {

            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);

            range.set_Value(miss, text);

            if (align.ToUpper() == "CENTER")

            {

                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

            }

            if (align.ToUpper() == "LEFT")

            {

                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

            }

            if (align.ToUpper() == "RIGHT")

            {

                range.HorizontalAlignment = Microsoft.Office.Interop.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)

        {

            Microsoft.Office.Interop.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);

            Microsoft.Office.Interop.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>

        /// 给单元格赋值4

        /// </summary>

        /// <param name="x">行号</param>

        /// <param name="y">列号</param>

        /// <param name="text">值</param>

        /// <param name="font">字符格式</param>

        public void setValue(int y, int x, string text, float size)

        {

            this.setValue(x, y, text);

            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss);

            range.Font.Size = size;



        }



        //public void CopeTo(string fromSheetName,string startCell,string endCell,string ToSheetName)

        //{



        //    Microsoft.Office.Interop.Excel.Worksheet fromsheet = GetSheet(fromSheetName);

        //    Microsoft.Office.Interop.Excel.Worksheet tosheet = GetSheet(ToSheetName);

        //    Microsoft.Office.Interop.Excel.Range rangefrom = fromsheet.get_Range(startCell, endCell);

        //    Microsoft.Office.Interop.Excel.Range rangeto = tosheet.get_Range(startCell, endCell);

        //    //rangeto.Copy(rangefrom);

        //    tosheet.Paste(rangefrom, rangeto);

        //}



        /// <summary>

        /// 复制sheet

        /// 修正完成，重命名即可

        /// 

        /// </summary>

        /// <param name="fromSheetName"></param>

        /// <param name="ToSheetName"></param>

        /// <returns></returns>

        public void CopySheet(string fromSheetName, string ToSheetName)

        {

            Microsoft.Office.Interop.Excel.Worksheet fromsheet = GetSheet(fromSheetName);



            fromsheet.Copy(fromsheet, miss);



            ReNameSheet(fromSheetName + " (2)", ToSheetName);



        }

        /// <summary>

        /// 插入新行 将设定行拷贝，然后插入空行，再将设定行粘贴到新行上

        /// </summary>

        /// <param name="y">模板行号</param>

        public void insertRow(int y)

        {

            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));

            range.Copy(miss);

            range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, miss);

            range.get_Range(GetAix(1, y), GetAix(25, y));

            range.Select();

            sheet.Paste(miss, miss);



        }



        //public void insertCopyRow(int x1,int y1,int x2,int y2)

        //{

        //    Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y));

        //    range.Copy(miss);

        //    range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, miss);

        //    range.get_Range(GetAix(1, y), GetAix(25, y));

        //    range.Select();

        //    sheet.Paste(miss, miss);



        //}



        / <summary>

        / 把剪切内容粘贴到指定区域

        / </summary>

        public void past(int x1, int y1, int x2, int y2, int x3, int y3, int x4, int y4)

        {

            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(GetAix(x1, y1), GetAix(x2, y2));

            range.Copy(miss);

            sheet.Paste(sheet.get_Range(this.GetAix(x3, y3), this.GetAix(x4, y4)), range);

        }



        /// <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)

        {

            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));

            range.Borders.Weight = Width;

        }







        /// <summary>

        /// 合并单元格，并设置居中方式 测试有问题 修正

        /// </summary>

        /// <param name="x1"></param>

        /// <param name="y1"></param>

        /// <param name="x2"></param>

        /// <param name="y2"></param>

        /// <param name="align">居中方式：center,left,right</param>

        public void mergeCell(int x1, int y1, int x2, int y2, string align)

        {

            UniteCells(sheet, x1, y1, x2, y2);

            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));

            //range.Merge(true);

            if (align.ToUpper() == "CENTER")

            {

                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;

            }

            if (align.ToUpper() == "LEFT")

            {

                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

            }

            if (align.ToUpper() == "RIGHT")

            {

                range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

            }

        }

        /// <summary>

        /// 获取Range区域

        /// </summary>

        /// <param name="x1"></param>

        /// <param name="y1"></param>

        /// <param name="x2"></param>

        /// <param name="y2"></param>

        /// <returns></returns>

        public Microsoft.Office.Interop.Excel.Range getRange(int x1, int y1, int x2, int y2)

        {

            Microsoft.Office.Interop.Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));

            return range;

        }



        private object miss = Missing.Value; //忽略的参数OLENULL 

        private Microsoft.Office.Interop.Excel.Application m_objExcel;//Excel应用程序实例 

        private Microsoft.Office.Interop.Excel.Workbooks m_objBooks;//工作表集合 

        private Microsoft.Office.Interop.Excel.Workbook m_objBook;//当前操作的工作表 

        private Microsoft.Office.Interop.Excel.Worksheet sheet;//当前操作的表格 



        /// <summary>

        /// 活动工作Sheet

        /// </summary>

        public Microsoft.Office.Interop.Excel.Worksheet CurrentSheet

        {

            get

            {

                return sheet;

            }

            set

            {

                this.sheet = value;

            }

        }



        /// <summary>

        /// 获取活动WorkBooks

        /// </summary>

        public Microsoft.Office.Interop.Excel.Workbooks CurrentWorkBooks

        {

            get

            {

                return this.m_objBooks;

            }

            set

            {

                this.m_objBooks = value;

            }

        }



        /// <summary>

        /// 获取活动WorkBook

        /// </summary>

        public Microsoft.Office.Interop.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 = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;



            m_objBook = m_objExcel.ActiveWorkbook;

            sheet = (Microsoft.Office.Interop.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;

        }



        /// <summary>

        /// 创建Excel文件

        /// </summary>

        public void CreateExceFile()

        {

            UserControl(false);

            m_objBooks = (Microsoft.Office.Interop.Excel.Workbooks)m_objExcel.Workbooks;

            m_objBook = (Microsoft.Office.Interop.Excel.Workbook)(m_objBooks.Add(miss));

            sheet = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.ActiveSheet;

        }



        /// <summary>

        /// Excel文件另存为

        /// </summary>

        /// <param name="FileName"></param>

        public void SaveAs(string FileName)

        {



            m_objBook.SaveAs(FileName, miss, miss, miss, miss,

             miss, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,

             Microsoft.Office.Interop.Excel.XlSaveConflictResolution.xlLocalSessionChanges,

             miss, miss, miss, miss);

            //m_objBook.Close(false, miss, miss); 

        }



        /// <summary>

        /// 释放Excel文件

        /// </summary>

        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="SheetName">Sheet名</param>

        /// <returns>Sheet对象</returns>

        public Microsoft.Office.Interop.Excel.Worksheet GetSheet(string SheetName)

        {

            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[SheetName];

            return s;

        }



        /// <summary>

        /// 添加一个工作表

        /// </summary>

        /// <param name="SheetName"></param>

        /// <returns></returns>

        public Microsoft.Office.Interop.Excel.Worksheet AddSheetWithReturn(string SheetName)

        {

            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            s.Name = SheetName;

            return s;

        }



        /// <summary>

        /// 添加一个工作表

        /// </summary>

        public void AddSheet(string SheetName)

        {

            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);

            s.Name = SheetName;



        }



        /// <summary>

        /// 删除一个工作表

        /// </summary>

        /// <param name="SheetName"></param>

        public void DelSheet(string SheetName)

        {

            ((Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[SheetName]).Delete();

        }



        /// <summary>

        /// 重命名工作表

        /// </summary>

        /// <param name="OldSheetName">原名</param>

        /// <param name="NewSheetName">新名</param>

        /// <returns></returns>

        public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(string OldSheetName, string NewSheetName)//重命名一个工作表一

        {

            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_objBook.Worksheets[OldSheetName];

            s.Name = NewSheetName;

            return s;

        }



        public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)//重命名一个工作表二

        {



            Sheet.Name = NewSheetName;



            return Sheet;

        }





        /// <summary>

        /// 设置单元格值

        /// </summary>

        /// <param name="ws"></param>

        /// <param name="x"></param>

        /// <param name="y"></param>

        /// <param name="value"></param>

        public void SetCellValue(Microsoft.Office.Interop.Excel.Worksheet ws, int x, int y, object value)

        {

            ws.Cells[y, x] = value;

        }



        /// <summary>

        /// 设置单元格值

        /// </summary>

        /// <param name="ws">sheet名</param>

        /// <param name="x"></param>

        /// <param name="y"></param>

        /// <param name="value"></param>

        public void SetCellValue(string ws, int x, int y, object value)

        {



            GetSheet(ws).Cells[y, x] = value;

        }





        public void SetCellProperty(Microsoft.Office.Interop.Excel.Worksheet ws, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)

        //设置一个单元格的属性   字体，   大小，颜色   ，对齐方式

        {

            //name = "宋体";

            //size = 12;

            color = Microsoft.Office.Interop.Excel.Constants.xlAutomatic;

            HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlRight;

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color;

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = HorizontalAlignment;

        }



        /// <summary>

        /// 设置单元格属性

        /// </summary>

        /// <param name="wsn">Sheet页名称</param>

        /// <param name="Startx">x1</param>

        /// <param name="Starty">y1</param>

        /// <param name="Endx">x2</param>

        /// <param name="Endy">y2</param>

        /// <param name="size">字体大小</param>

        /// <param name="name">字体名称</param>

        /// <param name="color">字体颜色</param>

        /// <param name="HorizontalAlignment">对齐方式</param>

        public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, Microsoft.Office.Interop.Excel.Constants color, Microsoft.Office.Interop.Excel.Constants HorizontalAlignment)

        {

            //name = "宋体";

            //size = 12;

            //color = Excel.Constants.xlAutomatic;

            //HorizontalAlignment = Excel.Constants.xlRight;





            Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color;



            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = HorizontalAlignment;

        }



        /// <summary>

        /// 设置单元格属性

        /// </summary>

        /// <param name="wsn">Sheet页名称</param>

        /// <param name="Startx">x1</param>

        /// <param name="Starty">y1</param>

        /// <param name="Endx">x2</param>

        /// <param name="Endy">y2</param>

        /// <param name="size">字体大小</param>

        /// <param name="name">字体名称</param>

        /// <param name="color">字体颜色名称（系统预定义颜色）</param>

        /// <param name="HorizontalAlignment">对齐方式</param>

        public void SetCellProperty(string wsn, int Startx, int Starty, int Endx, int Endy, int size, string name, string colorName, string HorizontalAlignment)

        {

            //name = "宋体";

            //size = 12;

            //color = Excel.Constants.xlAutomatic;

            //HorizontalAlignment = Excel.Constants.xlRight;





            Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Name = name;

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Size = size;



            Color color = Color.FromName(colorName);

            color = Color.FromArgb(color.B, color.G, color.R);



            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Font.Color = color.ToArgb();



            if (HorizontalAlignment.ToUpper() == "CENTER")

            {



                ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; ;

            }

            if (HorizontalAlignment.ToUpper() == "LEFT")

            {

                ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;

            }

            if (HorizontalAlignment.ToUpper() == "RIGHT")

            {

                ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignRight;

            }



        }



        /// <summary>

        /// 设置单元格底色

        /// </summary>

        /// <param name="wsn">sheet页名称</param>

        /// <param name="Startx">x1</param>

        /// <param name="Starty">y1</param>

        /// <param name="Endx">x2</param>

        /// <param name="Endy">y2</param>

        /// <param name="colorName">系统颜色名称</param>

        public void SetBorderColor(string wsn, int Startx, int Starty, int Endx, int Endy, string colorName)

        {

            Color color = Color.FromName(colorName);

            color = Color.FromArgb(color.B, color.G, color.R);

            Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Cells.Interior.Color = color.ToArgb();

            ws.get_Range(ws.Cells[Starty, Startx], ws.Cells[Endy, Endx]).Cells.Interior.Pattern = Microsoft.Office.Interop.Excel.XlBackground.xlBackgroundAutomatic;



        }



        //合并单元格 测试ok

        public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)

        {

            ws.get_Range(ws.Cells[y1, x1], ws.Cells[y2, x2]).Merge(Type.Missing);

        }



        //合并单元格 测试ok

        public void UniteCells(string ws, int x1, int y1, int x2, int y2)

        {

            GetSheet(ws).get_Range(GetSheet(ws).Cells[y1, x1], GetSheet(ws).Cells[y2, x2]).Merge(Type.Missing);



        }



        //将内存中数据表格插入到Excel指定工作表的指定位置 为在使用模板时控制格式时使用一

        public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)

        {



            for (int i = 0; i < dt.Rows.Count; i++)

            {

                for (int j = 0; j < dt.Columns.Count; j++)

                {

                    GetSheet(ws).Cells[startY + i, j + startX] = dt.Rows[i][j].ToString();



                }



            }



        }



        //将内存中数据表格插入到Excel指定工作表的指定位置二

        public void InsertTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)

        {

            for (int i = 0; i < dt.Rows.Count; i++)

            {

                for (int j = 0; j < dt.Columns.Count; j++)

                {



                    ws.Cells[startY + i, j + startX] = dt.Rows[i][j];



                }



            }



        }



        //插入图片操作一

        public void InsertPictures(string Filename, string ws, int x, int y, int wight, int lenth)

        {

            //后面的数字表示位置

            GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, x, y, wight, lenth);



        }



        //public void InsertPictures(string Filename, string ws, int Height, int Width)

        //插入图片操作二

        //{

        //    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);

        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;

        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;

        //}

        //public void InsertPictures(string Filename, string ws, int left, int top, int Height, int Width)

        //插入图片操作三

        //{



        //    GetSheet(ws).Shapes.AddPicture(Filename, MsoTriState.msoFalse, MsoTriState.msoTrue, 10, 10, 150, 150);

        //    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementLeft(left);

        //    GetSheet(ws).Shapes.get_Range(Type.Missing).IncrementTop(top);

        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Height = Height;

        //    GetSheet(ws).Shapes.get_Range(Type.Missing).Width = Width;

        //}



    }

}

修正了原来只有26列的问题，现在列数可以与Excel ZZ列兼容
添加sheet复制方法，可以创建一个sheet作为模板，通过CopySheet方法复制sheet模板，在一个报表内分sheet生成报表。
修改insert及past方法，方便动态添加表格行。
修正mergeCell方法，合并新插入的行的标题
b哈利路亚d
关注
Excel文件操作类
07-27
NULL 博文链接：https://zyy200411.iteye.com/blog/756412
Excel操作类
07-16 184
using System; using System.Collections.Generic; using System.Linq; using System.Text; using Excel = Microsoft.Office.Interop.Excel; using System.Data; using System.Data.OleDb; using System.Wind
EXCEL操作类
weixin_30652271的博客
08-28 111
using System; using System.Diagnostics; //using Excel; namespace DotNet.Utilities { /// <summary> /// 操作EXCEL导出数据报表的类 /// </summary> public class DataToExcel...
C#实现的Excel文件操作类实例
08-30
"C#实现的Excel文件操作类实例" 本文主要介绍了C#实现的Excel文件操作类实例，结合具体实例形式分析了C#数据库及Excel文件相关操作技巧。 首先，我们需要了解什么是Excel文件操作类。在C#中，我们可以使用...
易语言COM实现EXCEL操作类
08-17
在这个"易语言COM实现EXCEL操作类"中，我们可以学习到如何使用易语言来控制Excel应用程序，执行一系列与Excel相关的任务。下面我们将深入探讨这些知识点： 1. **创建Excel对象**：在易语言中，我们首先需要创建一个...
易语言EXCEL表格对象操作类2.3模块源码-易语言
06-13
具体到“excel_2.3.e”这个文件，它是易语言的模块文件，包含了上述所述的Excel操作类的源代码。开发者可以通过打开并阅读这个模块文件，学习如何在易语言中实现Excel的高级操作。这不仅有助于提升编程技能，还能...
EXCEL表格对象操作类2.3模块.rar
04-04
EXCEL表格对象操作类2.3模块.rar EXCEL表格对象操作类2.3模块.rar EXCEL表格对象操作类2.3模块.rar EXCEL表格对象操作类2.3模块.rar EXCEL表格对象操作类2.3模块.rar EXCEL表格对象操作类2.3模块.rar
excel 实用操作类
changyuan101的博客
02-29 240
maven 依赖 org.apache.poi poi 3.9 org.apache.poi poi-ooxml 3.9 org.apache.poi poi-ooxml-schemas 3.9 /**  *   */ public class PoiUtil { /** * excel版本值 */ private static
excel操作类库
12-29
多种方式操作excel。NOPI，OLDB，以及读取.csv文件
Python操作Excel的一些基本程序代码
veratata的博客
01-11 1259
今天跟大家分享一下Python操作Excel的一些基本程序代码，会有示例代码来演示使用Python操作Excel的一些常用操作。
C#操作Excel类，功能比较全
知行合一
12-21 4011
一个C#操作Excel类，功能比较全
C#常用工具类——Excel操作类
weixin_33800593的博客
08-16 605
/// 常用工具类——Excel操作类 /// &lt;para&gt;　------------------------------------------------&lt;/para&gt; /// &lt;para&gt;　CreateConnection：根据Excel文件路径和EXCEL驱动版本生成OleConnection对象实例&lt;/para&gt; ...
Excel的类、方法和Excel操作
qq_50587186的博客
07-13 1106
excel主要有四个类: Workbook.class(整个Excel文件)、 Sheet.class(Excel文件中的每个页)、 Row.class(页的行)、 Cell.class(行的每个单元格) 它们的层次： 由Workbook类对象（如名为workBook）获得/创建Sheet类对象（如名为sheet）， 再由Sheet类对象获得/创建Row类对象（如名为row）， 由Row类对象获得/创建Cell类对象（如名为cell） 常用的方法如下： 1. Workbook.cla......
封装poi操作Excel工具类
戈多
02-15 3679
原文点这里： 在上一篇文章《使用poi读写Excel》中分享了一下poi操作Excel的简单示例，这次要分享一下我封装的一个Excel操作的工具类。        该工具类主要完成的功能是：读取Excel、汇总Excel的功能。在读取时，可以设定开始和结束读取的位置、设定是否读取多个sheet、设定读取那个或者那些sheet等。在汇总时，如设定是否覆盖目标文件、设定是否比较
proc的insert操作
linlin1023的专栏
06-20 1434
/*  *proc的insert操作  *源文件：insert.pc  */#include#include#includeEXEC SQL INCLUDE SQLCA;EXEC SQL BEGIN DECLARE SECTION;    char db_name[25] = "dbname";EXEC SQL END DECLARE SECTION;void errorHandle(char *sm){    char buf[1024];    long bufLen,msgLen,fc;    spr
Python 在Excel中创建、更新和删除数据透视表
nuclear2011的博客
09-27 1356
本文介绍了如何使用Python在Excel中添加、更新和删除数据透视表。包含详细的介绍和代码示例。
【Linux】Linux环境基础开发工具使用
最新发布
Lumos_yyyx的博客
09-28 1033
vim的三种模式，分别是命令模式（command mode）、插入模式（Insert mode）和底行模式（last line mode）。正常/普通/命令模式：插入模式：末行模式：在命令模式下，即可进入该模式。
Java Excel操作工具类与依赖包获取指南
"这篇资源主要提供了Java中进行Excel操作的工具类ExcelUtil.java的源代码以及所需的依赖包下载链接，适合需要在Java项目中处理Excel文件的开发者参考使用。" 在Java开发中，处理Excel文件是一项常见的任务，尤其是...
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值