C# NPOIUtility

丷寒枫丷

已于 2022-06-09 09:16:19 修改

阅读量173

点赞数

分类专栏： C# 文章标签： c# java linq

于 2020-04-21 18:18:33 首次发布

本文链接：https://blog.csdn.net/qq_24432127/article/details/105664507

版权

C# 专栏收录该内容

34 篇文章 0 订阅

订阅专栏

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.Text.RegularExpressions;
using System.Web.Mvc;


namespace UtilityFactory
{

    public class NPOIUtility
    {

        public static int DefaultColumnWidth = 30 * 256;

        public static int PageSize = 65536;

        public static MemoryStream TableToExcel(DataTable dt)
        {
            return TableToExcel(dt, false, null);
        }

        public static MemoryStream TableToExcel(DataTable dt, bool IsAutoSizeColumn)
        {
            return TableToExcel(dt, IsAutoSizeColumn, null);
        }

        public static MemoryStream TableToExcel(DataTable dt, Dictionary<string, int> ColumnsWidth)
        {
            return TableToExcel(dt, false, ColumnsWidth);
        }

        public static MemoryStream TableToExcel(DataTable dt, bool IsAutoSizeColumn, Dictionary<string, int> ColumnsWidth)
        {
            XSSFWorkbook workBook = new XSSFWorkbook();// 2007版本, 目前默认版本;
            ICellStyle BodyStyle = CreateBodyStyle(workBook);
            int DataCount = dt.Rows.Count, PageIndex = 0;
            DataPaging(workBook, dt, IsAutoSizeColumn, ColumnsWidth);
            ISheet sheet = null;
            for (int i = 0; i < DataCount; i++)
            {
                if (i == 0 || i % PageSize == 0)
                {
                    sheet = workBook.GetSheetAt(PageIndex);
                    PageIndex++;
                }
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    NPOIUtility.SetCellValue(row.CreateCell(j), dt.Rows[i][j], BodyStyle);
                }
            }
            MemoryStream MStream = new NPOIMemoryStream();
            workBook.Write(MStream);
            MStream.Seek(0, SeekOrigin.Begin);
            return MStream;
        }

        public static void TableToSheet(XSSFWorkbook workBook, ISheet sheet, DataTable dt)
        {
            ICellStyle HeaderStyle = CreateHeaderStyle(workBook);
            IRow rowtitle = sheet.CreateRow(0);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                string ColumnName = dt.Columns[j].ColumnName;
                ICell cell = rowtitle.CreateCell(j);
                cell.SetCellValue(ColumnName);
                cell.CellStyle = HeaderStyle;
                sheet.SetColumnWidth(j, DefaultColumnWidth);
            }
            sheet.CreateFreezePane(0, 1); //首行冻结
            sheet.DefaultRowHeight = 320; // 设置行高

            ICellStyle BodyStyle = CreateBodyStyle(workBook);
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                IRow row = sheet.CreateRow(i + 1);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    NPOIUtility.SetCellValue(row.CreateCell(j), dt.Rows[i][j], BodyStyle);
                }
            }
        }

        public static void DataPaging(XSSFWorkbook workBook, DataTable dt, bool IsAutoSizeColumn, Dictionary<string, int> ColumnsWidth)
        {
            string[] Columns = new string[dt.Columns.Count];
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                Columns[i] = dt.Columns[i].ColumnName;
            }
            DataPaging(workBook, Columns, dt.Rows.Count, IsAutoSizeColumn, ColumnsWidth, DefaultColumnWidth);
        }

        public static void DataPaging(XSSFWorkbook workBook, string[] Columns, int DataCount)
        {
            DataPaging(workBook, Columns, DataCount, false, null, DefaultColumnWidth);
        }

        public static void DataPaging(XSSFWorkbook workBook, string[] Columns, int DataCount, int ColumnWidth)
        {
            DataPaging(workBook, Columns, DataCount, false, null, ColumnWidth);
        }

        public static void DataPaging(XSSFWorkbook workBook, string[] Columns, int DataCount, bool IsAutoSizeColumn)
        {
            DataPaging(workBook, Columns, DataCount, IsAutoSizeColumn, null, DefaultColumnWidth);
        }

        public static void DataPaging(XSSFWorkbook workBook, string[] Columns, int DataCount, Dictionary<string, int> ColumnsWidth)
        {
            DataPaging(workBook, Columns, DataCount, false, ColumnsWidth, DefaultColumnWidth);
        }

        public static void DataPaging(XSSFWorkbook workBook, string[] Columns, int DataCount, bool IsAutoSizeColumn, Dictionary<string, int> ColumnsWidth)
        {
            DataPaging(workBook, Columns, DataCount, IsAutoSizeColumn, ColumnsWidth, DefaultColumnWidth);
        }

        public static void DataPaging(XSSFWorkbook workBook, string[] Columns, int DataCount, bool IsAutoSizeColumn, Dictionary<string, int> ColumnsWidth, int ColumnWidth)
        {
            int PageCount = DataCount % PageSize == 0 ? DataCount / PageSize : DataCount / PageSize + 1;
            PageCount = PageCount == 0 ? 1 : PageCount;

            ICellStyle HeaderStyle = CreateHeaderStyle(workBook);

            for (int i = 1; i <= PageCount; i++)
            {
                ISheet sheet = workBook.CreateSheet("sheet" + i);

                IRow rowtitle = sheet.CreateRow(0);

                for (int j = 0; j < Columns.Length; j++)
                {
                    string ColumnName = Columns[j];
                    ICell cell = rowtitle.CreateCell(j);
                    cell.SetCellValue(ColumnName);
                    cell.CellStyle = HeaderStyle;

                    if (ColumnsWidth != null)
                    {
                        bool IsExist = false;
                        foreach (var item in ColumnsWidth)
                        {
                            if (item.Key == ColumnName)
                            {
                                if (item.Value == -1)
                                {
                                    sheet.AutoSizeColumn(j);
                                }
                                else
                                {
                                    sheet.SetColumnWidth(j, item.Value);
                                }
                                IsExist = true;
                                break;
                            }
                        }

                        if (!IsExist)
                        {
                            if (IsAutoSizeColumn)
                            {
                                sheet.AutoSizeColumn(j);
                            }
                            else
                            {
                                sheet.SetColumnWidth(j, ColumnWidth);
                            }
                        }
                    }
                    else
                    {
                        if (IsAutoSizeColumn)
                        {
                            sheet.AutoSizeColumn(j);
                        }
                        else
                        {
                            sheet.SetColumnWidth(j, ColumnWidth);
                        }
                    }
                }

                //sheet.SetAutoFilter(new CellRangeAddress(0, 0, 0, dataTable.Columns.Count - 1)); //首行筛选
                sheet.CreateFreezePane(0, 1); //首行冻结
                sheet.DefaultRowHeight = 320; // 设置行高
            }
        }

        public static ICellStyle CreateHeaderStyle(XSSFWorkbook workBook)
        {
            //创建首行样式
            ICellStyle HeaderStyle = workBook.CreateCellStyle();
            //设置单元格的样式：水平对齐居中
            HeaderStyle.Alignment = HorizontalAlignment.Center;
            //新建一个字体样式对象
            IFont HeaderFont = workBook.CreateFont();
            //设置字体加粗样式
            HeaderFont.IsBold = true;
            //设置字体大小
            HeaderFont.FontHeightInPoints = 10;
            //使用SetFont方法将字体样式添加到单元格样式中 
            HeaderStyle.SetFont(HeaderFont);
            //设置边框
            HeaderStyle.BorderBottom = BorderStyle.Medium;
            HeaderStyle.BorderLeft = BorderStyle.Thin;
            HeaderStyle.BorderRight = BorderStyle.Thin;
            HeaderStyle.BorderTop = BorderStyle.Thin;
            //设置背景颜色
            HeaderStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
            HeaderStyle.FillPattern = FillPattern.SolidForeground;
            HeaderStyle.VerticalAlignment = VerticalAlignment.Center;
            return HeaderStyle;
        }

        public static ICellStyle CreateBodyStyle(XSSFWorkbook workBook)
        {
            //创建内容的默认样式
            ICellStyle BodyStyle = workBook.CreateCellStyle();
            //新建一个字体样式对象
            IFont BodyFont = workBook.CreateFont();
            //设置字体大小
            BodyFont.FontHeightInPoints = 10;
            //使用SetFont方法将字体样式添加到单元格样式中 
            BodyStyle.SetFont(BodyFont);
            //设置边框
            BodyStyle.BorderBottom = BorderStyle.Thin;
            BodyStyle.BorderLeft = BorderStyle.Thin;
            BodyStyle.BorderRight = BorderStyle.Thin;
            BodyStyle.BorderTop = BorderStyle.Thin;
            BodyStyle.VerticalAlignment = VerticalAlignment.Center;
            return BodyStyle;
        }

        public static string GetCellValue(ICell cell)
        {
            if (cell == null)
            {
                return string.Empty;
            }

            switch (cell.CellType)
            {
                case CellType.Blank: //空数据类型 这里类型注意一下，不同版本NPOI大小写可能不一样,有的版本是Blank（首字母大写)
                    return string.Empty;
                case CellType.Boolean: //bool类型
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric: //数字类型
                    if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
                    {
                        return cell.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss.fff");
                    }
                    else //其它数字
                    {
                        return cell.NumericCellValue.ToString();
                    }
                case CellType.String: //string 类型
                    return cell.StringCellValue;
                case CellType.Formula: //带公式类型
                    try
                    {
                        return cell.StringCellValue;
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
                case CellType.Unknown: //无法识别类型
                default: //默认类型
                    return cell.ToString();
            }
        }

        public static string GetCellValue(IRow row, int index)
        {
            if (row == null)
            {
                return null;
            }

            if (index != -1)
            {
                string value = GetCellValue(row.GetCell(index));
                return string.IsNullOrEmpty(value) ? null : value;
            }
            return null;
        }

        public static void SetCellValue(ICell cell, object value)
        {
            SetCellValue(cell, value, null);
        }

        public static void SetCellValue(ICell cell, object value, ICellStyle cellStyle)
        {
            if (value == null)
            {
                return;
            }

            Type type = value.GetType();

            if (type.Name == "DBNull")
            {

            }
            else if ("Int64, Int32, Int16".IndexOf(type.Name) > -1)
            {
                cell.SetCellValue((int)value);
            }
            else if ("Double, Decimal, Single".IndexOf(type.Name) > -1)
            {
                cell.SetCellValue(Convert.ToDouble(value));
            }
            else if ("DateTime, DateTimeOffset".IndexOf(type.Name) > -1)
            {
                DateTime datetime = (DateTime)value;

                if (datetime.Hour == 0 && datetime.Minute == 0 && datetime.Second == 0)
                {
                    cell.SetCellValue(datetime.ToString("yyyy-MM-dd"));
                }
                else
                {
                    cell.SetCellValue(datetime.ToString("yyyy-MM-dd HH:mm:ss"));
                }
            }
            else if ("Boolean".IndexOf(type.Name) > -1)
            {
                cell.SetCellValue((bool)value);
            }
            else
            {
                cell.SetCellValue(value.ToString());
            }

            cell.CellStyle = cellStyle;
        }

        public static void SetColumnHidden(ISheet sheet, params int[] ColumnValue)
        {
            for (int i = 0; i < ColumnValue.Length; i++)
            {
                sheet.SetColumnHidden((short)ColumnValue[i], true);
            }
        }

        public static Dictionary<string, int> GetColumnIndexs(string[] Keys, IRow HeadRow)
        {

            if (HeadRow == null)
            {
                return null;
            }

            Dictionary<string, int> ColumnIndexs = new Dictionary<string, int>();

            int CellCount = HeadRow.LastCellNum;

            for (int i = 0; i < CellCount; i++)
            {
                string ColumnName = GetCellValue(HeadRow.GetCell(i));

                for (int j = 0; j < Keys.Length; j++)
                {
                    if (Keys[j] == ColumnName && !ColumnIndexs.ContainsKey(Keys[j]))
                    {
                        ColumnIndexs.Add(Keys[j], i);
                        break;
                    }
                }
            }

            for (int i = 0; i < Keys.Length; i++)
            {
                if (!ColumnIndexs.ContainsKey(Keys[i]))
                {
                    ColumnIndexs.Add(Keys[i], -1);
                }
            }

            return ColumnIndexs;
        }

        public static IWorkbook GetIWorkbook(Stream stream, string fileFormat)
        {
            IWorkbook book = null;

            if (fileFormat == ".xls")
            {
                book = new HSSFWorkbook(stream);
            }
            else if (fileFormat == ".xlsx")
            {
                book = new XSSFWorkbook(stream);
            }

            return book;
        }

    }

    /// <summary>
    /// 导出Excel2007 .xlsx必须使用此类
    /// </summary>
    public class NPOIMemoryStream : MemoryStream
    {

        /// <summary>
        /// 获取流是否关闭
        /// </summary>
        public bool IsColse
        {
            get;
            private set;
        }

        public NPOIMemoryStream(bool colse = false)
        {
            IsColse = colse;
        }

        public override void Close()
        {
            if (IsColse)
            {
                base.Close();
            }

        }

    }

}

using DataFactory;
using EntityFactory;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using UtilityFactory;

namespace BusinessFactory
{
    public class AuditExcel
    {
        public static void ImportAudit(Stream stream, string fileFormat)
        {
            AuditContext auditContext = new AuditContext();

            IWorkbook book = NPOIUtility.GetIWorkbook(stream, fileFormat);
            {
                #region 审计单
                //初始化文档

                for (int i = 0; i < book.NumberOfSheets; i++)
                {
                    //获取文档中第一个sheet表
                    ISheet sheet = book.GetSheetAt(i);
                    if (i != 0)
                    {
                        continue;
                    }
                    //获取有多少行
                    int RowCount = sheet.LastRowNum;

                    //确认列的位置
                    string[] Keys = new string[] {
                    "Company Name" ,  "Vsoap Co No.", "Incorporation date","Last Audit YE date", "IRD 檔案No." , "Remarks"
                    };

                    Dictionary<string, int> ColumnIndexs = NPOIUtility.GetColumnIndexs(Keys, sheet.GetRow(1));

                    if (ColumnIndexs == null)
                    {
                        continue;
                    }

                    string AuditIds = "";
                    string AuditHistoryIds = "";

                    try
                    {
                        //读取内容
                        for (int j = 2; j <= RowCount; j++)
                        {
                            IRow row = sheet.GetRow(j);
                            if (row == null || row.Cells.Count == 0)
                            {
                                break;
                            }

                            string CompanyName = NPOIUtility.GetCellValue(row, ColumnIndexs[Keys[0]]);
                            string CompanyNumber = NPOIUtility.GetCellValue(row, ColumnIndexs[Keys[1]]);
                            string IncorporationDate = NPOIUtility.GetCellValue(row, ColumnIndexs[Keys[2]]);
                            string LastAuditYeDate = NPOIUtility.GetCellValue(row, ColumnIndexs[Keys[3]]);
                            string IRDNo = NPOIUtility.GetCellValue(row, ColumnIndexs[Keys[4]]);
                            string Remarks = NPOIUtility.GetCellValue(row, ColumnIndexs[Keys[5]]);


                            string CompanyNameEN = GetEnglishString(CompanyName);
                            string CompanyNameCN = GetChineseString(CompanyName);

                            DateTime dt_IncorporationDate;
                            string str_IncorporationDate = IncorporationDate;
                            bool IsIncorporationDate = false;
                            if (DateTime.TryParse(IncorporationDate, out dt_IncorporationDate))
                            {
                                IsIncorporationDate = true;
                                str_IncorporationDate = dt_IncorporationDate.ToString("yyyy-MM-dd");
                            }

                            //查询是否存在香港审计公司
                            CompanyHKAudit companyHKAudit = auditContext.IsExist(CompanyName);

                            Guid CompanyHKAuditId = Guid.Empty;

                            if (companyHKAudit != null)
                            {
                                //HK审计公司存在
                                CompanyHKAuditId = companyHKAudit.Id;
                            }
                            else
                            {
                                CompanyHKAudit addmodel = new CompanyHKAudit();
                                addmodel.Id = Guid.NewGuid();
                                addmodel.BusinessAreaId = new Guid("8f9af450-3d45-4a56-890c-425d84403ff6");
                                addmodel.CompanyName = CompanyName;
                                addmodel.CompanyNameEN = CompanyNameEN;
                                addmodel.CompanyNameCN = CompanyNameCN;
                                addmodel.CompanyNumber = CompanyNumber;

                                if (IsIncorporationDate)
                                {
                                    addmodel.FoundDate = str_IncorporationDate;
                                }

                                auditContext.Insert(addmodel);
                                CompanyHKAuditId = addmodel.Id;
                                AuditIds += addmodel.Id.ToString() + ",";
                            }

                            DateTime date_LastAuditYeDate;
                            bool IsDateParse = DateTime.TryParse(LastAuditYeDate, out date_LastAuditYeDate) ? true : false;

                            if (!IsDateParse)
                            {
                                //判断是否 字符串日期格式为dd-MM-yyyy
                                string[] arrformat = { "dd-MM-yyyy", "dd/MM/yyyy", "dd.MM.yyyy" };
                                for (int t = 0; t < arrformat.Length; t++)
                                {
                                    string format = arrformat[t];
                                    IsDateParse = DateTime.TryParseExact(LastAuditYeDate, format, CultureInfo.InvariantCulture, DateTimeStyles.None, out date_LastAuditYeDate) ? true : false;
                                    if (IsDateParse)
                                    {
                                        break;
                                    }
                                }
                            }

                            if (IsDateParse)
                            {
                                //查询香港审计公司的审计历史表是否存在该次审计信息
                                CompanyHKAuditHistory companyHKAuditHistory = auditContext.IsExistCompanyHKAuditHistory(CompanyHKAuditId, date_LastAuditYeDate.ToString());

                                if (companyHKAuditHistory != null)
                                {
                                    //当前公司在历史表中存在系统审计年度的审计记录 ，但是IRDNo不一样，修改最新的审计历史表信息
                                    if (companyHKAuditHistory.IRDNo != IRDNo)
                                    {
                                        companyHKAuditHistory.AuditDate = date_LastAuditYeDate.ToString();
                                        companyHKAuditHistory.IRDNo = IRDNo;
                                        auditContext.Modify(companyHKAuditHistory);
                                    }
                                }
                                else
                                {
                                    CompanyHKAuditHistory addmodelhistory = new CompanyHKAuditHistory();
                                    addmodelhistory.Id = Guid.NewGuid();
                                    addmodelhistory.CompanyHKAuditId = CompanyHKAuditId;
                                    addmodelhistory.IRDNo = IRDNo;
                                    addmodelhistory.AuditDate = date_LastAuditYeDate.ToString();

                                    auditContext.Insert(addmodelhistory);
                                    AuditHistoryIds += addmodelhistory.Id.ToString() + ",";
                                }
                            }

                            //Remark字段直接保存审计历史表相关信息

                            CompanyHKAudit modifymodel = auditContext.GetCompanyHKAudit(CompanyHKAuditId);
                            modifymodel.Remark = string.Format("{0} | {1} | {2}", Remarks, LastAuditYeDate, IRDNo);
                            auditContext.Modify(modifymodel);

                        }
                    }
                    catch (Exception ex)
                    {
                        //出错 全部删除
                        auditContext.ErrorRollback(AuditIds, AuditHistoryIds);
                        throw;
                    }
                }
                #endregion
            }
        }

        public static string GetAuditYear(string StrAuditDate)
        {
            if (string.IsNullOrEmpty(StrAuditDate))
            {
                return StrAuditDate;
            }

            string ResultAuditYear = string.Empty;

            //1.替换审计日期中的所有字母  替换所有空格
            StrAuditDate = Regex.Replace(StrAuditDate, "[a-zA-Z]", "");
            StrAuditDate = StrAuditDate.Replace(" ", "");

            //2.
            char[] arrSign = { '/', '.', '-' };

            for (int i = 0; i < arrSign.Length; i++)
            {
                char Split = arrSign[i];
                string[] arrAuditDate = StrAuditDate.Split(Split);

                for (int j = 0; j < arrAuditDate.Length; j++)
                {
                    var check_item = arrAuditDate[j].ToString();

                    if (check_item.Length == DateTime.Now.Year.ToString().Length)
                    {
                        ResultAuditYear = check_item;
                        break;
                    }
                }
            }

            return ResultAuditYear;
        }

        public static string GetChineseString(string SourceStr)
        {
            if (string.IsNullOrEmpty(SourceStr))
            {
                return SourceStr;
            }

            bool IsChinese = StringUtility.HasChinese(SourceStr);

            string TargetStr = "";

            int FirstIndex = 0;

            if (IsChinese)
            {
                char[] arrs = SourceStr.ToArray();
                for (int i = 0; i < arrs.Length; i++)
                {
                    if (StringUtility.HasChinese(arrs[i].ToString()))
                    {
                        FirstIndex = i;
                        break;
                    }
                }
                TargetStr = SourceStr.Substring(FirstIndex);
            }
            return TargetStr;
        }

        public static string GetEnglishString(string SourceStr)
        {
            if (string.IsNullOrEmpty(SourceStr))
            {
                return SourceStr;
            }

            bool IsChinese = StringUtility.HasChinese(SourceStr);

            string TargetStr = "";

            int FirstIndex = 0;
            if (IsChinese)
            {
                char[] arrs = SourceStr.ToArray();
                for (int i = 0; i < arrs.Length; i++)
                {
                    if (StringUtility.HasChinese(arrs[i].ToString()))
                    {
                        FirstIndex = i;
                        break;
                    }
                }
                TargetStr = SourceStr.Substring(0, FirstIndex);
            }
            else
            {
                TargetStr = SourceStr;
            }

            return TargetStr;
        }
    }
}

丷寒枫丷

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
C# NPOIUtility

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.Text.RegularExpressions...
复制链接

扫一扫