一、需要导入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(); for (var i = ; i < title.Count; i++)
{
rowtitle.CreateCell(i).SetCellValue(title[i]);
} for (var i = ; i < data.Count; i++)
{
var row = sheet.CreateRow(i + ); //因为表头名称占了一行，所以加1
for (var j = ; 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 = ;
IRow headerRow = sheet.CreateRow();
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 = ;
int iCellIndex = ;
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 = ;
iRowIndex++;
} //自适应列宽度
for (int i = ; 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;