前端代码:
<table cellpadding="6" cellspacing="0" style="text-align: left; width: 100%; border: none; table-layout: fixed;">
<colgroup>
<col width="90px" />
<col />
</colgroup>
<tr style="border: none;">
</tr>
<tr style="border: none;">
<td align="center" style="text-align: left; border: none; width: 80px; font-size: 13px;">导出文件:
</td>
<td style="border: none;" colspan="2">
<span style="font-size: 12px;">录入归档年度:</span>
<Asp:TextBox ID="txt_Year" runat="server"></Asp:TextBox>
<asp:Button runat="server" Text="导出表格" ID="upLoad" Style="font-size: 13px; font-family: 宋体; font-weight: bold;" OnClick="upLoad_Click"
OnClientClick="return change()" />
<asp:Label ID="LabMess" runat="server" Style="color: #FF0000;" Text=""></asp:Label>
<asp:HiddenField ID="hidkey" runat="server" Value="0" />
</td>
</tr>
</table>
后端代码(运用的是webform点击事件):
在点击事件中创建导出相关代码例如:
/// <summary>
/// 数据导出 使用npoi处理
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
protected void upLoad_Click(object sender, EventArgs e)
{
HSSFWorkbook workbook = new HSSFWorkbook();//创建工作簿
string sql = string.Empty;
if (!string.IsNullOrEmpty(this.txt_Year.Text))//判断年度是否录入
{
sql = @"sql语句可自行配置,不同的年份导出不同数据";//根据年份查询数据信息
DataTable dtTheYear = dao.GetDataTable(sql);
if (dtTheYear.Rows.Count > 0)
{
for (int i = 0; i < dtTheYear.Rows.Count; i++)
{
ExportNpoi(workbook, dtTheYear.Rows[i]["SaveType"].ToString(), dtTheYear.Rows[i]["TheYear"].ToString());
}
}
else
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script type='text/javascript'>alert('没有和该年度相对应的数据信息,无法导出!')</script>");
return;
}
}
else
{
sql = @"归年度为空的时候导出数据库中所有的年份的不同信息";
DataTable dtGui = dao.GetDataTable(sql);
if (dtGui.Rows.Count > 0)
{
for (int i = 0; i < dtGui.Rows.Count; i++)
{
ExportNpoi(workbook, dtGui.Rows[i]["SaveType"].ToString(), dtGui.Rows[i]["TheYear"].ToString());
}
}
else
{
this.Page.ClientScript.RegisterStartupScript(this.GetType(), "", "<script type='text/javascript'>alert('没有该年度的数据信息,无法导出!')</script>");
return;
}
}
ExprotWrite(workbook);
}
**重点在此:**
private void ExportNpoi(HSSFWorkbook workbook, string saveType, string theYear)
{
string sqlYong = @"SELECT BuildNumber AS 件号,PersonLiable AS 责任者,DocumentCode AS 文号,
TitleName AS 题名,
CONVERT(VARCHAR(8),REPLACE(REPLACE(REPLACE(REPLACE(DocumentDT,'-',''),'/',''),'~',''),'.',''),112) AS 日期,PageTotal AS 页数,Remark AS 备注,
CONVERT(VARCHAR(12),AcceptDT,112) AS 收文日期
FROM TDocumentManagement WHERE TheYear = '" + theYear + "' and saveType ='" + saveType + "' and Status ='已归档' ORDER BY CAST(BuildNumber AS INT)";
DataTable dtYong = dao.GetDataTable(sqlYong); //获取数据中的值
if (dtYong.Rows.Count > 0)//判断是否有值
{
ISheet sheet = workbook.CreateSheet(saveType + theYear);//根据不同的年度创建工作表
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);
//合并单元格
CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 7);
sheet.AddMergedRegion(region0);
CellRangeAddress region1 = new CellRangeAddress(1, 1, 0, 2);
sheet.AddMergedRegion(region1);
CellRangeAddress region2 = new CellRangeAddress(1, 1, 4, 7);
sheet.AddMergedRegion(region2);
//创建第一行
ICellStyle HeadercellSt = workbook.CreateCellStyle();
HeadercellSt.Alignment = HorizontalAlignment.CENTER;
HeadercellSt.VerticalAlignment = VerticalAlignment.CENTER;
NPOI.SS.UserModel.IFont headerf = workbook.CreateFont();
headerf.Boldweight = (short)FontBoldWeight.BOLD;
headerf.FontHeight = 350;
HeadercellSt.SetFont(headerf);
IRow DataRowOne = sheet.CreateRow(0);
ICell cellOne = DataRowOne.CreateCell(0);
cellOne.SetCellValue("归档文件目录");
cellOne.CellStyle = HeadercellSt;
//创建第二行
ICellStyle HeadercellS = workbook.CreateCellStyle();
HeadercellS.BorderBottom = NPOI.SS.UserModel.BorderStyle.NONE;
HeadercellS.BorderLeft = NPOI.SS.UserModel.BorderStyle.NONE;
HeadercellS.BorderRight = NPOI.SS.UserModel.BorderStyle.NONE;
HeadercellS.BorderTop = NPOI.SS.UserModel.BorderStyle.NONE;
HeadercellS.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
//字体
NPOI.SS.UserModel.IFont headers = workbook.CreateFont();
headers.Boldweight = (short)FontBoldWeight.BOLD;
headers.FontHeight = 250;
HeadercellS.SetFont(headers);
IRow DataRowTwo = sheet.CreateRow(1);
ICell cellTwo0 = DataRowTwo.CreateCell(0);
cellTwo0.SetCellValue("归档年度:" + theYear);
cellTwo0.CellStyle = HeadercellS;
ICell cellTwo1 = DataRowTwo.CreateCell(4);
cellTwo1.SetCellValue("保管期限:" + saveType + "");
//((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region1, NPOI.SS.UserModel.BorderStyle.NONE, NPOI.HSSF.Util.HSSFColor.RED.index);
cellTwo1.CellStyle = HeadercellS;
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(2);
foreach (DataColumn item in dtYong.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被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;
cellStyle.WrapText = true;
IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.NORMAL;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 3;
int iCellIndex = 0;
foreach (DataRow Rowitem in dtYong.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dtYong.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex + 2; i++)
{
if (i == 1 || i == 2 || i == 6)
{
sheet.SetColumnWidth(i, 5500);
}
else
{
sheet.SetColumnWidth(i, 2650);
}
}
for (int i = 0; i <= dtYong.Rows.Count + 2; i++)
{
IRow currentRow = sheet.GetRow(i);
ICell currentCell = currentRow.GetCell(20);
//int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
currentRow.HeightInPoints = 20 * (60 / 60 + 1);
}
}
}
/// <summary>
/// 向客户端输出文件
/// </summary>
/// <param name="workbook"></param>
private void ExprotWrite(HSSFWorkbook workbook)
{
HttpContext context = HttpContext.Current;
context.Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode("目录" + this.txt_Year.Text + ".xls")); ;
context.Response.Clear();
context.Response.BinaryWrite(WriteToStream(workbook).GetBuffer());
context.Response.End();
}
private static MemoryStream WriteToStream(HSSFWorkbook hssfworkbook)
{
//Write the stream data of workbook to the root directory
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
return file;
}
**
表达略显拙略忘见谅!
**