You’re offline. This is a read only version of the page.
459
I'm using the EPPlus API to export my data to excel. What I want to achieve is to have a Wrap text in a cells in the first row of my excel and sizing of cells. I've tried with the following code but its not working. Any leads on how I can achieve that. Below is my code
System.IO.Stream workbookStream = new System.IO.MemoryStream();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
using (var package = new OfficeOpenXml.ExcelPackage(memoryStream)) { var currentRow = 1; var worksheets = package.get_Workbook().get_Worksheets(); var worksheet = worksheets.Add("Sheet1"); //worksheet.Protection.IsProtected = true; var cells = worksheet.get_Cells(); var cell = cells.get_Item(currentRow,1); cell.AutoFitColumns(); Style.ExcelBorderStyle styleBorder; //= cell.Style.WrapText;
styleBorder = cell.Style.Wraptext;
}
I know I could have used Office Integration for exporting data to excel but it does not give me the desired formatted spreadsheet.
This is my desired output
Your code contains a couple of things that aren't related to the problem in question, therefore let me provide a cleaner example. It's a runnable class that everybody can copy and execute; it has other dependencies besides EPPlus.
You'll get an Excel sheet with a hundred 'a' characters inside a text-wrapped cell.
Share
Report
All responses (
Answers (
using OfficeOpenXml;
class TextWrapDemo
{
public static void main(Args _args)
{
using (System.IO.MemoryStream stream = new System.IO.MemoryStream())
{
using (var package = new ExcelPackage(stream))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Sheet1");
Style.ExcelStyle style = worksheet.Column(1).Style;
style.WrapText = true;
ExcelRange cell = worksheet.Cells.get_Item(1,1);
cell.Value = strRep('a', 100);
package.Save();
}
File::SendFileToUser(stream, 'data.xlsx');
}
}
}
HcmLabourInvoiceTrans hlabourInvoiceTrans ;
HcmLabourInvoiceTransLines labourInvoiceTransLines;
DocuFileSaveResult saveResult = DocuFileSave::promptForSaveLocation(strFmt('Upload %1 %2 5M001', mthName(DateTimeUtil::month(today())), DateTimeUtil::year(today())), "xlsx", null, "Export to excel");
if (saveResult && saveResult.parmAction() != DocuFileSaveAction::Cancel)
{
saveResult.parmOpenParameters('web=1');
saveResult.parmOpenInNewWindow(false);
System.IO.Stream workbookStream = new System.IO.MemoryStream();
System.IO.MemoryStream memoryStream = new System.IO.MemoryStream();
using (var package = new OfficeOpenXml.ExcelPackage(memoryStream))
{
var currentRow = 1;
var worksheets = package.get_Workbook().get_Worksheets();
var worksheet = worksheets.Add("Sheet1");
//worksheet.Protection.IsProtected = true;
var cells = worksheet.get_Cells();
var cell = cells.get_Item(currentRow,1);
cell.AutoFitColumns();
Style.ExcelBorderStyle styleBorder; //= cell.Style.WrapText;
worksheet.Column(1).Width = 25;
worksheet.Column(1).Style.WrapText = true; //This is giving error Invalid token'='
styleBorder = cell.Style.Wraptext;
Style.Border borderStyle = cell.Style.Border;
borderStyle.BorderAround(ExcelBorderStyle::Thin, System.Drawing.Color::Black);
Style.ExcelFont style = cell.Style.Font;
//style.Bold = true;
style.Size = 11;
style.Name = "Trebuchet MS";
cell.set_Value("MIM CC");
cell = null;
for (hlabourInvoiceTrans = HcmLabourInvoiceTrans_ds.getFirst(true) ? HcmLabourInvoiceTrans_ds.getFirst(true) : hlabourInvoiceTrans; hlabourInvoiceTrans; hlabourInvoiceTrans = HcmLabourInvoiceTrans_ds.getNext())
{
currentRow ;
cell = null;
cell = cells.get_Item(currentRow, 1);
cell.set_Value(hlabourInvoiceTrans.CostCenter);
cell.AutoFitColumns();
Style.ExcelFont st1 = cell.Style.Font;
st1.Size = 11;
st1.Name = "Calibri";
cell = null;
}
package.Save();
}
memoryStream.Seek(0, System.IO.SeekOrigin::Begin);
DocuFileSave::processSaveResult(memoryStream, saveResult);
This is my whole code
Here is a link to an EPPlus tutorial page demonstrating WrapText: https://riptutorial.com/epplus/example/26411/text-alignment-and-word-wrap.
Can you show your complete code, please? Your brief reply doesn't make clear which object you tried to change.
Use Insert > Insert Code (in the rich-formatting view) to paste source code.
Setting it to true will give an error of "Wraptext is a readonly property"
This seems to be a question about how to use EPPlus library, not about D365FO, therefore this isn't the best place to ask.
Anyway, you can do that by setting WrapText property of a style to true.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,101
Super User 2024 Season 2
#2
Martin Dráb
229,896
Most Valuable Professional
#3
nmaenpaa
101,156