数据导入Excel表格

2021-09-30 13:32 111阅读 0赞

后台代码：

using BLL;
    using NPOI.HSSF.UserModel;
    using NPOI.SS.UserModel;
    using System;
    using System.Collections.Generic;
    using System.Data;
    using System.IO;
    using System.IO.MemoryMappedFiles;
    using System.Linq;
    using System.Web;
    using System.Web.UI;
    
    namespace EasyUI
    {
        /// <summary>
        /// ExcelHandler 的摘要说明
        /// </summary>
        public class ExcelHandler : IHttpHandler
        {
    
            public void ProcessRequest(HttpContext context)
            {
                try
                {
                    DataTable dt = new DataTable();
                    dt = Service.Excel();//数据源
                    string strFileName = "Excel";
                    //创建空excel文件
                    HSSFWorkbook workbook = new HSSFWorkbook();
                    ISheet sheet = string.IsNullOrEmpty(strFileName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(strFileName);
                    //表头
                    IRow row = sheet.CreateRow(0);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        ICell cell = row.CreateCell(i);
                        cell.SetCellValue(dt.Columns[i].ColumnName);
                        //创建列头的样式
                        HSSFCellStyle cellStyle = (HSSFCellStyle)workbook.CreateCellStyle();
                        //居中显示
                        cellStyle.Alignment = HorizontalAlignment.Center;
                        //垂直居中
                        cellStyle.VerticalAlignment = VerticalAlignment.Center;
                        HSSFFont font = (HSSFFont)workbook.CreateFont();
                        font.FontHeightInPoints = 12;
                        font.FontName = "宋体";
                        font.IsBold = true;
                        cellStyle.SetFont(font);
                        cell.CellStyle = cellStyle;
                    }
                    //数据
                    int[] len = new int[dt.Columns.Count];
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        //行
                        IRow rows = sheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            //列
                            ICell cell = rows.CreateCell(j);
                            cell.SetCellValue(dt.Rows[i][j].ToString());
                            var lenTemp = dt.Rows[i][j].ToString().Length > dt.Columns[j].ColumnName.Length ? dt.Rows[i][j].ToString().Length : dt.Columns[j].ColumnName.Length;
                            if (lenTemp > len[j])
                            {
                                len[j] = lenTemp;
                            }
                        }
                    }
                    //自动设置列宽
                    for (int i = 0; i < len.Length; i++)
                    {
                        if (len[i] < 8)
                        {
                            len[i] = Convert.ToInt32(Math.Round(len[i] * 2.5));
                        }
                        else
                        {
                            len[i] = Convert.ToInt32(Math.Round(len[i] * 1.3));
                        }
                        sheet.SetColumnWidth(i, len[i] * 256);
                    }
                    string fileName = strFileName + DateTime.Now.ToString("yyMMddHHmmssfff") + ".xls";
                    //写入到客户端
                    MemoryStream ms = new MemoryStream(); //创建其支持存储区为内存的流
                    //数据写入流中
                    workbook.Write(ms);
                    // 设置当前流的位置为流的开始
                    ms.Seek(0, SeekOrigin.Begin);
                    //防止中文乱码
                    fileName = HttpUtility.UrlEncode(fileName);
                    //设置输出编码格式
                    //context.Response.ContentEncoding = System.Text.Encoding.UTF8;
                    //设置输出流
                    context.Response.ContentType = "application/vnd.ms-excel";
                    //// 限制类型 
                    //context.Response.AddHeader("content-type", "application/vnd.ms-excel");
                    //下载后文件名
                    context.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8)); 
                    workbook = null;
                    ms.Close();
                    ms.Dispose();
                    //实现文件下载
                    context.Response.BinaryWrite(ms.ToArray());
                   
                }
                catch (Exception ex)
    
                {
    
                    context.Response.Write(ex.Message);
    
                }
                //ExportDataSetToExcel(dt, fileName, "sheet1");
            }
            public bool IsReusable
            {
                get
                {
                    return false;
                }
            }
        }
    }

返回前台后：  window.open("ExcelHandler.ashx");//打开浏览器窗口

别忘记在项目中引用NPOI

转载于:https://www.cnblogs.com/-zzc/p/9603594.html