using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;
namespace exceltodataset
{
public static class Program
{
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
{
DataSet ds = new DataSet();
HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
{
HSSFSheet sheet = workbook.GetSheetAt(a);
DataTable table = new DataTable(); HSSFRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列,则不再继续向后读取
cellCount = i + 1;
break;
} DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
} for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
{
// 如果遇到第一个空行,则不再继续向后读取
break;
} DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
} table.Rows.Add(dataRow);
}
ds.Tables.Add(table);
} excelFileStream.Close();
workbook = null; return ds;
}
[STAThread]
public static void Main()
{
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径。</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataSetFromExcel(stream, headerRowIndex);
}
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
using System.Data;
using System.IO;
using System.Web;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;
namespace exceltodataset
{
public static class Program
{
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
{
DataSet ds = new DataSet();
HSSFWorkbook workbook = new HSSFWorkbook(excelFileStream);
for (int a = 0, b = workbook.NumberOfSheets; a < b; a++)
{
HSSFSheet sheet = workbook.GetSheetAt(a);
DataTable table = new DataTable(); HSSFRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列,则不再继续向后读取
cellCount = i + 1;
break;
} DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
} for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = sheet.GetRow(i);
if (row == null || row.GetCell(0) == null || row.GetCell(0).ToString().Trim() == "")
{
// 如果遇到第一个空行,则不再继续向后读取
break;
} DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
dataRow[j] = row.GetCell(j).ToString();
}
} table.Rows.Add(dataRow);
}
ds.Tables.Add(table);
} excelFileStream.Close();
workbook = null; return ds;
}
[STAThread]
public static void Main()
{
/// <summary>
/// 由Excel导入DataSet,如果有多个工作表,则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径,为物理路径。</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataSetFromExcel(stream, headerRowIndex);
}
}
}
}
}
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货