NPOI操作excel

我不是女神ヾ 2023-06-05 11:16 25阅读 0赞

[本事例只演示导入][Link 1]

调用

using System;
    using System.Collections.Generic;
    using System.Data;
    using System.Diagnostics;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace NpoiTest
    {
        class Program
        {
            static void Main(string[] args)
            {
                string path = "D:\\";
                string xlsPath = Path.Combine(path, DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");;//excel的保存路径
                NpoiExcelUtility utility = new NpoiExcelUtility(xlsPath);
    
                #region MyRegion
                 DataTable dt = new DataTable("NewDt");
    
                //创建自增长的ID列
                DataColumn dc = dt.Columns.Add("ID", Type.GetType("System.Int32"));
                dc.AutoIncrement = true;   //自动增加
                dc.AutoIncrementSeed = 1;  //起始为1
                dc.AutoIncrementStep = 1;  //步长为1
                dc.AllowDBNull = false;    //非空
    
                //创建其它列表
                dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
                dt.Columns.Add(new DataColumn("Age", Type.GetType("System.Int32")));
                dt.Columns.Add(new DataColumn("Score", Type.GetType("System.Decimal")));
                dt.Columns.Add(new DataColumn("CreateTime", Type.GetType("System.DateTime")));
    
                //创建数据
                DataRow dr = dt.NewRow();
                dr["Name"] = "张三";
                dr["Age"] = 28;
                dr["Score"] = 85.5;
                dr["CreateTime"] = DateTime.Now;
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr["Name"] = "李四";
                dr["Age"] = 24;
                dr["Score"] = 72;
                dr["CreateTime"] = DateTime.Now;
                dt.Rows.Add(dr);
    
                dr = dt.NewRow();
                dr["Name"] = "王五";
                dr["Age"] = 36;
                dr["Score"] = 63.5;
                dr["CreateTime"] = DateTime.Now;
                dt.Rows.Add(dr);
                #endregion
    
                if (dt != null)
                {
                    utility.CreatExcelSheet("基本信息表", dt);
                    utility.SaveExcel();
                    Process.Start(xlsPath);//导入完毕后直接自动启动exce打开生成的excel文件
                }
            }
        }
    }

excel帮助类

using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace NpoiTest
    {
    
        /// <summary>
        /// 写Excel的实用类，NPOI 是POI项目的.NET 版本，是一个开源的用来读写Excel、WORD等微软OLE2组件文档的项目，不依赖于office，如果用Microsoft.Office.Interop.Excel，容易出错，尤其是安装了不同版本的office软件
        /// </summary>
        public class NpoiExcelUtility
        {
            private string _xlsPath = string.Empty;
            private HSSFWorkbook _workBook = null;
    
            /// <summary>
            /// 构造函数
            /// </summary>
            /// <param name="xlsPath">xls保存路径</param>
            public NpoiExcelUtility(string xlsPath)
            {
                _xlsPath = this.CheckFilePath(xlsPath);
    
                _workBook = new HSSFWorkbook();
            }
    
            /// <summary>
            /// 将DataTable保存到sheet里
            /// </summary>
            /// <param name="dt"></param>
            /// <param name="sheet"></param>
            private void DataTableToExcel(DataTable dt, ISheet sheet)
            {
                ICellStyle style = _workBook.CreateCellStyle();
                style.Alignment = HorizontalAlignment.Left;
                style.VerticalAlignment = VerticalAlignment.Center;
    
                ICellStyle colStyle = _workBook.CreateCellStyle();
                colStyle.Alignment = HorizontalAlignment.Left;
                colStyle.VerticalAlignment = VerticalAlignment.Center;
                IFont font = _workBook.CreateFont();
                font.Color = NPOI.HSSF.Util.HSSFColor.LightBlue.COLOR_NORMAL;
                colStyle.SetFont(font);
    
                //列名
                IRow row = sheet.CreateRow(0);
                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    sheet.SetDefaultColumnStyle(i, style);
    
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(dt.Columns[i].ToString());
    
                    cell.CellStyle = colStyle;
                }
                //内容
                for (int i = 1; i <= dt.Rows.Count; i++)
                {
                    row = sheet.CreateRow(i);
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        object obj = dt.Rows[i - 1][j];
                        if (obj != null)
                        {
                            ICell cell = row.CreateCell(j);
                            if (obj is double || obj is float || obj is int || obj is long || obj is decimal)
                            {
                                cell.SetCellValue(Convert.ToDouble(obj));
                            }
                            else if (obj is bool)
                            {
                                cell.SetCellValue((bool)obj);
                            }
                            else
                            {
                                cell.SetCellValue(obj.ToString());
                            }
                        }
                    }
                }
                //一下方法会报异常，可能是改NPOI版本的问题，之前老的版本不会报错，这里暂时注释掉
                //for (int i = 0; i < dt.Columns.Count; i++)
                //{
                //    sheet.AutoSizeColumn(i);
                //}
            }
    
            /// <summary>
            /// 保存Excel
            /// </summary>
            public void SaveExcel()
            {
                FileStream file = new FileStream(_xlsPath, FileMode.Create);
                _workBook.Write(file);
                file.Close();
            }
    
            /// <summary>
            /// 创建Sheet
            /// </summary>
            /// <param name="sheetName">sheet名称</param>
            /// <param name="tbl">DataTable数据表，当行数大于65536时，自动分割成几个sheet，sheet名称为sheetName_i</param>
            public void CreatExcelSheet(string sheetName, DataTable tbl)
            {
                string sName = this.CheckSheetName(sheetName);
    
                int rowMax = 65535;
                int intNum = tbl.Rows.Count / rowMax;
                int remainder = tbl.Rows.Count % rowMax;
    
                for (int i = 0; i < intNum; i++)
                {
                    DataTable subTbl = tbl.Clone();
                    for (int j = 0; j < 65535; j++)
                    {
                        int rowIndex = i * rowMax + j;
                        subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
                    }
                    string subSheet = sName + "_" + (i + 1);
                    ISheet sheet = _workBook.CreateSheet(subSheet);
                    this.DataTableToExcel(subTbl, sheet);
                }
                if (remainder > 0)
                {
                    DataTable subTbl = tbl.Clone();
                    for (int j = 0; j < remainder; j++)
                    {
                        int rowIndex = intNum * rowMax + j;
                        subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
                    }
                    string subSheet = sName + "_" + (intNum + 1);
                    if (intNum < 1)
                    {
                        subSheet = sName;
                    }
                    ISheet sheet = _workBook.CreateSheet(subSheet);
                    this.DataTableToExcel(subTbl, sheet);
                }
            }
    
            /// <summary>
            /// 检查sheet名称是否合法，并去掉不合法字符
            /// </summary>
            /// <param name="sheetName"></param>
            private string CheckSheetName(string sheetName)
            {
                string rlt = sheetName;
                string[] illegalChars = { "*", "?", "\"", @"\", "/" };
                for (int i = 0; i < illegalChars.Length; i++)
                {
                    rlt = rlt.Replace(illegalChars[i], "");
                }
                return rlt;
            }
    
            /// <summary>
            ///  检查xls路径是否合法，并去掉不合法字符
            /// </summary>
            /// <param name="filePath"></param>
            private string CheckFilePath(string filePath)
            {
                string dir = Path.GetDirectoryName(filePath);
                string fileName = Path.GetFileNameWithoutExtension(filePath);
                string ext = Path.GetExtension(filePath);
    
                string[] illegalChars = { ":", "*", "?", "\"", "<", ">", "|", @"\", "/" };
                for (int i = 0; i < illegalChars.Length; i++)
                {
                    fileName = fileName.Replace(illegalChars[i], "");
                }
                string rlt = Path.Combine(dir, fileName + ext);
                return rlt;
            }
        }
    }

转载于:https://www.cnblogs.com/macT/p/11606690.html

[Link 1]: https://pan.baidu.com/s/1zUpvZyOsn0tIVQqZabxuvA