ditie2.0_web/web/BaseCallCent.../ExcelReadWri...

using Microsoft.Win32;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;

namespace YTSoft.Common
{
    public class ExcelReadWrite
    {
        /// <summary>
        /// 将DataTable数据导入到excel中
        /// </summary>
        /// <param name="data">要导入的数据</param>
        /// <param name="isColumnWritten">DataTable的列名是否要导入</param>
        /// <param name="sheetName">要导入的excel的sheet的名称</param>
        /// <returns>导入数据行数(包含列名那一行)</returns>
        public IWorkbook DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
        {
            int i = 0;
            int j = 0;
            int count = 0;
            IWorkbook workbook = null;
            ISheet sheet = null;
            try
            {
                
                if (OfficeType() == 1)// 2003版本
                {
                    workbook = new HSSFWorkbook();
                }
                else  // 2007版本
                {
                    workbook = new XSSFWorkbook();
                }

                if (workbook != null)
                {
                    sheet = workbook.CreateSheet(sheetName);
                }
                else
                {
                    return null;
                }
                //表头字段样式
                ICellStyle styletitlehead = workbook.CreateCellStyle();
                styletitlehead.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                styletitlehead.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                IFont fonthead = workbook.CreateFont();
                fonthead.Color = 0;
                fonthead.IsBold = true;
                styletitlehead.SetFont(fonthead);

                //分录字段样式
                ICellStyle styletitledetail = workbook.CreateCellStyle();
                styletitledetail.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                styletitledetail.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                IFont fontdetail = workbook.CreateFont();
                fontdetail.Color = 3;
                styletitledetail.SetFont(fontdetail);

                if (isColumnWritten == true) //写入DataTable的列名
                {
                    IRow row = sheet.CreateRow(0);
                    for (j = 0; j < data.Columns.Count; j++)
                    {
                        if (data.ExtendedProperties.Contains(data.Columns[j].ColumnName))
                        {
                            if (data.ExtendedProperties[data.Columns[j].ColumnName].ToString() == "分录")
                            {
                                row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                                row.GetCell(j).CellStyle = styletitledetail;
                                row.GetCell(j).SetCellType(CellType.String);
                            }
                        }

                        else
                        {
                            row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
                            row.GetCell(j).CellStyle = styletitlehead;
                            row.GetCell(j).SetCellType(CellType.String);
                        }

                    }
                    count = 1;
                }
                else
                {
                    count = 0;
                }

                for (i = 0; i < data.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(count);
                    for (j = 0; j < data.Columns.Count; j++)
                    {
                        row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
                    }
                    count++;
                }

                sheet.CreateFreezePane(0, 1, 0, 1);
                ICellStyle styleconet = workbook.CreateCellStyle();
                styleconet.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
                styleconet.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
                IFont fontconet = workbook.CreateFont();
                fontconet.FontHeightInPoints = 14;
                styleconet.SetFont(fontconet);
                IDataFormat dataformat = workbook.CreateDataFormat();
                styleconet.DataFormat = dataformat.GetFormat("@");

                #region 获取当前列的宽度，然后对比本列的长度，取最大值
                //列宽自适应，只对英文和数字有效
                for (i = 0; i <= data.Columns.Count; i++)
                {
                    sheet.AutoSizeColumn(i);
                    sheet.SetDefaultColumnStyle(i, styleconet);
                }

                for (int columnNum = 0; columnNum < data.Columns.Count; columnNum++)
                {
                    int columnWidth = sheet.GetColumnWidth(columnNum) / 256;
                    for (int rowNum = 0; rowNum <= sheet.LastRowNum; rowNum++)
                    {
                        IRow currentRow;
                        //当前行未被使用过
                        if (sheet.GetRow(rowNum) == null)
                        {
                            currentRow = sheet.CreateRow(rowNum);
                        }
                        else
                        {
                            currentRow = sheet.GetRow(rowNum);
                        }
                        if (currentRow.GetCell(columnNum) != null)
                        {
                            ICell currentCell = currentRow.GetCell(columnNum);
                            if (currentCell != null)
                            {
                                int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
                                if (columnWidth < length)
                                {
                                    columnWidth = length;
                                }
                            }
                        }
                    }
                    if (columnWidth>100)
                    {
                        columnWidth = 100;
                    }

                    sheet.SetColumnWidth(columnNum, columnWidth * 256);
                }

                #endregion

                return workbook;




            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public byte[] GetExcelByte(DataTable data, string sheetName, bool isColumnWritten)
        {
            IWorkbook workbook = DataTableToExcel(data, sheetName, isColumnWritten);
            var ms = new NpoiMemoryStream();
            ms.AllowClose = false;
            workbook.Write(ms);
            ms.Flush();
            ms.Seek(0, SeekOrigin.Begin);

            byte[] bytes = new byte[ms.Length];
            ms.Read(bytes, 0, bytes.Length);
            // 设置当前流的位置为流的开始   
            ms.Seek(0, SeekOrigin.Begin);
            ms.AllowClose = true;

            return bytes;
        }

        public NpoiMemoryStream GetExcelMemoryStream(DataTable data, string sheetName, bool isColumnWritten)
        {
            IWorkbook workbook = DataTableToExcel(data, sheetName, isColumnWritten);
            var ms = new NpoiMemoryStream();
            ms.AllowClose = false;
            workbook.Write(ms);
            ms.Flush();
            ms.Seek(0, SeekOrigin.Begin);
            ms.AllowClose = true;

            return ms;
        }
        /// <summary>
        /// 重写Npoi流方法
        /// </summary>
        public class NpoiMemoryStream : MemoryStream
        {
            public NpoiMemoryStream()
            {
                AllowClose = true;
            }

            public bool AllowClose { get; set; }

            public override void Close()
            {
                if (AllowClose)
                    base.Close();
            }
        }
        /// <summary>
        /// 将excel中的数据导入到DataTable中
        /// </summary>
        /// <param name ="path">excel文件路径含excel文件名称及后缀</param>
        /// <param name="sheetname">excel工作薄sheet的名称</param>
        /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
        /// <returns>返回的DataTable</returns>
        public DataTable LoadExcel(Stream streamfile, bool isFirstRowColumn)
        {
            ISheet sheet = null;
            IWorkbook workbook = null;
            DataTable data = new DataTable();
            try
            {
                //if (fileName.IndexOf(".xlsx") > 0) // 2007版本
                //{
                //    workbook = new XSSFWorkbook(streamfile);
                //}
                //else if (fileName.IndexOf(".xls") > 0)// 2003版本
                //{
                //    workbook = new HSSFWorkbook(streamfile);
                //}
                //else
                //{
                //    return null;
                //} 
                try
                {
                    workbook = new XSSFWorkbook(streamfile);
                }
                catch (Exception ex)
                {
                    workbook = new HSSFWorkbook(streamfile);
                }

                sheet = workbook.GetSheetAt(0);

                IRow firstRow = sheet.GetRow(0);
                int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
                int rowCount = sheet.LastRowNum;  //一列最后一个cell的编号 即总的行数
                int startRow = 0;

                if (isFirstRowColumn)
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
                    {
                        ICell cell = firstRow.GetCell(i);
                        if (cell != null)
                        {
                            string cellValue = cell.StringCellValue;
                            if (cellValue != null)
                            {
                                DataColumn column = new DataColumn(cellValue);
                                data.Columns.Add(column);
                            }
                        }
                    }
                    startRow = sheet.FirstRowNum + 1;
                }
                else
                {
                    startRow = sheet.FirstRowNum;
                }
                //Execel第一行是标题，不是要导入数据库的数据
                for (int i = startRow; i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    if (row == null) continue; //没有数据的行默认是null　

                    DataRow dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j <= cellCount; j++)
                    {
                        if (i == startRow && data.Columns.Count < cellCount)
                        {
                            DataColumn column = new DataColumn(row.GetCell(j).ToString());
                            data.Columns.Add(column);
                        }

                        if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                            dataRow[j] = row.GetCell(j).ToString();
                    }
                    data.Rows.Add(dataRow);
                }
                return data;


            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 判断office版本
        /// 返回0为07及以上版本，返回1为03及以下版本
        /// </summary>
        /// <returns></returns>
        public int OfficeType()
        {
            int type = 1;
            RegistryKey rk = Registry.LocalMachine;

            //office 2003
            RegistryKey office2003 = rk.OpenSubKey(@"SOFTWARE\\Microsoft\\Office\\11.0\\Word\\InstallRoot\\");
            //office 97
            RegistryKey office97 = rk.OpenSubKey(@"SOFTWARE\\Microsoft\\Office\\8.0\\Word\\InstallRoot\\");
            //office 2000
            RegistryKey office2000 = rk.OpenSubKey(@"SOFTWARE\\Microsoft\\Office\\9.0\\Word\\InstallRoot\\");
            //office xp
            RegistryKey officexp = rk.OpenSubKey(@"SOFTWARE\\Microsoft\\Office\\10.0\\Word\\InstallRoot\\");
            //07
            RegistryKey f07 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\12.0\Excel\InstallRoot\");
            //10
            RegistryKey office2010 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\14.0\Common\InstallRoot");
            //13
            RegistryKey office2013 = rk.OpenSubKey(@"SOFTWARE\Microsoft\Office\15.0\Common\InstallRoot");
         
            ////查询wps
            RegistryKey wps = rk.OpenSubKey(@"SOFTWARE\Kingsoft\Office\6.0\common\");

            if (officexp != null)
            {
                string filexp = officexp.GetValue("Path").ToString();
                if (File.Exists(filexp + "Excel.exe"))
                {
                    type = 1;
                }
            }
            if (office2000 != null)
            {
                string file2000 = officexp.GetValue("Path").ToString();
                if (File.Exists(file2000 + "Excel.exe"))
                {
                    type = 1;
                }
            }
            if (office97 != null)
            {
                string file97 = officexp.GetValue("Path").ToString();
                if (File.Exists(file97 + "Excel.exe"))
                {
                    type = 1;
                }
            }
            if (office2003 != null)
            {
                string file2003 = officexp.GetValue("Path").ToString();
                if (File.Exists(file2003 + "Excel.exe"))
                {
                    type = 1;
                }
            }

            if (f07 != null)
            {
                string file2007 = f07.GetValue("Path").ToString();
                if (File.Exists(file2007 + "Excel.exe"))
                {
                    type = 2;
                }
            }
            if (office2010 != null)
            {
                string file2010 = office2010.GetValue("Path").ToString();
                if (File.Exists(file2010 + "Excel.exe"))
                {
                    type = 3;
                }
            }
            if (office2013 != null)
            {
                string file2013 = office2013.GetValue("Path").ToString();
                if (File.Exists(file2013 + "Excel.exe"))
                {
                    type = 3;
                }
            }
            //if (wps != null)
            //{
            //    string filewps = wps.GetValue("Path").ToString();
            //    if (File.Exists(filewps + "Excel.exe"))
            //    {
            //        type = 1;
            //    }
            //}
            return type;
        }
    }
}