SANAS/Base/CommTool/OfficeFileHandleHelper.cs

using System;
using System.Collections.Generic;
using System.IO;
using System.Windows.Forms;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using Microsoft.Office.Core;

namespace SmartCoalApplication.Base.CommTool
{
    /// <summary>
    /// excel操作类
    /// </summary>
    public static class OfficeFileHandleHelper
    {
        private static Microsoft.Office.Interop.Excel.Application m_excel;
        private static Workbook m_workbook;
        private static Worksheet m_worksheet;
        private static Worksheet m2_worksheet;
        private static object missing = Type.Missing;

        /// <summary>
        /// 打开word和excel文档
        /// </summary>
        /// <param name="filePath"></param>
        public static bool OpenOfficeFile(string filePath)
        {
            bool isSucceed = false;//打开是否成功
            int fileType = FileOperationHelper.IsFileWordOrExcel(filePath);
            try
            {
                m_excel = new Microsoft.Office.Interop.Excel.Application();
                m_excel.Workbooks.Open(filePath);
                m_excel.Visible = true;

                isSucceed = true;
            }
            catch (Exception)
            {
                if (m_excel != null)
                {
                    m_excel.DisplayAlerts = false;
                    m_excel.Visible = false;
                }

                isSucceed = false;
            }
            m_excel = null;
            //其他
            return isSucceed;
        }

        /// <summary>
        /// 通过公式模板创建并保存excel
        /// </summary>
        /// <param name="excelModulePath">excel公式模板路径</param>
        /// <param name="saveAsPath">新文件另存路径</param>
        /// <param name="contentList">数据对象模型</param>
        /// <returns></returns>
        public static bool SaveAsExcelWithModule(string excelModulePath, string saveAsPath, List<List<string>> contentList, string dictPath = null, Dictionary<string, string> pNames = null
            , List<List<string>> content2List = null)
        {
            try
            {
                m_excel = new Microsoft.Office.Interop.Excel.Application();
                m_workbook = m_excel.Workbooks.Add(excelModulePath);
                m_worksheet = m_workbook.Sheets.Item[2];//数据固定插入在第二个sheet页
                Worksheet m_worksheet1 = m_workbook.Sheets.Item[1];//数据固定插入在第二个sheet页
                if (content2List != null)
                {
                    if (m_workbook.Sheets.Count < 3)
                        m_workbook.Sheets.Add(Missing.Value, m_worksheet, Missing.Value, Missing.Value);
                    m2_worksheet = m_workbook.Sheets.Item[3];
                    AddTableToExcelSheet(m2_worksheet, 1, 1, content2List);
                }
                if (dictPath != null)
                {
                    Microsoft.Office.Interop.Excel.Pictures pics = (Microsoft.Office.Interop.Excel.Pictures)m_worksheet1.Pictures(Type.Missing);
                    Dictionary<string, Microsoft.Office.Interop.Excel.Range> keyValuePairs = new Dictionary<string, Range>();
                    for (int i = 1; i <= pics.Count; i++)
                    {
                        //可以循环这个excel里面的图
                        Picture pic = pics.Item(i) as Picture;
                        //可以获取图的左上cell
                        Range topleft = pic.TopLeftCell;
                        //可以获取图的右下cell
                        Range rightbottom = pic.BottomRightCell;
                        keyValuePairs.Add(pic.Name, m_worksheet1.get_Range(topleft, rightbottom));
                    }
                    pics.Delete();
                    foreach (string d in Directory.GetFileSystemEntries(dictPath))
                    {
                        string pName = d.Replace(dictPath + "\\", string.Empty);
                        string pName1 = pName.Replace(".jpg", string.Empty);
                        Microsoft.Office.Interop.Excel.Range range;
                        keyValuePairs.TryGetValue(pName1, out range);
                        if (range != null)
                            InsertPicture(range, m_worksheet1, d, dictPath, pNames);
                    }
                }
                AddTableToExcelSheet(m_worksheet, 1, 1, contentList);
                //if (content2List != null)
                //    ((Worksheet)m_workbook.Sheets.Item[2]).Select();

                m_worksheet.SaveAs(saveAsPath, missing, missing, missing, missing, missing, missing, missing, missing, missing);
                m_workbook.Close(false);
                m_excel.Application.Quit();

                Kill(m_excel);

                m_worksheet = null;
                m2_worksheet = null;
                m_workbook = null;
                m_excel = null;
            }
            catch (Exception)
            {
                if (m_excel != null)
                {
                    if (m_workbook != null)
                        m_workbook.Close(false);

                    m_excel.Application.Quit();

                    Kill(m_excel);
                }

                m_worksheet = null;
                m2_worksheet = null;
                m_workbook = null;
                m_excel = null;

                return false;
            }
            return true;
        }

        /// 将图片插入到指定的单元格位置，并设置图片的宽度和高度。
        /// 注意：图片必须是绝对物理路径
        /// </summary>
        /// <param name="rng">Excel单元格选中的区域</param>
        /// <param name="PicturePath">要插入图片的绝对路径。</param>
        public static void InsertPicture(Microsoft.Office.Interop.Excel.Range rng, Microsoft.Office.Interop.Excel._Worksheet sheet, string PicturePath, string dictPath, Dictionary<string, string> pNames)
        {
            //rng.Select();
            float PicLeft, PicTop, PicWidth, PicHeight;
            try
            {
                PicLeft = Convert.ToSingle(rng.Left);
                PicTop = Convert.ToSingle(rng.Top);
                PicWidth = Convert.ToSingle(rng.Width);
                PicHeight = Convert.ToSingle(rng.Height);

                sheet.Shapes.AddPicture(PicturePath, MsoTriState.msoFalse, MsoTriState.msoTrue, PicLeft, PicTop, PicWidth, PicHeight);
                Microsoft.Office.Interop.Excel.Pictures pics = (Microsoft.Office.Interop.Excel.Pictures)sheet.Pictures(Type.Missing);
                string pName = PicturePath.Replace(dictPath + "\\", string.Empty);
                string pName1 = pName.Replace(".jpg", string.Empty);
                if (pics.Count > 0)
                {
                    Picture pic = pics.Item(pics.Count) as Picture;
                    string pName2;
                    pNames.TryGetValue(pName1, out pName2);
                    pic.Name = pName2;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("错误：" + ex.Message);
            }
        }

        /// <summary>
        /// 向excel的sheet页指定单元格插入表格(自行拼接的list形式)
        /// </summary>
        /// <param name="worksheet">sheet页</param>
        /// <param name="x">插入初始位置横坐标</param>
        /// <param name="y">插入初始位置纵坐标</param>
        /// <param name="contentList">表格对象</param>
        public static void AddTableToExcelSheet(Worksheet worksheet, int x, int y, List<List<string>> contentList)
        {
            if (contentList.Count > 0)
            {
                for (int i = x; i < contentList.Count + 1; i++)
                {
                    List<string> rowContent = contentList[i - 1];
                    if (rowContent.Count > 0)
                    {
                        for (int j = y; j < rowContent.Count + 1; j++)
                        {
                            worksheet.Cells[i, j] = rowContent[j - 1];
                        }
                    }

                }
            }
        }

        /// <summary>
        /// 杀死进程
        /// </summary>
        /// <param name="hwnd"></param>
        /// <param name="ID"></param>
        /// <returns></returns>
        [DllImport("User32.dll", CharSet = CharSet.Auto)]
        public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
        public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
        {
            IntPtr t = new IntPtr(excel.Hwnd);   //得到这个句柄，具体作用是得到这块内存入口 
            int k = 0;
            GetWindowThreadProcessId(t, out k);   //得到本进程唯一标志k
            System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k);   //得到对进程k的引用
            p.Kill();     //关闭进程k
        }

    }
}