asp.net c# Excel导出可分页

引入 using ClosedXML.Excel;

 

表头

/// <summary>
/// 表头1
/// </summary>
private static string[] strArrPurHead = { "字段1", "字段2", "字段3", "字段4", "字段5", "字段6", "字段7", "字段8", "字段9" };

/// <summary>
/// 表头2
/// </summary>
private static string[] strArrInfoHead = { "字段1", "字段2", "字段3", "字段4", "字段5", "字段6", "字段7", "字段8", "字段9" };

/// <summary>
/// 表头3
/// </summary>
private static string[] strArrTariffHead = { "字段1", "字段2", "字段3", "字段4", "字段5", "字段6", "字段7", "字段8", "字段9" };

/// <summary>
/// 表头4
/// </summary>
private static string[] strArrAdvanceBill = { "字段1", "字段2", "字段3", "字段4", "字段5", "字段6", "字段7", "字段8", "字段9" };

/// <summary>
/// 表头5
/// </summary>
private static string[] strArrCancellation = { "字段1", "字段2", "字段3", "字段4", "字段5", "字段6", "字段7", "字段8", "字段9" };

 

导出按钮点击事件

protected void BtnAllExport_Click(object sender, EventArgs e)
{
string tStart = StartDate.Text.Trim();
string tEnd = EndDate.Text.Trim();
tEnd = Convert.ToDateTime(tEnd).AddDays(1).ToString("yyyy/MM/dd");
string[] strArrayAlf = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };
var wb = new XLWorkbook();
int flag = 0;
for (int n = 0; n < CB_ClosingInfSelect.Items.Count; n++)
{
if (CB_ClosingInfSelect.Items[n].Selected)
{
short sn = Convert.ToInt16(CB_ClosingInfSelect.Items[n].Value);
switch (sn)
{
case 1:
DataTable dt1 = GetCaterPurInfo(tStart, tEnd);
if (dt1 == null || dt1.Rows.Count == 0) break;
PrintTable(strArrayAlf, strArrPurHead, "sheetName", dt1, tStart, tEnd, wb);
flag++;
break;
case 2:
DataTable dt2 = GetInfoPurInfo(tStart, tEnd);
if (dt2 == null || dt2.Rows.Count == 0) break;
PrintTable(strArrayAlf, strArrInfoHead, "sheetName", dt2, tStart, tEnd, wb);
flag++;
break;
case 3:
DataTable dt3 = GetTariffPurInfo(tStart, tEnd);
if (dt3 == null || dt3.Rows.Count == 0) break;
PrintTable(strArrayAlf, strArrTariffHead, "sheetName", dt3, tStart, tEnd, wb);
flag++;
break;
case 4:
DataTable dt4 = GetAdvanceSalesInfo(tStart, tEnd);
if (dt4 == null || dt4.Rows.Count == 0) break;
PrintTable(strArrayAlf, strArrAdvanceBill, "sheetName", dt4, tStart, tEnd, wb);
flag++;
break;
case 5:
DataTable dt5 = GetCancellationSalesInfo(tStart, tEnd);
if (dt5 == null || dt5.Rows.Count == 0) break;
PrintTable(strArrayAlf, strArrCancellation, "sheetName", dt5, tStart, tEnd, wb);
flag++;
break;
}
}
}
if (flag > 0) {
byte[] FileBytes = null;
string FilePath = HttpContext.Current.Server.MapPath("Temp.xlsx");
wb.SaveAs(FilePath);
FileBytes = System.IO.File.ReadAllBytes(FilePath);
System.IO.File.Delete(FilePath);
Response.ContentType = "application/excel";
Response.AddHeader("content-disposition", "attachment; filename=" + HttpUtility.UrlEncode(DateTime.Today.Year + "年" + DateTime.Today.Month + "月" + DateTime.Today.Day + "-表单.xlsx", System.Text.Encoding.UTF8));
Response.AddHeader("content-length", FileBytes.Length.ToString());
Response.BinaryWrite(FileBytes);
Response.End();
}
}

 

//打印表格

private void PrintTable(string[] strArrayAlf, string[] strArrHead, string strTitle, DataTable dt, string tStart, string tEnd, XLWorkbook wb)
{
tStart = Convert.ToDateTime(tStart).ToString("yyyy年MM月dd日");
tEnd = Convert.ToDateTime(tEnd).AddDays(-1).ToString("yyyy年MM月dd日");
var ws = wb.Worksheets.Add(strTitle);
int StoreInCols, StoreInRows;
StoreInRows = dt.Rows.Count;
if (StoreInRows > 0)
{
StoreInCols = dt.Columns.Count;
if (StoreInCols < 27)
{
ws.Cell("A1").Value = strTitle + "(" + tStart + "-" + tEnd + ")";
for (int i = 0; i < StoreInCols; i++)
{
ws.Cell(strArrayAlf[i] + 2).Value = dt.Columns[i].ColumnName;
}
for (int i = 0; i < StoreInRows; i++)
{
for (int j = 0; j < StoreInCols; j++)
{
ws.Cell(strArrayAlf[j] + (i + 3)).Value = dt.Rows[i][strArrHead[j]];
}
}
}

int cols = dt.Columns.Count;
string strExcelColumnEnd = strArrayAlf[cols - 1] + (StoreInRows + 2);
var rngTable = ws.Range("A1:" + strExcelColumnEnd);
//头
var rngHeaders = rngTable.Range("A2:" + (strArrayAlf[cols - 1] + 2));
rngHeaders.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
rngHeaders.Style.Font.Bold = true;
rngHeaders.Style.Fill.BackgroundColor = XLColor.Aqua;

//Adding grid lines
rngTable.Style.Border.BottomBorder = XLBorderStyleValues.Thin;

//Format title cell
rngTable.Cell(1, 1).Style.Font.Bold = true;
rngTable.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.CornflowerBlue;
rngTable.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;

//Merge title cells
rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge()

//Add thick borders
rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thick;

//Adjust column widths to their content
ws.Columns(1, cols).AdjustToContents();
}
}