用c＃读取excel文件 [英] Read excel file with c#
本文介绍了用c＃读取excel文件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试阅读一个使用C＃的合并单元格的Excel表格，如下所示：
Day | 1 | 2
------------------------------------
月| CA | CATTC | CA | CATTC
------------------------------------
1 | 100 | 20 | 250 | 120
5 | 100 | 30 | 202 | 140
12 | 130 | 260 | 255 | 130
我的目标是将它存储在像
月| CA | CATTC |日
------------------------------------
1 | 100 | 20 | 1
5 | 100 | 30 | 1
12 | 130 | 260 | 1
1 | 250 | 120 | 2
5 | 202 | 140 | 2
12 | 255 | 130 | 2
要阅读它，请尝试一下代码c＃
private void Output_Excel_File（string inputFileLocation）
{
DataSet ds = Get_Spreadsheet_Data（inputFileLocation，Dashboard Statistics）;
if（ds.Tables.Count> 0）
{
foreach（ds.Tables中的DataTable dt）
{
int row = 0;
foreach（dt.Rows中的DataRow dr）
{
int col = 0;
foreach（dt.Columns中的DataColumn dc）
{
//做某事
col ++;
}
row ++;
}
}
}
}
没有看到如何继续执行代码。任何帮助将是最受赞赏的。
解决方案
你可以使用互操作，但这需要您必须参考Office或Excel，然后使用这些用法。
$ p
使用Microsoft.Office.Core;
使用Excel = Microsoft.Office.Interop.Excel;
然后你可以读写它。
Excel.Application xlsApp = new Excel.Application（）;
Excel._Workbook wrk = xlsApp.Workbooks.Open（@C：\test.xlsx，0，true，5，Missing.Value，Missing.Value，true，Excel.XlPlatform.xlWindows，Missing。值，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value，Missing.Value）;
int j = 1;
while（j< 100）{
xlsApp.Cells [j，1] = j;
j = j + 1;
}
xlsApp.Visible = true;
或者您可以使用EPPlus。这是我使用的 http://epplus.codeplex.com/ 它是免费的，您不需要在工作站上excel使用它。
下载。参考dll。使用这些使用
使用OfficeOpenXml;
使用OfficeOpenXml.Style;
从单元格中读取。
FileInfo AddressList = new FileInfo（c：\temp\test.xlsx）;
使用（ExcelPackage package = new ExcelPackage（AddressList））
{
//获取文件中的工作簿
ExcelWorkbook workBook = package.Workbook;
if（workBook！= null）
{
if（workBook.Worksheets.Count> 0）
{
//获取第一个工作表
// ExcelWorksheet Worksheet = workBook.Worksheets.First（）;
var worksheet = package.Workbook.Worksheets [1];
if（worksheet.Cells [A1]。Value.ToString（）！=Address）
{
MessageBox.Show（单元格A1应该说地址中止）;
返回;
}
//这是确保一个空单元格不会导致错误的安全方法。
string callValue = worksheet.Cells [E2]。值== null？ string.Empty：worksheet.Cells [E2]。Value.ToString（）;
if（string.IsNullOrEmpty（strTerminal.Trim（））== false）
{
MessageBox.Show（callValue.ToString（））;
}
}
}
package.Dispose（）;
}
或写入它，如。
FileInfo AddressList = new FileInfo（c：\\\\\\test.xlsx）;
使用（ExcelPackage package = new ExcelPackage（AddressList））
{
//获取文件中的工作簿
ExcelWorkbook workBook = package.Workbook;
if（workBook！= null）
{
if（workBook.Worksheets.Count> 0）
{
//获取第一个工作表
var worksheet = package.Workbook.Worksheets [1];
工作表Cells [D2]。值=其他一些字符串;
worksheet.Cells [E2]。Value =Some string;
}
}
try
{
package.Save（）;
}
catch（Exception ex）
{
//MessageBox.Show(\"保存电子表格的错误。+ ex）;
MessageBox.Show（保存电子表格时出错，是否打开？）;
返回;
}
}
我通常创建一个adodb记录集并存储数据需要在我从电子表格中读取。然后，它可以用于任何庄园分析，显示给用户，或以您需要的格式输出。
为此，您可以添加对adodb的引用。使用ADODB添加
根据代码范围，声明一个记录集
private ADODB.Recordset rsAddress = new ADODB.Recordset（）;
再次根据你的范围，在适当的地方建立记录集字段。
rsAddress.Fields.Append（Row，DataTypeEnum.adInteger）;
rsAddress.Fields.Append（Address，DataTypeEnum.adVarChar，75）;
rsAddress.Fields.Append（CustomerNumber，DataTypeEnum.adVarChar，75）;
rsAddress.Open（）;
然后当您从Excel中读取值时，可以将记录（行）添加到记录集中。这里是一些代码，我在循环使用电子表格的used range，并将数据保存到记录集。
/查找最后一行的真实行。
var rowRun = worksheet.Dimension.End.Row;
while（rowRun> = 1）
{
var range = worksheet.Cells [rowRun，1，rowRun，worksheet.Dimension.End.Column];
if（range.Any（c =>！string.IsNullOrEmpty（c.Text）））
{
break;
}
rowRun--;
}
//循环工作表并记录我们需要的值。
// var start = worksheet.Dimension.Start;
（int row = 2; row< = rowRun; row ++）
{
//检查我们是否已经有当前地址
string strHouseAddress = worksheet.Cells [ + row.ToString（）]。值== null？ string.Empty：worksheet.Cells [A+ row.ToString（）]。Value.ToString（）;
rsAddress.Filter =;
rsAddress.Filter =Address ='+ strHouseAddress.Trim（）+';
if（rsAddress.RecordCount == 0）
{
//记录此地址
rsAddress.Filter =;
rsAddress.AddNew（）;
rsAddress.Fields [Row]。Value = row;
try
{
if（string.IsNullOrEmpty（strHouseAddress.Trim（））== false）
{
rsAddress.Fields [Address]。Value = strHouseAddress.Trim（）;
}
else
{
rsAddress.Fields [Address]。Value =0 MISSING ST;
MessageBox.Show（row inrow.ToString（）+。修复电子表格并重新加载。
}
string strTerminal = worksheet.Cells [E+ row.ToString（）]。Value == null？ string.Empty：worksheet.Cells [E+ row.ToString（）]。Value.ToString（）;
if（string.IsNullOrEmpty（strTerminal.Trim（））== false）
{
rsAddress.Fields [CustomerNumber]。Value = strTerminal.Trim（）;
}
rsAddress.Update（）;
}
catch
{
MessageBox.Show（从行中的列A读取数据时出错+ row.ToString（））;
}
}
else
{
MessageBox.Show（地址列表和行中找到的重复地址+ row.ToString（）+ ）;
}
}
然后可以循环创建您创建的记录。 / p>
rsAddress.MoveFirst（）; （;！rsAddress.EOF; rsAddress.MoveNext（））
{
if（rsAddress.Fields [CustomerNumber]。Value =SomeValue）{
/ /做某事
}
}
I am trying to read an Excel sheet that has some merged cells using C#, looks like :
Day |1 |2
------------------------------------
Month|CA |CATTC | CA |CATTC
------------------------------------
1 |100 |20 | 250 |120
5 |100 |30 | 202 |140
12 |130 |260 | 255 |130
My goal is to store it in a table like
Month|CA |CATTC | Day
------------------------------------
1 |100 |20 | 1
5 |100 |30 | 1
12 |130 |260 | 1
1 |250 |120 | 2
5 |202 |140 | 2
12 |255 |130 | 2
To read it , T try a code c#
private void Output_Excel_File( string inputFileLocation)
{
DataSet ds = Get_Spreadsheet_Data(inputFileLocation, "Dashboard Statistics");
if (ds.Tables.Count > 0)
{
foreach (DataTable dt in ds.Tables)
{
int row = 0;
foreach (DataRow dr in dt.Rows)
{
int col = 0;
foreach (DataColumn dc in dt.Columns)
{
//do something
col++;
}
row++;
}
}
}
}
I didn't figure how to continue the code .Any help will be the most appreciated .
解决方案
You can use interop but that requires that Excel is installed on the computer that runs the tool.
You have to reference Office or just Excel and then use these usings.
using Microsoft.Office.Core;
using Excel = Microsoft.Office.Interop.Excel;
Then you can read and write to it.
Excel.Application xlsApp = new Excel.Application();
Excel._Workbook wrk = xlsApp.Workbooks.Open(@"C:\test.xlsx", 0, true, 5, Missing.Value, Missing.Value, true, Excel.XlPlatform.xlWindows, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
int j=1;
while (j < 100) {
xlsApp.Cells[j, 1] = j;
j = j + 1;
}
xlsApp.Visible = true;
Or you can use EPPlus. This is what i use. http://epplus.codeplex.com/ It is free and you don't need excel on a workstation to use it.
Download it. Reference the dll. Use these usings
using OfficeOpenXml;
using OfficeOpenXml.Style;
Read from a cell.
FileInfo AddressList = new FileInfo("c:\temp\test.xlsx");
using (ExcelPackage package = new ExcelPackage(AddressList))
{
// Get the work book in the file
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
// Get the first worksheet
//ExcelWorksheet Worksheet = workBook.Worksheets.First();
var worksheet = package.Workbook.Worksheets[1];
if (worksheet.Cells["A1"].Value.ToString() != "Address")
{
MessageBox.Show("The cell A1 should say Address. Aborting.");
return;
}
// This is a safe way to make sure a null cell will not cause you an error.
string callValue = worksheet.Cells["E2"].Value == null ? string.Empty : worksheet.Cells["E2"].Value.ToString();
if (string.IsNullOrEmpty(strTerminal.Trim()) == false)
{
MessageBox.Show(callValue.ToString());
}
}
}
package.Dispose();
}
Or write to it such as.
FileInfo AddressList = new FileInfo("c:\\temp\\test.xlsx");
using (ExcelPackage package = new ExcelPackage(AddressList))
{
// Get the work book in the file
ExcelWorkbook workBook = package.Workbook;
if (workBook != null)
{
if (workBook.Worksheets.Count > 0)
{
// Get the first worksheet
var worksheet = package.Workbook.Worksheets[1];
worksheet.Cells["D2"].Value = "Some other string";
worksheet.Cells["E2"].Value = "Some string";
}
}
try
{
package.Save();
}
catch (Exception ex)
{
//MessageBox.Show("Error saving the spreadsheet. " + ex);
MessageBox.Show("Error saving the spreadsheet. Do you have it open?");
return;
}
}
I usually create an adodb recordset and store the data i need there as i read it from the spreadsheet. Then it can be used in any manor to analyse, show to a user, or output in a format you need.
To do that you can add a reference to adodb. Add using
using ADODB;
Depending on the scope of your code, declare a recordset
private ADODB.Recordset rsAddress = new ADODB.Recordset();
Again depending on your scope, in the appropriate place build the recordset fields.
rsAddress.Fields.Append("Row", DataTypeEnum.adInteger);
rsAddress.Fields.Append("Address", DataTypeEnum.adVarChar, 75);
rsAddress.Fields.Append("CustomerNumber", DataTypeEnum.adVarChar, 75);
rsAddress.Open();
Then as you read values from Excel you can add records(rows) to your recordset. Here is some code where i am looping through the "used range" of a spreadsheet and saving the data to a recordset.
//Find the "real" last used row.
var rowRun = worksheet.Dimension.End.Row;
while (rowRun >= 1)
{
var range = worksheet.Cells[rowRun, 1, rowRun, worksheet.Dimension.End.Column];
if (range.Any(c => !string.IsNullOrEmpty(c.Text)))
{
break;
}
rowRun--;
}
// Loop through the worksheet and record the values we need.
//var start = worksheet.Dimension.Start;
for (int row = 2; row <= rowRun; row++)
{
//Check if we already have the current address
string strHouseAddress = worksheet.Cells["A" + row.ToString()].Value == null ? string.Empty : worksheet.Cells["A" + row.ToString()].Value.ToString();
rsAddress.Filter = "";
rsAddress.Filter = "Address='" + strHouseAddress.Trim() + "'";
if (rsAddress.RecordCount == 0)
{
//Record this address
rsAddress.Filter = "";
rsAddress.AddNew();
rsAddress.Fields["Row"].Value = row;
try
{
if (string.IsNullOrEmpty(strHouseAddress.Trim()) == false)
{
rsAddress.Fields["Address"].Value = strHouseAddress.Trim();
}
else
{
rsAddress.Fields["Address"].Value = "0 MISSING ST";
MessageBox.Show("Missing address at row " + row.ToString() + ". Fix the spreadsheet and reload.");
}
string strTerminal = worksheet.Cells["E" + row.ToString()].Value == null ? string.Empty : worksheet.Cells["E" + row.ToString()].Value.ToString();
if (string.IsNullOrEmpty(strTerminal.Trim()) == false)
{
rsAddress.Fields["CustomerNumber"].Value = strTerminal.Trim();
}
rsAddress.Update();
}
catch
{
MessageBox.Show("Error reading data from column A on row " + row.ToString());
}
}
else
{
MessageBox.Show("Duplicate address found on the Address list and row " + row.ToString() + ".");
}
}
You can then loop through the records you created.
rsAddress.MoveFirst();
for (; !rsAddress.EOF; rsAddress.MoveNext())
{
if ( rsAddress.Fields["CustomerNumber"].Value = "SomeValue"){
//Do something
}
}
这篇关于用c＃读取excel文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
