FAQ Database Discussion Community


Can I create or edit XLTM files using EPPlus?

epplus
I want to create, open or edit a .xltm file using EPPlus. If I try to open it, it is opening. But when I edit it or copy paste formulas from that .xltm file to another .xltm file, it is not opening any more....

EPPlus: how can I assign border around each cell after I apply LoadFromCollection?

c#,asp.net-mvc-5,epplus
In my export ActionResult I was able to load the model into my ExcelPackage. Where I am having trouble is assigning a border around each cell once LoadFromCollection is applied. While the AutoFitColumns correctly applies, the border style I applied only works on Cells["D1"], but not on the table. BorderAround...

EPPlus, handling big ExcelWorksheet

excel-2010,epplus
I'm using EPPlus to create xls report on daily and weekly basis. But sometimes the timeline is greater than one year and, in my last case it was three year, so the total amount of rows are around 180k. In this case I've optimized the query against a SQL Server...

How to remove a column from excel sheet in epplus

c#,epplus
I'm using csharp to insert data into excel sheet into 7 columns. The interface of this program will allow users to select 7 checkboxes. If they select all 7, all the 7 columns in spreadhseet will have data, if they select one checkbox then only one column will have data....

epplus adds unwanted decimal places when getting text values

c#,epplus
I am using eppplus to read some values from a spreadsheet but it seems to add some decimal places even with no formatting on the cell. For example doing string value = worksheet.Cells[row, 1].Text // value on spreadsheet is 1000 puts 1,000.00 into value. How can I stop this from...

How to set cell color programmatically epplus?

c#,asp.net,epplus
I was wondering if it is possible to set cell color programmatically using epplus? I load my data from a sql stored procedure and it works well, but my users want cells that contain the words 'Annual Leave' to have a background color of light yellow instead of the default...

EPPlus: How can I move workbook property assignment commands to a helper function?

c#,epplus
To provide more context, if I simply want to export my model to an Excel file in my ActionResult, I have that worked out. I learned the commends to assign values to the workbook properties and cell formatting and the exported Excel file appears correctly. My problem is that all...

Load stream in Epplus causes disk write error

asp.net-mvc-4,epplus
Im upload an excel file and trying to load it into a ExcelPackage. Like this public static void ImportStuff(Stream stream) { using (ExcelPackage package = new ExcelPackage()) { package.Load(stream); ... My calling method gets the stream from an uploaded file public async Task<ActionResult> UploadCsv(int Id, HttpPostedFileBase myfile) { ImportProducts(myfile.InputStream); }...

Is is possible to delete a table in a worksheet with epplus?

epplus
In Epplus Worksheets have the member ExcelTableCollection Tables for adding and editing tables. However, you cannot delete a table. Is there another way to do it?

Pass all selected MultiSelectList values to MVC Controller for EPPlus Export to Excel?

c#,json,asp.net-mvc,export-to-excel,epplus
I'm attempting to add an Export() functionality to my MVC5 Code-First application using the EPPlus library. On my View I have a MultiSelectList with all the values for my main Model properties: @Html.ListBox("PropertyList", typeof(InventoryTracker.Models.INV_Assets).GetProperties().Select(p => new SelectListItem { Text = p.Name, Value = p.Name, Selected = false }), new {...

Exception:The total length of a DataValidation list cannot exceed 255 characters

c#,asp.net,epplus
Am trying to create a formula field dynamically in epplus. If formula field contains less than 255 character then it is creating properly. If it exceeds 255 then it is throwing a exception as Exception:The total length of a DataValidation list cannot exceed 255 characters. Can any one please help...

Not able to access empty cells EPPlus

c#,excel,epplus
Trying to figure out how to read all the cells, even the empty ones. Problem: When a cell is empty it is ignored and the value from the next cell is used instead..and an exception is thrown. This: Name..........Number Car...............10 Box...............26 .....................19 Apple............26 Ends up: Name..........Number Car...............10 Box..............26 19.............Apple 26....................

EPPlus Excel Export - All data values going into Column A for each record?

c#,asp.net-mvc,linq,export-to-excel,epplus
I am attempting to add Excel Export functionality to my MVC5/Code-First Entity Framework application via the EPPlus library and System.Linq.Dynamic. On my Export View I have filled a MultiSelectList with each of my INV_Assets model properties. These then get passed into my ExportController to designate which fields of the model...

Selecting grouped max and min in a Epplus worksheet with Linq

c#,linq,epplus
I have an Epplus Excel Worksheet that looks like this. I want to group by the Group column (in column A), and then select the max and the min. Something in my group by clause isn't quite right and I'm getting an error: Cannot apply indexing with [] to ExcelRangeBase....

Using secondary axis for chart cause x-axis and primary y-axis issue (Excel)

c#,excel,epplus
Making a chart using secondary axis, makes my chart primary y-axis is shown with some values which I don't want to have. Only x-axis and secondary y-axis. and also the x-axis is drawn without the date values what I've passed. Code: chartType2 = GetChartType(worksheet, chartToDraw, endcolcnt, i, chartType2, chartType); chartType2.UseSecondaryAxis...

Importing excel file with all the conditional formatting rules to epplus

c#,epplus,epplus-4
I have an excel file which contains lots of data along with icon sets and data bars based on the values in the cell. It looks like this: I want to import this excel sheet along with the conditional formatting. Is there any library for this?? I went through this...

Conditional Formatting With Icons Epplus

epplus,epplus-4
I need to achieve something like this with Epplus. Can someone guide me with the code I need to use....

Export to Excel - LINQ - Include ForeignKey values in .ToList()?

c#,asp.net-mvc,linq,export-to-excel,epplus
I am attempting to add Excel Export functionality to my MVC5/Code-First Entity Framework application via the EPPlus library. On my Export View I have filled a MultiSelectList with each of my INV_Assets model properties. These then get passed into my ExportController to designate which fields of the model are to...

Is it possible to copy row (with data, merging, style) in Excel using Epplus?

c#,.net,excel,epplus
The problem is that I need to insert data into Excel from the collection several times using a single template for the entire collection. using (var pckg = new ExcelPackage(new FileInfo(association.TemplatePath))) { var workSheet = pckg.Workbook.Worksheets[1]; var dataTable = WorksheetToDataTable(workSheet); /*Some stuff*/ FindAndReplaceValue(workSheet, dictionary, row); } private DataTable WorksheetToDataTable(ExcelWorksheet oSheet)...

Epplus conditional formatting based on a rule

c#,epplus
How can we add three icon sets to each cell in excel using epplus conditional formatting. I'm using following code to add three icon set: using (ExcelRange scoreRange = workSheet.Cells[1, 2, 1, 10]) { ExcelAddress rangeAddress = new ExcelAddress(scoreRange.Address); var ruleIconSet = workSheet.ConditionalFormatting.AddThreeIconSet(rangeAddress, eExcelconditionalFormatting3IconsSetType.Arrows); // This calculates based on the...

EPPlus export model to Excel: System.NullReferenceException

c#,asp.net-mvc-5,epplus
I have an MVC view that correctly displays a model. Now that I have been asked to put an Export to Excel function on that page, I heard about EPPlus and gave it a try. From this site I thought I had something that would simply do the job. After...

Slow loading of .CSV files using EPPLUS

c#,epplus
I have loads of .csv files I need to convert to .xslx after applying some formatting. A file containing approx 20 000 rows and 7 columns takes 12 minutes to convert. If the file contains more than 100 000 it runs for > 1 hour. This is unfortunately not acceptable...