使用c#读取excel文件
本文关键字：文件 excel 读取 使用 | 更新日期: 2023-09-27 18:01:20
我正在尝试阅读一个Excel表格，有一些使用c#合并的单元格，看起来像:
Day |1 |2
------------------------------------
Month|CA |CATTC | CA |CATTC
------------------------------------
1 |100 |20 | 250 |120
5 |100 |30 | 202 |140
12 |130 |260 | 255 |130
我的目标是将它存储在像
这样的表中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
要读取它，请尝试代码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++;
}
}
}
}
我不知道如何继续代码，任何帮助将是最感激的。
您可以使用互操作，但这需要在运行该工具的计算机上安装Excel。
你必须参考Office或Excel，然后使用这些用法。
using Microsoft.Office.Core;
using 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.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;
或者您可以使用EPPlus。这是我用的。http://epplus.codeplex.com/它是免费的，你不需要在工作站上使用excel。
下载它。引用dll。使用这些用法
using OfficeOpenXml;
using OfficeOpenXml.Style;
从单元格中读取。
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();
}
或者写:
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;
}
}
我通常创建一个adodb记录集，并存储我需要的数据，因为我从电子表格中读取它。然后，它可以用于任何庄园进行分析，向用户显示，或以您需要的格式输出。
为此，您可以添加对adob的引用。添加使用
using 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中读取值时，您可以将记录(行)添加到记录集中。这里是一些代码，我通过电子表格的"使用范围"循环并将数据保存到记录集。
//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() + ".");
}
}
然后可以遍历创建的记录。
rsAddress.MoveFirst();
for (; !rsAddress.EOF; rsAddress.MoveNext())
{
if ( rsAddress.Fields["CustomerNumber"].Value = "SomeValue"){
//Do something
}
}