Excel操控方法之Com组件

来源：互联网发布：金贵软件官网编辑：程序博客网时间：2024/05/14 05:39
using Microsoft.Office.Interop.Excel;using System;using System.Diagnostics;using System.Drawing;using System.Linq;using System.Runtime.InteropServices;using System.Text;using System.Windows.Forms;namespace ComTest{    public class MSExcel    {        //Excel重要元素节点        private Microsoft.Office.Interop.Excel.Application app;        private Workbooks workbooks;        private Workbook workbook;        private string excelPath;        public MSExcel(string excelPath)        {            init();            this.excelPath = excelPath;        }        //Excel        private void init()        {            app = new Microsoft.Office.Interop.Excel.Application();            app.Visible = false;            workbooks = app.Workbooks;        }        //载入Excel        public bool LoadExcel()        {            workbook = workbooks.Open(excelPath);            return !workbook.ReadOnly;        }        //创建Excel        public void CreateExcel(string worksheetName)        {            workbook = workbooks.Add(true);            ((Worksheet)workbook.Worksheets[1]).Name = worksheetName;            workbook.SaveAs(excelPath);        }        //强制杀掉Excel的所有进程        public void KillExcel()        {            Close();            Dispose();            Process[] procs = Process.GetProcessesByName("excel");            foreach (Process pro in procs)            {                pro.Kill();            }            init();            LoadExcel();            Save();            Close();            Dispose();            init();            LoadExcel();        }        //判断指定名称的工作表是否存在        public bool IsWorksheetExist(string worksheetName)        {            foreach (Worksheet worksheet in workbook.Worksheets)            {                if (worksheet != null && worksheet.Name != null && worksheet.Name == worksheetName)                {                    return true;                }            }            return false;        }        //添加指定名称工作表        public void AddWorksheet(string worksheetName)        {            workbook.Worksheets.Add(Type.Missing, workbook.Worksheets[workbook.Worksheets.Count], 1, Type.Missing);            ((Worksheet)workbook.Worksheets[workbook.Worksheets.Count]).Name = worksheetName;            workbook.Save();        }        //获取指定名称的工作表        public Worksheet GetWorksheet(string worksheetName)        {            foreach(Worksheet worksheet in workbook.Worksheets)            {                if(worksheet!=null && worksheet.Name!=null && worksheet.Name==worksheetName)                {                    return worksheet;                }            }            return null;        }        //Excel文档保存        public void Save()        {            workbook.Save();        }        //Excel文档另存为        public void SaveAs()        {            SaveFileDialog saveFileDialog = new SaveFileDialog();            saveFileDialog.Filter = "excel files (*.xls)|*.xls";            if (saveFileDialog.ShowDialog() == DialogResult.OK)            {                workbook.SaveCopyAs(saveFileDialog.FileName);            }        }        //关闭Excel文档        public void Close()        {            if (workbook != null)            {                workbook.Close();                workbook = null;            }        }        //释放当前Excel进程        public void Dispose()        {            if (workbooks != null)            {                workbooks.Close();                workbooks = null;            }            if (app != null)            {                app.Quit();                app = null;            }        }        public class MSSheet        {            private Worksheet worksheet;            private int lastRow = 1;            private int lastCol = 1;            public int MaxRow            {                get                {                    return worksheet.UsedRange.Rows.Count;                }            }            public MSSheet(Worksheet worksheet)            {                this.worksheet = worksheet;                lastRow = worksheet.UsedRange.Rows.Count;                string value = null;                for (int i = worksheet.UsedRange.Columns.Count; i > 0; i++)                {                    value = ((Range)(worksheet.Cells[1, i])).Text.ToString();                    if (value != null || value != "")                    {                        lastCol = i;                        break;                    }                }            }            //根据指定名称寻找第一行中与之内容相同的表格所在的列            public int GetColIndexByHeaderName(string colName)            {                int count = worksheet.UsedRange.Columns.Count + 1;                for (int i = 1; i < count; i++)                {                    if (((Range)(worksheet.Cells[1, i])).Text.ToString() == colName)                    {                        return i;                    }                }                worksheet.Cells[1, count] = colName;                return count;            }            //添加一行            public void AddRow()            {                lastRow += 1;            }            //添加一个元素            public void AddCell(object data)            {                lastCol += 1;                write(lastRow, lastCol, false, data, Color.Transparent);            }            //添加一个指定背景颜色的元素            public void AddCell(object data,Color color)            {                lastCol += 1;                write(lastRow, lastCol, false, data, color);            }            //添加一个超链接            public void AddHyperlink(object data)            {                lastCol += 1;                write(lastRow, lastCol, true, data, Color.Transparent);            }            //添加一个指定背景颜色的超链接            public void AddHyperlink(object data, Color color)            {                lastCol += 1;                write(lastRow, lastCol, true, data, color);            }            //指定行和列确定表格位置，写入内容            public void Write(int rowIndex, int colIndex, object data)            {                write(rowIndex, colIndex, false, data, Color.Transparent);            }            //指定行和列确定表格位置，指定背景颜色写入内容            public void Write(int rowIndex, int colIndex, object data, Color color)            {                write(rowIndex, colIndex, false, data, color);            }            //指定行和列确定表格位置，写入超链接            public void WriteHyperlink(int rowIndex, int colIndex, object data)            {                write(rowIndex, colIndex, true, data, Color.Transparent);            }            //指定行和列确定表格位置，指定背景颜色写入超链接            public void WriteHyperlink(int rowIndex, int colIndex, object data, Color color)            {                write(rowIndex, colIndex, true, data, color);            }            //写            private void write(int rowIndex, int colIndex, bool isHyperlink, object data, Color color)            {                Range range = (Range)worksheet.Cells[rowIndex, colIndex];                if (isHyperlink)                {                    worksheet.Hyperlinks.Add(range, data.ToString());                }                else                {                    range.Value2 = data.ToString();                }                if (color != Color.Transparent)                {                    range.Interior.Color = System.Drawing.ColorTranslator.ToOle(color);                }            }            //读            public string Read(int rowIndex, int colIndex)            {                return ((Range)(worksheet.Cells[rowIndex, colIndex])).Text.ToString();            }        }    }}
0 0