NPOI是一个开源的C#读写Excel、WORD等微软OLE2组件文档的项目。NPOI可以在没有安装Office的情况下对Word或Excel文档进行读写操作
基本步骤
添加引用
可以去官网下载NPOI直接引用。
或者直接VS联网,管理NuGet程序包=>搜索NPOI安装,快速方便
using以下
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
代码如下
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
using System.Reflection;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Web;
namespace NPOIDemo
{
public class NPOIService
{
/// <summary>
/// 将SQLDataReader数据转换成DataTable格式数据
/// </summary>
/// <param name="objReader"></param>
/// <returns></returns>
public static DataTable ReaderToTable(SqlDataReader objReader)
{
DataTable dt = new DataTable();
for (int i = 0; i < objReader.FieldCount; i++)
{
dt.Columns.Add(objReader.GetName(i), objReader.GetFieldType(i));
}
object[] objValues = new object[objReader.FieldCount];
while (objReader.Read())
{
objReader.GetValues(objValues);
dt.LoadDataRow(objValues, true);
}
objReader.Close();
return dt;
}
/// <summary>
/// 将DataTable数据导出成Excel格式
/// </summary>
/// <param name="dt">数据</param>
/// <param name="fileName">文件名</param>
/// <returns></returns>
public static bool TableToExcel(DataTable dt, string fileName)
{
//创建workbook
IWorkbook workbook;
string fileExt = Path.GetExtension(fileName).ToLower();
if (fileExt == ".xlsx")
workbook = new XSSFWorkbook();
else if (fileExt == ".xls")
workbook = new HSSFWorkbook();
else
workbook = null;
//创建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