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);
}
}
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();
}
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
}
}
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;
//To the header row and totals font to italic, use the HeaderRowStyle and the TotalsRowStyle property. You can also use the tbl.DataStyle to style the data part of the table.
tbl.HeaderRowStyle.Font.Italic = true;
tbl.TotalsRowStyle.Font.Italic = true;
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();
}