| using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.SqlClient; using System.Text; using System.IO; using NPOI; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using NPOI.XSSF.UserModel; namespace NPOI_Test1 { public partial class Page1 : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnExport_Click(object sender, EventArgs e) { SqlConnection cn = new SqlConnection(); cn.ConnectionString = "server=.;uid=sa;pwd=密碼不告訴你;database=dawufan"; cn.Open(); string sqlstr = @"select * from interest"; SqlCommand cmd = new SqlCommand(); cmd.Connection = cn; cmd.CommandText = sqlstr; SqlDataReader reader = cmd.ExecuteReader(); DataTable dt = ReaderToTable(reader); ExportExcel(dt); cn.Close(); cn.Dispose(); cmd.Dispose(); reader.Close(); dt.Dispose(); } protected DataTable ReaderToTable(SqlDataReader dr) { DataTable dt = new DataTable(); for (int i = 0; i < dr.FieldCount; i++) { dt.Columns.Add(dr.GetName(i), dr.GetFieldType(i)); } object[] objValues = new object[dr.FieldCount]; while (dr.Read()) { dr.GetValues(objValues); dt.LoadDataRow(objValues, true); } dr.Close(); return dt; } protected void ExportExcel(DataTable dt) { HttpContext curContext = HttpContext.Current; //設(shè)置編碼及附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; string fullName = HttpUtility.UrlEncode("FileName.xls", Encoding.UTF8); curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fullName, Encoding.UTF8)); //attachment后面是分號(hào) byte[] data = TableToExcel(dt, fullName).GetBuffer(); curContext.Response.BinaryWrite(TableToExcel(dt, fullName).GetBuffer()); curContext.Response.End(); } public MemoryStream TableToExcel(DataTable dt, string file) { //創(chuàng)建workbook IWorkbook workbook; string fileExt = Path.GetExtension(file).ToLower(); if (fileExt == ".xlsx") workbook = new XSSFWorkbook(); else if (fileExt == ".xls") workbook = new HSSFWorkbook(); else workbook = null; //創(chuàng)建sheet ISheet sheet = workbook.CreateSheet("Sheet1"); //表頭 IRow headrow = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell headcell = headrow.CreateCell(i); headcell.SetCellValue(dt.Columns[i].ColumnName); } //表內(nèi)數(shù)據(jù) for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row.CreateCell(j); cell.SetCellValue(dt.Rows[i][j].ToString()); } } //轉(zhuǎn)化為字節(jié)數(shù)組 MemoryStream ms = new MemoryStream(); workbook.Write(ms); ms.Flush(); ms.Position = 0; return ms; } } } |
|
|
