Reputation:
Read excel file with c#
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 .
Upvotes: 0
Views: 6178
Answers (1)
Reputation: 6761
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
}
}
Upvotes: 3