如何读取asp.net excel文件 [英] How to read excel file in asp.net
问题描述
我使用Epplus库，以从Excel文件。该codeI上传数据现在用的是完全适用于具有标准form.ie如果第一行是列，其余全部数据对应于column.But excel文件现在是一个天我经常收到，EXCEL具有不同结构的文件，我不能够读取
excel文件像如下图所示。
我想是在第三排i湾地区唯一的ID，位置ID和values.Then第7行是列和第8至15是其values.Finally 17行是18日至20日。如何列装载所有这些DATAS到单独的数据表
code I采用的是如下图所示
我创建了一个扩展方法
公共静态数据集Exceltotable（此字符串路径）
{
数据集DS = NULL;
使用（VAR PCK =新OfficeOpenXml.ExcelPackage（））
{
尝试
{
使用（VAR流= File.Open（路径，FileMode.Open，FileAccess.Read，FileShare.ReadWrite））
{
pck.Load（流）;
}
DS =新的DataSet（）;
VAR WSS = pck.Workbook.Worksheets;
////////////////////////////////////
//应用程序=新的应用程序（）;
//app.Visible = TRUE;
//app.Workbooks.Add（）;
//app.Workbooks.Add(@\"c:\\MyWork\\WorkBook1.xls）;
//app.Workbooks.Add(@\"c:\\MyWork\\WorkBook2.xls）;
//的for（int i = 2; I＆LT; = app.Workbooks.Count;我++）
// {
//为（INT J = 1; J＆LT; = app.Workbooks [I] .Worksheets.Count; J ++）
// {
//表WS = app.Workbooks [I] .Worksheets [J]。
// ws.Copy（app.Workbooks [1] .Worksheets [1]）;
//}
//} ////////////////////////////////////////////////// / //对于（的int = 0; S小于5氏++）
// {
的foreach（WSS中VAR WS）
{
System.Data.DataTable TBL =新System.Data.DataTable（）;
布尔hasHeader = TRUE; //相应调整（我已经提到，这是一个简单的方法）
字符串的ErrorMessage =的String.Empty;
的foreach（在ws.Cells变种firstRowCell [1，1，1，ws.Dimension.End.Column]）
{ tbl.Columns.Add（？hasHeader firstRowCell.Text：的String.Format（列{0}，firstRowCell.Start.Column））; }
VAR STARTROW = hasHeader？ 2：1;
为（VAR的rowNum = STARTROW;＆的rowNum LT = ws.Dimension.End.Row;的rowNum ++）
{
VAR wsRow = ws.Cells第[ROWNUM，1，的rowNum，ws.Dimension.End.Column]
VAR行= tbl.NewRow（）;
的foreach（在wsRow VAR细胞）
{
//通过faras体改
如果（cell.Text！= NULL）
{
行[cell.Start.Column - 1] = cell.Text;
}
}
tbl.Rows.Add（行）;
tbl.TableName = ws.Name;
}
DataTable的DT = RemoveEmptyRows（TBL）;
ds.Tables.Add（DT）;
}
}
赶上（例外EXP）
{
}
返回DS;
}
}
如果你正在为用户提供上传的模板，可以减轻这种由一些在您的S preadsheet使用指定范围。这是一个好主意，反正编程时使用Excel工作，因为它有助于当你修改自己对S preadsheet，不只是当用户执行。
您可能知道如何命名一个范围，但对于完整性，<一个着想href=\"https://support.office.com/en-us/article/Define-and-use-names-in-formulas-4d0f13ac-53b7-422e-afd2-abd7ff379c64\"相对=nofollow>这里是如何命名的范围。
当你在$ C $在S preadsheet工作c可以使用 [yourworkbook] .Names得到的范围内引用[yourNamedRange] 。如果它只是一个单细胞，你需要引用您可以使用的行或列索引 .Start.Row 或 .Start.Column 。
我添加任何东西命名的范围 - 包含特定的值，列标题行，其中的数据集开始列细胞。如果我需要行或列的索引我有用分配变量名。保护您不必在您的S preadsheet种种幻数的。您（或您的用户）可以移动颇有几分不破坏任何东西。
如果他们修改该结构太大，那么它不会工作。您还可以使用在工作簿和工作表保护，以确保它们不会意外地修改结构 - 标签，行，列
这是从松散的测试，我与上周末时我正在学习这个拍摄。这只是一个Hello World，所以我没有试图让这一切精简和完善。 （我正在填充A S preadsheet，不读书的，所以我刚学的属性，我去。）
//打开工作簿
使用（VAR包=新ExcelPackage（新的FileInfo（PriceQuoteTemplate.xlsx）））
{
//获取我要找工作表
VAR quoteSheet = package.Workbook.Worksheets [引用]; //如果我想从一个命名范围获取文本
VAR CELLTEXT = quoteSheet.Workbook.Names [myNamedRange。文本 //如果我想获得该单元格的值作为一些其他类型
。VAR cellValue = quoteSheet.Workbook.Names [myNamedRange]的GetValue＆LT; INT＆GT;（）; //如果我有一个名为范围，通过我行要循环，并得到
从某些列的值//
VAR myRange = quoteSheet.Workbook.Names [rangeContainingRows]; //这是用来标记列的命名范围。因此，而不是使用的
//神奇的数字，我会无论从任何列有此命名范围读取。
VAR someColumn = quoteSheet.Workbook.Names [columnLabel] Start.Column。 对于（VAR ROWNUMBER = myRange.Start.Row; ROWNUMBER＆LT; myRange.Start.Row + myRange.Rows; ROWNUMBER ++）
{
VAR getTheTextForTheRowAndColumn = quoteSheet.Cells（ROWNUMBER，someColumn）。文本
}
有可能是一个更优雅的方式去了解它。我刚开始使用这个自己。但这个想法是你告诉它找到的S preadsheet一定的命名范围，然后使用该范围，而不是一个神奇的行或列数的行或列数。
即使一个范围可能是一个小区，一个行或一列，也可以潜在地是更大的区域。这就是为什么我用 .Start.Row 。换句话说，给我的的第一个的范围内的单元格的行。如果范围有多个行，则 .Rows 属性显示的行数，所以我知道有多少。这意味着，有人甚至可以插入行而不破code。
I am using Epplus library in order to upload data from excel file.The code i am using is perfectly works for excel file which has standard form.ie if first row is column and rest all data corresponds to column.But now a days i am getting regularly , excel files which has different structure and i am not able to read excel file like as shown below
what i want is on third row i wan only Region and Location Id and its values.Then 7th row is columns and 8th to 15 are its values.Finally 17th row is columns for 18th to 20th .How to load all these datas to seperate datatables code i used is as shown below I created an extension method
public static DataSet Exceltotable(this string path)
{
DataSet ds = null;
using (var pck = new OfficeOpenXml.ExcelPackage())
{
try
{
using (var stream = File.Open(path, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
pck.Load(stream);
}
ds = new DataSet();
var wss = pck.Workbook.Worksheets;
////////////////////////////////////
//Application app = new Application();
//app.Visible = true;
//app.Workbooks.Add("");
//app.Workbooks.Add(@"c:\MyWork\WorkBook1.xls");
//app.Workbooks.Add(@"c:\MyWork\WorkBook2.xls");
//for (int i = 2; i <= app.Workbooks.Count; i++)
//{
// for (int j = 1; j <= app.Workbooks[i].Worksheets.Count; j++)
// {
// Worksheet ws = app.Workbooks[i].Worksheets[j];
// ws.Copy(app.Workbooks[1].Worksheets[1]);
// }
//}
///////////////////////////////////////////////////
//for(int s=0;s<5;s++)
//{
foreach (var ws in wss)
{
System.Data.DataTable tbl = new System.Data.DataTable();
bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
string ErrorMessage = string.Empty;
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
var row = tbl.NewRow();
foreach (var cell in wsRow)
{
//modifed by faras
if (cell.Text != null)
{
row[cell.Start.Column - 1] = cell.Text;
}
}
tbl.Rows.Add(row);
tbl.TableName = ws.Name;
}
DataTable dt = RemoveEmptyRows(tbl);
ds.Tables.Add(dt);
}
}
catch (Exception exp)
{
}
return ds;
}
}
If you're providing the template for users to upload, you can mitigate this some by using named ranges in your spreadsheet. That's a good idea anyway when programmatically working with Excel because it helps when you modify your own spreadsheet, not just when the user does.
You probably know how to name a range, but for the sake of completeness, here's how to name a range.
When you're working with the spreadsheet in code you can get a reference to the range using [yourworkbook].Names["yourNamedRange"]. If it's just a single cell and you need to reference the row or column index you can use .Start.Row or .Start.Column.
I add named ranges for anything - cells containing particular values, columns, header rows, rows where sets of data begin. If I need row or column indexes I assign useful variable names. That protects you from having all sorts of "magic numbers" in your spreadsheet. You (or your users) can move quite a bit around without breaking anything.
If they modify the structure too much then it won't work. You can also use protection on the workbook and worksheet to ensure that they can't accidentally modify the structure - tabs, rows, columns.
This is loosely taken from a test I was working with last weekend when I was learning this. It was just a "hello world" so I wasn't trying to make it all streamlined and perfect. (I was working on populating a spreadsheet, not reading one, so I'm just learning the properties as I go.)
// Open the workbook
using (var package = new ExcelPackage(new FileInfo("PriceQuoteTemplate.xlsx")))
{
// Get the worksheet I'm looking for
var quoteSheet = package.Workbook.Worksheets["Quote"];
//If I wanted to get the text from one named range
var cellText = quoteSheet.Workbook.Names["myNamedRange"].Text
//If I wanted to get the cell's value as some other type
var cellValue = quoteSheet.Workbook.Names["myNamedRange"].GetValue<int>();
//If I had a named range and I wanted to loop through the rows and get
//values from certain columns
var myRange = quoteSheet.Workbook.Names["rangeContainingRows"];
//This is a named range used to mark a column. So instead of using a
//magic number, I'll read from whatever column has this named range.
var someColumn = quoteSheet.Workbook.Names["columnLabel"].Start.Column;
for(var rowNumber = myRange.Start.Row; rowNumber < myRange.Start.Row + myRange.Rows; rowNumber++)
{
var getTheTextForTheRowAndColumn = quoteSheet.Cells(rowNumber, someColumn).Text
}
There might be a more elegant way to go about it. I just started using this myself. But the idea is you tell it to find a certain named range on the spreadsheet, and then you use the row or column number of that range instead of a magic row or column number.
Even though a range might be one cell, one row, or one column, it can potentially be a larger area. That's why I use .Start.Row. In other words, give me the row for the first cell in the range. If a range has more than one row, the .Rows property indicates the number of rows so I know how many there are. That means someone could even insert rows without breaking the code.
这篇关于如何读取asp.net excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
