使用NPOI.dll组件来导出Excel文件，并设置样式，Nuget引用即可。
packages\NPOI.2.1.3.1\lib\net20\NPOI.dll
#region Excel
protected Stream DataTable2Excel(DataView view, Dictionary<string, string> titles = null)
{
List<DataColumn> cols = new List<DataColumn>();
if (titles != null)
{
foreach (var item in titles)
{
if (view.Table.Columns.Contains(item.Key))
{
var col = view.Table.Columns[item.Key];
col.Caption = item.Value;
cols.Add(col);
}
}
}
else
{
foreach (DataColumn item in view.Table.Columns)
{
item.Caption = item.ColumnName;
cols.Add(item);
}
}
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet(SheetName);
IRow headerRow = sheet.CreateRow(0);
ICellStyle cellstyle = Getcellstyle(workbook, stylexls.头);
ICellStyle intstyle = Getcellstyle(workbook, stylexls.数字);
for (int i = 0; i < cols.Count; i++)
{
var column = cols[i];
headerRow.CreateCell(i).SetCellValue(column.Caption);
headerRow.Cells[i].CellStyle = cellstyle;
}
int rowIndex = 1;
foreach (DataRowView row in view)
{
IRow dataRow = sheet.CreateRow(rowIndex);
int columnindex = 0;
foreach (DataColumn col in cols)
{
switch (col.DataType.Name)
{
case "DateTime":
dataRow.CreateCell(columnindex).SetCellValue((Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"));
break;
case "String":
dataRow.CreateCell(columnindex).SetCellValue(row[col.ColumnName].ToString());
break;
case "Int16":
case "Int64":
case "Decimal":
case "Int32":
ICell cell = dataRow.CreateCell(columnindex);
cell.SetCellValue(((int)row[col.ColumnName]).ToString("N0"));
cell.CellStyle = intstyle;
break;
default:
dataRow.CreateCell(columnindex).SetCellValue(row[col.ColumnName].ToString());
break;
}
columnindex++;
}
rowIndex++;
}
for (int i = 0; i < cols.Count; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return ms;
}
#region 定义单元格常用到样式
#region 定义单元格常用到样式的枚举
public enum stylexls
{
头,
url,
时间,
数字,
钱,
百分比,
中文大写,
科学计数法,
默认,
千分位
}
#endregion
protected static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont header = wb.CreateFont();
header.FontHeightInPoints = 10;
header.FontName = "微软雅黑";
header.Boldweight = (short)FontBoldWeight.Bold;
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
//cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.DOTTED;
//cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.HAIR;
//cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.HAIR;
//cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.DOTTED;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Blue.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Blue.Index;
//背景图形
//cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
cellStyle.FillForegroundColor = HSSFColor.White.Index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.Maroon.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;
cellStyle.Indention = 0;
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.SetFont(header);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
//cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.千分位:
IDataFormat format2 = wb.CreateDataFormat();
cellStyle.DataFormat = format2.GetFormat("#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.Single;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
}
return cellStyle;
}
#endregion
#endregion
使用EPPlus.dll来导出Excel, Nuget引用即可。可在Excel文件加密
packages\EPPlus.3.1.3.3\lib\net35\EPPlus.dll
#region Excel Encrypt
/// <summary>
/// 转换成带有密码的Excel文件。2007格式
/// </summary>
/// <param name="view"></param>
/// <param name="titles"></param>
/// <param name="passWord"></param>
/// <returns></returns>
protected Stream DataTable2Excel(DataView view, string passWord, Dictionary<string, string> titles = null)
{
List<DataColumn> cols = new List<DataColumn>();
if (titles != null)
{
foreach (var item in titles)
{
if (view.Table.Columns.Contains(item.Key))
{
var col = view.Table.Columns[item.Key];
col.Caption = item.Value;
cols.Add(col);
}
}
}
else
{
foreach (DataColumn item in view.Table.Columns)
{
item.Caption = item.ColumnName;
cols.Add(item);
}
}
MemoryStream stream = new MemoryStream();
using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage())
{
var rowIndex = 1;
OfficeOpenXml.ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(SheetName);
//写标题行
for (int i = 0; i < cols.Count; i++)
{
var column = cols[i];
var cell = worksheet.Cells[rowIndex, i + 1];
cell.Style.Font.Bold = true;
cell.Style.Font.Name = "微软雅黑";
cell.Style.Font.Size = 10;
cell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;
cell.Value = column.Caption;
}
rowIndex++;
foreach (DataRowView row in view)
{
int columnindex = 1;
foreach (DataColumn col in cols)
{
var cell = worksheet.Cells[rowIndex, columnindex];
switch (col.DataType.Name)
{
case "DateTime":
cell.Value = Convert.ToDateTime(row[col.ColumnName]).ToString("yyyy-MM-dd");
cell.Style.Numberformat.Format = "yyyy-mm-dd";
break;
case "String":
cell.Value = row[col.ColumnName].ToString();
break;
case "Int16":
case "Int64":
case "Decimal":
case "Int32":
cell.Value = (int)row[col.ColumnName];
cell.Style.Numberformat.Format = "0_);[Red](0)";
cell.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Right;
break;
default:
cell.Value = row[col.ColumnName].ToString();
break;
}
worksheet.Column(columnindex).AutoFit();
columnindex++;
}
rowIndex++;
}
package.SaveAs(stream, passWord);
stream.Flush();
stream.Position = 0;
}
return stream;
}
#endregion
