本事例只演示导入
导出参考调用
using System;
using System.Collections.Generic;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace NpoiTest
{
class Program
{
static void Main(string[] args)
{
string path = "D:\\";
string xlsPath = Path.Combine(path, DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");;//excel的保存路径
NpoiExcelUtility utility = new NpoiExcelUtility(xlsPath);
#region MyRegion
DataTable dt = new DataTable("NewDt");
//创建自增长的ID列
DataColumn dc = dt.Columns.Add("ID", Type.GetType("System.Int32"));
dc.AutoIncrement = true; //自动增加
dc.AutoIncrementSeed = 1; //起始为1
dc.AutoIncrementStep = 1; //步长为1
dc.AllowDBNull = false; //非空
//创建其它列表
dt.Columns.Add(new DataColumn("Name", Type.GetType("System.String")));
dt.Columns.Add(new DataColumn("Age", Type.GetType("System.Int32")));
dt.Columns.Add(new DataColumn("Score", Type.GetType("System.Decimal")));
dt.Columns.Add(new DataColumn("CreateTime", Type.GetType("System.DateTime")));
//创建数据
DataRow dr = dt.NewRow();
dr["Name"] = "张三";
dr["Age"] = 28;
dr["Score"] = 85.5;
dr["CreateTime"] = DateTime.Now;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["Name"] = "李四";
dr["Age"] = 24;
dr["Score"] = 72;
dr["CreateTime"] = DateTime.Now;
dt.Rows.Add(dr);
dr = dt.NewRow();
dr["Name"] = "王五";
dr["Age"] = 36;
dr["Score"] = 63.5;
dr["CreateTime"] = DateTime.Now;
dt.Rows.Add(dr);
#endregion
if (dt != null)
{
utility.CreatExcelSheet("基本信息表", dt);
utility.SaveExcel();
Process.Start(xlsPath);//导入完毕后直接自动启动exce打开生成的excel文件
}
}
}
}
excel帮助类
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace NpoiTest
{
/// <summary>
/// 写Excel的实用类，NPOI 是POI项目的.NET 版本，是一个开源的用来读写Excel、WORD等微软OLE2组件文档的项目，不依赖于office，如果用Microsoft.Office.Interop.Excel，容易出错，尤其是安装了不同版本的office软件
/// </summary>
public class NpoiExcelUtility
{
private string _xlsPath = string.Empty;
private HSSFWorkbook _workBook = null;
/// <summary>
/// 构造函数
/// </summary>
/// <param name="xlsPath">xls保存路径</param>
public NpoiExcelUtility(string xlsPath)
{
_xlsPath = this.CheckFilePath(xlsPath);
_workBook = new HSSFWorkbook();
}
/// <summary>
/// 将DataTable保存到sheet里
/// </summary>
/// <param name="dt"></param>
/// <param name="sheet"></param>
private void DataTableToExcel(DataTable dt, ISheet sheet)
{
ICellStyle style = _workBook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;
style.VerticalAlignment = VerticalAlignment.Center;
ICellStyle colStyle = _workBook.CreateCellStyle();
colStyle.Alignment = HorizontalAlignment.Left;
colStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = _workBook.CreateFont();
font.Color = NPOI.HSSF.Util.HSSFColor.LightBlue.COLOR_NORMAL;
colStyle.SetFont(font);
//列名
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
sheet.SetDefaultColumnStyle(i, style);
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ToString());
cell.CellStyle = colStyle;
}
//内容
for (int i = 1; i <= dt.Rows.Count; i++)
{
row = sheet.CreateRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
object obj = dt.Rows[i - 1][j];
if (obj != null)
{
ICell cell = row.CreateCell(j);
if (obj is double || obj is float || obj is int || obj is long || obj is decimal)
{
cell.SetCellValue(Convert.ToDouble(obj));
}
else if (obj is bool)
{
cell.SetCellValue((bool)obj);
}
else
{
cell.SetCellValue(obj.ToString());
}
}
}
}
//一下方法会报异常，可能是改NPOI版本的问题，之前老的版本不会报错，这里暂时注释掉
//for (int i = 0; i < dt.Columns.Count; i++)
//{
// sheet.AutoSizeColumn(i);
//}
}
/// <summary>
/// 保存Excel
/// </summary>
public void SaveExcel()
{
FileStream file = new FileStream(_xlsPath, FileMode.Create);
_workBook.Write(file);
file.Close();
}
/// <summary>
/// 创建Sheet
/// </summary>
/// <param name="sheetName">sheet名称</param>
/// <param name="tbl">DataTable数据表，当行数大于65536时，自动分割成几个sheet，sheet名称为sheetName_i</param>
public void CreatExcelSheet(string sheetName, DataTable tbl)
{
string sName = this.CheckSheetName(sheetName);
int rowMax = 65535;
int intNum = tbl.Rows.Count / rowMax;
int remainder = tbl.Rows.Count % rowMax;
for (int i = 0; i < intNum; i++)
{
DataTable subTbl = tbl.Clone();
for (int j = 0; j < 65535; j++)
{
int rowIndex = i * rowMax + j;
subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
}
string subSheet = sName + "_" + (i + 1);
ISheet sheet = _workBook.CreateSheet(subSheet);
this.DataTableToExcel(subTbl, sheet);
}
if (remainder > 0)
{
DataTable subTbl = tbl.Clone();
for (int j = 0; j < remainder; j++)
{
int rowIndex = intNum * rowMax + j;
subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
}
string subSheet = sName + "_" + (intNum + 1);
if (intNum < 1)
{
subSheet = sName;
}
ISheet sheet = _workBook.CreateSheet(subSheet);
this.DataTableToExcel(subTbl, sheet);
}
}
/// <summary>
/// 检查sheet名称是否合法，并去掉不合法字符
/// </summary>
/// <param name="sheetName"></param>
private string CheckSheetName(string sheetName)
{
string rlt = sheetName;
string[] illegalChars = { "*", "?", "\"", @"\", "/" };
for (int i = 0; i < illegalChars.Length; i++)
{
rlt = rlt.Replace(illegalChars[i], "");
}
return rlt;
}
/// <summary>
/// 检查xls路径是否合法，并去掉不合法字符
/// </summary>
/// <param name="filePath"></param>
private string CheckFilePath(string filePath)
{
string dir = Path.GetDirectoryName(filePath);
string fileName = Path.GetFileNameWithoutExtension(filePath);
string ext = Path.GetExtension(filePath);
string[] illegalChars = { ":", "*", "?", "\"", "<", ">", "|", @"\", "/" };
for (int i = 0; i < illegalChars.Length; i++)
{
fileName = fileName.Replace(illegalChars[i], "");
}
string rlt = Path.Combine(dir, fileName + ext);
return rlt;
}
}
}
