金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > c# .Net :Excel NPOI导入导出操作教程之List集合的数据

c# .Net :Excel NPOI导入导出操作教程之List集合的数据

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-07 16:25

将List集合的数据写到一个Excel文件并导出示例:

该链接有导入,导出源码,我的代码有下链接改写,完善而成的,

    string sql = @"select * from T_Excel";

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;

————————————————DataTable Star————————————————
        DataTable dt = SqlHelper.ExecuteDataTable(sql);
        if (dt.Rows.Count > 0)
        {
            //创建工作簿
            IWorkbook workbook = new HSSFWorkbook();
            //创建表
            ISheet sheet = workbook.CreateSheet("DBToExcel");
            IRow row0 = sheet.CreateRow(0);
            row0.CreateCell(0).SetCellValue("id信息");
            row0.CreateCell(1).SetCellValue("名称信息");
            row0.CreateCell(2).SetCellValue("备注信息");
            for (int r = 0; r < dt.Rows.Count; r++)
            {
                //创建行接受DataTable的行数据
                IRow row = sheet.CreateRow(r + 1);
                row.CreateCell(0).SetCellValue((int)dt.Rows[r]["Id"]);
                row.CreateCell(1).SetCellValue(dt.Rows[r]["Name"].ToString());
                row.CreateCell(2).SetCellValue(dt.Rows[r]["Remarks"].ToString());

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.IO;
using System.Data;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using NPOI.HSSF.Util;
using NPOI.HSSF.Extractor;
using NPOI.SS.UserModel;
using System.Web.UI.HtmlControls;

            }

  List<UserInfo> listUser = new List<UserInfo>()
           {
               new UserInfo { name="1", id="1", phone="1r" },
               new UserInfo { name="2", id="2", phone="2r" },
               new UserInfo { name="3", id="3", phone="3r" },
金沙棋牌app手机下载,               new UserInfo { name="4", id="4", phone="4r" },
               new UserInfo { name="5", id="5", phone="5r" },
           };
        1、//创建工作簿对象
       IWorkbook workbook = new HSSFWorkbook();
        2、//创建工作表
        ISheet sheet = workbook.CreateSheet("onesheet");
        IRow row0 = sheet.CreateRow(0);
        row0.CreateCell(0).SetCellValue("用户Id");
        row0.CreateCell(1).SetCellValue("用户名称");
        row0.CreateCell(2).SetCellValue("用户备注信息");
        for (int r = 1; r < listUser.Count; r++)
        {
            3、//创建行row
            IRow row = sheet.CreateRow(r);
            row.CreateCell(0).SetCellValue(listUser[r].id);
            row.CreateCell(1).SetCellValue(listUser[r].name);
            row.CreateCell(2).SetCellValue(listUser[r].phone);
            row.CreateCell(3).SetCellValue(listUser[r].pwd);
        }

private void to_excel()
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
MemoryStream ms = new MemoryStream();

    using (FileStream saveurl = File.OpenWrite(@"C:UsersAdministratorDesktop112.xls"))
            {
                workbook.Write(saveurl);
                Title = "已经导出数据!";
            }
        }
        else
        {
            Title = "没有导出任何数据!";
        }

    //创建流对象并设置存储Excel文件的路径
        using (FileStream url = File.OpenWrite(@"C:UsersAdministratorDesktop写入excel.xls"))
        {

ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);

————————————————DataTable END————————————————

    //导出Excel文件
            workbook.Write(url);
            Response.Write("<script>alert('写入成功!')</script>");
        };

//用column name 作为列名
string[] temArr = { "帐号", "子帐号", "客户经理号(8位)", "开户日期(YYYYMMDD)" };
List<string> headlist = new List<string>(temArr);
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (var item in headlist)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}

————————————————SqlDataReader star ————————————————
            SqlDataReader reader = SqlHelper.ExecuteReader(sql);
            if (reader.HasRows)
            {
               //创建工作簿
                IWorkbook workbook = new HSSFWorkbook();
                //创建工作表
               ISheet sheet = workbook.CreateSheet("DBToExcel");
                int rowIndex = 0;
                while (reader.Read())
                {
                    //读取一条数据就创建一行row
                    IRow row = sheet.CreateRow(rowIndex);
                    //记录读取数据行数
                    rowIndex++;
                    row.CreateCell(0).SetCellValue(reader.GetInt32(0));
                    row.CreateCell(1).SetCellValue(reader.GetString(1));
                    row.CreateCell(2).SetCellValue(reader.GetString(2));
               }

——————————分享End——————————

ICellStyle cellStyle = workbook.CreateCellStyle();

   //将数据写到硬盘
            using (FileStream saveurl = File.OpenWrite(@"C:UsersAdministratorDesktop112.xls"))
            {
                workbook.Write(saveurl);
                Title = "已经导出数据!";
            }
        }
        else
        {
            Title = "没有导出任何数据!";
        }

//为避免日期格式被Excel自动替换,所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;

 ————————————————SqlDataReader  end————————————————

NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);

//建立内容行
TMSDataContext dc = new TMSDataContext();

var lctlist = dc.wd_lct_dq_import.ToList();
int iRowIndex = 1;
//int iCellIndex = 0;
foreach (var lct in lctlist)
{
IRow DataRow = sheet.CreateRow(iRowIndex);

//第1列 账号
ICell cell = DataRow.CreateCell(0);
cell.SetCellValue(lct.zh);
cell.CellStyle = cellStyle;
//第2列 子账号
cell = DataRow.CreateCell(1);
cell.SetCellValue(lct.zh_sub);
cell.CellStyle = cellStyle;
//第3列 客户经理号
cell = DataRow.CreateCell(2);
cell.SetCellValue(lct.emp_lch);
cell.CellStyle = cellStyle;
//第4列 开户日期
cell = DataRow.CreateCell(3);
cell.SetCellValue(lct.kh_rq);
cell.CellStyle = cellStyle;

iRowIndex++;
}

//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(ms);
Response.AddHeader("Content-Disposition", string.Format("attachment; filename=EmptyWorkbook.xls"));
Response.BinaryWrite(ms.ToArray());

workbook = null;
ms.Close();
ms.Dispose();

}

本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:c# .Net :Excel NPOI导入导出操作教程之List集合的数据

关键词:

上一篇:查询某时间段的数据,复合查询

下一篇:没有了