NPOI导出EXCEL文件

【实例简介】

NPOI导出EXCEL文件

【实例截图】

【核心代码】

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NPOI导出
{
    class Program
    {
        static void Main(string[] args)
        {
            ExcelToFile.exportExcel();
        }

        public class ExcelToFile
        {

            /// <summary>
            /// 获取单元格类型
            /// </summary>
            /// <param name="cell"></param>
            /// <returns></returns>
            private static object GetValueType(ICell cell)
            {
                if (cell == null)
                    return null;
                switch (cell.CellType)
                {
                    case CellType.Blank: //BLANK:  
                        return null;
                    case CellType.Boolean: //BOOLEAN:  
                        return cell.BooleanCellValue;
                    case CellType.Numeric: //NUMERIC:  
                        return cell.NumericCellValue;
                    case CellType.String: //STRING:  
                        return cell.StringCellValue;
                    case CellType.Error: //ERROR:  
                        return cell.ErrorCellValue;
                    case CellType.Formula: //FORMULA:  
                    default:
                        return "="   cell.CellFormula;
                }
            }

            public static string getFilePath()
            {
                string desktop = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                Console.WriteLine(desktop);//桌面路径
                                           // Console.ReadLine();

                string fileName = DateTime.Now.ToString("yyyyMMddHHmmssfff")   ".xls"; // 文件名称
                string urlPath = desktop   "/"   fileName; // 文件下载的URL地址
                Console.WriteLine(urlPath);
                return urlPath;
            }

            public static string exportExcel()
            {
                IWorkbook workbook;
                string file = getFilePath();
                string fileExt = Path.GetExtension(file).ToLower();
                if (string.IsNullOrEmpty(fileExt) == false)
                {
                    if (fileExt == ".xlsx")
                    {
                        workbook = new XSSFWorkbook();
                    }
                    else if (fileExt == ".xls")
                    {
                        workbook = new HSSFWorkbook();
                    }
                    else
                    {
                        workbook = null;
                    }
                    if (workbook == null)
                    {
                        return "没有工作簿";
                    }
                }
                else
                {
                    return "导出路径出错";
                }

                //ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
                ISheet sheet = workbook.CreateSheet("sheet1");

                //表头  
                IRow row = sheet.CreateRow(0);
                row.CreateCell(0).SetCellValue("ID");
                row.CreateCell(1).SetCellValue("名称");
                row.CreateCell(2).SetCellValue("名称");
                row.CreateCell(3).SetCellValue("名称");
                row.CreateCell(4).SetCellValue("名称");
                row.CreateCell(5).SetCellValue("名称");
                row.CreateCell(6).SetCellValue("名称");
                row.CreateCell(7).SetCellValue("名称");
                row.CreateCell(8).SetCellValue("名称");

                //数据  

                IRow row1 = sheet.CreateRow(3);
                for (int j = 0; j < 9; j  )
                {
                    ICell cell = row1.CreateCell(j);
                }

                /*修改样式关键代码*/
                ICellStyle style = workbook.CreateCellStyle();
                style.FillPattern = FillPattern.SolidForeground;
                style.BorderTop = BorderStyle.Thin;
                style.BorderRight = BorderStyle.Thin;
                style.BorderBottom = BorderStyle.Thin;
                style.BorderLeft = BorderStyle.Thin;
                row.Cells[5].CellStyle = style;

                row1.GetCell(0).SetCellValue("1");
                row1.GetCell(1).SetCellValue("2");
                row1.GetCell(2).SetCellValue("3");
                row1.GetCell(3).SetCellValue("4");
                row1.GetCell(4).SetCellValue("5");
                row1.GetCell(5).SetCellValue("6");
                row1.GetCell(6).SetCellValue("7");
                row1.GetCell(7).SetCellValue("8");
                row1.GetCell(8).SetCellValue("9");


                //转为字节数组  
                MemoryStream stream = new MemoryStream();
                workbook.Write(stream);
                var buf = stream.ToArray();

                //保存为Excel文件  
                using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
                {
                    fs.Write(buf, 0, buf.Length);
                    fs.Flush();
                }
                return "ok";
            }
        }

    }
}