private static void PictureSample(ExcelPackage package)
{
var ws = package.Workbook.Worksheets.Add("Picture");
//Add an jpg image and apply some effects.
var pic = ws.Drawings.AddPicture("Landscape", FileInputUtil.GetFileInfo("14-ShapesAndImages", "LandscapeView.jpg"));
pic.SetPosition(2, 0, 1, 0);
pic.Effect.SetPresetShadow(ePresetExcelShadowType.OuterBottomRight);
pic.Effect.OuterShadow.Distance = 10;
pic.Effect.SetPresetSoftEdges(ePresetExcelSoftEdgesType.SoftEdge5Pt);
//Add the same image, but with 25 percent of the size. Let the position be absolute.
pic = ws.Drawings.AddPicture("LandscapeSmall", FileInputUtil.GetFileInfo("14-ShapesAndImages", "LandscapeView.jpg"));
pic.SetPosition(2, 0, 16, 0);
pic.SetSize(25); //25%
pic.ChangeCellAnchor(eEditAs.Absolute);
//Add the same image again, but let the picture move and resize when rows and colums are resized.
pic = ws.Drawings.AddPicture("LandscapeMoveAndResize", FileInputUtil.GetFileInfo("14-ShapesAndImages", "LandscapeView.jpg"));
pic.SetPosition(30, 0, 16, 0);
pic.ChangeCellAnchor(eEditAs.TwoCell);
//Add the image overlapping the first image, but make sure it is behind
pic = ws.Drawings.AddPicture("LandscapeSendToBack", FileInputUtil.GetFileInfo("14-ShapesAndImages", "LandscapeView.jpg"));
pic.SetPosition(8, 0, 8, 0);
pic.SetSize(25); //25%
pic.SendToBack();
}
/// <summary>
/// Shows a few different ways to load / save asynchronous
/// </summary>
/// <param name="connectionString">The connection string to the SQLite database</param>
public static async Task RunAsync(string connectionString)
{
var file = FileOutputUtil.GetFileInfo("03-AsyncAwait.xlsx");
using (ExcelPackage package = new ExcelPackage(file))
{
var ws = package.Workbook.Worksheets.Add("Sheet1");
using (var sqlConn = new SQLiteConnection(connectionString))
{
sqlConn.Open();
using (var sqlCmd = new SQLiteCommand("select CompanyName, [Name], Email, c.Country, o.OrderId, orderdate, ordervalue, currency from Customer c inner join Orders o on c.CustomerId=o.CustomerId inner join SalesPerson s on o.salesPersonId = s.salesPersonId ORDER BY 1,2 desc", sqlConn))
{
var range = await ws.Cells["B2"].LoadFromDataReaderAsync(sqlCmd.ExecuteReader(), true, "Table1", TableStyles.Medium10);
range.AutoFitColumns();
}
}
await package.SaveAsync();
}
//Load the package async again.
using (var package = new ExcelPackage())
{
await package.LoadAsync(file);
var newWs = package.Workbook.Worksheets.Add("AddedSheet2");
var range = await newWs.Cells["A1"].LoadFromTextAsync(FileInputUtil.GetFileInfo("03-UsingAsyncAwait", "Importfile.txt"), new ExcelTextFormat {
Delimiter = '\t'
});
range.AutoFitColumns();
await package.SaveAsAsync(FileOutputUtil.GetFileInfo("03-AsyncAwait-LoadedAndModified.xlsx"));
}
}
/// <summary>
/// Sample 7 - open Sample 1 and add 2 new rows and a Piechart
/// </summary>
public static string Run()
{
FileInfo newFile = FileOutputUtil.GetFileInfo("07-OpenWorkbookAndAddDataAndChartSample.xlsx");
FileInfo templateFile = FileInputUtil.GetFileInfo("07-OpenWorkbookAddDataAndChart", "ExistingWorkbook.xlsx");
using (ExcelPackage package = new ExcelPackage(newFile, templateFile))
{
//Open the first worksheet
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
worksheet.InsertRow(5, 2);
worksheet.Cells["A5"].Value = "12010";
worksheet.Cells["B5"].Value = "Drill";
worksheet.Cells["C5"].Value = 20;
worksheet.Cells["D5"].Value = 8;
worksheet.Cells["A6"].Value = "12011";
worksheet.Cells["B6"].Value = "Crowbar";
worksheet.Cells["C6"].Value = 7;
worksheet.Cells["D6"].Value = 23.48;
worksheet.Cells["E2:E6"].FormulaR1C1 = "RC[-2]*RC[-1]";
var name = worksheet.Names.Add("SubTotalName", worksheet.Cells["C7:E7"]);
name.Style.Font.Italic = true;
name.Formula = "SUBTOTAL(9,C2:C6)";
//Format the new rows
worksheet.Cells["C5:C6"].Style.Numberformat.Format = "#,##0";
worksheet.Cells["D5:E6"].Style.Numberformat.Format = "#,##0.00";
var chart = worksheet.Drawings.AddPieChart("PieChart", ePieChartType.Pie3D);
chart.Title.Text = "Total";
//From row 1 colum 5 with five pixels offset
chart.SetPosition(0, 0, 5, 5);
chart.SetSize(600, 300);
ExcelAddress valueAddress = new ExcelAddress(2, 5, 6, 5);
var ser = (chart.Series.Add(valueAddress.Address, "B2:B6") as ExcelPieChartSerie);
chart.DataLabel.ShowCategory = true;
chart.DataLabel.ShowPercent = true;
chart.Legend.Border.LineStyle = eLineStyle.Solid;
chart.Legend.Border.Fill.Style = eFillStyle.SolidFill;
chart.Legend.Border.Fill.Color = Color.DarkBlue;
//Set the chart style to match the preset style for 3D pie charts.
chart.StyleManager.SetChartStyle(ePresetChartStyle.Pie3dChartStyle3);
//Switch the PageLayoutView back to normal
worksheet.View.PageLayoutView = false;
// save our new workbook and we are done!
package.Save();
}
return(newFile.FullName);
}
private static void FillAndColorSamples(ExcelPackage package)
{
var ws = package.Workbook.Worksheets.Add("Fills And Colors");
//Drawing with a Solid fill
var drawing = ws.Drawings.AddShape("SolidFill", eShapeStyle.RoundRect);
drawing.SetPosition(0, 5, 0, 5);
drawing.SetSize(250, 250);
drawing.Fill.Style = eFillStyle.SolidFill;
drawing.Fill.SolidFill.Color.SetSchemeColor(eSchemeColor.Accent6);
drawing.Text = "RoundRect With Solid Fill";
//Drawing with a pattern fill
drawing = ws.Drawings.AddShape("PatternFill", eShapeStyle.SmileyFace);
drawing.SetPosition(0, 5, 4, 5);
drawing.SetSize(250, 250);
drawing.Fill.Style = eFillStyle.PatternFill;
drawing.Fill.PatternFill.PatternType = eFillPatternStyle.DiagBrick;
drawing.Fill.PatternFill.BackgroundColor.SetPresetColor(ePresetColor.Yellow);
drawing.Fill.PatternFill.ForegroundColor.SetSystemColor(eSystemColor.GrayText);
drawing.Border.Width = 2;
drawing.Border.Fill.Style = eFillStyle.SolidFill;
drawing.Border.Fill.SolidFill.Color.SetHslColor(90, 50, 25);
drawing.Font.Fill.Color = Color.Black;
drawing.Font.Bold = true;
drawing.Text = "Smiley With Pattern Fill";
//Drawing with a Gradient fill
drawing = ws.Drawings.AddShape("GradientFill", eShapeStyle.Heart);
drawing.SetPosition(0, 5, 8, 5);
drawing.SetSize(250, 250);
drawing.Fill.Style = eFillStyle.GradientFill;
drawing.Fill.GradientFill.Colors.AddRgb(0, Color.DarkRed);
drawing.Fill.GradientFill.Colors.AddRgb(30, Color.Red);
drawing.Fill.GradientFill.Colors.AddRgbPercentage(65, 100, 0, 0);
drawing.Fill.GradientFill.Colors[2].Color.Transforms.AddAlpha(75);
drawing.Text = "Heart with Gradient";
//Drawing with a blip fill
drawing = ws.Drawings.AddShape("BlipFill", eShapeStyle.Bevel);
drawing.SetPosition(0, 5, 12, 5);
drawing.SetSize(250, 250);
drawing.Fill.Style = eFillStyle.BlipFill;
var image = new Bitmap(FileInputUtil.GetFileInfo("14-ShapesAndImages", "EPPlusLogo.jpg").FullName);
drawing.Fill.BlipFill.Image = image;
drawing.Fill.BlipFill.Stretch = true;
drawing.Text = "Blip Fill";
}
public void Run()
{
//var resourceStream = GetResource("EPPlusSampleApp.Core.FormulaCalculation.FormulaCalcSample.xlsx");
var filePath = FileInputUtil.GetFileInfo("06-FormulaCalculation", "FormulaCalcSample.xlsx").FullName;
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
// Read the value from the workbook. This is calculated by Excel.
double?totalSales = package.Workbook.Worksheets["Sales"].Cells["E10"].GetValue <double?>();
Console.WriteLine("Total sales read from Cell E10: {0}", totalSales.Value);
// This code removes all calculated values
RemoveCalculatedFormulaValues(package.Workbook);
// totalSales from cell C10 should now be empty
totalSales = package.Workbook.Worksheets["Sales"].Cells["E10"].GetValue <double?>();
Console.WriteLine("Total sales read from Cell E10: {0}", totalSales.HasValue ? totalSales.Value.ToString() : "null");
// ************** 1. Calculate the entire workbook **************
package.Workbook.Calculate();
// totalSales should now be recalculated
totalSales = package.Workbook.Worksheets["Sales"].Cells["E10"].GetValue <double?>();
Console.WriteLine("Total sales read from Cell E10: {0}", totalSales.HasValue ? totalSales.Value.ToString() : "null");
// ************** 2. Calculate a worksheet **************
// This code removes all calculated values
RemoveCalculatedFormulaValues(package.Workbook);
package.Workbook.Worksheets["Sales"].Calculate();
// totalSales should now be recalculated
totalSales = package.Workbook.Worksheets["Sales"].Cells["E10"].GetValue <double?>();
Console.WriteLine("Total sales read from Cell E10: {0}", totalSales.HasValue ? totalSales.Value.ToString() : "null");
// ************** 3. Calculate a range **************
// This code removes all calculated values
RemoveCalculatedFormulaValues(package.Workbook);
package.Workbook.Worksheets["Sales"].Cells["E10"].Calculate();
// totalSales should now be recalculated
totalSales = package.Workbook.Worksheets["Sales"].Cells["E10"].GetValue <double?>();
Console.WriteLine("Total sales read from Cell E10: {0}", totalSales.HasValue ? totalSales.Value.ToString() : "null");
}
}
public IActionResult Import()
{
var filePath = FileInputUtil.GetFileInfo("Data", "ExcelProducts.xlsx").FullName;
using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0]; // => Pega o primeiro arquivo com o nome "ExcelProducts"
var rowCount = worksheet.Dimension.End.Row; // => Identifica quantas linhas preenchidas tem o arquivo
//var colCnt = worksheet.Dimension.End.Column + 1; // => Identifica quantas colunas preenchidas tem o arquivo
IList <Product> products = new List <Product>();
for (int row = 2; row <= rowCount; row++)
{
Product product = new Product();
for (int col = 1; col < 4; col++)
{
if (col == 1)
{
product.Id = new Guid(worksheet.Cells[row, col].Value.ToString());
}
if (col == 2)
{
product.Name = worksheet.Cells[row, col].Value.ToString();
}
if (col == 3)
{
product.Price = Convert.ToDecimal(worksheet.Cells[row, col].Value);
}
}
products.Add(product);
}
return(View(products));
}
}
private static void LoadFile1(ExcelPackage package)
{
//Create the Worksheet
var sheet = package.Workbook.Worksheets.Add("Csv1");
//Create the format object to describe the text file
var format = new ExcelTextFormat
{
EOL = "\n",
TextQualifier = '"',
SkipLinesBeginning = 2,
SkipLinesEnd = 1
};
var file1 = FileInputUtil.GetFileInfo("05-ImportAndExportCsvFiles", "Sample5-1.txt");
//Now read the file into the sheet. Start from cell A1. Create a table with style 27. First row contains the header.
Console.WriteLine("Load the text file...");
var range = sheet.Cells["A1"].LoadFromText(file1, format, TableStyles.Medium27, true);
Console.WriteLine("Format the table...");
//Tables don't support custom styling at this stage(you can of course format the cells), but we can create a Namedstyle for a column...
var dateStyle = package.Workbook.Styles.CreateNamedStyle("TableDate");
dateStyle.Style.Numberformat.Format = "YYYY-MM";
var numStyle = package.Workbook.Styles.CreateNamedStyle("TableNumber");
numStyle.Style.Numberformat.Format = "#,##0.0";
//Now format the table...
var tbl = sheet.Tables[0];
tbl.ShowTotal = true;
tbl.Columns[0].TotalsRowLabel = "Total";
tbl.Columns[0].DataCellStyleName = "TableDate";
tbl.Columns[1].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[1].DataCellStyleName = "TableNumber";
tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[2].DataCellStyleName = "TableNumber";
tbl.Columns[3].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[3].DataCellStyleName = "TableNumber";
tbl.Columns[4].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[4].DataCellStyleName = "TableNumber";
tbl.Columns[5].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[5].DataCellStyleName = "TableNumber";
tbl.Columns[6].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[6].DataCellStyleName = "TableNumber";
Console.WriteLine("Create the chart...");
//Now add a stacked areachart...
var chart = sheet.Drawings.AddChart("chart1", eChartType.AreaStacked);
chart.SetPosition(0, 630);
chart.SetSize(800, 600);
//Create one series for each column...
for (int col = 1; col < 7; col++)
{
var ser = chart.Series.Add(range.Offset(1, col, range.End.Row - 1, 1), range.Offset(1, 0, range.End.Row - 1, 1));
ser.HeaderAddress = range.Offset(0, col, 1, 1);
}
//Set the style to predefied style 27. You can also use the chart.StyleManager.SetChartStyle method to set more modern styles. See for example the csv2 sheet in this sample.
chart.Style = eChartStyle.Style27;
sheet.View.ShowGridLines = false;
sheet.Calculate();
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
}
private static async Task LoadFile2Async(ExcelPackage package)
{
//Create the Worksheet
var sheet = package.Workbook.Worksheets.Add("Csv2");
//Create the format object to describe the text file
var format = new ExcelTextFormat
{
EOL = "\n",
Delimiter = '\t', //Tab
SkipLinesBeginning = 1
};
CultureInfo ci = new CultureInfo("sv-SE"); //Use your choice of Culture
ci.NumberFormat.NumberDecimalSeparator = ","; //Decimal is comma
format.Culture = ci;
//Now read the file into the sheet.
Console.WriteLine("Load the text file...");
var file2 = FileInputUtil.GetFileInfo("05-ImportAndExportCsvFiles", "Sample5-2.txt");
var range = await sheet.Cells["A1"].LoadFromTextAsync(file2, format);
//Add a formula
range.Offset(1, range.End.Column, range.End.Row - range.Start.Row, 1).FormulaR1C1 = "RC[-1]-RC[-2]";
//Add a table...
var tbl = sheet.Tables.Add(range.Offset(0, 0, range.End.Row - range.Start.Row + 1, range.End.Column - range.Start.Column + 2), "Table");
tbl.ShowTotal = true;
tbl.Columns[0].TotalsRowLabel = "Total";
tbl.Columns[1].TotalsRowFormula = "COUNT(3,Table[Product])"; //Add a custom formula
tbl.Columns[2].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[3].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[4].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[5].TotalsRowFunction = RowFunctions.Sum;
tbl.Columns[5].Name = "Profit";
tbl.TableStyle = TableStyles.Medium10;
sheet.Cells[sheet.Dimension.Address].AutoFitColumns();
//Add a chart with two charttypes (Column and Line) and a secondary axis...
var chart = sheet.Drawings.AddChart("chart2", eChartType.ColumnStacked);
chart.SetPosition(0, 540);
chart.SetSize(800, 600);
var serie1 = chart.Series.Add(range.Offset(1, 3, range.End.Row - 1, 1), range.Offset(1, 1, range.End.Row - 1, 1));
serie1.Header = "Purchase Price";
var serie2 = chart.Series.Add(range.Offset(1, 5, range.End.Row - 1, 1), range.Offset(1, 1, range.End.Row - 1, 1));
serie2.Header = "Profit";
//Add a Line series
var chartType2 = chart.PlotArea.ChartTypes.Add(eChartType.LineStacked);
chartType2.UseSecondaryAxis = true;
var serie3 = chartType2.Series.Add(range.Offset(1, 2, range.End.Row - 1, 1), range.Offset(1, 0, range.End.Row - 1, 1));
serie3.Header = "Items in stock";
//By default the secondary XAxis is not visible, but we want to show it...
chartType2.XAxis.Deleted = false;
chartType2.XAxis.TickLabelPosition = eTickLabelPosition.High;
//Set the max value for the Y axis...
chartType2.YAxis.MaxValue = 50;
chart.StyleManager.SetChartStyle(ePresetChartStyle.ComboChartStyle2);
sheet.View.ShowGridLines = false;
sheet.Calculate();
}
public static void Run()
{
// Create a list of dynamic objects
dynamic p1 = new ExpandoObject();
p1.Id = 1;
p1.FirstName = "Ivan";
p1.LastName = "Horvat";
p1.Age = 21;
dynamic p2 = new ExpandoObject();
p2.Id = 2;
p2.FirstName = "John";
p2.LastName = "Doe";
p2.Age = 45;
dynamic p3 = new ExpandoObject();
p3.Id = 3;
p3.FirstName = "Sven";
p3.LastName = "Svensson";
p3.Age = 68;
List <ExpandoObject> items = new List <ExpandoObject>()
{
p1,
p2,
p3
};
// Create a workbook with a worksheet and load the data into a table
using (var package = new ExcelPackage(FileOutputUtil.GetFileInfo("04-LoadDynamicObjects.xlsx")))
{
var sheet = package.Workbook.Worksheets.Add("Dynamic");
sheet.Cells["A1"].LoadFromDictionaries(items, c =>
{
// Print headers using the property names
c.PrintHeaders = true;
// insert a space before each capital letter in the header
c.HeaderParsingType = HeaderParsingTypes.CamelCaseToSpace;
// when TableStyle is not TableStyles.None the data will be loaded into a table with the
// selected style.
c.TableStyle = TableStyles.Medium1;
});
package.Save();
}
// Load data from json (in this case a file)
var jsonItems = JsonConvert.DeserializeObject <IEnumerable <ExpandoObject> >(File.ReadAllText(FileInputUtil.GetFileInfo("04-LoadingData", "testdata.json").FullName));
using (var package = new ExcelPackage(FileOutputUtil.GetFileInfo("04-LoadJsonFromFile.xlsx")))
{
var sheet = package.Workbook.Worksheets.Add("Dynamic");
sheet.Cells["A1"].LoadFromDictionaries(jsonItems, c =>
{
// Print headers using the property names
c.PrintHeaders = true;
// insert a space before each capital letter in the header
c.HeaderParsingType = HeaderParsingTypes.CamelCaseToSpace;
// when TableStyle is not TableStyles.None the data will be loaded into a table with the
// selected style.
c.TableStyle = TableStyles.Medium1;
});
sheet.Cells["D:D"].Style.Numberformat.Format = "yyyy-mm-dd";
sheet.Cells[1, 1, sheet.Dimension.End.Row, sheet.Dimension.End.Column].AutoFitColumns();
package.Save();
}
}
/// <summary>
/// This sample creates a new workbook from a template file containing a chart and populates it with Exchange rates from
/// the database and set the three series on the chart.
/// </summary>
/// <param name="connectionString">Connectionstring to the db</param>
/// <param name="template">the template</param>
/// <param name="outputdir">output dir</param>
/// <returns></returns>
public static string Run(string connectionString)
{
FileInfo template = FileInputUtil.GetFileInfo("17-FXReportFromDatabase", "GraphTemplate.xlsx");
using (ExcelPackage p = new ExcelPackage(template, true))
{
//Set up the headers
ExcelWorksheet ws = p.Workbook.Worksheets[0];
ws.Cells["A20"].Value = "Date";
ws.Cells["B20"].Value = "EOD Rate";
ws.Cells["B20:F20"].Merge = true;
ws.Cells["G20"].Value = "Change";
ws.Cells["G20:K20"].Merge = true;
ws.Cells["B20:K20"].Style.HorizontalAlignment = ExcelHorizontalAlignment.CenterContinuous;
using (ExcelRange row = ws.Cells["A20:G20"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
row.Style.Font.Color.SetColor(Color.White);
row.Style.Font.Bold = true;
}
ws.Cells["B21"].Value = "USD/SEK";
ws.Cells["C21"].Value = "USD/EUR";
ws.Cells["D21"].Value = "USD/INR";
ws.Cells["E21"].Value = "USD/CNY";
ws.Cells["F21"].Value = "USD/DKK";
ws.Cells["G21"].Value = "USD/SEK";
ws.Cells["H21"].Value = "USD/EUR";
ws.Cells["I21"].Value = "USD/INR";
ws.Cells["J21"].Value = "USD/CNY";
ws.Cells["K21"].Value = "USD/DKK";
using (ExcelRange row = ws.Cells["A21:K21"])
{
row.Style.Fill.PatternType = ExcelFillStyle.Solid;
row.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
row.Style.Font.Color.SetColor(Color.Black);
row.Style.Font.Bold = true;
}
int startRow = 22;
//Connect to the database and fill the data
using (var sqlConn = new SQLiteConnection(connectionString))
{
int row = startRow;
sqlConn.Open();
using (var sqlCmd = new SQLiteCommand("SELECT date, SUM(Case when CurrencyCodeTo = 'SEK' Then rate Else 0 END) AS [SEK], SUM(Case when CurrencyCodeTo = 'EUR' Then rate Else 0 END) AS [EUR], SUM(Case when CurrencyCodeTo = 'INR' Then rate Else 0 END) AS [INR], SUM(Case when CurrencyCodeTo = 'CNY' Then rate Else 0 END) AS [CNY], SUM(Case when CurrencyCodeTo = 'DKK' Then rate Else 0 END) AS [DKK] FROM CurrencyRate where [CurrencyCodeFrom]='USD' AND CurrencyCodeTo in ('SEK', 'EUR', 'INR','CNY','DKK') GROUP BY date ORDER BY date", sqlConn))
{
using (var sqlReader = sqlCmd.ExecuteReader())
{
// get the data and fill rows 22 onwards
while (sqlReader.Read())
{
ws.Cells[row, 1].Value = sqlReader[0];
ws.Cells[row, 2].Value = sqlReader[1];
ws.Cells[row, 3].Value = sqlReader[2];
ws.Cells[row, 4].Value = sqlReader[3];
ws.Cells[row, 5].Value = sqlReader[4];
ws.Cells[row, 6].Value = sqlReader[5];
row++;
}
}
//Set the numberformat
ws.Cells[startRow, 1, row - 1, 1].Style.Numberformat.Format = "yyyy-mm-dd";
ws.Cells[startRow, 2, row - 1, 6].Style.Numberformat.Format = "#,##0.0000";
//Set the Formulas
ws.Cells[startRow + 1, 7, row - 1, 11].Formula = $"B${startRow}/B{startRow+1}-1";
ws.Cells[startRow, 7, row - 1, 11].Style.Numberformat.Format = "0.00%";
}
//Set the series for the chart. The series must exist in the template or the program will crash.
var chart = ws.Drawings["SampleChart"].As.Chart.LineChart; //We know the chart is a linechart, so we can use the As.Chart.LineChart Property directly
chart.Title.Text = "Exchange rate %";
chart.Series[0].Header = "USD/SEK";
chart.Series[0].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[0].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 7, row - 1, 7);
chart.Series[1].Header = "USD/EUR";
chart.Series[1].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[1].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 8, row - 1, 8);
chart.Series[2].Header = "USD/INR";
chart.Series[2].XSeries = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1);
chart.Series[2].Series = "'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 9, row - 1, 9);
var serie = chart.Series.Add("'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 10, row - 1, 10),
"'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1));
serie.Header = "USD/CNY";
serie.Marker.Style = eMarkerStyle.None;
serie = chart.Series.Add("'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 11, row - 1, 11),
"'" + ws.Name + "'!" + ExcelRange.GetAddress(startRow + 1, 1, row - 1, 1));
serie.Header = "USD/DKK";
serie.Marker.Style = eMarkerStyle.None;
chart.Legend.Position = eLegendPosition.Bottom;
//Set the chart style
chart.StyleManager.SetChartStyle(236);
}
//Get the documet as a byte array from the stream and save it to disk. (This is useful in a webapplication) ...
var bin = p.GetAsByteArray();
FileInfo file = FileOutputUtil.GetFileInfo("17-FxReportFromDatabase.xlsx");
File.WriteAllBytes(file.FullName, bin);
return(file.FullName);
}
}
public IActionResult OpenEdit(Processo modelo)
{
//string modelo = Path.Combine(@"c:\word\ABC.docx");
var modelFile = FileInputUtil.GetFileInfo("Data", "ABC.docx").FullName;
using (MemoryStream memoryRepository = new MemoryStream()) // => Cria um repositório em memória
{
using (WordprocessingDocument doc = WordprocessingDocument.Open(modelFile, false)) // => faz a leitura do doc modelo
using (WordprocessingDocument newDoc = WordprocessingDocument.Create(memoryRepository, WordprocessingDocumentType.Document)) // => instancia um novo docx no repositório em memória
{
foreach (var part in doc.Parts)
{
newDoc.AddPart(part.OpenXmlPart, part.RelationshipId); // => copia o texto do docx modelo para o novo docx em memória
}
var document = newDoc.MainDocumentPart.Document; // => Separa o texto do novo docx em partes para a leitura
foreach (var text in document.Descendants <Text>())
{
if (text.Text.Contains("101")) // => Nesse bloco são identificados os caracteres e substituídos caso existam no texto
{
text.Text = text.Text.Replace("101", modelo.Vara);
}
if (text.Text.Contains("102"))
{
text.Text = text.Text.Replace("102", modelo.Comarca);
}
if (text.Text.Contains("103"))
{
text.Text = text.Text.Replace("103", modelo.NrProcessoCnj);
}
if (text.Text.Contains("104"))
{
text.Text = text.Text.Replace("104", modelo.BancoNome);
}
if (text.Text.Contains("105"))
{
text.Text = text.Text.Replace("105", modelo.Acao);
}
if (text.Text.Contains("106"))
{
text.Text = text.Text.Replace("106", modelo.ClienteNome);
}
if (text.Text.Contains("107"))
{
text.Text = text.Text.Replace("107", modelo.TotalDivida.ToString("C") + "(" + ConverteParaExtenso.ValorParaExtenso2(modelo.TotalDivida) + ")");
}
if (text.Text.Contains("108"))
{
text.Text = text.Text.Replace("108", modelo.ValorEntrada.ToString("C") + "(" + ConverteParaExtenso.ValorParaExtenso2(modelo.ValorEntrada) + ")");
}
if (text.Text.Contains("109"))
{
text.Text = text.Text.Replace("109", modelo.DataVencimento.ToString("dd/MM/yyyy"));
}
if (text.Text.Contains("110"))
{
text.Text = text.Text.Replace("110", modelo.NrParcelas.ToString() + "(" + ConverteParaExtenso.NumeroParaExtenso(modelo.NrParcelas) + ")");
}
if (text.Text.Contains("111"))
{
text.Text = text.Text.Replace("111", modelo.ValorParcela.ToString("C") + "(" + ConverteParaExtenso.ValorParaExtenso2(modelo.ValorParcela) + ")");
}
}
}
return(File(memoryRepository.ToArray(), "application/vnd.openxmlformats-officedocument.wordprocessingml.document", "Documento.docx")); // => Faz o dowload do docx em memória
}
}
