一、需要导入NPOI 库文件
打开VS2012 工具》》库程序包管理器》》管理解决方案的NuGet程序包，搜索NPOI，如下图
安装完成；
添加
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
方法一： LIst到处Excel文件
public void ListToExcelByNPOI(List<T>data) { string pasthname = "结果-" + DateTime.Now.ToString("yyyy-MM-dd") + "导出" + ".xls"; HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); //获取公共属性由于做表头 var propertys = typeof(UploadTestResultInfos).GetProperties(); List<string> title = new List<string>(); foreach (PropertyInfo item in typeof(UploadTestResultInfos).GetProperties()) { //if (!Ignore.IgnoreField(item.Name)) // continue; title.Add(item.Name); } var rowtitle = sheet.CreateRow(0); for (var i = 0; i < title.Count; i++) { rowtitle.CreateCell(i).SetCellValue(title[i]); } for (var i = 0; i < data.Count; i++) { var row = sheet.CreateRow(i + 1); //因为表头名称占了一行，所以加1 for (var j = 0; j < propertys.Length; j++) { //if (!Ignore.IgnoreField(propertys[j].Name)) // continue; var obj = propertys[j].GetValue(data[i], null); row.CreateCell(j).SetCellValue(obj.ToString().Trim()); } } using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); //Web导出 HttpContext curContext = HttpContext.Current; curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(pasthname, Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); curContext.Response.End(); } }
方法二：Datatable 到处Excel表
private static void TableToExcelByNPOI(DataTable dt) { string strExcelFileName = "MCS测试结果-" + DateTime.Now.ToString("yyyy-MM-dd") + "导出" + ".xls"; try { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet("Sheet1"); 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); //用column name 作为列名 int icolIndex = 0; IRow headerRow = sheet.CreateRow(0); foreach (DataColumn item in dt.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; NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont(); cellfont.Boldweight = (short)FontBoldWeight.Normal; cellStyle.SetFont(cellfont); //建立内容行 int iRowIndex = 1; int iCellIndex = 0; foreach (DataRow Rowitem in dt.Rows) { IRow DataRow = sheet.CreateRow(iRowIndex); foreach (DataColumn Colitem in dt.Columns) { ICell cell = DataRow.CreateCell(iCellIndex); cell.SetCellValue(Rowitem[Colitem].ToString()); cell.CellStyle = cellStyle; iCellIndex++; } iCellIndex = 0; iRowIndex++; } //自适应列宽度 for (int i = 0; i < icolIndex; i++) { sheet.AutoSizeColumn(i); } //写Excel //FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate); //workbook.Write(file); //file.Flush(); //file.Close(); using (MemoryStream ms = new MemoryStream()) { workbook.Write(ms); //Web导出 HttpContext curContext = HttpContext.Current; curContext.Response.BufferOutput = true; curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strExcelFileName, Encoding.UTF8)); curContext.Response.BinaryWrite(ms.GetBuffer()); curContext.Response.End(); //StringWriter sw = new StringWriter(); //System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(); } //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); } catch (Exception ex) { //ILog log = LogManager.GetLogger("Exception Log"); //log.Error(ex.Message + Environment.NewLine + ex.StackTrace); ////记录AuditTrail //CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex); //MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information); } finally { } }
另外导出方式使用using System.Data.OleDb;
类似操作数据库操作EXCEL文件
DataTable data = new DataTable();
OleDbConnection connection = null;
string strConn = null;
if (filePath.IndexOf(".xlsx") > 0 || filePath.IndexOf(".XLSX") > 0) // 2007版本
{
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1;'";
}
else if (filePath.IndexOf(".xls") > 0 || filePath.IndexOf(".XLS") > 0) // 2003版本
{
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=No;IMEX=1;'";
}
connection = new OleDbConnection(strConn);
connection.Open();
//获取Excel中所有Sheet表的信息
DataTable schemaTable = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
//获取Excel的第一个Sheet表名
string tableName = schemaTable.Rows[0][2].ToString().Trim();
string strSql = "select * from [" + tableName + "]";
OleDbDataAdapter myData = new OleDbDataAdapter(strSql, connection);
myData.Fill(data);
connection.Close();
return data;