问题：导出Excel文档的时候只能导出65535条数据，超出部分无法导出也不会报错
解决方案：创建多个sheet 具体实现如下
/// <summary>
/// 导出
/// </summary>
/// <param name="_strWhere">查询条件</param>
protected void EXCEL(string _strWhere)
{
BLL.CardBLL bll = new BLL.CardBLL();
BLL.orders bll1 = new BLL.orders();
if (model != null)
{
string sheetName = "商品列表";
DataTable dt = PrisonersInformationListProxy.GetDateList(_strWhere, " add_time desc").Tables[0];
FpSpread fp = new FpSpread();
//每页显示行数*
int sheetRowTotalCount = 20000;
//页数
int sheetCount = dt.Rows.Count / sheetRowTotalCount;
if (dt.Rows.Count % sheetRowTotalCount > 0)
{
sheetCount++;
}
//DataTable 索引
int rowIndex = 0;
for (int i = 0; i < sheetCount; i++)
{
//获取一个Sheet数据
//SheetView svProject = GenerateSheet(string.Format("{0}-{1}", sheetName, i+1), dt, sheetRowTotalCount, ref rowIndex);
SheetView svProject = GenerateSheet(sheetName, dt, sheetRowTotalCount, ref rowIndex);
fp.Sheets.Add(svProject);
}
System.IO.MemoryStream ms = new MemoryStream();
fp.SaveExcel(ms);
Response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("商品列表" + ".xls", System.Text.Encoding.UTF8).Replace("+", "%20"));
Response.AddHeader("Content-Length ", ms.ToArray().Length.ToString());
Response.ContentType = "application/vnd.ms-excel";
Response.BinaryWrite(ms.ToArray());
Response.End();
Response.Clear();
}
}
/// <summary>
/// 生成Sheet表格
/// </summary>
/// <param name="sheetName">Sheet标签名</param>
/// <param name="dataTable">数据表</param>
/// <param name="pageRowCount">每页数量</param>
/// <param name="rowIndex">当前索引</param>
/// <returns></returns>
private SheetView GenerateSheet(string sheetName, DataTable dataTable, int pageRowCount, ref int rowIndex)
{
//获取剩余行数
int lastCount = dataTable.Rows.Count - rowIndex;
//本页起始数
int pageFristIndex = rowIndex + 1;
//取最后一页的余数：8191
if (lastCount > pageRowCount)
{
//设置当前页数
lastCount = pageRowCount;
}
SheetView svProject = new SheetView();
string title = "戒毒学员商品列表";
svProject.HorizontalGridLine = new FarPoint.Win.Spread.GridLine(FarPoint.Win.Spread.GridLineType.Raised);//网格线
svProject.OperationMode = FarPoint.Win.Spread.OperationMode.ReadOnly;//只读
FarPoint.Win.ComplexBorderSide cBorderSide = new FarPoint.Win.ComplexBorderSide(FarPoint.Win.ComplexBorderSideStyle.ThinLine);
//边框
FarPoint.Win.ComplexBorder cBorder = new FarPoint.Win.ComplexBorder(cBorderSide, cBorderSide, cBorderSide, cBorderSide);
svProject.SheetName = sheetName;
svProject.Cells[0, 0].Font = new System.Drawing.Font("Gerorgia", 12);
svProject.Cells[0, 0].Value = title;
svProject.Cells[0, 0].Row.Height = 50;
svProject.AddSpanCell(0, 0, 1, 8);
svProject.Cells[0, 0].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;//左右居中
svProject.Cells[0, 0].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;//上下居中
svProject.Cells[0, 0].Border = cBorder;
svProject.Cells[0, 8].Value = "总合计金额：" + dataTable.Compute("sum(totalprice)", "").ToString() + " 元";
svProject.AddSpanCell(0, 8, 1, 3);
svProject.Cells[0, 10].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;//左右居中
svProject.Cells[0, 10].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;//上下居中
svProject.Cells[0, 10].Border = cBorder;
svProject.Cells[1, 0].Value = "序号";
svProject.Cells[1, 1].Value = "人员名称";
svProject.Cells[1, 2].Value = "人员卡号";
svProject.Cells[1, 3].Value = "商品类别";
svProject.Cells[1, 4].Value = "商品名称";
svProject.Cells[1, 5].Value = "商品价格";
svProject.Cells[1, 6].Value = "商品数量";
svProject.Cells[1, 7].Value = "总金额";
svProject.Cells[1, 8].Value = "时间";
svProject.Cells[1, 9].Value = "状态";
svProject.Cells[1, 10].Value = "配送单";
svProject.Cells[1, 0].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 1].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 2].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 3].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 4].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 5].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 6].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 7].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 8].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 9].Font = new System.Drawing.Font("Gerorgia", 11);
svProject.Cells[1, 10].Font = new System.Drawing.Font("Gerorgia", 11);
int Count = rptList.Items.Count;
//zsvProject.RowCount = dt.Rows.Count + 10;//原有内容
svProject.RowCount = lastCount + 10;//原有内容
double sumTotal = 0;
for (int i = 0; i < lastCount; i++)
{
svProject.Cells[i + 2, 0].Value = rowIndex + 1;
svProject.Cells[i + 2, 1].Value = dataTable.Rows[rowIndex]["UserName"].ToString();
svProject.Cells[i + 2, 2].Value = dataTable.Rows[rowIndex]["CardNUM"].ToString();
svProject.Cells[i + 2, 3].Value = dataTable.Rows[rowIndex]["categoryName"].ToString();
svProject.Cells[i + 2, 4].Value = dataTable.Rows[rowIndex]["TITLE"].ToString();
svProject.Cells[i + 2, 5].Value = dataTable.Rows[rowIndex]["PRICE"].ToString();
svProject.Cells[i + 2, 6].Value = dataTable.Rows[rowIndex]["QUANTITY"].ToString();
svProject.Cells[i + 2, 7].Value = dataTable.Rows[rowIndex]["totalprice"].ToString();
svProject.Cells[i + 2, 8].Value = dataTable.Rows[rowIndex]["payment_time"].ToString();
svProject.Cells[i + 2, 9].Value = dataTable.Rows[rowIndex]["statuscn"].ToString();
svProject.Cells[i + 2, 10].Value = dataTable.Rows[rowIndex]["delivery_title"].ToString();
svProject.Rows[i + 1].Height = 30;
// sumTotal +=dataTable.Rows[rowIndex]["totalprice"] is null ? 0 : Convert.ToDouble(dataTable.Rows[rowIndex]["totalprice"]);
sumTotal += Convert.ToDouble(dataTable.Rows[rowIndex]["totalprice"]);
rowIndex += 1;//索引增加
}
//本页合计行
svProject.Cells[lastCount + 2, 0].Font = new System.Drawing.Font("Gerorgia", 12);
svProject.Cells[lastCount + 2, 0].Value = "";
svProject.Cells[lastCount + 2, 0].Row.Height = 50;
svProject.AddSpanCell(lastCount + 2, 0, 1, 8);
svProject.Cells[lastCount + 2, 8].Value = "本页合计金额：" + sumTotal + " 元";
svProject.AddSpanCell(lastCount+2, 8, 1, 3);
svProject.Cells[lastCount + 2, 10].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;//左右居中
svProject.Cells[lastCount + 2, 10].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;//上下居中
//单个页名称设置
svProject.SheetName = string.Format("{0}({1}-{2})", sheetName, pageFristIndex, rowIndex);
for (int i = 0; i < svProject.NonEmptyColumnCount; i++)
{
//背景颜色
svProject.Cells[0, i].BackColor = Color.LightBlue;
svProject.Cells[1, i].BackColor = Color.LightBlue;
//边框颜色
svProject.Cells[0, i].Border = new FarPoint.Win.LineBorder(Color.Black, 1);
//左右居中
svProject.Cells[0, i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
//上下居中
svProject.Cells[0, i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
//左右居中
svProject.Columns[i].HorizontalAlignment = FarPoint.Win.Spread.CellHorizontalAlignment.Center;
//上下居中
svProject.Columns[i].VerticalAlignment = FarPoint.Win.Spread.CellVerticalAlignment.Center;
//列宽
if (i == 0)
{
svProject.Columns[i].Width = 50;
}
if (i == 3)
{
svProject.Columns[i].Width = 300;
}
if (i == 2 || i == 7)
{
svProject.Columns[i].Width = 150;
}
else
{
svProject.Columns[i].Width = 100;
}
}
return svProject;
}