NPOI Excel导入 导出

using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

public DataSet ExcelToDataTable(string path)
{
DataSet dataSet1 = new DataSet();
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file); //把xls文件中的数据写入hssfworkbook中 //for (int i = 0; i < hssfworkbook.NumberOfSheets; i++) //NumberOfSheets是myxls.xls中总共的表数
//{
ISheet sheet = hssfworkbook.GetSheetAt(); //读取当前表数据 DataTable dt = new DataTable(); for (int j = ; j <= sheet.LastRowNum; j++) //LastRowNum 是当前表的总行数
{
IRow row = sheet.GetRow(j); //读取当前行数据
DataRow dr = dt.NewRow();
if (row != null)
{
//sbr.Append("-------------------------------------\r\n"); //读取行与行之间的提示界限
for (int k = ; k < row.LastCellNum; k++) //LastCellNum 是当前行的总列数
{
ICell cell = row.GetCell(k); //当前表格
if (cell != null)
{
if (j == )
{
dt.Columns.Add(cell.ToString());
}
else
{
RegexHelper regexHelper = new RegexHelper(); if (regexHelper.IsMatch(cell.ToString(), "[0-9]{1,2}/[0-9]{1,2}/[0-9]{2,4}"))
{
dr[k] = cell.DateCellValue.ToString();
}
else
{
dr[k] = cell.ToString();
}
//日期格式判断输出 }
//sbr.Append(cell.ToString()); //获取表格中的数据并转换为字符串类型 }
}//for
}// if (row != null)
if (j != )
{
dt.Rows.Add(dr);
}
}//for j dataSet1.Tables.Add(dt); //} }//using
return dataSet1;
}

#region "数据导出"
/// <summary>
/// WinForm DataTable数据导出Excel到本地
/// </summary>
/// <param name="table"></param>
/// <param name="fileName"></param>
public static void SaveToFile(DataTable table, string fileName)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
MemoryStream ms = RenderToExcel(table); byte[] data = ms.ToArray(); fs.Write(data, , data.Length);
fs.Flush(); data = null;
}
}
public static MemoryStream RenderToExcel(DataTable table)
{
MemoryStream ms = new MemoryStream(); using (table)
{
using (IWorkbook workbook = new HSSFWorkbook())
{
using (ISheet sheet = workbook.CreateSheet())
{
IRow headerRow = sheet.CreateRow(); // 处理标题.
foreach (DataColumn column in table.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//如果标题没有设置,返回ColumnName的值 int rowIndex = ; foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in table.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
} rowIndex++;
} workbook.Write(ms);
ms.Flush();
ms.Position = ;
}
}
}
return ms;
} /// <summary>
/// Web DataTable 数据导出到浏览器客户端
/// </summary>
/// <param name="table"></param>
/// <param name="context"></param>
/// <param name="fileName"></param>
static void RenderToBrowser(DataTable table, HttpContext context, string fileName)
{
MemoryStream ms = RenderToExcel(table);
if (context.Request.Browser.Browser == "IE")
fileName = HttpUtility.UrlEncode(fileName);
context.Response.AddHeader("Content-Disposition", "attachment;fileName=" + fileName);
context.Response.BinaryWrite(ms.ToArray());
} #endregion

ExcelHelp ExcelHelp = new ExcelHelp();
//导入Excel到datagridview
dataGridView1.DataSource = ExcelHelp.ExcelToDataTable(txtfileurl.Text).Tables[];
//导出
saveFileDialog1.Filter = "Excel|*.Xls;";
saveFileDialog1.FileName = "测试.Xls";
if (saveFileDialog1.ShowDialog() == DialogResult.OK)
{
ExcelHelp.SaveToFile(rptDst.Tables["interaction"], saveFileDialog1.FileName);
}