ExcelHelper:
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace CMS.Common
{
public class ExcelHelper
{
/// <summary>
/// 将Excel表导入DataTable
/// </summary>
/// <param name="filePath">目标Excel文件的物理路径(xxx.xls)</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns></returns>
public static DataTable ImportExcelFile(string filePath, bool isFirstRowColumn)
{
HSSFWorkbook hssfworkbook;
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
}
catch (Exception e)
{
throw e;
}
NPOI.SS.UserModel.ISheet sheet = hssfworkbook.GetSheetAt(0);
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable();
#region 添加列
if (isFirstRowColumn)
{
for (int j = sheet.GetRow(0).FirstCellNum; j < (sheet.GetRow(0).LastCellNum); ++j)
{
ICell cell = sheet.GetRow(0).GetCell(j);
if (cell != null)
{
string cellValue = cell.StringCellValue;//将列的值设为列名称
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
dt.Columns.Add(column);
}
}
}
rows.MoveNext();//如果Excel表的第一行是表名称,则往下推进一行(数据的遍历从第二行开始)
}
#endregion
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell)) //如果单元格是日期格式
{
dr[i] = cell.DateCellValue.ToString("yyyy-MM-dd");
}
else
{
dr[i] = cell.ToString();
}
}
}
dt.Rows.Add(dr);
}
RemoveEmpty(dt);//删除DataTable中的空白行
return dt;
}
/// <summary>
/// 将DataTable导出到Excel表:
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="filePath">写入到目标Excel文件的路径(xxx.xls)</param>
public static void DataTableToExcel(DataTable dt, string filePath)
{
if (dt.TableName == null || dt.TableName == "")
{
dt.TableName = DateTime.Now.ToString();
}
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
sheet.CreateFreezePane(0, 1, 0, 1);//冻结第一行(第一行不能拖动)
// 写入到客户端
using (System.IO.MemoryStream ms = new System.IO.MemoryStream())
{
book.Write(ms);
using (FileStream fs = new FileStream(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
book = null;
}
}
}
/// <summary>
/// 删除DataTable中的空白行
/// </summary>
/// <param name="dt"></param>
protected static void RemoveEmpty(DataTable dt)
{
List<DataRow> removelist = new List<DataRow>();
for (int i = 0; i < dt.Rows.Count; i++)
{
bool IsNull = true;
for (int j = 0; j < dt.Columns.Count; j++)
{
if (!string.IsNullOrEmpty(dt.Rows[i][j].ToString().Trim()))
{
IsNull = false;
}
}
if (IsNull)
{
removelist.Add(dt.Rows[i]);
}
}
for (int i = 0; i < removelist.Count; i++)
{
dt.Rows.Remove(removelist[i]);
}
}
}
}
文件下载:
public class HomeController : Controller
{
public ActionResult Index()
{
//SqlHelper.SqlBulkCopy();
DataTable table = SqlHelper.ExecuteDataTable("select * from Table_1", null);
var filePath = Server.MapPath("Excel/abc.xls");//设定要文件要保存的路径
ExcelHelper.WriteExcel(table,filePath); //将DataTable表的数据写入到Excel文件中
FileInfo info = new FileInfo(filePath); //读取刚刚保存的Excel文件
long fileSize = info.Length;
Response.Clear();
Response.ContentType = "application/octet-stream";
Response.AddHeader("Content-Disposition", "attachement;filename=" + "abc.xls");
//指定文件大小
Response.AddHeader("Content-Length", fileSize.ToString());
Response.WriteFile(filePath, 0, fileSize); //将文件响应到浏览器
Response.Flush();
Response.Close();
return View();
}
}