• 使用NPOI操纵Excle,并输入到客户端


    NPOI下载:http://files.cnblogs.com/files/gosky/NPOI_2.2.0.0.zip

    导入以下5个引用:

    ICSharpCode.SharpZipLib.dll

    NPOI.dll

    NPOI.OOXML.dll

    NPOI.OpenXml4Net.dll

    NPOI.OpenXmlFormats.dll

    1.将Excle数据导入到DataTable中

    新建工具类: 根据上传文件后缀名判断 xls使用 ImportExcelFile2003方法 xlsx使用ImportExcelFile2007方法

    using NPOI.XSSF.UserModel;
    using NPOI.SS.UserModel;
    using NPOI.HSSF.UserModel;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.Text;
    using NPOI.SS.Formula.Eval;
    /// <summary>
    /// NPOIExcelHelper 的摘要说明
    /// </summary>
    public class NPOIExcelHelper
    {
    
        public static DataTable ImportExcelFile2007(string filePath)
        {
    
            XSSFWorkbook hssfworkbook;
            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new XSSFWorkbook(file);;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
    
            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();;
            DataTable dt = new DataTable();;
            rows.MoveNext();;
    
            XSSFRow row = (XSSFRow)rows.Current;
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());;  
                //将第一列作为列表头  
                dt.Columns.Add(row.GetCell(j).ToString());;
            }
            while (rows.MoveNext())
            {
                row = (XSSFRow)rows.Current;
                DataRow dr = dt.NewRow();;
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);;
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();;
                    }
                }
                dt.Rows.Add(dr);;
            }
            return dt;
        }
    
    
        public static DataTable ImportExcelFile2003(string filePath)
        {
    
            HSSFWorkbook hssfworkbook;
            #region//初始化信息
            try
            {
                using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
                {
                    hssfworkbook = new HSSFWorkbook(file);;
                }
            }
            catch (Exception e)
            {
                throw e;
            }
            #endregion
    
            NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);;
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();;
            DataTable dt = new DataTable();;
            rows.MoveNext();;
    
            HSSFRow row = (HSSFRow)rows.Current;
            for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
            {
                //dt.Columns.Add(Convert.ToChar(((int)'A') + j).ToString());;  
                //将第一列作为列表头  
                dt.Columns.Add(row.GetCell(j).ToString());;
            }
            while (rows.MoveNext())
            {
                row = (HSSFRow)rows.Current;
                DataRow dr = dt.NewRow();;
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    NPOI.SS.UserModel.ICell cell = row.GetCell(i);;
                    if (cell == null)
                    {
                        dr[i] = null;
                    }
                    else
                    {
                        dr[i] = cell.ToString();;
                    }
                }
                dt.Rows.Add(dr);;
            }
            return dt;
        }
    
    }

    2.将数据导出到Excle

    引用命名空间:

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

    代码实例:

    //创建工作簿
    HSSFWorkbook workbook = new HSSFWorkbook();
    //创建标签
    ISheet sheet = workbook.CreateSheet("sheet1");
    //新建表头
    IRow rowHead = sheet.CreateRow(0);
    rowHead.CreateCell(0, CellType.String).SetCellValue("序号");
    rowHead.CreateCell(1, CellType.String).SetCellValue("学生姓名");
    rowHead.CreateCell(2, CellType.String).SetCellValue("性别");
    rowHead.CreateCell(3, CellType.String).SetCellValue("生源地");
    rowHead.CreateCell(4, CellType.String).SetCellValue("身份证");
    rowHead.CreateCell(5, CellType.String).SetCellValue("准考证");
    rowHead.CreateCell(6, CellType.String).SetCellValue("专业名称");
    rowHead.CreateCell(7, CellType.String).SetCellValue("专业方向");
    rowHead.CreateCell(8, CellType.String).SetCellValue("来源");
    rowHead.CreateCell(9, CellType.String).SetCellValue("生源学校");
    rowHead.CreateCell(10, CellType.String).SetCellValue("审核状态");
    rowHead.CreateCell(11, CellType.String).SetCellValue("录取状态");
    //循环填充内容
    for (int i = 0; i < table.Rows.Count; i++)
    {
        IRow row = sheet.CreateRow(i + 1);
        DataRow datarow = table.Rows[i];
        row.CreateCell(0, CellType.String).SetCellValue(Convert.ToString(datarow["rn"]));
        row.CreateCell(1, CellType.String).SetCellValue(Convert.ToString(datarow["StuName"]));
        row.CreateCell(2, CellType.String).SetCellValue(Constants.sex[datarow["StuSex"].ToString()]);
        row.CreateCell(3, CellType.String).SetCellValue(Constants.stuAddr[datarow["StuProvince"].ToString()] + Constants.stuAddr[datarow["StuCity"].ToString()]);
        row.CreateCell(4, CellType.String).SetCellValue(Convert.ToString(datarow["StuCardNo"]));
        row.CreateCell(5, CellType.String).SetCellValue(Convert.ToString(datarow["StuZKNo"]));
        row.CreateCell(6, CellType.String).SetCellValue(Convert.ToString(datarow["zyname"]));
        row.CreateCell(7, CellType.String).SetCellValue(Convert.ToString(datarow["zyfx"]));
        row.CreateCell(8, CellType.String).SetCellValue(Constants.stuFrom[datarow["StuFrom"].ToString()]);
        row.CreateCell(9, CellType.String).SetCellValue(Convert.ToString(datarow["StuSchool"]));
        row.CreateCell(10, CellType.String).SetCellValue(Constants.stuState[datarow["State"].ToString()]);
        row.CreateCell(11, CellType.String).SetCellValue(Constants.stuAdmit[datarow["Admit"].ToString()]);
    }
    
    System.IO.MemoryStream ms = new System.IO.MemoryStream();
    //写入内存
    workbook.Write(ms);
    Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
    //输出到客户端
    Response.BinaryWrite(ms.ToArray());
    workbook = null;
    ms.Close();
    ms.Dispose();

    导出结果如图:

    3.将服务器上的文件输出到客户端

    //读取文件
    FileStream fileStream = new FileStream(Server.MapPath("~/Uploads/test.xlsx"), FileMode.Open);
    long fileSize = fileStream.Length;
    byte[] fileBuffer = new byte[fileSize];
    fileStream.Read(fileBuffer, 0, (int)fileSize);
    //如果不写fileStream.Close()语句,用户在下载过程中选择取消,将不能再次下载
    fileStream.Close();
    Response.ContentType = "application/octet-stream";
    Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyyMMdd")));
    Response.AddHeader("Content-Length", fileSize.ToString());
    Response.BinaryWrite(fileBuffer);
    Response.Flush();
    Response.Close();

    将服务器上 /Uploads/test.xlsx 文件 输出到客户端并使用日期命名

  • 相关阅读:
    CentOS7安装(三)- 配置阿里云yum源
    OSQA的配置
    MySQL学习 (三) Limit-Distinct-Union
    MySQL学习(二)-字段类型及约束
    MySQL学习(一)-基本知识
    Python闭包
    软件测试面试常考点
    人生感悟
    常用的Linux命令
    细说php一些常见的知识点
  • 原文地址:https://www.cnblogs.com/gosky/p/5394465.html
Copyright © 2020-2023  润新知