using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Data.OleDb;
|
using System.IO;
|
using System.Linq;
|
using System.Web;
|
|
namespace VueWebApi.Tools
|
{
|
public class NPOIHelper
|
{
|
/// <summary>
|
/// NPOI简单Demo，快速入门代码
|
/// </summary>
|
/// <param name="dtSource"></param>
|
/// <param name="strFileName"></param>
|
/// <remarks>NPOI认为Excel的第一个单元格是：(0，0)</remarks>
|
/// <Author>柳永法 http://www.yongfa365.com/ 2010-5-8 22:21:41</Author>
|
public static void ExportEasy(DataTable dtSource, string strFileName)
|
{
|
HSSFWorkbook workbook = new HSSFWorkbook();
|
ISheet sheet = workbook.CreateSheet();
|
//填充表头
|
IRow dataRow = sheet.CreateRow(0);
|
foreach (DataColumn column in dtSource.Columns)
|
{
|
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
|
}
|
|
|
//填充内容
|
for (int i = 0; i < dtSource.Rows.Count; i++)
|
{
|
dataRow = sheet.CreateRow(i + 1);
|
for (int j = 0; j < dtSource.Columns.Count; j++)
|
{
|
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
|
}
|
}
|
|
|
//保存
|
using (MemoryStream ms = new MemoryStream())
|
{
|
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
|
{
|
workbook.Write(ms);
|
ms.Flush();
|
ms.Position = 0;
|
byte[] data = ms.ToArray();
|
fs.Write(data, 0, data.Length);
|
fs.Flush();
|
}
|
}
|
//workbook.Dispose();
|
}
|
|
|
|
|
public static System.Data.DataTable GetExcelDatatable(string fileUrl)
|
{
|
|
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileUrl + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
|
|
|
DataSet myDataSet = new DataSet();
|
|
//连接串
|
OleDbConnection conn = new OleDbConnection(strConn);
|
conn.Open();
|
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
|
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
|
//包含excel中表名的字符串数组
|
string[] strTableNames = new string[dtSheetName.Rows.Count];
|
for (int k = 0; k < dtSheetName.Rows.Count; k++)
|
{
|
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
|
}
|
OleDbDataAdapter myCommand = null;
|
DataTable dt = new DataTable();
|
//从指定的表明查询数据,可先把所有表明列出来供用户选择
|
string strExcel = "select*from[" + strTableNames[0] + "]";
|
myCommand = new OleDbDataAdapter(strExcel, strConn);
|
//myCommand.Fill(dt);
|
myCommand.Fill(myDataSet, "ExcelInfo");
|
conn.Close();
|
DataTable ExcelTable = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable();
|
return ExcelTable;
|
}
|
|
|
/// <summary>
|
/// 多个sheet导入
|
/// </summary>
|
/// <param name="fileUrl"></param>
|
/// <returns></returns>
|
public static List<DataTable> GetExcelDatatableList(string fileUrl)
|
{
|
List<DataTable> list = new List<DataTable>();
|
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileUrl + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
|
|
|
DataSet myDataSet = new DataSet();
|
|
//连接串
|
OleDbConnection conn = new OleDbConnection(strConn);
|
conn.Open();
|
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
|
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
|
//包含excel中表名的字符串数组
|
string[] strTableNames = new string[dtSheetName.Rows.Count];
|
for (int k = 0; k < dtSheetName.Rows.Count; k++)
|
{
|
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
|
}
|
for (int i = 0; i < strTableNames.Length; i++)
|
{
|
OleDbDataAdapter myCommand = null;
|
DataTable dt = new DataTable();
|
//从指定的表明查询数据,可先把所有表明列出来供用户选择
|
string strExcel = "select*from[" + strTableNames[i].Trim() + "]";
|
myCommand = new OleDbDataAdapter(strExcel, strConn);
|
//myCommand.Fill(dt);
|
myCommand.Fill(myDataSet, "" + i + "");
|
conn.Close();
|
DataTable ExcelTable = myDataSet.Tables[i].DefaultView.ToTable();
|
list.Add(ExcelTable);
|
}
|
|
return list;
|
}
|
|
/// <summary>
|
/// 多个sheet导入
|
/// </summary>
|
/// <param name="fileUrl"></param>
|
/// <returns></returns>
|
public static List<DataTable> GetExcelDatatableListName(string fileUrl)
|
{
|
List<DataTable> list = new List<DataTable>();
|
string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + fileUrl + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
|
|
|
DataSet myDataSet = new DataSet();
|
|
//连接串
|
OleDbConnection conn = new OleDbConnection(strConn);
|
conn.Open();
|
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
|
DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
|
for (int m = 0; m < dtSheetName.Rows.Count; m++)
|
{
|
string TABLE_NAME = dtSheetName.Rows[m]["TABLE_NAME"].ToString();
|
string FilterDatabase = "$FilterDatabase";
|
if (TABLE_NAME.Contains(FilterDatabase))
|
{
|
dtSheetName.Rows.Remove(dtSheetName.Rows[m]);
|
}
|
}
|
|
//包含excel中表名的字符串数组
|
string[] strTableNames = new string[dtSheetName.Rows.Count];
|
for (int k = 0; k < dtSheetName.Rows.Count; k++)
|
{
|
strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
|
}
|
for (int i = 0; i < strTableNames.Length; i++)
|
{
|
OleDbDataAdapter myCommand = null;
|
DataTable dt = new DataTable();
|
|
//从指定的表明查询数据,可先把所有表明列出来供用户选择
|
string strExcel = "select*from[" + strTableNames[i].Trim() + "]";
|
myCommand = new OleDbDataAdapter(strExcel, strConn);
|
//myCommand.Fill(dt);
|
myCommand.Fill(myDataSet, "" + i + "");
|
conn.Close();
|
DataTable ExcelTable = myDataSet.Tables[i].DefaultView.ToTable();
|
ExcelTable.TableName = strTableNames[i].Trim();
|
list.Add(ExcelTable);
|
}
|
|
return list;
|
}
|
}
|
}
|
