Office组件读写Excel 不同版本兼容问题一直以来是比较头疼的问题
NPOI 提供Excel读写组件 NuGet 搜 NPOI 第一个 添加引用即可
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 WindowsFormsApplication1
{
public static class ExcelHelper
{
/// <summary>
/// 导出表格数据到 xls
/// </summary>
/// <param name="dt"></param>
/// <param name="filePath"></param>
public static void Export(DataTable dt, string filePath)
{
if (!string.IsNullOrEmpty(filePath) && null != dt && dt.Rows.Count > 0)
{
HSSFWorkbook book = new HSSFWorkbook();
ISheet sheet = book.CreateSheet(dt.TableName);
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++)
{
row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j]));
}
}
using (FileStream fs = new FileStream(filePath, FileMode.Create, FileAccess.Write))
{
fs.Seek(0, SeekOrigin.Begin);
book.Write(fs);
}
book = null;
}
}
/// <summary>
/// WEBAPI 下载Excel表格文件
/// </summary>
/// <param name="request"></param>
/// <param name="dt"></param>
/// <param name="filename"></param>
/// <returns></returns>
//public static HttpResponseMessage DownLoadExcel(this HttpRequestMessage request, DataTable dt, String filename)
//{
// var response = request.CreateResponse(HttpStatusCode.OK);
// HSSFWorkbook book = new HSSFWorkbook();
// ISheet sheet = book.CreateSheet(dt.TableName);
// 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++)
// {
// 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]));
// }
// }
// // 写入到客户端
// System.IO.MemoryStream stream = new System.IO.MemoryStream(8192);
// book.Write(stream);
// stream.Seek(0, SeekOrigin.Begin);
// response.Content = new StreamContent(stream);
// response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
// response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
// {
// FileName = filename
// };
// book = null;
// return response;
//}
/// <summary>
/// 从 Xls文件导入数据到 DataTable
/// </summary>
/// <param name="filePath"></param>
/// <param name="SheetName"></param>
/// <returns></returns>
public static DataTable Import(string filePath, String SheetName)
{
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheet(SheetName);
if (sheet == null)
{
return new DataTable();
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable(SheetName);
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
ICell cell = sheet.GetRow(0).Cells[j];
dt.Columns.Add(cell.ToString());
}
rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow Row = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
Row[i] = cell == null ? null : cell.ToString();
}
dt.Rows.Add(Row);
}
hssfworkbook.Close();
hssfworkbook = null;
return dt;
}
/// <summary>
/// 从 Xls文件导入数据到 DataTable
/// </summary>
/// <param name="filePath"></param>
/// <param name="SheetName"></param>
/// <returns></returns>
public static DataTable Import(string filePath, Int32 SheetIndex)
{
HSSFWorkbook hssfworkbook;
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
hssfworkbook = new HSSFWorkbook(file);
}
ISheet sheet = hssfworkbook.GetSheetAt(SheetIndex);
String SheetName = hssfworkbook.GetSheetName(SheetIndex);
if (sheet == null)
{
return new DataTable();
}
System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
DataTable dt = new DataTable(SheetName);
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
ICell cell = sheet.GetRow(0).Cells[j];
dt.Columns.Add(cell.ToString());
}
rows.MoveNext();
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow Row = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
ICell cell = row.GetCell(i);
Row[i] = cell == null ? null : cell.ToString();
}
dt.Rows.Add(Row);
}
hssfworkbook.Close();
hssfworkbook = null;
return dt;
}
}
}