CODEWITHSUNDEEP
c#excel
007
007

Reputation: 2186

Clear formatting from an Excel file w/ C#

I have a highly customized excel file (i.e. .xlsx). I'm reading the file using ACE provider.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=myfilefullpath.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=NO;IMEX=1";

I have mixed data format in some of the fields (i.e. Column E has a few rows with string value and then hundreds of rows with double values).

Some fields has custom formats. When reading this file into datatable, I'm seeing custom format output and not the "real" cell value.

i.e. date ("yyyy/mm/dd") is custom formatted to "day mm/dd" and datatable reads "day mm/dd." I want to read the "yyyy/mm/dd or at least the integer value of the date in this example) BEFORE the data is inserted into the datatable. If possible, how would I go about doing that?

Update: I'm using Microsoft.Office.Interop.Excel

Upvotes: 1

Views: 2178

Answers (1)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

I have been working lately with two libraries for parsing and generating Excel files and I can recommend them to read your Excel file:

1) Open XML SDK 2.5 for Office. Quick example:

FileInfo newFile = new FileInfo(filePath);
ExcelPackage pck = new ExcelPackage(newFile);            

var dataSheet = pck.Workbook.Worksheets.FirstOrDefault(ws => ws.Name.Equals("Data"));
var someUnformattedValue = dataSheet .Cells[1, 2].Value;

2) ClosedXML. Quick example:

var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets[0];
var someUnformattedValue = worksheet.Cell("A1").Value;
workbook.SaveAs("data.xlsx");

Both allow in memory processing (no need to save file on the disk), but ClosedXML does not have support for charts (at least, not yet). Also, they provide an intuitive object model.

Besides some problems regarding conditional formatting in ClosedXML, I was able to parse and generate pretty complex Excel files (with pivots and/or tens of thousands of data).

Upvotes: 1

Related Questions