导出参考调用
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; } } }
