EPPLUS how to know the format of the Worksheet cell

I am using EPPlus to read excel sheets and load the data into datatable to perform further operations and then save back the modified data to the excel file.The below code checks if the cell value is a float value then it converts the float value to datetime. The code works fine when the cell value is a date eg: Invoice Date = 42009 , but it converts the not a date value like eg : amount = 10 to a date.Is there any way in EPPlus library from which i can determine the format of the cell (i.e General/date/number etc) ?float floatValue;if (float.T...Read more

EPPlus - How to copy only cells with data?

How can I read an excel file and select ONLY those CELLS that have data - not formatting, not spaces , just text or numbers.Using EPPlus? I know I can read the spreadsheet like this : How can I do a 'select' on only those cells and make a copy. (But But these values are wrong !! int rowCount = excelRange.Rows.Count; int colCount = excelRange.Columns.Count;)Excel.Application excelApp = new Excel.Application(); if (excelApp != null) { Excel.Workbook excelWorkbook = excelApp.Workbooks.Open(@"C:\test.xls", 0, true, 5, ""...Read more

EPPlus - Cannot find column 10

I have an application where user uploads excel ( 2007 or higher ) file, this file gets parsed, and content is stored in Database. I'm reading content from Excel using EPPlus. Here's the code snippetforeach (var worksheet in package.Workbook.Worksheets) { for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) { DataRow newRow = dt.NewRow(); for (int j = worksheet.Dimension.S...Read more

Broken rows when reading inlineStr cells with EPPlus C#

I have xlsx-file which opens succesfully with Excel and which can be parsed with other excel-libraries than EPPlus. We are likely to continue using EPPlus, so it will be nice to get some advice with this issue. Excel data in is plain text data without formatting. When parsing with EPPlus with C#, let's say 3x3 sheet, parsed data is fragmented following way to memory (empty cells added to every row so total size is 3x9 or something):r1c1 r1c2 r1c3 r2c1 r2c2 r2c3 r3c1 r3c2 r3c3instead of 3x3 arrayr1c1 r1...Read more

EPPlus Conditional Format 'AddExpression' problem

I'm hoping someone who's got more experience than I have with EPPlus can point me in a better direction.I have a table of values in one sheet (40 columns, roughly 600 rows), and a list of values in another sheet. For each row, any value in the table that doesn't match the corresponding value in the list (same row) should be highlighted by setting the background to red.I figured out a way to make this happen; but it is VERY slow. It is creating a conditional format rule for each of some 24000 cells, which would be a long time in any case, but t...Read more

How to set the font color for conditional formatting with EPPLUS?

Does anybody know how to set the font color for conditional formatting with EPPLUS?Using rng = ws.Cells("A7:AD29") Dim condSumLn = ws.ConditionalFormatting.AddExpression(rng) condSumLn.Style.Font.Bold = True condSumLn.Style.Fill.BackgroundColor.Color = Drawing.Color.LightGray condSumLn.Formula = "If(OFFSET($AD" & iHeadRow + 1 & ",0,0)>0,1,0)"End Using...Read more

epplus - Detecting last row and calculating total rows on page

I am currently working with a client that generates their invoices manually in Excel. I have used epplus to generate this file automatically for them. One of the requirements I have is that each page should have a border around it and start with a header that is about 7 rows long (and each row has different heights based on what I have to put in them). In order to do this, I need to detect when I am at the end of a page so that I can pause my data printing and print out the header, then print out all the data for that page and print the bord...Read more

c# EPPlus: how to fix - ShowFilter doesn't work

The issue with Table.ShowFilter, using EPPLUS library.Created new ExcelTable in the sheet, but can't apply Table.ShowFilter = false.The filters are still in the table.TableStyle and StyleName work fine.ExcelTable et = (ExcelTable)Table;int firstRow = newRow;int lastRow;if (DataStructure.Data != null) lastRow = newRow + DataStructure.Data.Count();else lastRow = newRow + 1;int firstColumn = OriginalAddress.StartColumn;int lastColumn = OriginalAddress.EndColumn;ExcelRange rg = ws.Cells[firstRow, firstColumn, lastRow, lastColumn];Guid gui...Read more

Epplus - LoadFromCollection and column order

I'm using the method LoadFromCollection to fill an excel with a List. However, the excel columns need to have a certain order to provide a better context for the user, so I'm trying to find out how to accomplish this.One option I've is to set the order of the parameters inside the class, but I'm avoiding this because someone else can change their order and that will affect my code.The second option I've is to use Linq:var orderedColumns = (from p in myList select new { p.Name, p.Age, ...}).ToList();This way I can also defin...Read more

Epplus Cell with DateTime type not recognized

I have a problem with the DateTime format.A cell has the following NumberFormat "[$-F800]dddd\,\ mmmm\ dd\,\ yyyy" but it is not recognized as DateTime.if (cells.Value is DateTime){ var dateTime = DateTime.Parse(cells.Value.ToString()); }How can i Get the DataType of Cell?Best Regards...Read more

Create a column dropdown in EPPlus

I have everything else worked out but I want to put a drop down on a cell (range of cells) so that users are forced to select from the list. I've tried this: var dd = worksheet.Cells[5, 3, row, 3].DataValidation.AddListDataValidation() as ExcelDataValidationList; dd.AllowBlank = true; //Add list hereBut I can't find any method or property that allows me to link the list.How is this done? I can't find any documentation on it....Read more