首页 > 代码库 > DataSet导出Excel
DataSet导出Excel
1、添加引用
2、封装方法
using System; using System.Data; using System.IO; using NPOI.HSSF.UserModel; using NPOI.SS.UserModel; namespace Demo { /// <summary> /// Excel操作类 /// </summary> public class Excel { private readonly HSSFWorkbook workbook = null; private readonly DataSet dataSet = null; private readonly string excelFilename = string.Empty; private readonly ICellStyle cellDateTimeStyle = null; /// <summary> /// Excel操作类构造函数 /// </summary> /// <param name="dataSet"></param> /// <param name="excelFilename"></param> public Excel(DataSet dataSet, string excelFilename) { this.dataSet = dataSet; this.excelFilename = excelFilename; workbook = new HSSFWorkbook(); IDataFormat iDataFormat = workbook.CreateDataFormat(); //设置一个DateTime单元格的样式 cellDateTimeStyle = workbook.CreateCellStyle(); cellDateTimeStyle.DataFormat = iDataFormat.GetFormat("yyyy-m-d h:mm:ss"); } /// <summary> /// 生成excel /// </summary> /// <returns></returns> public bool Write() { bool returns = false; using (FileStream saveFile = new FileStream(excelFilename, FileMode.Create, FileAccess.ReadWrite)) { WriteExcelFile(dataSet); workbook.Write(saveFile); returns = true; } return returns; } private void WriteExcelFile(DataSet ds) { foreach (DataTable dt in ds.Tables) { ISheet sheet = workbook.CreateSheet(dt.TableName); WriteDataTableToExcelWorksheet(dt, sheet); } } private void WriteDataTableToExcelWorksheet(DataTable dt, ISheet sheet) { //生成sheet第一行列名 IRow row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].Caption); } //写入数据 for (int i = 0; i < dt.Rows.Count; i++) { IRow rows = sheet.CreateRow(i + 1); for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = rows.CreateCell(j); var cellValue =http://www.mamicode.com/ dt.Rows[i][j]; if (cellValue =http://www.mamicode.com/= DBNull.Value) { continue; } //类型判断处理 var type = cellValue.GetType(); var typeName = type.Name.ToLower(); switch (typeName) { case "int32": cell.SetCellValue(double.Parse(cellValue.ToString())); break; case "string": cell.SetCellValue(cellValue.ToString()); break; case "datetime": cell.CellStyle = cellDateTimeStyle; cell.SetCellValue(DateTime.Parse(cellValue.ToString())); break; case "guid": cell.SetCellValue(cellValue.ToString()); break; case "boolean": cell.SetCellValue(bool.Parse(cellValue.ToString())); break; default: throw new Exception(string.Format("类型:{0}未做处理,请自行添加", typeName)); } } } } } }
3、实际应用
using System; using System.Data; namespace Demo { class Program { static void Main(string[] args) { DataSet dataSet = new DataSet(); DataTable dataTable = new DataTable(); dataTable.TableName = "Demo"; dataTable.Columns.Add("Id", typeof(int)); dataTable.Columns.Add("名称", typeof(string)); dataTable.Columns.Add("年龄", typeof(int)); dataTable.Columns.Add("添加日期", typeof(DateTime)); for (int i = 0; i < 1000; i++) { dataTable.Rows.Add(i, "名称demo" + i, 18, DateTime.Now); } dataSet.Tables.Add(dataTable); Excel excel = new Excel(dataSet, "Demo.xls"); excel.Write(); } } }
DataSet导出Excel
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。