使用NPOI根据DataSet(DataTable)创建Excel

来源：互联网发布：阿里云子账号登录编辑：程序博客网时间：2024/05/23 07:23

使用NPOI根据DataSet(DataTable)创建Excel

。逻辑代码：

using System;using System.Collections.Generic;using System.Data;using System.IO;using System.Linq;using System.Text;using System.Threading.Tasks;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using System.Web;namespace ExportExcel{   public class DataSetExportExcel    {/// <summary>///根据DataSet获得Excel文件流，保存文件        /// </summary>/// <param name="tableSet">DataSet内容</param>/// <param name="sheetName">Excel中表名</param>/// <param name="filePath">文件直接保存到本地的位置</param>/// <returns>Excel内容二进制流</returns>        public static List<string> RenderToExcel(DataSet tableSet, string sheetName, string filePath)        {            using (MemoryStream ms = ExportDataSetToExcel(tableSet, sheetName))            {                SaveToFile(ms, filePath);                byte[] bytes = ms.GetBuffer();                string encodeData = Convert.ToBase64String(bytes);                List<string> fileData = new List<string>();                fileData.Add(encodeData);                fileData.Add(ms.Length.ToString());                return fileData;            }        }/// <summary>        ///根据DataSet获得Excel文件流，弹出选择框用户选择储存位置        /// </summary>/// <param name="tableSet">DataSet内容</param>/// <param name="context">浏览器请求</param>        /// <param name="fileName">Excel文件名</param>/// <param name="sheetName">Excel中表名</param>        public static void RenderToExcel(DataSet tableSet, HttpContext context, string fileName, string sheetName)        {            using (MemoryStream ms = ExportDataSetToExcel(tableSet, sheetName))            {                RenderToBrowser(ms, context, fileName);            }        }        /// <summary>        /// 根据传入的DataSet导出Excel        /// </summary>        /// <param name="sourceDs">DataSet</param>        /// <param name="sheetName">Excel中表名</param>        /// <returns>Excel</returns>        private static MemoryStream ExportDataSetToExcel(DataSet sourceDs, string sheetName)        {            IWorkbook workbook = new HSSFWorkbook();//创建Workbook对象            MemoryStream ms = new MemoryStream();            string[] sheetNames = sheetName.Split(',');            for (int i = 0; i < sheetNames.Length; i++)            {                ISheet sheet = workbook.CreateSheet(sheetNames[i]);//创建工作表                IRow headerRow = sheet.CreateRow(0);                ICellStyle style = workbook.CreateCellStyle();//设置Excel单元格格式                style.Alignment = HorizontalAlignment.Left;//设置Excel内容left对齐                style.VerticalAlignment = VerticalAlignment.Center;//设置Excel内容垂直居中                // handling header.                foreach (DataColumn column in sourceDs.Tables[i].Columns)                {                    ICell Icell = headerRow.CreateCell(column.Ordinal);sheet.AutoSizeColumn(column.Ordinal);//根据内容自动调整单元格宽度                    Icell.SetCellValue(column.ColumnName);                    Icell.CellStyle = style;                }                // handling value.                int rowIndex = 1;                foreach (DataRow row in sourceDs.Tables[i].Rows)                {                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);                    foreach (DataColumn column in sourceDs.Tables[i].Columns)                    {                        ICell Icell = dataRow.CreateCell(column.Ordinal);sheet.AutoSizeColumn(column.Ordinal);                        Icell.SetCellValue(row[column].ToString());                        if (row[column].ToString().Contains("\n"))//包含\n的到\n处自动换行                        {                            style.WrapText = true;                            Icell.CellStyle = style;                        }                        //dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());                        Icell.CellStyle = style;                    }                    rowIndex++;                }            }            workbook.Write(ms);            workbook.Write(ms);            ms.Flush();            ms.Position = 0;            workbook = null;            return ms;        }        /// <summary>        /// 保存Excel文件        /// </summary>        /// <param name="ms">内存流</param>        /// <param name="fileName">Excel文件名</param>        private static void SaveToFile(MemoryStream ms, string fileName)        {            using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))            {                byte[] data = ms.ToArray();                fs.Write(data, 0, data.Length);                fs.Flush();                data = null;            }        }        /// <summary>        /// 在浏览器输出文件        /// </summary>        /// <param name="ms">Excel内容流</param>        /// <param name="context">浏览器请求</param>        /// <param name="fileName">Excel文件名</param>        private static void RenderToBrowser(MemoryStream ms, HttpContext context, string fileName)        {            if (context.Request.Browser.Browser == "IE")                fileName = HttpUtility.UrlEncode(fileName);            context.Response.ContentType = "application/vnd.ms-excel";            context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);            context.Response.BinaryWrite(ms.ToArray());        }    }}

调用：

。在IE界面可以弹出选择框保存到本地：

<span style="font-family:SimSun;font-size:14px;">List<string> list = new List<string>();list.Add("Test1"); list.Add("Test2"); list.Add("Test3"); list.Add("Test4"); DataSet dstmp = new DataSet(); if (dstmp.Tables.Contains("TAB_NM"))//已经存在该表的话，删除掉 dstmp.Tables.Remove("TAB_NM"); DataTable table = new DataTable("TAB_NM"); //创建虚拟数据表 DataColumnCollection columns = table.Columns;//获取列集合,添加列 columns.Add("col_id", typeof(String)); columns.Add("col_test1", typeof(String)); columns.Add("col_test2", typeof(String)); columns.Add("col_test3", typeof(String)); columns.Add("opr_date", typeof(DateTime)); DataRow row = table.NewRow();//添加一行数据 row["col_id"] = list[0]; row["col_test1"] = list[1]; row["col_test2"] = list[2]; row["col_test3"] = list[3]; row["opr_date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"); table.Rows.Add(row); dstmp.Tables.Add(table); //把信息表放入DataSet中 DataSetExportExcel.RenderToExcel(dstmp, Context,"Test.xls","sheet1");//调用方法打开界面另存Excel</span>

。直接保存到本地：

               <span style="font-family:SimSun;font-size:14px;">list = new List<string>();            list.Add("Test1");            list.Add("Test2");            list.Add("Test3");            list.Add("Test4");            DataSet dstmp = new DataSet();            if (dstmp.Tables.Contains("TAB_NM"))//已经存在该表的话，删除掉                dstmp.Tables.Remove("TAB_NM");            DataTable table = new DataTable("TAB_NM"); //创建虚拟数据表            DataColumnCollection columns = table.Columns;//获取列集合,添加列            columns.Add("col_id", typeof(String));            columns.Add("col_test1", typeof(String));            columns.Add("col_test2", typeof(String));            columns.Add("col_test3", typeof(String));            columns.Add("opr_date", typeof(DateTime));            DataRow row = table.NewRow();//添加一行数据            row["col_id"] = list[0];            row["col_test1"] = list[1];            row["col_test2"] = list[2];            row["col_test3"] = list[3];            row["opr_date"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");            table.Rows.Add(row);            dstmp.Tables.Add(table); //把信息表放入DataSet中            DataSetExportExcel.RenderToExcel(dstmp, "Test", @"C:\TEST\Test.xls");</span>

0 0