27,580
社区成员
发帖
与我相关
我的任务
分享关于提取一个excel文件中的数据
想将一个excel 表中从第三行开始的数据作为一个表导入到一个结果集dataset中，跪求最简便的方法。谢谢！
...全文
请发表友善的回复…
发表回复
尚方宝剑彤叟无欺 2017-09-18
- 打赏
- 举报
无论如何还是感谢yenange的帮助，虽然没有采用她提供的方法。
自己参考他人代码编写一个函数实现此需求，代码如下：
//首先是Using部分：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;
//以下为函数本身：
public DataSet ExcelToDataSetNew(string filePath)
{
Excel.Application excel = new Excel.ApplicationClass();
try
{
excel.Visible = false;
Excel.Workbook wb = excel.Workbooks.Open(filePath,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = (Excel.Worksheet)wb.Worksheets[1];
Excel.Range r = ws.get_Range(ws.Cells[1, 1], ws.Cells[40000, 50]);
object[,] values = (object[,])r.Value2;
DataTable dt = new DataTable("table1");
Hashtable hsField = new Hashtable();
// int ii = 0;
for (int i = 1; i <= 50; i++)
{
// ii=ii+1;
string v = Convert.ToString(values[2, i]);
if (v == null || v == "") continue;
hsField[v] = i;
dt.Columns.Add(v, typeof(string));
}
// MessageBox.Show(ii.ToString());
for (int i = 2; i < 40000; i++)
{
DataRow drNew = dt.NewRow();
int NotnullVCount = hsField.Keys.Count;
foreach (string key in hsField.Keys)
{
object v = values[i, Convert.ToInt32(hsField[key])];
if (v == null || Convert.ToString(v) == "") NotnullVCount--;
drNew[key] = v;
}
if (NotnullVCount <= 0) break;
drNew.EndEdit();
dt.Rows.Add(drNew);
}
DataSet ds = new DataSet();
ds.Tables.Add(dt);
values = null;
wb.Close(false, null, null);
return ds;
}
catch (System.Exception err)
{
MessageBox.Show(err.Message);
return null;
}
finally
{
excel.Quit();
excel = null;
}
}
//以下为调用部分：
OpenFileDialog ofd = new OpenFileDialog();
ofd.Title = "打开汇总表";
ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx";
ofd.ValidateNames = true;
ofd.CheckPathExists = true;
ofd.CheckFileExists = true;
ofd.RestoreDirectory = true;
ofd.ShowDialog();
string str = @"" + ofd.FileName;
if(str=="")
return;
else if (str.IndexOf("煤改电") < 0 || str.IndexOf("签字汇总") < 0)
{
MessageBox.Show("打开的Excel文件不是煤改电签字汇总表，请核实！", "打开失败", MessageBoxButtons.OK,
MessageBoxIcon.Error);
return;
}
///////////
bool blCheckError = false;
string strFilePath = str;// System.IO.Directory.GetCurrentDirectory() + "\\XXXX社区煤改电签字汇总.xlsx";
DataSet dsExcel = ExcelToDataSetNew(strFilePath);
..............
//后续代码与本课题无关，忽略不计
尚方宝剑彤叟无欺 2017-08-25
- 打赏
- 举报
已经引用了，IWorkbook等还是不能识别。
吉普赛的歌 2017-08-24
- 打赏
- 举报
先添加对这几个 dll 的引用吧
尚方宝剑彤叟无欺 2017-08-24
- 打赏
- 举报
谢谢楼上共享的代码，受累把IWorkbook、ISheet、IRow、ICell的引用或声明共享一下，NPOI.dll我已经引用了。万分感谢！
吉普赛的歌 2017-08-23
- 打赏
- 举报
java 用 POI
.net 用 NPOI
下面是 .net 版（C# ）的帮助类：
NPOI的dll 你自己找下
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Transactions;
using System.Configuration;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.XSSF.UserModel;
using NPOI.SS.UserModel;
namespace Util
{
/// <summary>
/// NPOI辅助类，add by ngye, on 2014-08-26.
/// </summary>
public static class NPOIHelper
{
/// <summary>
/// 根据路径，得到所有的 Sheet , 以及每个Sheet 第一行的表头
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static Dictionary<string, List<string>> GetAllSheetsWithHeader(string filePath)
{
Dictionary<string, List<string>> result = new Dictionary<string, List<string>>();
IWorkbook workbook = GetWorkBook(filePath); //获取到工作簿
ISheet sheet = null;
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
//获取到 Sheet 名
string sheetName = workbook.GetSheetName(i);
sheet = workbook.GetSheetAt(i);
List<string> colNameList = new List<string>();
if (sheet != null)
{
//得到第一行
IRow firstRow = sheet.GetRow(0);
if (firstRow == null)
continue;
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
for (int j = firstRow.FirstCellNum; j < cellCount; ++j)
{
string colName = firstRow.GetCell(j).StringCellValue;
colNameList.Add(colName);
}
}
result.Add(sheetName, colNameList);
}
workbook.Clear();
return result;
}
/// <summary>
/// 导入Excel文件
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="sheetIndex">Sheet序号</param>
/// <returns>DataTable</returns>
public static DataTable ImportExcelFile(string filePath, int sheetIndex)
{
IWorkbook workbook = GetWorkBook(filePath);
ISheet sheet = null;
DataTable dt = new DataTable();
int startRow = 0;
try
{
sheet = workbook.GetSheetAt(sheetIndex);
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);
dt.Columns.Add(column);
}
startRow = sheet.FirstRowNum + 1;
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null) continue; //没有数据的行默认是null
DataRow dataRow = dt.NewRow();
int dataColumn = 0;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null){ //同理，没有数据的单元格都默认是null
Cell2DataRow(dataRow, row, j);
//如果对应行的列不为NULL, 而且
if (!dataRow.IsNull(j) && !string.IsNullOrEmpty(Convert.ToString(dataRow[j])))
{
dataColumn++;
}
}
}
//只要有一列有数据，就应该加入
if (dataColumn != 0)
{
dt.Rows.Add(dataRow);
}
}
}
return dt;
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 根据 Excel 文件路径获取 IWorkbook
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public static IWorkbook GetWorkBook(string filePath)
{
GC.Collect();
IWorkbook workbook = null;
FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
if (filePath.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook(fs);
else if (filePath.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook(fs);
fs.Close();
fs.Dispose();
GC.Collect();
return workbook;
}
/// <summary>
/// Excel 单元格转成 DataRow
/// </summary>
/// <param name="dr"></param>
/// <param name="row"></param>
/// <param name="j"></param>
public static void Cell2DataRow(DataRow dr, IRow row, int j)
{
ICell cell = row.GetCell(j);
switch (cell.CellType)
{
case CellType.Blank:
dr[j] = DBNull.Value;
break;
case CellType.Boolean:
dr[j] = cell.BooleanCellValue;
break;
case CellType.Numeric:
if (DateUtil.IsCellDateFormatted(cell))
{
dr[j] = cell.DateCellValue;
}
else
{
dr[j] = cell.NumericCellValue;
}
break;
case CellType.String:
dr[j] = cell.StringCellValue;
break;
case CellType.Error:
dr[j] = cell.ErrorCellValue;
break;
case CellType.Formula:
//cell = evaluator.EvaluateInCell(cell) as ICell;
dr[j] = cell.ToString();
break;
default:
throw new NotSupportedException(string.Format("Catched unhandle CellType[{0}]", cell.CellType));
}
}
}//end of class
}//end of namespace
尚方宝剑彤叟无欺 2017-08-23
- 打赏
- 举报
的确可以读取，找不到现成的代码了，那位提供一下！直接提取到datasets。
二月十六 2017-08-23
- 打赏
- 举报
用程序应该可以直接读取吧，不用到数据库中再转一圈了。
