NPOI 导出Excel 多文件打包下载

暗尘掩月 2012-10-10 02:55:02

protected void Button1_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable("客户档案");
ArrayList arrColumns = GetXlsColumns();
DataTable dtMemberUser = DAL.Member_User.GetList();

for (int i = 0; i < arrColumns.Count; i++)
{
dt.Columns.Add(arrColumns[i].ToString());
}

foreach (DataRow dr in dtMemberUser.Rows)
{
DataRow row = dt.NewRow();
Model.Sell_Order modelSellOrder = DAL.Sell_Order.GetModel(Convert.ToInt32(dr["UserID"]));
if (modelSellOrder != null)
{
row["客户编码"] = "";
row["客户名称"] = dr["UserName"].ToString() != "" ? dr["UserName"] : dr["NickName"];
row["联系电话"] = dr["Tel"];
row["手机"] = dr["Mobile"];
row["旺旺ID"] = "";
row["收货人"] = modelSellOrder.Recipients;
row["客户分类"] = "";
row["省市"] = string.Format("{0} {1}", dr["Province"], dr["City"]);
row["MSN"] = "";
row["skype"] = "";
row["支付宝账号"] = "";
row["淘宝地址"] = "";
row["易趣地址"] = "";
row["拍拍地址"] = "";
row["传真"] = "";
row["电子邮件"] = dr["Email"];
row["邮编"] = modelSellOrder.Zip;
row["收货人地址"] = string.Format("{0} {1} {2} {3}", modelSellOrder.Province, modelSellOrder.City, modelSellOrder.Area, modelSellOrder.Street);
row["店名"] = "";
row["经营地址"] = "";
row["发展时间"] = dr["CreateTime"];
row["到期日期"] = "";
row["生日"] = dr["BirthDay"];
row["备注"] = "";
row["邮件内容"] = dr["Email"];
row["积分"] = dr["UsablePoint"];
row["性别"] = dr["Sex"];
row["证件类型"] = "";
row["证件号码"] = "";
row["店铺名称"] = "官网";
row["视力矫正要求"] = "";
row["屈光度状态"] = "";
row["自定义属性3"] = "";
row["自定义属性4"] = "";
row["自定义属性5"] = "";
row["自定义属性6"] = "";
row["自定义属性7"] = "";
row["自定义属性8"] = "";
row["自定义属性9"] = "";
dt.Rows.Add(row);
}

}
//GridView1.DataSource = dt;
//GridView1.DataBind();
string xlsHeaderText = string.Format("{0} (导入时删除此行)", "客户档案");
//Common.ExcelHelper.Export(dt, null, "客户档案(未下单).xls");

int recordCount = dt.Rows.Count; ;
int pageSize = 10000;
int pageCount = (recordCount % pageSize) == 0 ? recordCount / pageSize : (recordCount / pageSize) + 1;
MemoryStream st = new MemoryStream();
using (ZipFile zip = ZipFile.Create(st))
{
zip.BeginUpdate();
for (int pageIndex = 1; pageIndex <= pageCount; pageIndex++)
{
DataTable dtPage = Common.Utility.GetPage(pageIndex, pageSize, out recordCount, dt);
MemoryStream ms = Common.ExcelHelper.GetExcelStream(dtPage, xlsHeaderText);
StreamDataSource sds = new StreamDataSource(ms);
zip.Add(sds, string.Format("客户档案({0}).xls", pageIndex));
}
zip.CommitUpdate();
}

string filename = "客户档案.zip";
Response.ContentType = "application/zip";

if (Request.UserAgent.ToLower().IndexOf("msie") > -1)
{
filename = HttpUtility.UrlPathEncode(filename);
}

if (Request.UserAgent.ToLower().IndexOf("firefox") > -1)
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=\"{0}\"", filename));
}
else
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
}

Response.BinaryWrite(st.GetBuffer());
Response.Flush();
Response.End();
}

数据量小的时候是正常的,当数据量大的时 报错
Capacity: “ms.Capacity”引发了“System.ObjectDisposedException”类型的异常
Length: “ms.Length”引发了“System.ObjectDisposedException”类型的异常
Position: “ms.Position”引发了“System.ObjectDisposedException”类型的异常
...全文
1178 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
卫兵不容侵犯 2014-01-08
  • 打赏
  • 举报
回复
引用 7 楼 haukwong 的回复:

		
        protected static void SetSheetDropdownList(HSSFSheet excelSheet)
        {
            for (int i = 0; i < ListColumnsName.Count; i++)
            {
                string[] s = null;
                switch (i)
                {
                    case 5:
                        s = new string[] { "Y", "N" };
                        break;
                    case 7:
                        s = new string[] { "Y", "N" };
                        break;
                    case 8:
                        s = new string[] { "1", "2", "3", "4", "4*" };
                        break;

                    case 9:
                        s = new string[] { "In", "Out" };
                        break;
                    case 10:
                        s = new string[] { "1", "2", "3", "4", "4*" };
                        break;
                    case 11:
                        s = new string[] { "In", "Out" };
                        break;
                    case 12:
                        s = new string[]{"6","20","7","21","6.1","201.1","20.1","207","10","195","15","198","24",
                                "204","27","32","209","35.2","22","23","208","8.1","203","22.1","208.1"};
                        break;
                    case 13:
                        s = new string[] { "On-going agreement", "Single Occurrence", "No Restatement Possible", "Pending" };
                        break;
                    case 16:
                        s = new string[] { "Y", "N" };
                        break;
                    default:
                        break;
                }
                InsertList(excelSheet, s, 65535, i);
            }
        }

        /// <summary>
        /// 插入数据行
        /// </summary>
        protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
        {
            int rowCount = 0;
            int sheetCount = 1;
            HSSFSheet newsheet = null;
            //行数
            int daSourceCount = dtSource.Rows.Count;

            //循环数据源导出数据集
            newsheet = excelWorkbook.CreateSheet("Report Table");
            CreateHeader(newsheet, excelWorkbook);
            SetSheetDropdownList(newsheet);
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount++;
                //超出65535条数据 创建新的工作簿
                if (rowCount % 65535 == 0)
                {
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet("Report Table" + sheetCount);
                    CreateHeader(newsheet, excelWorkbook);
                    SetSheetDropdownList(newsheet);
                    rowCount = 1;
                }
                HSSFRow newRow = newsheet.CreateRow(rowCount);

                InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, rowCount);
            }
        }

        /// <summary>
        /// sheet1的插入
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook, int rowCount)
        {
            for (int i = 0; i < drSource.ItemArray.Length; i++)
            {
                string cellValue = string.Empty;
                HSSFCell cell = currentExcelRow.CreateCell(i);
                if (!(drSource.ItemArray[i] is DBNull))
                {
                    cellValue = drSource.ItemArray[i].ToString();
                }
                cell.SetCellValue(cellValue);
            }
        }

        protected static void InsertList(HSSFSheet excelSheet, string[] s, int rowCount, int cellIndex)
       {
            if (s == null)
            {
                return;
            }
            CellRangeAddressList regions = new CellRangeAddressList(1, rowCount, cellIndex, cellIndex);
            DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(s);
            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
            excelSheet.AddValidationData(dataValidate);
        }
		
        /// <summary>
        /// 设置边框绿色底色字体加粗
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <returns></returns>
        protected static HSSFCellStyle setborderGreen(HSSFWorkbook excelWorkBook)
        {
            HSSFCellStyle style = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
            HSSFFont font = excelWorkBook.CreateFont();
            font.Boldweight = (short)HSSFFont.BOLDWEIGHT_BOLD;
            style.SetFont(font);
            style.FillForegroundColor = HSSFColor.GREEN.LIGHT_GREEN.index;
            style.FillPattern = HSSFCellStyle.BORDER_THIN;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            return style;
        }
		
        /// <summary>
        /// 设置边框灰色底色字体加粗
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <returns></returns>
        protected static HSSFCellStyle setborderViolet(HSSFWorkbook excelWorkBook)
        {
            HSSFCellStyle style = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
            HSSFFont font = excelWorkBook.CreateFont();
            font.Boldweight = (short)HSSFFont.BOLDWEIGHT_BOLD;
            style.SetFont(font);
            style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
            style.FillPattern = HSSFCellStyle.BORDER_THIN;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            return style;
        }
    }
}

 

两个接起来。。。 我以前大概写的一个,版本比较老。。。
怎么设置列名...
卫兵不容侵犯 2014-01-08
  • 打赏
  • 举报
回复
引用 7 楼 haukwong 的回复:

		
        protected static void SetSheetDropdownList(HSSFSheet excelSheet)
        {
            for (int i = 0; i < ListColumnsName.Count; i++)
            {
                string[] s = null;
                switch (i)
                {
                    case 5:
                        s = new string[] { "Y", "N" };
                        break;
                    case 7:
                        s = new string[] { "Y", "N" };
                        break;
                    case 8:
                        s = new string[] { "1", "2", "3", "4", "4*" };
                        break;

                    case 9:
                        s = new string[] { "In", "Out" };
                        break;
                    case 10:
                        s = new string[] { "1", "2", "3", "4", "4*" };
                        break;
                    case 11:
                        s = new string[] { "In", "Out" };
                        break;
                    case 12:
                        s = new string[]{"6","20","7","21","6.1","201.1","20.1","207","10","195","15","198","24",
                                "204","27","32","209","35.2","22","23","208","8.1","203","22.1","208.1"};
                        break;
                    case 13:
                        s = new string[] { "On-going agreement", "Single Occurrence", "No Restatement Possible", "Pending" };
                        break;
                    case 16:
                        s = new string[] { "Y", "N" };
                        break;
                    default:
                        break;
                }
                InsertList(excelSheet, s, 65535, i);
            }
        }

        /// <summary>
        /// 插入数据行
        /// </summary>
        protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
        {
            int rowCount = 0;
            int sheetCount = 1;
            HSSFSheet newsheet = null;
            //行数
            int daSourceCount = dtSource.Rows.Count;

            //循环数据源导出数据集
            newsheet = excelWorkbook.CreateSheet("Report Table");
            CreateHeader(newsheet, excelWorkbook);
            SetSheetDropdownList(newsheet);
            foreach (DataRow dr in dtSource.Rows)
            {
                rowCount++;
                //超出65535条数据 创建新的工作簿
                if (rowCount % 65535 == 0)
                {
                    sheetCount++;
                    newsheet = excelWorkbook.CreateSheet("Report Table" + sheetCount);
                    CreateHeader(newsheet, excelWorkbook);
                    SetSheetDropdownList(newsheet);
                    rowCount = 1;
                }
                HSSFRow newRow = newsheet.CreateRow(rowCount);

                InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, rowCount);
            }
        }

        /// <summary>
        /// sheet1的插入
        /// </summary>
        /// <param name="dtSource"></param>
        /// <param name="drSource"></param>
        /// <param name="currentExcelRow"></param>
        /// <param name="excelSheet"></param>
        /// <param name="excelWorkBook"></param>
        protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook, int rowCount)
        {
            for (int i = 0; i < drSource.ItemArray.Length; i++)
            {
                string cellValue = string.Empty;
                HSSFCell cell = currentExcelRow.CreateCell(i);
                if (!(drSource.ItemArray[i] is DBNull))
                {
                    cellValue = drSource.ItemArray[i].ToString();
                }
                cell.SetCellValue(cellValue);
            }
        }

        protected static void InsertList(HSSFSheet excelSheet, string[] s, int rowCount, int cellIndex)
       {
            if (s == null)
            {
                return;
            }
            CellRangeAddressList regions = new CellRangeAddressList(1, rowCount, cellIndex, cellIndex);
            DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(s);
            HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
            excelSheet.AddValidationData(dataValidate);
        }
		
        /// <summary>
        /// 设置边框绿色底色字体加粗
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <returns></returns>
        protected static HSSFCellStyle setborderGreen(HSSFWorkbook excelWorkBook)
        {
            HSSFCellStyle style = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
            HSSFFont font = excelWorkBook.CreateFont();
            font.Boldweight = (short)HSSFFont.BOLDWEIGHT_BOLD;
            style.SetFont(font);
            style.FillForegroundColor = HSSFColor.GREEN.LIGHT_GREEN.index;
            style.FillPattern = HSSFCellStyle.BORDER_THIN;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            return style;
        }
		
        /// <summary>
        /// 设置边框灰色底色字体加粗
        /// </summary>
        /// <param name="excelWorkBook"></param>
        /// <returns></returns>
        protected static HSSFCellStyle setborderViolet(HSSFWorkbook excelWorkBook)
        {
            HSSFCellStyle style = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
            HSSFFont font = excelWorkBook.CreateFont();
            font.Boldweight = (short)HSSFFont.BOLDWEIGHT_BOLD;
            style.SetFont(font);
            style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
            style.FillPattern = HSSFCellStyle.BORDER_THIN;
            style.BorderBottom = HSSFCellStyle.BORDER_THIN;
            style.BorderLeft = HSSFCellStyle.BORDER_THIN;
            style.BorderRight = HSSFCellStyle.BORDER_THIN;
            style.BorderTop = HSSFCellStyle.BORDER_THIN;
            return style;
        }
    }
}

 

两个接起来。。。 我以前大概写的一个,版本比较老。。。
求控制器中代码
thousandlin 2012-11-27
  • 打赏
  • 举报
回复
我把代码换成 先保存在服务器 然后打包下载 也是同样的问题 ------>给个处理的提醒,文件保存在服务器这个没错,现在还倒不出来的原因是你建立压缩文件的时候内存溢出了,之前我也是遇到这样的问题,后来处理压缩环节,就可以了。
暗尘掩月 2012-10-11
  • 打赏
  • 举报
回复
我把代码换成 先保存在服务器 然后打包下载 也是同样的问题
暗尘掩月 2012-10-11
  • 打赏
  • 举报
回复
经过测试 我把excel保存在服务器是不会出问题的
应当是使用 SharpZipLib 多文件打包下载的问题

MemoryStream st = new MemoryStream();
using (ZipFile zip = ZipFile.Create(st))
{
zip.BeginUpdate();
for (int pageIndex = 1; pageIndex <= pageCount; pageIndex++)
{
DataTable dtPage = Common.Utility.GetPage(pageIndex, pageSize, out recordCount, dt);
MemoryStream ms = Common.ExcelHelper.GetExcelStream(dtPage, xlsHeaderText);
StreamDataSource sds = new StreamDataSource(ms);
zip.Add(sds, string.Format("客户档案({0}).xls", pageIndex));
}
zip.CommitUpdate();
}

string filename = "客户档案.zip";
Response.ContentType = "application/zip";

if (Request.UserAgent.ToLower().IndexOf("msie") > -1)
{
filename = HttpUtility.UrlPathEncode(filename);
}

if (Request.UserAgent.ToLower().IndexOf("firefox") > -1)
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename=\"{0}\"", filename));
}
else
{
Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
}

Response.BinaryWrite(st.GetBuffer());
Response.Flush();
Response.End();

请帮忙看下这里有什么问题 不知道是单个excel文件过大 还是文件太多的问题 打包下载后的文件 解压失败 程序没提示任何问题
孟子E章 2012-10-10
  • 打赏
  • 举报
回复
数据多了可以导出多个Sheet

足球中国 2012-10-10
  • 打赏
  • 举报
回复
excel2007之前版本只支持65536行数据。多了就换个方式吧。
lzk1018834925 2012-10-10
  • 打赏
  • 举报
回复
这是俺现在用的,不怎么地感觉,唉,希望谁帮忙修改下哈
我现在只能导出6W数据一下,多了就不行了。。。。
不知道是不是因为导出格式是03的问题
lzk1018834925 2012-10-10
  • 打赏
  • 举报
回复
晕哦,

using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using System.IO;

/// <summary>
///OutExcel 的摘要说明
/// </summary>
public class OutExcel
{
public OutExcel()
{
//
//TODO: 在此处添加构造函数逻辑
//
}
public void initdata(System.Web.UI.Page page, string filename, DataTable dt, string[] title)
{
page.Response.ContentType = "application/vnd.ms-excel";
page.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpUtility.UrlEncode(filename)));
page.Response.Clear();

InitializeWorkbook();
GenerateData(dt, title);
page.Response.BinaryWrite(WriteToStream().GetBuffer());
page.Response.End();
}
HSSFWorkbook hssfworkbook;

MemoryStream WriteToStream()
{
//Write the stream data of workbook to the root directory
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
return file;
}

protected void GenerateData(DataTable dt, string[] title)
{
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
IRow row_title = sheet1.CreateRow(0);
for (int i = 0; i < title.Length; i++)
{
row_title.CreateCell(i).SetCellValue(title[i]);
}

for (int i = 1; i <= dt.Rows.Count; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i - 1][j].ToString());
}
}
}

protected void InitializeWorkbook()
{
hssfworkbook = new HSSFWorkbook();

////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
hssfworkbook.DocumentSummaryInformation = dsi;

////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
}
}

调用

DataTable dt = DBHelper.GetDataTableBySql(sql_search);
OutExcel oe = new OutExcel();
string[] title = { "乡镇", "管理单位" };//表头

oe.initdata(this, "XXOO.xls", dt, title);
Hauk 2012-10-10
  • 打赏
  • 举报
回复


protected static void SetSheetDropdownList(HSSFSheet excelSheet)
{
for (int i = 0; i < ListColumnsName.Count; i++)
{
string[] s = null;
switch (i)
{
case 5:
s = new string[] { "Y", "N" };
break;
case 7:
s = new string[] { "Y", "N" };
break;
case 8:
s = new string[] { "1", "2", "3", "4", "4*" };
break;

case 9:
s = new string[] { "In", "Out" };
break;
case 10:
s = new string[] { "1", "2", "3", "4", "4*" };
break;
case 11:
s = new string[] { "In", "Out" };
break;
case 12:
s = new string[]{"6","20","7","21","6.1","201.1","20.1","207","10","195","15","198","24",
"204","27","32","209","35.2","22","23","208","8.1","203","22.1","208.1"};
break;
case 13:
s = new string[] { "On-going agreement", "Single Occurrence", "No Restatement Possible", "Pending" };
break;
case 16:
s = new string[] { "Y", "N" };
break;
default:
break;
}
InsertList(excelSheet, s, 65535, i);
}
}

/// <summary>
/// 插入数据行
/// </summary>
protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
{
int rowCount = 0;
int sheetCount = 1;
HSSFSheet newsheet = null;
//行数
int daSourceCount = dtSource.Rows.Count;

//循环数据源导出数据集
newsheet = excelWorkbook.CreateSheet("Report Table");
CreateHeader(newsheet, excelWorkbook);
SetSheetDropdownList(newsheet);
foreach (DataRow dr in dtSource.Rows)
{
rowCount++;
//超出65535条数据 创建新的工作簿
if (rowCount % 65535 == 0)
{
sheetCount++;
newsheet = excelWorkbook.CreateSheet("Report Table" + sheetCount);
CreateHeader(newsheet, excelWorkbook);
SetSheetDropdownList(newsheet);
rowCount = 1;
}
HSSFRow newRow = newsheet.CreateRow(rowCount);

InsertCell(dtSource, dr, newRow, newsheet, excelWorkbook, rowCount);
}
}

/// <summary>
/// sheet1的插入
/// </summary>
/// <param name="dtSource"></param>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelSheet"></param>
/// <param name="excelWorkBook"></param>
protected static void InsertCell(DataTable dtSource, DataRow drSource, HSSFRow currentExcelRow, HSSFSheet excelSheet, HSSFWorkbook excelWorkBook, int rowCount)
{
for (int i = 0; i < drSource.ItemArray.Length; i++)
{
string cellValue = string.Empty;
HSSFCell cell = currentExcelRow.CreateCell(i);
if (!(drSource.ItemArray[i] is DBNull))
{
cellValue = drSource.ItemArray[i].ToString();
}
cell.SetCellValue(cellValue);
}
}

protected static void InsertList(HSSFSheet excelSheet, string[] s, int rowCount, int cellIndex)
{
if (s == null)
{
return;
}
CellRangeAddressList regions = new CellRangeAddressList(1, rowCount, cellIndex, cellIndex);
DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(s);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
excelSheet.AddValidationData(dataValidate);
}

/// <summary>
/// 设置边框绿色底色字体加粗
/// </summary>
/// <param name="excelWorkBook"></param>
/// <returns></returns>
protected static HSSFCellStyle setborderGreen(HSSFWorkbook excelWorkBook)
{
HSSFCellStyle style = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
HSSFFont font = excelWorkBook.CreateFont();
font.Boldweight = (short)HSSFFont.BOLDWEIGHT_BOLD;
style.SetFont(font);
style.FillForegroundColor = HSSFColor.GREEN.LIGHT_GREEN.index;
style.FillPattern = HSSFCellStyle.BORDER_THIN;
style.BorderBottom = HSSFCellStyle.BORDER_THIN;
style.BorderLeft = HSSFCellStyle.BORDER_THIN;
style.BorderRight = HSSFCellStyle.BORDER_THIN;
style.BorderTop = HSSFCellStyle.BORDER_THIN;
return style;
}

/// <summary>
/// 设置边框灰色底色字体加粗
/// </summary>
/// <param name="excelWorkBook"></param>
/// <returns></returns>
protected static HSSFCellStyle setborderViolet(HSSFWorkbook excelWorkBook)
{
HSSFCellStyle style = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
HSSFFont font = excelWorkBook.CreateFont();
font.Boldweight = (short)HSSFFont.BOLDWEIGHT_BOLD;
style.SetFont(font);
style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
style.FillPattern = HSSFCellStyle.BORDER_THIN;
style.BorderBottom = HSSFCellStyle.BORDER_THIN;
style.BorderLeft = HSSFCellStyle.BORDER_THIN;
style.BorderRight = HSSFCellStyle.BORDER_THIN;
style.BorderTop = HSSFCellStyle.BORDER_THIN;
return style;
}
}
}






两个接起来。。。
我以前大概写的一个,版本比较老。。。
Hauk 2012-10-10
  • 打赏
  • 举报
回复

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using System.Data;
using System.IO;

namespace StarTech.NPOI
{
public class DVEE_NOPIHelper
{
/// <summary>
/// 导出列名
/// </summary>
public static System.Collections.SortedList ListColumnsName;
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="filePath"></param>
public static void ExportExcel(DataTable dtSource, string filePath)
{
if (ListColumnsName == null || ListColumnsName.Count == 0)
throw (new Exception("请对ListColumnsName设置要导出的列明!"));

HSSFWorkbook excelWorkbook = CreateExcelFile();
InsertRow(dtSource, excelWorkbook);
SaveExcelFile(excelWorkbook, filePath);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="filePath"></param>
public static void ExportExcel(DataTable dtSource, Stream excelStream)
{
if (ListColumnsName == null || ListColumnsName.Count == 0)
throw (new Exception("请对ListColumnsName设置要导出的列明!"));

HSSFWorkbook excelWorkbook = CreateExcelFile();
InsertRow(dtSource, excelWorkbook);
SaveExcelFile(excelWorkbook, excelStream);
}
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="excelWorkBook"></param>
/// <param name="filePath"></param>
protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
{
FileStream file = null;
try
{
file = new FileStream(filePath, FileMode.Create);
excelWorkBook.Write(file);
}
finally
{
if (file != null)
{
file.Close();
}
}
}
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="excelWorkBook"></param>
/// <param name="filePath"></param>
protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, Stream excelStream)
{
try
{
excelWorkBook.Write(excelStream);
}
finally
{

}
}
/// <summary>
/// 创建Excel文件
/// </summary>
/// <param name="filePath"></param>
protected static HSSFWorkbook CreateExcelFile()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
return hssfworkbook;
}
/// <summary>
/// 创建excel表头
/// </summary>
/// <param name="dgv"></param>
/// <param name="excelSheet"></param>
public static void CreateHeader(HSSFSheet excelSheet, HSSFWorkbook excelWorkbook)
{
int cellIndex = 0;

//循环导出列
foreach (System.Collections.DictionaryEntry de in ListColumnsName)
{

HSSFRow newRow = excelSheet.CreateRow(0);
HSSFCell newCell = newRow.CreateCell(cellIndex);

if (cellIndex >= 0 && cellIndex <= 17)
{

newCell.CellStyle = setborderViolet(excelWorkbook);

}
else
{

newCell.CellStyle = setborderGreen(excelWorkbook);

}
newCell.SetCellValue(de.Value.ToString());
excelSheet.SetColumnWidth(cellIndex, 5000);
cellIndex++;
}
}


太长了,下面继续。。。
Hauk 2012-10-10
  • 打赏
  • 举报
回复
代码太多,看起来好累。。。
我用NPOI导出30多W条记录都可以啊。
liutao132 2012-10-10
  • 打赏
  • 举报
回复
添加这几句
HttpContext curContext = HttpContext.Current;

// 设置编码和附件格式
curContext.Response.ContentType = "application/vnd.ms-excel";
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = "";


参考:http://www.cnblogs.com/dreamof/archive/2010/06/02/1750151.html
暗尘掩月 2012-10-10
  • 打赏
  • 举报
回复
总数据量超过5000条就出错.... 来个大神啊
暗尘掩月 2012-10-10
  • 打赏
  • 举报
回复
GetExcelStream方法数据量大的时候就出错


/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream GetExcelStream(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();

#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "";
workbook.DocumentSummaryInformation = dsi;

SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = ""; //填加xls文件作者信息
si.ApplicationName = ""; //填加xls文件创建程序信息
si.LastAuthor = ""; //填加xls文件最后保存者信息
si.Comments = ""; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = "";//填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion

ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach(DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for(int i = 0; i < dtSource.Rows.Count; i++)
{
for(int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if(intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}



int rowIndex = 0;

foreach(DataRow row in dtSource.Rows)
{
#region 新建表,填充表头,填充列头,样式
if(rowIndex == 65535 || rowIndex == 0)
{
if(rowIndex != 0)
{
sheet = workbook.CreateSheet();
}

#region 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);

ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);

headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));

}
#endregion


#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);


ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);


foreach(DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);

}

}
#endregion

rowIndex = 2;
}
#endregion


#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach(DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);

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);
newCell.SetCellValue(dateV);

newCell.CellStyle = dateStyle;//格式化显示
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.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}

}
#endregion

rowIndex++;
}


using(MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;

workbook.Dispose();
return ms;
}

}

61,657

社区成员

发帖
与我相关
我的任务
社区描述
.NET技术交流专区
javascript云原生 企业社区
社区管理员
  • ASP.NET
  • .Net开发者社区
  • R小R
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告

.NET 社区是一个围绕开源 .NET 的开放、热情、创新、包容的技术社区。社区致力于为广大 .NET 爱好者提供一个良好的知识共享、协同互助的 .NET 技术交流环境。我们尊重不同意见,支持健康理性的辩论和互动,反对歧视和攻击。

希望和大家一起共同营造一个活跃、友好的社区氛围。

试试用AI创作助手写篇文章吧