控件bin文件下载地址：https://download.csdn.net/download/u012949335/10610726
@{
ViewBag.Title = "dcxx";
}
<script type="text/javascript" language="javascript">
function excelport()
{
var checktag=$("#ischecked:checked");
var form=$("<form action=\"dcxx\" method=\"post\"></form>");
$(document.body).append(form);
form.submit();
}
</script>
<button type="button" class="but-primary" onclick="excelport()"> 导出数据</button>
//调用方法
public ActionResult dcxx()
{
DataTable dt = dal.getlist(" 1=1 ");
NpoiWorkbook book = new NpoiWorkbook();
book.CreateSheet(dt, "xxx信息");
Stream excelstream = book.GetMemoryStream();
return File(excelstream, "application/octet-stream", "xx信息.xls");
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
namespace YidiTutor.Common.NOPI
{
/// <summary>
/// 工作薄
/// </summary>
public class NpoiWorkbook
{
public HSSFWorkbook workbook;
public XSSFWorkbook workbook2007;
public bool isExcel2007 = false;//生成excel2007
/// <summary>
/// 表头格式
/// </summary>
private ICellStyle HeadStyle
{
get
{
if (!isExcel2007)
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
headStyle.SetFont(font);
return headStyle;
}
else
{
XSSFCellStyle headStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
XSSFFont font = (XSSFFont)workbook2007.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
headStyle.SetFont(font);
return headStyle;
}
}
}
private ICellStyle HeadStyle_red
{
get
{
ICellStyle Iheadstyle = null;
if (!isExcel2007)
{
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
headStyle.SetFont(font);
Iheadstyle = headStyle;
}
else
{
XSSFCellStyle headStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
XSSFFont font = (XSSFFont)workbook2007.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
headStyle.SetFont(font);
Iheadstyle = headStyle;
}
return Iheadstyle;
}
}
/// <summary>
/// 时间格式
/// </summary>
private ICellStyle DateStyle
{
get
{
ICellStyle datestyle = null;
if (!isExcel2007)
{
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
datestyle = dateStyle;
}
else
{
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook2007.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
datestyle = dateStyle;
}
return datestyle;
}
}
private ICellStyle ContentStyle
{
get
{
ICellStyle contentstyle = null;
if (!isExcel2007)
{
HSSFCellStyle contentStyle = (HSSFCellStyle)workbook.CreateCellStyle();
contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = ;
font.FontName = "宋体";
contentStyle.SetFont(font);
contentstyle = contentStyle;
}
else
{
XSSFCellStyle contentStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
XSSFFont font = (XSSFFont)workbook2007.CreateFont();
font.FontHeightInPoints = ;
font.FontName = "宋体";
contentStyle.SetFont(font);
contentstyle = contentStyle;
}
return contentstyle;
}
}
private ICellStyle ContentStyle_red
{
get
{
ICellStyle contentStyle_red = null;
if (!isExcel2007)
{
HSSFCellStyle contentStyle = (HSSFCellStyle)workbook.CreateCellStyle();
contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = ;
font.FontName = "宋体";
font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
contentStyle.SetFont(font);
contentStyle_red = contentStyle;
}
else
{
XSSFCellStyle contentStyle = (XSSFCellStyle)workbook2007.CreateCellStyle();
contentStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
contentStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
XSSFFont font = (XSSFFont)workbook2007.CreateFont();
font.FontHeightInPoints = ;
font.FontName = "宋体";
font.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
contentStyle.SetFont(font);
contentStyle_red = contentStyle;
}
return contentStyle_red;
}
}
/// <summary>
/// 实例一个工作薄
/// </summary>
public NpoiWorkbook()
{
workbook2007 = new XSSFWorkbook();
workbook = new HSSFWorkbook();
#region 右击文件 属性信息
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "SiBu";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
#endregion
}
/// <summary>
/// 导出excel初始化
/// </summary>
/// <param name="isexcel2007"></param>
public NpoiWorkbook(bool isexcel2007)
: this()
{
isExcel2007 = isexcel2007;
}
/// <summary>
/// 读取excel初始化,加载Excel文件
/// </summary>
/// <param name="filePath">文件路径</param>
public NpoiWorkbook(string filePath)
{
string filetype = Path.GetExtension(filePath).ToLower();
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (".xls".Equals(filetype))
{
isExcel2007 = false;
}
else
{
isExcel2007 = true;
}
if (filetype.Equals(".xls"))
{
workbook = new HSSFWorkbook(file);
isExcel2007 = false;
}
else
{
workbook2007 = new XSSFWorkbook(file);
isExcel2007 = true;
}
}
}
catch (Exception ex)
{
}
}
/// <summary>
/// 读取excel初始化,加载Excel文件流
/// </summary>
/// <param name="excelstream">excel文件流，使用场景：excel上传导入时候直接获取的文件流</param>
/// <param name="filename">带后缀名的文件名(格式如xx.xls或D:\excel\xx.xls)</param>
public NpoiWorkbook(Stream excelstream, string filename)
{
string filetype = Path.GetExtension(filename).ToLower();
if (".xls".Equals(filetype))
{
workbook = new HSSFWorkbook(excelstream);
isExcel2007 = false;
}
else
{
workbook2007 = new XSSFWorkbook(excelstream);
isExcel2007 = true;
}
}
/// <summary>
/// 获取Sheet页的数据
/// </summary>
/// <param name="sheetIndex">Sheet页Index，从0开始</param>
/// <returns>DataTable</returns>
public DataTable GetDataTable(int sheetIndex = )
{
DataTable dt = new DataTable();
ISheet sheet = null;
IWorkbook iworkbook = null;
if (!isExcel2007)
{
sheet = (HSSFSheet)workbook.GetSheetAt(sheetIndex);
iworkbook = workbook;
}
else
{
sheet = (XSSFSheet)workbook2007.GetSheetAt(sheetIndex);
iworkbook = workbook;
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
IRow headerRow = sheet.GetRow();
int cellCount = headerRow.LastCellNum;
for (int j = ; j < cellCount; j++)
{
ICell cell = headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null)
continue;
DataRow dataRow = dt.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
//NPOI中数字和日期都是NUMERIC类型的，这里对其进行判断是否是日期类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
dataRow[j] = cell.DateCellValue;
}
else//其他数字类型
{
dataRow[j] = cell.NumericCellValue;
}
}
else if (cell.CellType == CellType.Blank)//空数据类型
{
dataRow[j] = "";
}
else if (cell.CellType == CellType.Formula)//公式类型
{
HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(iworkbook);
dataRow[j] = eva.Evaluate(cell).StringValue;
}
else //其他类型都按字符串类型来处理
{
dataRow[j] = cell.StringCellValue;
}
}
}
dt.Rows.Add(dataRow);
}
return dt;
}
/// <summary>
/// 创建一个Sheet页
/// </summary>
/// <param name="Sheet">Sheet</param>
public void CreateSheet(Sheet sheetInfo)
{
if (string.IsNullOrWhiteSpace(sheetInfo.Name)) sheetInfo.Name = "Sheet" + workbook.NumberOfSheets + ;
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet(sheetInfo.Name);
int rowIndex = ;
#region 新建表，填充表头，填充列头，样式
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = ;
var columIndex = ;
foreach (var column in sheetInfo.Columns)
{
headerRow.CreateCell(columIndex).SetCellValue(column.Name);
if ("Red".Equals(column.ColorType))
headerRow.GetCell(columIndex).CellStyle = HeadStyle_red;
else
headerRow.GetCell(columIndex).CellStyle = HeadStyle;
//设置列宽
sheet.SetColumnWidth(columIndex, column.Width * );
sheet.SetColumnHidden(columIndex, column.Hidden);
columIndex++;
}
#endregion
#region 填充内容
rowIndex = ;
HSSFCellStyle contentStyle = (HSSFCellStyle)ContentStyle;
foreach (DataRow row in sheetInfo.DataSource.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
var columnIndex = ;
//dataRow.RowStyle = ContentStyle;
foreach (var column in sheetInfo.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);
if (!sheetInfo.DataSource.Columns.Contains(column.Code))
{
newCell.SetCellValue("");
}
else
{
string drValue = row[column.Code].ToString();
switch (column.DataType.ToUpper())
{
case "S"://字符串类型
newCell.SetCellValue(drValue);
if ("Red".Equals(column.ColorType))
newCell.CellStyle = ContentStyle_red;//格式化显示
else
newCell.CellStyle = contentStyle;//格式化显示
break;
case "D"://日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = contentStyle;//格式化显示
break;
case "B"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "I"://整型
int intV = ;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "F"://浮点型
double doubV = ;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
default:
newCell.SetCellValue(drValue);
break;
}
}
columnIndex++;
}
rowIndex++;
}
#endregion
}
public void CreateSheet(DataTable dt, string sheetname)
{
Sheet sheet = new Sheet();
sheet.Name = sheetname;
List<Column> columns = new List<Column>();
for (int i = ; i < dt.Columns.Count; i++)
{
DataColumn col = dt.Columns[i];
Column column = new Column();
column.Code = col.ColumnName;
column.Name = col.ColumnName;
column.DataType = "S";
column.Width = ;
column.Hidden = false;
columns.Add(column);
}
sheet.Columns = columns;
sheet.DataSource = dt;
if (!isExcel2007)
{
this.CreateSheet(sheet);
}
else
{
this.CreateSheet2007(sheet);
}
}
public void CreateSheet2007(Sheet sheetInfo)
{
if (string.IsNullOrWhiteSpace(sheetInfo.Name)) sheetInfo.Name = "Sheet" + workbook2007.NumberOfSheets + ;
XSSFSheet sheet = (XSSFSheet)workbook2007.CreateSheet(sheetInfo.Name);
int rowIndex = ;
#region 新建表，填充表头，填充列头，样式
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(rowIndex);
headerRow.HeightInPoints = ;
var columIndex = ;
foreach (var column in sheetInfo.Columns)
{
headerRow.CreateCell(columIndex).SetCellValue(column.Name);
if ("Red".Equals(column.ColorType))
headerRow.GetCell(columIndex).CellStyle = HeadStyle_red;
else
headerRow.GetCell(columIndex).CellStyle = HeadStyle;
//设置列宽
sheet.SetColumnWidth(columIndex, column.Width * );
sheet.SetColumnHidden(columIndex, column.Hidden);
columIndex++;
}
#endregion
#region 填充内容
rowIndex = ;
XSSFCellStyle contentStyle = (XSSFCellStyle)ContentStyle;
//XSSF contentStyle = (HSSFCellStyle)ContentStyle;
foreach (DataRow row in sheetInfo.DataSource.Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
var columnIndex = ;
//dataRow.RowStyle = ContentStyle;
foreach (var column in sheetInfo.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(columnIndex);
if (!sheetInfo.DataSource.Columns.Contains(column.Code))
{
newCell.SetCellValue("");
}
else
{
string drValue = row[column.Code].ToString();
switch (column.DataType.ToUpper())
{
case "S"://字符串类型
newCell.SetCellValue(drValue);
if ("Red".Equals(column.ColorType))
newCell.CellStyle = ContentStyle_red;//格式化显示
else
newCell.CellStyle = contentStyle;//格式化显示
break;
case "D"://日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = contentStyle;//格式化显示
break;
case "B"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "I"://整型
int intV = ;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "F"://浮点型
double doubV = ;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
default:
newCell.SetCellValue(drValue);
break;
}
}
columnIndex++;
}
rowIndex++;
}
#endregion
}
/// <summary>
/// 保存
/// </summary>
/// <param name="filePath">文件路径</param>
public void SaveAs(string filePath)
{
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = ;
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, , data.Length);
fs.Flush();
}
}
}
/// <summary>
/// 获取Workbook的MemoryStream
/// </summary>
/// <returns></returns>
public MemoryStream GetMemoryStream()
{
MemoryStream ms = new MemoryStream();
if (!isExcel2007)
{
workbook.Write(ms);
ms.Flush();
ms.Position = ;
}
else
{
workbook2007.Write(ms);
}
return ms;
}
}
public class Column
{
public string Code { get; set; }
public string Name { get; set; }
public string DataType { get; set; }
public int Width { get; set; }
public bool Hidden { get; set; }
public string ColorType { get; set; }
public Column() { }
public Column(string code, string name, string dataType, int width, bool hidden = false)
{
Code = code;
Name = name;
DataType = dataType;
Width = width;
Hidden = hidden;
}
}
public class Sheet
{
public string Name { get; set; }
public List<Column> Columns { get; set; }
public DataTable DataSource { get; set; }
public Sheet() { }
public Sheet(string name, List<Column> columns, DataTable dataSource)
{
Name = name;
Columns = columns;
DataSource = dataSource;
}
}
}