JW/Framework/Bowin.Common/Utility/ExcelHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Excel;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data;
using System.Xml.Linq;

namespace Bowin.Common.Utility
{
    public enum ExcelFormat
    {
        Excel95 = Excel.XlFileFormat.xlExcel7,
        Excel95_97 = Excel.XlFileFormat.xlExcel9795,
        Excel2000_03 = Excel.XlFileFormat.xlExcel8,
        Excel2007 = Excel.XlFileFormat.xlExcel12,
        Excel2010 = Excel.XlFileFormat.xlExcel4
    }

    /// <summary>
    /// 使用Excel2007 com封装
    /// </summary>
    public class ExcelHelper
    {
        /// <summary>
        /// excel路径
        /// </summary>
        public string FilePath { get; set; }

        private Excel.Application oXL;
        private Excel.Workbook oWB;
        private bool isNew = false;
        private bool isOpened = false;


        public ExcelHelper(string a_filepath = "")
        {
            FilePath = a_filepath;
        }


        /// <summary>
        /// 打开目标excel
        /// </summary>
        public void Open()
        {
            if (oXL != null)
            {
                throw new Exception("已打开一个Excel文件，请先关闭原文件后再打开新文件");
            }
            if (string.IsNullOrEmpty(FilePath))
            {
                throw new Exception("无法找到指定的文件");
            }

            oXL = new Excel.Application();
            oXL.Visible = false;
            oXL.DisplayAlerts = false;
            oWB = oXL.Workbooks.Open(Filename: FilePath);

            isNew = false;
            isOpened = true;
        }

        /// <summary>
        /// 创建一份excel
        /// </summary>
        public void Create()
        {
            if (oXL != null)
            {
                throw new Exception("已打开一个Excel文件，请先关闭原文件后再打开新文件");
            }

            oXL = new Excel.Application();
            oXL.Visible = false;
            oXL.DisplayAlerts = false;
            oWB = oXL.Workbooks.Add();
            isNew = true;
            isOpened = true;
        }

        /// <summary>
        /// 获取整个excel表数据
        /// </summary>
        /// <param name="sheetindex"></param>
        /// <param name="firstColsName"></param>
        /// <returns></returns>
        public DataTable getSheetAllDatas(int sheetindex = 1, bool firstColsName = true)
        {
            var oSheet = oWB.Worksheets.get_Item(sheetindex);

            int colslen = oSheet.UsedRange.Cells.Columns.Count;//获得已经用行数
            int rowslen = oSheet.UsedRange.Cells.Rows.Count;//获得已经用列数

            return getSheetDatas(sheetindex, 0, 0, colslen, rowslen, firstColsName);
        }

        /// <summary>
        /// 获取指定表数据
        /// </summary>
        /// <param name="sheetindex">工作薄从1开始</param>
        /// <param name="colAt">第几列开始，从0开始</param>
        /// <param name="rowAt">第几行开始，从0开始</param>
        /// <param name="colslen">行数</param>
        /// <param name="rowslen">列数</param>
        /// <param name="firstRowName">第一行当成数据列名</param>
        public DataTable getSheetDatas(int sheetindex = 1, int colAt = 0, int rowAt = 0, int colslen = 1, int rowslen = 1, bool firstColsName = true)
        {
            if (oWB == null)
            {
                throw new Exception("没有打开Excel文件");
            }
            Excel.Worksheet oSheet = oWB.Worksheets.get_Item(sheetindex);

            Excel.Range oRng = oSheet.Range[oSheet.Cells[1 + rowAt, 1 + colAt], oSheet.Cells[rowAt + rowslen, colAt + colslen]];

            object[,] celldatas = oRng.Value2;

            DataTable dt = new DataTable("ExcelCells");

            if (celldatas != null && celldatas.Length > 0)
            {
                int firstrow = 0;

                if (firstColsName)
                {
                    for (int i = 1; i <= celldatas.GetLength(1); i++)
                    {
                        var celldata = celldatas[1, i];
                        if (celldata != null)
                        {
                            dt.Columns.Add(celldatas[1, i].ToString());
                        }
                    }

                    firstrow = 2;
                }
                else
                {
                    for (int i = 1; i <= celldatas.GetLength(1); i++)
                    {
                        dt.Columns.Add("col" + i.ToString());
                    }

                    firstrow = 1;
                }

                if (celldatas.GetLength(0) >= firstrow)
                {
                    for (int j = firstrow; j <= celldatas.GetLength(0); j++)
                    {
                        var newrow = dt.NewRow();

                        dt.Rows.Add(newrow);

                        for (int i = 1; i <= dt.Columns.Count; i++)
                        {
                            newrow[i - 1] = celldatas[j, i];

                        }
                    }
                }

            }

            return dt;

        }

        /// <summary>
        /// 具体格写数据
        /// </summary>
        /// <param name="sheetindex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <param name="data"></param>
        public void setCell(int sheetindex = 1, int rowIndex = 1, int colIndex = 1, dynamic data = null)
        {
            Excel.Worksheet oSheet = oWB.Worksheets.get_Item(sheetindex);

            oSheet.Cells[rowIndex, colIndex].Value = data;

        }

        /// <summary>
        /// 具体格读数据
        /// </summary>
        /// <param name="sheetindex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="colIndex"></param>
        /// <returns></returns>
        public string getCell(int sheetindex = 1, int rowIndex = 1, int colIndex = 1)
        {

            Excel.Worksheet oSheet = oWB.Worksheets.get_Item(sheetindex);

            return oSheet.Cells[rowIndex, colIndex].Value;
        }

        /// <summary>
        /// 指定一个范围的数据导入
        /// </summary>
        /// <param name="sheetindex"></param>
        /// <param name="colAt"></param>
        /// <param name="rowAt"></param>
        /// <param name="dtdatas"></param>
        public void setSheetDatas(int sheetindex = 1, int colAt = 0, int rowAt = 0, DataTable dtdatas = null)
        {
            if (dtdatas != null)
            {
                Excel.Worksheet oSheet = oWB.Worksheets.get_Item(sheetindex);

                object[,] ddatas = new object[dtdatas.Rows.Count, dtdatas.Columns.Count];

                for (int i = 0; i < dtdatas.Rows.Count; i++)
                {
                    for (int j = 0; j < dtdatas.Columns.Count; j++)
                    {
                        ddatas[i, j] = dtdatas.Rows[i][j];
                    }
                }

                Excel.Range oRn = oSheet.Range[oSheet.Cells[rowAt + 1, colAt + 1], oSheet.Cells[rowAt + dtdatas.Rows.Count, colAt + dtdatas.Columns.Count]];

                oRn.Value2 = ddatas;
                //oRn.Columns.AutoFit();
            }
        }

        /// <summary>
        /// 设置格字符格式
        /// </summary>
        /// <param name="sheetindex"></param>
        /// <param name="rowbegin"></param>
        /// <param name="colbegin"></param>
        /// <param name="rowend"></param>
        /// <param name="colend"></param>
        /// <param name="NumberFormat">1."@"  数字->文本;2."￥#,###.00"　数字－两位小数;3.考虑excel单元格格式</param>
        public void setCellsDataFormat(int sheetindex = 1, int rowbegin = 1, int colbegin = 1, int rowend = 1, int colend = 1, string NumberFormat = null)
        {

            Excel.Worksheet oSheet = oWB.Worksheets.get_Item(sheetindex);

            oSheet.Range[oSheet.Cells[rowbegin, colbegin], oSheet.Cells[rowend, colend]].NumberFormat = NumberFormat;

        }

        /// <summary>
        /// 设置列格式
        /// </summary>
        /// <param name="sheetindex"></param>
        /// <param name="colIndex"></param>
        /// <param name="NumberFormat">1."@"  数字->文本;2."￥#,###.00"　数字－两位小数;3.考虑excel单元格格式</param>
        public void setColsDataFormat(int sheetindex = 1, int colIndex = 1, string NumberFormat = null)
        {
            Excel.Worksheet oSheet = oWB.Worksheets.get_Item(sheetindex);

            Excel.Range oRn = oSheet.Columns[colIndex];
            oRn.NumberFormat = NumberFormat;

        }

        /// <summary>
        /// 设置行格式
        /// </summary>
        /// <param name="sheetindex"></param>
        /// <param name="rowIndex"></param>
        /// <param name="NumberFormat">1."@"  数字->文本;2."￥#,###.00"　数字－两位小数;3.考虑excel单元格格式</param>
        public void setRowsDataFormat(int sheetindex = 1, int rowIndex = 1, string NumberFormat = null)
        {
            Excel.Worksheet oSheet = oWB.Worksheets.get_Item(sheetindex);

            Excel.Range oRn = oSheet.Rows[rowIndex];
            oRn.NumberFormat = NumberFormat;

        }


        /// <summary>
        /// 保存excel
        /// </summary>
        /// <param name="excelformat"></param>
        public void Save(ExcelFormat excelformat = ExcelFormat.Excel2000_03)
        {
            if (string.IsNullOrEmpty(FilePath) || (!isOpened))
            {
                return;
            }

            if (isNew)
            {
                oWB.SaveAs(Filename: FilePath, FileFormat: excelformat);
            }
            else
            {
                oWB.Save();
            }
        }

        /// <summary>
        /// 另存excel
        /// </summary>
        /// <param name="fullFileName">新路径</param>
        /// <param name="excelformat"></param>
        public void SaveAs(string fullFileName, ExcelFormat excelformat = ExcelFormat.Excel2000_03)
        {
            if (string.IsNullOrEmpty(fullFileName) || (!isOpened))
            {
                return;
            }

            oWB.SaveAs(Filename: fullFileName, FileFormat: excelformat);
        }

        /// <summary>
        /// 关闭excel.exe
        /// 所有对excel操作都应该调用释放内存
        /// </summary>
        /// <param name="hasSave"></param>
        public void Close(bool hasSave = true)
        {
            if (oWB != null)
            {
                oWB.Close(hasSave);
                CloseComObj(oWB);

            }
            if (oXL != null)
            {
                oXL.Workbooks.Close();
                oXL.Quit();

                CloseComObj(oXL);
            }
            GC.Collect();
            isOpened = false;
        }

        private string CloseComObj(object obj)
        {
            string result = "";
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);

            }
            catch (Exception ex)
            {
                result = ex.Message;
            }
            finally
            {
                obj = null;

            }

            return result;
        }

    }
}