1.可以从任何来源获取npoi的dll文件;
2.把这四个dll文件引入到你的bin目录下面
3.引入程序集:
XSSF.UserModel对应的导出是xlsx;
HSSF.UserModel对应的导出是xls;在导出时莫要用错了
下面奉上实例:(此处我用的版本较高的xlsx对应XSSF)
public static byte[] Export(DataTable dtSource,string strfilepath)
{
ISheet sheet;
XSSFWorkbook workbook = new XSSFWorkbook();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
strfilepath = System.IO.Path.GetFileNameWithoutExtension(strfilepath);
string fileFullName = HttpContext.Current.Server.MapPath(System.Web.HttpContext.Current.Request.ApplicationPath + "/Export/" + strfilepath + ".xlsx");
if (File.Exists(fileFullName))
{
GC.Collect();
//GC.SuppressFinalize(this);
GC.WaitForPendingFinalizers();
File.Delete(fileFullName);
}
sheet = workbook.CreateSheet(dtSource.TableName.ToString());
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
// 新建表,填充表头,填充列头,样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(dtSource.TableName);
}
// 列头及样式
{
IRow headerRow = sheet.CreateRow(0);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
rowIndex = 1;
}
// 填充内容
IRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
if (!string.IsNullOrEmpty(drValue))
newCell.SetCellValue(dateV.ToString("yyyy/MM/dd"));
else
newCell.SetCellValue("");
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
}
FileStream fileStream = new FileStream(fileFullName, FileMode.Create, FileAccess.Write);
workbook.Write(fileStream);
workbook = null;
fileStream.Dispose();
FileStream fs = new FileStream(fileFullName, FileMode.Open, FileAccess.Read);
long fileSize = fs.Length;
byte[] fileBuffer = new byte[fileSize];
fs.Read(fileBuffer, 0, (int)fileSize);
fs.Close();
fileStream.Dispose();
File.Delete(fileFullName);
return fileBuffer;
}
最后一步,就是导出了,直接看代码:
public static void ExportXlsxFile(DataTable dtSource, string strFileName)
{
HttpContext curContext = HttpContext.Current;
// 设置编码和附件格式
curContext.Response.ContentType = “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”;
curContext.Response.ContentEncoding = Encoding.UTF8;
curContext.Response.Charset = “”;
curContext.Response.AppendHeader(“Content-Disposition”,
“attachment;filename=” + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
curContext.Response.BinaryWrite(Export(dtSource, strFileName));
curContext.Response.End()
}