62,243
社区成员
发帖
与我相关
我的任务
分享NPOI 导出Excel 多文件打包下载
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”类型的异常
...全文
请发表友善的回复…
发表回复
卫兵不容侵犯 2014-01-08
- 打赏
- 举报
怎么设置列名...
卫兵不容侵犯 2014-01-08
- 打赏
- 举报
求控制器中代码
thousandlin 2012-11-27
- 打赏
- 举报
我把代码换成 先保存在服务器 然后打包下载 也是同样的问题
------>给个处理的提醒，文件保存在服务器这个没错，现在还倒不出来的原因是你建立压缩文件的时候内存溢出了，之前我也是遇到这样的问题，后来处理压缩环节，就可以了。
暗尘掩月 2012-10-11
- 打赏
- 举报
我把代码换成 先保存在服务器 然后打包下载 也是同样的问题
暗尘掩月 2012-10-11
- 打赏
- 举报
经过测试 我把excel保存在服务器是不会出问题的
应当是使用 SharpZipLib 多文件打包下载的问题
请帮忙看下这里有什么问题 不知道是单个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的问题
我现在只能导出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条记录都可以啊。
我用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
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;
}
}
