The first value is for how many rows you want frozen, and the second is for how many columns you want frozen. Therefore, to freeze the first 3 rows and 5 columns you would need to call it as the following: Worksheets.View.FreezePanes(3, 5); You can also take a look at this SO question for more … Read more
Not sure why but none of the above solution work for me. So sharing what worked: public void readXLS(string FilePath) { FileInfo existingFile = new FileInfo(FilePath); using (ExcelPackage package = new ExcelPackage(existingFile)) { //get the first worksheet in the workbook ExcelWorksheet worksheet = package.Workbook.Worksheets[1]; int colCount = worksheet.Dimension.End.Column; //get Column Count int rowCount = worksheet.Dimension.End.Row; … Read more
None of the other answers quite got me there (the Excel worksheet was always empty), but this worked for me: using (var package = new ExcelPackage()) { var worksheet = package.Workbook.Worksheets.Add(“Worksheet Name”); worksheet.Cells[“A1”].LoadFromCollection(data); var stream = new MemoryStream(package.GetAsByteArray()); }
This is the other way to do: var cell = sheet.Cells[“A1”]; cell.Hyperlink = new Uri(“http://www.google.com”); cell.Value = “Click me!”; I have tested. It works fine.
While searching for help on the same problem, I stumbled across this link. It certainly worked for me! Definitely better than using Interop objects. 🙂 I adapted it slightly though: var package = new ExcelPackage(new FileInfo(“sample.xlsx”)); ExcelWorksheet workSheet = package.Workbook.Worksheets[0]; var start = workSheet.Dimension.Start; var end = workSheet.Dimension.End; for (int row = start.Row; row <= … Read more
You do need the DataTable column to have the right type but you also need to modify the column or cell’s Style.Numberformat.Format property. Say you have an ExcelWorksheet named ws: ws.Column(1).Style.Numberformat.Format = “yyyy-mm-dd”; //OR “yyyy-mm-dd h:mm” if you want to include the time!
using (ExcelPackage pck = new ExcelPackage(newFile)) { ExcelWorksheet ws = pck.Workbook.Worksheets.Add(“Accounts”); ws.Cells[“A1”].LoadFromDataTable(dataTable, true); pck.Save(); } That should do the trick for you. If your fields are defined as int EPPlus will properly cast the columns into a number or float.
I agree with Yosoyke. You’re probably targeting the wrong cells. You can try: ws.Cells[“A3”].Style.Numberformat.Format = “yyyy-mm-dd”; ws.Cells[“A3”].Formula = “=DATE(2014,10,5)”;
Try this Color colFromHex = System.Drawing.ColorTranslator.FromHtml(“#B7DEE8”); ws.Cells[“A1:B1”].Style.Fill.PatternType = ExcelFillStyle.Solid; ws.Cells[“A1:B1”].Style.Fill.BackgroundColor.SetColor(colFromHex);
Here are some number format options for EPPlus: //integer (not really needed unless you need to round numbers, Excel will use default cell properties) ws.Cells[“A1:A25”].Style.Numberformat.Format = “0”; //integer without displaying the number 0 in the cell ws.Cells[“A1:A25”].Style.Numberformat.Format = “#”; //number with 1 decimal place ws.Cells[“A1:A25”].Style.Numberformat.Format = “0.0”; //number with 2 decimal places ws.Cells[“A1:A25”].Style.Numberformat.Format = “0.00”; … Read more