-
引用dll
去管网下载NPOI:http://npoi.codeplex.com/releases/
或者百度网盘:链接:https://pan.baidu.com/s/1GxG42R_TAo606D3IZ8yWvQ
提取码:g4yx
里面包含.net2.0,.net4.0,.net4.5 -
工具类
using Brettle.Web.NeatUpload;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Data;
using System.IO;
using System.Web.UI;
/// <summary>
/// ExcelImport 的摘要说明
/// 将Excel数据导入Datatable
///
/// 将dataset导出为Excel并下载
/// </summary>
public class ExcelImport
{
public ExcelImport()
{
}
//获取Excel上传路径
public static string setFilepath(InputFile inputfile)
{
string SaveFilePath = "";
string end = Path.GetExtension(inputfile.FileName); //获取扩展名
string datetime = DateTime.Now.Date.ToString("yyyyMMdd");
string time = DateTime.Now.ToShortTimeString().Replace(":", "");
string newFilename = datetime + time + DateTime.Now.Millisecond.ToString() + end;
SaveFilePath = System.IO.Path.Combine(System.Web.HttpContext.Current.Request.MapPath("~/UpLoads/Excel"), newFilename);//文件存放路径
inputfile.MoveTo(SaveFilePath, Brettle.Web.NeatUpload.MoveToOptions.Overwrite);
return SaveFilePath;
}
//将Excel转为datatable
public static DataTable ImportExcel(string filepath)
{
DataTable dt = new DataTable();
using (FileStream fsRead = System.IO.File.OpenRead(filepath))
{
IWorkbook wk = null;
//获取后缀名
string extension = filepath.Substring(filepath.LastIndexOf(".")).ToString().ToLower();
//判断是否是excel文件
if (extension == ".xlsx" || extension == ".xls")
{
//判断excel的版本
if (extension == ".xlsx")
{
wk = new XSSFWorkbook(fsRead);
}
else
{
wk = new HSSFWorkbook(fsRead);
}
//获取第一个sheet
ISheet sheet = wk.GetSheetAt(0);
//获取第一行
IRow headrow = sheet.GetRow(0);
//创建列
for (int i = headrow.FirstCellNum; i < headrow.Cells.Count; i++)
{
// DataColumn datacolum = new DataColumn(headrow.GetCell(i).StringCellValue);
DataColumn datacolum = new DataColumn("F" + (i + 1));
dt.Columns.Add(datacolum);
}
//读取每行,从第二行起
for (int r = 1; r <= sheet.LastRowNum; r++)
{
bool result = false;
DataRow dr = dt.NewRow();
//获取当前行
IRow row = sheet.GetRow(r);
//读取每列
for (int j = 0; j < headrow.Cells.Count; j++)
{
ICell cell = row.GetCell(j); //一个单元格
dr[j] = GetCellValue(cell); //获取单元格的值
//全为空则不取
if (dr[j].ToString() != "")
{
result = true;
}
}
if (result == true)
{
dt.Rows.Add(dr); //把每行追加到DataTable
}
}
}
}
return dt;
}
//对单元格进行判断取值
private static string GetCellValue(ICell cell)
{
if (cell == null)
{
return string.Empty;
}
switch (cell.CellType)
{
case CellType.Blank: //空数据类型
return string.Empty;
case CellType.Boolean: //bool类型
return cell.BooleanCellValue.ToString();
case CellType.Error:
return cell.ErrorCellValue.ToString();
case CellType.Numeric: //数字类型
if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型
{
return cell.DateCellValue.ToString();
}
else //其它数字
{
return cell.NumericCellValue.ToString();
}
case CellType.Unknown: //无法识别类型
default: //默认类型
return cell.ToString();//
case CellType.String: //string 类型
return cell.StringCellValue;
case CellType.Formula: //带公式类型
try
{
HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
e.EvaluateInCell(cell);
return cell.ToString();
}
catch
{
return cell.NumericCellValue.ToString();
}
}
}
//下载
public static void downloadExcel(Page page,string fileName)
{
try
{
string strfile = page.Server.MapPath("~/UpLoads/Excel/"+fileName);
FileInfo fileInfo = new FileInfo(strfile);
page.Response.Clear();
//Response.Charset = "GB2312";
page.Response.ContentEncoding = System.Text.Encoding.UTF8;
page.Response.AddHeader("Content-Disposition", "attachment;filename=" + page.Server.UrlEncode(fileInfo.Name));
page.Response.AddHeader("Content-Length", fileInfo.Length.ToString());
page.Response.ContentType = "application/x-bittorrent";
page.Response.WriteFile(fileInfo.FullName);
page.Response.End();
}
catch (System.Threading.ThreadAbortException ex)
{
//不做处理
}
catch (Exception ex)
{
//做处理
}
}
//将dataset导入Excel
public static bool DsToExcel(Page page,DataSet _ds)
{
try
{
string datetime = DateTime.Now.Date.ToString("yyyyMMdd");
string time = DateTime.Now.ToShortTimeString().Replace(":", "");
string newFilename = datetime + time + DateTime.Now.Millisecond.ToString() + ".xlsx";
string path= System.IO.Path.Combine(System.Web.HttpContext.Current.Request.MapPath("~/UpLoads/Excel"), newFilename); //Excel存放路径
#region NPOI 导出方式
XSSFWorkbook hw = new XSSFWorkbook();
#region output every sheet
for (int t = 0; t < _ds.Tables.Count; t++)
{
ISheet sheet2 = (ISheet)hw.CreateSheet(_ds.Tables[t].TableName);
IRow rowCol2 = (IRow)sheet2.CreateRow(0);
for (int j = 0; j < _ds.Tables[t].Columns.Count; j++)
{
ICell cell = (ICell)rowCol2.CreateCell(j);
cell.SetCellValue(_ds.Tables[t].Columns[j].ColumnName);
}
for (int i = 0; i < _ds.Tables[t].Rows.Count; i++)
{
IRow row = (IRow)sheet2.CreateRow(i + 1);
for (int j = 0; j < _ds.Tables[t].Columns.Count; j++)
{
ICell cell = row.CreateCell(j);
//如果某一列是整型数据,在导出的Excel结果文件中那一列的值会转换成数值类型
if (j == 0 || j == 7 || j == 11 || j == 12 || j == 13 || j == 14 || j == 15 || j == 16 || j == 17)
{
int num = -1;
bool flag = int.TryParse(_ds.Tables[t].Rows[i][j].ToString(), out num);
if (flag)
{
cell.SetCellValue(num);
}
else
{
cell.SetCellValue(_ds.Tables[t].Rows[i][j].ToString());
}
}
else
{
if (_ds.Tables[t].Rows[i][j].ToString().Contains("<br>"))
{
ICellStyle cs = hw.CreateCellStyle();
cs.WrapText = true;
cell.CellStyle = cs;
}
cell.SetCellValue(_ds.Tables[t].Rows[i][j].ToString().Replace("<br>", "\r\n"));
}
}
}
}
#endregion
FileStream file = new FileStream(path, FileMode.Create);
hw.Write(file);
file.Close();
#endregion
//sw.WriteLine("生产的excel 建立完成,再次检查文件是否成功建立");
#region check if the excel file is created
if (!File.Exists(path))
{
//sw.WriteLine("生产的excel 未建立成功");
return false;
}
#endregion
//下载
downloadExcel(page, newFilename);
return true;
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
}
}