首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >NPOI简述与运用

NPOI简述与运用

作者头像
跟着阿笨一起玩NET
发布2018-09-19 15:16:50
7890
发布2018-09-19 15:16:50
举报

最近想把项目中Excel中的操作部分改成NPOI ,由于2.0版本已经支持office07/10格式,但还处于测试版不稳定,于是封装如下代码

  • 1.支持1.25版本
  • 2.支持B/S与C/S导入导出
  • 3.知道Excel数据导入数据库

   需要合并单元格等需求的功能没有 自己添加吧!

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.IO;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.SS.UserModel;
using System.Data.SqlClient;
/*
 Vesion: 适用于 NPOI 1.25版本
 Author: Irving
 Description: Execl操作
 Date:2012年6月25日16:17:06
 UpdateDate:2012年9月26日13:03:31 修正日期与导入数据库中的性能
 * 
 * 
 * 
 *          //必须与数据源中的列一致
            Dictionary<string, string> dtDictText = new Dictionary<string, string>();
            dtDictText.Add("ProductID", "唯一号");
            dtDictText.Add("ProductNo", "编号");
            dtDictText.Add("ProductName", "姓名");
            dtDictText.Add("Price", "价格");
            dtDictText.Add("Memo", "备注");

            NPOIHelper.ExportByWeb(dtDictText, "sheet1", "测试啦", HttpContext.Current, BLL.ProductBLL.LoadDataByDataSet());
            NPOIHelper.ExportByWin(dtDictText, "sheet1", "测试啦", AppDomain.CurrentDomain.BaseDirectory, BLL.ProductBLL.LoadDataByDataSet());
 *          NPOIHelper.Import(Server.MapPath("/Files/" + "测试啦.xls"));
 * 
 * 
 * 
 */
namespace Common
{
    public class NPOIHelper
    {
        /// <summary>
        /// 读取Excel
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <param name="sheetNo">Sheet编号(默认为0,即sheet1,可选参数)</param>
        /// <returns></returns>
        public static DataTable Import(string filePath, params int[] sheetNo)
        {
            int sheetNO;
            if (sheetNo.Length != 0)
                sheetNO = sheetNo[0];
            else
                sheetNO = 0;
            DataTable dt = new DataTable();
            HSSFWorkbook hssfworkbook;
            using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(file);
            }
            HSSFSheet sheet = hssfworkbook.GetSheetAt(sheetNO) as HSSFSheet;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            HSSFRow headerRow = sheet.GetRow(0) as HSSFRow;
            int cellCount = headerRow.LastCellNum;
            for (int j = 0; j < cellCount; j++)
            {
                HSSFCell cell = headerRow.GetCell(j) as HSSFCell;
                dt.Columns.Add(cell.ToString());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                HSSFRow row = sheet.GetRow(i) as HSSFRow;
                DataRow dataRow = dt.NewRow();

                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                    {
                        if (row.GetCell(j).CellType.ToString() != "NUMERIC")//处理时间格式化问题
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                        else
                        {
                            dataRow[j] = row.GetCell(j).DateCellValue.Date.ToString();
                        }
                    }
                }

                dt.Rows.Add(dataRow);
            }
            return dt;
        }

        /// <summary>
        /// 导出Excel数据(Win)
        /// </summary>
        /// <param name="dtDictHeadText">表头数据(字段必须与数据源一致)</param>
        /// <param name="sheetName">纸张名</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="filePath">路径</param>
        /// <param name="dtSource">数据源</param>
        public static void ExportByWin(Dictionary<string, string> dtDictHeadText, string sheetName, string fileName, string filePath, DataTable dtSource)
        {
            HSSFWorkbook hssfworkbook = Export(dtDictHeadText, sheetName, fileName, dtSource);
            try
            {
                using (Stream stream = File.Create(String.Format("{0}{1}.xls", filePath, fileName)))
                {
                    hssfworkbook.Write(stream);
                    stream.Flush();
                }
            }
            catch (Exception ex)
            {
                //Log4记录
                throw new Exception("操作失败: " + ex.Message);
            }
        }
        /// <summary>
        /// 导出Excel数据(Web)
        /// </summary>
        /// <param name="dtDictHeadText">表头数据(字段必须与数据源一致)</param>
        /// <param name="sheetName">纸张名</param>
        /// <param name="fileName">文件名</param>
        /// <param name="context">上下文对象</param>
        /// <param name="dtSource">数据源</param>
        public static void ExportByWeb(Dictionary<string, string> dtDictHeadText, string sheetName, string fileName, HttpContext context, DataTable dtSource)
        {
            HSSFWorkbook hssfworkbook = Export(dtDictHeadText, sheetName, fileName, dtSource);
            try
            {
                using (MemoryStream ms = new MemoryStream())
                {
                    hssfworkbook.Write(ms);
                    ms.Flush();
                    ms.Position = 0;
                    context.Response.ContentType = "application/vnd.ms-excel";
                    context.Response.ContentEncoding = Encoding.UTF8;
                    context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName + ".xls", Encoding.UTF8));
                    context.Response.BinaryWrite(ms.ToArray());
                }
            }
            catch (Exception ex)
            {
                //Log4记录
                throw new Exception("操作失败: " + ex.Message);
            }
        }

        /// <summary>
        /// 读取Execl数据到数据库中
        /// </summary>
        /// <param name="conText">连接字符串</param>
        /// <param name="tabeName">表名</param>
        /// <param name="ipDict">字典(SourceColumn(数据源列名称)  DestinationColumn(目标列名称))</param>
        /// <param name="filePath">路径(只支持xls格式)</param>
        /// <param name="sheetNo">纸张页</param>
        public static void ImportExcelDBSourceIntoTable(string conText, string tabeName, Dictionary<string, string> ipDict, string filePath, params int[] sheetNo)
        {
            int sheetNO;
            if (sheetNo.Length != 0)
                sheetNO = sheetNo[0];
            else
                sheetNO = 0;
            using (SqlConnection conn = new SqlConnection(conText))
            {
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                DataTable dt = NPOIHSSFHelper.Import(filePath, sheetNO);
                SqlBulkCopy blkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, tran) { BatchSize = dt.Rows.Count, DestinationTableName = tabeName };
                foreach (KeyValuePair<string, string> item in ipDict)
                {
                    blkCopy.ColumnMappings.Add(item.Key, item.Value).ToString().Trim();
                }
                try
                {
                    blkCopy.WriteToServer(dt);
                    tran.Commit();
                    conn.Close();
                    blkCopy.Close();
                }
                catch (Exception ex)
                {   //Log4
                    tran.Rollback();
                    conn.Close();
                    blkCopy.Close();
                    throw new Exception(ex.Message);
                }
            }
        }
        #region  操作
        /// <summary>
        /// 填充数据
        /// </summary>
        /// <param name="dtDictHeadText">表头字典</param>
        /// <param name="sheetName">名称</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="dtSource">数据源</param>
        /// <returns></returns>
        private static HSSFWorkbook Export(Dictionary<string, string> dtDictHeadText, string sheetName, string fileName, DataTable dtSource)
        {
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            SetSummaryData(hssfworkbook, fileName);                       //版权信息
            ISheet sheet = hssfworkbook.CreateSheet(sheetName);
            SetHeadData(dtSource, dtDictHeadText, hssfworkbook, sheet);   //创建表头
            int rowIndex = 1;
            foreach (DataRow row in dtSource.Rows)
            {
                HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
                SetContentData(dtSource, dtDictHeadText, row, dataRow, hssfworkbook);//填充数据集
                rowIndex++;
            }
            return hssfworkbook;
        }
        /// <summary>
        /// 属性信息
        /// </summary>
        /// <param name="workbook"></param>
        /// <param name="fileName"></param>
        private static void SetSummaryData(HSSFWorkbook workbook, string fileName)
        {
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI";
            workbook.DocumentSummaryInformation = dsi;
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Author = "Irving"; //填加xls文件作者信息
            si.Title = fileName; //填加xls文件标题信息
            si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
            si.Comments = "作者信息"; //填加xls文件作者信息
            si.Subject = "主题信息";//填加文件主题信息
            si.CreateDateTime = DateTime.Now;
            workbook.SummaryInformation = si;
        }
        /// <summary>
        /// 填充表头数据
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="dtDictHeadText"></param>
        /// <param name="workbook"></param>
        /// <param name="sheet"></param>
        private static void SetHeadData(DataTable dtSource, Dictionary<string, string> dtDictHeadText, HSSFWorkbook workbook, ISheet sheet)
        {
            HSSFRow headerRow = sheet.CreateRow(0) as HSSFRow;
            int index = 0;
            foreach (KeyValuePair<string, string> item in dtDictHeadText)
            {
                foreach (DataColumn column in dtSource.Columns)
                {
                    if (column.ColumnName.Contains(item.Key))
                    {
                        headerRow.CreateCell(index).SetCellValue(item.Value);
                        index++;
                        break;
                    }
                }
            }
        }
        /// <summary>
        /// 填充表数据
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="dtDictHeadText"></param>
        /// <param name="row"></param>
        /// <param name="dataRow"></param>
        /// <param name="workbook"></param>
        private static void SetContentData(DataTable dtSource, Dictionary<string, string> dtDictHeadText, DataRow row, HSSFRow dataRow, HSSFWorkbook workbook)
        {
            int index = 0;
            foreach (KeyValuePair<string, string> item in dtDictHeadText)
            {
                foreach (DataColumn column in dtSource.Columns)
                {
                    if (item.Key.ToLower() == column.ColumnName.ToLower())
                    {
                        HSSFCell newCell = dataRow.CreateCell(index) as HSSFCell;
                        string drValue = row[column].ToString();

                        switch (column.DataType.ToString())
                        {
                            case "System.String"://字符串类型
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.DateTime"://日期类型
                                DateTime dateV;
                                DateTime.TryParse(drValue, out dateV);
                                if (drValue.Contains("上午 12:00:00"))
                                {
                                    drValue = dateV.ToString("yyyy/MM/dd");
                                }
                                else
                                {
                                    if (!string.IsNullOrEmpty(drValue))
                                    {
                                        drValue = dateV.ToString();
                                    }
                                }
                                newCell.SetCellValue(drValue);
                                break;
                            case "System.Boolean"://布尔型
                                bool boolV = false;
                                bool.TryParse(drValue, out boolV);
                                newCell.SetCellValue(boolV);
                                break;
                            case "System.Int16"://整型
                            case "System.Int32":
                            case "System.Int64":
                            case "System.Byte":
                                int intV = 0;
                                int.TryParse(drValue, out intV);
                                newCell.SetCellValue(intV);
                                break;
                            case "System.Decimal"://浮点型
                            case "System.Double":
                                double doubV = 0;
                                double.TryParse(drValue, out doubV);
                                newCell.SetCellValue(doubV);
                                break;
                            case "System.Guid"://GUID
                                Guid guid = Guid.Empty;
                                Guid.TryParse(drValue, out guid);
                                newCell.SetCellValue(guid.ToString());
                                break;
                            case "System.DBNull"://空值处理
                                newCell.SetCellValue("");
                                break;
                            default:
                                newCell.SetCellValue("");
                                break;
                        }
                        index++;
                        break;
                    }
                }
            }
        }
        #endregion
    }
}
本文参与 腾讯云自媒体分享计划,分享自作者个人站点/博客。
原始发表:2012-11-02 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档