OTS/OTSIncAMeasureApp/5-OTSMeasureStatuImageFun/ExcelEdit.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OTSMeasureApp
{
    /// <SUMMARY>
    /// C#操作Excel类，更新日期2018-8-28,补充部份操作功能，后续功能在使用需要再进行完善
    /// </SUMMARY>
    public class ExcelEdit
    {
        #region 变量定义
        /// <summary>
        /// excel的文件名
        /// </summary>
        public string m_Filename;
        public Microsoft.Office.Interop.Excel.Application m_app;
        public Microsoft.Office.Interop.Excel.Workbooks m_wbs;
        public Microsoft.Office.Interop.Excel.Workbook m_wb;
        public Microsoft.Office.Interop.Excel.Worksheets m_wss;
        public Microsoft.Office.Interop.Excel.Worksheet m_ws;

        /// <summary>
        /// 使用的Excel版本号
        /// </summary>
        public int m_FormatNum = -4143;
        #endregion

        #region 构造函数
        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelEdit()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        #endregion

        #region 文件操作
        /// <summary>
        /// 创建一个Microsoft.Office.Interop.Excel对象
        /// </summary>
        public void Create()
        {
            m_app = new Microsoft.Office.Interop.Excel.Application();
            m_wbs = m_app.Workbooks;
            if (Convert.ToDouble(m_app.Version) < 12)
            {
                m_FormatNum = -4143;
            }
            else
            {
                m_FormatNum = 56;
            }
            m_wb = m_wbs.Add(true);

        }

        /// <summary>
        /// 打开一个Microsoft.Office.Interop.Excel文件
        /// </summary>
        /// <param name="FileName"></param>
        public void Open(string FileName)
        {
            m_app = new Microsoft.Office.Interop.Excel.Application();
            m_wbs = m_app.Workbooks;
            m_wb = m_wbs.Add(FileName);
            //wb = wbs.Open(FileName, 0, true, 5,"", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "t", false, false, 0, true,Type.Missing,Type.Missing);
            //wb = wbs.Open(FileName,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing);
            m_Filename = FileName;
        }


        /// <summary>
        /// 保存文档
        /// </summary>
        /// <returns></returns>
        public bool Save()
        {
            if (m_Filename == "")
            {
                return false;
            }
            else
            {
                try
                {
                    m_wb.Save();
                    return true;
                }

                catch (Exception ex)
                {
                    string str = ex.ToString();
                    return false;
                }
            }
        }

        /// <summary>
        /// 文档另存为
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public bool SaveAs(object FileName)
        {
            try
            {
                m_wb.SaveAs(FileName, m_FormatNum, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                return true;
            }
            catch (Exception ex)
            {
                string str = ex.ToString();
                return false;
            }
        }


        /// <summary>
        /// 文档保存为PDF格式
        /// </summary>
        /// <param name="FileName"></param>
        /// <returns></returns>
        public bool SaveAsPDF(string FileName)
        {
            try
            {
                //转PDF部份,好用是好用，但是格式不好控制
                m_ws.PageSetup.Zoom = false;//只有该项为false时，下面的才有效
                m_ws.PageSetup.FitToPagesWide = 1;
                m_ws.PageSetup.FitToPagesTall = false;
                m_ws.PageSetup.Orientation = Microsoft.Office.Interop.Excel.XlPageOrientation.xlLandscape;
                m_ws.PageSetup.PaperSize = Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;

                m_wb.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF,
                    FileName + ".pdf",
                    Microsoft.Office.Interop.Excel.XlFixedFormatQuality.xlQualityStandard,
                    Type.Missing,
                    false,
                    Type.Missing,
                    Type.Missing,
                    false,
                    Type.Missing);

                return true;
            }
            catch (Exception ex)
            {
                string str = ex.ToString();
                return false;
            }
        }


        /// <summary>
        /// 关闭一个Microsoft.Office.Interop.Excel对象，销毁对象
        /// </summary>
        public void Close()
        {
            m_wb.Close(Type.Missing, Type.Missing, Type.Missing);
            m_wbs.Close();
            m_app.Quit();
            m_wb = null;
            m_wbs = null;
            m_app = null;
            GC.Collect();
        }
        #endregion

        #region 工作表相关
        /// <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)m_wb.Worksheets[SheetName];
            return s;
        }

        /// <summary>
        /// 添加一个工作表
        /// </summary>
        /// <param name="SheetName"></param>
        /// <returns></returns>
        public Microsoft.Office.Interop.Excel.Worksheet AddSheet(string SheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet s = (Microsoft.Office.Interop.Excel.Worksheet)m_wb.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            s.Name = SheetName;
            return s;
        }

        /// <summary>
        /// 删除一个工作表
        /// </summary>
        /// <param name="SheetName"></param>
        public void DelSheet(string SheetName)
        {
            ((Microsoft.Office.Interop.Excel.Worksheet)m_wb.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_wb.Worksheets[OldSheetName];
            s.Name = NewSheetName;
            return s;
        }

        /// <summary>
        /// 重命名一个工作表
        /// </summary>
        /// <param name="Sheet"></param>
        /// <param name="NewSheetName"></param>
        /// <returns></returns>
        public Microsoft.Office.Interop.Excel.Worksheet ReNameSheet(Microsoft.Office.Interop.Excel.Worksheet Sheet, string NewSheetName)
        {
            Sheet.Name = NewSheetName;
            return Sheet;
        }
        #endregion

        #region 单元格相关
        /// <summary>
        /// 向单元格中设置值，ws：要设值的工作表     X行Y列     value   值
        /// </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[x, y] = value;
        }

        /// <summary>
        /// ws：要设值的工作表的名称 X行Y列 value 值
        /// </summary>
        /// <param name="ws"></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[x, y] = value;
        }

        /// <summary>
        /// 设置一个单元格的属性   字体，   大小，颜色   ，对齐方式
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="Startx"></param>
        /// <param name="Starty"></param>
        /// <param name="Endx"></param>
        /// <param name="Endy"></param>
        /// <param name="size"></param>
        /// <param name="name"></param>
        /// <param name="color"></param>
        /// <param name="HorizontalAlignment"></param>
        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[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).HorizontalAlignment = HorizontalAlignment;
        }

        /// <summary>
        /// 设置单元格的属性
        /// </summary>
        /// <param name="wsn"></param>
        /// <param name="Startx"></param>
        /// <param name="Starty"></param>
        /// <param name="Endx"></param>
        /// <param name="Endy"></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)
        {
            Microsoft.Office.Interop.Excel.Worksheet ws = GetSheet(wsn);
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Name = name;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Size = size;
            ws.get_Range(ws.Cells[Startx, Starty], ws.Cells[Endx, Endy]).Font.Color = color;

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

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        public void UniteCells(Microsoft.Office.Interop.Excel.Worksheet ws, int x1, int y1, int x2, int y2)
        {
            ws.get_Range(ws.Cells[x1, y1], ws.Cells[x2, y2]).Merge(Type.Missing);
        }

        /// <summary>
        /// 合并单元格
        /// </summary>
        /// <param name="ws"></param>
        /// <param name="x1"></param>
        /// <param name="y1"></param>
        /// <param name="x2"></param>
        /// <param name="y2"></param>
        public void UniteCells(string ws, int x1, int y1, int x2, int y2)
        {
            GetSheet(ws).get_Range(GetSheet(ws).Cells[x1, y1], GetSheet(ws).Cells[x2, y2]).Merge(Type.Missing);
        }
        #endregion

        #region 内存中操作相关
        /// <summary>
        /// 将内存中数据表格插入到Microsoft.Office.Interop.Excel指定工作表的指定位置 为在使用模板时控制格式时使用一
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ws"></param>
        /// <param name="startX"></param>
        /// <param name="startY"></param>
        public void InsertTable(System.Data.DataTable dt, string ws, int startX, int startY)
        {
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    GetSheet(ws).Cells[startX + i, j + startY] = dt.Rows[i][j].ToString();
                }
            }
        }

        /// <summary>
        /// 将内存中数据表格插入到Microsoft.Office.Interop.Excel指定工作表的指定位置二
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ws"></param>
        /// <param name="startX"></param>
        /// <param name="startY"></param>
        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 - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    ws.Cells[startX + i, j + startY] = dt.Rows[i][j];
                }
            }
        }

        /// <summary>
        /// 将内存中数据表格添加到Microsoft.Office.Interop.Excel指定工作表的指定位置一
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ws"></param>
        /// <param name="startX"></param>
        /// <param name="startY"></param>
        public void AddTable(System.Data.DataTable dt, string ws, int startX, int startY)
        {
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    GetSheet(ws).Cells[i + startX, j + startY] = dt.Rows[i][j];
                }
            }
        }

        /// <summary>
        /// 将内存中数据表格添加到Microsoft.Office.Interop.Excel指定工作表的指定位置二
        /// </summary>
        /// <param name="dt"></param>
        /// <param name="ws"></param>
        /// <param name="startX"></param>
        /// <param name="startY"></param>
        public void AddTable(System.Data.DataTable dt, Microsoft.Office.Interop.Excel.Worksheet ws, int startX, int startY)
        {
            for (int i = 0; i <= dt.Rows.Count - 1; i++)
            {
                for (int j = 0; j <= dt.Columns.Count - 1; j++)
                {
                    ws.Cells[i + startX, j + startY] = dt.Rows[i][j];
                }
            }
        }
        #endregion

        #region 图片操作相关
        /// <summary>
        /// 插入图片操作
        /// </summary>
        /// <param name="Filename"></param>
        /// <param name="ws"></param>
        public void InsertPictures(string Filename, string ws)
        {
            GetSheet(ws).Shapes.AddPicture(Filename, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, 10, 10, 30, 30);
        }

        /// <summary>
        /// 插入图片操作，重载
        /// </summary>
        /// <param name="Filename"></param>
        /// <param name="ws"></param>
        /// <param name="left"></param>
        /// <param name="top"></param>
        /// <param name="width"></param>
        /// <param name="height"></param>
        public void Insertpictures(string Filename, string ws, float left, float top, float width, float height)
        {
            GetSheet(ws).Shapes.AddPicture(Filename, Microsoft.Office.Core.MsoTriState.msoFalse, Microsoft.Office.Core.MsoTriState.msoTrue, left, top, width, height);
        }

        /// <summary>
        /// 插入图表操作
        /// </summary>
        /// <param name="ChartType"></param>
        /// <param name="ws"></param>
        /// <param name="DataSourcesX1"></param>
        /// <param name="DataSourcesY1"></param>
        /// <param name="DataSourcesX2"></param>
        /// <param name="DataSourcesY2"></param>
        /// <param name="ChartDataType"></param>
        public void InsertActiveChart(Microsoft.Office.Interop.Excel.XlChartType ChartType, string ws, int DataSourcesX1, int DataSourcesY1, int DataSourcesX2, int DataSourcesY2, Microsoft.Office.Interop.Excel.XlRowCol ChartDataType)
        {
            ChartDataType = Microsoft.Office.Interop.Excel.XlRowCol.xlColumns;
            m_wb.Charts.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
            {
                m_wb.ActiveChart.ChartType = ChartType;
                m_wb.ActiveChart.SetSourceData(GetSheet(ws).get_Range(GetSheet(ws).Cells[DataSourcesX1, DataSourcesY1], GetSheet(ws).Cells[DataSourcesX2, DataSourcesY2]), ChartDataType);
                m_wb.ActiveChart.Location(Microsoft.Office.Interop.Excel.XlChartLocation.xlLocationAsObject, ws);
            }
        }
        #endregion

        #region 颜色定义
        /// <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
        }
        #endregion


    }
}