using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using NPOI.SS.UserModel;
using System.Data;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using System.Linq;
namespace Sys.LXZX.Core.Utils
{
/// <summary>
/// 下载Excel帮助类
/// </summary>
public class ExcelDownHelper : System.Web.UI.Page
{
public string fileFullName = ""; // 文件路径+名称
public string fileName = ""; //模板名称
public string fileDowName = ""; // 下载后的文件名称
public string fileDowFullName = ""; // 下载文件路径+名称
public string filePath = "";//
//构造函数
public ExcelDownHelper(string filePath, string fileName, string fileDowName)
{
this.filePath = filePath;
this.fileName = fileName;
this.fileDowName = fileDowName;
fileFullName = filePath + fileName;
fileDowFullName = filePath + fileDowName;
//File.Copy(@"E:\test.xls", fileFullName);
}
/// <summary>
/// DataTable 数据写入Excel
/// </summary>
/// <param name="dt">DataTable 数据</param>
/// <param name="sheetName">表格名称</param>
/// <param name="ishead">是否跳过头部</param>
/// <param name="jump">数据跳过行数</param>
public void TableToExcel(DataTable dt, string sheetName, bool ishead, int jump = 0)
{
if (File.Exists(fileDowFullName))
{ // 文件存在则删除
File.Delete(fileDowFullName);
}
File.Copy(fileFullName, fileDowFullName); // copy结构
IWorkbook workbook;
FileStream fsRead = null;
using (fsRead = File.OpenRead(fileDowFullName))
{
string fileExt = Path.GetExtension(fileDowFullName).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fsRead); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fsRead); } else { workbook = null; }
if (workbook == null) { return; }
// ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName
ISheet sheet;
if (sheetName == "")
{
sheet = workbook.GetSheet("Sheet1");
}