以下DataTable转Excel为引用他人页面，https://jingyan.baidu.com/article/fedf0737a0f54335ac8977b8.html
其余类型转Excel为自己所需要而进行更改的。
此篇文章为记录该功能编写，如果该断代码，对各位有用，各位在使用时，所遇到的问题，所采用的解决方法希望能贴以下，万一我也遇到别的问题好借鉴各位大佬的。
/// <summary>
/// 导出设备造册管理列表
/// </summary>
/// <param name="devSearch"></param>
/// <returns></returns>
[AcceptVerbs("Get", "Post")]
public string ExportDevComList(DevSearch devSearch)
{
int RecordCount = 0;
var pageIndex = 1;
var pageSize =99999;
int dataCount = 0;
devSearch.orgNo = devSearch.orgNo.TrimEnd(‘0‘);
if (devSearch.Scence == "-1")
{
devSearch.Scence = "";
}
if (devSearch.DevName == null)
{
devSearch.DevName = "";
}
if (devSearch.GBID == null)
{
devSearch.GBID = "";
}
List<SPJKSB> DevList = new List<SPJKSB>();
DevList = BizContext.GetSqlProcedure<SPJKSB>("DS_GetDevToComList"
, new
{
orgNo = devSearch.orgNo,
Scence = devSearch.Scence,
DevName = devSearch.DevName,
GBID = devSearch.GBID,
comed = devSearch.comed,
pageIndex = pageIndex,
pageSize = pageSize
}, VideoConnectionKind.Reader, (o) => { o.Add("RecordCount", System.Data.DbType.Int32, System.Data.ParameterDirection.Output, 1024); }
, (r) => { dataCount = r.Get<int>("RecordCount"); });
// string fileFullPath = "/Excel";
//以上内容为获取数据，无借鉴意义。以下开始进行导出数据工作
List<Dictionary<string, object>> ListDic = new List<Dictionary<string, object>>();
foreach (SPJKSB item in DevList)
{
Dictionary<string, object> dic = new Dictionary<string, object>();
dic.Add("*机关",item.SBAZSZDW_GAJGMC);
dic.Add("设备名称",item.SBMC);
dic.Add("国标编码",item.GBID);
dic.Add("场所名称",item.ScenceName);
string StateStr = "";
if (item.DomainID == "0")
{
StateStr= "未在册";
}
else
{
StateStr= "在 册";
}
dic.Add("状态", StateStr);
ListDic.Add(dic);
}
IWorkbook workbook = new XSSFWorkbook();//当引入NPOI时，未引用ICSharpCode.SharpZipLib时，这里可能异常，如果再NuGet包安装失败时，可先移除NPOI相关引用，删除packages包里面ICSharpCode文件夹和NPOI文件夹，先安装ICSharpCode.SharpZipLib，再安装NPOI
var sheetName = System.DateTime.Now.ToString("yyyyMMddhhmmss");//获取年月日时分秒作为文件存储名称
var fileFullPath = AppDomain.CurrentDomain.BaseDirectory + "Excel";//获取所在绝对目录
if (!Directory.Exists(fileFullPath)){//如果不存在就创建file文件夹
Directory.CreateDirectory(fileFullPath);//创建该文件夹
}
workbook = DictionaryToExcel(ListDic, workbook, "设备造册列表");
DirectoryInfo folder = new DirectoryInfo(fileFullPath);
//删除文件夹下面昨天保存的excel文件
foreach (FileInfo file in folder.GetFiles("*.xlsx"))
{
DateTime dt = file.CreationTime;
//dt < DateTime.Today.AddDays(1) 我做实验时所用的条件
if (dt < DateTime.Today)
{
try
{
File.Delete(file.FullName);
}
catch { }
}
}
using (FileStream fs = System.IO.File.Create(fileFullPath +"\\"+ sheetName + ".xlsx"))
{
workbook.Write(fs);
}
return "Excel\\" + sheetName + ".xlsx";
}
/// <summary>
/// 数据转Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="workbook"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static IWorkbook DataTableToExcel(DataTable dt, IWorkbook workbook, string sheetName)
{
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellstyle.SetFont(font);
for (int j = 0; j < dt.Columns.Count; j++)
{
string columnName = dt.Columns[j].ColumnName;
ICell cellColumn = headerRow.CreateCell(j);
cellColumn.SetCellValue(columnName);
cellColumn.CellStyle = cellstyle;
int length = Encoding.UTF8.GetBytes(columnName).Length;
sheet.SetColumnWidth(j, (length + 1) * 256);
}
ICellStyle cellstyle1 = workbook.CreateCellStyle();
cellstyle1.BorderBottom = BorderStyle.Thin;
cellstyle1.BorderLeft = BorderStyle.Thin;
cellstyle1.BorderRight = BorderStyle.Thin;
cellstyle1.BorderTop = BorderStyle.Thin;
cellstyle1.VerticalAlignment = VerticalAlignment.Center;
cellstyle1.Alignment = HorizontalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 12;
font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
cellstyle1.SetFont(font1);
for (int a = 0; a < dt.Rows.Count; a++)
{
DataRow dr = dt.Rows[a];
IRow row = sheet.CreateRow(a + 1);
for (int b = 0; b < dt.Columns.Count; b++)
{
ICell cellRow = row.CreateCell(b);
string str = dr[b] != DBNull.Value ? dr[b].ToString() : string.Empty;
//因导出格式问题，不再转换 updated by dhp at 2018.12.20
//double db = 0;
//if (!str.Contains(",") && double.TryParse(str, out db)) cellRow.SetCellValue(db);
//else cellRow.SetCellValue(str);
cellRow.SetCellValue(str);
cellRow.CellStyle = cellstyle1;
int columnWidth = sheet.GetColumnWidth(b) / 256;
int length = Encoding.UTF8.GetBytes(str).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}
if (columnWidth > 255) columnWidth = 255;
sheet.SetColumnWidth(b, columnWidth * 256);
}
}
sheet.CreateFreezePane(0, 1, 0, 1);
return workbook;
}
/// <summary>
/// 实体类数据转Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="dt"></param>
/// <param name="workbook"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static IWorkbook EntityToExcel<T>(List <T> dt, IWorkbook workbook, string sheetName)
{
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellstyle.SetFont(font);
PropertyInfo[] properties = dt[0].GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
for (int j = 0; j < properties.Length; j++)
{
string columnName = properties[j].Name;//获取实体类字段名称
ICell cellColumn = headerRow.CreateCell(j);
cellColumn.SetCellValue(columnName);
cellColumn.CellStyle = cellstyle;
int length = Encoding.UTF8.GetBytes(columnName).Length;
sheet.SetColumnWidth(j, (length + 1) * 256);
}
ICellStyle cellstyle1 = workbook.CreateCellStyle();
cellstyle1.BorderBottom = BorderStyle.Thin;
cellstyle1.BorderLeft = BorderStyle.Thin;
cellstyle1.BorderRight = BorderStyle.Thin;
cellstyle1.BorderTop = BorderStyle.Thin;
cellstyle1.VerticalAlignment = VerticalAlignment.Center;
cellstyle1.Alignment = HorizontalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 12;
font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
cellstyle1.SetFont(font1);
for (int i = 0; i < dt.Count(); i++)
{
IRow row = sheet.CreateRow(i + 1);
PropertyInfo[] propertiesi = dt[i].GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);
int b = 0;
foreach (PropertyInfo item in propertiesi)
{
ICell cellRow = row.CreateCell(b);
string str = item.GetValue(dt[i]) == null ? "" : item.GetValue(dt[i]).ToString();//获取字段的值
cellRow.SetCellValue(str);
cellRow.CellStyle = cellstyle1;
int columnWidth = sheet.GetColumnWidth(b) / 256;
int length = Encoding.UTF8.GetBytes(str).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}
if (columnWidth > 255) columnWidth = 255;
sheet.SetColumnWidth(b, columnWidth * 256);
b = b + 1;
}
}
sheet.CreateFreezePane(0, 1, 0, 1);
return workbook;
}
/// <summary>
/// 字典集合数据转Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="workbook"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static IWorkbook DictionaryToExcel(List<Dictionary<string,object>> dt, IWorkbook workbook, string sheetName)
{
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow(0);
ICellStyle cellstyle = workbook.CreateCellStyle();
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
cellstyle.SetFont(font);
//编写表头，便利Dictionary集合中第一个的字段，
for (int j = 0; j < dt[0].Count(); j++)
{
string columnName = dt[0].ToList()[j].Key;
ICell cellColumn = headerRow.CreateCell(j);//创建列
cellColumn.SetCellValue(columnName);//设值
cellColumn.CellStyle = cellstyle;//设置样式，
int length = Encoding.UTF8.GetBytes(columnName).Length;//字符长度，用于设置列宽
sheet.SetColumnWidth(j, (length + 1) * 256);//设置列宽
}
ICellStyle cellstyle1 = workbook.CreateCellStyle();
cellstyle1.BorderBottom = BorderStyle.Thin;
cellstyle1.BorderLeft = BorderStyle.Thin;
cellstyle1.BorderRight = BorderStyle.Thin;
cellstyle1.BorderTop = BorderStyle.Thin;
cellstyle1.VerticalAlignment = VerticalAlignment.Center;
cellstyle1.Alignment = HorizontalAlignment.Center;
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 12;
font1.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Normal;
cellstyle1.SetFont(font1);
for (int i = 0; i < dt.Count(); i++)
{
IRow row = sheet.CreateRow(i + 1);
int b = 0;
foreach (var item in dt[i].ToList())
{
ICell cellRow = row.CreateCell(b);
string str = item.Value== null ? "" : item.Value.ToString();
cellRow.SetCellValue(str);
cellRow.CellStyle = cellstyle1;
int columnWidth = sheet.GetColumnWidth(b) / 256;
int length = Encoding.UTF8.GetBytes(str).Length;//获取当前单元格的内容宽度
if (columnWidth < length + 1)
{
columnWidth = length + 1;
}
if (columnWidth > 255) columnWidth = 255;
sheet.SetColumnWidth(b, columnWidth * 256);
b = b + 1;
}
}
sheet.CreateFreezePane(0, 1, 0, 1);
return workbook;
}